阿里巴巴DBA,原去哪儿网DBA。专注于MySQL源码研究、DBA运维、CGroup虚拟化及Linux Kernel源码研究等。 github:https://github.com/HengWang/ Email:king_wangheng@163.com 微博 :@王恒-Henry QQ :506437736
分类: Mysql/postgreSQL
2012-08-21 19:16:31
目的
根据之前《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编程》中的测试正好从特殊的情况,认为查询输出不一定按照主键输出的。
因此,查询输出的规则依赖于查询语句的类型。如果查询进行全表扫描或者使用主键索引的方式进行查询,那么查询输出按照主键的顺序进行输出;如果查询使用辅助索引进行查询,那么查询输出按照辅助索引的键值顺序进行输出。
参考
2、《MySQL技术内幕--SQL编程》