很多时候, 我们的程序出现的 "性能问题", 其实是我们自己写的那 "坨" 代码的问题, 是自己 Coding 的问题, 是 MySQL 的 DML 语句使用的问题. 以下是我总结的关于 MySQL DML 语句的使用中需要注意的点.
对于 select * 要时刻保持谨慎的态度
绝大多数情况, 是不需要 select * 的. 一旦使用了这种语句, 便会让优化器无法完成索引覆盖扫描这类优化, 而且还会增加额外的 I/O, 内存和 CPU 的消耗. 当然, 使用 select * 也并不是全是坏处, 合理的使用 select * 可以简化开发, 提高相同代码的复用性.
是否扫描的太多额外的记录
有时候会发现某些查询可能需要读取几千行数据, 但是仅返回几条或者很少的结果, 可以使用以下方式去优化:
看看能否改表结构. 例如使用汇总表
看看获取数据结果的方式是否最优, 获取路劲是否已经是最短.
使用覆盖索引, 把所有需要的列都放到索引中, 以减少返回表中对应行中取数据的步骤.
切分某些 SQL 语句
传统的互联网系统中, 强调网络连接尽量少, 数据层尽可能在一次连接中完成尽可能多的工作, 防止建立多次链接, 但是这种想法对于 MySQL 并不适用, MySQL 从设计上让连接和断开都很轻量, 在一般服务器上可以支持每秒超过 10 万的查询.
所以对于有些场景下, 可以将一个大的查询 "分而治之", 切分成小查询, 然后再组合起来. 例如以下情况:
对于全量数据查询变成分页. 假如一张表中有数千万条数据, 一次 select all, 肯定是不行的. 可以换成一次取一部分, 把一次的压力分摊.
删除大量旧数据的时候, 不要一个大的语句一次性清完, 推荐一次删一万条. 如果用一个大的语句一次性完成的话, 可能需要一次锁住大量数据, 占满大量日志事务, 让 MySQL 停在那儿了, 为避免这种情况发生, 最好一次性删除一万条左右的数据, 然后每次删完暂停一会儿再操作, 将服务器上的一次性压力分散.
注意: 虽然 MySQL 建立连接十分轻量, 但是这不意味着可以逐条循环中查询然后再拼接, 这样效率依然是非常慢, 而且通常是工作中 sql 优化的点.
慎用 join 操作
这算是一条禁忌吧, 很多公司的互联网产品都杜绝 join 操作, 换成先从一张表中先取出数据 id, 再从另外一张表中使用 where in 查询的两次单表查询操作. 主要是以下几点原因:
让应用的缓存 (Redis,memcache 等) 更高效. 例如在第一张表中查询出部分 id 了, 如果命中了缓存, 就可以省去一条 where in 语句了.
更容易应对业务的发展, 方便对数据库进行拆分, 更容易做到高性能和高扩展.
对 where in 中的 id 进行升序排序后, 查询效率比 join 的随机关联更高效
减少多余的查询. 在应用层中两次查询, 意味着对某条记录应用只需要查询一次, 而使用 join 可能需要重复的扫描访问一部分数据.
单张表查询可以减少锁的竞争.
假如非用不可, 可以采用以下方式来优化:
确保 ON 或者 using 子句中的列上有索引
确保任何的 group by 和 order by 中的表达式只涉及到一个表中的列.
在性能要求比较高的场景中, 杜绝查询中使用临时表
MySQL 的临时表示没有任何索引的, 使用临时表一般都意味着性能比较低, 因此在对性能要求比较高的场景中, 最好不要使用带有临时表的操作:
未带索引的字段上的 group by 操作.
UNION 查询.
查询语句中的子查询.
部分 order by 操作, 例如 distinct 函数和 order by 一起使用且 distinct 和 order by 同一个字段. 再例如某些情况下 group by 和 order by 字段不同.
具体是否用到临时表, 可以通过 explain 来查看, 查看 Extra 列的结果, 如果出现 Using temporary 则需要注意.
count()函数优化
count()函数有一点需要特别注意: 它是不统计值为 NULL 的字段的! 所以: 不能指定查询结果的某一列, 来统计结果行数. 即 count(xx column) 不太好.
如果想要统计结果集, 就使用 count(*), 性能也会很好.
尽量不使用子查询
尽量别使用子查询, 尽可能的使用关联来代替
优化分页 limit
通常我们在分页的时候, 通常使用的是 limit 50, 10 这种语句. 数据少还不错, 但是当数据偏移量非常大的时候, 性能就会出现问题, 例如 select xx,xxx from test_table limit 100000020, 20. 扫描了 100000020 条数据, 才返回 20 条数据. 这个时候我们可以用一下两种方式来优化:
利用 between and 和主键索引
利用主键自增 id, 我们如果知道了分页的上边界, 以上查询可以改写为: select xxx, xxx from test_table where id between xxxxx and xxxx.
利用自增主键索引, order by 加 limit, 不使用 offset
limit 和 offset 的问题, 其实就是 offset 的问题, 它会导致 MySQL 扫描大量不需要的行然后再抛弃掉. 如果使用某个标签记录上一次所取数据的位置, 那么下次就可以直接从书签位置开始扫描, 这样就可以避免使用 offset.
例如以上查询可以改为:
第一组数据:``select xxx, xxxx from test_table order by id desc limit 20;
这样就拿到了本次数据和下次数据的分解 id 值, 则下一页查询就知道可以: select xxx, xxx from test_table where id < '上页 id 分界值' order by id desc limit 20
熟悉并灵活使用 explain
以下是 MySQL 执行查询的整个过程, explain 可以查看图中标红部分,
explain 会展示很多字段和内容, 其中的内容往往不好记, 使用的时候, 可以查看以下图解内容: explain 图解
来源: https://juejin.im/post/5c67aaf7e51d45362c36229d