关于mysql:高性能MySQL实战二索引-京东物流技术团队

咱们在上篇高性能 MySQL 实战（一）：表构造中曾经建设好了表构造，这篇咱们则是针对已有的表构造和搜寻条件为表创立索引。

咱们还是先将表构造的初始化 SQL 拿过去：

CREATE TABLE `service_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `service_type` smallint NOT NULL DEFAULT -1 COMMENT '接口类型',
  `service_name` varchar(30) DEFAULT ''COMMENT' 接口名称 ',
  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT '接口方式 1-HTTP 2-TCP',
  `serial_no` int DEFAULT -1 COMMENT '音讯序号',
  `service_caller` tinyint DEFAULT -1 COMMENT '调用方',
  `service_receiver` tinyint DEFAULT -1 COMMENT '接管方',
  `status` tinyint DEFAULT 10 COMMENT '状态 10- 胜利 20- 异样',
  `error_message` varchar(200) DEFAULT ''COMMENT' 异样信息 ',
  `message` varchinar(1000) DEFAULT ''COMMENT' 报文内容 ',
  `create_user` varchar(50) DEFAULT ''COMMENT' 创建者 ',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',
  `update_user` varchar(50) DEFAULT ''COMMENT' 更新者 ',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新工夫',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除标记',
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '工夫戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口调用日志';

现有如下搜寻条件：

依据 接口名称 来搜寻对应的调用日志
依据状态查问胜利或异样的调用日志
依据 接口名称 和状态查问调用日志
依据 音讯序号 来获取一组调用日志
依据 创立工夫 的工夫范畴查问调用日志
依据 报文内容 查问调用日志

索引是晋升查问性能最无效的伎俩 ，它能够疾速定位到记录，大大减少须要扫描的数据量，将随机 I/O 变为程序 I/O，而且 B+ Tree 索引会依据索引值顺序存储，所以也可能利用于 排序和分组。

为了在查看这些接口调用日志时有更好的体验，那么咱们就须要依据搜寻条件来创立索引。

咱们先关注下接口名称的搜寻条件，咱们能够发现：接口名称和接口类型这两个字段都能查问到雷同类型接口的日志数据，只不过它们的类型不同，前者是字符串类型，后者是整型。

这时咱们须要留神：抉择创立索引的列的类型要尽量小 。因为每创立一个索引就相当于创立了“一棵 B 树”，数据类型越小，那么索引占用的存储空间就越少， 在一个数据页内就能寄存更多的记录，因而磁盘 I/O 带来的性能损耗也就越少。

除此之外，在 MySQL 外部整型数据的比拟相比于字符串类型的比拟更简略，效率也更高。所以，咱们会抉择为接口类型创立索引而不是为接口名称创立索引。

为 接口类型 列增加索引的 SQL 如下：

alter table service_log add index index_service_type(`service_type`);

依据条件 4，音讯序号列也须要增加索引：

alter table service_log add index index_serial_no(`serial_no`);

同样地，依据搜寻条件 2，咱们为状态列增加上索引：

alter table service_log add index index_status(`status`);

这时，咱们再看看条件 3，须要为接口类型和状态增加 联结索引 。不过须要留神的是：该联结索引和曾经增加的接口类型索引是反复索引，依据联结索引的 最左匹配准则，第一列为接口类型的联结索引同样能为查问条件只有接口类型的查问服务，所以咱们须要将原有为接口类型增加的索引删掉，再创立新的接口类型和状态的联结索引。

-- 删除 index_service_type
alter table service_log drop index index_service_type;

-- 增加联结索引
alter table service_log add index index_service_type_status(`service_type`, `status`);

创立联结索引时有一个重要的经验性法令：将列值反复率最低的放到索引的最前列。如果反复的值过多，那么扫描到的数据行数也就越多，这样就会使得回表的压力很大。

通常状况下，把 WHERE 条件外面的列都独立地创立多个单列索引，在大部分状况下并不能进步 MySQL 的查问性能 。咱们应该尽可能的去思考 索引列的程序 或者创立一个 全笼罩索引。

在咱们的理论业务中，接口调用的状态简直所有都是胜利，很少会呈现失败的状况，所以这时咱们为状态列创立索引并不是很适合。因为如果咱们查问所有状态为胜利的数据，那么它可能会执行太屡次的回表操作，导致查问效率降落，可能还不如执行全表扫描来的快。然而咱们再思考另一种状况，有时咱们会依据状态为失败的记录做业务剖析或排查问题，失败的数据是比拟少的，如果咱们通过索引查问就会十分高效，所以该列索引还有必要保留。

只不过咱们在这里须要做一个解决：如果状态为胜利时，咱们为生成的 SQL 语句增加上疏忽索引的关键字 ignore index(index_name)，那么这样咱们就能达到在查问胜利状态的数据时全表扫描，而在查问失败状态的数据时应用索引了。

select * from service_log ignore index(index_status)
where status = 10;

条件 5 依据创立工夫来进行全值匹配和按值范畴匹配 非常适合创立索引：

alter table service_log add index index_create_time(`create_time`);

FULLTEXT 全文索引是一种非凡类型的索引，它查找的是文本中的关键词，而不是间接比拟索引中的值，更相似于搜索引擎所做的事件。在查问时实用于 MATCH AGAINST 操作，而不是一般的 WHERE 条件。

对于条件 5，咱们须要在接口申请的报文中依据关键字，比如说包裹号来查问特定的数据，这就使得咱们须要为报文内容列创立全文索引，SQL 如下：

alter table service_log add fulltext fulltext_message(`message`);

-- 执行查问时的语句
select * from service_log where match(message) against('123456');

全文索引在日常应用的并不多，它有许多须要留神的细节，如停用词、词干、复数和布尔搜寻等，具体的详情信息能够查看文末的参考文献。

那么，最终初始化表构造的 DDL 语句如下：

CREATE TABLE `service_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `service_type` smallint NOT NULL DEFAULT -1 COMMENT '接口类型',
  `service_name` varchar(30) DEFAULT ''COMMENT' 接口名称 ',
  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT '接口方式 1-HTTP 2-TCP',
  `serial_no` int DEFAULT -1 COMMENT '音讯序号',
  `service_caller` tinyint DEFAULT -1 COMMENT '调用方',
  `service_receiver` tinyint DEFAULT -1 COMMENT '接管方',
  `status` tinyint DEFAULT 10 COMMENT '状态 10- 胜利 20- 异样',
  `error_message` varchar(200) DEFAULT ''COMMENT' 异样信息 ',
  `message` varchar(1000) DEFAULT ''COMMENT' 报文内容 ',
  `create_user` varchar(50) DEFAULT ''COMMENT' 创建者 ',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',
  `update_user` varchar(50) DEFAULT ''COMMENT' 更新者 ',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新工夫',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除标记',
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '工夫戳',
  PRIMARY KEY (`id`),
  index index_serial_no(`serial_no`),
  index index_status(`status`),
  index index_create_time(`create_time`),
  index index_service_type_status(`service_type`, `status`),
  fulltext fulltext_message(`message`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口调用日志';

个别状况下，对于 VARCHAR、BLOB 和 TEXT 等相干类型的列创立索引时，为了晋升索引的性能和节俭索引空间，会只对字段的前一部分字符进行索引，不过这样做的毛病是使得索引的选择性升高。

索引的选择性是指不反复的索引值和记录总数的比值，能够了解为反复率越低选择性越高，惟一索引的选择性为 1。

在咱们的数据库示意例中，并没有字段适宜建设前缀索引。其中报文内容列也并不适宜创立前缀索引，因为这些报文的前缀都很类似，而且咱们在执行查问时并不会带上前缀，而是只应用关键词信息查问。

然而，前缀索引比拟重要，所以咱们在这里也对创立前缀索引的办法介绍一下。

MySQL 并不反对对这些长字符类型列的残缺内容进行索引，咱们抉择前缀长度的关键点在于：既要保障抉择足够长的前缀使得选择性较高，同时又不能太长避免占用太多的空间。

能够依据如下办法来确定前缀的长度：

首先，查看要增加索引的列呈现最频繁的一些值：

select count(0) as c, specific_column
from specific_table
group by specific_column
order by c desc
limit 10;

之后先从 3 个前缀字母开始匹配尝试：

select count(0) as c, left(specific_column, 3) as pref
from specific_table
group by pref
order by c desc
limit 10;

缓缓地减少前缀长度，直到这个前缀的选择性靠近咱们首次查问的残缺列的选择性即可。

或者，采纳如下的办法，先计算出残缺列的选择性：

select count(distinct specific_column) / count(0)
from specific_table;

而后别离计算不同前缀的选择性，直到找到与残缺列靠近的选择性前缀长度即可：

select count(distinct left(specific_column, 3)) / count(0) as sel3,
count(distinct left(specific_column, 4)) / count(0) as sel4,
count(distinct left(specific_column, 5)) / count(0) as sel5,
count(distinct left(specific_column, 6)) / count(0) as sel6,
count(distinct left(specific_column, 7)) / count(0) as sel7
from specific_table;

不过，也有例外的状况，那就是即便当初咱们抉择了比拟靠近残缺列选择性的前缀，但数据的散布依然很不平均。

这时咱们须要用该前缀执行如下查问，并与残缺列查问出的数目作比拟，察看这些呈现频率最高的前缀值与残缺列呈现频率是否靠近，否的话须要再将前缀值调大。

select count(0) as c, left(specific_column, 5) as pref
from specific_table
group by pref
order by c desc
limit 10;

-- 残缺列的呈现频率
select count(0) as c, specific_column
from specific_table
group by specific_column
order by c desc
limit 10;

最初，找到适合的前缀数创立前缀索引能够应用如下 SQL：

alter table specific_table add index index_specific_column(specific_column(7));

尽管前缀索引可能使索引更小，更快，然而咱们不能应用前缀索引做 ORDER BY 和 GROUP BY 操作，也无奈应用前缀索引做索引笼罩。

下文中咱们所说的索引如果没有特地指明类型，那么就代表咱们说的是 B+ Tree 索引，它应用 B+ Tree 数据结构来保留数据。

B+ Tree 会将所有的数据保留在叶子节点上，并且通过双向链表将叶子节点连接起来。

聚簇索引并不是一种独自的索引类型，而是一种数据存储形式，InnoDB 聚簇索引在数据页中同时保留索引和数据行，这使得它的数据拜访相比于非聚簇索引（二级索引）要快。

聚簇的意思是说 数据行和相邻的键值紧凑的存储在一起 ，因为无奈同时把数据行放在两个不同的中央，所以一个表只能有一个聚簇索引。InnoDB 依据主键聚簇数据，如果没有定义主键，InnoDB 会主动生成一个惟一的隐式主键作为聚簇索引。

咱们创立一个简略的表，并插入一些数据，来看一下 B+ Tree 索引的数据结构图：

create table demo (
    c1 int,
    c2 int,
    c3 char(1),
    primary key(c1)
)engine=InnoDB;

MySQL 是通过 数据页 来保留数据的，每个页的大小默认为 16KB，在每个数据页中都默认有最小记录 Infimum 和最大记录 Supremum，如下图所示：

咱们能够发现在叶子节点中保留了所有数据行，每个页之间通过页文件头部（File Header）记录的双向链表指针进行连贯，数据记录之间通过单向链表连贯，单向链表的指针记录在每行数据记录的 记录头信息 中。

在非叶子节点中，咱们能够发现记录的信息只有 主键值和对应的页号，因而数据页能寄存的数据更多，B+ Tree 也就能更加“矮胖”，这样就能使得磁盘 I/O 更少。个别状况下咱们用到的 B+ Tree 不会超过 4 层。

B+ Tree 依照索引列数据的大小程序排序存储，所以很适宜依照范畴来查问。每次搜寻数据都从索引的根节点开始，通过比拟节点中的值和要查找的值来找到适合的指针进入上层子节点，最终在叶子节点中找到或找不到对应的记录。

聚簇索引可能放慢咱们拜访数据的速度，然而它也有一些局限性咱们须要理解一下：

聚簇索引最大限度地进步了 I/O 密集型利用的性能，但如果 数据全副都放在内存中，则拜访的程序就没那么重要了，聚簇索引也就没什么劣势了
随着 RAM 变得更便宜，而且许多数据集不是那么大，所以将它们全副保留在内存中是十分可行的，包含可能散布在多个服务器上，这也促成了内存数据库的倒退。
插入速度重大依赖于插入程序。依照主键的程序插入行是将数据加载到 InnoDB 表中最快的形式。但如果不是依照主键的程序插入，会因页决裂影响插入速度。最好防止随机的聚簇索引，特地是对于 I/O 密集型的利用
聚簇索引列更新的代价很高，因为它会强制 InnoDB 将每个被更新的行挪动到新的地位，这也会产生页决裂，导致性能降落

二级索引是非聚簇索引，InnoDB 引擎在 B+ Tree 的叶子节点存储的不是实现的数据记录，而只是 索引列和主键列的值。如果在查问时没有产生笼罩索引的话，须要依据主键值进行回表操作以获取须要的后果。

二级索引是关系型数据库的根底，并且在文档数据库中也很广泛。许多键值存储（如 HBase 和 Volde-mort）为了缩小实现的复杂度而放弃了二级索引，然而一些（如 Riak）曾经开始增加它们，因为它们对于数据模型切实是太有用了。并且次级索引也是 Solr 和 Elasticsearch 等搜寻服务器的基石。

实际上，有两种用二级索引对文档数据库进行分区的办法：基于文档（document-based） 的分区和 基于关键词（term-based） 的分区。

* 基于文档的分区

假如咱们有一个汽车销售网站，每条数据都有惟一的 ID，咱们称之为文档 ID。咱们应用文档 ID 进行分区，并为汽车色彩字段创立二级索引，分区后果如下图所示：

这样的二级索引调配办法，使得每个分区都是独立的：每个分区本人保护本人的索引，它不关怀其余分区的数据，这种文档分区索引也被称为 本地索引。

当咱们查问红色的汽车时，须要将申请公布到所有的分区，并合并所有返回的后果，这种查询数据库的办法被称为 扩散 / 汇集，可能会使得二级索引查问数据比拟耗时。

* 基于关键词的分区

咱们也能够构建一个笼罩所有分区数据的 全局索引，比方咱们将 a 到 r 结尾的色彩的二级索引保留在分区 0 中，将 s 到 z 的保留在分区 1 中，如下图所示：

咱们将这种分区办法称为 关键词分区，依据关键词自身分区对于范畴扫描十分有用，比如说我当初想获取 a 到 r 结尾的色彩的所有汽车数据；而对关键词的哈希分区又可能提供分区负载平衡的能力。

基于关键词分区的全局索引优于文档分区索引的中央在于它的读取更加高效，并不需要将申请打到所有分区上，只须要将申请发送到含有对应关键词的分区即可，而它的毛病在于对单个分区文档的写入可能会产生多个分区的索引的数据变更，须要协调跨分区的分布式事务。

笼罩索引能够简略地了解成 查问只须要拜访索引列而无需拜访其余数据列。

优良的索引设计不单单只思考 WHERE 条件，也会依据想要查问的列去综合剖析。如果只须要索引列的话，那么笼罩索引是十分有用的工具，它能防止回表操作，这样 MySQL 就会极大地缩小数据访问量，而且索引占用的空间很小，将这些数据缓存在内存中的压力远小于缓存所有相干数据行。

如果业务无需查问其余列，那么咱们最好把业务须要的列放在查问列表中，以实现笼罩索引，而不是简略地以 * 来代替；在某些状况下，能够依据想要查问的列，对所应用的索引进行扩大，即减少想要查问的列达到笼罩索引的目标。

当执行一个笼罩索引的查问时，在 EXPLAIN 的 Extra 列能够看到 Using index 的信息。

它是 InnoDB 的一个个性，当 InnoDB 发现某些索引值被十分频繁的拜访时，它会在原有的 B+ Tree 索引之上，再在内存中构建一个哈希索引，以此来放慢对应数据的拜访。这个过程是自动化的，咱们无奈进行干涉，不过能够通过参数配置将其敞开。

《数据密集型利用零碎设计》：第三章、第六章
《高性能 MySQL 第四版》：第七章
《MySQL 是怎么运行的》：第四、五、六、七章
14.6.2.4 InnoDB Full-Text Indexes

作者：京东物流王奕龙

起源：京东云开发者社区自猿其说 Tech 转载请注明起源

关于mysql:高性能MySQL实战二索引-京东物流技术团队

1. 依据搜寻条件创立索引

索引的类型尽量小

冗余和反复索引

为反复率低的列创立索引

全值匹配和按值范畴匹配的工夫列

全文索引

前缀索引

2. 对于索引必须晓得的事儿

聚簇索引

二级索引

* 基于文档的分区

* 基于关键词的分区

笼罩索引

自适应哈希索引

伟人的肩膀