MySQL调优学习笔记(一)：索引排序

目录

索引是什么

B+树索引结构

优化 B+ 树索引的插入性能

总结

参考资料：姜承尧的MySQL实战宝典

索引是什么

索引是提升查询速度的一种数据结构。索引之所以能提升查询速度，是因为它在插入时对数据进行了排序。显而易见，它的缺点是影响插入的性能。

在目前的 MySQL 8.0 版本中，InnoDB 存储引擎支持的索引包含 B+ 树索引、全文索引、R 树索引。全文索引用于多个维度的查询，类似搜索引擎的查询，输入多个条件，然后输出结果；
R树索引用于地理空间查询；B+树索引是数据库系统中最为常见的一种索引数据结构。

B+树索引结构

B+树索引是目前为止排序最有效率的数据结构，几乎所有的关系型数据库都支持它。像二叉树、哈希索引、红黑树、SkipList，一般仅用于内存对象，在海量数据基于磁盘存储效率方面远不如 B+ 树索引高效。

B+树索引的特点： 是基于磁盘的平衡树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3~4 次 I/O。目前的固态硬盘每秒能执行至少 10000 次 I/O ，所以3~4 次 I/O速度很快。另外在做排序时，也只需要比较 3~4 次就能定位数据需要插入的位置，排序效率非常不错。

B+树索引由根节点、中间节点、叶子节点组成，根节点和中间节点存放的是索引键对，由（索引键、指针）组成，而叶子节点存放所有排序后的数据。索引是对记录进行排序， 若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。

所有 B+ 树都是从高度为 1的树开始，然后随着插入索引的记录变多，1个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，高度慢慢增加。

优化 B+ 树索引的插入性能

B+ 树在插入时就对要对数据进行排序，但排序的开销其实并没有那么大。真正的开销在于 B+ 树索引的维护，保证数据排序，这里存在两种不同数据类型的插入情况。

数据顺序（或逆序）插入： B+ 树索引的维护代价非常小，叶子节点都是从左往右进行插入。比较典型的是自增 ID 的插入、时间的插入。
数据无序插入： B+树索引为了维护排序，需要对页进行分裂、旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会受到较大影响。比较典型的是用户昵称的插入。

在实际应用场景下，不可能要求所有插入的数据都是有序的。在 MySQL 数据库设计中，对于 B+ 树索引，主键的索引设计要尽可能地使用顺序值，比如使用自增，或使用函数 UUID_TO_BIN 排序的 UUID。这样才能保证在海量并发业务场景下的性能。

在 MySQL 数据库中，可以通过查询表mysql.innodb_index_stats来查看每个索引的大致情况。

#查看orders表中主键索引的情况
select table_name,index_name,stat_name,stat_value,stat_description 
from mysql.innodb_index_stats
where table_name = 'orders' and index_name = 'PRIMARY';

由于业务开发同学对数据库不熟悉，可能会创建很多无效索引，优化器不会选择这些低效的索引，所以这些索引从创建开始就没被使用过。这些无效索引既占用了空间，又影响了插入的性能。

那怎么知道哪些 B+树索引未被使用过呢？在 MySQL 数据库中，可以通过查询表sys.schema_unused_indexes来查看有哪些索引一直未被使用过。

#查询未被使用过的索引
select * from sys.schema_unused_indexes
where object_schema != 'performance_schema';

MySQL 8.0 版本推出了索引不可见功能。在删除无效索引之前，可以将索引设置为对优化器不可见，然后观察业务是否受到影响。若不产生影响，即可较安心地删除这些索引。

#设置orders表中索引idx_name对优化器不可见
alter table orders
alter index idx_name invisible;

#设置orders表中索引idx_name对优化器可见
alter table orders
alter index idx_name visible;

#删除索引
drop index idx_name on orders;

总结

• 索引是加快查询的一种数据结构，其原理是插入时对数据排序，缺点是会影响插入的性能；
• MySQL 当前支持 B+树索引、全文索引、R 树索引；
• B+ 树索引的高度通常为 3~4 层，能存放千万到上亿的排序数据，由于 B+ 树比较矮，查询效率极高，千万到上亿的数据也只需要3~4 次 I/O；
• 在 MySQL 数据库设计中，对于 B+ 树索引，主键的索引设计要尽可能地使用顺序值；
• MySQL 单表的索引没有个数限制，根据业务查询需要创建即可；
• 可以通过表 sys.schema_unused_indexes 和索引不可见特性，删除无用的索引。