【MySQL高级】查询截取分析-查询优化


摘要:查询截取分析主要包括查询优化、慢查询日志、批量数据脚本、Show Profile和全局查询日志。本篇文章只针对查询优化的的部分情况进行说明,第一使用小表驱动大表,使用INEXISTS进行案例分析;第二order by关键字优化,为排序使用索引;第三group by关键字优化,实质基本与order by关键字优化相同。

优化步骤

数据库优化四步骤:
① 慢查询日志的开启并捕获。
EXPLAIN + 慢SQL分析。
SHOW PROFILE 查询SQLMySQL服务器里面的执行细节和生命周期情况。
MySQL数据库服务器的参数调优。

小表驱动大表(INEXISTS

优化原则:小表驱动大表,即小的数据集驱动大的数据集。使用INEXISTS进行说明。

使用IN的情况:

当B表的数据集小于A表的数据集时,用IN优于EXISTS

1
2
3
4
select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id = B.id

使用EXISTS的情况:

当A表的数据集小于B表的数据集时,用EXISTS优于IN

1
2
3
4
select * from A where exists (select 1 for B where B.id = A.id)
等价于:
for select * from A
for select * from B where B.id = A.id

EXISTS语法:

1
SELECT .. FROM table WHERE EXISTS(subquery)

该语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUEFALSE)来决定主查询的数据结果是否予以保留。

EXISTS(subquery)只返回TRUEFALSE,因此子查询中的SELECT *也可以是SELECT 1 或者其他,官方说法是实际执行时会忽略SELECT清单,因此没有区别。

建表语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `TableA` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`locAdd` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='表A';
CREATE TABLE `TableB` (
`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=9 DEFAULT CHARSET=utf8 COMMENT='表B';

数据插入:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 插入表A和表B数据
INSERT INTO TableA(deptName,locAdd) VALUES('PD',11);
INSERT INTO TableA(deptName,locAdd) VALUES('HR',12);
INSERT INTO TableA(deptName,locAdd) VALUES('MK',13);
INSERT INTO TableA(deptName,locAdd) VALUES('MIS',14);
INSERT INTO TableA(deptName,locAdd) VALUES('FD',15);
INSERT INTO TableB(name,deptId) VALUES('z3',1);
INSERT INTO TableB(name,deptId) VALUES('z4',1);
INSERT INTO TableB(name,deptId) VALUES('z5',1);
INSERT INTO TableB(name,deptId) VALUES('w5',2);
INSERT INTO TableB(name,deptId) VALUES('w6',2);
INSERT INTO TableB(name,deptId) VALUES('s7',3);
INSERT INTO TableB(name,deptId) VALUES('s8',4);
INSERT INTO TableB(name,deptId) VALUES('s9',51);

使用INSQL

1
select * from TableB b where b.`deptId` in (select id from TableA a);

使用EXISTSSQL

1
select * from TableB b where exists (select 1 from TableA a where b.`deptId` = a.id);

mysql-query-optimization-01

排序优化(order by )

MySQL支持IndexFileSort两种方式的排序,Index是指扫描索引本身完成排序,FileSort是扫描文件内容进行排序,Index效率高于FileSort
建表语句:

1
2
3
4
5
CREATE TABLE tblA(
id int primary key not null auto_increment,
age INT,
birth TIMESTAMP NOT NULL
);

数据插入:

1
2
3
INSERT INTO tblA(age,birth) VALUES(28,NOW());
INSERT INTO tblA(age,birth) VALUES(27,NOW());
INSERT INTO tblA(age,birth) VALUES(26,NOW());

创建索引:

1
CREATE INDEX idx_A_ageBirth ON tblA(age,birth);

mysql-query-optimization-02
第一种情况和第二种情况,未产生文件排序;第三种情况和第四种情况因为创建的索引顺序为ageindex,未按照创建索引的顺序排序会导致查询时进行文件排序。

注:在进行ORDER BY 子句,尽量使用Index方式排序,避免使用FileSort方式排序。

mysql-query-optimization-03
以上四种情况,只有第三种没有用到FileSort文件排序,效率是最高的。第一种和第二种情况没有满足索引创建时的最佳左前缀原则,直接忽略了第一层索引,跳到第二层索引。第四种情况是因为索引要升序都升序,要降序都降序,有升有降,导致索引部分失效。

尽可能在索引列上完成排序操作,遵照索引创建时的最佳左前缀原则。

如果不在索引列上,FileSort文件排序有两种算法:双路排序和单路排序。

双路排序:取一批数据,要对磁盘进行两次扫描。具体操作是读取行指针和所排序的列,进行排序,然后扫描已经排序好的列表按照表中的值重新输出,从磁盘取出排序字段,在buffer进行排序,再从磁盘取其他数据。双路排序多适用于MySQL4.1之前。

单路排序:从磁盘读取查询需要的所有列,按照order by 列在buffer对它们进行排序,然后扫描排序后的列表进行输出。单路排序的效率更快一些,避免了第二次读取数据,并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中。

单路排序可能导致的问题:
单路排序比多路排序要多占很多空间,因为单路排序一次性取出所有的字段,会导致取出的数据总大小超出sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据进行排序,排完再取,再排…..从而导致多次I/O。本来想省一次I/O操作,反而导致了大量的I/O操作。

提高Order By的速度:
① 进行 Order By 时应避免使用select *,应该只查询需要的字段。这是因为当Query的字段大总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会使用改进后的算法-单路算法,否则会使用老算法-多路排序。查询少量的字段会使得占用的空间较小。
② 尝试提高sort_buffer_size。不管使用多路排序还是单路排序,两种算法的数据都有可能超出sort_buffer_size的容量,超出之后会创建tmp文件进行合并排序,导致多次I/O,所以应该尽可能的提高sort_buffer_size。当然,需要根据系统的能力去提高。
③ 尝试提高max_length_for_sort_data。不管使用哪种算法,提高这个参数都会提高效率,但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,会导致高的磁盘I/O活动和低的处理器使用。
为排序使用索引结论:
MySQL两种排序方式,文件排序和索引排序。
MySQL能为排序和查询使用相同的索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
KEY a_b_c(a,b,c)
order by 能使用索引最左前缀
-- ORDER BY a
-- ORDER BY a,b
-- ORDER BY a,b,c
-- ORDER BY a DESC,b DESC,c DESC

如果 WHERE 使用索引的最左前缀定义为常量,则order by 能使用索引
-- WHERE a = const ORDER BY b
-- WHERE a = const AND b = const ORDER BY c
-- WHERE a = const ORDER BY b,c
-- WHERE a = const AND b > const ORDER BY b,c

不能使用索引进行排序
-- ORDER BY a ASC,b DESC,c DESC /*排序不一致*/
-- WHERE g = const ORDER BY b,c /*丢失a索引*/
-- WHERE a = const ORDER BY c /*丢失b索引*/
-- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
-- WHERE a in(..) ORDER BY b,c /*对于排序来说,多个相等条件也是范围查询*/

mysql-query-optimization-04

分组优化(group by)

group by 实质是先排序后进行分组,遵照索引创建的最佳左前缀。
② 当无法使用索引列,增大max_length_for_sort_data参数的设置,增大sort_buffer_size参数的设置。
where高于having,能写在where限定的条件就不要在having限定。