MySQL笔记——索引篇第一话

最近在学习 MySQL 的内容，重头梳理了一下相关的知识点，在这里记录一下。

这里主要讲一下 MySQL 索引方面的知识。

什么是索引

索引是一张表，它保存了主键与索引字段，指向了实体表的记录。

如果说记载了各项记录的实体表是一本书的话，那么索引就是这本书的目录，利用它可以对记录进行快速的查找，提高查询效率。

索引分为两种，一种是单列索引，另一种是组合索引。

单列索引，顾名思义，就是每个索引只包含了一列，一张表可以有多个单列索引，而组合索引则是一个索引包含多个列。

索引的实现

了解了什么是索引之后，就会想着怎么去利用数据结构去实现索引。

首先，索引与实体表记录之间存在着一一对应的映射关系，最容易想到的能够反映映射关系的数据结构就是哈希表。

哈希表是 key-value 类型存储数据的数据结构，只需要输入待查找的 key，就可以获得对应的 value 值。

哈希表的优点在于哈希表查找的时间复杂度是 O(1)，查找效率非常的高。但是哈希表也存在着缺点，那就是哈希表的存储的数据是无序的，如果需要进行区间查询，效率会非常的低。

其次，可以做为存储结构的是有序数组，有序数组的优点是查找效率高，同时它也支持区间查找，但是有序数组的插入十分低效，每一次插入都需要移动插入位置后的所有数据。适用于存储不会再修改的的数据，即静态存储引擎。

最后则是 N 叉树，利用 N 叉树既能保证搜索的效率，以及区间查找，又能保证插入修改的效率。因此在 InnoDB 引擎中就是使用 B+ 树来存储数据的。

根据 B+ 树叶子节点的不同，将索引分为两种：

• 主键索引（聚簇索引 clustered index）：存储的是整行数据。
• 二级索引（secondary index）：存储的是主键的值。

使用主键索引查找时，直接查找主键索引所在的 B+ 树；而利用二级索引进行查找时，会先在二级索引所在的 B+ 树上查找到对应的主键值，然后再去根据获取到的主键值去主键索引所在的 B+ 树上进行查找，这个过程被称之为回表。

基于二级索引的查询，会多遍历一次 B+ 树，因此尽量多使用主键查询。

索引的操作语句

1. 创建索引

普通索引

创建最基本的索引，没有任何的限制条件：

CREATE INDEX indexName ON tableName(columnName(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length.

唯一索引

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

CREATE UNIQUE INDEX indexName ON tableName(columnName(length));

2. 修改表结构方式添加

普通索引

ALTER table tableName ADD INDEX indexName(columnName)

唯一索引

ALTER table tableName ADD INDEX indexName(columnName)

3. 创建表的时候指定

普通索引

CREATE TABLE tableName(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
); 

唯一索引

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
); 

4. 删除索引

DROP INDEX [indexName] on tableName;

或者使用 alert 语句

ALTER TABLE tableName DROP INDEX (indexName)；