Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1735333
  • 博文数量: 107
  • 博客积分: 1715
  • 博客等级: 上尉
  • 技术积分: 3168
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-18 18:42
个人简介

阿里巴巴DBA,原去哪儿网DBA。专注于MySQL源码研究、DBA运维、CGroup虚拟化及Linux Kernel源码研究等。 github:https://github.com/HengWang/ Email:king_wangheng@163.com 微博 :@王恒-Henry QQ :506437736

文章分类

全部博文(107)

文章存档

2014年(2)

2013年(38)

2012年(67)

分类: 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编程》中的测试正好从特殊的情况,认为查询输出不一定按照主键输出的。

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

参考

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

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

阅读(2180) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~