MySQL索引优化分析（下）

五、索引优化

1、索引分析

（1）、单表

建表

CREATE TABLE IF NOT EXISTS article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
author_id INT(10) UNSIGNED NOT NULL COMMENT'作者id',
category_id INT(10) UNSIGNED NOT NULL COMMENT'分类id',
views INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
comments INT(10) UNSIGNED NOT NULL COMMENT'回帖的备注',
title VARBINARY(255) NOT NULL COMMENT'题目',
content LONGTEXT NOT NULL COMMENT'内容'
)

INSERT INTO article(id,author_id,category_id,views,comments,title,content) VALUES
(NULL,1,1,1,1,'1','1'),
(NULL,2,2,2,2,'2','2'),
(NULL,1,1,3,3,'3','3');


SELECT * FROM article;

案列：

查询category_id为1且comments大于1的情况下，views最多的文章id

分析：

结论：type为ALL，即最坏的情况，使用了全表扫描。Extral里还出现了Using filesort，也是最坏的情况，产生了悲剧的文件排序，所以优化是必须的。

查看表中的索引：

只有主键，没有别的索引

开始优化：

①、新建索引+删除索引

创建两种方式：

① ALTER TABLE article ADD INDEX idx_article_ccv(category_id,comments,views);

② CREATE INDEX idx_article_ccv on article(category_id,comments,views)

创建索引之后有所效果但是不好：

type变为range是可以的忍受的，但是Extral里使用Using filesort仍然是不可以的。

我们已经建立了索引为啥不行？这是因为按照BTree索引的工作原理，先排序category_id,如果遇到相同的category_id，则再排序comments，如果遇到相同的comments则再排序views。

当comments在联合索引里处于中间位置的时候，因comments>1是一个范围（所谓的range）

MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效，索引失效。

删除索引：

DROP INDEX idx_article_ccv ON article

再建索引：

优化后的结果：

结论：可以看到type变为ref，Extral中的Using filesort也消失了，结果很理想。

（2）、两表

建表

CREATE TABLE IF NOT EXISTS class(
id INT(10) UNSIGNED NOT NULL auto_increment,
card INT(10) UNSIGNED NOT NULL COMMENT'分类card',
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS book(
bookId INT(10) UNSIGNED NOT NULL auto_increment,
card INT(10) UNSIGNED NOT NULL COMMENT'分类card',
PRIMARY KEY (bookId)
);


INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));


INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1+RAND()*20));

案例：对两张表左连接进行优化

结论：type有ALL。

开始优化：

添加索引（将索引添加在右表）

可以看到第二行的type变为ref，rows也变成了优化比较明显。

这是左连接特性确定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是关键点，一定要建立索引。

（左连接索引加右表）

将索引添加在左表：

比上面差！

重新删除索引+新建索引+Explain

同理，在右连接上，RIGHT JOIN条件用于确定如何从左表搜索行，右边一定都有，所以左边使我们的关键点，一定需要建立索引。

（3）、三表

建表

CREATE TABLE IF NOT EXISTS phone(
phoneId INT(10) UNSIGNED NOT NULL auto_increment,
card INT(10) UNSIGNED NOT NULL COMMENT'分类card',
PRIMARY KEY (phoneId)
)ENGINE = INNODB;


INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1+RAND()*20));

案例

加索引

总结：后两行的type都是ref，且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

JOIN语句优化：

①、尽可能减少JOIN语句中NestedLoop的循环总次数，“永远用小结果集驱动大结果集”

②、优先优化NestedLoop的内循环。

③、保证JOIN语句中被驱动表上join条件字段已经被索引。

④、当无法保证被驱动表中的join条件字段被索引且内存资源充足的前提下，不要吝啬JoinBuffer的设置

2、索引失效（应该避免）

（1）建表

CREATE TABLE IF NOT EXISTS 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());
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());

SELECT * FROM staffs;

添加索引：

（2）案例（索引失效）

①、全职匹配我最爱

②、最佳右前缀法则

如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中间的列。

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

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

⑤、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select.*。

⑥、mysql在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描

⑦、is null ，is not null 也无法使用索引

⑧、like以通配符开头（‘%abc····’）mysql索引失效会变成全表扫描的操作

结论：%-写在最右边效果最好

问题：解决like "%字符串%"索引不被使用的方法？

可以用覆盖索引来解决失效的问题。

⑨、字符串不加单索引号索引失效

⑩、少用or，用它来连接的时候索引会失效

小总结口诀：

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

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

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

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

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

VAR引号不可丢，SQL高级也不难；

（3）面试题讲解

建表

CREATE TABLE IF NOT EXISTS test(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
c1 CHAR(10),
c2 CHAR(10),
c3 CHAR(10),
c4 CHAR(10),
c5 CHAR(10) 
);


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


SELECT * FROM test;

建索引