MySQL执行计划

  想了解mysql性能优化，首先要学会使用查看执行计划，执行计划主要用来查看SQL语句在数据库中的表现情况，通常用于SQL性能分析，SQL优化等

创建执行计划的语句有两种，如下所示

desc select * from table_test where table_test.id = 1;explain select * from table_test where table_test.id = 1;# desc  和 explain 两个关键字都可以用来查看SQL执行计划

压力测试准备

    创建表：

CREATE TABLE IF NOT EXISTS temp_100w(  id int not null DEFAULT 0 COMMENT "学号",  name VARCHAR(100) not null DEFAULT '未知' COMMENT "姓名",  age TINYINT(10) not null DEFAULT 99 comment "年龄",  sex ENUM('boy','girl','secret') DEFAULT 'secret' COMMENT '性别',  create_time TIMESTAMP NOT NULL default NOW() comment '软删除标记'  )engine=INNODB DEFAULT CHARSET utf8;# 故意没有添加任何索引

    创建存储过程制造随机数据100万行

# 创建函数delimiter //CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8 BEGIN      DECLARE chars_str varchar(255) DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';      DECLARE return_str varchar(255) DEFAULT '';      DECLARE i INT DEFAULT 0;      WHILE i < n DO          SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));         SET i = i + 1;     END WHILE;     RETURN return_str;  END//# 创建存储过程CREATE DEFINER = CURRENT_USER PROCEDURE `rand_isnert`(IN n INT,IN i INT) BEGIN    WHILE i < n DO    insert into temp_100w(id,name,age,sex)      VALUES(i,rand_string(10),FLOOR(RAND()*70),FLOOR(RAND()*2));    SET i = i + 1;   END WHILE;END//DELIMITER ;  # 修改换行符为;# 说明RAND()  # 创建0-1 随机小数FLOOR()  # 向下取整CEILING()  # 向上取整# 查看存储过程SHOW    { PROCEDURE   |   FUNCTION   }  STATUS   [  LIKE  'pattern'  ]show procedure status like 'rand%'\G;​# 调用存储过程 随机插入1000万行数据call rand_isnert(10000000,0)# 时间可能有点久

现在表中已经有了1000万数据

select count(id) from temp_100w;+----------+| count(*) |+----------+| 10000001 |+----------+

选取前10行数据查看

select * from temp_100w limit 10;+----+------------+-----+-----+---------------------+| id | name       | age | sex | create_time         |+----+------------+-----+-----+---------------------+|  1 | d4BA05gVEl |  30 |   1 | 2020-01-01 02:26:18 ||  1 | HR1nDSmYGh |   3 |   1 | 2020-01-01 02:35:33 ||  1 | KpBEgc2rWf |   6 |   0 | 2020-01-01 02:38:00 ||  2 | R4EQ7SUJKo |  29 |   0 | 2020-01-01 02:38:00 ||  3 | XVzUHAGwql |   9 |   0 | 2020-01-01 02:38:00 ||  4 | vrv4G20IqN |  27 |   0 | 2020-01-01 02:38:00 ||  5 | hB038jYP2w |  15 |   1 | 2020-01-01 02:38:00 ||  6 | nmvhKKlkr3 |  44 |   0 | 2020-01-01 02:38:00 ||  7 | HoH9x1oIeS |  23 |   1 | 2020-01-01 02:38:00 ||  8 | ORIMDFe3nB |  35 |   0 | 2020-01-01 02:38:00 |+----+------------+-----+-----+---------------------+10 rows in set (0.00 sec)​

现在抽取名字为KpBEgc2rWf 模拟100个用户对数据表进行200次查询的压力测试

mysqlslap  --defaults-file=/etc/my.cnf \--concurrency= 100  --iterations = 1 --create-schema='test'\--query='select * from test.temp_100w where name = "KpBEgc2rWf"' engine=innodb\--number-if-querues=2000 -uroot -p123456 -verbose

查看执行计划

desc select * from temp_100w where name = "KpBEgc2rWf";+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | temp_100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9738914 |    10.00 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+

分析执行计划

1. 看table列，单表可以忽略，但是多表的时候，需要优先看table字段

2. 查看type类型(查询的类型  全表扫描/索引扫描)

   1. ALL  全表扫描（最慢的）

   2. 索引扫描的级别（性能从慢到快）

      1. index 全索引扫描（结果集需要遍历整个索引树，性能比全表扫描要好，获取的数据量不多，顺序IO不是随机IO）

      2. range 索引范围扫描(>,<,<=,>=,like,between an ,or, in )

      3. ref  辅助索引等值查询 (唯一键可能也是辅助索引)

      4. eq_ref  多表连接的时候，子表使用主键列或者唯一列作为连接条件（A join B，A是驱动表,B就是子表，驱动是不走索引的，所以子表连接条件尽量要用主键，或者唯一键）

      5. const(system) ：主键或唯一键的等值查询

      6. NULL  

   3. 注意点：

      1. 对于辅助索引，!=,not in 等查询是不走索引的

      2. 对于or 和in 建议改写成union all代替 union 走的是ref比range要快

      3. 多表查询的时候，保证驱动表数据量集越小越好，因为不走索引（必须全表扫描），所有子表是走索引的

3. possible key 可能会用到的索引（可能有多个索引

4. key 实际用到的索引

5. ken_len 索引覆盖长度（索引短好）

6. extra

   1. using filesort 查询过程中建立了额外的排序，说明在查询中有关排序的条件列没有合理应用排序（order by,group by,distinct,union）

如何计算索引的长度呢？（重要）

索引的长度是按照字段的最大预留长度来计算的

以utf8mb4为例子，一个字符长度占4个字节，创建一张测试表

create table test_index_len(id int ,name char(10),age varchar(10) not null ,sex char(2) not null ,class_name varchar(10))charset='utf8mb4';

表结构如下

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | char(10)    | YES  |     | NULL    |       |
| age        | varchar(10) | NO   |     | NULL    |       |
| sex        | char(2)     | NO   |     | NULL    |       |
| class_name | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

给每一个字段都创建测试索引

alter table test_index_len add index k1(id);

alter table test_index_len add index k2(name);

alter table test_index_len add index k3(age);

alter table test_index_len add index k4(sex);

alter table test_index_len add index k5(classname);

插入数据

insert into test_index_len(id,name,age,sex,class_name)values(4,'curry','22','01',"Python02"),(2,'吴琳琳','12','女',"Python01"),(3,'吴琳琳','12','男',"Python01");

1. 查看id的索引长度

desc select * from test_index_len where id =1;
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | k1            | k1   | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
解析，因为id是int类型，占4个字节，而且没有设置非空，所以多一个字节标识非空，所以key_len = 5

2. 查看name的索引长度

desc select * from test_index_len where name='curry';
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id  | select_type | table          | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1  | SIMPLE   | test_index_len | NULL  | ref  | k2                         | k2   | 41      | const |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
解析：

因为是char（10）类型，所以索引定长40字节，非空，所以key_len = 41

3 . 查看sex索引的长度

desc select * from test_index_len where sex='女';
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | k4            | k4   | 8       | const |    2 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
解析：char(2)  not null 类型  8个字节 没毛病

4.查看class_name类型索引长度

desc select * from test_index_len where class_name='Python02';
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | k5            | k5   | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+

解析：因为varchar不定长字符串，varchar(10) not null 所以,至少占40个字节，加上not null 一个标志段 40+1 =41

然后 因为特殊情况，varchar需要多出两个字节位标识起停，所以宁外多出两个字节 所以为43

 

5.查看age类型索引长度

desc select * from test_index_len where age='22';
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | k3            | k3   | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+-------+
解析，varchar(10) null =4*10+2 =42 没毛病！

联合索引：add index idx(a,b,c);

删除所有索引，重新创建联合索引

alter table test_index_len drop index k1,k2,k3,k4,k5;

添加联合索引

alter table test_index_len add index ids(name,age,sex,class_name);

测试

desc select * from test_index_len where name="吴琳琳" and age="12" and sex="女" and class_name="Python01";
+----+-------------+----------------+------------+------+---------------+------+---------+-------------------------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref                     | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | ids           | ids  | 134     | const,const,const,const |    2 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+-------------------------+------+----------+-------+

解析

索引长度为：10*4+1 + 10*4+2 + 2*4 + 10 *4 +3 = 120+8+2+1+3 =134 全部使用到的情况

desc select * from test_index_len where name="吴琳琳" and age="12" and sex="女";
+----+-------------+----------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | ids           | ids  | 91      | const,const,const |    2 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
因为少用了一个class_name列，所以，索引长度为134-varchar(10) null=134-43=91 

所以联合索引的字节长度就是用到的索引列的字节数和

对于单列索引，索引长度越短越好，而对于联合索引来说，越长越好！
总结：

所有索引字段，如果没有设置not null ，则需要多加一个字节

定长字段，int 占4个字节，date占3个字节，char(n)占n个字符（是字符长度）

对于varchar(n)则有n*字符长度+2个字节

不同字符集，一个字符占用的字节数不一样，

latin1	1 个字符	1字节
gbk	1一个字符	2个字节
utf8	1个字符	3个字节
utf8mb4	一个字符	4个字节

如果已经创建了表，并且插入了数据，那么及时修改表结构，字节数还是以原来的字符集标准进行计算

对于联合索引 add index idx (a,b,c,d)

• 只要我们的查询，所有索引列都是全等值查询的时候，无关列的顺序（全等值和顺序无关）优化器会自动做查询条件的排列，但是要满足最左前缀原则
• 不连续部分条件
  • cda--.>> acd ----  a   优化 index(c,d,a)
  • dba -->> abd ----  ab
  • 原则之一就是唯一值多的列放在最左侧
• 在where查询中，出现不等值查询（<,>,<=,>=,like等）索引只能停止在这个条件，所以可以把不等值查询放在最后
• 多子句联合索引

索引应用规范

• 必须有主键，如果没有可以作为主键条件的列，创建无关列
• 经常做where条件的列，order by,group by ,join on ,distinct的条件
• 最好使用唯一值多的列作为列和索引的前导列
• 列值长度较长的索引列，我们建议使用前缀索引
• 降低索引条目，一方面不要再创建没用的索引，不常使用的索引清理，percona toolkit
• 索引维护要避开业务繁忙期
• 小表不要建立索引

不走索引的情况

• 没有查询条件，或者查询条件没有建立索引
• 查询结果集是原表中的大部分数据，应该是25%以上，
• 索引本身失效，统计数据不真实
• 查询条件使用函数在索引列上，或者对索引列进行计算，包括（+，-，*，/,! 等）
• 隐式转换导致索引失效
• <>，not in不走索引（辅助索引）
• like ‘%aa’百分号在前不走索引
• 联合索引