MySQL高性能索引创建（一）

MySQL高性能索引创建（一）

在MySQL中，索引是在存储引擎层，不是在服务器层面实现的，所以，索引没有统一的标准：不同存储引擎的索引工作方式并不一样。我们先看一下MySQL支持的索引类型，以及他们优缺点：

索引类型

B-Tree索引

大多数MySQL引擎都支持这种索引，不过底层的存储引擎也可能使用不同的存储结构。例如NDB集群存储引擎内部实际上用了T-Tree结构；InnoDB使用的是B+Tree。
B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。如下图大概反应了B-Tree索引是如何工作的。

B-Tree索引能够加快访问数据的速度，是因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。
B-Tree适用的查询类型（以下表为例）：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
#创建name，age的复合索引。
create index code_name_age on student(code,name,age)

• 全值匹配
  全值匹配指的是和索引中的所有列进行匹配，可用于查找code为001、年龄为21的数据。
• 匹配最左前缀
  只使用索引的第一列，例如：复合索引第一列。
• 匹配前缀列
  可以只匹配值的开头部分，例如：name为魏开头的名字，like ‘魏%’。
• 匹配范围值
  用于查询范围值，例如查询age在20到29之间的人。
• 精确匹配某一列并范围匹配到另外一列
  复合索引第一列为全匹配，第二列为范围匹配。例如：where code = ‘001’ and name like ‘魏%’
• 只访问索引的查询（也叫覆盖索引）
  查询只需要访问索引，而无须访问访问数据行。
  B-Tree索引的限制：
• 不是按照索引最左列开始查找，则无法使用索引。where后面不跟code条件，name和age的相关查询不能匹配索引。
• 不能跳过索引中的列。如果不指定name列，关于age的查询也不能使用索引，索引只能使用code第一列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如：where where code = ‘001’ and name like ‘魏%’ and age = 21，这个查询索引只能作用前两列，like是一个范围查询，所以通常将可能会使用范围查询的索引列顺序向后放置。

B+Tree

B+Tree实际为B-tree的演变，B+tree数据结构图如下：

B+Tree与B-Tree区别：

1. B+Tree非叶节点不保存数据相关信息，只保存关键字和子节点的引用。
2. B+Tree关键字对应的数据保存在叶子节点中。
3. B+Tree叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。

InnoDB索引采用B+Tree的数据结构，因为B+Tree数据结构比B-Tree更具有以下优势：

1. B+Tree的查询效率相比B-Tree更加稳定，由于数据只存在在叶子结点上，所以查找效率固定为O(log n)
2. B+Tree相比B-Tree减少了I/O读写的次数。内存读取索引文件时，B+Tree的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字。
3. B+Tree把数据读取的从随机I/O转变为顺序I/O.
4. B+Tree叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B-Tree由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+Tree的效率更高。

哈希索引

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效注。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hashcode),哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
因为索引自身只需存储对应的哈希值，索引的结构十分紧凑，所以哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引只支持等值比较查询,包括=、IN()、 <=> (注意<和<=>是不同的操作)。也不支持任何范围查询，例如WHERE age> 20。
• 当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

空间数据索引（R-Tree）

MyISAM引擎支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。MySQL对于空间数据支持并不完善，开源数据库对空间数据解决比较好的是Postgres SQL中的PostGIS。

全文检索

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一 样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
对于MySQL中全文检索在日常中使用较少，对于检索来说，大部分会选择Elasticsearch，分布式多用户能力的全文搜索引擎来代替。

索引相关内容会在下方列表统一维护

MySQL高性能索引创建（一）链接：https://blog.csdn.net/yhz_1/article/details/119972312