mysql中的索引

索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。

索引的常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种。介绍下三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树。

1.哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。

优点：
查询、插入、删除时间复杂度O(1)。
缺点：
哈希表这种结构适用于只有等值查询的场景，无法处理排序的情况

2.有序数组

优点：
等值查询和范围查询时间复杂度O(logn)。
缺点：
插入、删除时间复杂度O(n)。

有序数组索引只适用于静态存储引擎。

3.二叉搜索树

特点：
在树中的任意一个节点，其左子树中的每个节点的值，都要小于这个节点的值。
右子树节点的值都大于这个节点的值。
查询、插入、删除时间复杂度O(logn)。

mysql中的索引

一、数据结构角度

1. B+树索引

B+ 树的特点（m叉树）：

1.每个节点中子节点的个数不能超过 m，也不能小于 m/2。
2.根节点的子节点个数可以不超过 m/2，这是一个例外。
3.m 叉树只存储索引，并不真正存储数据，这个有点儿类似跳表。
4.通过链表将叶子节点串联在一起，这样可以方便按区间查找。
5.一般情况，根节点会被存储在内存中，其他节点存储在磁盘中。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个子节点。
一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。磁盘随机读一个数据块是很慢的，为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。
以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

2.Hash索引

InnoDB存储引擎会监控对表上各索引页的查询，如果监控到某个索引页被频繁查询，并诊断后发现如果为这一页的数据创建Hash索引会带来更大的性能提升，则会自动为这一页的数据创建Hash索引，并称之为自适应Hash索引。自适应Hash是通过缓冲池中B+树的页进行构建的，建立速度很快，不需要对整张表的数据都构建Hash索引，所以我们又可以把自适应Hash索引看成是索引的索引，。注意一点就是InnoDB只会对热点页构建自适应索引，且是由InnoDB自动创建和删除的，所以不能人为干预是否在一张InnoDB的表中创建Hash索引。

3. Full-Text全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
like + % 就可以实现模糊匹配了，但like + % 在文本比较少时是合适的，对于大量的文本数据检索是非常慢的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。全文索引的实现是倒排索引。

版本支持：
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

二、从逻辑角度

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
联合索引：联合索引指多个字段上创建的索引。遵循最左前缀原则。
全文索引：是目前搜索引擎使用的一种关键技术。

三、从物理存储角度

1.聚簇索引
索引B+ Tree的叶子节点存储了整行数据的被称之为聚簇索引。

InnoDB的聚簇索引：

一个表只能有一个聚簇索引。InnoDB中会对主键建立聚簇索引。
如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。
如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。

2.非聚簇索引
索引B+ Tree的叶子节点只存储了主键的值被称之为非聚簇索引。

覆盖索引
是指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。
因为非聚簇索引只存储了主键的值，所以如果希望通过索引查询主键之外的值的时候，需要通过索引查询到主键，再通过主键的索引查询到完整的数据。需要多查询一次。

索引失效

1.对索引字段做函数操作，可能会破坏索引值的有序性，因此就用不上索引了。
例如：

select count(*) from table where month(t_modified)=7;
select * from table where id + 1 = 10000;

2.隐式类型转换
例如：

select * from table where id=110717;
select * from table where CAST(id AS signed int) = 110717;

如果id 的字段类型是 varchar(32)，索引也会失效。

3.隐式字符编码转换。
如果两张表字符集不同，一个是 utf8，一个是 utf8mb4，做关联查询的时候索引也会失效。

4.索引遵循最左前缀原则，是按以%开头的Like模糊查询，索引失效。

使用（B-Tree）索引时，有以下一些技巧和注意事项：

索引设计：

主键建议使用自增主键
表的主键一般都要使用自增 id，不建议使用业务 id ，是因为使用自增 id 可以避免页分裂。
mysql 的InnoDB 引擎底层数据结构是 B+ 树，所谓的索引其实就是一颗 B+ 树，一个表有多少个索引就会有多少颗 B+ 树。然后 mysql 在底层又是以数据页为单位来存储数据的，一个数据页大小默认为 16k，当然你也可以自定义大小，也就是说如果一个数据页存满了，mysql 就会去申请一个新的数据页来存储数据。
如果主键为自增 id 的话，mysql 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。
如果主键是非自增 id，为了确保索引有序，mysql 就需要将每次插入的数据都放到合适的位置上。当往一个快满或已满的数据页中插入数据时，新插入的数据会将数据页写满，mysql 就需要申请新的数据页，并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂，这个大量移动数据的过程是会严重影响插入效率的。
索引字段尽量使用数字型（简单的数据类型）
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
尽量不要让字段的默认值为NULL
索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
所以我们在数据库设计时尽量不要让字段的默认值为NULL，应该指定列为NOT NULL，除非你想存储NULL。你应该用0、一个特殊的值或者一个空串代替空值。