一、索引使用场景
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引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,索引失效要少用