MySQL之索引

索引

基本存储结构

• 各个数据页可以组成一个双向链表
• 而每个数据页中的记录又可以组成一个单向链表
• • 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
  • 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

索引分类

聚簇索引

B+树本身就是一个目录，或者说本身就是一个索引。它有两个特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

2. • 页内的记录是按照主键的大小顺序排成一个单向链表。
   • 各个存放用户记录的页也是根据页中记录的主键大小顺序排成一个双向链表。
   • 各个存放目录项的页也是根据页中记录的主键大小顺序排成一个双向链表。

3. B+树的叶子节点存储的是完整的用户记录。

   所谓完整的用户记录，就是指这个记录中存储了所有列的值。

二级索引

同聚簇索引类似，不同的是：

​ 1.B+树的叶子节点存储的并不是完整的用户记录，而只是非主键列+主键这两个列的值。

​ 2.拿到主键值后，再通过主键值去查找全部数据。

联合索引

以两个不同的列的大小为排序规则建立的B+树称为联合索引，它的意思与分别为两个不同的列建立索引的表述是不同的，不同点如下：

• 建立联合索引只会建立如上图一样的1棵B+树。
• 为两个不同的列建立索引会分别以两个不同的列的大小为排序规则建立2棵B+树。

哈希索引

1.适合select ...from table_name where keys=key_name 这样的等值查询，优势明显，一旦适合，它带来的性能提升是非常显著的。

2.不适合范围查询和模糊查询，因为经过哈希算法之后，值不是连续的。

3.当然也不支持最左匹配原则

4.InnoDB引擎中提供了特殊的功能“自适应哈希索引”

MyISAM存储引擎的索引

1.实现都是通过B+树，只是在叶子节点中存储的不是具体需要的信息，而是数据所对应的行号。

2.取到行号之后，再通过回表的方式查询具体信息,创建的索引全部是二级索引。

索引原则

最左匹配原则

• 索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
• 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
• 因此，列的排列顺序决定了可命中索引的列数。

=、in自动优化顺序

如有索引 (a,b,c,d)，查询条件 c>3andb=2anda=1andd<4与 a=1andc>3andb=2andd<4等顺序都是可以的，MySQL会自动优化为 a=1andb=2andc>3andd<4，依次命中a、b、c。

总结

1，何时使用聚簇索引或非聚簇索引：

使用动作描述	使用聚簇索引	使用非聚簇索引
列经常被分组排序	√	√
返回某范围内的数据	√	×
一个或极少不同的值	×	×
小数目不同的值	√	×
大数目不同的值	×	√
频繁更新的列	×	√
外键列	√	√
主键列	√	√
频繁修改索引列	×	√

2，索引不会包含有NULL值的列：只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列有NULL值，那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时，不要让索引字段的默认值为NULL。

3，使用短索引：假设，如果有一个数据类型为CHAR(255)的列，在前10个或20个字符内，绝大部分数据的值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省I/O操作。

4，索引列排序：MySQL查询只使用一个索引，因此如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下，不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列也创建组合索引。

5，LIKE语句操作：一般情况下，不建议使用LIKE操作；如果非使用不可，如何使用也是一个研究的课题。LIKE "%aaaaa%"不会使用索引，但是LIKE "aaa%"却可以使用索引，等同于范围搜索。

6，不要在索引列上进行运算：在建立索引的原则中，提到了索引列不能进行运算，这里就不再赘述了。