mysql的这些坑你踩过吗？
作为一个phper,此处也吐槽一下php的弱类型
怎么优化mysql?Explain 分析查看mysql性能
- 关于数据表格式规范
  - 谈谈mysql中utf8和utf8mb4区别

mysql的这些坑你踩过吗？

创建数据表，插入数据

CREATE TABLE `t_user` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`email` varchar(30) DEFAULT NULL COMMENT '邮箱',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`telephone` varchar(30) DEFAULT NULL COMMENT '电话',
`status` tinyint(4) DEFAULT NULL COMMENT '0:正常  1:下线 ',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP comment '创建时间',
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP comment '更新时间',
PRIMARY KEY (`id`),
KEY `idx_email` (`email`),
KEY `idx_name` (`name`),
KEY `idx_telephone` (`telephone`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
# 插入一条数据
INSERT INTO test.t_user (name, email, age, telephone, status, created_at, updated_at) VALUES ('jimi', 'ffdsa', 11, '15001262936', 0, DEFAULT, DEFAULT);
# 批量插入数据
INSERT INTO test.t_user  select  null,  name, email, age, telephone, 0, null, null from t_user;

字符串转数字,通过以下可以看到，主键id的类型是int,但是查询的关键字是string,这个时候就会转换

mysql> select * from t_user where id='2424786gafafdfdsa';
+---------+------+-------+------+-------------+--------+------------+------------+
| id      | name | email | age  | telephone   | status | created_at | updated_at |
+---------+------+-------+------+-------------+--------+------------+------------+
| 2424786 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL       | NULL       |
+---------+------+-------+------+-------------+--------+------------+------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain  select * from t_user where id='2426gafafdfdsa';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

字符串类型的字段0会全匹配

mysql> select * from t_user where  email=0 limit 10;
+----+------+-------+------+-------------+--------+---------------------+---------------------+
| id | name | email | age  | telephone   | status | created_at          | updated_at          |
+----+------+-------+------+-------------+--------+---------------------+---------------------+
|  2 | jimi | ffdsa |   11 | 15001262936 |      0 | 2020-11-27 14:33:57 | 2020-11-27 14:33:57 |
|  3 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
|  4 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
|  5 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
|  7 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
|  8 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
|  9 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
| 10 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
| 14 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
| 15 | jimi | ffdsa |   11 | 15001262936 |      0 | NULL                | NULL                |
+----+------+-------+------+-------------+--------+---------------------+---------------------+
10 rows in set, 10 warnings (0.00 sec)

大小写敏感问题（造成线上缓存击穿，如语音模块,视频模块已控制）

mysql> select * from t_user where email='ffdsaADFG';
+---------+------+-----------+------+-------------+--------+------------+------------+
| id      | name | email     | age  | telephone   | status | created_at | updated_at |
+---------+------+-----------+------+-------------+--------+------------+------------+
| 2424786 | jimi | ffdsaADFG |   11 | 15001262936 |      0 | NULL       | NULL       |
+---------+------+-----------+------+-------------+--------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from t_user where email='ffdsaadfg';
+---------+------+-----------+------+-------------+--------+------------+------------+
| id      | name | email     | age  | telephone   | status | created_at | updated_at |
+---------+------+-----------+------+-------------+--------+------------+------------+
| 2424786 | jimi | ffdsaADFG |   11 | 15001262936 |      0 | NULL       | NULL       |
+---------+------+-----------+------+-------------+--------+------------+------------+
1 row in set (0.00 sec)
# 解决大小写问题
#utf8_general_ci，表示不区分大小写；utf8_general_cs表示区分大小写；utf8_bin表示二进制比较，也可以比较大小写
ALTER TABLE t_user MODIFY COLUMN email VARCHAR(30) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;
mysql> select * from t_user where email='ffdsaadfg';
Empty set (0.00 sec)

数字转字符串,但是这种转化是用不上索引的

mysql> select * from t_user where email=123;
;
+---------+------+--------+------+-------------+--------+------------+------------+
| id      | name | email  | age  | telephone   | status | created_at | updated_at |
+---------+------+--------+------+-------------+--------+------------+------------+
| 2424789 | jimi | 123abc |   11 | 15001262936 |      0 | NULL       | NULL       |
+---------+------+--------+------+-------------+--------+------------+------------+
1 row in set, 65535 warnings (2.57 sec)
mysql> explain  select * from t_user where email=123;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | idx_email     | NULL | NULL    | NULL | 2090340 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

作为一个phper,此处也吐槽一下php的弱类型

/**
   * Notes:布尔类型转换
   * User: zhangguofu
   * Date: 2020/12/1
   * Time: 4:35 下午
   */
  public function test1()
  {
      $a = 2;
      $b = 3;
      if ($a = 3 || $b = 6) {
          $a++;
          $b++;
      }
      echo $a . " " . $b;//1  4
  }
  /**
   * Notes:字符串 数字类型转换
   * User: zhangguofu
   * Date: 2020/11/26
   * Time: 8:01 下午
   */
  public function test2()
  {
      $a = 'a';
      $b = 'b';
      $a++;
      var_dump($a == $b);//true
  }
/**
   * Notes:字符串 数字 弱类型对比和转换
   * User: zhangguofu
   * Date: 2020/12/4
   * Time: 3:12 下午
   */
  function test3()
  {
      var_dump(md5('240610708') == md5('QNKCDZO')); //true
      var_dump("admin" == 0);  //true
      var_dump("1admin" == 1); //true
      var_dump("admin1" == 1);//false
      var_dump("admin1" == 0);//true
      var_dump("0e123456" == "0e4456789"); //true
      var_dump(0 == "a"); // 0 == 0 -> true
      var_dump("1" == "01"); // 1 == 1 -> true
      var_dump("10" == "1e1"); // 10 == 10 -> true
      var_dump(100 == "1e2"); // 100 == 100 -> true
  }

怎么优化mysql?Explain 分析查看mysql性能

mysql> explain  select * from t_user where email=123;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | idx_email     | NULL | NULL    | NULL | 2090340 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

id : 编号
select_type ：查询类型
table ：表
type ：类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref :表之间的引用
rows ：通过索引查询到的数据量
Extra :额外的信息

解释：

插入数据

#课程表
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
#教师表
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
# 教师证
create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

id: id值相同，从上往下顺序执行。id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层再查外层)

mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
  -> and t.tcid = tc.tcid and c.cid = 2 or tc.tcid=3 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

select_type:查询类型 simple 简单子查询，不包含子查询和union primary 包含union或者子查询，最外层的部分标记为primary subquery 一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询 derived 派生表——该临时表是从子查询派生出来的，位于form中的子查询 union 位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived union result 用来从匿名临时表里检索结果的select被标记为union result dependent union 顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询 subquery 子查询中第一个SELECT语句 dependent subquery 和DEPENDENT UNION相对UNION一样

mysql> explain select * from   teacherCard limit 1;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | teacherCard | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
mysql> explain select  cr.cname from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL            |
|  2 | DERIVED      | course     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
|  3 | UNION        | course     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

type system: 只有一条数据的系统表；或衍生表只有一条数据的主查询 const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引（类型与索引类型有关）

mysql> create table test01
  -> (
  -> tid int(3),
  -> tname varchar(20)
  -> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> insert into test01 values(1,'a') ;
Query OK, 1 row affected (0.01 sec)
mysql> alter table test01 add constraint tid_pk primary key(tid) ;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from (select * from test01 )t where tid =1 ;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test01 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）

mysql>
mysql> alter table test01 drop primary key ;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> create index test01_index on test01(tid) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from (select * from test01 )t where tid =1 ;
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | test01_index  | test01_index | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）

mysql>  alter table teacherCard add constraint pk_tcid primary key(tcid);
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table teacher add constraint uk_tcid unique index(tcid) ;
ERROR 1061 (42000): Duplicate key name 'uk_tcid'
mysql>
mysql>
mysql> explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | index  | uk_tcid       | uk_tcid | 5       | NULL        |    3 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | tc    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t.tcid |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效，从而转为无索引all—5.7以前的版本)

mysql> alter table teacher add index tid_index (tid) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select t.* from teacher t where t.tid in (1,2) ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | tid_index     | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select t.* from teacher t where t.tid <3 ;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | tid_index     | tid_index | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index：查询全部索引中数据,不需要回表查找，黄金索引

mysql> explain select tid from teacher ;
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | teacher | NULL       | index | NULL          | tid_index | 5       | NULL |    3 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

all：查询全部表中的数据，全表扫描

mysql> explain select *  from teacher
  -> ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

possible_keys ：可能用到的索引，是一种预测，不准。

mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
  -> and t.tcid = tc.tcid and c.cname = 'sql' ;
+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys     | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL    | NULL              | NULL      | NULL    | NULL        |    4 |    25.00 | Using where |
|  1 | SIMPLE      | t     | NULL       | ref    | uk_tcid,tid_index | tid_index | 5       | test.c.tid  |    1 |   100.00 | Using where |
|  1 | SIMPLE      | tc    | NULL       | eq_ref | PRIMARY           | PRIMARY   | 4       | test.t.tcid |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

key ：实际使用到的索引

key_len ：索引的长度 ; 作用：用于判断复合索引是否被完全使用（a,b,c）。

mysql> create table test_kl
  -> (
  -> name char(20) not null default ''
  -> );
Query OK, 0 rows affected (0.03 sec)
mysql> alter table test_kl add index index_name(name) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from test_kl where name =''
  -> ;
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_kl | NULL       | ref  | index_name    | index_name | 80      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.06 sec)
# 字符集utf8mb4 char 20 就是 80，如果有null 则null 占一个字节，如果是varchar 则需要1-2个字节存储值的长度
mysql> alter table test_kl add column name1 char(20) ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table test_kl add index name_name1_index (name,name1) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from test_kl where name1 = '' ;
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_kl | NULL       | index | NULL          | name_name1_index | 161     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_kl where name = ''
  ->
  -> ;
+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys               | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_kl | NULL       | ref  | index_name,name_name1_index | index_name | 80      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from myTest  where b=3 and c=4;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | myTest | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 32893 |     1.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from myTest  where a=3 and c=4;
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | myTest | NULL       | ref  | a             | a    | 5       | const |    1 |    10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

ref 指明当前表所参照的字段。

mysql> alter table course  add index tid_index (tid) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from course c,teacher t where c.tid = t.tid  and t.tname ='tw' ;
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | tid_index     | NULL      | NULL    | NULL       |    3 |    33.33 | Using where |
|  1 | SIMPLE      | c     | NULL       | ref  | tid_index     | tid_index | 5       | test.t.tid |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+

rows: 被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)

mysql> explain select * from course c,teacher t  where c.tid = t.tid
  -> and t.tname = 'tz' ;
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | tid_index     | NULL      | NULL    | NULL       |    3 |    33.33 | Using where |
|  1 | SIMPLE      | c     | NULL       | ref  | tid_index     | tid_index | 5       | test.t.tid |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Extra：using filesort ：性能消耗大；需要“额外”的一次排序（查询）。常见于 order by 语句中。对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；怎么避免：where哪些字段，就order by那些字段 where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

mysql> create table test02
  -> (
  -> a1 char(3),
  -> a2 char(3),
  -> a3 char(3),
  -> index idx_a1(a1),
  -> index idx_a2(a2),
  -> index idx_a3(a3)
  -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> explain select * from test02 where a1 ='' order by a1 ;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test02 | NULL       | ref  | idx_a1        | idx_a1 | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from test02 where a1 ='' order by a2 ;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test02 | NULL       | ref  | idx_a1        | idx_a1 | 13      | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

using temporary:性能损耗大，用到了临时表。一般出现在group by 语句中。避免：查询那些列，就根据那些列 group by .
using index :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据（不需要回表查询),只要使用到的列全部都在索引中，就是索引覆盖using index
using where （需要回表查询）
impossible where ：where子句永远为false select * from test02 where a1='x' and a1='y'

关于数据表格式规范

谈谈mysql中utf8和utf8mb4区别
简介 MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上 🙂 😂)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等。 34.mysql的一些坑 - 图1
34.mysql的一些坑 - 图2

- 我认为 合理表应该这样设计
CREATE TABLE `demo`  (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`uuid` varchar(12)  NOT NULL DEFAULT '' COMMENT '业务id',
`create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
`update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
`status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态 0:正常 1:下线',
`logical_del` tinyint(2) NOT NULL DEFAULT 0 COMMENT '逻辑删除标识',
PRIMARY KEY `id`(`Id`) USING BTREE,
INDEX `uuid`(`uuid`) USING BTREE 
) ENGINE = InnoDB  DEFAULT  CHARSET=utf8mb4 COLLATE=utf8_bin   COMMENT = 'demo';

转自：https://mp.weixin.qq.com/s/BCzBARRpnPVA8y6oerP4hQ

34.mysql的一些坑

mysql的这些坑你踩过吗？

作为一个phper,此处也吐槽一下php的弱类型

怎么优化mysql?Explain 分析查看mysql性能

关于数据表格式规范

谈谈mysql中utf8和utf8mb4区别