mysql索引与存储结构（一）
??? 首先从一个问题说起。
??? 问题现象：
??? 查询语句如下：
-- sql1
SELECT
w.wid, w.rid
FROM warestock w JOIN product p ON w.wid = p.product_code
WHERE w.rid IN (3, 4, 5, 6, 10)
?
??? warestock 在 wid ,rid上创建了联合主键，product_code是product的主键。我期望的输出结果能按照w.wid, w.rid的顺序排序。结果却没有按照这个w.wid, w.rid升序输出结果。
????? 按照以往的知识，sql执行时应该是直接索引扫描warestock，然后再关联product时用到主键关联。然后再用rid过滤数据。 结果却并没有按照w.wid, w.rid的顺序排序。
?? 分析原因：
??? 1.怀疑是查询缓存问题：加上SQL_NO_CACHE，输出结果和sql1完全一致，说明与查询缓存无关。
??? 2.warestock 和product执行顺序的原因。把sql1换成如下子查询，确实也能实现按照顺序wid，rid输出结果。
SELECT
w.wid, w.rid
FROM warestock w where w.rid IN (3, 4, 5, 6, 10)
and exists(select 1 from product p where p.product_code = w.wid)
?
??? 或者忽略warestock表的主键索引也能达到同样的效果，sql如下，但是根本原因还是没有找到。
SELECT SQL_NO_CACHE
w.wid, w.rid
FROM warestock w IGNORE INDEX (PRIMARY) JOIN product p ON w.wid = p.product_code
WHERE w.rid IN (3, 4, 5, 6, 10)
?
??? 3.再次分析执行计划，发现warestock并没有按照用到主键索引。而是index_pc，再查看索引： show index from product?? 不知道什么时候在product上的run_type字段建了索引。到此为止，查明原因：product并没有使用主键索引导致没有按照顺序输出结果。
??
?
?? 解决方案如下：
??????? 1.删除掉离散度较低的索引 或者
???????? 2.强制使用product的主键索引
SELECT SQL_NO_CACHE
w.wid, w.rid
FROM warestock w JOIN product p FORCE INDEX (PRIMARY) ON w.wid = p.product_code
WHERE w.rid IN (3, 4, 5, 6, 10)
?? 结论、扩展：
?
?? 1.MyIsam和innodb数据和索引存储结构方式是不一样的。
??????? Innodb主键索引是主键和数据列放在一起，每个普通索引都带着主键列，并且索引顺序是按照普通索引列和主键列排序；MyIsam主键索引是和数据列放开存放的，普通索引和主键索引没有任何区别，普通索引也不会保存主键索引的信息。从以下sql的执行计划中也可以看出这一点：
-- sql2
CREATE TABLE test1 (
`wid` bigint(20) NOT NULL COMMENT 'SKU代码(商品编号)',
`orgid` int(11) NOT NULL DEFAULT '0',
`topwpid` int(11) DEFAULT NULL,
PRIMARY KEY (`wid` ),
KEY k_orgid (`orgid`)
) ENGINE=Innodb DEFAULT CHARSET=utf8;
?
?
insert into test1 values(12345,12345,111),
(12346,12346,111) ;
-- sql3:
select wid from test1
?
?
??? 索引和主键存放在一起，用到了覆盖索引。执行计划如下：
???
?
??? 修改test1 的存储引擎为MyISAM，执行计划如下：
???
?
????? 上面的执行计划用到的索引不一样，原因是存储引擎是InnoDB时sql3用到了覆盖索引，而存储引擎是MyISAM时，只需主键索引扫描即可，因为主键索引和数据列是分开存放的。 这也从侧面验证了如上面所说的Innodb和MyIsam普通索引的存储方式。
? `?? 如此说来sql3执行时，存储引擎是Innodb时，如果暗示优化器忽略IGNORE INDEX普通索引k_orgid，则会按照PRIMARY扫描。
??????? 如果忽略PRIMARY，则会按照普通索引k_orgid扫描
??????? 存储引擎是MyIsam时,如果IGNORE INDEX普通索引k_orgid，则依然按照PRIMARY扫描。如果忽略PRIMARY，则会执行全表扫描。
?????? 2.根据统计信息分析后，优化器会合理选择小表驱动大表的执行计划。
??????? sql1 的执行计划可以看出这一点
?
