MySQL的SQL性能优化总结

栏目: 数据库 · 发布时间: 4年前

内容简介:很多时候,我们的程序出现的“性能问题”,其实是我们自己写的那”坨”代码的问题,是自己Coding的问题,是Mysql的DML语句使用的问题。以下是我总结的关于MySQL DML语句的使用中需要注意的点。绝大多数情况,是不需要

很多时候,我们的程序出现的“性能问题”,其实是我们自己写的那”坨”代码的问题,是自己Coding的问题,是 Mysql 的DML语句使用的问题。

以下是我总结的关于MySQL DML语句的使用中需要注意的点。

对于 select * 要时刻保持谨慎的态度

绝大多数情况,是不需要 select * 的。一旦使用了这种语句,便会让优化器无法完成索引覆盖扫描这类优化,而且还会增加额外的I/O、内存和CPU的消耗。

当然,使用 select * 也并不是全是坏处,合理的使用 select * 可以简化开发,提高相同代码的复用性。

是否扫描的太多额外的记录

有时候会发现某些查询可能需要读取几千行数据,但是仅返回几条或者很少的结果,可以使用以下方式去优化:

  • 看看能否改表结构。例如使用汇总表
  • 看看获取数据结果的方式是否最优,获取路劲是否已经是最短。
  • 使用覆盖索引,把所有需要的列都放到索引中,以减少返回表中对应行中取数据的步骤。

切分某些 SQL 语句

传统的互联网系统中,强调网络连接尽量少,数据层尽可能在一次连接中完成尽可能多的工作,防止建立多次链接,但是这种想法对于MySQL并不适用,MySQL从设计上让连接和断开都很轻量,在一般服务器上可以支持每秒超过10万的查询。

所以对于有些场景下,可以将一个大的查询“分而治之”,切分成小查询,然后再组合起来。例如以下情况:

一次删一万条

注意:虽然Mysql建立连接十分轻量,但是这不意味着可以逐条循环中查询然后再拼接,这样效率依然是非常慢,而且通常是工作中sql优化的点。

慎用 join 操作

这算是一条禁忌吧,很多公司的互联网产品都杜绝join操作,换成先从一张表中先取出数据id,再从另外一张表中使用 where in 查询的两次单表查询操作。主要是以下几点原因:

  • 让应用的缓存(redis、memcache等)更高效。例如在第一张表中查询出部分id了,如果命中了缓存,就可以省去一条where in语句了。
  • 更容易应对业务的发展,方便对数据库进行拆分,更容易做到高性能和高扩展。
  • 对where in中的id进行升序 排序 后,查询效率比join的随机关联更高效
  • 减少多余的查询。在应用层中两次查询,意味着对某条记录应用只需要查询一次,而使用join可能需要重复的扫描访问一部分数据。
  • 单张表查询可以减少锁的竞争。

假如非用不可,可以采用以下方式来优化:

  • 确保 ON 或者 using 子句中的列上有索引
  • 确保任何的 group byorder by 中的表达式只涉及到一个表中的列。

在性能要求比较高的场景中,杜绝查询中使用 临时表

MySQL的临时表示没有任何索引的,使用临时表一般都意味着性能比较低,因此在对性能要求比较高的场景中,最好不要使用带有临时表的操作:

  • 未带索引的字段上的 group by 操作。
  • UNION 查询。
  • 查询语句中的子查询。
  • 部分 order by 操作,例如 distinct 函数和 order by 一起使用且 distinctorder by 同一个字段。再例如某些情况下 group byorder 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 bylimit ,不使用offset

limitoffset 的问题,其实就是 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 可以查看图中标红部分,

MySQL的SQL性能优化总结

explain 会展示很多字段和内容,其中的内容往往不好记,使用的时候,可以查看以下图解内容:

explain图解

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Programming Python

Programming Python

Mark Lutz / O'Reilly Media / 2006-8-30 / USD 59.99

Already the industry standard for Python users, "Programming Python" from O'Reilly just got even better. This third edition has been updated to reflect current best practices and the abundance of chan......一起来看看 《Programming Python》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

html转js在线工具
html转js在线工具

html转js在线工具