简单MySQL教程五

一、查询优化

1、永远小表驱动大表

EXISTS

SELECT ... FROM table WHERE EXISTS (subquery)

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

提示

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

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

3 EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代,何种最优需要具体问题具体分析

2、order by关键字优化

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

以下两种情况会使用Index排序：

• order by语句使用索引最左前列.

• 使用where子句与order by子句条件列组合满足最左前列.

尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀,如果不在索引列上,filesort有两种算法：双路排序和单路排序

由于单路是后出的,总体而言好过双路

优化策略

• 增大sort_buffer_size参数的设置

• 增大max_length_for_sort_data参数的设置

提高Order By的速度

1. Order by时select * 是一个大忌,只Query需要的字段, 这点非常重要.在这里的影响是：

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活动和低的处理器使用率.

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值的SQL,则会被记录到慢查询日志中.long_query_time的默认值为10,意思是运行10秒以上的语句.

2、查看是否开启(默认不开启)：

SHOW VARIABLES LIKE '%slow_query_log%';

开启慢查询：使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,

如果要永久生效,要修改配置文件,在[mysqld]下增加参数：

slow_query_log =1

slow_query_log_file=/var/lib/mysql/slow.log

3、哪些参数会被记录到慢查询日志

这是由long_query_time控制,默认是10,即大于10秒的sql会被记录下来.

show variables like 'long_query_time%';查看

4、通过set global long_query_time=3;设置阙值时间,

5、查询当前系统中有多少条慢sql

show global status like '%Slow_queries%';

6、配置慢查询

在【mysqld】下配置：

slow_query_log=1;

slow_query_log_file=/var/lib/mysql/slow.log

long_query_time=3;

log_output=FILE