MySQL索引的特性及使用小结

0. 引言：索引的基本概念：

索引出现的唯一目的，就是为了提高 查询 的效率，就像书的目录一样。
如果没有索引，在表中查找某个符合条件的数据，就需要逐条遍历。

索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录。

应该注意到：虽然索引大大提高了查询速度，但同时也降低了更新表的速度，例如对表进行 INSERT、UPDATE、DELETE等操作时，MySQL不仅要保存数据，还要保存一下索引文件。另外，建立索引还会额外占用磁盘空间的索引文件。因此，应合理的建立索引，不应过多的使用索引造成滥用。

索引的分类：
索引可分为单列索引、多列索引。其中单列索引又分为 主键索引、唯一索引、普通索引这三类。

主键索引： 每个表只能有一个主键索引，主键索引所在列的数据 不能为空、不能重复；
（如果列上有重复数据，若试图在此列上建立主键索引将会返回失败；如果列上已经建立主键索引，若试图修改某行数据与其他行重复，则会修改失败）

唯一索引： 同一个表上可以有多个唯一索引，唯一索引所在列上的数据不能重复，可以为空。

普通索引：

1. 索引的常见模型：

1.1 索引的三种实现模型：

索引的出现是为了提高查询效率，但是实现索引的方式有很多种，所以这里就引入了 “索引模型” 的概念。
可以 用于提高读写效率 的数据结构 很多，这里介绍常见的三种简单的数据结构：
哈希表、有序数组、搜索树。（InnoDB引擎使用的B+树）

1.1.1 哈希表：

哈希表就是 key-value键值对 存储数据结构。
特点是 插入、查找、删除 的时间复杂度都是O(1)，但是 “区间查找” 的速度很慢，时间复杂度是 O(n)，例如查找 [ID_card_X, ID_card_Y] 这个区间的所有用户，则需要遍历所有的元素。

所以哈希表这种数据结构适用于 只有 “等值查询” 的场景，比如 Memcached、Redis 等NoSQL 引擎。

1.1.2 有序数组：

有序数组中等值查找的时间复杂度是 O(logN)，因为元素的值有序，所以可进行 二分查找，查找的时间复杂度是 O(logN)，而插入和删除由于涉及到元素在内存中的移动，效率会低一些。

所以，有序数组只适用于 “静态存储引擎”，比如你要保存的是 2017年某个城市的所有人口信息，这类不会再修改的数据，查找的效率很高。

1.1.3 搜索树：

二叉搜索树的查找、插入、删除的时间复杂度都是 O(logN)。

单从数据结构搜索效率上来说，二叉搜索的效率是最高的，然而在实际应用中，大多数的数据结构、文件系统并不会使用二叉树，而是使用 B树 或 B+树。其原因在于数据库的数据存储在磁盘上，如果每访问一次树的节点就要访问一次磁盘，这样的查询是很慢的。

使用B+树的目的就是让一个查询尽量的减少读盘，让查询过程访问尽量少的数据块。

举例来说，在机械硬盘时代，从磁盘随机读一个数据块需要 10ms 左右的寻址时间，对于一个有 100万行数据的表来说，如果使用二叉树存储，树的深度是20，单独访问一行数据需要的时间可能就是 20*10ms = 0.2s，这样的查询是很慢的；而以InnoDB的一个整数字段索引为例，其中的N叉树的N差不多是1200，当这棵树的高度是4的时候，就可以存放 1200^3=17亿。考虑到树的根结点数据块总是在内存中，一个10亿行的表上一个整数字段的索引，查找一个值只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

在 MySQL中，索引是在 存储引擎层 实现的， 所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。

Tips：从索引的三种实现模型再来深入理解“MySQL与Redis的区别”：

实现索引有三种方式：哈希表（key-value）、有序数组（适合做静态表）、搜索树（B+树）。

哈希表的key-value存储结构决定其只适用于只有“等值查询”的场景，也就是说一对一的查询，输入一个key，返回一个其对应的值，时间复杂度是O(1)，非常快，Redis、Memecached这些NoSQL都是基于哈希表结构，NoSQL存在的问题就是无法高效的进行“区间查找”，或者说“范围查找”，而这是MySQL这种传统的关系型数据库的强项，百度百科中对“关系型数据库”的描述是：
关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。

其中，“以行和列的形式存储数据”，从底层的数据来看就是使用 B+树，MySQL插入、删除、查找（包括范围查找）的时间复杂度都是 O(logN)。

总结下来就是，在实际项目的后台开发中，MySQL适用于持久化存储所有数据，按照数据类型分为不同的表，但由于MySQL的底层数据结构是B+树且存取操作需要访问磁盘，所以MySQL的执行效率不高，因此引入Redis作为“缓存层”，Redis基于内存存取，且使用哈希表作为底层数据结构，将一些常用的数据缓存在Redis中，提高应用程序的访问效率。

1.2 InnoDB的索引模型：

在InnoDB中，表是根据 主键顺序 以索引的形式存放的，这种存储方式的表称为“索引组织表”。又因为我们前面提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

以上图中结构为例：其中id为主键索引，k为普通索引：
主键索引的叶子节点存储的是“整行数据”（数据内容本身），非主键索引的叶子节点存储的是 “主键的值”。
（InnoDB中主键索引也称 聚簇索引：cluster index；非主键索引也称为 二级索引：secondary index）

1.2.1 基于主键索引与普通索引的区别是什么？

如果查询语句是：

select * from T where ID=500;

即 主键查询方式，则只需要搜索 ID这棵B+树；如果查询语句是：

select * from T where k = 5; 

即 普通索引查询方式，则需要先搜索 k索引树，得到ID的值为500，再到ID索引树搜索由此，这个过程称为“回表”。

也就是说，基于非主键索引的查询需要多扫描一颗索引树。
因此，我们在应用中应该尽量使用主键查询。

1.3 索引维护：

1.3.1 应尽量使用自增主键：

结论：出于<性能>和<存储空间>的考虑，应尽量使用 “自增主键”（AUTO_INCREMENT），而不应该使用“业务逻辑的字段”做主键。

具体原因：
（1）自增主键的插入数据模式，符合我们前面提到的 递增插入的场景，这样每次在插入一条新记录时，都是“追加操作”，都不涉及挪动其他记录，也不会触发叶子节点的分裂（B+树的特点）。而有业务逻辑的字段做主键，则往往不容易保证“有序插入”，这样写数据成本相对较高。

（2）另外，除了考虑 写数据（插入节点）的性能以外，还可以从“存储空间”的角度来看：
假设你的表中有一个唯一字段，比如字符串类型的身份证号，这时不应该使用身份证号做主键，而是应该使用自增字段做主键。
由于每个 非主键索引的叶子节点上都是主键的值，如果使用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只需要4个字节，如果是长整型则需要8个字节。
显然，主键的长度越小，普通索引（二级索引）的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间 两方面考虑，自增主键往往是更合理的选择。

1.3.2 什么时候适合使用业务逻辑字段 做主键？

当业务场景是：
（1）只有一个索引；
（2）该索引必须是唯一索引；

这就是典型的KV场景，此时由于没有其他索引，所以就不需要考虑其他索引的叶子节点大小的问题，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

1.3.3 自增主键的写法举例：

# NOT NULL PRIMARY KEY AUTO_INCREMENT
mysql> 	CREATE TABLE my_tb (
			runoob_id INT NOT NULL AUTO_INCREMENT,
			runoob_title VARCHAR(100) NOT NULL,
			submission_date DATE,
			PRIMARY KEY(runoob_id)
		)ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.3.4 为什么B+树比B树更适合做数据库索引？

（1）B+树的读写代价更低；
（2）B+树的查询效率更加稳定；
（3）B+树便于 “范围查找”（这是最重要的原因，范围查找是数据库的常态）。

1.3.5 覆盖索引：

在上文提到的“回表”的搜索过程中，如果执行的语句是

select ID from T where k between 3 and 5;

这是只需要查找ID的值，而ID的值已经是k索引树上的叶子节点了，因此可以直接提供查询结果，不需要回表。
也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称之为“覆盖索引”。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

2. 普通索引和唯一索引，应该怎么选择？

索引的分类：

MySQL的索引分为：单列索引、组合索引。
其中单列索引又分为：主键索引、唯一索引、普通索引。

唯一索引是什么？
要求 索引列 的值必须唯一，但允许有空值。

2.1 普通索引与唯一索引在 查找、更新 操作时的区别：

2.1.1 查找：

① 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一条记录，直到碰到第一个不满足 k=5 条件的记录；
② 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

但是这种不同带来的性能差异微乎其微，这是因为InnoDB的数据是按“数据页”为单位来读写的，当需要读一条记录的时候，并不是将这个记录单独的从磁盘读出来，而是以“页”为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是 16KB。

2.1.2 更新：

索引的更新过程，涉及到 change buffer 的使用，change buffer是一种为了减少随机读写而将操作先缓存到内存再集中写入磁盘的机制。

当需要更新一个数据页时，需要先判断数据页是否在内存中：如果数据页在内存中，则直接更新；如果这个数据页不在内存中的话，在不影响一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

什么时候真正更新：
① 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个数据页有关的操作；
② 系统后台线程也会定期执行 merge操作；
③ 数据库正常关闭（shutdown）的过程中，也会执行merge操作。
（将change buffer中的操作应用到“原数据页”，得到最新的结果，称为merge）

只有普通索引才能使用 change buffer，唯一索引不能使用 change buffer，原因是：
对于唯一索引，所有的更新操作（更新、插入）都要先判断这个操作是否违反了 唯一性约束。而这必须要将数据页读入到内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要更新change buffer了。

综上，可以这样认为，唯一索引的优点是保证了数据的唯一性，但是在 查找 和 更新 的性能上，普通索引要优于唯一索引。所以，如果没有保证行数据唯一这样的需求的话，出于性能上的考虑，应该优先选择普通索引。

2.1.3 change buffer 和 redo log 的执行过程：

二者的作用都是为了减少随机读写。
以下面的语句为例说明MySQL在执行更新操作时的过程：

mysql> insert into t(id, k) values (id1, k1), (id2, k2);

假设MySQL此时选用的是K索引树，且k1所在数据页在内存中，k2所在数据页不在内存中，此时更新语句会执行如下过程：

① Page 1 在内存中，直接更新内存；
② Page 2 不在内存中，就在内存的change buffer区域，记录下这行操作；
③ 将上述两个动作记入 redo log 中。

3. 查看MySQL执行时选择的索引：

MySQL中一张表是可以支持多个索引的，使用哪个索引是由MySQL来确定的。然而如果MySQL选错了索引，一条本来可以很快执行的语句可能会变得速度很慢。

3.1 force index：强制MySQL使用某个指定的索引：

select * from T force index(a) where a between 10000 and 20000;

注意index指的是 KEY_NAME，而不是 COLUMN_NAME：（因为一个column上可能建立了多个索引）

mysql> show index from t;
+-------+---------------+-------------+------------+
| Table | Key_name      | Column_name | Index_type |
+-------+---------------+-------------+------------+
| t     | PRIMARY       | a           | BTREE      |
+-------+---------------+-------------+------------+
| t     | my_normal_key | a           | BTREE      |
+-------+---------------+-------------+------------+
2 rows in set (0.00 sec)

# 正确操作：
mysql> select * from t force index(PRIMARY) where a = 1; 
# 错误操作：
mysql> select * from t force index(a) where a = 1;

3.2 explain 命令：查询一个命令将如何执行：

mysql> explain select * from t where a between 10000 and 20000;
+----+-------+-----------------------+---------+---------+------+
| id | table | possible_keys         | key     | key_len | rows |
+----+-------+-----------------------+---------+---------+------+
| 1  | t     | PRIMARY,my_normal_key | PRIMARY | 4       | 1    |
+----+-------+-----------------------+---------+---------+------+
1 row in set, 1 warning (0.00 sec)

从explain命令的输出结果中可以看到，这条select查询语句可选的索引包括PRIMARY 和 my_normal_key，执行时MySQL选择的索引是PRIMARY。

3.3 show columns 命令：查询一个表中的列信息：

有时候我们只想查看表中的列信息而不想查询表中元素，或者对于一个空表，select命令是查看不到任何信息的，这时可以使用 show columns 命令：

mysql> show columns from runoob_test;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| runoob_id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| runoob_title    | varchar(100) | NO   |     | NULL    |                |
| submission_date | date         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

可查看到列名及列属性。

3.4 slow_query_log：慢查询：

在MySQL中，会引发性能问题的慢查询，大体有以下三种可能：
① 索引没有设计好；
② SQL语句没有写好；
③ MySQL选错了索引。

可通过启动慢查询日志的方法，找到导致查询过慢的语句，在配置文件中修改：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 5

或者通过命令方式：（重启后失效）

mysql> set global slow_query_log = 1;
mysql> set global slow_query_log_file = "/var/log/mysql/slow-query.log";
msyql> set global long_query_time = 5;

采集到日志后，使用 mysqldumpslow 工具进行分析：

mysqldumpslow -s c -t 10 /var/log/mysql/mysqld-slow.log	#取出使用最多的10条慢查询
mysqldumpslow -s t -t 3 /var/log/mysql/mysqld-slow.log	#取出查询时间最慢的3条慢查询

4. 索引的相关操作：

4.1 索引的创建：

4.1.1 创建单列索引：

# 1. 创建普通索引：
# 第 1 种方式：
CREATE INDEX <index_name> ON <table_name>(column_name);
# 举例：
mysql> create index title_index on runoob_tb1(runoob_title); //添加runoob_tb1表中runoob_title列为索引，取名为title_index

# 第 2 种方式：
ALTER TABLE <table_name> ADD INDEX <index_name>(column_name);
# 举例：
mysql> alter table runoob_tb1 add index author_index(runoob_author); //添加runoob_tb1表中runoob_author列为索引，取名为author_index


# 2. 创建唯一索引：
CREATE UNIQUE INDEX <index_name> ON <table_name>(字段名);


# 3. 创建主键索引：
# 主键索引不允许有空值（NOT NULL）
ALTER TABLE <table_name> ADD PRIMARY KEY <index_name>(column_name);
CREATE INDEX <index_name> ON <table_name>(column_name); 

4.1.2 创建组合索引：

注意多个单列索引不叫组合索引，组合索引是有多个字段名捆绑作为一个索引。

CREATE INDEX <index_name> ON <table_name>(column_name_1, column_name_2, ...);

# 举例：
mysql> create index multi_index on runoob_tb1(runoob_title, runoob_author); 

4.2 索引的删除：

# 删除：
DROP INDEX <index_name> on <table_name>;
# 举例：
mysql> drop index title_index on runoob_tb1;

4.3 索引的查询：

# 查询：
SHOW INDEX FROM <table_name>;
# 举例：
mysql> show index from runoob_tb1;