MySQL性能优化(一)

  • 2015-12-24
  • 1400
  • 0

mysql

最近在看《高性能MySQL(第三版)》,发现知识繁多,再加上之前接触的关于MySQL的知识,因此将自认为比较重要的或是当前工作比较容易接触到的分设两点来进行整理:索引与查询优化、运维与架构优化。后期如有更深入的见闻,应该会另设文章,但不会再在此文赘述。

索引与查询优化

1、数据索引优化

1)为什么要使用数据索引

  • 《高性能MySQL(第三版)》中提出,索引优化应该是对查询性能优化最有效的手段了,它能够轻易将查询性能提高几个数量级。
  • 关系型数据库的数据索引(Btree及常见索引结构)的存储是有序的,及Btree通常意味着所有的值都是按顺序存储的。
  • 在有序的情况下,通过索引查询一个数据是无需遍历索引记录的。
  • 关系型数据库数据索引的查询效率趋近于二分法查询效率,趋近于 log2(N)。
  • 以上特性将加快了访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。

2)理解数据索引

  • 数据索引通常默认采用btree索引,(Memory引擎默认使用hash索引,当前工作基本使用InnoDB,因此hash索引先不整理)。
  • 在进行索引分析和SQL优化时,可以将数据索引字段想象为单一有序序列,并以此作为分析的基础。涉及到复合索引情况,复合索引按照索引顺序拼凑成一个字段,想象为单一有序序列,并以此作为分析的基础。
  • 一条数据查询只能使用一个索引,索引可以是多个字段合并的复合索引。但是一条数据查询不能使用多个索引。

3)优化案例

  • 目标:查找与访问者同一地区的异性,按照最后登录时间逆序
  • 挑战:高访问量社区的高频查询,如何优化。

          查询SQL: select * from user where area=’$area’ and sex=’$sex’ order by lastlogin desc limit 0,30;

          建立复合索引并不难, area+sex+lastlogin 三个字段的复合索引,如何理解?

  • 解读:首先,忘掉btree,将索引字段理解为一个排序序列。

另外,牢记数据查询只能使用一个索引,每个字段建立独立索引的情况下,也只能有一条索引被使用!

如果只使用area会怎样?搜索会把符合area的结果全部找出来,然后在这里面遍历,选择命中sex的并排序。 遍历所有 area=’$area’数据!

如果使用了area+sex,略好,仍然要遍历所有area=’$area’ and sex=’$sex’数据,然后在这个基础上排序!!

Area+sex+lastlogin复合索引时(切记lastlogin在最后),该索引基于area+sex+lastlogin 三个字段合并的结果排序,该列表可以想象如下。

广州女$时间1

广州女$时间2

广州女$时间3

广州男

….

深圳女

….

          数据库很容易命中到 area+sex的边界,并且基于下边界向上追溯30条记录,搞定!在索引中迅速命中所有结果,无需二次遍历!

2、认识影响结果集

1)影响结果集的获取

  • 通过Explain 分析SQL,查看 rows 列内容。
  • 通过慢查询日志的Rows_examined: 后面的数字。
  • 影响结果集数字是查询优化的重要中间数字,在开发和调试过程中,应随时关注这一数字。

2)影响结果集的解读

  • 查询条件与索引的关系决定影响结果集。
    • 影响结果集不是输出结果数,不是查询返回的记录数,而是索引所扫描的结果数。
    • 范例 select * from user where area=’厦门’and sex=’女’
      • 假设 索引为 area
      • 假设User表中 area=’厦门’的有 125000条,而搜索返回结果为60233条。
      • 影响结果集是125000条,索引先命中125000条厦门用户,再遍历以sex=’女’进行筛选操作,得到60233条结果。
      • 如果该SQL 增加 limit 0,30的后缀。查询时,先命中 area=’厦门’,然后依顺序执行 sex=’女’筛选操作,直到满足可以返回30条为止,所涉及记录数未知。除非满足条件的结果不足30条,否则不会遍历125000条记录。
      • 但是如果SQL中涉及了排序操作,比如 order by lastlogin desc 再有limit 0,30时,排序需要遍历所有area=’厦门’的记录,而不是满足即止。
    • 影响结果集越趋近于实际输出或操作的目标结果集,索引效率越高。
    • 影响结果集与查询开销的关系可以理解为线性相关。减少一半影响结果集,即可提升一倍查询效率!当一条搜索query可以符合多个索引时,选择影响结果集最少的索引。
    • SQL的优化,核心就是对结果集的优化,认识索引是增强对结果集的判断,基于索引的认识,可以在编写SQL的时候,对该SQL可能的影响结果集有预判,并做出适当的优化和调整。

3)优化案例

  • 论坛翻页优化
  • 背景,常见论坛帖子页 SQL: select * from post where tagid=$tagid order by lastpost limit $start, $end 翻页 。索引为 tagid+lastpost 复合索引
  • 挑战, 超级热帖,几万回帖,用户频频翻到末页,limit 25770,30 一个操作下来,影响结果集巨大(25770+30),查询缓慢。
  •  解决方法:
  •  只涉及上下翻页情况
  • 每次查询的时候将该页查询结果中最大的 $lastpost和最小的分别记录为 $minlastpost 和 $maxlastpost ,上翻页查询为 select * from post where tagid=$tagid and lastpost<$minlastpost order by lastpost desc limit 30; 下翻页为 select * from post where tagid=$tagid and lastpost>$maxlastpost order by lastpost limit 30; 使用这种方式,影响结果集只有30条,效率极大提升。
  •  涉及跳转到任意页
    • 互联网上常见的一个优化方案可以这样表述,select * from post where tagid=$tagid and lastpost>=(select lastpost from post where tagid=$tagid order by lastpost limit $start,1) order by lastpost limit 30; 或者 select * from post where pid in (select pid from post where tagid=$tagid order by lastpost limit $start,30);(第2条S语法在新的mysql版本已经不支持,新版本mysql in的子语句不再支持limit条件,但可以分解为两条SQL实现,原理不变,不做赘述)
    • 以上思路在于,子查询的影响结果集仍然是$start +30,但是数据获取的过程(Sending data状态)发生在索引文件中,而不是数据表文件,这样所需要的系统开销就比前一种普通的查询低一个数量级,而主查询的影响结果集只有30条,几乎无开销。但是切记,这里仍然涉及了太多的影响结果集操作。

3、查询语句优化

1)避免在列上进行运算,这样会导致索引和查询缓存失效。


优化为:

2)EXPLAIN 你的SELECT查询

使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。

有表关联的查询,如下列:

发现查询缓慢,然后在group_id字段上增加索引,则会加快查询

3)当只要一行数据时使用LIMIT 1

4)优化COUNT()查询

 5)避免SELECT *

6)尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议,请往下看。

7)尽量避免JOIN操作,适度冗余减少查询请求。

比如说,信息表,fromuid, touid, message字段外,还需要一个fromuname字段记录用户名,这样查询者通过touid查询后,能够立即得到发信人的用户名,而无需进行另一个数据表的查询。

  • 摘除数据表之间的关联,是分库的基础工作。
  • 摘除关联的目的是,当数据表分布到不同服务器时,查询请求容易分发和处理。

8)应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。

9)in 和 not in 也要慎用,否则会导致全表扫描,如:

优化为:

参考书籍:

《高性能MySQL(第三版)》

《PHP核心技术与最佳实践》

《Mysql性能优化教程(曹政)》

参考链接:

mysql性能优化的19个要点

30种mysql优化sql语句查询的方法


上一篇:  下一篇:

评论

还没有任何评论,你来说两句吧

Copyright © 2014-2016 lxlxw All Right Reserved