MySQL的索引

MySQL索引的原理：

索引的目的：索引的目的在于提高查询效率，如果没有索引，MySQL查询必须从第一条记录开始然后读完整个表直到找到相关的行，大部分情况表越大，花费的时间就越多。

索引的数据结构：

B Tree和B+ Tree 的数据结构：

B Tree指的是Blance Tree,也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree是基于B Tree和叶子节点顺序访问指针进行实现，它具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询的性能。（B+树的特点是能够保持数据稳定有序，其插入于修改拥有较稳定的对数时间复杂度。B+树元素自底向上插入。）

B+树的结构特点：

在B+树中的节点通常被标示为一组有序的元素和子指针。

对于所有的内部节点，子指针的数目总是与元素的数目相同。

所以叶子都在相同的高度上，叶节点本身按关键字大小从小到大连接。

如上图，是一颗b+树，浅蓝色的快称为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘快1包含数据项5和65，包含指针p1,p2,p3，p1表示小于5-28的磁盘块，p2表示在28-65之间的磁盘块，p3表示大于65的磁盘快。真实的数据存在于叶子节点中，非叶子节点不存储真实数据，只存储指引索引方向的数据项

操作：

在进行查找操作时，首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的data

插入删除操作会破坏平衡树的平衡性，因此在插入和删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

MySQL索引的类型

物理角度：

1. 聚集索引
2. 非聚集索引

逻辑角度：

1. 普通索引：这是最基本的索引，它没有任何限制。
2. 唯一索引：与普通索引类似，不同的就是：索引列的值必须是唯一。
3. 主键索引：是特殊的唯一索引，不允许为空值。

数据结构角度：

1. B+树索引
2. hash索引（仅仅能满足“=”，“IN”和“<=”,">="）,不能使用范围查询
   1. 无法用于排序与分组
   2. 只支持精确查找，无法用于部分查找和范围查找
   3. 注（InnoDB存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在B+Tree索引之上在创建一个哈希索引，这样就让B+Tree索引具有哈希索引的一些优点，比如快速的哈希查找）

索引优化:

1. 独立的列：在进行查询时，索引列不能时表达式的一部分，也不能是函数的参数，否则无法使用索引

   select id from student where id+1=5;
   --上面的查询不能使用id列的索引

    

2.  

   多列索引：在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列性能更好，列入下面的语句将 id 和 age 设值为多列索引

   select id,age from student where id = 1 and age= 18;

3. 索引列的顺序：让选择性最强的索引列放在前面。（索引的选择性是指不重复的索引值和记录总数的比值。最大值为1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高）

4. 前缀索引：对于BOLB、TEXT和VARCHAR类型的列，必须使用前缀索引，只索引开始的部分字符。

5. 覆盖索引：索引包含所有需要查询的字段值

具有以下优点;

1.索引通常远小于数据行的大小，只读取索引能大大减少数据访问量

2.一些存储引擎在内存中缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用

3.对于innoDB引擎，如辅助索引能够覆盖查询，则无需访问主索引

索引的优点：

• 大大减少了服务器需要扫描的数据行
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree索引是有序的，可以用于ORDER By 和Group BY操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机I/O变为顺序I/O

索引使用的条件：

• 对于非常小的表、大部分情况下简单的全表扫描比简历索引更有效；
• 对于中到大型的表，索引就非常有效；
• 但是对于大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条条的匹配。可以使用分区技术。