MySQL---索引

MySQL—索引

---

一、索引概述

索引（index）是帮助MySQL高效获取数据的数据结构（有序），在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，也可以把索引比喻成一本书的目录，通过目录可以快速定位目标内容。

二、索引

1.特点

优势
1）提高数据的检索效率，降低了数据库的IO成本。
2）通过索引列对数据进行排序，降低数据排序的成本，减低CPU的消耗。
劣势
1）索引列需要占有空间。
2）索引大大提高了查询效率，同时却也降低更新表的速度，例如对表进行INSERT、UPDATE、DELETE时，效率较低。

2.分类

分类	含义	特点	关键词
主键索引	根据表中创建的主键创建索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	同一个表可以建立多个唯一索引	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

3.聚集索引&二级索引

在InnoDB存储引擎中，根据索引的存储形式，又可以分为聚集索引与二级索引两种：

分类	含义	特点
聚集索引（Clusterered Index）	将数据存储与索引放到一起，索引结构的叶子节点存储行数据（参考图：1）	必须有而且只有一个
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（参考图：2）	可以存在多个

聚集索引选取规则：

• 如果主键存在，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果关系表既没有主键也没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据
• 二级索引的叶子节点挂的是该字段值对应的主键值（查询时可能需要进行回表查询）

什么情况小需要回表查询呢？具体见下图：

具体工程如下：

• 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
• 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
• 最终拿到这一行的数据，直接返回即可。

4.索引语法使用

创建索引：

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name ;

---

总结

以上是对MySQL索引的基本概述