【MySQL进阶】MySQL 查询优化

MySQL 查询优化

1、小表驱动大表，即小的数据集驱动大的数据集

user 表的字段如下：

create table user (
 id int not null primary key auto_increment,
 name varchar(30) not null) default charset=utf8;

user 表的插入数据如下：

 insert into user(name) values('张三');
 insert into user(name) values('李四');
 insert into user(name) values('王五');
 insert into user(name) values('小二');

role 表的字段如下：

create table role(
id int(10) not null primary key auto_increment,
user_id int(10),
dept_id int(10),
role_name varchar(30) not null
) default charset=utf8;

role 表插入的数据如下：

insert into role(user_id,dept_id,role_name) values(1,1,'教授');
insert into role(user_id,dept_id,role_name) values(1,2,'老师');
insert into role(user_id,dept_id,role_name) values(2,3,'会计');
insert into role(dept_id,role_name) values(4,'搬砖');
insert into role(role_name) values('2000');
insert into role(role_name) values('学生');
insert into role(role_name) values('司机');
insert into role(role_name) values('1');  ;
insert into role(role_name) values('2');
insert into role(role_name) values('3');
insert into role(role_name) values('4');

在 role 表的 user_id 上建立索引：

alter table role add index index_userid(user_id);

① 第一种子查询连接语句：

select * from A where id in (select id from B);

相当于：
    for select id from B
    	for select * from A where A.id=B.id

当 B表 的数据集小于A表 的数据集时，用 in 优先于 exists。

例如:

select * from role where user_id in (select id from user);

可以通过 explain 执行计划来证明（ role表的记录数 > user 表的记录数 ，优先使用 in 进行子查询）。

explain select * from role where user_id in (select id from user);

explain select * from role where exists (select 1 from user where user.id=role.user_id);

通过分析可以证明结论：当 B表 的数据集小于A表 的数据集时，用 in 优先于 exists。

② 第二种子查询连接语句

select * from A where exists (select 1 from B where A.id=B.id)

相当于：
	for select * from A
		for select * from B where A.id=B.id

该语句可以理解为：
	将主查询的结果集，放到子查询中作为查询条件验证，根据验证结果（true 或者 false）来决定主查询的结果是否保留。

说明：

​ 1）exists（subquery）只返回 true 或者 false，因此子查询中的select * 也可以是 select 1 ，select id 等等，因为实际执行时会忽略 select 清单；

​ 2）exists 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可以进行实际检验确定是否存在效率问题；

​ 3）exists 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来代替，何种最有具体问题具体分析。

例如：

select * from user where exists (select 1 from role where role.user_id=user.id);

可以通过 explain 执行计划来证明（ user 表的记录数 < role表的记录数，优先使用 exists 进行子查询）。

explain select * from user where exists (select 1 from role where role.user_id=user.id);

explain select * from user where id in (select user_id from role);

通过分析可以证明结论：当 A表 的数据集小于 B表 的数据集时，用 exists 优先于 in。

2、Order By 关键字优化

Order By 子句尽量使用 Index 方式排序，避免 FileSort 方式排序。

Index 方式排序效率高，FileSort 方式排序效率低。

Order By 满足一下两种情况，会使用 Index 方式排序：

1）Order By 子句使用索引最佳左前缀原则（如果只用到 Order By 子句，那么在这之前的前提：要使用到覆盖索引）；

2）Where 子句和 Order By 子句使用索引最佳左前缀原则。

例如：

// 只使用到 Order By 子句时，没有用到覆盖索引
explain select * from role order by user_id,dept_id;
explain select * from role order by user_id;

// 只用到 Order By 子句，同时也使用到了覆盖索引
explain select user_id,dept_id from role order by user_id,dept_id;
explain select user_id from role order by user_id,dept_id;

// 同时用到了 Where 子句和 Order By 子句
explain select * from role where user_id>1 order by user_id;
explain select * from role where user_id>1 order by user_id,dept_id;

// 在 Order By 满足前两种情况中的一种或两种的前提下，只有当都按照索引列的升序或者降序排列，才会使用 Index 方式排序
explain select user_id from role order by user_id asc,dept_id desc;

补充

排序尽量在索引列上使用排序，如果不在索引列上使用排序，则会使用文件排序（FileSort）。

文件排序算法：

双路排序：根据相应的查询条件查询出该条记录所对应的行数据 id，即主键 id，然后将主键 id 和 排序字段 添加到 sort_buffer 中根据排序字段进行排序，然后根据排序好后的主键 id 列表，到表中读取出对应的数据输出。（MySQL 4.1 之前采用的双路排序，通过两次的磁盘 IO 来获取数据）

单路排序：根据相应的查询条件取出所有字段的值，然后将所取出的所有字段的值和 排序字段 添加到 sort_buffer 中，再根据排序字段进行排序，最后将排序后的结果返回。（单路排序算法效率更高一点，避免了第二的磁盘的读取，将随机 IO 变成了顺序 IO，但是占用的内存更多，因为它把查询出的所有字段的值都保存在了内存中）

注意！！：由于单路排序是将所有字段都取出来了，所以可能会超过 sort_buffer 的容量，这样每次就只能取 sort_buffer 容量大小的字段值然后进行排序（产生 temp 文件），然后再读取 sort_buffer 容量大小的字段值…从而造成多次磁盘读取。（因此单路排序相比于多路排序是危险较高的）

那有没有解决办法呢？？

有解决办法的：① 增大sort_buffer_size 参数设置；② 增大 max_length_for_sort_data 参数设置（该参数指的是某个表的所有列长度总和，默认值是1024字节），但是如果值设置得太大可能会导致数据总容量超出 sort_buffer_size 的概率增大。

至于 MySQL优化器是优先使用双路排序还是单路排序是有自己的判断的，比如：如果所查询的列的长度总和大于 max_length_for_sort_data，那么就会使用双路排序，反之使用单路排序。

3、Group By 关键字优化

1）Group By 实质是先排序后分组，所以需要遵循最佳左前缀原则；

2）当无法使用索引列的时候，适当增大 sort_buffer_size 参数设置以及 max_length_for_sort_data 参数设置；

3）Where 高于 having，能在 Where 中写的限定条件，就不要出现在 having 中了。