一般优化SQL查询的步骤：
- 观察，至少跑1天，看看生产的慢SQL的情况
- 开启慢查询日志，设置阙值，比如超过5秒的就是慢SQL，并将它抓取出来
- explain + 慢SQL分析
- show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
- 运维经理 or DBA 进行数据库服务器的参数调优
一、查询优化
1. 永远小表驱动大表（类似嵌套循环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字段应建立索引
- exists：
select … from table where exists(subquery)
该语法可理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据是否得以保存- 提示
- exists(subquery)只返回TRUE或FALSE，因此子查询中的select *也可以是select 1，官方的说法是实际执行会忽略 select 清单，因此没有区别
- exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
- exists子查询往往也可以用条件表达式、其他子查询或join来替代，何种最优需要具体问题具体分析。
2. order by 关键字优化
1） order by 子句，尽量使用Index方式排序，避免使用FileSort方式排序
①建表sql：MySQL高级篇（高阳）建表sql语句大全
②MySQL支持两种方式的排序，filesort和index，index效率高，它指MySQL扫描索引本身完成排序。filesort方式效率低
③order by满足两种条件，会使用index方式排序：
—— a. order by语句使用索引最左前列（同升同降）
—— b. 使用where子句与order by 子句条件列组合满足索引最左前列
2） 尽可能在索引上完成排序操作，遵照索引建的最佳左前缀
3）如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
①双路排序：
MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据
读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中获取其他数据
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
②单路排序：
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后列表输出，它的效率会更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是这种算法会使用跟多的空间，因为他的每一行都保存在了内存。
③结论及引申出的问题：
由于单路是后出的，总体而言好过多路
使用单路的问题：一次IO没办法把数据全部读完，导致需要多次IO读取
4）优化策略
①order by 是select * 是一个大忌，只Query 需要的字段，这点非常的重要。在这里的影响是：
- 当query的字段大小总和小于max_length_for_sort_data而且排序字段你不是TEXT|BLOB类型时，使用单路排序，否则使用多路排序
- 两种算法的数据都有可能超过sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是使用单路排序算法的风险更大一些，所以需要提高sort_buffer_size。
②尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
③尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率，但如果设得太高，数据总容量超出sort_buffer_size的概率就会增大，明显症状是高的磁盘IO活动和低的处理器使用率。
小总结：
- MySQL两种排序方式：文件排序和扫描有序索引排序
- MySQL能为排序与查询使用相同的索引
- order by能使用索引最左前缀
- 如果where使用索引的最左前缀定义为常量，则order by能使用索引
3. group by 关键字优化
①group by实质是先排序后分组，遵照索引建的最佳左前缀
②当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
③where高于having，能写在where的限制条件就不要去having限定了
二、慢查询日志
1）是什么
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应超过阙值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
- long_query_time的默认值是10，意思是运行10秒以上的语句
- 由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
2）怎么玩
①说明
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动去设置这个参数
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件
②查看是否开启及如何开启
- 默认：
SHOW VARIABLES LIKE '%slow_query_log%'; - 开启：
set global slow_query_log = 1;
③那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？
- 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
- 查看long_query_time的值：
SHOW VARIABLES LIKE 'long_query_time';
- 修改：可以使用命令修改
set global long_query_time = 秒数，修改之后需要重新连接或新开一个会话才能看到修改值，或者使用SHOW global VARIABLES LIKE '%long_query_time%';命令查看， - 也可以在my.cnf参数里面配置
- 假如运行时间正好等于long_query_time的情况下，并不会被记录下来。也就是说，在mysql源码里是判断
大于long_query_time - 查看慢SQL：在存放慢SQL的文件中查看
- 查询当前系统中有多少条慢查询记录
show global status like '%Slow_queries%';
④配置版
在my.cnf配置：
在【mysqlid】下配置
slow_query_log=1;
slow_query_log_file=慢查询日志文件路径;
long_query_time=3;
log_output=FILE;
3）日志分析工具mysqldumpslow
- mysql提供了日志分析工具mysqldumpslow
- 查看mysqldumpslow的帮助信息
mysqldumpslow --help
| 可选项 | 说明 |
|---|---|
| s | 按照何种方式排序 |
| c | 按照访问次数排序 |
| l | 按照锁定时间排序 |
| r | 按照返回记录排序 |
| t | 按照查询时间排序 |
| al | 按照平均锁定时间排序 |
| ar | 按照平均返回记录排序 |
| at | 按照平均查询时间排序（默认值） |
| t | 即为返回前面多少条数据 |
| g | 后边可以搭配一个正则匹配模式，大小写不敏感 |
-实例：
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 日志文件路径
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 日志文件路径
得到按照时间排序的前10条里面含有左查询的查询语句
mysqldumpslow -s t -t 10 -g "left join" 日志文件路径
另外建议在使用这些命令时结合|和more使用，否则可能出现爆屏情况
mysqldumpslow -s t -t 10 -g "left join" 日志文件路径 |more
三、批量数据脚本
1.建表
2.设置参数log_bin_trust_function_creators
创建函数，假如报错：This function has none of DETERMINISTIC
由于我们开过慢查询日志，因为我们开启了bin-log，我们必须为我们的function指定一个参数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
这样添加参数后，如果mysql重启，上述参数又会消失，永久方法
- windows下
my.ini[mysqld]加上log_bin_trust_function_creators=1 - linux下
/etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1
3.创建函数，保证每条数据不同
- 随机产生字符串
- 随机产生部门编号
MySQL高级篇（高阳）建表sql语句大全，第八点
4.创建存储过程
- 创建往emp表中插入数据的存储过程
- 创建往dept表中插入数据的存储过程
MySQL高级篇（高阳）建表sql语句大全，第八点
5.调用存储过程
- dept：
call insert_dept(100,10); - emp:
call insert_emp(10000,500000)
四、Show Profiles
1.是什么：
- 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
- 官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
- 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
2.分析步骤
- 是否支持，看看当前的mysql版本是否支持
show variables like 'profiling';
- 开启功能，默认是关闭的，使用前需要开启
set profiling=on;
- 运行SQL
select * from emp group by id%10 limit15000;select * from emp group by id%20 order by 5
- 查看结果，show profiles;
- 诊断SQL，
show profile cpu,block io for query 上一步前面的问题SQL数字号码；
| 参数 | 说明 |
|---|---|
| ALL | 显示所有的开销信息 |
| BLOCK IO | 显示块IO相关开销 |
| CONTEXT SWITCHES | 上下文切换相关开销 |
| CPU | 显示CPU相关开销信息 |
| IPC | 显示发送和接收相关开销信息 |
| MEMORY | 显示内存相关开销信息 |
| PAGE FAULTS | 显示页面错误相关开销信息 |
| SOURCE | 显示和Source_ function， Source_ file， Source_ line相关的开销信息 |
| SWAPS | 显示交换次数相关开销的信息 |
- 日常开发需要注意的结论
- converting HEAP 统MyISAM查询结果太大，内存不够用了，往磁盘上搬
- Creating tmp table创建临时表（拷贝数据到临时表，用完再删除）
- Copying to tmp table on disk，把内存中临时表复制到磁盘，危险！！！
- locked
五、全局查询日志（切记：在测试环境中使用）
永远不要在生产环境开启这个功能
1.通过配置启用
在mysql的my.cnf中，设置如下：
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
2.通过编码启用
#开启
set global general_log = 1;
#输出格式
set global log_output = 'table';
#此后，你说编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
