MySQL之执行计划

0. 执行计划

(1) select * from t1 where xx='xxx'
获取到的是优化器选择的,它认为cost(代价)最小的执行计划.
对于计算机来说，代价: IO、CPU、MEM
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法
1. 全表扫描(应当尽量避免,因为性能低)
2. 索引扫描
3. 获取不到数据

1. 查看执行计划

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

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

2. 执行计划分析

2.1 type详解

ALL --> INDEX --> RANGE --> REF --> EQ-REF --> CONST 从左到右性能依次变好.

ALL  :  
全表扫描,不走索引
例子:
1. 查询条件列,没有索引
SELECT * FROM t_100w WHERE k2='780P';  
2. 查询条件出现以下语句(辅助索引列)
USE world 
DESC city;
DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';
-- 注意:对于聚集索引列,使用以上语句,依然会走索引
DESC SELECT * FROM city WHERE id <> 10;

INDEX  :
全索引扫描
1. 查询需要获取整个索引树种的值时:
DESC  SELECT countrycode  FROM city
2. 联合索引中,任何一个非最左列作为查询条件时:
idx_a_b_c(a,b,c)  ---> a  ab  abc
SELECT * FROM t1 WHERE b 
SELECT * FROM t1 WHERE c    

RANGE :
索引范围扫描 
对于辅助索引 > < >= <= LIKE IN OR between and
对于主键索引 <>  NOT IN
例子:
DESC SELECT * FROM city WHERE id<5;
DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');
注意: 
1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的.
所以,我们可以将3号列子改写:
DESC SELECT * FROM city WHERE countrycode='CHN'
UNION ALL 
SELECT * FROM city WHERE countrycode='USA';

REF: 
非唯一性索引,等值查询
DESC SELECT * FROM city WHERE countrycode='CHN';

EQ_REF: 
在多表连接时,连接条件使用了非驱动表的唯一索引(uk、pK)
DESC SELECT b.name,a.name FROM city AS a 
JOIN country AS b 
ON a.countrycode=b.code 
WHERE a.population <100;
DESC country

CONST(SYSTEM):
唯一索引的等值查询
DESC SELECT * FROM city WHERE id=10;

2.2 possible_keys, key

• possible_keys：可能会走的索引，所有和此次查询有关的索引
• key：优化器最终选择的索引

2.3 key_len

索引的长度，一般用于计算联合索引的覆盖程度

对于数值类型:
tinyint: 1+1=2
tinyint not none: 1
int: 4+1=5
int not none: 4
digint: 8+1=9
digint not none: 8

对于字符串类型(engine=utf8):
char(10): 3*10+1=31
char(10) not none: 3*10=30
varchar(10): 3*(10)+2+1=33
varchar(10): 3*(10)+2=32

-- 建表
mysql> create table t1(
    -> a int not null,
    -> b int,
    -> c char(10) not null,
    -> d varchar(10)
    -> )charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
-- 建联合索引
mysql> alter table t1 add index idx(a,b,c,d);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 覆盖a、b、c、d，索引长度为92
mysql> desc select * from t1 where a=1 and b=2 and c='3' and d='4';
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx           | idx  | 92      | const,const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- 覆盖a、b、c，索引长度为49
mysql> desc select * from t1 where a=1 and b=2 and c='3';
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx           | idx  | 49      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- 覆盖a、b，索引长度为9
mysql> desc select * from t1 where a=1 and b=2;
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx           | idx  | 9       | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- 覆盖a，索引长度为4
mysql> desc select * from t1 where a=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.4 extra

using filesort: 表示此次查询使用到了文件排序，说明在查询中的排序操作(order by、group by、distinct...)没有走索引，需要建立联合索引
结论: 
1. 当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2. 观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引

3. 索引建立规范

0. 说明:为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引
1. 建表时一定要有主键，一般是个数据无关列
2. 选择唯一性索引: 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
   (1) 如果非得使用重复值较多的列作为查询条件(例如性别)，可以将表逻辑拆分
   (2) 可以将此列和其他的查询类做联合索引
3. 为经常需要where、group by、order by、join on等操作的字段建立索引
   如果经常一起作为条件的列，重复值特别多，可以建立联合索引
4. 尽量使用前缀来进行索引
5. 限制索引的数目
   (1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大
   (2) 修改表时，对索引的重构和更新很麻烦，越多的索引，会使得表的更新变得很浪费时间
   (3) 优化器的负担会很重，有可能影响到优化器的选择
6. 删除不再使用或者很少使用的、重复的索引
7. 大表加索引，要避开业务繁忙期
8. 尽量少在需要经常更新值的列上建索引

4. 不走索引的情况

1. 没有查询条件，或者查询条件没有建立索引
   (1) 建索引
2. 查询结果集是原表中的大部分数据(大约是15-30%)
   查询的结果集，超过了总行数的25%，优化器可能觉得就没必要走索引了
   与数据库的预读能力有关，以及一些参数有关
   (1) 业务允许的情况下，用limit分页
   (2) 尽量不要走mysql存放这种数据，可以放到redis里面
3. 索引本身失效，统计数据不真实
   索引和表有自我维护的能力，对于表内容比较频繁变化的情况下，统计信息不准确、过旧，有可能导致索引失效
   现象: select 语句平时查询很快，突然有一天变得很慢
   (1) 一般是删除重建
   (2) 手动更新统计信息： optimize table world.city;
4. 查询条件使用函数在索引列上，或者对索引列进行运算(+、-、*、/、!等)
   如：select * from test where id-1=9;
5. 隐式转换导致索引失效
      如: 数值类型自动转型成字符串
   mysql> desc t1;
   +--------+----------+------+-----+---------+-------+
   | Field  | Type     | Null | Key | Default | Extra |
   | id     | int(11)  | NO   | PRI | NULL    |       |
   | telnum | char(11) | YES  | MUL | NULL    |       |
   +--------+----------+------+-----+---------+-------+
   2 rows in set (0.00 sec)
   -- 数值被隐式转型为字符串，不走索引
   mysql> desc select * from t1 where telnum=110;
   +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
   | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
   +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
   |  1 | SIMPLE      | t1    | NULL       | index | idx           | idx  | 12      | NULL |    5 |    20.00 | Using where; Using index |
   +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
   1 row in set, 3 warnings (0.00 sec)
   -- 走索引
   mysql> desc select * from t1 where telnum='110';
   +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
   | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
   +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
   |  1 | SIMPLE      | t1    | NULL       | ref  | idx           | idx  | 12      | const |    1 |   100.00 | Using index |
   +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
   1 row in set, 1 warning (0.00 sec)
6. <>、not in 不走索引(辅助索引)
7. like "%" 百分号在最前面不走索引

5. 优化器

【mysql】关于ICP、MRR、BKA等特性