MySQL高级知识点(六)
文章目录
关联查询优化
建表语句
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)));
1、LEFT JOIN优化
SQL语句
--未建立索引时的左外连接查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
--左表(class)建立索引
CREATE INDEX idx_class_card ON class(card);
--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
--去掉左表索引
DROP INDEX idx_class_card ON class;
--右表建立索引
CREATE INDEX idx_book_card ON book(card);
--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
对应结果
结论
- 在优化关联查询时,只有在被驱动表上建立索引才有效
- left join 时,左侧的为驱动表,右侧为被驱动表
2、INNER JOIN优化
SQL语句
--查询操作,目前索引在book表的card上,class表和book表的位置不会改变查询结果
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
EXPLAIN SELECT * FROM book INNER JOIN class ON book.card = class.card;
--删除book表中的几条记录
DELETE FROM book WHERE bookid<10;
--再次查询
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
--删除book表card字段索引,给class表的card字段添加索引
DROP INDEX idx_book_card ON book;
CREATE INDEX idx_class_card ON class(card);
--再次查询
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
对应结果
结论:inner join 时,mysql 会把小结果集的表选为驱动表(小表驱动大表)
所以最好把索引建立在大表(数据较多的表)上
3、RIGHT JOIN优化
优化类型和LEFT JOIN类似,只不过被驱动表变成了左表
排序分组优化
在查询中难免会对查询结果进行排序操作。进行排序操作时要避免出现 Using filesort,应使用索引给排序带来的方便
索引信息
1、ORDER BY 优化
以下查询都是在索引覆盖的条件下进行的
SQL语句
--不满足索引覆盖时进行排序查询
EXPLAIN SELECT empno FROM t_emp WHERE age > 50 ORDER BY age, deptId;
--按照复合索引顺序进行排序
EXPLAIN SELECT age, deptId FROM t_emp WHERE age > 50 ORDER BY age;
EXPLAIN SELECT age, deptId FROM t_emp WHERE age > 50 ORDER BY age, deptId;
EXPLAIN SELECT age, deptId FROM t_emp WHERE age > 50 ORDER BY age, deptId, name;
--不按照复合索引顺序进行排序(无 age 字段),发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp WHERE age > 50 ORDER BY deptId, name;
--不按照复合索引顺序进行排序(索引顺序打乱),发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp WHERE age > 50 ORDER BY deptId, name, age;
--排序时部分(age)升序,部分(deptId)降序,发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp WHERE age > 50 ORDER BY age ASC, deptId DESC;
--排序时都为降序
EXPLAIN SELECT age, deptId FROM t_emp WHERE age > 50 ORDER BY age DESC, deptId DESC;
--排序时,在前面的字段为常量时(非范围)
EXPLAIN SELECT age, deptId FROM t_emp WHERE age = 50 ORDER BY deptId, name;
EXPLAIN SELECT age, deptId FROM t_emp WHERE age = 50 AND deptId>10000 ORDER BY deptId, name;
对应结果
结论:
要想在排序时使用索引,避免 Using filesort,首先需要发生索引覆盖,其次
- ORDER BY 后面字段的顺序要和复合索引的顺序完全一致
- ORDER BY 后面的索引必须按照顺序出现,排在后面的可以不出现
- 要进行升序或者降序时,字段的排序顺序必须一致。不能一部分升序,一部分降序,可以都升序或者都降序
- 如果复合索引前面的字段作为常量出现在过滤条件中,排序字段可以为紧跟其后的字段
MySQL的排序算法
当发生 Using filesort 时,MySQL会根据自己的算法对查询结果进行排序
- 双路排序
- MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和 order by 列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
- 从磁盘取排序字段,在 buffer 进行排序,再从磁盘取其他字段
- 简单来说,取一批数据,要对磁盘进行了两次扫描,众所周知,I\O 是很耗时的,所以在 mysql4.1 之后,出现了第二种改进的算法,就是单路排序
- 单路排序
- 从磁盘读取查询需要的所有列,按照 order by 列在 buffer 对它们进行排序,然后扫描排序后的列表进行输出, 它的效率更快一些,避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间, 因为它把每一行都保存在内存中了
- 存在的问题:在 sort_buffer 中,方法 B 比方法 A 要多占用很多空间,因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量,导致每次只能取 sort_buffer 容量大小的数据,进行排序(创建 tmp 文件,多 路合并),排完再取取 sort_buffer 容量大小,再排……从而多次 I/O。也就是本来想省一次 I/O 操作,反而导致了大量的 I/O 操作,反而得不偿失
- 优化Using filesort
- 增大 sort_butter_size 参数的设置
- 不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的 1M-8M 之间调整
- 增大 max_length_for_sort_data 参数的设置
- mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data
- 提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总容量超出 sort_buffer_size 的概率就增大, 明显症状是高的磁盘 I/O 活动和低的处理器使用率。(1024-8192 之间调整)
- 减少 select 后面的查询的字段
- 查询的字段减少了,缓冲里就能容纳更多的内容了,间接增大了sort_buffer_size
- 增大 sort_butter_size 参数的设置
2、GROUP BY 优化
优化方式和 ORDER BY 类似,参考ORDER BY 的优化方式即可