Mysql执行计划1-执行计划体系与执行计划字段详解

• 基于Centos7.6

1.作用

将优化器选择后的执行计划截取出来，便于管理员判断语句的执行效率。

语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题。
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。

2.获取执行计划

方法有两种:
desc+SQL语句
explain+SQL语句

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

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

#desc与explain在获取执行计划方面等效
#语句放在desc与explain后面，并不执行该语句，只是获取该语句的执行计划，对性能没有影响

3.分析执行计划

1>字段分析

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

2>type详解

查询的类型：全表扫描和索引扫描
全盘扫描查询低效，性能差,不走索引。

如果是全表扫描type字段显示:ALL(见上图)
如果是索引扫描type字段显示:INDEX、RANGE、REF、EQ_REF、CONST(SYSTEM)、NULL			#从左到右性能依次变好

常见使用场景：
ALL:
	select * from t1;
	select * from t1 where xxx;		#where条件无索引
	select * from t1 where != not in like '%xxx%';
	
INDEX:
	select id from city;
	
RANGE:
	> ,< ,>= ,<= ,like ,in ,or

eq_ref:多表连接

const:主键和唯一键的等值查询



<1>.INDEX：全索引扫描

    1>>. 查询需要获取整个索引树种的值时:
		 mysql> desc student;
		 +-------+---------------------+------+-----+---------+----------------+
		 | Field | Type                | Null | Key | Default | Extra          |
		 +-------+---------------------+------+-----+---------+----------------+
		 | sno   | int(11)             | NO   | PRI | NULL    | auto_increment |
		 | sname | varchar(20)         | NO   |     | NULL    |                |
		 | sage  | tinyint(3) unsigned | NO   |     | NULL    |                |
		 | ssex  | enum('f','m')       | NO   |     | m       |                |
		 +-------+---------------------+------+-----+---------+----------------+
		 4 rows in set (0.00 sec)

		 mysql> desc select sno from student;
		 +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
		 | id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
		 +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
		 |  1 | SIMPLE      | student | NULL       | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | Using index |
		 +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
		 1 row in set, 1 warning (0.00 sec)

    2>>. 联合索引中,任何一个非最左列作为查询条件时:
		 idx_a_b_c(a,b,c)  ---> a  ab  abc

		 SELECT * FROM t1 WHERE b 
		 SELECT * FROM t1 WHERE c   


<2>.RANGE :索引范围扫描 

	辅助索引> < >= <= LIKE IN OR 
	主键 <>  NOT IN
	对于辅助索引来讲，!=和not in等语句是不走索引的
	对于主键列来讲，!=和not in等语句是走RANGE

	例子:
	1. 
	DESC SELECT * FROM city WHERE id<5;
	2. 
	DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
	3. 
	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';


<3>.ref: 辅助索引等值查询
	非唯一性索引,等值查询
	DESC SELECT * FROM city WHERE countrycode='CHN';


<4>.eq_ref:在多表连接时，子表使用主键列或唯一列作为连接条件
	例子：
	a join b
	驱动表：a	子表：b

	在多表连接时,连接条件使用了唯一索引(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;


<5>.system,const :主键或唯一键的等值查询
	唯一索引的等值查询
	DESC SELECT * FROM city WHERE id=10;
	
	
<6>.null :所查数据在表内没有，索引里也没有，因为没有值所以不用遍历，性能也最好
	例子：
	stu表内tel列没有160这个值
	
	mysql> select * from stu;
	+----+-------+------+---------+-----+---------------------+-----+
	| id | sname | sage | sgender | sfz | intime              | tel |
	+----+-------+------+---------+-----+---------------------+-----+
	|  1 | zs1   |    0 | n       | 1   | 2021-09-26 14:07:35 | 110 |
	|  2 | zs1   |    0 | n       | 2   | 2021-09-26 14:07:42 | 120 |
	|  3 | zs1   |    0 | n       | 3   | 2021-09-26 14:07:48 | 130 |
	+----+-------+------+---------+-----+---------------------+-----+
	3 rows in set (0.00 sec)

	mysql> desc select * from stu where tel='160';
	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
	|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
	1 row in set, 1 warning (0.00 sec)

3>其他类型详解

table：表名

extra: 
filesort ,文件排序.
SHOW INDEX FROM city;
ALTER TABLE city ADD INDEX CountryCode(CountryCode);
ALTER TABLE city DROP INDEX idx_c_p;

DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 

ALTER TABLE city ADD INDEX idx_(population);
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 
ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
ALTER TABLE city DROP INDEX idx_;
ALTER TABLE city DROP INDEX CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 

结论: 
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引

索引优化效果测试:
优化前:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t_100w where k2='780P'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -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='oldboy' --query="select * from oldboy.t_100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -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 条件中出现不等值查询条件
DESC  SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF';
我们建索引时:
ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num);
语句书写时
DESC  SELECT * FROM t_100w WHERE  k2='DEEF'  AND  num <1000 ;
3. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.