索引查询优化

2019-09-11

一、关联查询优化

建表插入数据

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

创建索引

CREATE INDEX index_card ON book(card) 
DROP INDEX idx_card ON book

下面开始explain分析

EXPLAIN SELECT * FROM book  LEFT JOIN class ON class.card = book.card;#索引用上了，但是还是扫描主表 rows为20行，连接表20行，因为book是主表（驱动表）
#结论：type 有All

换成INNER JOIN 解决
EXPLAIN SELECT * FROM book  INNER JOIN class ON class.card = book.card;#主表扫描20行，连接表1行 ，索引起作用了
#原因 换成inner后，两个表没有了主从顺序，所以也没有了位置关系
#结论 给left join 添加索引的时候，需要给被驱动表添加索引才能起作用

#测试结论一把
#删除索引函数
CALL proc_drop_index("learning","book")
CREATE INDEX idx_card ON class(card) #给left join的被驱动表card创建索引
EXPLAIN SELECT * FROM book  LEFT JOIN class ON class.card = book.card;#索引用上了

 
# 第2次explain 将主表改为了class 索引仍为book表的索引
#删除索引函数
CALL proc_drop_index("learning","book")
ALTER TABLE book ADD INDEX Y (card)
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
 
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; #没有起作用

结论建议

1、保证被驱动表的join字段已经被索引
2、left join 时，选择小表作为驱动表，大表作为被驱动表。
3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
4、子查询尽量不要放在被驱动表，有可能使用不到索引。
5、能够直接多表关联的尽量直接关联，不用子查询。

二、子查询优化

尽量不要使用not in 或者 not exists 用left outer join on xxx is null 替代

三、排序分组优化

CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME)
#以下  是否能使用到索引，能否去掉using filesort
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid; #未使用索引，extra显示Using filesort
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10;
#无过滤 不索引
3、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY deptid;
4、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY   deptid,NAME;  
#顺序错，必排序
5、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  deptid,empno;
6、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  NAME,deptid; 
7、 EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;
#方向反 必排序
8、  EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid DESC, NAME DESC ;   
9、 EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid ASC, NAME DESC ;
#ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

如果不在索引列上，filesort有两种算法：

mysql就要启动双路排序和单路排序

1.双路排序
1.1MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
1.2从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
2.取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
3.单路排序
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
4.由于单路是后出的，总体而言好过双路
5.但是用单路有问题
6.优化策略
6.1增大sort_buffer_size参数的设置
6.2增大max_length_for_sort_data参数的设置
6.3减少select 后面的查询的字段。
7.提高Order By的速度
7.1. Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是：
7.1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
7.1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
7.2. 尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的  1M-8M之间调整
7.3. 尝试提高 max_length_for_sort_data
提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整

GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

最后使用索引的手段：覆盖索引

什么是覆盖索引？
简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键
减少select *的使用