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