Mysql索引优化分析

累计阅读量: 440

explain查看执行计划

2.1MySql Query Optimizer

MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）

2.2. Explain

2.2.1.是什么

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈.

2.2.2.能做啥

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

每张表有多少行被优化器查询

2.2.3.怎么玩

使用Explain+SQL语句

2.3.EXPLAIN输出项

来源于官方文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

2.4. explain各字段解释

2.4.1表环境和数据准备

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));

 INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); 
 INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));  
 INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));    
 INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

1、id★

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。

存在三种情况：

id相同，执行顺序由上至下

Explain select * from t1,t2,t3;

id不同：如果是子查询id的序号会递增,id值越大优先级越高,越先被执行

explain select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));

结论：

id相同，不同

id如果相同，可以认为是一组，从上往下顺序执行；

id不同，id值越大，优先级越高，越先执行;

关注点：id号每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好。

2.select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

有哪些：

1) SIMPLE

简单的 select 查询,查询中不包含子查询或者UNION

EXPLAIN SELECT * FROM t1;

2）PRIMARY

查询中若包含任何复杂的子查询部分，最外层查询则被标记为primary

EXPLAIN select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));

3)SUBQUERY

在SELECT或WHERE列表中包含了子查询

#在where后
EXPLAIN select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));

#在select后
explain SELECT t1.id, (SELECT t2.id from t2  )from t1;

4)DEPENDENT SUBQUERY

在SELECT或WHERE列表中包含了子查询,子查询基于外层

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);

5) UNCACHEABLE SUBQUREY【当前子查询】

表示这个subquery的查询要受到外部系统变量的影响

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);

6)UNION

若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
```
EXPLAIN SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) aa; 
```

7)UNION RESULT

从UNION表获取结果的临时存储表

EXPLAIN SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) aa;

3. table

显示这一行的数据是关于哪张表的

4. partitions

代表分区表中的命中情况，非分区表，该项为null

说白了，意思是指将同一表中不同行的记录分配到不同的物理文件中。而说白了几个分区就有几个.idb文件。

5. type★

5.1.访问类型排列

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const> eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

5.2 类型介绍

5.2.1.System

表仅有一行记录，必须是系统表,查询起来非常迅速。

explain SELECT * from mysql.proxies_priv WHERE `User`='root';

explain SELECT * from mysql.proc where db='sys';

5.2.2.const

该表最多有一个匹配的行，在查询开始时读取。因为只有一行，所以优化器的其他部分可以将这一行中的列的值视为常量。

explain select * from t1 where id = 1;

表示通过索引一次就找到了,const用于primary key或者unique索引。
因为只匹配一行数据，所以很快.。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

5.2.3.eq_ref

对于每个索引键，表中只有一条记录与之匹配。简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

explain select * from t1,t2 where t1.id = t2.id;

5.2.4.ref *

非唯一性索引扫描，返回匹配某个值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，但是它可能会找到多个符合条件的行。

举个例子来说明这两个区别：

先说eq_ref:

表t1包含列（id,context）,其中id是主键。表t2具有相同的列（id,content）,其中id是主键.

表A包含以下数据；

1 263

2 857

表B包含以下数据

1 334

2 665

而eq_ref为t1 t2之间的join:

select * from t1,t2 where t1.id=t2.id;

因为对于表t1中扫描的每一行，表t2中只能有一行满足。**因为t2.id是独一无二的。 **

再谈ref:

对于上面一张表t1, 给content建立索引但非UNQIE

1 263

2 263

那么ref为t1 t2之间的join:

select * from t1,t2 where t1.content=t2.content

这个join就没有eq_ref的快，因为对于t1中扫描的的每一行，t2表中有几个可能的行与之匹配，因为t2.content不是唯一的。

alter  table  t2  add index idx_content(content) 

EXPLAIN SELECT * FROM t1, t2 WHERE t1.content = t2.content;

5.2.5range *

只检索给定范围的行，使用一个索引来选择指定范围行

explain select * from t2 where id >1 and id <5;

5.2.6.index

出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引

index与ALL区别为index类型只遍历索引树。

这通常比ALL快，因为索引文件通常比数据文件小。

也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从磁盘中读。

explain select id from t1;

5.2.7.all

Full Table Scan，将遍历全表以找到匹配的行

explain select * f  rom t1;

6. possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

7. key

实际使用到的索引，如果为null，则没有使用索引,查询中如果使用了覆盖索引，则该索引仅出现在key列表中。

 explain   select t1.id  from t1;

8. key_len ★

key_len表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。值越大越好。

给mydb库中的t_tmp表建立复合索引

create index idx_age_name on t_emp (age,name);

#sql1:
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30 AND t_emp.name LIKE 'ab%'; 
#sql2:
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30;

第一组：

key_length=name的字节长度 + age的字节长度=63+5=68

name字节长度=20*3+1+2；

age字节长度=4+1

第二组:

key_length=name字节的长度=5

常见的类型例子说明：

key_len的长度计算公式:

varchr(10):变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

9. ref

显示将哪些列或常量与键列中命名的索引进行比较，以从表中选择行。

说白了一句话 索引被用于哪一列。

explain select * from t1,t2 where t1.id = t2.id;

10.rows

rows列显示MySQL认为它执行查询时必须检查的行数。值越小越好

11.filtered

这个字段表示存储引擎返回的数据在mysql server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

12.Extra★

包含不适合在其他列中显示,但十分重要的额外信息

12.1. Using filesort *

如果出现Using filesort 说明mysql会对数据使用一个内部进行排序，而不是按照表内的索引顺序进行读取 ，那么MySQL中无法利用索引完成的排序操作称为“文件排序”。

另外创建表t_student，并指定索引

create table t_student(id int not null PRIMARY key , age int, height int, name varchar(10),index idx_age(age), index idx_height(height));

插入10条数据

INSERT into student values(1,10,170,'A1');
INSERT into student values(2,20,160,'A2');
INSERT into student values(3,22,166,'A3');
INSERT into student values(4,25,165,'A4');
INSERT into student values(5,25,169,'A5');
INSERT into student values(6,22,170,'A6');
INSERT into student values(7,30,170,'A7');
INSERT into student values(8,40,174,'A8');
INSERT into student values(9,50,172,'A9');
INSERT into student values(10,50,177,'A10');

explain  select * from  t_student  where age=20 order by height;

如何去掉filesort排序？

优化：

给age和height字段建立复合索引

create index idx_age_height on t_student(age,height);

explain  select * from  t_student  where age=20 order by height;

完美，去掉using filesort

过程：因为建立了（age,height）的复合索引，那么也即age,height是排好序的，并且当在age相等下，height也一定是被顺序组织好的。因此直接根据在复合索引树上根据主键值回表到主键索引上找到相应的数据记录。不需要在进行mysql内部在进行排序了。

小总结：

当Where 条件和 order by 子句作用在不同的列上，建立联合索引可以避免Using filesort的产生。

12.2.Using temporary

如果出现了Using temporary情况：分组没有用上索引。产生临时表。注意：分组操作是需要先排序后分组的。所以，也会出现Using filesort。

explain SELECT age,count(*) from t_student where height=165 GROUP BY age;

如何去掉using temporary?

优化：

给height和age字段建立复合索引

create  index idx_height_age on t_student(height,age);

explain SELECT age,count(*) from t_student where height=165 GROUP BY age;

12.3.Using index

表示使用了覆盖索引 [age是一个索引]

explain select age from t_student where age=20;

因为我们height也是一个索引。那么它也满足

 explain  select height from t_student  where height=170;

当然还有一种情况 Using index 和 Using where同时存在

为了演示效果方便

删除t_student中的idx_age、idx_height索引。

#创建age、height的复合索引
create index idx_age_height on t_student(age,name)

explain select  age,height from t_student where height=170;

思考为什么出现了using where?

12.4.Using where

注意：当出现using where时表示条件是由Mysql server层过滤的，而不是存储引擎层过滤的。

#删除复合索引
drop  index idx_age_height on t_student
#分别建立单个索引
create index idx_age on t_student(age);
create  index idx_height on t_student(height);

explain  select * from t_student where age=20 and name='A10';

分析：因为age是索引字段，name不是索引字段可以看到这里也使用了这个索引。首先在age这颗索引树上找到age=20,然后在根据主键值回表到主键索引树上找到记录然后再次过滤。在这个过程中回表查询记录之后进行的过滤就不是于存储引擎做的了，而是Mysql Server做的过滤。

比如下面这个就用不到using where

explain select * from t_student where id=1 and name='A1'\G;

总结：并不是带了where条件就一定会出现using where。而是主要看过滤条件是存储引擎完成的，还是Mysql Server完成的。

灵犀壹指

最新文章

概要

爪哇国和他的伙伴们 · 2022年 10月 5日 0

2.1MySql Query Optimizer

2.2. Explain

2.2.1.是什么

2.2.2.能做啥

2.2.3.怎么玩

2.3.EXPLAIN输出项

2.4. explain各字段解释

2.4.1表环境和数据准备

1、id★

2.select_type

3. table

4. partitions

5. type★

5.2.1.System

5.2.2.const

5.2.3.eq_ref

5.2.4.ref *

5.2.5range *

5.2.6.index

5.2.7.all

6. possible_keys

7. key

8. key_len ★

9. ref

10.rows

11.filtered

12.Extra★

12.1. Using filesort *

12.2.Using temporary

12.3.Using index

12.4.Using where

您可能还喜欢...

发表回复取消回复

2025 年 5 月
一	二	三	四	五	六	日
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

爪哇国和他的伙伴们 · 2022年 10月 5日 0

2.1MySql Query Optimizer

2.2. Explain

2.2.1.是什么

2.2.2.能做啥

2.2.3.怎么玩

2.3.EXPLAIN输出项

2.4. explain各字段解释

2.4.1表环境和数据准备

1、id★

2.select_type

3. table

4. partitions

5. type★

5.2.1.System

5.2.2.const

5.2.3.eq_ref

5.2.4.ref *

**5.2.5range ***

5.2.6.index

5.2.7.all

6. possible_keys

7. key

8. key_len ★

9. ref

10.rows

11.filtered

12.Extra★

12.1. Using filesort *

12.2.Using temporary

12.3.Using index

12.4.Using where

您可能还喜欢...

【【B站最系统的Java教程】整整400集，全程干货无废话，学完变大佬！看完学不会，我退出IT圈！】

灯箱测试

Mybatis

发表回复 取消回复

5.2.5range *

发表回复取消回复