vlambda博客
学习文章列表

点击蓝色“潭时录”关注我丫

每天和小潭一起快乐的学习~

    你好，我是在学mysql的小潭。在上一期的推文中我们通过多种案例学习了索引失效的情况，进而了解了如何避免索引失效及索引优化的一些方法。今天，我们将学习有关查询截取的知识，此部分包含查询优化，慢查询日志，批量数据脚本，show profile和全局查询日志部分的讲解。

查询优化

    我们在先前的推文中学习到了explain分析工具的用法，但在具体的日常工作中，我们一般要通过以下步骤进行分析，笼统步骤：先慢查询的开启和查获，再explain+慢SQL分析，再show profile查询SQL在MySQL服务器里的执行细节和生命周期，最后SQL数据库服务器的参数调优。具体细化步骤如下：

1-观察，至少跑一天，来查看生产的慢SQL情况；

2-开启慢查询日志，设置阈值，比如超过5秒钟的即认为慢SQL，并将其抓取出来；

3-explain+慢SQL分析；

4-show profile更深层次分析；

5-运维经理或dba进行SQL数据库服务器的参数调优。

    在这之前，我们先事先再巩固学习下查询优化的相关知识。

小表驱动大表

    类似嵌套循环nested loop，案例：

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

原理（RBO）：

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 A where exists (select 1 from B where B.id = A.id);等价于：for select * from A for select * from B where B.id = A.id

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

注意：A表与B表的ID字段应建立索引。

总结：

*EXISTSSELECT .... FROM table WHERE EXISTS(subquery);

该语法可以理解为:

    将主查询的数据放到子查询中做条件验证，根据验证结果(TRUE或FALSE)，决定主查询的数据结果是否得以保留。

提示：

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

2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可以进行实际校验以确定是否有效率问题。

3.EXISTS子查询往往也可以用条件表达式，其他子查询或JOIN来代替，哪种最优需要根据具体问题具体分析。

案例：

效果演示：

性能比较：

order by关键字优化

1.ORDER BY子句，尽量使用index方式排序，避免使用FileSort方式排序

建表sql:

CREATE TABLE tblA( #id int primary key not null auto_increment, age INT, birth TIMESTAMP NOT NULL);
INSERT INTO tblA(age,birth) VALUE(22,NOW());INSERT INTO tblA(age,birth) VALUE(23,NOW());INSERT INTO tblA(age,birth) VALUE(24,NOW());
CREATE INDEX inx_A_ageBirth ON tblA(age,birth);
SELECT * FROM tblA;

案例1：

1.EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;2.EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;3.EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;4.EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;

案例2：

1.EXPLAIN SELECT * FROM tblA ORDER BY birth;2.EXPLAIN SELECT * FROM tblA WHERE birth > '2021-07-24' ORDER BY birth;3.EXPLAIN SELECT * FROM tblA WHERE birth > '2021-07-24' ORDER BY age;4.EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;

总结：

    Mysql支持两种方式的排序，FileSort和Index。Index效率高，它指Mysql扫描索引本身完成排序，FileSort方式效率较低。

    ORDER BY满足两种情况将使用Index方式排序：

    1.ORDER BY语句使用索引最左前列。

    2.使用Where子句与ORDER BY子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引键的最佳左前缀。

2.如果不在索引列上，FileSort有两种算法，mysql就要启动双路排序和单路排序，具体如下：

双路排序：MySQL 4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和order by列，对其进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。

    从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行两次扫描，此时I/O将很耗时，因此MySQL 4.1后出现第二种改进算法，即单路排序。

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

引申出的问题：由于单路是后出现的，总体会好过双路。但单路排序依旧存在问题，即在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取去取sort_buffer容量大小数据，再排....从而发生多次IO，即本来想省一次IO操作，却导致了大量的IO操作，得不偿失。

优化策略：

1.增大sort_buffer_size参数的设置

    两种算法下增大该参数都会提高效率，当然，要根据系统能力去提高，因为该参数是针对每个进程的。

2.增大max_length_for_sort_data参数的设置

    提高这个参数会增加用改进算法-单路排序的概率，但如果设置过高，数据总容量超出sort_buffer_size的概率就会增大，从而导致的症状是较高的磁盘IO活动和较低的处理器使用率。

总之在使用order by时，select *是一个大忌，建议只query需要的字段

    1)当query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法-单路排序，否则会使用老算法-多路排序。

    2)两种算法的数据都有可能超过sort_buffer的容量，超出之后会创建tmp文件进行合并排序，从而导致多次IO，但使用单路排序算法的风险更大一些，因此要提高sort_buffer_size。

小总结：

为排序使用索引

MySQL两种排序方式：文件排序(using_file_sort)或扫描有序索引排序(using_index)

MySQL能为排序与查询使用相同的索引

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,c- WHERE a=const AND b=const ORDER BY c- WHERE a=const AND b>const ORDER BY b,c
不能使用索引进行排序- ORDER BY a ASC, b DESC, c DESC  /*排序不一致*/- WEHRE d=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 /*对于排序来讲，多个相等条件也是范围查询*/

group by关键字优化

group by类似order by，实质是先排序后再进行分组，遵照索引键的最佳左前缀。

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。

where高于having，能写在where限定的条件不要使用having限定。

下期预告：慢查询日志