MySQL之SQL性能（2）——索引优化

目录

1.索引分析

（1）单表案例分析

1）未建索引

2）按顺序建立用到的三个字段的索引

3）只创建非范围字段的索引

（2）两表优化案例

1）未建索引

2）建左表的索引

3）建右表的索引

（3）三表优化案例

2.索引失效

（1）常见索引失效原因

1）最佳左前缀法则

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

3）存储引擎不能使用索引种范围条件右边的列，范围之后全失效

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

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

6）is null和is not null也无法使用索引

7）like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描

       1.未建索引前

       2.为经常查询的字段name,age建立索引

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

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

3.案例分析

（1）案例1：

（2）案例2：

4.一般性建议

5.优化总结口诀

---

1.索引分析

（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,
	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'),
(1,1,3,3,'3','3');

需求：查询category为1，且comments大于1的情况下，views最多的article_id;

1）未建索引

可以看到这种sql可以实现功能，但是性能极低(type是ALL是最坏的情况，Extra出现了Using filesort，也是最坏的情况，需要优化)

2）按顺序建立用到的三个字段的索引

创建了三值索引后，虽然type从all提升到了range，也使用了创建的索引，但还是有Using filesort，性能依然不好，需要优化

原因：范围会导致索引失效，此处第二字段comments>1会导致复合索引后面的字段索引失效

按照BTree的原理，先排序category_id，遇到相同的category_id再排序comments，如果遇到相同的comments，再排序views

当comments在复合索引中处于中间位置时，由于comments>1条件是一个范围值（range），mysql无法再对后面的views部分进行检索，即range类型查询后面的索引会失效

3）只创建非范围字段的索引

先删除上一步的索引

创建新的索引

type优化到了ref，索引也进行了使用，而且还多了const，又没有文件排序，优化完成

（2）两表优化案例

建表

CREATE TABLE IF NOT EXISTS class(
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(id)
);


CREATE TABLE IF NOT EXISTS book(
	bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(bookid)
);

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



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

          

需求：class表左外连接cook

1）未建索引

type有ALL，性能低

2）建左表的索引

3）建右表的索引

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

这是由左连接的特性决定的，left join用于确定如何从右表中搜索行，左边一定都有

同理，右连接用于确定如何从左表搜索行，右边一定都有，所以左表建立索引性能好

总结：左右连接相反建，左连接为右表建立索引，右连接为左表建立索引

（3）三表优化案例

在上述示例的基础上，再建一个表