首页 运维知识 此篇文有关 MySQL慢SQL语句常见诱因

此篇文有关 MySQL慢SQL语句常见诱因

1. 无索引、索引失效导致慢查询 如果在一张几千万数据的表中以一个没有索引的列作为查询条件,大部分情况下查询会非常耗时,这种查询毫无疑问是一个慢SQL查询。所以对于大数据量的查询,…

1. 无索引、索引失效导致慢查询

如果在一张几千万数据的表中以一个没有索引的列作为查询条件,大部分情况下查询会非常耗时,这种查询毫无疑问是一个慢SQL查询。所以对于大数据量的查询,需要建立适合的索引来优化查询。

虽然很多时候建立了索引,但在一些特定的场景下,索引还有可能会失效,所以索引失效也是导致慢查询的主要原因之一。

 

2. 锁等待

常用的存储引擎有 InnoDB 和 MyISAM,前者支持行锁和表锁,后者只支持表锁

如果数据库操作是基于表锁实现的,试想下,如果一张订单表在更新时,需要锁住整张表,那么其它大量数据库操作(包括查询)都将处于等待状态,这将严重影响到系统的并发性能。

这时,InnoDB 存储引擎支持的行锁更适合高并发场景。但在使用 InnoDB 存储引擎时,要特别注意行锁升级为表锁的可能。在批量更新操作时,行锁就很可能会升级为表锁。

MySQL认为如果对一张表使用大量行锁,会导致事务执行效率下降,从而可能造成其它事务长时间锁等待和更多的锁冲突问题发生,致使性能严重下降,所以MySQL会将行锁升级为表锁。还有,行锁是基于索引加的锁,如果在更新操作时,条件索引失效,那么行锁也会升级为表锁。

因此,基于表锁的数据库操作,会导致SQL阻塞等待,从而影响执行速度。在一些更新操作(insert\update\delete)大于或等于读操作的情况下,MySQL不建议使用MyISAM存储引擎。

除了锁升级之外,行锁相对表锁来说,虽然粒度更细,并发能力提升了,但也带来了新的问题,那就是死锁。因此,在使用行锁时,要注意避免死锁。

 

3. 不恰当的SQL语句

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

 

Note:

慢查询问题,很可能是没有利用好索引导致的,MySQL 内置的 explain 命令,可以帮助确认查询语句是否利用了索引。

Column name Description
id Sequence number that shows in which order tables are joined.
select_type What kind of SELECT the table comes from.
table Alias name of table. Materialized temporary tables for sub queries are named <subquery#>
type How rows are found from the table (join type).
possible_keys keys in table that could be used to find rows in the table
key The name of the key that is used to retrieve rows. NULL is no key was used.
key_len How many bytes of the key that was used (shows if we are using only parts of the multi-column key).
ref The reference that is used to as the key value.
rows An estimate of how many rows we will find in the table for each key lookup.
Extra Extra information about this join.

其中 type 表示 表中找到所需行的方式

Value Description
ALL A full table scan is done for the table (all rows are read). This is bad if the table is large and the table is joined against a previous table! This happens when the optimizer could not find any usable index to access rows.
const There is only one possibly matching row in the table. The row is read before the optimization phase and all columns in the table are treated as constants.
eq_ref A unique index is used to find the rows. This is the best possible plan to find the row.
fulltext A fulltext index is used to access the rows.
index_merge A ‘range’ access is done for for several index and the found rows are merged. The key column shows which keys are used.
index_subquery This is similar as ref, but used for sub queries that are transformed to key lookups.
index A full scan over the used index. Better than ALL but still bad if index is large and the table is joined against a previous table.
range The table will be accessed with a key over one or more value ranges.
ref_or_null Like ‘ref’ but in addition another search for the ‘null’ value is done if the first value was not found. This happens usually with sub queries.
ref A non unique index or prefix of an unique index is used to find the rows. Good if the prefix doesn’t match many rows.
system The table has 0 or 1 rows.
unique_subquery This is similar as eq_ref, but used for sub queries that are transformed to key lookups

常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)

  • ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

  • index: Full Index Scan,index与ALL区别为index类型只遍历索引树

  • range: 只检索给定范围的行,使用一个索引来选择行

  • ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

  • eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件

  • const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system

  • NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

 

SQL性能优化目标
此篇文有关 MySQL慢SQL语句常见诱因插图

免责声明:文章内容不代表本站立场,本站不对其内容的真实性、完整性、准确性给予任何担保、暗示和承诺,仅供读者参考,文章版权归原作者所有。如本文内容影响到您的合法权益(内容、图片等),请及时联系本站,我们会及时删除处理。

作者: 小小编

为您推荐

dell R710 更换raid卡后,raid卡信息没有了,处理方案

dell R710 更换raid卡后,raid卡信息没有了,处理方案

1.将一台服务器(A)的硬盘依次拔出,按相同顺序插入另一台同样配置的服务器(B) 2.启动服务器(B) 3.按提示键盘按...
PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

下载 并安装 PL SQL Developer 13,默认支持中文语言 ========================...
关于一条sql语句在mysql中是如何执行的

关于一条sql语句在mysql中是如何执行的

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在my...
关于sql注入姿势总结(mysql)

关于sql注入姿势总结(mysql)

前言 学习了sql注入很长时间,但是仍然没有系统的了解过,这次总结一波,用作学习的资料。 从注入方法分:基于报错、基于布...
关于Oracle SQL外连接

关于Oracle SQL外连接

SQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。 连接...

发表回复

返回顶部