乐趣区

关于mysql:分享几个索引创建的小-Tips

jiezi

@[toc]
对于 MySQL 中的索引，松哥后面曾经和小伙伴们聊了不少了，不过在索引应用的时候，还是有一些须要留神的细节，如果疏忽了这些细节，可能会让索引的应用成果大打折扣。

1. 冗余索引

留神我这里应用了冗余索引，没有应用反复索引，因为我感觉在小伙伴们应用索引的过程中，创立反复索引的概率应该还是比拟小，同一个字段上创立多个截然不同的索引，应该很少有人会犯这种谬误。然而，会有一些容易被大家疏忽的冗余索引，咱们来捋一捋。

1.1 联结索引右边列

例如我创立了一个联结索引 (A,B,C)，依照咱们之前跟大家讲的最左匹配准则，当咱们应用 A、（A、B）或者（A、B、C）去查问数据的时候，都会用到这个联结索引，所以咱们就没有必要再去独自针对 A 字段创立一个索引，或者针对 A、B 字段创立一个联结索引。

1.2 索引中退出主键

假如我有一张表，该表有如下字段（ID、A、B、C），其中 ID 是主键，当初又针对 A 和 ID 两个字段创立了联结索引（A、ID）。

依据松哥后面的介绍，小伙伴们晓得，在二级索引中，叶子结点上存储的数据就是 ID，所以，这个联结索引中的 ID 字段显然是多余的。

大部分状况下咱们都不须要冗余索引，然而也有一些非凡状况可能让咱们不得不创立一些冗余索引，这个小伙伴们还是要具体问题具体分析。

另外须要留神一点，针对雷同的字段，如果索引类型不同，则不能算是反复索引，例如一个一般索引和一个全文索引，同一个字段上同时有这两个索引，不算反复索引。

2. 暗藏的索引排序

上篇文章松哥刚刚和大家聊了索引排序的问题。

联合上篇文章的内容，小伙伴们思考这样一个问题：假如我有一张表，表中蕴含如下字段（ID、A、B），其中 ID 是主键，当初我针对 A 字段建设一个索引，如果我有如下查问 SQL：

select ... from table where A=xxx order by ID

因为在 A 这个二级索引中就蕴含了 ID 字段，所以下面这个查问是能够应用到索引排序的。此时，如果因为其余需要，咱们将 A 这个索引扩大成联结索引（A、B）了，那么很显著，再执行下面的查问的时候就用不了索引排序了，只能 filesort 了。这样的问题小伙伴们在创立或者批改索引的时候很容易疏忽，所以肯定要认真。

3. 删除不应用的索引

有的索引可能是因为适度思考创立了，创立胜利之后就没用过，这样的索引也应该删除掉。

小伙伴们晓得，索引尽管能够进步查问速度，然而却会升高插入和批改速度。

在 MySQL 的元数据库 sys 中有一个名为 schema\_unused\_indexes 的视图，该视图中就保留了各种创立了然而未应用的索引：

4. 手动更新索引统计信息

当咱们想要查看一条 SQL 的执行打算时，这个执行打算中会展现进去这个 SQL 执行过程中大略会扫描多少行数据，如下：

这个预估的扫描行数十分重要，这是 MySQL 优化器在执行 SQL 的时候一个重要的参考指标，如果表没有这个统计信息，或者统计信息不精确，那么就有可能导致优化器做出谬误的决定。

当满足如下条件的时候，这个统计信息会主动生成或者更新：

  • 首次关上表。
  • 表大小发生变化。
  • 执行 SHOW TABLE STATUS
  • 执行 SHOW INDEX
  • MySQL 客户端开启主动补全性能
  • 关上 infomation\_schema 库中一些相干的表

这些行为都会触发统计信息的自动更新，如果表中数据量比拟大，放心以上行为升高表的性能，那么也能够批改 innodb\_stats\_on\_metadata 参数来敞开以上行为。

当然，咱们也能够手动执行 analyze table 命令来更新索引的统计信息。

5. 适时优化表

InnoDB 中的索引是一个 B+Tree，这个咱们在之前的文章中就和小伙伴们聊过了。B+Tree 通过一个多路均衡查找树将数据组织在一起，然而这个树中的各个结点在存储的时候在物理散布上却并不一定间断，如果是间断的，则在数据操作的时候就会快很多，如果不需间断，数据操作性能必然会有降落，一般来说，存在这样几种不同的碎片模式：

  1. 行碎片：数据行散布在不同的中央，读取数据行的时候波及到屡次随机 IO。
  2. 行间碎片：逻辑上应该是间断的行或者数据页，在磁盘上存储时并不间断。本来全表扫描的时候是程序 IO，当初变成了随机 IO。
  3. 残余空间碎片：小伙伴们晓得，InnoDB 操作数据表最根本单位是页，一页是 16KB，也就是 InnoDB 从磁盘上读、往磁盘上写，最低单位都是 16KB，有时候这 16KB 中，无效数据很少，其余中央都是残余空间，就会让 InnoDB 在读写数据的时候造成很大节约。

对于以上状况，咱们能够通过执行 optimize table 来重新整理数据，如果存储引擎不反对 optimize table 命令，那么咱们也能够通过执行 alter table <table> engine=xxx 命令来实现数据的重整（命令中的 xxx 就是表本来的引擎）。

当然，optimize table 命令在执行的过程中还有一些细节问题，这个松哥前面再整文章和小伙伴们分享。

退出移动版