Innodb存储引擎查询输出分析--补充

目的

       根据之前《Innodb存储引擎查询输出分析》中对Innodb查询输出的逻辑处理过程的分析和测试，对Innodb的输出有了深入的了解。然而在阅读了《MySQL技术内幕--SQL编程》中P91设计的测试，并通过跟该书作者进行沟通交流。对查询输出进行补充，弥补之前没有考虑周全的地方。

测试设计

       该测试主要依据《MySQL技术内幕--SQL编程》P91页设计的测试。首先对设计进行测试；然后做相应的调整，进行进一步的测试。在此，将该设计再次描述如下。

       表结构的设计如下所示： 

CREATE TABLE animals(

id INT PRIMARY KEY,

name VARCHAR(30),

UNIQUE KEY(name)

) ENGINE=INNODB;

        测试数据设计如下所示： 

INSERT INTO animals SELECT 1, 'Tiger';

INSERT INTO animals SELECT 2, 'Dog';

INSERT INTO animals SELECT 3, 'Cat';

        测试的查询语句如下所示： 

SELECT * FROM animals;

        调整测试的数据表，具体如下所示： 

ALTER TABLE animals ADD COLUMN age INT;

        调整后的数据表进行同样的查询语句进行测试。

测试

       在给出输出查询结果之前，非常有必要首先给出查询的查询计划。因为对照查询计划，可以从查询结果中得到导致该现象的原因。

       查询语句explain select * from animals;查询计划如下所示： 

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

animals

index

NULL

name

93

NULL

3

Using index

 

       从查询计划来看，该查询使用了索引，索引为name字段的unique索引。具体为什么使用unique索引，将在之后的内容中详细从源码的逻辑处理介绍MySQL索引选择的原则。

       查询结果与书中给出的结果一致。如下所示： 

id

name

3

Cat

2

Dog

1

Tiger

        通过结果来看，确实没有按照id进行顺序输出，当然结果也是按照id有序输出的，但这只能是设计的巧合。而参照查询计划来看查询输出的结果，可以发现实际的输出顺序是按照name有序输出的。原因就是MySQL虽然是全表查询，但是查询优化器找到了unique索引，认为查询字段通过索引查找的效率要远远高于全表扫描。因此，查询计划是索引查询。由于unique索引是辅助索引，所以输出结果按照辅助索引的键值进行输出的。当然本书作者测试情况比较特殊，而没有从普遍情况到特殊情况的全面描述，导致了读者的误解。至少我跟我的同事误解了该问题的解释。

       为了进一步对该问题进行研究和测试，对表进行调整，添加一个字段，改变当前的查询计划，然后对比测试。

       同样，首先给出查询计划，同样的查询语句，查询计划如下所示： 

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

animals

ALL

NULL

NULL

NULL

NULL

3

 

        从查询计划可以看出，查询类型为全表扫描，没有通过索引进行查询。

       查询结果如下所示 

id

name

1

Tiger

2

Dog

3

Cat

        从查询结果来看，查询输出以id主键顺序输出。这仍然符合《Innodb存储引擎查询输出分析》中的逻辑分析和测试的过程。

结论

       通过以上测试和对比，《Innodb存储引擎查询输出分析》中的测试仅仅对普遍的全表扫描进行的测试和描述，而没有考虑特殊的查询。而《MySQL技术内幕--SQL编程》中的测试正好从特殊的情况，认为查询输出不一定按照主键输出的。

       因此，查询输出的规则依赖于查询语句的类型。如果查询进行全表扫描或者使用主键索引的方式进行查询，那么查询输出按照主键的顺序进行输出；如果查询使用辅助索引进行查询，那么查询输出按照辅助索引的键值顺序进行输出。

参考

1、《Innodb存储引擎查询输出分析》

2、《MySQL技术内幕--SQL编程》