MYSQL 索引的学习

索引概述

什么是索引？

索引是数据库表中一列或多列的值进行排序的一种存储结构。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引是存储引擎用于快速找到记录的一种数据结构

索引的作用

作用和优点其实差不多。
为了大大加快数据的检索速度

索引的分类：

个人认为这个问题意义不大

1. 普通索引：普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。
2. 唯一索引：唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
3. 主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
4. 组合索引：组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

优缺点：

优点：

索引可以让MySQL快速地定位到表的指定位置。
然后因为索引那列基本是有序的，所以相关的列的值就会存储在一起（指在磁盘中）。
1. 索引大大减少了需要扫描的数据量
2. 索引可以将随机IO变成顺序IO

缺点：

索引是额外的数据结构，所以维护必然是需要消耗资源的
1. 数据表越大，建立和使用索引的代价将随之增长
2. .索引需要占用额外的物理空间

不同情况下使用索引

不适合使用索引的情况：

当对表中的数据进行增加、删除和修改的时候， 索引也要动态的维护，所以经常修改的字段不适合索引

索引是为了配合where快速查找记录，如果基本用不到where自然不适合
数据量少的表，大部分情况下简单的全表扫描可能比索引更高效

索引的选择性（不重复的索引值÷记录总数）越高则查询效率越高，因为选择性越高的索引可以让MySQL在查找时过滤掉更多的行，唯一索引的选择性是 1.

如果使用了运算（包括运算符和函数），那么会导致索引失效

1. 频繁更新的字段不适合建立索引
2. where条件中用不到的字段不适合建立索引
3. 表数据比较少的不需要建索引
4. 数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值
5. 参与列计算的列不适合建索引

适合使用索引的情况：

即上面的反例均可使用索引

索引的数据结构：

不同的索引类型使用的数据结构自然是不一样的。
MYSQL的INNODB存储引擎支持的有 B+树索引，哈希索引和全文索引。
如果没有特别指明一般都说是 B+树索引，这个索引的数据结构是 B+树

哈希索引和B+索引的区别

首先是数据结构不同，哈希索引结构是哈希。
哈希索引在innoDB是自适应的，INNODB存储引擎会根据表的使用情况自动为表生成哈希索引，不能手动生成。但是B+索引是可以手动生成的

B+树比 B树作为索引好在哪

首先要知道B+树对比B树的特点：

1. 非叶子节点不存储数据
2. 叶子节点的存储是逻辑上连续的，使用链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

综上
1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非叶子节点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

最左匹配原则

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

聚簇索引（聚集索引）

聚簇表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
聚簇索引只是一种数据存储方式，它在B+树的叶子节点上包含了行的所有数据。
InnoDB 将通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一且非空的索引，如果也没有，那么InnoDB则会隐式定义一个主键来作为聚簇索引。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引,也就是索引的子节点已经包含了要查询的数据，而不需要再根据主键值去二次查询
覆盖所以不包含整行记录的所有信息，故大小要远小于聚集索引。

索引设计原则

设计原则可以从不适合创建索引的原因引申。

1. 选择唯一性索引
2. 为经常需要排序、分组操作的字段建立索引
3. 为常作为查询条件的字段建立索引
4. 限制索引的数目
5. 数据量小的表最好不要使用索引
6. 尽量使用数据量少的索引，比如char(10)和char(100) 的两种字段最好使用 char(10)
7. 删除不再使用或者很少使用的索引

索引失效的情况：

1. 出现NULL值，比如用not null 或者is not null
2. 非等值或者范围查询 ，如<>、NOT、in、not exists
3. like使用前置通配符 ，like %明'是不能走索引的
4. 条件上带了函数或者运算符 ，
   查询条件上尽量不要对索引列使用函数，比如下面这个SQL
5. 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

select * from test where upper(name)='SUNYANG';

这样是不会走索引的，因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算，那么依然可以走索引。比如

select * from test where name=upper('sunyang');

前缀索引：

如果我们使用字符串列作为索引，这会让索引变得大且慢。此时我们可以索引开始的部分字符，这样就可以大大节省索引空间，从而提高索引效率，这就是前缀索引。
对于 BLOB,TEXT,或很长的 VARCHAR 类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度。
但是使用前缀索引会降低索引的选择性（不重复的索引值和数据表的记录总数的比值），所以需要选择足够长的前缀以保证较高的选择性，同时又不能太长。