谈扫描的行数与访问类型-mysql

通过一个例子来分析扫描的行数与访问类型来分析优化慢查询。
新建一张演员表，ddl如下：
CREATE TABLE test.film_actor (
id INT NOT NULL DEFAULT 1 COMMENT ”,
name VARCHAR(45) NULL COMMENT ”,
sex VARCHAR(2) NULL COMMENT ”,
age TINYINT(4) NULL COMMENT ”,
PRIMARY KEY (id) COMMENT ”)
DEFAULT CHARACTER SET = utf8
COMMENT = ‘演员表’;

这里默认使用的存储引擎是InnoDB.
插入一定数量的数据后，查询所有记录如下：
select * from film_actor
id name sex age
‘1’,’刘德华’,’m’,’57’
‘2’,’张学友’,’m’,’57’
‘3’,’黎明’,’m’,’57’
‘4’,’郭富城’,’m’,’57’

执行如下查询的查询计划如下
explain select * from film_actor where name = ‘刘德华’
id select_type table type possible_keys key key_len ref rows Extra
‘1’,’SIMPLE’,’film_actor’,’ALL’,NULL,NULL,NULL,NULL,’4’,’Using where’

其中，rows表示扫描了4行记录才能查询出name为刘德华的数据行;type是ALL表示是全表扫描；Extran是Using where表示在mysql服务器层使用了where进行过滤从存储引擎返回的数据。
如果在name列上建立一个btree索引，ddl如下：
ALTER TABLE test.film_actor
ADD INDEX idx_name USING BTREE (name ASC) COMMENT ”;

再次执行
explain select * from film_actor where name = ‘刘德华’
那么结果如下：
id select_type table type possible_keys key key_len ref rows Extra
‘1’,’SIMPLE’,’film_actor’,’ref’,’idx_name’,’idx_name’,’138’,’const’,’1’,’Using index condition’
可以看到，rows为1，即仅仅需要扫描一行就可以得到想要查询的数据行；type的值是ref；Extra的值是Using index condition，这个值表示在存储引擎层使用了where过滤。

总结：一般mysql能够使用如下三种方式应用where条件

1. 在索引中使用where过滤不匹配的记录，这个是在存储引擎层完成的。

2. 使用索引覆盖（在Extra列中出现Using index）扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在mysql服务器层完成的，但是无须在回表查询记录，举个例子，下面的查询就是这种情况：
   explain select name from film_actor where name = ‘刘德华’

3. 从数据表返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）。这是在mysql服务器层完成，mysql需要从数据表读取记录，然后过滤。

说明：扫描的行数和访问类型并不是唯一影响查询的因素，也要考虑sql响应时间等，比如，如下查询
explain select * from film_actor where name = ‘刘德华’
虽然Extra列中出现了Using index condition，并且扫描的行数rows是1，但是这个查询是需要回表查询的，所以在数据量非常大的情况下并不是最好的。