MySQL 索引（一）

1.数据访问方式

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

1.1.顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。实现比较简单，但是当表中有大量数据的时候，效率非常低下。

1.2. 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。索引是一种用于快速查询和检索数据的数据库存储结构，保存了数据库指定字段的数据位置，类似图书目录的作用；

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

 

2. 如何创建索引 

语法形式：

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

 

3. 索引的优缺点

3.1.优点

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

3.2.缺点

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占用额外的磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增删改时，索引需要动态维护，这样就降低了增删改的速度。

 

4. 索引分类

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

4.1. 存储方式分

        根据数据结构存储方式的不同，MySQL 中常用的索引，在物理上分为 B+Tree 索引 和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

4.1.1. B+Tree索引

BTree

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

首先，BTree是一个多路平衡查找树（Balance Tree，多路也就是多叉的意思），所有的叶子节点在同一高度，能够保持数据有序，同时BTree为系统大块数据的读写操作做了优化。

B+Tree

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

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

 

BTree与 B+Tree的主要区别：

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

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次数一定是稳定的。

4.1.2. 哈希索引

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

哈希索引的特点

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

4.2. 使用逻辑分

4.2.1. 主键索引

主键索引就是专门为主键字段创建的索引，也属于索引的一种。主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引，需要以创建或修改表结构的方式进行添加。

示例：在表中的 id 字段上添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY (id)

4.2.2.普通索引

普通索引也被称为辅助索引。是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

普通索引允许在定义索引的列中，插入重复值和空值。

创建普通索引时，通常使用的关键字是 INDEX 。

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

CREATE INDEX index_id ON tb_student(id);

4.2.3. 唯一索引

唯一索引与普通索引类似，不同的是，创建唯一性索引的目的：除了提高访问速度以外，同时还能避免数据出现重复。

唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引通常使用 UNIQUE 关键字。

CREATE UNIQUE INDEX index_id ON tb_student(id);

4.2.4. 全文索引

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

创建全文索引使用 FULLTEXT 关键字

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

CREATE FULLTEXT INDEX index_info ON tb_student(info);

4.3. 字段限制分

4.3.1. 单列索引

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

4.3.2. 组合索引

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

注意：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。

比如：在一个表中创建了一个组合索引(c1,c2,c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1,c2)和多列索引(c1,c2,c3)。