Mysql 索引设计的要点

      刚加入尚游科技两天，发现很多表的设计都不是很合理，这里把表的设计要点稍微的记录一下。
     
     Mysql数据库内部数据存储都是采用b+树的方式存储，所有的数据都是按照primary key的顺序来存放，所以当我们通过主键进行查找或者order by,group by等操作，会相应的快很多，因为主键已经排过序。当然主键是根据何种规则来生成的呢？
     1.当你在建表的时候定义过primary key,Innodb会使用该主键作为b+树的key值来进行排序。
     2.如果你没有在建表的时候定义primary key,Mysql会使用第一个唯一非空( first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.)的索引
     3.如果表没有定义主键或者没有合适的唯一索引，Innodb会在内部生成一个隐藏的的主键，插入该表的所有记录都会按照该主键键来生成b+树。该主键占用6个字节，并随着记录的插入而单调递增。
    可以简单看一下B+tree的简单结构：
    
   可以看到该表其实类似ORACLE的IOT(INDEX ORGANIZATION TABLE) 表，数据按照主键排序。
   接下来我们在来看看二级索引的结构：
    
     主键的大小在某种程度上影响了二级索引的大小，所以主键的设计是十分重要的，我们来看看High performace mysql对二级索引设计的理解：
     InnoDB’s secondary index leaf nodes contain the primary key values, which serve as the “pointers” to the rows. This  strategy reduces the work needed to maintain secondary indexes when rows move or
     when there’s a data page split. Using the row’s primary key values as the pointer makes  the index larger, but it means InnoDB can move a row without updating pointers to it
    主键的大小影响着二级索引的大小，但使用主键而不是使用地址来作为"指针"，在行发生迁移的时候，对二级索引不需要更改，这点比oracle做的似乎更巧妙(oracle使用地址作为指针，使得行迁移的代价更高（比如最常见的page split）。
    当然，通过二级索引访问row的代价也相应的增加(相比oracle而言，必须根据主键扫描主表，而不像ORACLE根据地址直接定位，可谓有得必有失，任何东西都不能样样俱全!).
    我们在设计的表的时候，经常会出现如下不合理的情况
     1.设计的主键和业务相关，经常会更新
     2.设计的主键没有根据实际表未来的数据量来进行预估，而为了保险起见使用了很大的值
     3.没有创建主键，而是使用非空唯一索引来创建主键
     4.使用数据库默认的隐藏6字节主键
     
 第1种情况，导致b+树会经常的分裂，造成过多的磁盘碎片，还有可能对数据库产生一定的压力，影响正常的运行。
 第2种情况，因为我们的第二索引都是存放的索引键值+主键值，最终通过主键定位到具体的值。而主键设置的过大，会导致二级索引的值也会非常大，从而耗费了空间，在使用二级索引来读取数据的时候，也会有额外的I/O消耗。
 第3种情况，使用非空唯一索引会使我们的数据在插入的时候造成大量的随机插入，影响写的性能。
 第4种情况，主键可能不能作为查询条件来进行数据过滤，而只是作为一行的一个标识而已，并且浪费了空间。
 
 对于INNODB表，在顺序插入的情况下：
 
  
 
 而对于类似UUID之类的主键，导致不能顺序插入的情况如下：
 
所以非单调递增的主键记录插入只会导致page不断的split！
 综上所述，在设计表结构时，对主键的设计应遵循如下原则：
 1.尽量使用与业务无关的字段来作为主键，避免由于业务的更新造成b+树的分裂
 2.尽量使用无符号型整数来作为主键，根据未来数据的增长量，来预估合适的类型
 3.采用自增的形式来定义主键，保证数据的插入都插入到b+树的最右端，避免b+树的分裂带来额外的负载