性能优化手段

调优步骤：

1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
4. SQL数据库服务器的参数调优 -> DBA or 运维经理，像是join buffer，sort_buffer_size和max_length_for_sort_data

in 和 exists优化

使用原则：小表驱动大表，也就是尽量让小表出现在explain id靠前的位置

EXISTS 语法

1

SELECT ... FROM table WHERE EXISTS(subquery)

该语法可以理解为：将主查询的数据结果，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。

• EXISTS(subquery) 只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别
• EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
• EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

用法演示

1
2
3
4

# in
mysql> select * from tbl_emp e where e.deptId in (select id from tbl_dept);
# exists
mysql> select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);

使用原则

结论：永远记住小表驱动大表 当 A 表数据集大于 B表数据集时，使用 in 当 A 表数据集小于 B 表数据集时，使用 exists

ORDER BY 优化

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

• 索引：

1

create index idx_A_ageBirth on tblA(age, birth);

• tblA 表中的测试数据

1
2
3
4
5
6
7
8
9

mysql> select * from tblA;
+------+---------------------+
| age  | birth               |
+------+---------------------+
|   22 | 2020-08-05 10:36:32 |
|   23 | 2020-08-05 10:36:32 |
|   24 | 2020-08-05 10:36:32 |
+------+---------------------+
3 rows in set (0.00 sec)

• tbl 中的索引

1
2
3
4
5
6
7
8

mysql> SHOW INDEX FROM tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tblA  |          1 | idx_A_ageBirth |            1 | age         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| tblA  |          1 | idx_A_ageBirth |            2 | birth       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

CASE1：能使用索引进行排序的情况

• 只有带头大哥 age

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT * FROM tblA where birth>'2016-01-28 00:00:00' order by age; +----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)

• 带头大哥 age + 小弟 birth

  1 2 3 4 5 6 7

  mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)

• mysql 默认升序排列，全升序或者全降序，都扛得住

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC; +----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+ | 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index | +----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC; +----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+ | 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index | +----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+ 1 row in set (0.01 sec)

CASE2：不能使用索引进行排序的情况

• 带头大哥 age 挂了

  1 2 3 4 5 6 7

  mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth; +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ 1 row in set (0.01 sec)

• 小弟 birth 居然敢在带头大哥 age 前面

  1 2 3 4 5 6 7

  mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth,age; +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ 1 row in set (0.00 sec)

• mysql 默认升序排列，如果全升序或者全降序，都 ok ，但是一升一降 mysql 就扛不住了

  1 2 3 4 5 6 7

  mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC; +----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index; Using filesort | +----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)

结论

1. MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低。
2. ORDER BY满足两情况（最佳左前缀原则），会使用Index方式排序
   • ORDER BY语句使用索引最左前列
   • 使用where子句与OrderBy子句条件列组合满足索引最左前列
3. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

双路排序和单路排序

如果未在索引列上完成排序，mysql 会启动 filesort 的两种算法：双路排序和单路排序

1. 双路排序
   • MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和将要进行orderby操作的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
   • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
2. 单路排序
   • 取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了改进的算法，就是单路排序。
   • 从磁盘读取查询需要的所有列，按照将要进行orderby的列，在sort buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
3. 结论及引申出的问题：
   • 由于单路是改进的算法，总体而言好过双路
   • 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 从而会导致多次I/O。
   • 结论：本来想省一次I/O操作，反而导致了大量的/O操作，反而得不偿失。
4. 更深层次的优化策略：
   • 增大sort_buffer_size参数的设置
   • 增大max_length_for_sort_data参数的设置

遵循如下规则，可提高Order By的速度

1. Order by时select *是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是：
   • 当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
   • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
2. 尝试提高 sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
3. 尝试提高max_length_for_sort_data提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

Order By 排序索引优化的总结

GROUP BY 优化

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

group by 语法

1
2
3
4

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

group by 优化

1. group by实质是先排序后进行分组，遵照索引的最佳左前缀
2. 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置，提高Filesort的效率
3. where高于having，能写在where限定的条件就不要去having限定了
4. 其余的规则均和 order by 一致

统计类工作是在数据库完成还是在内存完成？

尽量在数据库完成，可以减少IO传输，并且数据综合查询本来就是数据库干的活，那么多高级 sql 特性应该比自己在内存中使用算法更高效，如果怕影响服务就1.增加硬件资源，2.在从服务器，备份服务器操作，3.在人少的时候操作（报表统计工作），4.优化查询