MySQL（2）索引分析（Explain查看执行计划）
一、索引使用场景哪些情况需要创建索引主键自动建立唯一索引频繁作为查询条件的字段应该创建索引where多表关联查询中，关键字段应该穿啊建索引on两边都要创建索引select *from user left join order on user.id = order.userid查询中排序的字段，应该创建索引B+ tree 有顺序覆盖索引 好处是？不需要回表组合索引select *...
一、索引使用场景
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引where
- 多表关联查询中，关键字段应该创建索引on两边都要创建索引
select *from user left join order on user.id = order.userid - 查询中排序的字段，应该创建索引B+ tree 有顺序
- 覆盖索引 好处是？
不需要回表组合索引
select *from user------全表扫描，没有使用索引
select name，age from user ------索引覆盖，不需要回表。 - 统计或者分组字段，应该创建索引
哪些情况不需要创建索引
- 表记录太少 ， 索引是要有存储的开销
- 频繁更新 ， 索引要维护
- 查询字段使用频率不高
为什么使用组合索引
由多个字段组成的索引 使用顺序就是创建的顺序
只要是组合索引（除了联合主键），都是次要索引
ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3）
在一颗索引树上由多个字段
好处：
- 建一个索引树，其实相当于创建了多颗索引树
- 可以优化排序
- 可以使用覆盖索引优化sql查询
使用：遵循最左前缀原则
- 前缀索引
like 常量% 使用索引 like %常量 不使用索引 - 最左前缀
从左向右匹配直到遇到范围查询 > < between 索引失效
二、Explain查看执行计划
查看执行计划
建表语句
create table tuser(
id int primary key,
name varchar(100),
age int,
sex char(1),
address varchar(100)
);
alter table tuser add index idx_name_age(name(100),age);
alter table tuser add index idx_sex(sex(1));
insert into tuser(id,name,age,sex,address) values (1,'zhangsan',20,'0','致真 大厦');
-
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.
-
使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。
-
可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
参数说明
例如：
mysql> explain select * from user where id = 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL 1
row in set, 1 warning (0.00 sec)
| 参数 | 说明 |
|---|---|
| id: | SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符. |
| select_type： | SELECT 查询的类型. |
| table: | 查询的是哪个表 |
| partitions: | 匹配的分区 |
| type: | join 类型 |
| possible_keys: | 此次查询中可能选用的索引 |
| key: | 此次查询中确切使用到的索引. |
| key_len: | 索引长度 |
| ref: | 哪个字段或常数与 key 一起被使用 |
| rows: | 显示此查询一共扫描了多少行. 这个是一个估计值. |
| filtered: | 表示此查询条件所过滤的数据的百分比 |
| Extra: | 额外的信息 |
id
每个单位查询的SELECT语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序，有四种情况：
- id相同：执行顺序由上到下
- id不同：如果是子查询，id号会自增，id越大，优先级越高。
- id相同的不同的同时存在
- id列为null的就表示这是一个结果集，不需要使用它来进行查询。
select_type（重要）
单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。
simple
表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个
EXPLAIN SELECT * FROM tuser
primary
一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个
EXPLAIN SELECT (SELECT NAME FROM tuser)FROM tuser
union
union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
EXPLAIN SELECT * FROM tuser a UNION SELECT *FROM tuser b
EXPLAIN SELECT * FROM (SELECT * FROM tuser a UNION SELECT *FROM tuser b)c
dependent union
与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
EXPLAIN SELECT * FROM tuser u WHERE u.id IN (SELECT id FROM tuser a UNION SELECT id FROM tuser b)
union result
包含union的结果集，在union和union all语句中，因为他不需要参与查询，所以id字段为null
EXPLAIN SELECT * FROM tuser a UNION SELECT *FROM tuser b
subquery
除了from 字句中包含的子查询外，其他地方出现的子查询都可能是subquery
EXPLAIN SELECT (SELECT `name` FROM tuser)FROM tuser
dependent subquery
与dependent union类似，表示这个subquery的查询要受到外部查询的影响
EXPLAIN SELECT (SELECT `name` FROM tuser a WHERE a.id = b.id)FROM tuser b
derived
from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
EXPLAIN SELECT *FROM (SELECT *FROM tuser) t
table
显示的单位查询的表名，有如下几种情况：
- 如果查询使用了别名，那么这里显示的是别名
- 如果不涉及数据表的操作，那么这显示为null
- 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生
- 如果是尖括号括起来的<union.M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M，N的结果集。
type
显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：
system
const
eq_ref
ref
fulltext
ref_or_null
unique_subquery
index_subquery
range
index_merge
index
ALL
注意事项：
除了all之外，其他的type都可以使用到索引
除了index_merge之外，其他的type只可以用到一个索引
最少要使用到range级别
system
表中只有一行数据或者空表
EXPLAIN SELECT * FROM (SELECT *FROM tuser WHERE id = 1)t
const（重要）
使用唯一索引或者主键，返回记录一定是一行记录的等值where条件时，通常type是const。其他数据库也叫唯一索引扫描。
EXPLAIN SELECT * FROM tuser WHERE id = 1
eq_ref（重要）
- 多表关联
- 等值连接
- 等值连接的两个表的列是唯一索引列或者主键列
此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 = , 查询效率较高
EXPLAIN SELECT * FROM tuser a LEFT JOIN tuser b ON a.id=b.id
ref（重要）
- 多表关联
- 等值连接
- 等值连接的两个表的列是非唯一索引列
针对非唯一新索引，使用等值（=）查询。或者是使用最左前缀规则索引的查询
- 组合索引
EXPLAIN SELECT * FROM tuser a LEFT JOIN tuser b ON a.name=b.name
EXPLAIN SELECT * FROM tuser WHERE `name`='zhangsan'
- 非唯一索引
EXPLAIN SELECT * FROM tuser WHERE sex = '1'
fulltext
全文搜索引擎，要注意，全文搜索的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择权使用全文搜索
ref_or_null
与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery
用于where中的in形式子查询，子查询返回不重复唯一值
index_subquery
用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range（重要）
索引范围扫描，常见于使用>,<,is，null，between，in，like等运算符的查询中。
EXPLAIN SELECT * FROM tuser WHERE `name` LIKE'a%'
EXPLAIN SELECT * FROM tuser WHERE id > 1
index_marge
表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
index(重要）
select 结果列中使用到了索引，type会显示为index。
全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
EXPLAIN SELECT `name` FROM tuser
EXPLAIN SELECT name FROM tuser
all（重要）
这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
EXPLAIN SELECT * FROM tuser
EXPLAIN SELECT * FROM tuser WHERE address='致真大厦'
possible_keys
此次查询中可能选用的索引，一个或多个
key
查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
key_len
- 用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。
- 留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。
- 另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len中。
ref
- 如果是使用的常数等值查询，这里会显示const
- 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
- 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
rows
这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里面使用了MVCC并发机制）
extra(重要)
这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有
using filesort（重要）
- 排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
- 说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。
- MySQL中无法利用索引完成的排序操作称为“文件排序”
EXPLAIN SELECT * FROM tuser ORDER BY address;
using index（重要）
查询时不需要回表查询，直接通过索引就可以获取查询的数据。
- 表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错！
- 如果同时出现Using Where ，说明索引被用来执行查找索引键值
- 如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作
EXPLAIN SELECT `name`,age FROM tuser ;
using index condition(重要)
- Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用WHERE 子句中的其他条件去过滤这些数据行；
- 因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP（IndexCondition Pushdown，索引下推）其实就是实现了index filter技术，将原来的在server层进行的
- table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter。
- Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种 优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
using where（重要）
表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
- 查询条件无索引
EXPLAIN SELECT * FROM tuser WHERE address='beijing';
- 索引失效
EXPLAIN SELECT * FROM tuser WHERE age=1;
EXPLAIN SELECT * FROM tuser WHERE id IN(1,2);
三、索引失效分析
建表语句
CREATE TABLE tuser2(
id INT PRIMARY KEY,
NAME VARCHAR(100),
age INT,
sex CHAR(1),
address VARCHAR(100)
);
ALTER TABLE tuser ADD INDEX idx_name_age_sex(NAME(100),age,sex);
1.全值匹配
EXPLAIN SELECT * FROM tuser WHERE NAME='zhaoyun' AND age=1 AND sex='1';
2.最佳左前缀法则
组合索引
带头索引不能死，中间索引不能断
如果索引了多个列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且不跳过索引中的列。
- 带头索引死：
EXPLAIN SELECT * FROM tuser2 WHERE age=23;
- 中间索引断（带头索引生效，其他索引失效）：
explain select * from tuser where name='aa' and sex='1';
EXPLAIN SELECT * FROM tuser2 WHERE NAME='aa' AND sex=1 AND age=23;
- 对比
EXPLAIN SELECT * FROM tuser2 WHERE NAME='aa' AND sex='1' AND age=23;
- =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.不要在索引上做计算
EXPLAIN SELECT * FROM tuser2 WHERE `name`='zy';
EXPLAIN SELECT * FROM tuser2 WHERE LEFT(NAME,1)='zy';
4.范围条件右边的列失效
EXPLAIN SELECT * FROM tuser2 WHERE NAME='asd' AND age>20 AND sex='1';
5.尽量使用覆盖索引
EXPLAIN SELECT * FROM tuser2 ;
EXPLAIN SELECT `name`,age FROM tuser2 ;
EXPLAIN SELECT sex FROM tuser ;
6.索引字段上不要使用不等
- 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描 注：主键索引会使用范围索引，辅助索引会失效
EXPLAIN SELECT * FROM tuser2 WHERE `name`='zhy';
EXPLAIN SELECT * FROM tuser2 WHERE `name`!='zhy';
7.主键索引字段上不可以判断null
- 主键字段上不可以使用 null
索引字段上使用 is null 判断时，可使用索引
EXPLAIN SELECT * FROM tuser2 WHERE NAME IS NULL;
EXPLAIN SELECT * FROM tuser WHERE id IS NULL;
8.索引字段使用like不以通配符开头
- 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM tuser2 WHERE NAME LIKE 'a%';
EXPLAIN SELECT * FROM tuser2 WHERE NAME LIKE '%a';
-
由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、>、in等）不同的是：不会导致右边的索引失效。
-
问题：解决like ‘%字符串%’时，索引失效问题的方法？
使用覆盖索引可以解决。
EXPLAIN SELECT NAME , age,sex FROM tuser2 WHERE NAME LIKE '%a%';
9.索引字段字符串要加单引号
- 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM tuser2 WHERE NAME=123;
10.索引字段不要使用or
- 非主键索引字段使用 or 时，会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM tuser2 WHERE NAME='asd' OR age=23;
- 主键索引字段使用 or 时，会使用range
EXPLAIN SELECT * FROM tuser2 WHERE id=1 OR id=2;
总结
假设index（a，b，c）
| where语句 | 索引是否被使用 |
|---|---|
| where a = 3 | Y，使用到a |
| where a = 3 and b = 5 | Y，使用到a，b |
| where a = 3 and b = 5 and c = 4 | Y，使用到a，b，c |
| where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
| where a = 3 and c = 5 | 使用到a，但是c不可以，b中间断了 |
| where a = 3 and b > 4 and c = 5 | 使用到a和b,c不能用在范围之后，b断了 |
| where a = 3 and b like ‘kk%’ and c = 4 | Y，使用到a，b，c |
| where a = 3 and b like ‘%kk’ and c = 4 | Y，只用到a |
| where a = 3 and b like ‘%kk%’ and c = 4 | Y，只用到a |
| where a = 3 and b like ‘k%kk%’ and c = 4 | Y，使用到a，b，c |
- 优化总结口诀
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE百分写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
鸿蒙生态一站式服务平台。
更多推荐
2
0- 0
已为社区贡献1条内容
所有评论(0)