MySQL 索引

MySQL 索引

一、概述

• 在 MySQL 中，通常有两种方式访问数据库表的行数据：顺序访问和索引访问
• 在 MySQL 中默认使用的数据库引擎是 innoDB，存储结构是 B+Tree

1、顺序访问

• 顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。
• 顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如：在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能

2、索引访问

• 索引是一种特殊的数据结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配（全表扫描）。
• 索引访问是通过遍历索引来直接访问表中记录行的方式。
• 使用这种方式的前提，是对表建立一个索引，在数据表的列上，进行索引的创建之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序

• 可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10多页的音序表中直接查找。这样就可以大大节省时间
• 所以，可以理解为索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表

二、创建索引

ALTER TABLE `table_name` ADD INDEX 索引名 ( 字段名 );
1

ALTER TABLE user_info ADD INDEX index_login_phone_number ( login_phone_number );
1

三、索引的优缺点

1、优点

• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。
• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性

2、缺点

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

• 索引需要占用额外的磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了增删改的速度。

• • 索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显

四、索引分类

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 中的索引，可以从存储方式、使用逻辑和实际使用等不同角度来进行分类

1、按存储方式区分

• 索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
• 这样的话，索引是使用过程中，就要产生磁盘对索引文件的 I/O 消耗，相对于内存存取的消耗，I/O 存取的消耗要更高。所以评价索引的优劣最重要的指标，就是在查找过程中磁盘 I/O 操作次数的复杂度，而索引的本质都是基于某种数据结构来设计的，所以，索引的数据结构要尽量减少查找过程中磁盘 I/O 的存取次数
• 根据数据结构存储方式的不同，MySQL 中常用的索引，在物理上分为 B+Tree 索引 和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围

（1）B+Tree 索引

1、BTree

B+Tree 是BTree 的一种特殊变种

• BTree 是一个 多路平衡查找树（Balance Tree），多路也就是多叉的意思
• 所有叶子节点在同一高度，保证数据有序

假设要从图中查找id = X的数据，BTREE 搜索过程如下：

1. 取出根磁盘块，加载40和60两个关键字。
2. 如果X = 40，则命中；如果X < 40走P1；如果40 < X < 60走P2；如果X = 60，则命中；如果X > 60走P3。
3. 根据以上规则命中后，接下来加载对应的数据， 数据区中存储的是具体的数据或者是指向数据的指针。

2、B+Tree

B+Tree 在原有 BTree 的基础上补充了如下特性：

• B+Tree 根节点和支节点没有数据区，数据data全部存储在叶子节点中；
• 每一个父节点的值，都包含在叶子节点中，是叶子节点中==最大（或最小）==的元素；
• 每一个叶子节点，都持有一个指向下一个叶子节点的指针，形成了有序链表

假设为字段ID添加索引，搜索X = 1的数据，**B+TREE** 搜索过程如下：

1. 取出根磁盘块，加载1，28，66三个关键字。
2. X <= 1 走P1，取出磁盘块，加载1，10，20三个关键字。
3. X <= 1 走P1，取出磁盘块，加载1， 8， 9三个关键字。
4. 已经到达叶子节点，命中1，加载对应数据节点

3、BTree 个 B+Tree 的主要区别

1. BTree的数据（或指向数据的指针）存在每个节点里，而 B+Tree的数据（或指向数据的指针）仅存在叶子节点里，非叶子节点只有索引。
2. BTree 的查找，可能会在任意一个节点停止，而 B+Tree的查找相对稳定。
3. B+Tree 的非叶子节点可以存储更多的索引值，阶数更高
4. B+Tree 的叶子节点使用双向链表链接，提高顺序查询效率
5. 相比于 BTree ， B+Tree 在区间查找方面更胜一筹

4、MySQL为什么选择B+Tree

1. B+Tree**全表扫描能力更强。**如果我们要根据索引去进行数据表的扫描，如果基于BTREE进行扫描，需要把整棵树遍历一遍，而B+TREE只需要遍历所有叶子节点即可（叶子节点之间形成有序列表）。
2. B+Tree排序能力更强
3. B+TREE**磁盘读写能力更强。**他的根节点和枝节点不保存数据区，所以根节点和枝节点同样大小的情况下，保存的关键字要比BTREE要多。所以，B+TREE读写一次磁盘加载的关键字比BTREE更多。
4. B+Tree查询性能稳定。B+Tree数据只保存在叶子节点，每次查询数据，查询IO次数一定是稳定的

（2）哈希索引

• 哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY存储引擎和 HEAP存储引擎支持这类索引。
• 哈希索引，是基于哈希表实现的一种索引结构。将字段的内容（key）通过哈希算法，计算该字段的哈希值，用于访问哈希表结构中的对应索引，该索引指向数据行

1、哈希索引的特点

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。
• InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的优点，比如：快速的精准查找

2、使用逻辑区分

根据索引的具体用途，MySQL 中的索引在使用逻辑上分为以下 4 类：

（1）普通索引

• 普通索引也被称为辅助索引。是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
• 普通索引允许在定义索引的列中，插入重复值和空值
• 创建普通索引时，通常使用的关键字是 Index

示例：

在 tb_student 表中的 id 字段上建立名为 index_id 的索引

CREATE INDEX index_id ON tb_student(id);
1

（2）唯一索引

• 唯一索引与普通索引类似，不同的是，创建唯一性索引的目的：除了提高访问速度以外，同时还能避免数据出现重复。
• 唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一
• 创建唯一索引通常使用 UNIQUE关键字

示例：

在 tb_student 表中的 id 字段上建立名为 index_id 的索引

CREATE UNIQUE INDEX index_id ON tb_student(id);
1

（3）主键索引

• 主键索引就是专门为主键字段创建的索引，也属于索引的一种。主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
• 创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE ``INDEX 语句创建主键索引，需要以创建或修改表结构的方式进行添加

示例：

在 tb_student 表中的 id 字段上添加主键索引

ALTER TABLE tb_student ADD PRIMARY KEY (id)
1

（4）全文索引

• 全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。全文索引允许在索引列中插入重复值和空值。
• 不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
• 创建全文索引使用 FULLTEXT关键字

示例：

在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引

CREATE FULLTEXT INDEX index_info ON tb_student(info);
1

3、按实际的使用情况区分

（1）单列索引

• 单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
• 单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

（2）组合索引

• 组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。
• 多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用

示例：

在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引。该索引创建好了以后，查询条件中必须有 name 字段才能使用索引

CREATE INDEX index_na ON tb_student(name,address);
1

• 一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。
• 比如：在一个表中创建了一个组合索引(c1,c2,c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1,c2)和多列索引(c1,c2,c3)

五、不同存储引擎的索引结构

1、MyISAM 存储引擎

• MYD文件：数据文件，所有的数据保存在这个文件中。
• MYI文件 ：索引文件

• 如果要查询id = 40的数据：
• 先根据MyISAM索引文件（user_myisam.MYI）去找id = 40的节点，通过这个节点的数据区拿到真正保存数据的磁盘地址，再通过这个地址从MYD数据文件（user_myisam.MYD）中加载对应的记录

如果有多个索引，在MyISAM存储引擎中，主键索引 和 **普通索引（辅助索引）**是同级别的，没有主次之分

2、InnoDB存储引擎

• Innodb主键索引是一个聚集索引。
• 聚集索引的概念：数据库表行中数据的物理顺序和键值的逻辑顺序相同。所以，叶子节点的数据区保存的就是真实的数据，在通过索引进行检索的时候，命中叶子节点，就可以直接从叶子节点中取出行数据

如果有多个索引，在Innodb存储引擎中，主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值。

假如要查询name = C 的数据，其搜索过程如下：

1. 先在辅助索引中通过C查询最后找到主键id = 9.
2. 在主键索引中搜索id=9的数据，最终在主键索引的叶子节点中获取到真正的数据。
3. 所以通过辅助索引进行检索，需要检索两次索引

3、B+Tree索引在Innodb 和 MYISAM在的区别

六、索引失效

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况

1、查询语句中使用LIKE关键字

• 在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为通配符“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被命中使用。
• 为north_american_box_office表中的zhongwenpianming字段添加索引index_zhongwenpianming

CREATE INDEX index_zhongwenpianming ON north_american_box_office(zhongwenpianming);
1

通过使用EXPLAIN语句，观察SQL执行计划，检查索引是否生效

示例1

在where条件中，使用=进行查询，索引正常被使用

--索引生效
EXPLAIN SELECT * FROM north_american_box_office
WHERE zhongwenpianming = '狮子王';
1
2
3

示例2

在where条件中，使用like进行查询，如果匹配字符串的第一个字符为通配符“%”，索引不会被使用

--索引失效
EXPLAIN SELECT * FROM north_american_box_office
WHERE zhongwenpianming LIKE '%狮%';

或者

EXPLAIN SELECT * FROM north_american_box_office
WHERE zhongwenpianming LIKE '%狮';
1
2
3
4
5
6
7
8

示例3

在where条件中，使用like进行查询，如果匹配字符串的第一个字符为正常字符内容，索引正常被使用

--索引有效
EXPLAIN SELECT * FROM north_american_box_office
WHERE zhongwenpianming LIKE '狮%';
1
2
3

2、查询语句中使用多列索引

• 多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用
• 为north_american_box_office表中的shangyingtianshu字段和leijipiaofang字段添加多列索引index_tianshu_piaofang

--创建索引
CREATE INDEX index_tianshu_piaofang ON north_american_box_office(shangyingtianshu, leijipiaofang);
1
2

示例1

--索引有效
EXPLAIN SELECT * FROM north_american_box_office
WHERE (shangyingtianshu BETWEEN 9 AND 13) AND leijipiaofang >=3000
1
2
3

示例2

--索引失效
EXPLAIN SELECT * FROM north_american_box_office
WHERE leijipiaofang >=3000 
1
2
3

示例3

--索引失效
EXPLAIN SELECT * FROM north_american_box_office
WHERE shangyingtianshu >= 9 AND leijipiaofang >=3000
1
2
3

• 该索引失效的原因是MySQL在优化器阶段，发现全表扫描比走索引效率更高，因此就放弃使用索引。当MySQL发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。
• 类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况

3、查询语句中使用OR关键字

查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引。如果 OR 前后有一个条件的列不是索引，那么查询中将不使用索引

示例1

--索引有效
EXPLAIN SELECT * FROM north_american_box_office
WHERE zhongwenpianming LIKE '狮%' OR shangyingtianshu BETWEEN 90 AND 100
1
2
3

示例2

--索引失效
EXPLAIN SELECT * FROM north_american_box_office
WHERE zhongwenpianming LIKE '狮%' OR leijipiaofang BETWEEN 90 AND 100
1
2
3

虽然OR关键字前的第一个条件中的字段zhongwenpianming虽然存在索引，但是由于多列索引index_tianshu_piaofang并不包含leijipiaofang字段，导致OR关键字后面的条件不存在索引，所以导致整条SQL语句的索引失效

4、查询语句中使用函数

查询语句中使用函数时，会造成查询中将不使用索引

示例1

--索引失效
EXPLAIN SELECT * FROM north_american_box_office
WHERE length(zhongwenpianming) = 9
1
2
3

七、索引选择原则

索引的选择，可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引

1、列的离散性

• 离散性的计算公式：count(distinct column_name):count(*)，即：去重后的列值个数 VS 总个数。
• 离散度越高，选择性越好。因为离散度越高，通过索引最终确定的范围越小，最终扫描的行数也就越少，索引效率也就越高

2、为经常需要排序、分组和联合操作的字段建立索引

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作/3、

3、为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度

4、限制索引的数目

• 索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长
• 如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。
• 注意避免冗余索引 ，冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。例如：(name,city)和(name)这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的

5、尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如：对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多

6、数据量小的表最好不要使用索引

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

7、尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和 BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度

8、删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

八、什么是覆盖索引

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

例如：通过name进行数据查询

select * from users where name = ?
1

需要需要在name索引中找到name对应的id，然后通过获取的id在主键索引中查到对应的行。整个过程需要扫描两次索引，一次name，一次id

如果我们查询只想查询id的值，就可以改写SQL为

select id from users where name = ?
1

• 因为只需要id的值，通过name查询的时候，扫描完name索引，我们就能够获得id的值了，所以就不需要再去扫描id索引，就会直接返回。此时，该索引就是当前SQL的覆盖索引。
• 当然，如果你同时需要获取age的值

select id,age from users where name = ?
1

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