MySQL索引的理解

一、索引的概述

在MySQL中，访问数据行的方式有两种：顺序访问和索引访问。

1、顺序访问

• 在进行查询时，对表进行全表扫描，找到符合条件的目标数据。
• 顺序访问的实现比较简单，但是在面对大量数据的时候，查找的速度就比较低。

2、索引访问

索引是一种用于快速查询和检索数据的数据结构，由表中的一列或几列组成。通过索引，查询数据时不用读完所有数据，而只是查询索引列。

可以理解为索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

二、索引的创建

1、创建索引的方式

修改表结构

ALTER TABLE 表名 ADD INDEX 索引名 (字段名);

或创建索引结构

CREATE INDEX 索引名 ON 表名(字段名);

2、索引的优缺点

优点：

可以提高数据的查询速度；

可以提高分组和排序的速度；

通过创建唯一索引可以对表中的数据进行约束；

缺点：

创建和维护索引需要耗费时间；

创建索引会额外占用磁盘空间；

对表中的数据进行增删改时，也要进行索引的动态维护；

三、索引的分类

1、按存储方式分类：B+Tree索引和HASH索引

B+Tree：是BTree的一中特殊类型。

BTree是一个多路平衡查找树，所有的叶子结点在同一高度，有够保证数据的有序，数据是存储在每个节点中的。

B+Tree：是对BTree功能的增强：

B+Tree中数据存储在叶子节点中，根节点和支节点只有索引；

每一个叶子节点都持有下一个叶子节点的指针，叶子节点之间形成了一个双向链表。

BTree和B+Tree的区别：

• BTree中数据是储存在每个节点中，而B+Tree中的数据是存储在叶子节点中，非叶子节点中只有索引；
• BTree的查找可能会停在任意一个节点上，而B+Tree的查找相对稳定；
• B+Tree的非叶子节点可以存储更多的索引值；
• B+Tree的叶子节点之间使用双向链表，提高顺序查询效率；
• 相比于BTree，B+Tree的区间查找效率更高；

MySQL为什么使用B+Tree？

• B+Tree的全表扫描能力更强：因为数据是在叶子节点中存储的，并且叶子节点之间使用了双向链表；要想进行全表扫描，只需遍历叶子节点即可；
• B+Tree的排序能力更强：因为叶子节点中的数据都是从大到小(或从小到大)排序的；
• B+Tree磁盘读取能力更强：B+Tree的根节点和枝节点不保存数据，所以根节点和枝节点同样大小的情况下，保存的关键字要比BTree要多；
• B+Tree查询性能稳定：数据都是保存在叶子节点中的，每次查询数据时，IO次数是稳定的；

HASH索引也称散列索引，是一种基于哈希表实现的索引结构，将字段中的内容通过哈希算法，计算出哈希值，用于访问哈希表结构中的对应索引，该索引指向数据行。如果两个字段计算出的哈希值一样，就在该位置生成一个链表。

哈希索引的特点：

• 无法用于分组和排序，因为哈希表中索引是散列的；
• 精确查找的效率非常高；

2、按实际使用的字段数量分类：单列索引和组合索引。

单列索引：只包含表中的一列。

组合索引：在表的多个字段上创建一个索引。只有查询条件中使用这些字段中的第一个字段时 ，索引才会生效。

3、按逻辑方式分类

主键索引：不允许有null值，不必须有重复值；在指定表的主键时会自动创建主键索引，不允许使用"alter table"创建主键索引，要使用"create table"修改表结构的方式创建主键索引引。

ALTER TABLE 表名ADD PRIMARY KEY (字段名);

辅助索引：

• 唯一索引：使用unique index关键字，允许有null值，不必须有重复值

CREATE UNIQUE INDEX 索引名 ON 表名(字段名);

• 普通索引：使用index关键字，允许有null值和重复值

CREATE INDEX 索引名 ON 表名(字段名);

• 前缀索引：对文本的前几个字符创建索引，只适用于字符串类型的数据
• 全文索引：查找文本中的关键字，允许有null值和重复值

四、索引失效

在判断索引是否失效时，可以使用explain语句，观察SQL执行计划。

id：执行顺序；table：表名；

type：主要有三种类型，all：全表扫描，range：范围查找，ref：引用；

possible_keys：可能使用的索引名称；key：使用的索引名称；rows：扫描行数。

索引可以提高查询的速度，但是以下几种情况会使索引失效，

1、使用like关键字进行查询时，如果匹配字符串的第一个字符为通配符"%"，索引会失效。

但是，"%"不是第一个位置，索引就不会失效。

2、对表中的多个字段创建一个索引，如果查询条件中不包含这些字段中的第一个字段，索引会失效。

3、查询语句只有OR关键字时，OR前后有一个条件的列不是索引时，索引会失效。

4、查询语句中使用函数时，索引会失效。

5、字段内容会null，索引会失效。

6、查询条件中使用不等于运算(!=，<，>，not in)时，索引会失效。

7、数据类型不匹配时，索引会失效。

五、覆盖索引

如果查询的列，用过索引项的信息可以直接返回，则该索引称为查询SQL的覆盖索引。

SQL中要求尽量不要使用select *，要写明具体要查询的字段。其中一个原因就是在可以使用到覆盖索引的情况下，不需要进入到数据区，数据就能直接返回，提升了查询效率。