MYSQL--索引优化

EXPLAIN字段

id

id相同执行顺序从上到下
id不同 id越大 优先级越高 越先被执行

select_type

查询的类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。
SIMPLE：简单的select查询 查询中不包含子查询或者union
PRIMARY:查询中若包含复杂的子部分 最外层查询则被标记为PRIMARY 最后执行
DERIVER：在FROM列表中包含的子查询被标记为DERIVERD（派生）MYSQL递归执行子查询 结果防在临时表
SUBQUERY：在select或where中包含了子查询
UNION:若第二个select出现在union之后 则被标记为union 若union包含在from子句的子查询中 外层select将被标记为derived

TYPE

type表示这行查询的关联类型（访问类型，或查询类型），通过该值可以了解该行查询数据记录的大概范围。
常见的值依次从最优到最差分别为：system > const > eq_fef > ref > range > index > all；

一般优化至少要到range
ALL：all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。
INDEX：遍历索引树全表
RANGE：表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
REF：表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
eq_ref:使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
SYSTEM/CONST：MySQL能对某个查询部分进行优化并将其转化成一个常量（可以通过 show warnings 查看优化的结果），主要是查询主键（primary key）或唯一索引（Unique Key）对应的记录，因为不存在重复，所以最多只能查询出一条记录，所以速度比较快。system 是 const的特例，当临时表只有一条记录时为system。

possible_keys

显示可能用到的索引 注意是可能 并不一定真的用上

key

实际使用的索引，若为null，则没有使用索引
当使用了覆盖索引时，key和select字段应该相同

key_len

表示索引中使用的字节数，通过该列计算查询中使用的索引的长度，在不损失精度的情况下，通常越少越好。
它显示的是最大可能长度，而并非一定就是实际使用长度。
即它是根据表定义计算得到而不是通过表内检索而得到的。
key_len字段能够帮你检查是否充分的利用上了索引。

rows

MYSQL认为执行查询时必须检查的行数

Extra

不适合在其他列中显示 但是很重要的信息
using filesort：明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取的。出现这个就说明问题很严重
using temporary：使用了临时表 常见于排序order by和分组group by 出现说明问题巨严重
using index:使用的覆盖索引
using where:使用了where
impossible where:where子句的值总是false 不能用来获取任何元组

索引优化

单表例子：
创建表：

CREATE TABLE if not EXISTS 'article'(
'id' int(10) unsigned not null PRIMARY key auto_increment,
'author_id' int(10) unsigned not null,
'category_id' int(10) UNSIGNED not null,
'views' int(10) UNSIGNED not null,
'comments' int(10) UNSIGNED not null,
'title' VARBINARY(255) not null,
'content' text not null

);

插入数据

EXPLAIN SELECT
	id,
	authorid 
FROM
	article 
WHERE
	category_id = 1 
	AND comments > 1 
ORDER BY
	views DESC 
	LIMIT 1;

结果：

此时type为all 是最坏情况 且出现了using filesort（说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取的。）

添加索引:

create index idx_article_ccv on article(category_id,comments,views)

再进行查询

此时虽然type已降低到rande 但是仍有using filesort，是因为 comments>1 是一个范围查询 会导致索引失效
按照B树索引原理，按照ccv的顺序来进行排序 先按照category_id 排序 如果此字段相同 按照comments来排序，但是comments是一个范围值（range） MYSQL无法利用索引在对后面的view部分检索，即rande类型查询字段后面的索引无效
可以参考MYSQL索引里的索引失效情况的 联合索引非最左匹配 一起看

此索引无法解决问题 删除
drop index idx_article_ccv on article

comments是范围查询会导致后面索引失效 那如果跳过comments只见立cv呢？
create index idx_article_cv on article(category_id,views)

再次执行查询 发现using filesort消失 可以解决范围查询问题 type也变为ref

多表案例
建表：

create table if not exists `classes`(
id int(10) UNSIGNED not null AUTO_INCREMENT,
card int(10) UNSIGNED not null,
PRIMARY KEY(`id`)
)

create table if not exists `classes`(
id int(10) UNSIGNED not null AUTO_INCREMENT,
card int(10) UNSIGNED not null,
PRIMARY KEY(`id`)

)

两表各插入20条随机数据

SELECT * from book left join classes on book.card=classes.card

执行此条查询时，应该给哪个表加索引？

Using join buffer (Block Nested Loop)：使用连接缓冲区
Block Nested-Loop Join算法：
BNL算法原理：将外层循环的行/结果集存入join buffer，内存循环的每一行数据与整个buffer中的记录做比较，可以减少内层循环的扫描次数。 出现这个应该在关联字段添加索引（如果加上索引仍然没有命中，可能是数据类型不一致（两表字符集不一样），导致索引没有生效）

添加索引：
create index X on classes(card)
结果；

对左表添加索引 右边仍然有using join buffer 右表没有受到影响
删除

对右表添加索引：
CREATE index Y on book(card)

此时右表rows变为1行，没了using join buffer type变为ref 但是左表仍然为null，这是由左连接特性决定 left join确定如何从右表搜索行 左边一定全有

再次添加classes表的索引

左右表查询都不是all了

三表查询：
在二表的基础上添加phone表：

CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`phoneid`)
);
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));

添加索引：
CREATE index Z on phone(card)
查询：

就是二表查询基础上的套娃

由于三个表的表结构只有两个字段 一个主键（默认有主键索引），一个非唯一的字段（添加了索引），所以在select * 时 索引能够全覆盖到 type是index 如果给classes表随便加一个字段 且该字段没有索引 再次查询classes表的type会变为all（有字段索引覆盖不到，需要走全表）

在进行索引优化时 需要不断地添加删除索引来尝试

索引失效

建表

CREATE TABLE staffs (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR ( 24 ) NOT NULL ,
	age INT NOT NULL ,
	pos VARCHAR ( 20 ) NOT NULL,
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
) 


insert into staffs(NAME,age,pos,add_time) VALUES('july',23,'dev',now())
insert into staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',now())

create index idx_staffs_nameAgePos on staffs(NAME,age,pos)

最佳左前缀

联合索引顺序（NAME,age,pos）

根据NAME查询 可以用上索引

根据age查询 用不上索引 mysql会按照索引顺序从左到右来 name相同再看age 前面没用name age就会失效
详情看mysql索引 联合索引非最左匹配

把name放在后面 也能用上索引

跳过age 只用name和pos 虽然用上了索引 但不是全都用上了 只有name用上（ken_len短 应该是pos没有用上）

总结： 联合索引的第一个字段不能丢失 查询从索引的最左列开始 并且不能跳过索引中的列

不在索引列上做任何操作

任何操作是指 计算、函数、类型转换（包括自动），这会导致索引失效

在where中使用了left（name左移4位，结果还是july）导致索引失效

存储引擎不能使用索引中范围条件右边的列

name和pos使用等值查询 但是age是范围查询 age索引仍然生效，但是不是用于检索，而是用于排序。 因为是一个范围量 不准确 mysql不知道如何去用索引 所以pos索引失效 且type也变为range级别

尽量使用覆盖索引 减少使用select *

如上图，使用了select * extra出现using index condition（意思是查询条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一边再匹配无法使用索引的条件）,意思就是add_time没有用上索引

如上图 使用select name，age，pos（全是有索引的字段），extra出现了using index 使用了覆盖索引 效率好

MYSQL在使用不等于（！=或者<>）时无法使用索引会导致全表扫描

我的mysql版本是5.6.50 尝试了一下发现并没有失效 type虽然退化成了index 但是索引没有完全失效而退化成all

借用他人的图 mysql5.0版本使用不等于会造成索引失效

来源：添加链接描述

IS NULL，IS NOT NULL无法使用索引

使用的mysql是5.6.50版本
设置name字段不为空：

如上图，name is null会导致索引失效

name is not null并没有失效 type为index

将name字段改为可以为空

如上图 name is null 索引没有失效 且type 为ref

name is not null索引没有失效 type为index

like模糊查询以通配符开头导致索引失效

用like时 前面不可以加通配符 比如“%1”会导致索引失效 “1%”不会 因为索引B+树是按照索引值有序排列存储的，只能根据前缀进行比较。

前面有% 失效

前面无% 不会失效
这里是查询所有字段 如果只是查询索引字段呢？

前面没有% type为index 我的mysql版本是5.6.50 有的版本结果type是range

前面有% type为index 这代表着是通过全扫描二级索引的 B+ 树的方式查询到数据的，也就是遍历了整颗索引树。此时所有字段都在索引树里 只需要遍历索引树就可以了 并不会造成索引失效

字符串不加单引号导致索引失效

实际上就是类型转换

有引号，type为ref

无引号 type降级为index（因为覆盖索引）

如果是查询所有字段降级为all

题外话index一定就比all快吗？
index的意思是对二级索引的B+树进行了全表的扫描，all是直接对聚簇索引B+树进行全表扫描。index只是说有没有扫描二级索引的树 没说有没有进行回表。如果进行了回表，那么就是先在二级索引树中根据索引（索引是有序的，所以回表无需再排序）的主键在聚簇索引的树中进行遍历，而all的流程是先进行排序，然后进行全表的扫描 ，all和index的区别就在于all是先排序再遍历聚簇索引树，而index是先遍历二级索引树再遍历聚簇索引树，两者的性能差距没法比较，并不是说all一定比index差，需要根据具体的情况进行具体分析。

少用or 用它来连接时会导致索引失效

例题

1.staff表中 ，select * from staffs where name=‘z3’ and age = 22 and pos=‘manager’
和 select * from staffs where pos=‘manager’ and age = 22 and name=‘z3’ 各用了几条索引

答：3条 优化器是自动给第二个select的where调整顺序

2.select * from staffs where name=‘z3’ and age > 22 and pos=‘manager’
用了几条索引

答：2条 只用了name age的索引 其中name是用于查找 age 是用于排序 pos已经失效

3.select * from staffs where name=‘z3’ and pos=‘manager’ and age > 22 用了几条索引

答：3条 前两题的结合 先 优化器调整顺序 然后和第二题一样

4.select * from staffs where name=‘z3’ and age = 22 order by pos
pos也用到了 只不过不是用于查找而是排序

5.select * from staffs where name=‘z3’ order by pos

出现了using filesort 用到了一个索引（name）。 没用到age ，所以pos没用索引

第一个没有按照联合索引的顺序来 出现了using firesort 第二个正常顺序 using firesort消失

上图虽然pos age没有按照索引顺序，但是前面的等值查询已经用了name age的索引，age不再是个范围，而是一个固定值，所以pos也能用到索引。

using temporary和using filesort卧龙凤雏齐聚 ，想不死都难
二图 group by会进行排序 而pos，age顺序没有用到索引 会进行排序，产生临时表 出现using temporary
而一图是按照索引顺序来的 索引本身就是有序的 索引group by时不需要再额外进行排序，不会产生临时表
小结：order by字段就类似于不等值的范围查询 范围之后的索引会失效；group by基本上都需要进行排序，会有临时表产生

一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的