慢 SQL 产生的原因
1.查询的表无索引
如果一个查询 SQL 以一个没有索引的列作为查询条件,导致需要全表扫描。
在表数据量比较少的时候,不会觉得慢。但如果在一张几千万数据的表中,查询会非常耗时。我们需要建立合适的索引来优化查询。
2.索引失效
很多情况下我们建立了索引,但在一些特定的情况下,索引可能失效,此时会导致慢查询。
以下几种,就是常见的导致索引无效:
- 查询条件使用 or, 选择式过滤条件,导致索引无效
- 查询条件使用like,且从头部开始模糊匹配,导致索引无效
- 查询条件不满足复合索引的最左匹配原则,导致索引无效
- 查询条件,索引列使用了隐式类型转换,导致索引无效
- 查询条件,索引列使用了聚合函数,导致索引无效
- 查询条件,索引列使用了算术运算(+、-、…),导致索引无效
- 查询条件,索引列使用了逻辑运算(!=、<>、is null、 is not null …),导致索引无效
- 左右关联时,字段类型不一致,导致索引无效
3.不恰当的 SQL 查询
使用 SELECT *
、 SELECT COUNT(*)
SQL 语句,以及对非索引字段进行排序等等。
在大数据表中使用 LIMIT M,N
分页查询,如果偏移量很大时,就会发现 SQL 执行起来非常非常慢了。因为,偏移量会分页读取到 buffpool 中,数据量大,占用的 buffpool 空间就会大,而这个空间大小是配置的,一般不会很大,所以,导致了慢 sql。
对于这个问题的优化,建议写一个过滤条件,再与limit结合实现。
4.查询中使用临时表
临时表可能大家不知道,但是回表查询,大家可能听说过,就是说一次查询不满足,还需要再查一次,查两次才能出结果,这当然就会慢啦。
哪临时表一般都是怎么产生的呢?通过一次查询返回的数据,要进行下一步的过滤、显示时,发现返回的数据中不满足过滤条件,或者没有显示的字段,又要回头查一次原表,从原表中获取满足条件的数据,这些数据,就放在临时表中。本来,回头查一次,就已经消耗了时间了,奈何,临时表还有空间大小限制,占用内存空间,还可能空间不够用,存放不下所有数据。所以,一般,只要出现使用了临时表,这个sql的性能都很差。
5.Join或子查询太多
关联查询,在实际工作中,非常场景,关联的表越多,那么,数据过滤筛选就越来复杂,时间自然就会越长了。所以,一般而言,关联表不建议超过3个,而且数据量小的表放左边,大的表放在右边。
6.查询结果的数据量太大
查询结果数据量太大,常见的有两种,第1种,就是直查的表数据量太大,如千万级。一张表千万级,即使建了索引,索引文件也会很大,深度也会很深,查询速度,自然就会很慢了。 第2种,就是联表笛卡尔积量太大。
对于第一种,优化建议,一般是对表采用分表分区了。而第二种,就简单粗暴的sql拆分优化。
7.锁竞争/锁等待
我们常用的 MySQL 存储引擎有 InnoDB 和 MyISAM,InnoDB 支持表级锁和行级锁,MyISAM 支持表级锁。
如果数据库操作是基于表锁实现的,假设一张订单表在更新时,需要锁住整张表,那么其它大量数据库操作(包括查询)都将处于等待状态,这将严重影响到系统的并发性能。
这时,InnoDB 存储引擎支持的行锁更适合高并发场景。但在使用 InnoDB 存储引擎时,我们要特别注意行锁升级为表锁的可能。在批量更新操作时,行锁就很可能会升级为表锁。
MySQL 认为如果对一张表使用大量行锁,会导致事务执行效率下降,从而可能造成其它事务长时间锁等待和更多的锁冲突问题发生,致使性能严重下降,所以 MySQL 会将行锁升级为表锁。还有,行锁是基于索引加的锁,如果我们在更新操作时,条件索引失效,那么行锁也会升级为表锁。
因此,基于表锁的数据库操作,会导致 SQL 阻塞等待,从而影响执行速度。在一些更新操作(insert\update\delete)大于或等于读操作的情况下,MySQL 不建议使用 MyISAM 存储引擎。
除了锁升级之外,行锁相对表锁来说,虽然粒度更细,并发能力提升了,但也带来了新的问题,那就是死锁。因此,在使用行锁时,我们要注意避免死锁。
8.参数配置不合理
我们很多时候使用数据库,都是安装了之后,就直接用,不会对数据库配置参数进行过多了解和设置。在本文中多次提到 buff,这就是数据库中一类非常重要的配置参数。在mysql中,有很多带有 buff、cache、size、length、max、min、limit 等字样的配置参数,都是非常重要的配置参数。这些配置参数,是直接关系数据库的性能的。如果,你数据库安装在一个配置很高的机器上,但是,这些配置参数却不知道修改,都用默认值。哪也就只能哀怨“这么高的硬件配置,性能怎么还是这么差?”
9.频繁刷脏页
脏页,是内存数据页和磁盘数据页不一致。这个一般发生在数据更新操作中。更新数据,需要先把数据读取出来,然后再内存中更新,然后再生成日志文件,再回放日志文件,实现表数据更新。而当更新数据量大,buffpool 写满,或者是后续生成的回放日志文件写满,都会导致这个操作过程变慢。
对于这种问题优化,一般建议是少批量修改,多次提交。
10.系统资源不够用
数据库,使用来存储数据的,要频繁进行磁盘操作,所以,一般,我们都会选择磁盘 IO 性能比较好的机器作为数据库服务器。同时,数据库还要经常进行数据交换,所以,也需要有足够的内存,所以,内存也会相应要求高些。而这些硬件,仅仅只是作为数据库服务器硬件选择的基本要求;数据库也是一个软件,软件也是安装在操作系统中的,所以,也会受操作系统的参数的一些限制,所以,当硬件资源不够用,或者达到了系统参数限制值时,也是会导致操作变慢的。
评论区