2019-03-11

MySQL | 《高性能MySQL》读书笔记之索引篇

基本概念

索引在MySQL中也称作为「键」（key），是存储引擎用于快速找到记录的一种数据结构。
基本使用如下。如果在 actor_id 上建有索引，MySQL 将使用该索引找到actor_id为5的行。「先在索引上按值查找，然后根据匹配的索引记录找到对应的数据行」
```
SELECT first_name FROM actor WHERE actor_id=5
```
索引可以包含一个或更多的列。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一个列的索引是大不相同的。

索引的类型

B-Tree/B+Tree 索引
```
CREATE TABLE people (
    last_name varchar(50) NOT NULL,
    first_name varchar(50) NOT NULL,
    dob date NOT NULL,
    gender enum('m','f') NOT NULL,
    key(last_name,first_name,dob)
)
```
- 是被指代最多的索引。使用 b-tree/b+tree 数据结构来存储数据。 InnoDB使用的是B+Tree，且根据主键引用被索引的行。
- 加快访问数据的速度。存储引擎不再需要进行全表扫描来获取数据。而是采用索引的根节点开始进行搜索。
- 对索引列是采用顺序存储，很适合查找范围数据。
- 支持的查询类型：
  - 全值匹配。就是和索引中的所有列进行匹配。
  - 匹配最左前缀。只使用索引的第一列。
  - 匹配列前缀。只匹配某一列的值的开头的部分。
  - 匹配范围值。顾名思义，就是匹配某一个范围的人。
  - 精确匹配某一列并范围匹配另外一列。比如第一列全匹配「姓氏」，第二列「名字」用作范围匹配。
  - 只访问索引的查询。
  - 还可以用于查询中的ORDER BY操作
- 如果不是按照索引的最左列开始查找，则无法使用索引。
- 不能跳过索引的列。如果不指定 first_name，则 MySQL 只能使用索引的第一列。
哈希索引
- 在 MySQL 中，只有 Memory 引擎显式支持哈希索引。
- 基于哈希表实现，只有精确匹配索引所有列的查询才有效。
空间数据索引（R-Tree）
- MyISAM 表支持空间索引。
全文索引
- 一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。更加类似搜索引擎。

索引的优点

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。

索引的优化

独立的列。如果 WHERE 子句中使用了函数或其他运算符，会导致无法使用索引。
索引尽可能不重复。「索引的选择性」越高，则查询效率更高。唯一索引的选择性是1，性能是的最好的。
使用列前缀索引。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。前缀索引是一种能够索引更小、更快的有效办法。缺点是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
反向索引。将字符串反转后存储，并基于此建立前缀索引。
多列索引。也可称之为复合索引，为每个列创建独立的索引或者按照错误的顺序创建多列索引都是不当的。索引合并。在 MySQL 5.0 和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。但是可能存在一些问题。对多个索引做相交操作，可能表示需要一个多列索引，而不是多个独立列的单列索引。同时通常需要耗费大量的 CPU 和内存资源。这种情况下查询的成本被「低估」，导致该执行计划还不如直接走全表扫描。
多个索引列的最佳顺序：将选择性最高的列放到索引最前列。
聚簇索引。表示在同一个结构中保存了 B-Tree 索引和数据行。一个表只能有一个聚簇索引。
- 优点：
  - 可以把相关数据保存在一起。
  - 数据访问更快。
  - 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
- 缺点：
  - 提高了I/O密集型应用性能。如果数据全部都放在内存中，则访问的顺序就没有那么重要。此时聚簇索引的优势没有发挥出来。
  - 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
  - 更新聚簇索引列的代价很高。因为必须将每个行移动到新的位置。
  - 当插入行或者移动行时，可能面临「页分裂（page split）」的问题，可能会导致表占用更多的磁盘空间。
  - 二级索引可能比想象的要更大。
  - 二级索引访问需要两次索引查找，而不是一次。
覆盖索引
- 概念：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为「覆盖索引」。 MySQL 中只能使用 B-Tree 索引做索引覆盖。
- 优点：
  - 索引条目通常远小于数据行大小。减少了缓存的负载，覆盖索引对于I/O密集型的应有帮助。因为索引比数据更小，更容易全部放入内存中。
  - 因为索引是按照列值顺序存储的，因此对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
  - 如 MyISAM 引起在内存中只缓存索引，数据则依赖于操作系统来缓存。因此访问数据需要发生系统调用，导致性能下降。
  - 索引覆盖对InnoDB的聚簇索引特别有用。

一些其他的注意点

不要重复建立索引。MySQL 的唯一限制和主键显示都是通过索引实现的。
- 冗余索引：如果创建了索引（A，B），在创建索引（A）就是冗余索引。由于索引（A）相当于索引（A，B）的前缀索引。
- 重复索引：在同一个字段上建立主键、唯一索引、普通索引等。
定期删除未使用的或者使用率很低的索引。
在InnoDB中，使用索引可以减少行锁的使用
在 MySQL 的 InnoDB 中，在显示上索引可能存在大小写展示的不同。但是其实是不区分大小写的。9.2.3 Identifier Case Sensitivity