慢查询日志和人为发现慢sql

慢查询特征：

  • 数据库CPU负载高。一般是查询语句中有很多计算逻辑，或并发处理线程较多，导致数据库cpu负载。
  • IO过高导致服务器卡住，这个一般和全表查询没索引有关系，问题出在处理的数据量太大。
  • 查询语句正常，索引正常但是还是慢。如果表面上索引都配置了，但查询慢，看索引是否生效。

有些SQL虽然出现在慢查询日志中，但未必是其本身的性能问题，可能是因为锁等待，服务器压力高等等。

使用Explain工具来逐步调优

了解执行这个SQL语句时的细节，比如是否进行了优化、使用了索引、索引选择器是否正确选择等等。

比较重要的字段有：

  • select_type : 查询类型有：简单查询、联合查询、子查询等
  • key : 使用的索引
  • rows : 扫描的行数

关于索引的创建及优化原则

  1. 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整；
  2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，当然，好习惯要靠自己保持；
  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录；
  4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)；
  5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

当然，我们知道还有单表数据量过大、大字段检索、模糊匹配效率、时间维度统计效果差等MySQL硬性问题，俗称硬伤，那我们就得基于实际情况来进行横向扩展，分库分表、切换检索引擎（如ES、FastDFS）等方式来处理了，术业有专攻，总不能在一棵树上吊死对吧。

  1. 优化数据访问
  1. 减少请求的数据量
  • 只返回必要的列，必要的行，
  • 缓存重复查询的数据
  1. 减少服务器端扫描的行数。使用索引来覆盖查询。
  1. 重构查询方式

1、大查询切分

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小但重要的查询。

2、 大连接查询分解

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

  1. 让缓存更能尽可能的使用。方便缓存的使用，方便的查询已经被缓存好的数据。如：①上面的sql查询，tag表中的相关的数据已经被缓存，那么就可以跳过第一个查询。上面的第三个查询的sql语句，in中要查询的记录就会少很多。减少冗余记录的查询（从相当于一个缓存拆分多个缓存的使用率来说）②缓存层中如果要修改表，表的字段改变，那就不能使用该关联后的查询了。（从稳定性来讲）
  2. 减少锁的竞争少了。
  3. 容易对数据库拆分，可扩展性好。
  4. 查询本身效率比之前提升

某些场景哈希关联的效率要高很多