sql慢查的原因总结

1. SQL的where没有对应的索引，导致走了全表查询。

2. 索引失效

   • 索引列的隐式转换。类型不匹配。
   • 索引列直接使用内置函数。
   • 多个列排序时顺序不一致。order by a asc,b desc会导致索引失效。

   • 查询条件包含or可能会导致索引失效。
     比如sql，优化器成本分析走name索引之后还要去全表扫描过滤age条件，不如全表扫描，所以最后没有走索引。

     -- name有索引 age没有
     select * from user where name = 'xxx' or age =10;

   • like通配符可能导致索引失效。比如查询条件是 like ‘%aaa’。这里可以优化的一个点是优化为最左前缀原则和select的字段尽量只有索引值，让sql可以走索引覆盖。

   • 联合索引没有走最左前缀原则。这里可以注意下二级联合索引的icp索引下推减少回表次数的特性。
   • 索引值进行运算。走不了索引的。
   • in、not in、is null、is not null使用如果扫描过多行记录，优化器可能选择全表扫描。
   • 左右连接，用于关联的字段编码格式不一样，这个比较隐蔽。
   • Mysql优化器的选择不一定是最佳的，如果想让走一个索引可以使用force inedx。

3. limit深度分页问题

   • limit的过程：

     select * from table where created_time > '2020-01-01' limit 100000, 10;

     1. 通过二级索引created_time，过滤条件，找到符合条件的id。
     2. 主键id回表查询。
     3. 依次扫描符合条件的100010行，取最后10行返回。
   • 深度分页慢的原因
     1. limit深度分页会扫描前100000行，然后再取到对应步长的数据。
     2. 扫描这么多行，意味着需要回表这么多次，回表查询是一个随机IO的过程。

   • 如何优化？

     1. 标签记录。

        select  id,name,balance FROM account where id > 100000 limit 10;

        这个方法有一定的局限性，比如要求查询条件能定位到id这种标签，走聚簇索引去直接拿到对应的记录去捞取10条记录。

     2. 延迟关联
        这个方法的思路就是拆分成join查询，因为之前一次查询只能走二级索引之后回表去查询对应深度的分页数据，这里思路是先在二级索引上查找符合条件的主键id，再与原来的表join通过主键id关联，这里的连接查询是主键关联，不需要二级索引每条查出来主键id去回表，速度也是不慢的。

        select id, name, balance from account b inner join 
            (select a.id from account a where a.created_time > '2020-01-01' limit 100000, 10) on a.id = b.id

4. 单表中的数据太大，三层B+树大概能承载2000w的记录，所以如果单表数据量太大，那么B+树的高度会变高，加多了加载数据的磁盘IO次数，即索引的效率会变慢。

5. 过多的表连接查询。一般不建议超过3个表进行连接查询，连接查询也要用索引列关联，否则会占用内存创建join_buffer，来用块的嵌套循环查询算法，加大对内存的压力。可以尝试在应用代码里做关联。

6. 数据库在刷脏页时可能会阻塞sql的执行。

   • redo log文件写满了，要将循环写的redo log文件中记录的数据脏页刷入磁盘，已能为redo log提供空间。这时会阻塞写sql的性能。
   • Buffer Pool没有额外的空闲缓存页，这时候要根据lru链表将一些脏页、不常用的冷数据刷入到磁盘。因为脏页刷入磁盘是随机IO的过程。

7. order by 文件排序

如果order by不能使用索引排序，则会使用file sort文件排序，这里超过sort_buffer之后会到磁盘中排序，性能很差。索引天生有序这个特点要在order by这个语句中使用，尽量让排序走索引。

8. 锁等待。

如果出现同一行数据被多个事务竞争锁，那么会造成事务锁等待，会加大sql的执行时间。

9. group by 默认排序和使用临时表

group by默认是排序的，而且为了实现分组可能使用临时表进行统计。这两点可能造成慢查询。优化点可以有：

• 让group by 的字段不排序，则少了一个排序的过程。这个取决于业务上只要分组。
• 注意内存临时表参数的设置，避免使用磁盘临时表。
• group by使用索引，因为索引天生有序，所以统计可以直接扫描索引树。

10. 本身数据库的机器问题和一些参数设置。
    本身机器比如IOPS（机器随机读写的性能）、CPU、内存、网络带宽都会影响到数据库的效率。

比如测试环境的机器打开了索引下推ICP或者index merge，而线上机器没有打开，则sql查询使用索引情况肯定不一样，这里要注意下。

还有Buffer Pool设置的大小，如果设置的太小，频繁造成刷页到内存中，会造成数据库慢查询。