面试老大难的SQL优化问题

1,802 阅读4分钟

MySQL 基本是每个后台面试者必问的问题,而SQL优化又是MySQL中重之又重的一块了。如果你的SQL优化回答的好,有条理,那你入取的概率就大大提升了。

像这种SQL优化,网上很多,所以面试官听的也多,想要回答的让面试官耳目一新,那就要学会装逼回答,哈哈哈。。。

比如,现在有个面试官说,现在线上有个SQL执行很慢,你怎么优化?

这种时候最好分几步回答,不要一上来就说,该怎么怎么写SQL,面试时要学会,跳出来,看全貌,装进去,看本质。

慢 SQL 语句的几种常见诱因?

在这个时候,就可以说,面试官,您好,我觉得SQL慢出现的原因可以分为以下几种,切记,不要回答自己模糊不清的,没法自圆其说的即使知道你也别回答,不然就是给自己挖坑。。。

  • 无索引、索引失效导致慢查询
    如果表很大,而对where或者order by 后面字段没有建立索引,那这种情况查起来肯定很费力。但是有时候建立了索引,但在一些特定的场景下,索引还有可能会失效,所以索引失效也是导致慢查询的主要原因之一。失效场景很多,比如模糊查询白分号在前、查询的字段使用了函数或者做了计算操作都有可能导致索引失效。

  • 锁等待
    InnoDB 存储引擎支持行锁和表锁,我们要注意行锁升级为表锁的可能。在批量更新操作时,行锁就很可能会升级为表锁。
    MySQL 认为如果对一张表使用大量行锁,会导致事务执行效率下降,从而可能造成其它事务长时间锁等待和更多的锁冲突问题发生,致使性能严重下降,所以 MySQL 会将行锁升级为表锁。还有,行锁是基于索引加的锁,如果我们在更新操作时,条件索引失效,那么行锁也会升级为表锁。
    除了锁升级之外,行锁相对表锁来说,虽然粒度更细,并发能力提升了,但也带来了新的问题,那就是死锁。

  • 不恰当的 SQL 语句
    使用不恰当的 SQL 语句也是慢 SQL 最常见的诱因之一。例如,习惯使用 <SELECT >,<SELECT COUNT()> SQL 语句,在大数据表中使用 <LIMIT M,N> 分页查询,以及对非索引字段进行排序等等。

对于一些慢的SQL,我该怎么分析?

上面我说了一些关于SQL执行慢的原因,下面就该看看怎么分析这些SQL了

  • 找出慢SQL
    开启慢查询,慢查询是分析SQL执行慢的常见手段,主要有这几个参数,slow_query_log:慢查询开启状态,slow_query_log_file:慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录),long_query_time:查询超过多少秒才记录。

  • 通过 EXPLAIN 分析 SQL 执行计划

  • 通过 Show Profile 分析 SQL 执行性能

平时写SQL时该注意什么?有什么经验可谈?

到这里,如果面试官还在听你回答,这时候,你可以适当提高一点语气,说对于一些慢SQL,有很大一部分是由于我们开发者写SQL时不注意SQL优化导致的,那么我就说一下我知道的一些注意事项。。。(提高声音分贝,以防面试官睡着,并敲重点,告诉面试官,我准备装逼了,哈哈哈。。。)

  • 统计时尽量使用count(*),count(*)≈count(1),大于count(主键)。

  • 如果明确查找一条语句,请使用limit 1;,因为找到一条符合条件的记录后就不会继续查找了。

  • 优化分页查询

  • 避免 Select *
    用到什么字段就具体写什么字段,原因除了 select * 查询所有字段会多出网络传输开销之外,还有更重要的一点是,select * 无法使用覆盖索引。

  • 尽量使用 MySQL 5.6以后的版本

  • 对于使用索引方面

    • 对索引字段做函数操作或者做运算操作,都不能使用上索引。
      所以针对这一点,除了我们索引的字段不要加函数之外。
      • 还要注意一些隐式转换,比如,交易日志表(tradelog),tradeid 的字段类型是 varchar(32),字段有索引,但是当你执行 select * from tradelog where tradeid=110717;语句,你发现走的还是全索引扫描。这是因为它其实做了类型转换,相当于这么执行mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
      • 隐式字符编码转换
        如果两张表使用的编码集不一致,比如一个是utf8mb4,一个是utf8,那么这两个字段连接后,相当于做了一次CONVERT(traideid USING utf8mb4),这样也用不了索引。其实字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。