命令记不住

登陆

sql 执行计划分析

  |   129 浏览

sql执行计划

执行计划获取及分析

1. 执行计划

1.获取到的是优化器选择完成的,他认为代价最小的执行计划
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.

2.select 获取数据的方法

    1. 全表扫描(应当尽量避免,因为性能低)
    1. 索引扫描
    1. 获取不到数据

2.执行计划获取

获取优化器选择后的执行计划

db01 [world]>desc select * from city where countrycode='CHN'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ref
possible_keys: CountryCode,idx_co_po
          key: CountryCode
      key_len: 3
          ref: const
         rows: 363
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

3.执行计划分析

1.重点关注的信息

table: city                               ---->查询操作的表    **
possible_keys: CountryCode,idx_co_po      ---->可能会走的索引  **
key: CountryCode						  ---->真正走的索引    ***
type: ref								  ---->索引类型        *****
Extra: Using index condition              ---->额外信息        *****

2.type详解

从左到右性能依次变好.

ALL,index,range,ref,eq_ref,system(const),NULL

(1) ALL: 代表的是全表扫描

desc select * from city;
desc select * from city where name like '%C%';
desc select * from city where name != 'CHN';
desc select * from city where countrycode not in ('CHN','USA');

注意：生产中几乎是没有这种需求的。尽量避免

(2) index: 全索引扫描
需要扫描整个索引树,获取到想要数据,比ALL性能好,顺序IO,可以减少回表查询

db01 [world]>desc select name from city;

(3) range : 索引范围查询

>  <  >= <= 
in 
or 
like 'CH%'
between and 
db01 [world]>desc select * from city where id<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

db01 [world]>desc select * from city where countrycode in ('CHN','USA');
+----+-------------+-------+------------+-------+-----------------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys         | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode,idx_co_po | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


db01 [world]>desc select * from city where countrycode like 'CH%';
+----+-------------+-------+------------+-------+-----------------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys         | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode,idx_co_po | CountryCode | 3       | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

B+树额外优化:

> < >= <= 
between and 
like 'CH%'

in or无法享受B+树的额外优化,可以用union all来替代

(4) ref: 辅助索引的等值查询

db01 [world]>desc select * from city where countrycode = 'CHN';
+----+-------------+-------+------------+------+-----------------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys         | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode,idx_co_po | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------+-------------+---------+-------+------+----------+-------+

db01 [world]>desc select * from city where countrycode in ('CHN','USA');
db01 [world]>desc select * from city where countrycode='CHN'
    -> union all
    -> select * from city where countrycode='USA';

(5) eq_ref :多表连接的表,On的条件是主键或唯一键
(6) system 或 const :主键或唯一键的等值查询

db01 [world]>desc select * from city where id=10;

(7) NULL , 索引中扫描不到这个数据

db01 [world]>desc select * from city where id=5000;

结论：在索引扫描类型方面，至少保证在range以上级别。

3.其他字段解释：

  • Extra:
  • Using filesort
desc select * from city where countrycode='CHN' order by population desc limit 10;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | city  | ref  | CountryCode   | CountryCode | 3       | const |  363 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------

db01 [world]>alter table city add index idx_po(countrycode,population);
db01 [world]>desc select * from city where countrycode='CHN' order by population limit 10;

解决思路：

  • 索引可以减少排序,可以很大程度减少CPU时间
  • 辅助索引 应用顺序(优化器选择的)
  • 如果查询条件:符合覆盖索引的顺序时,优先选择覆盖索引
  • 不符合顺序,优先会走where条件的索引

优化方法:
将where列和order列建立联合索引

alter table city add index idx_co_po(countrycode,population);

4.explain使用场景

业务慢,请你从数据库的角度分析原因
mysql出现性能问题,我总结有两种情况:

(1)应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:

  • show processlist; 获取到导致数据库hang的语句
  • explain 分析SQL的执行计划,有没有走索引,索引的类型情况
  • 建索引,改语句

(2)一段时间慢(持续性的):

  • 记录慢日志slowlog,分析slowlog
  • explain 分析SQL的执行计划,有没有走索引,索引的类型情况
  • 建索引,改语句
推荐阅读