数据库索引分析

上篇文章详细分析了B树B+树的原理及应用，其中提到了索引，索引又分主键索引、唯一索引、全文索引、单列索引、多列索引、聚簇（聚集）索引，那么接下就分析一下这些数据结构到底是如何在数据库索引中发挥作用的，以及在不同存储引擎当中又有什么不同。

先来介绍一下索引的各个种类：

1.PRIMARY KEY()：ALTER TABLE table_name ADD PRIMARY KEY('columm');

2.UNIQUE：ALTER TABLE table_name ADD UNIQUE('column') ;

3.FULLTEXT：ALTER TABLE table_name ADD FULLTEXT('column');

4.普通单列索引：ALTER TABLE table_name ADD INDEX index_name('column');

5.多列索引：ALTER TABLE table_name ADD INDEX index_name('column1','column2','column3');

各个索引特点：

1.主键索引：不允许有空值，一般建议为自增的序列

2.唯一索引：不允许有重复值，但可以有空值

3.针对较大的文本数据，但较耗空间和时间

4.普通单列索引：没有什么限制

5.多列索引：遵循最左前缀匹配原则

MyISAM以及InnoDB都使用了B+树作为索引。但两者的具体索引实现上又有不同。索引有主键索引及辅助索引，区别在于辅助索引可以有重复键值，而主键索引不行。

MyISAM的索引是非聚集索引，具体表现在结构与数据的分离。MyISAM的B+树的叶子结点的data域中存储的是地址，与该地址相映射的是另一个文件中所存储的数据。MyISAM的辅助索引与主键索引区别不大。

MyISAM支持全文索引。

MyISAM的主键索引图：从图中可以看到，MyISAM的B+树的叶子结点的data域存放的不是数据，而是地址，因此需要从主键值获取到数据地址，再从地址获取具体数据。索引保存在"tableName.myi"文件当中，数据保存在"tableName.myd"文件中.

MyISAM的辅助索引图：与主键索引图类似

InnoDB的索引是聚集索引，因为InnoDB的B+树的叶子结点的data域存储的就是数据，省去了根据地址查找数据的步骤。InnoDB的辅助索引的data域就是主键的数值，所以进行辅助索引前，会先获取主键的信息，所以主键不宜过大，因为会到致辅助索引过大，很消耗资源。

InnoDB支持事务，InnoDB在每行数据中增加了DB_TRX_ID、DB_ROLL_ID、DB_ROW_ID来支持事务

InnoDB主键索引：从图中我们可以看到叶子结点的data域不再像MyISAM一样存放的是地址而是直接的数据。

InnoDB在MySQL5.6之后也支持了全文索引

InnoDB辅助索引：从图中可以看到，辅助索引的叶子结点data域是包含了主键的

Memory使用了Hash作为索引，Memory引擎MySQL重启后，数据会丢失（可用init-file解决）。

Hash索引的好处，通过一次值运算就可以找到索引数据所在的位置，相比B+树需要多次磁盘I/O才能找到数据的做法，Hash在这方面显然效率要更高速度更快，但是MyISAM及InnoDB不采用Hash索引的原因是，Hash索引存在许多缺点。

1.Hash是通过一个个值来进行计算索引的，所以不支持范围索引，因为原先有序的键值，进过hash函数计算之后就有可能不连续了，因此不能加速排序操作。

2.Hash无法进行like'%XXX%'这种模糊查询，这也可以理解为范围查询

3.哈希表不支持多列联合索引的最左匹配

4.在有大量重复值得时候，hash的效率也是很受影响的，因为存在hash碰撞问题

5.每次都要全表查询

B+树特点：

1.在大多数情况下，有排序、分组、范围查询等情景时，使用B+树。

2.由于每次查找操作都是从根结点一直找到叶子结点才结束，所以每次查找时间稳定。

3.B+树层数较低（3层），数值有序的值都在附近，结合磁盘顺序读写、OS预先读取的特点，都使得B+树很适合做索引，而RB树值相近的数据离得较远，效率低下。

注：OS预先读取：由于磁盘与内存的存储介质的先天不同，加上磁盘机械运动的耗时，所以两者的读取速度相差很大，内存比磁盘读取要快N个数量级，所以每次从磁盘读取数据时，都会预先向后读取一部分数据，通常以页来做单位。这样做的依据是著名的局部性原理：当一个数据被使用时，其附近的数据也往往接下来要被使用，程序运行期间所需要用的数据往往比较集中。页是计算机管理存储器的逻辑快。硬件和操作系统往往把主存和磁盘存储区分割成各个大小相同的块。每个存储块称为一页（一般为4K）。主存和磁盘一般以页来交换数据。当程序需要检测到要获取的数据不在主存当中时，会触发一个缺页异常，此时系统会想磁盘发出读盘请求，磁盘找到数据的起始位置后会连着读取一个页或者几个页的内容载入内存，然后异常返回，程序继续运行。

最左匹配原则：

B+树是按照从左到有的顺序来搜索树的，例如A是主键，B,C是辅助键，则select(A,B,C)...的时候如果A相同则继续按B搜索，B相同则继续按C搜索，如果A是自增序列则不存在这个问题了，因为A就不会重复，因此推荐主键为自增序列。如select(B,C)...因为没有了A,所以会先全局搜索再按B，C的顺序再查找，此时已经没有用到索引了。

B+树的主键索引最好使用自增序列，这样每次增加数据直接在后面添加即可，如果是非单调的主键（如身份证、学号等）有可能导致B+树需要分裂与调整，不必要的操作就增加了，影响效率。

建立索引的优化建议：

1.利用最左匹配原则，MySQL会一直从左向右检索，直到检索了所有属性或遇到有范围检索（>,<,like between）就会停止匹配。如：select a = 1 and b = 2 and c > 3 and d = 4...这时d以及d之后的是不会起到索引作用的。

2.不能过多的建立索引，因为在修改数据内容时，索引是要重新构建的也就意味着需要消耗资源。

3.不同值较少的字段最好不要建立索引（性别、有效性、婚姻状况）

4.如果查询用到了函数或表达式，则该函数或表达式的索引不起作用