Mysql刨根问底之三（高性能索引）

B+Tree数据结构

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。
而每一个页的存储空间是有限的，如果data数据较大是会导致每个节点（即一页）能存储的key的数量很小，当储存的数据量很大时，同样会导致B-tree的深度较大，增大查询时的磁盘IO次数，进而影响查询效率。
在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

• 非叶子节点只存储键值信息
• 所有叶子节点之间都有一个链指针
• 数据记录都存放在叶子节点中
• 于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
  
  
  

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1-3次磁盘I/O操作。
参考：https://blog.csdn.net/u013235478/article/details/50625677

自适应哈希索引

InnoDB存储引擎有一个被称为自适应哈希索引的特
性。当InnoDB发现某些索引值被非常频繁地被访问时，它会在原有的
B-tree索引之上，在内存中再构建一个哈希索引。这就让B-tree索引
也具备了一些哈希索引的优势，例如，可以实现非常快速的哈希查
找。这个过程是完全自动化的，用户无法进行控制或者配置。

索引匹配规则

• 全值匹配

  • 全值匹配指的是和索引中的所有列进行匹配。
  • explain select * from staffs where name = ‘July’ and age = ‘23’

• 匹配最左前缀

  • 匹配前面的几列，顺序无所谓但是必须包含。
  • explain select * from staffs where name = ‘July’ and age = ‘23’;
    explain select * from staffs where name = ‘July’;

• 也可以只匹配某一列的值的开头部分。例如，前面提到的索引可
  用于查找所有姓以J开头的人。这里也只使用了索引的第一列。如果后边再加个%就匹配不到了。explain select * from staffs where name like ‘J%’;

• *匹配范围值：可以查找某一个范围的数据
  explain select * from staffs where name > ‘Mary’;

• 精确匹配某一列并范围匹配另外一列：可以查询第一列的全部和第二列的部分。

• 只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引，这里的using index意思是使用到了索引覆盖。

索引用于order by 规则

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某列的范围查询，则其右边所有列都无法使用索
  引 优 化 查 找 。
  • 例 如 ， 有 查 询 WHERE last_name='Smith’AND
    first_name LIKE’J%'AND dob=‘1976-12-23’，这个查询只能使
    用索引的前两列，因为这里LIKE是一个范围条件（不过，MySQL
    可以把其余列用于其他目的）。如果范围查询列值的数量有
    限，那么可以通过使用多个等于条件来代替范围条件。

索引的优点

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表（mysql 执行复杂sql时候借助临时表）
• 索引可以将随机IO变成顺序IO

索引三星评价系统

索引将相关的记录放到一起则获得“一星”；（等值匹配的索引放最左边）
如果索引中的数据顺序和查找中的排列顺序一致则获得“二星”；
如果索引中的列包含了查询中需要的全部列则获得“三星”；

高性能索引策略

前缀索引

一般情况下，列前缀的选择性也是足够高的，足以满足查询性能。对
于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL并不支持对这些列的完整内容进行索引。

多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每列创建
独立的索引。
如果在EXPLAIN中看到有索引合并，那么就应该好好检查一下查询语句
的写法和表的结构，看是不是已经是最优的。

合适的索引顺序

将选择性最高的列放到索引最前列。索引选择性 = 索引基数/数据总数。

聚簇索引

叶子页包含了一条记录的全部数据，但是节点页只包含了索引列。

有些数据库服务器允许你选择用于聚簇的索引，但是MySQL内置的存储
引擎都不支持这个特性。InnoDB根据主键聚簇数据。这意味着图7-3中
所示的“索引列”就是主键列。
如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。
如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点:

• 你可以把相互关联的数据保存在一起。

• 数据访问更快。聚簇索引将索引和数据保存在同一个B-tree
  中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要
  快。

• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

• 聚簇数据最大限度地提高了I/O密集型应用的性能

• 插入速度严重依赖于插入顺序。

• 更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更
  新的行移动到新的位置。

• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动
  行的时候，可能面临页分裂（page split）的问题。当行的主
  键值要求必须将这一行插入某个已满的页中时，存储引擎会将
  该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页
  分裂会导致表占用更多的磁盘空间。

• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由
  于页分裂导致数据存储不连续的时候。

• 二级索引（非聚簇索引）可能比想象中的要更大，因为二级索
  引的叶子节点包含了引用行的主键列。

• 二级索引访问需要两次索引查找，而不是一次。

优化建议

在InnoDB表中按主键顺序插入行。

如果你正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义
一个代理键（surrogate key）作为主键，这种主键的值和应用无关，
最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是
按顺序写入的，对于根据主键做联接操作的性能也会更好。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是
对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID作为聚簇
索引会很糟糕：它使得聚簇索引的插入变得完全随机，这就是最糟糕
的情况，数据本身没有任何聚集特性。

什么时候按主键顺序插入反而会更糟

对于高并发的工作负载，在InnoDB中按主键顺序插入可能会造成
明显的写入竞争。主键的上界会成为“热点”。因为所有的插入
都发生在这里，所以并发插入可能导致间隙锁竞争。一个热点
可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要
考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode
配置。

覆盖索引

在使用索引查询时候，如果索引字段包含查询字段，那么就可以使用覆盖索引，避免回表查询，能够极大提高性能。

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，
  那么MySQL就会极大地减少数据访问量。
• 因为索引是按照列值的顺序存储的（至少在单页内如此），所
  以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的
  I/O要少得多。
• 由于InnoDB的聚簇索引的特点，覆盖索引对InnoDB表特别有
  用。

索引扫描排序

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的
下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不
每扫描一条索引记录都回表查询一次对应的记录。这基本上都是随机
I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤
其是在I/O密集型的应用负载上。
MySQL可以使用同一个索引既满足排序，又用于查找行。
如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序。
需要满足索引的最左前缀的要求，否则，MySQL需要执行排序操作，而无法利用索引排序。

冗余和重复索引

重复索引是指在相同的列上按照相同顺序创建的相同类型的索引。应
该避免创建这样的重复索引，发现以后应该立即移除。
MySQL的唯一限制和主键限制都是通过索引实现的。
冗余索引的情况
如果创建了索引（A，B），再创建索引（A）就是冗余索引。

未使用的索引

找到未使用索引的最好办法就是使用系统数据库performance_schema和sys。
查询未被使用索引的sql之一:
select * from sys.schema_unused_indexes;

维护索引和表

找到并修复损坏的表，维护准确的索引统计信息，减少碎
片。在数据转移和备份中有些表可能被损坏了。需要注意是否有索引丢失了。
更新索引统计信息
如果存储引擎向优化器提供的扫描行数信息不准确，或者执行计划本
身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使
用索引统计信息来估算扫描行数。
show index from t5;
可以使用SHOW INDEX FROM命令来查看索引的基数（cardinality）。如果发现生成的索引基数不对，可以使用可以通过运行ANALYZE TABLE来重新生成统计信息，以解决这个问题。

减少索引和数据的碎片

B-tree索引可能会产生碎片化，这会降低查询的效率。碎片化的索引
可能会以很差或者无序的方式存储在磁盘上。根据设计，B-tree索引需要随机磁盘访问才能定位到叶子页，所以随机访问总是不可避免的。果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多；对于索引覆盖扫描，这一点会表现得更加明显。
有三种类型的数据碎片：
行碎片（Row fragmentation）
这种碎片指的是数据行被存储在多个地方的多个片段中。即使查
询只从索引中访问一行记录，行碎片也会导致性能下降。
行间碎片（Intra-row fragmentation）
行间碎片是指逻辑上顺序的页或者行，在磁盘上不是顺序存储
的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大
的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获
益。
剩余空间碎片（Free space fragmentation）
剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器
读取大量不需要的数据，从而造成浪费。
通过执行sql
OPTIMIZE TABLE tablename;
或者导出再导入的方式来重新整理数据。 如果显示不支持OPTIMIZE Table，可以通过可以通过一个不做任何操作（n o-o p）的ALTER TABLE
操作来重建表。只需将表的存储引擎修改为当前的引擎即可：

索引访问原则总结

• 单行访问是很慢的，特别是在机械硬盘中存储（SSD的随机I/O
  要快很多，不过这一点仍然成立）。如果服务器从存储中读取
  一个数据块只是为了获取其中一行，那么就浪费了很多工作。
  最好读取的块中能包含尽可能多的所需要的行。
• 按顺序访问范围数据是很快的，有两个原因。第一，顺序I/O
  不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对于机
  械硬盘）。第二，如果服务器能够按需顺序读取数据，那么就
  不再需要额外的排序操作，并且GROUP BY查询也无须再做排序
  和将行按组进行聚合计算了。
• 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有
  列，那么存储引擎就不需要再回表查找行。这避免了大量的单
  行访问，而上面的第一点已经写明单行访问是很慢的。