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