Mysql索引的优化分析-索引优化（1）

一.索引分析

一. 单表

建表sql:
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'),
(1, 1, 3, 3,'3','3');

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

可以看见type=all extra:using filesort 需要索引优化
首先看看本表的索引

show index from articlel;

开始优化：
1.1 新建索引+删除索引

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

show index from articlel;

此时在执行语句发现type已经变成了range，但是extra中的using filesort 依然存在，为什么？

分析：如果把语句变为：

explain select id,author_id from article 
where category_id = 1 and comments = 1 
order by views DESC
LIMIT 1 

结论：范围之后的索引会导致失效，所以此索引不合适；
这是因为按照BTree索引的工作原理，先排序category_id,如果遇到相同的category_id则再排序commnets,如果遇到相同的comments，则再排序views，当comments字段在联合索引里处于中间位置时，因comments>1条件是一个范围值（所谓range）
mysql无法利用索引在对后面的views 部分进行检索，即range类型的查询字段后面的索引无效
但是至少解决了全表扫描的问题，删除索引，重建

drop index idx_article_ccv on article

1.2 重建索引
既然范围后的索引会失效，那么我们就不建comments的索引

alter table article add index idx_article_cv(categoey_id,views)

此时再执行explain

---

二.俩表

建表sql:
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() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

1. 查询一个左连接(未建索引)

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

type为all需要优化，那么问题来了，索引是建在class上还是建在book上呢？
首先建在book 上

create index Y on book(card);

再建立在class上

drop index Y on book;

alter table class add index Y(card);

可以发现rows的和为40，前面的rows的值为21
结论：俩表关联，索引建在连接的相反面

---

三.三表

建表sql
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (phoneid)
) engine innodb;

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

explain 语句

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

创建索引

alter table book add index X(card);
alter table phone add index Y(card);

结论：join语句的优化
1.尽可能的减少join语句中的NestedLoop的循环总数次数：“永远小表驱动大表；

2.优先优化NestedLoop的内层循环；

3.保证join语句中被驱动表上的join条件字段已经被索引；

4.当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要舍不得jionbuffer的设置；