优化案例
覆盖索引
比如执行最多的语句是select A,B,C from student where A=? and B=? and C=?
可以直接考虑创建一个<A,B,C>
组合索引。对于这个SQL来说,这个组合索引就是一个覆盖索引,避免了回表。
优化order by
查询一些数据后,都要求对数据做一定的排序,比如按update_time
排序,需要将排序的列加入索引。
比如select * from student where id = 1 order by update_time
这个语句,如果用户的数据比较多,这个语句执行的速度比较慢,可以在<id,update_time>
上创建一个新的索引。因为在id
确定以后,索引内的update_time
就是有序的了,避免了再次排序的消耗,可以将查询时间从秒级降到10ms级
在所有排序场景里,都尽量使用索引来排序,这样能够有效减轻数据库的负担,加快响应速度。
优化count
select count(*)
是一个很常用的计算总数的语句。但是InnoDB引擎并没有存储数据总数,就导致类似的语句执行起来特别慢。
优化count
一般有两种思路
用估计值取代精确值:如果这个场景对数据的准确性不高的话,可以借助
EXPLAIN
返回的预估行数来拿到一个预估值。如果需要精确值的话,可以考虑使用
Redis
之类的NoSQL
来直接记录总数,或是直接有一个额外的表来记录总数。如果用
Redis
来维持总数的话,会涉及到数据一致性的问题。如果插入数据库失败,但是更新Redis
的总数失败了怎么办?如果数据只是短时间不一致,而且这个不一致业务可以接受的话,可以考虑异步刷新
Redis
的总数使用
Cancel
之类的工具监听binlog
,刷新Redis
的总数
索引提示优化
在实际工作的时候,如果有的时候数据库的执行比较奇怪,不用索引或用了错误的索引,就可以考虑通过FORCE INDEX、USE INDEX 和 IGNORE INDEX 这些索引提示来纠正数据库的行为。但是这个本身并不是什么好的实践,还是要谨慎。
用where
替换having
一般来说,数据库都是先根据where
条件找到候选的列,再根据having
条件进行二次过滤。如果将having
的部分条件提前到where
里,就可以提前把不符合条件的数据过滤掉了。
规律:如果不是使用聚合函数来作为过滤条件,最好还是将过滤条件优先写到where
里。
SQL执行顺序相关问题
深度分页问题优化分页的偏移量
有一些SQL在在不断执行中会产生极大的偏移量,比如文章分页,一页50条数据,当要拿101页的数据,需要写成LIMIT 5000,50
,5000
就是偏移量。实际执行的时候,数据库需要读出5050
条数据,然后把前面的5000
条都丢掉,只保留50
条。
优化思路是使用小偏移量。比如在原先的查询语句加where id > max_id
的条件,这个max_id
就是上一批的最大ID
,这样可以保证LIMIT
的偏移量永远是0
。很多时候因为测试环境数据量小,这种性能问题很难被发现,所有使用分页的查询都应该考虑引入类似的查询条件。