一、查询优化
1、小表驱动大表
2、order by
排序优化
尽量使用index
方式排序,避免使用filesort
方式排序
使用index
排序的情况如下
使用filesort
方式排序的情况如下
MySQL支持两种方式排序,filesort
和index
。index
效率更高,它是MySQL扫描索引本身完成的排序。
order by
满足两种情况会使用index
排序:
order by
语句使用索引满足最佳左前缀原则where
条件字段和ORDER BY
子句组合起来,满足最佳左前缀法则
尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀顺序,如果不再索引列上,filesort
会使用双路排序或者单路排序算法完成排序。
双路排序
- 双路排序是指两次扫描磁盘,最终得到数据。
- 首先读取行指针和
order by
列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据并输出。 - 双路排序从磁盘读取排序字段,并在
buffer
中进行排序,再从磁盘读取其它字段。取一批数据,要对磁盘进行了两次扫描,IO很耗时。
单路排序
- 从磁盘读取查询所需要的所有列,按照
order by
列在buffer
中对它们进行排序,然后扫描排序后的列表进行输出。 - 单路排序效率更高,避免了第二次读取数据,并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中。
- 单路排序存在问题。在
sort_buffer
中,单路排序比双路排序要占用更多的空间,因为单路排序是把所有的字段全部取出来,所以有可能取出的数据总大小超过了sort_buffer
的容量,导致每次只能取sort_buffer
容量大小的数据来进行排序(创建tmp,多路合并),每次排序完后再取sort_buffer
容量大小的数据,再进行排序,只能所有数据排序完成,这样就会导致多次IO操作。
优化策略
排序总结
- MySQL有两种排序方式,文件排序和扫描有序索引排序。
- MySQL可以使用索引来进行查询或者排序。
order by
如果要使用索引排序,需满足最佳左前缀原则。- 如果
where
使用索引的最左前缀定义为常量,则order by
能使用索引。
3、group by
优化
group by
实质是先排序后分组,遵照索引建的最佳左前缀原则- 当无法使用索引列,增大
max_length_for_sort_data
参数的设置和增大sort_buffer_size
参数的设置 where
高于having
,尽量在where
中限定条件
二、慢查询日志
MySQL慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指的是运行时间超过long_query_time
值的SQL,则会被记录到慢查询日志中,long_query_time
默认值是10
。提供慢查询日志再结合explain
可以进行全面的分析。
日志分析工具mysqldumpslow
//--获取返回集最多的10条SQL
mysqldumpslow -s r -t 10 G:\Programs\mysql-8.0.18\data\layman-slow.log
//--获取访问次数最多的10条SQL
mysqldumpslow -s c -t 10 G:\Programs\mysql-8.0.18\data\layman-slow.log
//--获取按时间排序的前10条含有LEFT JOIN的SQL语句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" G:\Programs\mysql-8.0.18\data\layman-slow.log
//--结合|more使用,否则有可能会爆屏
mysqldumpslow -s r -t 10 G:\Programs\mysql-8.0.18\data\layman-slow.log |more
三、批量数据脚本
创建dept
和emp
两张表
创建函数,保证每条数据不同
随机产生字符串
随机产生部门编号
创建往emp
表中插入数据的存储过程
创建往dept
表中插入数据的存储过程
调用存储过程向dept
表中插入数据
调用存储过程向emp
表中插入50w条数据
四、使用show profile
进行SQL分析
show profile
是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量。
开启Show Profile
功能,默认是关闭的,使用前需要开启。
执行SQL语句
SELECT * FROM `emp` where `id`%20 = 0 ORDER BY 5;
SELECT * FROM `emp` where `id`%10 =0 LIMIT 150000;
查看结果,执行SHOW PROFILES;
诊断SQL,SHOW PROFILE cpu,block io FOR QUERY Query_ID;
可以在SHOW PROFILE
中看到一条SQL中完整的生命周期。
Show Profile
查询列表,日常开发需要注意的结论:
converting HEAP to MyISAM
:查询结果太大,内存都不够用了,往磁盘上搬了。Creating tmp table
:创建临时表(拷贝数据到临时表,用完再删除),非常耗费数据库性能。Copying to tmp table on disk
:把内存中的临时表复制到磁盘,危险!!!locked
:死锁。