成都人源码站

MySQL 性能调优——SQL 查询优化

如何设计最优的数据库表结构,如何建立最好的索引,以及如何扩展数据库的查询,这些对于高性能来说都是必不可少的。但是只有这些还不够,要获得良好的数据库性能,我们还要设计合理的数据库查询,如果查询设计的很糟糕,即使增加再多的只读从库,表结构设计的再合理,索引再合适,只要查询不能使用到这些东西,也无法实现高性能的查询。所以说查询优化,索引优化,库表结构优化需要齐头并进。

在进行库表结构设计时,我们要考虑到以后的查询要如何的使用这些表,同样,编写 SQL 语句的时候也要考虑到如何使用到目前已经存在的索引,或是如何增加新的索引才能提高查询的性能。

想要对存在性能问题的查询进行优化,需要能够找到这些查询,下面先看下如何获取有性能问题的 SQL。

1.获取有性能问题的SQL

获取有性能问题的 SQL 的三种方法:

  • 通过用户反馈获取存在性能问题的 SQL;

  • 通过慢查日志获取存在性能问题的 SQL;

  • 实时获取存在性能问题的 SQL;

1.慢查询日志获取性能问题SQL

MySQL 慢查询日志是一种性能开销比较低的获取存在性能问题 SQL 的解决方案,其主要的性能开销在磁盘 IO 和存储日志所需要的磁盘空间。对于磁盘 IO 来说,由于写日志是顺序存储,开销基本上忽略不计,所以主要需要关注的还是磁盘空间。

MySQL 提供了以下参数用于控制慢查询日志:

slow_query_log:是否启动慢查询日志,默认不启动,on 启动;
slow_query_log_file:指定慢查询日志的存储路径及文件,默认情况下保存在 MySQL 的数据目录中;
long_query_time:指定记录慢查询日志 SQL 执行时间的阈值,单位秒,默认 10 秒,通常对于一个繁忙的系统来说,改为0.001秒比较合适;
log_queries_not_using_indexes:是否记录未使用索引的 SQL;

和二进制日志不同,慢查询日志会记录所有符合条件的 SQL,包括查询语句、数据修改语句、已经回滚的 SQL。

慢查询日志中记录的内容:

# Query_time: 0.000220             //执行时间,可以精确到毫秒,220毫秒
# Lock_time: 0.000120              //所使用锁的时间,可以精确到毫秒
# Rows_sent: 1                     //返回的数据行数
# Rows_examined: 1                 //扫描的数据行数
SET timestamp=1538323200;          //执行sql的时间戳
SELECT c FROM test1 WHERE id =100;    //sql

通常情况下,在一个繁忙的系统中,短时间内就可以产生几个 G 的慢查询日志,人工检查几乎是不可能的,为了快速分析慢查询日志,必须借助相关的工具。

常用的慢查询日志工具:

1、mysqldumpslow:一个常用的,MySQL 官方提供的慢查询日志分析工具,随着 MySQL 服务器的安装而被安装。可以汇总除查询条件外其他完全相同的 SQL,并将分析结果按照参数中所指定的顺序输出。

2、pt-query-digest:用于分析 MySQL 慢查询的一个工具。

2.实时获取性能问题SQL

为了更加及时的发现当前的性能问题,我们还可以通过实时的方法来获取有性能问题的 SQL。最方便的一种方法就是利用 MySQL information_schema 数据库下的 PROCESSLIST 表来实现实时的发现性能问题 SQL。例如下面这条 SQL 表示查询出当前服务器中执行时间超过 1 秒的 SQL:

SELECT id,user,host,db,command,time,state,info FROM information_schema.PROCESSLIST WHERE TIME>=1

然后我们可以通过脚本周期性的来执行这条 SQL,实时的发现哪些 SQL 执行的是比较慢的。

2.SQL的解析预处理及生成执行计划

找到了那些查询存在性能问题的 SQL,那么下面我们就看下,为什么这些 SQL 会存在性能问题?

为了搞清楚这个问题,我们先来看下 MySQL 服务器处理一条 SQL 请求所需要经历的步骤都有哪些:

1.客户端通过 MySQL 的接口发送 SQL 请求给服务器,这一步通常不会影响查询性能;
2.MySQL 服务器检查是否可以在查询缓存中命中该 SQL,如果命中,则立即返回存储在缓存中的结果,否则进入下一阶段;
3.MySQL 服务器进行 SQL 解析,预处理,再由 SQL 优化器生成对应的执行计划;
4.根据执行计划,调用存储引擎 API 来查询数据;
5.将结果返回给客户端。

这就是 MySQL 服务器处理查询请求的整个过程。在第二到第五步,都有可能对查询的响应速度造成影响,下面来分别看下这些过程可能对查询的响应速度有影响的因素都有些什么:

在解析查询语句前,如果查询缓存是打开的,那么 MySQL 优先检查这个查询是否命中查询缓存中的数据,这个检查是通过一个对大小写敏感的 Hash 查找实现的。由于 Hash 查找只能进行全值匹配,所以请求的查询和缓存中的查询就算只有一个字节的不同,那么也不会匹配到缓存中的结果,这种情况下,查询就会进入到下一阶段处理。如果正好命中查询缓存,在返回查询结果之前,MySQL 就会检查用户权限,也是无需解析 SQL 语句的,因为在查询缓存中,已经存放了当前查询所需要访问的表的信息,如果权限没有问题,MySQL 会跳过所有的其他阶段,直接从缓存中拿到结果,并返回给客户端,这种情况下查询是不会被解析的,也不会生成查询计划,不会被执行。

可以发现,从查询缓存中直接返回结果并不容易。

联系客服
网站客服 业务合作 Q交流群
8888888
返回顶部