1、查询截取优化

SQL调优过程：

  1. 观察，至少跑1天，看看生产的慢SQL情况。
  2. 开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
  3. explain + 慢SQL分析。
  4. show profile。
  5. 运维经理 or DBA，进行SQL数据库服务器的参数调优。

总结：

  • 慢查询的开启并捕获
  • explain + 慢SQL分析
  • show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
  • SQL数据库服务器的参数调优。

 

1.1、in 和 exists（小表驱动大表）

在查询的优化中永远小表驱动大表。

举例说明：

for(int i=5;.......)
{
     for(int j=1000;......)
     {}
}

如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作；但如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

 

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

即B表的数据集小于A表的数据集时，用 select ... from A where ... in (selct ... from B) 的语法。如下：

select * from A where id in (select id from B);
等价于:
for select id from B
for select * from A where A.id = B.id

 

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

即A表的数据集小于B表的数据集时，用 select ... from A where exists (selct ... from B where ...) 的语法。如下：

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

如上所述，在查询中永远遵循小表驱动大表的原则。

 

1.1.1、判断驱动表与非驱动表

  • 1 LEFT JOIN 左连接,左边为驱动表,右边为被驱动表.
  • 2 RIGHT JOIN 右连接,右边为驱动表,左边为被驱动表.
  • 3 INNER JOIN 内连接, mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表.
  • 4 可通过EXPLANIN查看SQL语句的执行计划,EXPLANIN分析的第一行的表即是驱动表.

 

1.2、order by 优化

MySQL支持二种方式的排序：FileSort和Index

  • Index效率高，它指MySQL扫描索引本身完成排序
  • FileSort方式效率较低。应尽量使用Index方式排序，避免使用FileSort方式排序。

当 ORDER BY满足以下情况时，会使用Index方式排序：

  1. ORDER BY语句遵循索引的最左前缀原则，并且中间兄弟不能断。
  2. where 子句和 order by 子句拼起来符合最左前缀，并且 where 子句中的条件是常量

 

当出现以下情况时，会使用 FileSort 排序：

  1. 不满足最左前缀原则
  2. 多字段之间的排序规则不一致，比如a是降序，b是升序。只要索引的字段的排序规则都一样，不管是升序还是降序，都能使用 Index 排序。
  3. 用于排序的字段不属于索引

 

举例说明，假设给表 tblA 建索引 tblA(age, birth)，下面的 order by 语句遵循最左前缀原则，所以能使用 Index 方法进行排序，如下：

mysql提升查询效率 mysql查询调优_mysql

 

下面 where 和 order by 子句都不遵循最左前缀原则，所以只能使用 FileSort 方式进行排序，如下：

mysql提升查询效率 mysql查询调优_mysql_02

 

或者，where 子句和 order by 子句拼起来符合最左前缀，并且 where 子句中的条件是常量，此时排序也会使用 Index 进行排序的，如下：

mysql提升查询效率 mysql查询调优_mysql提升查询效率_03

 

1.3、group by优化

Group By 的优化和 order by差不多，group by 实质上是先排序后进行分组，也需遵照索引的最佳左前缀。

where高于having，能写在where限定的条件就不要去having限定了。

 

2、慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

通过慢查询日志文件可以查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟就算慢SQL，我们就可以收集到超过5秒的sql，结合 explain 进行全面分析。

 

2.1、开启慢查询日志

默认情况下，MySQL数据库并没有开启慢查询日志的记录，需要我们手动来设置这个参数。

我们可以通过以下命令来查看是否有开启慢查询日志：

-- 查看日志是否开启
SHOW VARIABLES LIKE '%slow_query_log%';

查询结果如下：

mysql提升查询效率 mysql查询调优_mysql_04

可以看到当前是未开启的，并且可以看到慢查询日志文件的目录是在 /var/lib/mysql/192-slow.log 下。该文件是系统默认指定的，格式为：host_name-slow.log。

 

要想开启慢查询日志，可通过如下命令进行操作：

-- 开启慢查询日志
set global slow_query_log=1  -- 该命令只对当前数据库生效

要想永久生效，需要修改配置文件 my.cnf，在 [mysqld] 下增加或修改参数 slow_query_log 和 slow_query_log_file ，然后需重启MySQL服务器，修改如下：

slow_query_log =1
slow_query_log_file=/var/lib/manongyanjiuseng-slow.log

不过一般我们也不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

 

2.2、设置慢SQL时间阈值

判定某条 SQL 是否算是慢 SQL，是否会放到慢查询日志文件上，这个是由参数 long_query_time 控制的，默认情况下，long_query_time 的值为10秒。

SQL 的执行时间只有大于 long_query_time 的值时才会被记录，小于或者等于时并不会被记录。

可通过以下命令查看时间阈值：

SHOW VARIABLES LIKE 'long_query_time%';

mysql提升查询效率 mysql查询调优_mysql提升查询效率_05

可通过以下命令设置时间阈值：

set global long_query_time=3;   -- 设置为 3 秒

设置完后，需要重启一下命令行窗口或者打开新窗口才可以看到设置后的效果。

 

也可以在配置文件中设置阈值，配置如下：

#[mysqld]下配置:
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE;

 

2.3、查询慢SQL

可通过以下命令来查询当前系统中有多少条慢查询记录：

show global status like '%Slow_queries%';

mysql提升查询效率 mysql查询调优_mysql_06

 

我们可以通过 select sleep(xx秒); 命令来模拟一个执行时间为 xx 秒的 SQL，以此来验证上面的命令。如下：

mysql提升查询效率 mysql查询调优_mysql提升查询效率_07

 

2.4、使用 mysqldumpslow 工具分析慢SQL

在生产环境中，如果要手工地去分析日志，查找、分析SQL，这显然是个体力活，由此 MySQL 提供了日志分析工具mysqldumpslow，我们可以通过该工具来分析慢 SQL 。

mysqldumpslow 命令的参数如下：

  • -s, 是sort的意思，表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；
  • -t, 是top n的意思，即为返回前面多少条的数据；
  • -g, 是grep的意思，后边可以写一个正则匹配模式，大小写不敏感的；

 

例子：

  • mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log：得到返回记录集最多的10个SQL 
  • mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log：得到访问次数最多的10个SQL
  • mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log：得到按照时间排序的前10条里面含有左连接的查询语句

另外建议在使用这些命令时结合│和more 使用，不然可能会出现数据量多大不断刷屏的情况，mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more

 

3、show profiles（分析SQL执行情况）

Show Profile 是 mysql 提供的可以用来分析当前会话中 sql 语句执行的资源消耗情况的工具，可用于 sql 调优的测量。

show profile 默认是处于关闭状态的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。默认也只保存最近15次的运行结果。

我们可以通过下面语句来查看是否开启：

show variables like 'profiling';

如未开启，则可以通过以下命令开启：

set profiling=on;

如下图，OFF 为未开启状态，ON 为已开启状态。

mysql提升查询效率 mysql查询调优_mysql提升查询效率_08

 

mysql提升查询效率 mysql查询调优_慢查询_09

 

3.1、show profiles的使用

我们可以随便执行一些 SQL 语句，然后通过 show profiles 来查看这些语句的执行情况。示例如下：

mysql提升查询效率 mysql查询调优_mysql_10

说明如下：

  • Query_ID：每条SQL语句都有一个query id
  • Duration：该SQL语句的执行消耗时间，单位为秒

 

3.1.1、show profiles的参数

可以通过使用一些参数来针对指定的SQL进行分析，如下：

show profile cpu,block io for query Query_ID;  -- Query_ID为上图中show profiles列表中的Query_ID

示例如下：

mysql提升查询效率 mysql查询调优_SQL_11

 

show profile 的常用查询参数如下：

  • ALL：显示所有的开销信息。
  • BLOCK IO：显示块IO开销。
  • CONTEXT SWITCHES：上下文切换开销。
  • CPU：显示CPU开销信息。
  • IPC：显示发送和接收开销信息。
  • MEMORY：显示内存开销信息。
  • PAGE FAULTS：显示页面错误开销信息。
  • SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
  • SWAPS：显示交换次数开销信息。

 

如果在show profile诊断结果中出现了以下4条结果中的任何一条，则sql语句需要优化。

  • converting  HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
  • Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
  • Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
  • locked

 

4、全局查询日志（切勿在生产环境开启该功能）

全局查询日志功能会记录下你所查询的所有SQL语句。但非常不建议在生产环境上开启该功能，因为所有的操作都会被记录下来，记录的日志量会非常大，对磁盘是一个很大的压力，对MySQL数据的性能有一定的影响。

可以通过修改配置文件或者直接执行命令来开启记录全局查询日志的功能。

  • 以修改配置文件的方式开启

在配置文件 my.cnf 文件中添加以下配置：

#开启记录全局日志的功能
general_log=1
#记录全局日志的文件的路径
general_log_file=/path/logfile
#输出格式：FILE-以文件格式记录 TABLE-记录在数据库表general_log中
log_output=FILE

 

  • 直接执行命令来开启

可以通过以下命令来开启记录全局查询日志的功能：

set global general_log = 1;
set global log_output = 'TABLE';  -- 配置日志记录在数据库表中

示例如下：

mysql提升查询效率 mysql查询调优_SQL_12

 

当开启并配置记录在数据库表中后，所有的SQL执行语句都会被记录在 mysql.general_log 表中，可以通过查看该表数据来查看到所有的SQL语句。