优化SQL查询

分析SQL查询慢的方法

  • 记录慢查询日志(分析慢查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和精力,可以使用pt-query-digest工具进行分析)
  • 使用show profile(set profiling=1;开启,服务器上执行的所有语句会检测消耗的时间,存到临时表中)

流程:

1
2
3
4
5
6
7
SET profiling=1;

SELECT xxx FROM xxx // 查询语句...

SHOW PROFILES; // 查看查询语句的Query_ID

SHOW PROFILE FOR QUERY {Query_ID};

  • 使用show status:show status: 会返回一些计数器,show global status查看服务器级别的所有计数。有时根据这些计数,可以猜测出哪些操作代价比较高或者消耗时间多
  • 使用show processlist: 观察是否有大量线程处于不正常的状态或者特征
  • 使用explain(分析单条SQL语句)

优化查询过程中的数据访问

  • 访问数据太多导致查询性能下降
  • 确定应用程序是否在检索大量超过需要的数据,可能是太多行或列
  • 确认MYSQL服务器是否在分析大量不必要的数据行
  • 避免使用如下SQL语句
    • 查询不需要的记录,使用LIMIT解决
    • 多表关联返回全部列,制定部分列 如:A.a_id,B.b_name
    • 总是去除全部列,SELECT * 会让优化器无法完成索引覆盖扫描的优化
    • 重复查询相同的数据,可以缓存数据,下次直接读取缓存
  • 是否在扫描额外的记录,使用explain来进行分析,如果发现查询需要扫描大量的数据,但只返回少数的行,可以通过如下优化:
    • 使用索引覆盖扫描,把所有用的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果
    • 改变数据库和表的结构,修改数据表范式
    • 重写SQL语句,让优化器可以以更优的方式进行查询
  • 使用一个查询还是多个简单查询?
    • MYSQL内部每秒能扫描内存中上百万行数据,相比之下,相应数据给客户端就要慢得多
    • 使用尽可能少的查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要的
  • 优化长难的查询语句
    • 切分查询: 将一个大的查询分为多个小的相同的查询:一次性删除1000万的数据要比一次删除1万,暂停一会的方案更加损耗服务器性能
    • 分解关联查询(提高查询效率,较少冗余记录查询): 可以将一条关联语句分解成多条SQL来执行,让缓存的效率更高;执行单个查询可以减少锁的竞争;在应用层做关联可以更容易对数据库进行才分

优化特定类型的查询语句

  • 优化count()查询
    • count()中的会忽略所有的列,直接统计所有列数,因此不要使用count(列名)
    • MyISAM中,没有任何WHERE条件的count(*)非常快,当有WHERE条件,MyISAM的count统计不一定比其他引擎快
    • 可以使用explain查询近似值,用近似值替代count(*)
    • 增加汇总表
    • 使用缓存
  • 优化关联查询
    • 确定ON或者USING字句的列上有索引
    • 确保GROUP BY 和ORDER BY 中只有一个表中的列,这样MYSQL才有可能使用索引
  • 优化子查询: 尽可能使用关联查询来替代
  • 优化GROUP BY 和 DISTINCT
    • 这两种查询均可使用索引来优化,是最有效的优化方法
    • 关联查询中,使用标识列进行分组的效率会更高
    • 如果不需要ORDER BY, 进行GROUP BY时使用ORDER BY NULL,MYSQL不会再进行文件排序
  • 优化LIMIT分页
    • LIMIT偏移量大的时候,查询效率较低
    • 可以记录上次查询的最大ID,下次查询时直接根据该ID来查询
  • 优化UNION查询: UNION ALL的效率高于UNION