mysql教程目录_mysql目录与存储结构（一）

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 的执行计划可以看出这一点

?