MySQL索引

索引是什么

       1. 索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

      2. 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

       3. 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

 

索引类型

 
主键索引
索引列中的值必须是唯一的，不允许有空值。

普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引
索引列中的值必须是唯一的，但是允许为空值。

全文索引
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空间索引
MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
 

 索引的数据结构

 

 

 

B+树是应文件系统所需而产生的一种B-树的变形树。一棵m阶的B+树和m阶的B树的差异在于: 

        (1)有n棵子树的结点中含有n个关键码； 

        (2)所有的叶子结点中包含了全部关键码的信息，及指向含有这些关键码记录的指针，且叶子结点本身依关键码的大小自小而大的顺序链接；

        (3)所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大(或最小)关键码。

 

 什么是Explain？

        Explain可以用来分析SQL语句和表结构的性能。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

explain 执行计划包含字段信息如下：

type：表示MySQL在表中找到所需行的方式，或者叫访问类型
type=ALL，全表扫描，MySQL遍历全表来找到匹配行
type=index，索引全扫描
type=range，索引范围扫描
type=eq_ref，唯一索引
type=NULL，MySQL不用访问表或者索引，直接就能够得到结果（性能最好）
possible_keys: 表示查询可能使用的索引
key: 实际使用的索引
key_len: 使用索引字段的长度
rows: 扫描行的数量
 

 索引什么时候会失效？

 

1. 数据修改：如果索引所基于的数据发生了修改，包括插入、更新或删除操作，那么索引就会失效。这是因为索引是根据数据的特定排序方式来建立的，一旦数据变化，索引就可能不再准确。

2. 数据类型变化：如果索引所基于的列的数据类型发生了变化，比如从整数变成字符串，或者从日期变成时间戳，那么索引就会失效。因为索引是根据特定的数据类型来建立的，数据类型的改变会导致索引无法正确地匹配数据。

3. 数据量变化：如果数据量发生较大的变化，比如删除了大量数据或者插入了大量数据，那么索引的效果可能会受到影响。如果数据的分布方式发生了较大变化，索引可能需要重新构建才能保持高效。

4. 索引过期：有些数据库管理系统可能会对索引设置过期时间，在达到过期时间后自动失效。这可以防止索引过时而导致查询性能下降。