后端面经系列-如何发现SQL中的问题

August 19, 2023

请你解释一下EXPLAIN命令 #

explain命令是用来查看mysql执行语句的执行计划，帮助我们分析SQL语句的性能瓶颈。其输出中各个列的作用如下：

其中较为重要的是：

• type 列：表明了查询时使用的访问方法。常见的有：all、index、range、ref、eq_ref、const、system。依据性能从好到坏 依次是：system > const > eq_ref > ref > range > index > all。

  1. system 表中只有一条记录。
  2. const 根据主键或者唯一索引进行常数等值匹配，最多只能返回一条记录。
  3. eq_ref 进行连接查询时，如果被驱动表是通过主键或者唯一二级索引进行等值匹配，则对被驱动表的访问方法就eq_ref。
  4. ref 当通过使用普通二级索引与常量进行等值匹配来查询时，访问方法就可能是ref，展示的是与索引列进行等值匹配的东西是什么。
  5. index 可以使用索引覆盖，但是要扫描全部索引记录。
  6. all 全表扫描。应该坚决避免。

• possible_keys 列：表示查询时可能用到的索引。候选索引越多，查询优化器在计算成本时花费的时间就越长。

• key 列：表示查询时实际使用的索引。

• rows 列：表示查询时扫描的行数。

• filtered 列：表示经过搜索条件过滤后剩余记录条数的百分比，是个预测值。

• extra一些描述额外信息的列，我们可以通过这些额外信息来判断索引的使用情况。比如：

  1. using index 使用覆盖索引查询。
  2. using index condition 条件下推。搜索条件中出现了索引列，但却不能充当边界条件来形成扫描区间，不能用来减少扫描的记录数量。例如： 联合索引中有a,b,c三个，字段，查询条件是a=? and c=?，那么c=?无法形成扫描区间，mysql为了减少回表次数，仍然会在索引中完成c的过滤。
  3. using where 某个where条件需要再server层判断。
  4. using where;using index
  5. using temporary 使用到了临时表。
  6. using filesort 无法使用索引排序，只能在内存中或者磁盘中排序。

你有优化过SQL吗？具体怎么优化的？ #

优化过。 优化的核心目的，是为了减少磁盘IO（减少扫描的记录数），减少cpu和内存消耗（排序，去重类）。 我会遵循下面几个策略：

• 尽可能使用覆盖索引。
• 要尽可能保证没有导致索引失效的语句，比如：where条件中使用了函数，或者隐式类型转换。
• 要尽可能高效利用索引，比如：联合索引，符合最左匹配原则，且尽可能保证一个索引满足where条件和order by条件。
• 尽可能只使用等值查询，如果一定使用范围查询，要注意扫描的行数。
• 看看查询条件中是否有过多不必要的列，或者有超长列。比如内容类，有text类型，在列表中不需要显示，可以不查询出来。
• 如果一定要扫描很多行，则需要考虑是否可以从架构上改变，甚至是从业务上改变。比如，使用辅助统计，比如，业务上，缩小查询范围。

你有没有优化过索引？怎么优化的？ #

优化过。大致步骤为：

1. 收到慢查询告警，或者锁相关告警。
2. 通过explain命令查看执行计划，分析索引使用情况。
3. 如果是没有使用到索引，看是否需要添加索引。尽可能的使用覆盖索引。尽可能保证一个索引能满足where条件和order by条件。
4. 如果数据量过大，则和产品沟通，看是否可以限制查询范围，这样可以减少扫描的数据页。
5. 判断是否可以合并索引，如果是，尽可能合并索引。
6. 在开发/测试环境验证，验证通过后上线，观察使用情况。

怎么优化COUNT查询？ #

count查询，在表的记录数很多时性能非常差。有两个优化思路：

• 使用辅助表来统计，写入主表的同时写入统计表。
• 使用缓存。比如redis，将count的结果缓存起来，然后定时更新。但是可能会带来短时内的不准确性和误差。

怎么优化order by？ #

• 排序要尽可能是用到索引排序。如果是大表，那就是一定要用到索引排序。这点设计索引时要巧妙处理，保证where和排序都能在同一个索引内完成。

怎么优化limit offset 查询？ #

• 如果可以不跳转页，那么就不跳转页。尽可能采用上一页、下一页，或者滚动加载的方式，这样翻页时记住当前页的的条件，每次查询都是 limit ? offset 0,不会面临 深度翻页的问题。

当然，实际中可能不是每个场景都适合，或者说不是每个功能产品都愿意采用这种方式。

• 限制翻页深度，比如限制最大只能跳转到第x页。这样可以避免扫描过多的page。
• 采用子查询的方式来优化，再查依据二级索引查询到的主键id，然后再根据主键id到聚簇索引中查询数据。

为什么要尽量把条件写到where里而不是写到having里？ #

因为having的执行顺序在where之后，数据库是先根据where条件找到候选列，再根据having条件进行过滤。 放到where里，在索引扫描时就能把不符合条件的记录排除掉。

怎么给一张表添加新的索引/修改表结构？如果我的数据量很大呢？ #

修改大表的表结构/添加索引，这个过程是非常耗时的，会造成锁表，影响业务。因为涉及到大量的数据页的修改，还会导致磁盘IO使用率飙升。这些都不可避免的会影响业务。 考虑方案/原则有：

• 停机变更，这个几乎不可能，或者很少被采用，除非逼不得已。
• 在业务低峰期变更。这个是最常用的方案。
• 先创建一个新表，然后将旧表的数据复制到新表中，再将旧表重命名为临时表，将新表重命名为旧表。

use index/force index/ignore index有什么效果？ #

• use index 告诉查询优化器使用某个索引，但是查询优化器也可能不用。
• force index强制使用某个索引
• ignore index 告诉查询优化器不使用某个索引。