索引失效原则与查询优化

文章列表

索引失效原则与查询优化

数据库调优的维度：

索引建立
SQL优化（本文重点）
my.cnf的调整（线程数，缓存等）
分库分表

SQL查询优化的技术从大方向上可以分为物理查询优化，逻辑查询优化

物理查询优化：即通过建立索引，表连接的方式来进行优化
逻辑查询优化：SQL等价变换提升效率

1. 数据准备

学员表插 50万条，班级表插 1万条。

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。创建函数
保证每条数据都不同。随机产生字符串
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END 随机产生班级编号
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END创建存储过程
#创建往stu表中插入数据的存储过程
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END创建往class表中插入数据的存储过程
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
commit;
END#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);# 删除索引存储过程
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END

2. 索引失效的11种情况

2.1 全值匹配我最爱

explain select * from student where age = 20

索引失效原则与查询优化

explain select * from student where age = 20 and classId = 4

索引失效原则与查询优化

explain select * from student where age = 30 and classId = 4 and name = 'abcd'

索引失效原则与查询优化
上面三条sql语句的type全部为ALL

性能由好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

加上索引

CREATE INDEX idx_age on student(age)
CREATE INDEX idx_age_classid on student(age,classId)
CREATE INDEX idx_age_classid_name on student)age,classId,name)

再次进行explain
索引失效原则与查询优化
就会使用上索引

2.2 最佳左前缀法则

要遵守最佳左前缀法则

CREATE INDEX idx_age_classid_name on student)age,classId,name)

explain select * from student where age = 30 and classId = 4 and name = 'abcd'

索引失效原则与查询优化
上述sql是可以使用到联合索引的，因为查询条件的顺序和个数都是完全匹配上索引的。

explain select * from student where classId = 4 and name = 'abcd' and age = 30

索引失效原则与查询优化
那这里为什么顺序和索引的顺序不一致了，还能使用上索引呢？因为顺序不一致，但是字段是能够匹配的上索引的字段的，所以能够使用上索引（满足总结的3）

explain select * from student where name = 'abcd' and classId = 4

索引失效原则与查询优化
此时，就用不到索引了（因为不满足下述总结的1）

explain select * from student where name = 'abcd' and age = 30

索引失效原则与查询优化
为什么这个sql又能用到索引呢？（总结的2）

总结：对于联合索引，是否能用到索引的条件

查询条件的字段要能够从最左边开始覆盖到索引的字段
如果能从最左边开始覆盖到索引，那怕中间断开了，也能使用索引，只不过使用的索引不是索引的全部
顺序如果与索引定义的顺序不一致也没关系，只要能满足1，优化器在底层也会给我们自动排序

2.3 主键插入顺序

对于InnoDB存储引擎的表来说，表中的实际数据都是存储在聚簇索引的叶子节点的，记录是存在数据页中，数据页和记录是按照主键值从小到大进行排序的，如果我们插入的记录的主键值是依次增大的话，那么插入的记录会依次往后排，但是如果主键值忽大忽小，那么就会存在页分裂的情况。

索引失效原则与查询优化
例如现在这个数据页已经满了，此时再插入id为9的数据

可这个数据页已经满了，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。

所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入

但是在分布式系统中，主键一般都是代码里生成的，所以…

2.4 计算、函数、类型转换(自动或手动)导致索引失效

CREATE INDEX idx_name ON student(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

上述两个sql哪个性能更好？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

索引失效原则与查询优化

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

索引失效原则与查询优化
由此可见是第一个更好！使用函数后，已经不能使用上索引了。上述like可以使用上索引

接下来看看这三条sql语句

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc%';

其对应的结果如下
索引失效原则与查询优化

由此可见，只有第一条可以使用上索引

CREATE INDEX idx_sno ON student(stuno);

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

上述两个sql，一个对字段进行了计算，一个没有，答案显而易见不做运算的能够使用上索引

索引失效原则与查询优化

类型自动转换不能使用索引

INSERT INTO `sql_optimize`.`student`(`id`, `stuno`, `name`, `age`, `classId`) VALUES (817239817, 1111111, '123', 12, 317);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

显而易见第二条sql能够使用上索引，因为name是varchar，第一条sql的name为int，第二条sql的name为字符串类型，虽然第一条也能匹配的上记录，但是是由于底层给我们使用了函数进行类型转换。

索引失效原则与查询优化

2.5 范围条件右边的列索引失效

create index idx_age_classId_name on student(age,classId,`name`)

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

观察上述sql能否用到索引
索引失效原则与查询优化
虽然是用到索引了，但是只用到了age和classId两个字段，name字段没有用到。因为classId是范围条件，范围条件右边的列索引失效

那如果我交换查询条件classId和name的顺序呢？

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30  AND student.name = 'abc' AND student.classId>20;

索引失效原则与查询优化
结果也还是一样，因为你的索引的顺序是不变的(age,classId,name)，你的sql查询条件的顺序变化了，优化器底层还是会改变查询条件的顺序来匹配索引列的顺序来使用上索引，除非改变索引列的顺序为(age,name,classId)，这样就能使用完全索引了。

下述都是属于范围查询

<  <=  >  >=  between

总结应用开发中范围查询，例如金额，日期等，设计索引时应该将这些字段放到联合索引的最后。

2.6 不等于(!= 或者<>)索引失效

create index idx_name on student(`name`)EXPLAIN SELECT * from student where name = 'abc'EXPLAIN SELECT * from student where name <> 'abc'

观察上述sql，哪个不能使用上索引
索引失效原则与查询优化

由此可见，不等于是不能使用索引的

2.7 is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

索引失效原则与查询优化

is not null无法使用索引

2.8 like以通配符%开头索引失效

上述最左匹配原则时有提到过

拓展：Alibaba《Java开发手册》【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

2.9 or 前后存在非索引的列，索引失效

create index idx_age on student(age)

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

索引失效原则与查询优化
可以看到possible_keys有值，但是key没有，因为age能匹配上索引，但是classId匹配不上，那为什么不用idx_age索引呢？

因为如果走了idx_age索引，后面跟上or classid = 100 ，classid没有索引，就相当于还是得走一遍全表扫描，所以idx_age还不如不走，直接走全表扫描来的更快。

create index idx_age on student(age)

索引失效原则与查询优化
此时在创建classId的索引，就使用上了索引 type为index_merge

2.10 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

3. 关联查询优化

数据准备

CREATE TABLE IF NOT EXISTS `type` (
`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`)
);#向分类表中添加20条记录
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));#向图书表中添加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)));

3.1 左外连接

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

索引失效原则与查询优化
上述没有使用上索引

CREATE INDEX Y ON book(card);

给book加上索引
索引失效原则与查询优化
给type加上索引

CREATE INDEX X ON `type`(card);

索引失效原则与查询优化
我删除掉book的索引

DROP INDEX Y ON book;

索引失效原则与查询优化
可以得到book没有使用上索引

3.2 内连接

删除掉上述的book和type的索引

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

索引失效原则与查询优化

CREATE INDEX Y ON book(card);

索引失效原则与查询优化

CREATE INDEX X ON `type`(card);

索引失效原则与查询优化

对于内连接来说，查询优化器是可以决定谁作为驱动表，谁作为被驱动表。

现在删除book的索引

DROP INDEX Y ON book;

索引失效原则与查询优化
可以看到book的位置跑到了type的上面，即book是驱动表

结论：
1. 如果内连接有索引的话，索引给被驱动表，成本消耗是最低的。
2. 对于内连接来说，如果两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。即小表驱动大表

因为join连接中，例如 A inner join B … 例如是取出A的一条数据来匹配B的所有数据，若此时B有索引，则匹配的时候，就能使用上索引，但是A是必须得全部取出来的，所以这满足了结论1

如果B的数量级很大，那么索引的优势越明显，所以小表驱动大小满足了结论2

3.3 join的底层原理

join方式连接表，本质就是各个表之间数据进行循环匹配，Mysql5.5之前，Mysql只支持一种表间关联方式，就是嵌套循环（Nested Loop Join）。如果关联表的数据量很大，则join关联的执行时间会很长。在Mysql5.5之后的版本中，Mysql通过引入BNLJ算法来优化嵌套执行。

上述我们看到了Mysql优化器会帮我们决定inner join中驱动表与被驱动表。那么对于外连接（left join，right join）Mysql优化器也会帮我们决定驱动表与被驱动表

3.3.1 Simple Nested-Loop Join

索引失效原则与查询优化

开销统计	SNLJ
外表扫描次数	1
内表扫描次数	A
读取记录数	A+A*B
join次数	B*A
回表读取次数	0 （因为没有索引）

3.3.3 Index Nested-Loop Join

索引失效原则与查询优化
Index Nested-Loop Join其优化的主要思路就是减少内层的匹配次数，所以要求被驱动表必须有索引。

开销统计	SNLJ
外表扫描次数	1
内表扫描次数	0
读取记录数	A+B（match）
join次数	A*Index(Height)
回表读取次数	B（mathc）

3.3.3 Block Nested-Loop Join

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表扫描的次数太多了，每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中进行匹配，匹配完之后取出内存，然后再从驱动表中取出一条数据，加载被驱动表的记录到内存中继续比较，周而复始，这种方式大大的增加了IO次数，为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join。

不再是逐条获取驱动表的数据，而是一块一块的获取，存入join buffer缓冲区中，将驱动表join相关的部分数据列（大小受到join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单的嵌套循环中的多次比较合并为一次，降低了被驱动表的访问频率。

注意：

这里缓存的不只是关联表的列，select 后面的列也会进行缓存
在一个有N个join关联的SQL中会分配n-1个join buffer，所以查询的时候尽量减少不必要的字段，可以让join buffer中存放更多的列
所以尽量别用select * …

索引失效原则与查询优化

3.3.4 相关参数

show variables like '%optimizer_switch%' 查看block_nested_loop的状态，默认是ON

show variables like '%join_buffer_size%' 查看join_buffer_size的大小，默认是256K

join_buffer_size在32位系统上可以申请4G，在64位系统上可以申请大于4G的空间（64位windows系统除外，其最大值会被截断位4G并发出警告）

3.3.5 总结

效率上：Index Nested-Loop Join > Block Nested-Loop Join > Simple Nested-Loop Join
永远使用小结果集驱动大结果集（本质就是减少外层循环数量）（小的度量单位是指表的行数*每行大小）
被驱动表匹配的条件增加索引列
增大join buffer size的大小
减少驱动表不必要的字段查询（为什么是驱动表？因为如果是Block Nested-Loop Join，驱动表的查询字段也会加载到join buffer中）

3.3.6 Hash Join

索引失效原则与查询优化

4. 子查询优化

Mysql从4.1开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个子查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。但是子查询的效率不高，原因如下：

执行子查询时，Mysql需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些表，这样会消耗过多的cpu和io资源，产生大量慢查询
子查询的结果存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会有影响
对于返回结果集比较大的子查询，其对查询性能的影响也越大

建议实际开发中，使用join操作来替代子查询

5. 排序优化

为什么在order by字段上添加索引？

Mysql中支持两种排序方式，分别是FileSort和Index排序。

Index排序中，索引可以保证数据的有序性，不需要在进行排序，效率更高
FileSort排序则是在内存中进行排序，占用CPU资源，如果待排序的数据较大，会产生临时文件IO到磁盘进行排序，效率低下

优化建议：

SQL中，可以在where和order by子句中使用索引，目的是在where子句中避免全表扫描，order by子句中避免使用FileSort排序，但是，某些情况下全表扫描或者FileSort排序不一定比索引排序慢。
尽量使用Index排序，如果where和order by是同一个字段，则单列索引就可以满足，如果不一致，则使用联合索引
无法使用Index排序，则对FileSort方式进行调优

5.1 fileSort算法

双路排序：MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
单路排序（快）从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

在sort_buffer中，单路比多路要占用很多的空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超过了sort_buffer的容量，导致每次只能去sort_buffer容量大小的数据进行排序（然后创建tmp文件，多路合并）排完后取sort_buffer容量大小，再排…导致多次IO

优化策略：

提高sort_buffer_size大小 Mysql5.7默认位1M， show variables like '%sort_buffer_size%'
尝试提高max_length_for_sort_data提高这个参数会增加用改进算法的概率，但是如果设置的太高，数据总容量容易超过max_buffer_size，明显症状就是IO增加，如果需要返回列的总长度大于max_length_for_sort_data，则使用双路，否则使用单路，该值建议在1024-8192字节之间进行调整
order by时候建议不要使用 select * 原因见优化策略1，2条

6. group by 优化

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

7. 分页优化

优化思路1：使用order by

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
a WHERE t.id = a.id;

优化思路2：该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

8. 覆盖索引

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是，索引列+主键包含 SELECT 到 FROM之间查询的列。
查询的字段在索引中存在，即不需要回表进行查找

优点：

避免Innodb表进行索引的二次查询（回表）
可以把随机IO变成顺序IO加快查询效率（我们二级索引是有顺序的，但是如果没有索引覆盖，就得回表，从二级索引获取的主键值，在聚簇索引中不一定是连续的，所以就有可能是随机IO）

缺点：

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

具体的联合索引内容见Innodb索引还不清楚？看这一篇就够啦

9. 索引下推（ICP）

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

create index idx_name_stuno_age on student(name,stuno,age)

explain select * from student where name = '大帅' and stuno like '%10' and classId = 11

索引失效原则与查询优化
可以看到Extra中有Using index condition;即ICP索引下推

原理过程分析：

name使用到了索引，在二级索引过滤name的查询条件后，然后就直接回表了吗？
此时并没有直接回表，因为使用到的联合索引中还包含了stuno字段，我们可以在过滤name后的数据集中进行stuno的过滤，然后再去回表

减少了回表的随机IO的次数

在不使用ICP索引扫描的过程：

storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层

server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行

索引失效原则与查询优化

使用ICP扫描的过程：
storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的indexfilter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。

server 层：对返回的数据，使用table filter条件做最后的过滤

索引失效原则与查询优化
使用前后的成本差别：

使用前，存储层多返回了需要被index filter过滤掉的整行记录
使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。
ICP的加速效果取决于在存储引擎内通过 ICP筛选掉的数据的比例

ICP的使用条件：

只能用于二级索引(secondary index)
explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录
到server端做where过滤。
ICP可以用于MyISAM和InnnoDB存储引擎
MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
当SQL使用覆盖索引时，不支持ICP优化方法
ICP是一定基于有回表操作的情况下的

10 一切基于成本考虑

上述说明了索引失效的很多种情况，但是实际中并不是死板的，所有的一切，是否使用索引，最终还是交由Mysql的优化器来根据成本进行决策。

举个例子：

!= 或者 <> 无法使用索引

上述结论有提到过

create index idx_age_name on student(age,name)

创建一个索引然后执行sql

explain select * from student where age <> 20

索引失效原则与查询优化
可以看到并没有使用上索引，然后我再修改一下sql

explain select age,name from student where age <> 20

索引失效原则与查询优化
可以看到已经使用上了索引，因为此时我改变查询列，完全满足索引覆盖，没必要回表，开销小，所以就使用上了索引。总之一切都是基于开销来做决定。

早知网

索引失效原则与查询优化

1. 数据准备

2. 索引失效的11种情况

2.1 全值匹配我最爱

2.2 最佳左前缀法则

2.3 主键插入顺序

2.4 计算、函数、类型转换(自动或手动)导致索引失效

2.5 范围条件右边的列索引失效

2.6 不等于(!= 或者<>)索引失效

2.7 is null可以使用索引，is not null无法使用索引

2.8 like以通配符%开头索引失效

2.9 or 前后存在非索引的列，索引失效

2.10 数据库和表的字符集统一使用utf8mb4

3. 关联查询优化

3.1 左外连接

3.2 内连接

3.3 join的底层原理

3.3.1 Simple Nested-Loop Join

3.3.3 Index Nested-Loop Join

3.3.3 Block Nested-Loop Join

3.3.4 相关参数

3.3.5 总结

3.3.6 Hash Join

4. 子查询优化

5. 排序优化

5.1 fileSort算法

6. group by 优化

7. 分页优化

8. 覆盖索引

9. 索引下推（ICP）

10 一切基于成本考虑

相关问题

公告