本文正在参加「技术专题19期 漫谈数据库技术」活动
in和exists
建表:
create table student(id int, class_name varchar(10));
create table class (class_name varchar(10));
插入数据:
insert into student values
(1, "5-1"),(2, "6-1"),(3, "4-1"),
(4, "5-1"),(5,"6-1"),(6,"4-1"),(7,"6-1");
insert into class values("4-1"),("5-1");
查询语句:
select * from student where class_name in (select class_name from class);
select * from student where exists ( select 1 from class where class._class_name = student.class_name )
分析:
以上两条语句的执行结果都是一样的, 并且两条语句的语义都是为了等到student表中class_name值存在于
class表的所有数据, 可以根据explain语句看到, class表是被优先加载的, 然后才加载了student表,
对于以上的两种查询, 当class表的数据少于student表的数据时, in语句的执行效率会更高, 反之则exists
语句的执行效率会更高
对于exists语句, 可以理解为将主查询的数据, 放到子查询中做条件验证, 根据验证结果(true/false)来决定
主查询的数据结果是否得以保留
orderby语句(group by类似)
order by语句中有两种排序方式, 一种是通过索引进行排序, 一种是通过filesort文件内排序, 下面我们来谈一
下会使用索引排序的情况:
<1> 查询字段是索引的顺序字段, 并且没有where条件, orderby的顺序和索引顺序相同, 例如:
索引为idx_age_birth, 查询语句为select age, birth from test order by age, birth;
<2> where和orderby语句构成了索引的顺序使用, 并且where语句中没有使用索引的范围, 例如:
索引为idx_age_birth, 查询语句为select * from test where age = xxx order by birth;
如果age不是使用常量, 而是使用age > xxx的形式, 则会导致birth索引失效, 从而order by的执行会出现
文件内排序
<3> where和orderby语句中构成了索引的顺序使用, 并且索引使用的个数不是复合索引的全部, 例如:
索引为idx_age_birth, 查询语句为select * from test where age > 18 order by age;
因为age索引生效了, 虽然age使用范围导致了birth索引失效, 但是orderby中并没有出现birth, 而是age,
所以orderby会使用到索引
<4> 满足查询字段是索引字段或者where语句和orderby语句构成了索引的顺序使用的情况下, orderby必须统一
为升序或者降序, 如下:
explain select age, birth from test order by age desc,birth desc;
不会出现索引排序而是使用filesort排序的情况:
<1> 查询其它字段的情况下, orderby并没有跟where语句构成顺序使用:
索引为idx_age_birth, 查询语句为select id,age from test order by age;
上述会造成索引的失效, 造成文件内排序
<2> 混合排序, desc和asc一起使用:
explain select age,birth from test order by age asc,birth desc;
总结:
key(a, b, c)
<1> order by能使用索引最左前缀
order by a
order by a, b, c
order by a desc, b desc, c desc
<2> where中使用了索引常量, 并且满足最左前缀的情况, 则order by能使用索引
where a = "xxx" order by a, b, c
where a = "xxx" order by b, c
<3> 不能使用索引进行排序
order by a asc, b desc, c desc; // 排序不一致
where g = xxx order by b, c; // 丢失a索引
where a = xxx order by c; // 丢失b索引
where a = xxx order by a, d; // d不是索引的一部分
where a in (xx,xx,xx) order by b, c; // a是范围, 范围后面的索引失效, 索引导致b,c失效
filesort
当没有在索引列上进行排序的时候, 就会导致filesort文件内排序, 对于文件内排序来说, 有两种算法, 分别是
单路排序算法和双路排序算法:
双路排序:
MySQL4.1之前是使用双路排序, 字面意思就是两次扫描磁盘, 最终得到数据, 读取行指针和orderby列, 对他们
进行排序, 然后扫描已经排序号的列表, 按照列表中的值重新从列表中读取对应的数据, 读取排序字段, 在buffer
中进行排序, 再从磁盘取其它字段(进行了两次磁盘扫描)
单路排序:
从磁盘读取查询需要的所有列, 按照orderby列在buffer对他们进行排序, 然后扫描排序后的列表进行输出, 它
的效率更快一些, 避免了第二次读取数据, 并且把随机IO变成了顺序IO, 但是它会使用更多的空间, 因为它把
每一行都保存在内存中了
单路排序的问题:
在sort_buffer中, 方法B比方法A要多占用很多空间, 因为方法B是把所有字段都取出, 所以有可能取出的数据
的总大小超出了sort_buffer的容量, 导致每次只能取sort_buffer容量大小的数据, 进行排序(创建temp文件,
多路合并), 排序完再取出sort_buffer容量大小, 再继续排序, 从而多次IO, 单路排序是为了省一次排序,但是
在该情况下反而导致了大量的I/O操作, 反而得不偿失, 总的来说, 就是单路排序一下子就会把所有数据进行
读取, 而不是只读取一部分
优化策略:
<1> 增大sort_buffer_size参数的值
<2> 增大max_length_for_sort_data参数的值
提高order by的速度
1、order by时select *是一个大忌, 应该只取query需要的字段, 因为在单路排序中会将select字段中的值所
有取出来
1.1 当query的字段大小总和小于max_length_for_sort_data而且排序字段不是text/blob类型时, 会用改进
后的算法-单路排序, 否则用老算法-多路排序
1.2 两种算法的数据都有可能超出sort_buffer的容量, 超出之后, 会创建tmp文件进行合并排序, 导致多次
I/O, 但是用单路排序算法的风险会更大一些, 所以要提高sort_buffer_size
2、尝试提高sort_buffer_size
不管用哪种算法, 提高这个参数都会提高效率, 当然, 要根据系统的能力去提高, 因为这个参数是针对每个进程的
3、尝试提高max_length_for_sort_data
提高这个参数, 会增加用改进算法的概率, 因为当查询的字段的大小低于该字段值时则用单路排序, 但是如果
设置的太高, 数据总容量超出sort_buffer_size的概率就增大, 明显症状是高的磁盘I/O活动和低的处理器使
用率
group by: 等同于order by
group by实质是先排序后进行分组, 按照索引键的最佳左前缀
当无法使用索引列, 增大max_length_fro_data参数的设置 + 增大sort_buffer_size参数的设置
where高于having, 能写在where限定的条件就不要去having限定了
