MYSQL高级2：索引优化分析

索引优化分析

一、SQL性能下降的原因

• 查询语句写的差

• 索引失效：索引建了，但是没有用上

• 关联 查询太多join（设计缺陷或者不得已的需求）

• 服务器调优以及各个参数的设置（缓冲、线程数等）

---

二、常见通用的Join查询

SQL执行顺序

手写

SELECT DISTANCT
	<select_list>
FROM
	<left_table> <join_type> 
	join <right _table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT <limit number>

机读

FROM <left_table> 
ON <join_condition> 
<join_type> join <right _table> 
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTANCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit number>

总结

Join图

注意：FULL OUTER JOIN MySQL不支持

使用union

SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union
SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id

A,B独有改为

SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
union
SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null

建表测试

CREATE TABLE `tbl_emp`  (
 `id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20)  DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) ,
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

 CREATE TABLE `tbl_dept`  (
 `id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
 `locAdd` varchar(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

insert into tbl_dept values(null,'RD',1);
insert into tbl_dept values(null,'HR',12);
insert into tbl_dept values(null,'MK',13);
insert into tbl_dept values(null,'MIS',14);
insert into tbl_dept values(null,'FD',15);

insert into tbl_emp values(null,'z3',1);
insert into tbl_emp values(null,'z4',1);
insert into tbl_emp values(null,'z5',1);
insert into tbl_emp values(null,'w5',2);
insert into tbl_emp values(null,'w6',2);
insert into tbl_emp values(null,'s7',3);
insert into tbl_emp values(null,'s8',4);
insert into tbl_emp values(null,'s9',51);

---

三、索引简介

1. 是什么

MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结果

可以获得索引的本质：索引是数据结构。

可以简单的理解为“排好序的快速查找数据结构”

除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。下图就是一种可能的索引方式示例

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

2. 优势

• 查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本
• 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗

3. 劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

• 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

• 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

4. 索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。

• 唯一索引：索引列的值必须唯一，但是允许空值。

• 复合索引：一个索引包含多个字段。

建议：一张表建的索引最好不要超过5个！

5. 基本语法

/*创建  加[UNIQUE]表示唯一索引  columnName(length)多个字段为复合索引，单个字段为单值索引*/
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
ALTER TABLE tabName ADD [UNIQUE] INDEX indexName ON (columnName(length));

/* 2、删除索引 */
DROP INDEX [indexName] ON tabName;

/* 3、查看索引 */
/* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */
SHOW INDEX FROM tabName \G;

使用alter命令

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

6. mysql索引结构

如果到此对索引还不够了解，可以看看这边文章

Mysql索引

索引数据结构：

• B+Tree索引

• Hash索引

• Full-text全文索引

• R-Tree索引

BTree索引检索原理

初始化介绍

一颗b+树，浅蓝色快我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1,P2,P3

P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99

非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查找过程

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定此判断1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不急，通过磁盘块1的P2指针的磁盘地址把磁盘块3you磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次IO，显然成本非常非常高。

7. 那些情况需要创建索引

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该创建索引

• 查询中与其他表关联的字段，外键关系建立索引

• 单值/组合索引的选择问题。（在高并发下倾向于创建组合索引）

• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

• 查询中统计或者分组字段（group by也和索引有关）

8. 哪些情况不要创建索引

• 记录太少的表。

• 经常增删改的表。 （索引提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）

• 频繁更新的字段不适合创建索引。

• Where条件里用不到的字段不创建索引。

• 数据重复且分布平均的表字段，应该职位最经常查询和最经常排序的数据列建立索引。注意，如果每个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。

假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

索引的选择性=索引列中不同的数据数量/索引列中总的数据数量

---

四、性能分析

1. MySQL Query Optimizer

MySQL中专门负责优化SELECT语句的优化器模块

主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）

当客户端像MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer ，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件，结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读区所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

2. MySQL常见瓶颈

CPU：CPU再饱和的时候一般发生在数据装入内存或从磁盘上读区数据的时候

IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：top free iostat和vmstat来查看系统的性能状态

3. explain

3.1 是什么

EXPLAIN：SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

3.2 能干嘛

• 表的读取顺序 id
• 数据读取操作的操作类型 select_type
• 那些索引可以使用 possible_keys
• 那些索引被实际使用 key
• 表之间的引用 ref
• 每张表有多少行被优化器查询 rows

3.3 怎么使用

Explain + SQL语句

执行计划包含的信息

3.4 各字段解释

3.4.1 id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

值情况

• id相同，执行顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行

3.4.2 select_type

数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

• SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION。

• PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

• SUBQUERY：在**SELECT或者WHERE子句中包含了子查询**。

• DERIVED：在**FROM子句中包含的子查询**被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。

• UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。

• UNION RESULT：从UNION表获取结果的SELECT。

3.4.3 table

显示这一行的数据是关于那张表的

3.4.4 type

访问类型排列

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。

• system : 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计。

• const : 表示通过索引一次就找到了，const用于比较primary key或者unique索引。被连接的部分是一个常量值。因为只匹配一行数据，所以很快。如将主键置于where中，MySQL就能将该查询转化为一个常量

• eq_ref : 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，即对于前表的每一行，后表只有一行被扫描。常见于主键或唯一索引扫描，join查询，等值连接。除了 system 和const 类型之外, 这是最好的联接类型。

• ref ： 非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。 对于前表的每一行，后表可能有多于一行的数据被扫描。

• range ：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。

• index : Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。

也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。

• ALL ： Full Table Scan，没有用到索引，全表扫描。
• NULL:不访问任何表，索引，直接返回结果

注意：一般来说，得保证查询至少达到range级别，最好达到ref

3.4.5 possible_keys

显示可能应用在这张表中的索引，一个或者多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

3.4.6 key

实际使用的索引。如果为NULL，则没建或没有使用索引，即索引失效。

查询中如果使用了覆盖索引，则该索引和查询的select字段重叠

3.4.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。

3.4.8 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

3.4.9 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

3.4.10 Extra

包含不适合在其他列中显示但十分重要的额外信息。

• Using filesort

说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

MySQL中无法利用索引完成的排序操作称为"文件内排序"。

• Using temporary

使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。

• Using index

表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！

如果同时出现**Using where，表示索引被用来执行索引键值的查找**；

如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

• Using where：表明使用了WHERE过滤。

• Using join buffer：使用了连接缓存。

• impossible where：WHERE子句的值总是false，不能用来获取任何元组。

• select tables optimized away: 在没有GROUPBY子句的情况下、基于索引优化MIN/MAX操作或者碎玉MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段在进行计算。查询执行计划生成的阶段即完成优化

• distinct ： 优化distinct操作，在找到第一匹配的元组后停止找同样值的动作

3.5 示例

---

五、索引优化

5.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'),
(3,3,3,3,'3','3'),
(1, 1, 3, 3, '3', '3'),
(1, 1, 4, 4, '4', '4');
 
SELECT * FROM ARTICLE;

案例：查询category_id为1且comments大于1的情况下，views最多的article_id。

1. 编写sql

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

2. 查看SQL执行计划。

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

从表中可以看到当前sql没有使用到索引，且访问类型为全表搜索。为了提高性能尝试添加索引

3. 对where中使用到的字段category_id,comments,views添加索引

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

重新查看执行计划，发现使用到索引，但访问类型为范围内检索，而且仍存在Useing filesort文件内排序

结论

• type变成了range，这是可以忍受的，但extra里使用Useing filesort仍是无法接受的。
• 为什么建立了索引没有用？
  • 因为按照Btree索引的工作原理。先排序category_id，然后遇到相同的category_id则在排序comments，如果遇到相同的comments则在排序views。当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效

4. 修改索引

我们把引起索引失效的comments字段的索引删掉

drop index idx_article_ccv on article;
/* 创建索引 idx_article_cv */
CREATE INDEX idx_article_cv ON article(category_id,views);

可以看到类型变为ref，同时Useing filesort也没有了

5.2 两表索引分析

测试用例

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
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 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)));
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)));

两表连接查询的SQL执行计划

1、不创建索引的情况下，SQL的执行计划。

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

可以看到两个表上的type都为all，没有使用索引，而是进行全表搜索

2. 尝试进行索引添加，在左表建索引

alter table book add index idx_c(card);

再次查询

我们可以看到book的类型变为了index，但是搜索行数仍为20，并没有得到太大的优化。

3. 删除左表索引，在右表建索引

drop index idx_c on book;alter table class add index idx_c(card);

再次进行测试

可以看到class字段类型变为ref，搜索行数也变为1，性能得到了较好的提高。

结论

• 对于左连接，LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有。所以右边是我们的关键点，一定需要建立索引。
• 对于右链接则相反，RIGHT JOIN条件用于确定如何从左表搜索行，左边一定都有。所以左边是我们的关键点，一定需要建立索引。

5.3 三表索引分析

测试数据

CREATE TABLE IF NOT EXISTS `phone`(`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL)ENGINE = INNODB;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)));

1、不加任何索引，查看SQL执行计划。

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

2. 根据量表的经验，左连接在右边加索引，添加book和phone的索引

create index idx_c on book(card);create index idx_c on phone(card);

再次测试，可以发现优化了不少

因此索引最好设置在需要经常查询的字段中

Join语句优化结论

• 尽可能减少join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”

• 优先优化NestedLoop的内层循环

• 保证join语句中被驱动表上join条件字段已经被索引

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

六、索引失效

测试数据

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());
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());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)

6.1 索引失效情况

• 全值匹配我最爱。
• 最佳左前缀法则。——如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
• 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
• 索引中范围条件右边的字段会全部失效。
• 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。
• MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。
• is null、is not null也无法使用索引。
• like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）。
• 字符串不加单引号索引失效。
• 少用or，用它来连接时会索引失效。

6.1.1 全值匹配

EXPLAIN SELECT * FROM `staffs` 
WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

6.1.2 最佳左前缀法则

EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';

EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;

EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';

EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';

EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';

测试结果

前三条索引有效

后三条，前两条索引失效，最后一条索引部分失效

口诀：带头大哥不能死，中间兄弟不能断。

6.1.3 索引列上不计算

#现在要查询`name` = 'July'的记录下面有两种方式来查询！
# 1、直接使用 字段 = 值的方式来计算
explain SELECT * FROM `staffs` WHERE `name` = 'July';

# 2、使用MySQL内置的函数
explain SELECT * FROM `staffs` WHERE LEFT(`name`, 4) = 'July';

测试结果

可以看到使用内置函数的索引会失效，变成全表扫描

口诀：索引列上不计算。

6.1.4 范围之后全失效

EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 'dev';

测试结果

可以看到，范围后面的索引会失效，示例中即pos失效

口诀：范围之后全失效。

6.1.5 覆盖索引尽量用

/* 没有用到覆盖索引 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

/* 用到了覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

测试结果

可以看到使用覆盖索引，会多出using index

口诀：查询一定不用*。

6.1.6 不等有时会失效

/* 会使用到覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` != 'Ringo';

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` != 'Ringo';

测试结果

6.1.7 is null会导致索引失效

explain select * from staffs where name is null;

explain select * from staffs where name is not null;

6.1.8 like百分加右边

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%July%';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%July';
/* 使用索引范围查询 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'July%';

测试结果

口诀：like百分加右边。

如果一定要使用like %字符串%，而且还要保证索引不失效，那么使用覆盖索引来编写SQL。

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

CREATE INDEX idx_user_nameAge on tbl_user(Name,age);

有效
EXPLAIN SELECT ID FROM TBL_USER WHERE NAME LIKE '%aa%';  
EXPLAIN SELECT NAME FROM TBL_USER WHERE NAME LIKE '%aa%';
EXPLAIN SELECT AGE FROM TBL_USER WHERE NAME LIKE '%aa%';

有效
EXPLAIN SELECT ID,NAME FROM TBL_USER WHERE NAME LIKE '%aa%';
EXPLAIN SELECT ID,NAME,AGE FROM TBL_USER WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME,AGE FROM TBL_USER WHERE NAME LIKE '%aa%';

无效
EXPLAIN SELECT * FROM TBL_USER WHERE NAME LIKE '%aa%';
EXPLAIN SELECT ID,NAME,AGE,EMAIL FROM TBL_USER WHERE NAME LIKE '%aa%';

测试结果

口诀：覆盖索引保两边

6.1.9 字符要加单引号

EXPLAIN SELECT * FROM `staffs` WHERE `name` = '2000';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;

测试结果

口诀：字符要加单引号。

6.1.10 or有时会失效

EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'July' or name = 'z3';

测试结果

小练习

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y，使用到a
where a = 3 and b = 5	Y，使用到a，b
where a = 3 and b = 5	Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N，没有用到a字段
where a = 3 and c = 5	使用到a，但是没有用到c，因为b断了
where a = 3 and b > 4 and c = 5	使用到a，b，但是没有用到c，因为c在范围之后
where a = 3 and b like ‘kk%’ and c = 4	Y，a，b，c都用到
where a = 3 and b like ‘%kk’ and c = 4	只用到a
where a = 3 and b like ‘%kk%’ and c = 4	只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y，a，b，c都用到

6.2 面试题分析

测试数据

create table test03(
	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 test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

1

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

从结果看，可以看出他使用到可索引c1，c2，c3，c4全部，属于全值匹配

2

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

可以看到虽然我们改变了where字段的顺序，但是它仍然用到了c1，c2，c3，c4全部索引。因为MySQL的查询优化器会优化SQL语句的顺序

结论 最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次

3

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

可以看到用到索引c1 c2 c3字段，c4字段失效，范围之后全失效

4

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

可以看到用到索引c1 c2 c3 c4全字段，因为MySQL的查询优化器会优化SQL语句的顺序

5

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

用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中，c4字段失效

6

EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;

用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中

7

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

用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了 Using filesort 说明排序没有用到c4字段

8

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

用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序

9

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

用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort

10

EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;

用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序

11

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

用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序

12

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

用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 因为之前c2这个字段已经确定了是’a2’了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！所以没有产生Using filesort 和(9)进行对比学习

13

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

用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效

14

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

用到c1这一个字段，c4失效，c2和c3排序失效产生了 Using temporary

**当前版本（5.7之后）的group by无法使用select ***

1：对于用到 GROUP BY 的 select 语句, 查出来的列必须是 group by 后面声明的列, 或者是聚合函数里面的列有这样一个数据库的表.
2：group by id (id 是主键) 的时候, select 什么都没有问题, 包括有聚合函数.
3：group by role (非主键) 的时候, select 只能是聚合函数和 role ( group by 的字段) , 否则报错

具体group by该怎么用可以看看这篇博文，写的挺好

关于group by的用法 原理

结论

先看字段是定值、范围还是排序，一般order by是给个范围。group by基本上都需要进行排序，会有临时表产生。

七、一般性建议

• 对于单值索引，尽量选择针对当前query过滤性更好的索引。

• 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

• 在选择复合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。

• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

口诀

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

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

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

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

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

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

相关视频：尚硅谷MySQL数据库高级，mysql优化，数据库优化