SQL执行计划获取及分析
介绍
(1)获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案(2) select语句获取数据的方法
1. 全表扫描(应当尽量避免,因为性能低)
2. 索引扫描
3. 获取不到数据
SQL执行计划获取
sql文件下载链接:
https://alnk-blog-pictures.oss-cn-shenzhen.aliyuncs.com/blog-pictures/world.sql
https://alnk-blog-pictures.oss-cn-shenzhen.aliyuncs.com/blog-pictures/t100w.txt导入数据库
mysql> source /root/world.sql
mysql> source /root/t100w.txt
获取优化器选择后的sql执行计划
mysql> use test
mysql> desc select * from t100w where id=9000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t100w | ALL | NULL | NULL | NULL | NULL | 997470 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)mysql> use test
mysql> explain select * from t100w where id=9000\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t100w# 查询的表
type: ALL# 查询类型
possible_keys: NULL# 可能走的索引
key: NULL# 走的索引名
key_len: NULL# 应用索引的长度
ref: NULL
rows: 997470# 查询结果集的长度
Extra: Using where # 额外信息
1 row in set (0.00 sec)
SQL执行计划分析
mysql> use world;
mysql> desc select * from city where countrycode = 'CHN'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: const
rows: 363
Extra: Using index condition
1 row in set (0.00 sec)重点关注的信息
table: city ---->查询操作的表 **
possible_keys: CountryCode---->可能会走的索引 **
key: CountryCode ---->真正走的索引 ***
type: ref ---->索引类型 *****
Extra: Using index condition ---->额外信息 *****type详解
从左到右性能依次变好: ALL --> INDEX -->RANGE -->ref --> eq_ref --> system,constALL:全表扫描,不走索引
1 查询条件列,没有索引
mysql> use test
mysql> show index from t100w;
# 结果没有索引
Empty set (0.00 sec)mysql> desc SELECT * FROM t100w WHERE k2='780P';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t100w | ALL | NULL | NULL | NULL | NULL | 997470 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)2 查询条件出现以下语句, (有辅助索引列)也不走索引
mysql> USE world
mysql> DESC city; #MUL :辅助索引(单列,联和,前缀)
+-------------+----------+------+-----+---------+----------------+
| 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 | |
+-------------+----------+------+-----+---------+----------------+mysql> DESC SELECT * FROM city WHERE countrycode <> 'CHN';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | CountryCode | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+mysql> DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | CountryCode | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+mysql> DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+注意:对于聚集索引列,使用以上语句,依然会走索引
mysql> DESC SELECT * FROM city WHERE id <> 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | city | range | PRIMARY | PRIMARY | 4 | NULL | 2103 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+INDEX:全索引扫描
1. 查询需要获取整个索引树种的值时
mysql> use world
mysql> DESC SELECT countrycode FROM city;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | city | index | NULL | CountryCode | 3 | NULL | 4188 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)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
主键
<> NOT INmysql> DESC SELECT * FROM city WHERE id<5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | city | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql> DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL | 397 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)mysql> DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL | 637 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)注意:
1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的.
所以,我们可以将3号列子改写:
mysql> DESC SELECT * FROM city WHERE countrycode='CHN'
-> UNION ALL
-> SELECT * FROM city WHERE countrycode='USA';
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
| 1 | PRIMARY | city | ref | CountryCode | CountryCode | 3 | const | 363 | Using index condition |
| 2 | UNION | city | ref | CountryCode | CountryCode | 3 | const | 274 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
3 rows in set (0.00 sec)
ref: 非唯一性索引,等值查询
mysql> DESC SELECT * FROM city WHERE countrycode='CHN';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
| 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 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
eq_ref: 在多表连接时,连接条件使用了唯一索引(uk pK)
mysql> DESC SELECT b.name,a.name FROM city AS a
-> JOIN country AS b
-> ON a.countrycode=b.code
-> WHERE a.population <100;
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 239 | NULL |
| 1 | SIMPLE | a | ref | CountryCode | CountryCode | 3 | world.b.Code | 9 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
2 rows in set (0.00 sec)
system,const :唯一索引的等值查询
mysql> DESC SELECT * FROM city WHERE id=10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | city | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
其他字段解释
extra字段:
Using filesort: 文件排序 出现这个说明需要排序，会影响查询速度mysql> SHOW INDEX FROM city\G;
*************************** 1. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 4188
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: city
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 465
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:# 创建单列普通索引（未解决问题，还是有Using filesort）
mysql> ALTER TABLE city ADD INDEX CountryCode(CountryCode);
mysql> ALTER TABLE city DROP INDEX idx_c_p;
mysql> DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: const
rows: 363
Extra: Using index condition; Using where; Using filesort# 创建单列普通索引（未解决问题，还是有Using filesort）
mysql> ALTER TABLE city ADD INDEX idx_(population);
mysql> DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: const
rows: 363
Extra: Using index condition; Using where; Using filesort# 创建联合索引（问题解决）
mysql> ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
mysql> ALTER TABLE city DROP INDEX idx_;
mysql> ALTER TABLE city DROP INDEX CountryCode;
mysql> DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: idx_c_p
key: idx_c_p
key_len: 3
ref: const
rows: 363
Extra: Using index condition; Using where
# 此时这里的 Using filesort 排序已经不存在了，查询数据会变快 结论:
1.当我们看到执行计划extra位置出现filesort,说明有文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT)的条件,有没有索引
3.根据子句的执行顺序,去创建联合索引
索引优化效果测试
优化前:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from test.t100w where k2='780P'" engine=innodb \
> --number-of-queries=2000 -uroot -proot123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 701.743 seconds
Minimum number of seconds to run all queries: 701.743 seconds
Maximum number of seconds to run all queries: 701.743 seconds
Number of clients running queries: 100
Average number of queries per client: 20优化后:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -proot123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 0.190 seconds
Minimum number of seconds to run all queries: 0.190 seconds
Maximum number of seconds to run all queries: 0.190 seconds
Number of clients running queries: 100
Average number of queries per client: 20
联合索引创建注意事项
1.SELECT * FROM t1 WHERE a= b=
我们建立联合索引时:
ALTER TABLE t1 ADD INDEX idx_a_b(a,b);
ALTER TABLE t1 ADD INDEX idx_b_a(b,a);
以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序
注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边.2.如果有where条件中出现不等值查询条件
mysql> use test;
mysql> DESC SELECT * FROM t100w WHERE num <1000 AND k2='DEEF';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t100w | ALL | NULL | NULL | NULL | NULL | 997470 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+我们建索引时:
mysql> ALTER TABLE t100w ADD INDEX idx_2_n(k2,num);
语句书写时
mysql> DESC SELECT * FROM t100w WHERE k2='DEEF' AND num <1000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| 1 | SIMPLE | t100w | range | idx_2_n | idx_2_n | 22 | NULL | 2 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.12 sec)3. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引
explain(desc)使用场景
公司业务慢,请你从数据库的角度分析原因1.mysql出现性能问题,总结有两种情况
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist获取到导致数据库hang的语句。然后kill ID杀掉这条语句的进程
2.explain分析SQL的执行计划,有没有走索引,索引的类型情况
3.建索引,改语句（2）一段时间慢(持续性的)
1.记录慢日志slowlog,分析slowlog
2.explain分析SQL的执行计划,有没有走索引,索引的类型情况
3.建索引,改语句
