MySQL基础篇--执行计划扫描方式详解

type列

全表扫描 ALL

在查询结果集在达到全表数据>15-30%，优化器有可能会选择全表。

在查询条件中出现隐式转换统计信息过旧，不准确。

条件列是函数或者计算。

使用 IS NULL 和 IS NOT NULL 联合索引，前导列。

查询条件出现以下语句(辅助索引列)

USE world DESC city;

DESC SELECT * FROM city WHERE countrycode <> 'CHN';

DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');

DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';

注意:对于聚集索引列,使用以上语句,依然会走索引优化器会自动转换为范围查询

RANGE

DESC SELECT * FROM city WHERE id <> 10;

DESC SELECT * FROM city WHERE id != 10;

DESC SELECT * FROM city WHERE id not in (10,20);

索引全扫描 INDEX

二级索引记录比聚簇索引记录小得多，聚簇索引要存储用户定义的所有列，二级索引只需要存放索引列和主键，而且这个过程不用执行回表操作。所以直接扫描全部的二级索引记录比直接扫描全部的聚簇索引成本要小很多。这种扫描全部二级索引的访问称为index访问方法。

比如：联合索引（a,b,c） select a,b,c from t where b='';

这种破坏了联合索引最左前缀法则，所以优化器会选择index查询方法。

order by 主键是典型的index访问方法。

索引范围扫描 RANGE

使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间。（不包含一个单点区间，或者全表范围查询）这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

< >= <= between and in or like 索引范围扫描辅助索引 > < >= <= LIKE IN OR 主键 <> NOT IN

DESC SELECT * FROM city WHERE id<5;

DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';

DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');

辅助索引等值查询 REF

搜索条件为二级索引列与常数进行等值查询比较，形成的扫描区间为单点扫描区间。

非唯一性索引等值查询

desc select * from city where countrycode='CHN';

非唯一性索引扫描，返回匹配某个单独值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

eq_ref

多表连接，非驱动表连接条件是主键或唯一键。唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

DESC SELECT b.name,a.name FROM city AS a 
JOIN country AS b 
ON a.countrycode=b.code 
WHERE a.population <100;
DESC country

+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                | 4188 |    33.33 | Using where |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+

ref_or_null

在ref的基础上，同时查找该列中值为null的记录，对应的扫描区间是[NULL,NULL]以及等值的单点扫描区间。注：在二级索引树的结构上，值为NULL的记录会被放在索引的最左边。

const

通过主键或者唯一二级索引列与常数的等值比较来定位一条记录的访问方法定义为： const 。 mysql> desc select * from city where id=10;

System

表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

Const：表示通过索引一次就找到了

扫描方式优劣排名

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

秒客网