索引优化

1. 索引分析

1.1 单表优化

• 创建表插入数据

create table 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
);

insert into article(author_id,category_id,views,comments,title,content) values
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,1,1,3,'3','3');

• 查询category_id为1且comments大于1的情况下，views最多的article_id

select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1

• 查看执行计划，sql分析：type是all表示全表扫描，Extra还出现了filesort，产生了文件内排序，且未使用到索引；必须优化。

explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1

• 开始优化，创建复合索引

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

• 再次查看执行计划，sql分析：type变为了range表示范围内索引，且使用到了建立的复合索引，但还是产生了文件内排序。 但是按照Btree索引的工作原理，先排序category_id,再排序commnets，因为comments字段大于1是一个范围值，导致无法利用索引对后面的views进行检索，索引部分失效了

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

• 优化2，删除非最优索引，绕过comments创建复合索引

drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id,views); 

• 查看执行计划，sql分析：type为ref级别，using filesort也没了，非常理想的优化结果。

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

1.2 两表优化

• 创建表插数据

create table class(
    id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key(id)
);

create table book(
    bookid int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key(bookid)
);
给两个表各插入20条数据
insert into class(card) values
(FLOOR(1+(RAND()*20))); # 执行20次
insert into book(card) values
(FLOOR(1+(RAND()*20))), # 执行20次

• 开始explain分析, sql分析：type是all，全表扫描

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card` = book.`card`;

• 添加索引尝试优化

ALTER TABLE book ADD INDEX idx_y(card);

• 查看计划，sql分析：book表type优化到了ref级别

explain select * from class left join book on class.`card` = book.`card`;

• 继续尝试优化

DROP INDEX idx_y ON book;
ALTER TABLE class ADD INDEX idx_y(card); # 给class表加索引

• 查看计划，sql分析：class表type仅优化为了index级别，全索引扫描

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card` = book.`card`;

• 小结：由于左连接的特性，决定了左边内容一定都是有的，left join 条件用于确定如何从右表搜索行；所以left join一定要给右表建立索引。
• 修改回最优索引

DROP INDEX idx_y ON class;
ALTER TABLE book ADD INDEX idx_y(card);

• 那么当right join时呢?

explain select * from class right join book on class.`card` = book.`card`;

• 跟左连接相反，右连接给左表建立索引即可

ALTER TABLE class ADD INDEX idx_x(card);

• 查看计划，sql分析：可以看到class表优化为了ref级别

explain select * from class right join book on class.`card` = book.`card`;

• 总结：左连接给右表建索引，右连接给左边建索引。

1.3 三表优化

• 沿用上两张表的情况下，加入一张表

CREATE TABLE phone(
    phone_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    card INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(phone_id)
)ENGINE=INNODB;

INSERT INTO phone(card) VALUES
(FLOOR(1+(RAND()*20))); # 执行20次

• 清除上一个case建立的全部索引

drop index idx_y on book;
drop index idx_x on class;

• 三表查询计划, sql分析：type均为all，必须优化。

explain select * from class left join book on class.`card`=book.`card` left join phone on book.`card`=phone.`card`;

• 根据左连接查询给右表建索引原则，给右边的phone表和book表建立索引

alter table phone add index z(card);
ALTER TABLE book ADD INDEX Y(card);

• 查看计划，sql分析：book和phone表都优化到了red级别，优化很好，因此索引最好设置在需要经常查询的字段中。

explain select * from class left join book on class.`card`=book.`card` left join phone on book.`card`=phone.`card`;

• Join语句优化
  • 尽可能减少Join语句中的NestedLoop循环总次数； “永远用小结果集驱动大结果集”
  • 优先优化NestedLoop的内层循环
  • 保证Join语句中被驱动表上Join条件字段已经被索引
  • 当无法保证被驱动表的Join条件被索引且内存资源充足的前提下，不必太吝惜JoinBuffer的设置

2. 索引失效

2.1 建表

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default "" comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default "" comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
)charset utf8 comment '员工记录表';
# 插入数据
insert into staffs(name,age,pos,add_time)values
('z3',22,'manager',now()),
('July',23,'dev',now()),
('2000',23,'dev',now());
# 建立复合索引
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
show index from staffs;

2.2 索引失效案例

2.2.1 全值匹配

如果关键列建立了索引，尽量全部都用到。

全值匹配我最爱，最左前缀要遵守

2.2.2 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则；指的是查询从索引的最左前列开始且不跳过索引中间的列；即索引的列一个都不要少。

带头大哥不能死，中间兄弟不能断

2.2.3 不在索引列上做任何操作(计算、函数、自动or手动类型转换)，会导致索引失效而转向全表扫描

• 举例如下

# 没在索引列进行函数相关操作，使用到了索引且type为ref级别
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
# 在索引列进行了函数操作，会导致索引失效，type为all全表扫描
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July';

索引列上少计算

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

• 举例如下操作会让范围后面的pos索引失效，type降低为range类型。

# 4.存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 25 AND pos='manager'

范围之后全失效

2.2.5 尽量使用覆盖索引(索引列和查询列一致)，减少select *

• 这样可以Using index，性能不错

EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME = 'July' AND age  = 25 AND pos='dev';

覆盖索引不写星

2.2.6 mysql在使用不等于(!=或者<>)时无法使用索引会导致全表扫描

• 使用不等也会导致索引失效全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME != 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';

不等空值还有or，索引失效要少用

2.2.7 is null,is not null也无法使用索引

• is null 会出现Impossible WHERE情况；is not null会导致索引失效全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL;
EXPLAIN SELECT * FROM staffs WHERE NAME is not null;

不等空值还有or，索引失效要少用

2.2.8 like以通配符开头(‘%abc…’)也会导致索引失效变成全表扫描。

• 百分号尽量写右边，不然会导致索引失效全表扫描，使用覆盖索引关键列可优化到index级别。

EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%'; # 全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July'; # 全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'July%'; # range范围扫描

Like百分%写最右

2.2.9 字符串不加单引号索引失效

• varchar类型字符串不加引号也会导致索引失效全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME = 2000; #因为name是varchar类型，mysql做了自动类型转换，导致索引失效
EXPLAIN SELECT * FROM staffs WHERE NAME = '2000'; # 不会失效

Varchar引号不可丢

2.2.10 少用or，用它连接时会索引失效

• or也会导致索引失效

EXPLAIN SELECT * FROM staffs WHERE NAME='July' OR NAME = 'z3';

不等空值还有or，索引失效要少用

2.3 索引优化分析题

2.3.1 准备工作

• 建立表插数据

CREATE TABLE ex01(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    c1 CHAR(10),
    c2 CHAR(10),
    c3 CHAR(10),
    c4 CHAR(10),
    c5 CHAR(10)
);

INSERT INTO ex01(c1,c2,c3,c4,c5) VALUES
('a1','a2','a3','a4','a5'),
('b1','b2','b3','b4','b5'),
('c1','c2','c3','c4','c5'),
('d1','d2','d3','d4','d5'),
('e1','e2','e3','e4','e5');

• 建立索引

CREATE INDEX idx_ex01_c1234 ON ex01(c1,c2,c3,c4);
SHOW INDEX FROM ex01;

2.3.2 根据以下SQL分析索引的使用情况

• 全索引使用，全值匹配，ref级别；顺序改变mysql会自动优化，故以下SQL性能一致。

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 = 'a3' AND c4 = 'a4';
EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 = 'a4' AND c3 = 'a3';
EXPLAIN SELECT * FROM ex01 WHERE c4 = 'a4' AND c3 = 'a3' AND c2 = 'a2' AND c1 = 'a1';

• 三索引使用，范围匹配，range级别；范围之后全失效。

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 > 'a3' AND c4 = 'a4';

• 全索引使用，范围匹配，range级别。

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 > 'a4' AND c3 = 'a3';

• 两索引使用，c3作用在排序而不是查找

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 = 'a4' ORDER BY c3;

• 两索引使用，c3在排序用不到索引查找

explain SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' ORDER BY c3;

• 两索引使用，因为跳过了c3导致c4无法用到排好序的索引，只能自己排序，从而出现了Using filesort

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' ORDER BY c4;

• 单索引使用，c2、c3在排序

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c5 = 'a5' ORDER BY c2,c3;

• 单索引使用，因为c3跳过了c2,导致索引没使用到从而自己排序，出现Using filesort

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c5 = 'a5' ORDER BY c3,c2;

• 两索引使用

explain SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' ORDER BY c2,c3;

• 两索引使用，c2、c3用于排序

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' AND c5 = 'a5' ORDER BY c2,c3;

• 两索引使用，c1、c2用到了索引，所以c2变成了一个常量，在order by那边常量不用排序，故没有产生文件内排序

EXPLAIN SELECT * FROM ex01 WHERE c1 = 'a1' AND c2 = 'a2' AND c5 = 'a5' ORDER BY c3,c2;

• 单索引使用，c2、c3在排序，c4用不到

EXPLAIN SELECT * FROM ex01 WHERE c1='a1' AND c4='a4' GROUP BY c2,c3;

• 单索引使用，c3跳过了c2产生文件内排序，group by还用到了临时表Using temporary

EXPLAIN SELECT * FROM ex01 WHERE c1='a1' AND c4='a4' GROUP BY c3,c2;

• 小结：定值、范围还是排序，一般order by是给范围排序；group by基本上都会进行排序，会产生临时表，分组之前必定会排序。

3. 一般建议

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

3.1 索引优化口诀

• 全值匹配我最爱，最左前缀要遵守；

• 带头大哥不能死，中间兄弟不能断；

• 索引列上少计算，范围之后全失效；

• Like百分写最右，覆盖索引不写星；

• 不等空值还有or，索引失效要少用；

• Varchar引号不可丢，SQL高级也不难！