explain
explain
(1)
获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法
1. 全表扫描(应当尽量避免,因为性能低)
2. 索引扫描
3. 获取不到数据
分析执行效率
重点关注信息
table: city ---->查询操作的表
possible_keys: CountryCode,idx_co_po ---->可能会走的索引
key: CountryCode ---->真正走的索引
type: ref ---->索引类型
Extra: Using index condition ---->额外信息
1.全表扫描
mysql> select * from world.city;
.....
| 4076 | Hebron | PSE | Hebron | 119401 |
| 4077 | Jabaliya | PSE | North Gaza | 113901 |
| 4078 | Nablus | PSE | Nablus | 100231 |
| 4079 | Rafah | PSE | Rafah | 92020 |
+------+------------------------------------+-------------+------------------------+
4079 rows in set (0.01 sec)
mysql> mysql> explain select * from world.city;
+----+-------------+-------+------+---------------+------+---------+------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| | | | | | | | | | | |
+----+-------------+-------+------+---------------+------+---------+------+------+------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+------+
1 row in set (0.01 sec)
2.索引扫描
从上到下，性能从最差到最好，我们认为至少要达到range级别
- index 全索引扫描
index与ALL区别为index类型只遍历索引树
mysql> select countrycode from world.city;
......
| ZWE |
| ZWE |
| ZWE |
| ZWE |
| ZWE |
+-------------+
4079 rows in set (0.01 sec)
mysql> explain select countrycode from world.city;
+----+-------------+-------+-------+---------------+-------------+---------+------+------
| id | select_type | table | type | possible_keys | key | key_len | ref |rows |
+----+-------------+-------+-------+---------------+-------------+---------+------+------
| 1 | SIMPLE | city | index | NULL | CountryCode | 3 | NULL | 4188|
+----+-------------+-------+-------+---------------+-------------+---------+------+------
+-------------+
| Extra |
+-------------+
| Using index |
+-------------+
#查找一个没有索引的数据 就算再精确 还是全表扫描
mysql> desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> explain select countrycode from world.city where district='shanghai';
+----+-------------+-------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 |
+----+-------------+-------+------+---------------+------+---------+------+------+
+-------------+
| Extra |
+-------------+
| Using where |
+-------------+
- range 索引范围扫描
对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
#有索引值才能用索引扫描 对没有索引的数据 先添加索引
mysql> alter table world.city add index idx_pop(population);
mysql> desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | MUL | 0 | |
+-------------+----------+------+-----+---------+----------------+
mysql> explain select countrycode from world.city where population>3000000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 46 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
#还是要看范围的 范围不在还是要全表扫描
mysql> explain select countrycode from world.city where population>3;
+----+-------------+-------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | |
+----+-------------+-------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | city | ALL | idx_pop | NULL | NULL | NULL | 4188 |
+----+-------------+-------+------+---------------+------+---------+------+------+
+-------------+
| Extra |
+-------------+
| Using where |
+-------------+
- ref
使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
mysql> explain select * from world.city where countrycode in ('chn','usa');
+----+-------------+-------+-------+---------------+-------------+---------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
+----+-------------+-------+-------+---------------+-------------+---------+------+
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL |
+----+-------------+-------+-------+---------------+-------------+---------+------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
mysql> explain select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';
+----+--------------+------------+------+---------------+-------------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
+----+--------------+------------+------+---------------+-------------+---------+-------+
| 1 | PRIMARY | city | ref | CountryCode | CountryCode | 3 | const |
| 2 | UNION | city | ref | CountryCode | CountryCode | 3 | const |
|NULL| UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL |
+----+--------------+------------+------+---------------+-------------+---------+-------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
| Using index condition |
| Using temporary |
+-----------------------+
- eq_ref连表查询(内连接)，并且等价条件是主键或者唯一键
类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
join B
on A.sid=B.sid
- const、system
当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量
mysql> explain select * from world.city where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
+----+-------------+-------+-------+---------------+---------+---------+-------+
| 1 | SIMPLE | city | const | PRIMARY | PRIMARY | 4 | const |
+----+-------------+-------+-------+---------------+---------+---------+-------+
+-------+
| Extra |
+-------+
| NULL |
+-------+
- NULL
MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
mysql> explain select countrycode from world.city where population>30000000000000000000000000000;
+----+-------------+-------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+
+-----------------------------------------------------+
| Extra |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+
Extra（扩展）
就是结果最后一个 告诉你问题在哪
一般是不可避免的 但是Using filesort可以解决 但是没啥意义
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer
如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上有没有索引
mysql> explain select * from world.city where countrycode='CHN' order by population;
+----+-------------+-------+------+---------------+-------------+---------+-------+-----
| id | select_type | table | type | possible_keys | key | key_len | ref |rows|
+----+-------------+-------+------+---------------+-------------+---------+-------+-----
| 1 | SIMPLE | city | ref | CountryCode | CountryCode | 3 | const | 363|
+----+-------------+-------+------+---------------+-------------+---------+-------+-----
+----------------------------------------------------+
| Extra |
+----------------------------------------------------+
| Using index condition; Using where; Using filesort |
+----------------------------------------------------+
当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现
mysql> explain select * from world.city where population>30000000 order by population;
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 1 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
mysql> select * from world.city where population=2870300 order by population;
+------+-------------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------------+-------------+----------+------------+
| 1899 | Nanking [Nanjing] | CHN | Jiangsu | 2870300 |
+------+-------------------+-------------+----------+------------+
注意：
key_len（查找前几个字符排序）: 越小越好 可以通过前缀索引控制
rows: 越小越好
索引建立规范（原则）
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
1.选择唯一索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
#判断能不能创建唯一索引
distinct()
count()
#总共9行 去重之后还有2行 说明重复了 不能创建
mysql> select count(name) from student4;
+-------------+
| count(name) |
+-------------+
| 9 |
+-------------+
mysql> select count(distinct(name)) from student4;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 2 |
+-----------------------+
#一共239行 去重之后还有239行 说明没重复的 可以创建
mysql> select count(name) from country;
+-------------+
| count(name) |
+-------------+
| 239 |
+-------------+
mysql> select count(distinct(name)) from country;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 239 |
+-----------------------+
2.联合索引
假设学生表没有办法创建唯一索引 除了学号为主键 之后的值都有可能会重复 这个时候可以创建联合索引
3.为经常需要排序、分组和联合操作的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。也是建立联合索引
注：如果经常作为条件的列，重复值特别多，可以建立联合索引
4.为常作为查询条件的字段建立索引
- 唯一索引
- 联合索引
- 普通索引
5．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
前缀索引 用字符串定义的才能用前缀索引
mysql> desc xiangqin;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | enum('0','1') | YES | MUL | NULL | |
| face | varchar(10) | YES | | NULL | |
| height | int(11) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
| hobby | varchar(10) | YES | | NULL | |
| QQ | varchar(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
mysql> alter table xiangqin add index idx_lianhe(face(3),weight(2),QQ(3));
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
mysql> alter table xiangqin add index idx_lianhe(face(3),hobby(2),QQ(3)));
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
6．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
------------------------------------------------------------ 我是华丽的分割线 ---------------------------------------------------
走索引和不走索引的情况
不走索引
1.全表扫描
mysql> select * from world.city;
mysql> explain select * from world.city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra|
+----+-------------+-------+------+---------------+------+---------+------+------+-------
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------
#优化
不要执行就好了
2.结果集太多了 查询结果集是原表中的大部分数据 应该是25％以上
mysql> select * from world.city where population >30000000;
Empty set (0.00 sec)
mysql> explain select * from world.city where population >30000000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 1 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
mysql> explain select * from world.city where population >300000;
+----+-------------+-------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | city | ALL | idx_pop | NULL | NULL | NULL | 4188 |
+----+-------------+-------+------+---------------+------+---------+------+------+
+-------------+
| Extra |
+-------------+
| Using where |
+-------------+
#优化
mysql> explain select * from world.city where population >300000 limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 1062 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
3.索引失效损坏
修改数据 插入数据什么的 频率太高
#优化
删除索引，重建索引
4.使用列名进行条件运算
mysql> explain select * from world.city where id=10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+
| 1 | SIMPLE | city | const | PRIMARY | PRIMARY | 4 | const | 1 |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+
+-------+
| Extra |
+-------+
| NULL |
+-------+
mysql> explain select * from world.city where id=10-1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+
| 1 | SIMPLE | city | const | PRIMARY | PRIMARY | 4 | const | 1 |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+
+-------+
| Extra |
+-------+
| NULL |
+-------+
mysql> explain select * from world.city where id-1=9;
+----+-------------+-------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 |
+----+-------------+-------+------+---------------+------+---------+------+------+
+-------------+
| Extra |
+-------------+
| Using where |
+-------------+
#优化
数学不要学的太好 该查啥就查啥
5.隐式转换
你给了char或者varchar字符串类型 搜索的时候没有按照字符串搜索 没有尊重他的隐式 那么就不走索引
mysql> create table yinshi(id int,QQ varchar(12));
mysql> insert into yinshi(id,QQ) values(1,110),(2,120)(3,130);
mysql> select * from yinshi;
+------+------+
| id | QQ |
+------+------+
| 1 | 110 |
| 2 | 120 |
| 3 | 130 |
+------+------+
mysql> alter table yinshi add index idx_qq(QQ);
mysql> explain select * from yinshi where QQ='110';
+----+-------------+--------+------+---------------+--------+---------+-------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+--------+------+---------------+--------+---------+-------+------+
| 1 | SIMPLE | yinshi | ref | idx_qq | idx_qq | 15 | const | 1 |
+----+-------------+--------+------+---------------+--------+---------+-------+------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
mysql> explain select * from yinshi where QQ=110;
+----+-------------+--------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+--------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | yinshi | ALL | idx_qq | NULL | NULL | NULL | 3 |
+----+-------------+--------+------+---------------+------+---------+------+------+
+-------------+
| Extra |
+-------------+
| Using where |
+-------------+
#优化
注意给的约束条件 是字符串就按字符串查询
mysql> alter table yinshi add phone int;
mysql> insert into yinshi(id,phone) values(3,140),(4,150);
mysql> alter table yinshi add index idx_phone(phone);
mysql> explain select * from yinshi where phone=140;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+
| 1 | SIMPLE | yinshi | ref | idx_phone | idx_phone | 5 | const | 1 |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+
+-------+
| Extra |
+-------+
| NULL |
+-------+
6.模糊查询like %在最前面的时候，不管结果集是多少
mysql> mysql> explain select * from world.city where countrycode like '%HN';
+----+-------------+-------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 |
+----+-------------+-------+------+---------------+------+---------+------+------+
+-------------+
| Extra |
+-------------+
| Using where |
+-------------+
mysql> explain select * from world.city where countrycode like 'H%';
+----+-------------+-------+-------+---------------+-------------+---------+------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows|
+----+-------------+-------+-------+---------------+-------------+---------+------+------
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL | 22|
+----+-------------+-------+-------+---------------+-------------+---------+------+------
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
#优化
建议，不使用MySQL，而是使用 `elasticsearch`
7.<> 、 not in不走索引
mysql> explain select * from world.city where population <> 102361;
+----+-------------+-------+------+---------------+------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+------+
| 1 | SIMPLE | city | ALL | idx_pop | NULL | NULL | NULL | 4188 |
+----+-------------+-------+------+---------------+------+---------+------+------+
+-------------+
| Extra |
+-------------+
| Using where |
+-------------+
#优化
mysql> mysql> explain select * from world.city where population <> 102361 limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 4076 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+
+-----------------------+
| Extra |
+-----------------------+
| Using index condition |
+-----------------------+
mysql> explain select * from world.city where population > 102361 union all select * from world.city where population < 102361;
+----+--------------+----------+-------+---------------+-------+---------+------+-------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+--------------+----------+-------+---------------+-------+---------+------+-------
| 1 | PRIMARY | city | ALL | idx_pop | NULL | NULL | NULL | 4188 |
| 2 | UNION | city | range | idx_pop | idx_pop | 4 | NULL | 625 |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL |
+----+--------------+------------+-------+---------------+---------+---------+------+---
+-----------------------+
| Extra |
+-----------------------+
| Using where |
| Using index condition |
| Using temporary |
+-----------------------+
#为什么一个是all 一个是range 因为第一条结果集太多了 所以显示all
8.联合索引，单独引用联合索引里非第一位置的索引列
按照创建索引的顺序，查询数据
explain(desc)使用场景（面试题）
题目意思: 我们公司业务慢,请你从数据库的角度分析原因
mysql出现性能问题,我总结有两种情况:
1.应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1) show processlist; 获取到导致数据库hang的语句
2) explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3) 建索引,改语句
2.一段时间慢(持续性的):
1) 记录慢日志slowlog,分析slowlog
2) explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3) 建索引,改语句
浙公网安备 33010602011771号