表结构设计只是设计数据库最初的环节之一，还有最为重要的一个环节——索引设计，只有正确设计索引，业务才能达到上线的初步标准。
索引是什么
索引是关系型数据库中对某一列或多个列的值在插入时进行排序以提升查询效率的一种数据结构（显而易见，它的缺点就是影响插入或者更新的性能），通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
所以，索引是一门排序的艺术，有效的设计并创建索引，会提升数据库系统的整体性能。在目前的MySQL 8.0版本中，InnoDB存储引擎支持的索引有B+树索引、全文索引、R树索引。
B+树索引结构
B+树索引是数据库系统中最为常见的一种索引数据结构，几乎所有的关系型数据库都支持它。它是目前为止排序最有效率的数据结构。像二叉树、哈希索引、红黑树、SkipList，在海量数据基于磁盘存储效率方面都远不如B+树索引高效。
B+树索引的特定：基于磁盘的平衡树，树比较矮，通常为3~4层，能存放千万到亿的排序数据，树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用3、4次I/O。
又因为现在的固态硬盘每秒能执行至少10000次I/O，所以查询一条数据，哪怕全部在磁盘上，也只需要0.0030.004秒。另外，因为B+树矮，在做排序时，也只需要比较34次就能定位数据需要插入的位置，排序效率非常不错。
B+树索引由根节点(root node)、中间节点(non leaf node)、叶子节点(leaf node)组成，其中叶子节点存放所有排序后的数据。也存在一种比较特殊的情况，比如高度为1的B+树索引：
高度为1
第一个列就是 B+ 树索引排序的列，你可以理解它是表 User 中的列 id，类型为 8 字节的 BIGINT，所以列 userId 就是索引键（key），类似下表：
CREATE TABLE User (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
sex CHAR(6) NOT NULL,
register_date DATETIME NOT NULL,
...
);
所有B+树都是从高度为1的树开始，然后根据数据的插入，慢慢增加树的高度。高度为1的B+树索引中，存放的记录都是已经排好序的。若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。
随着插入B+树索引的记录变多，一个页无法存放这么多数据，所以会发生B+树的分裂，B+树的高度变为2，当B+树的高度大于等于2时，根节点和中间节点存放的是索引键对，由索引键和指针组成。索引键就是排序的列，指针是指向下一层的地址，在MySQL的InnoDB存储引擎中占用6个字节。下图显式了B+树高度为2时，B+树索引的样子：
高度为2
在上面的B+树索引中，若要查询索引键值为 5 的记录，则首先查找根节点，查到键值对（20，地址），这表示小于 20 的记录在地址指向的下一层叶子节点中。接着根据下一层地址就可以找到最左边的叶子节点，在叶子节点中根据二叉查找就能找到索引键值为 5 的记录。
高度为 3 的 B+ 树索引本质上与高度 2 的索引一致，如下图所示，不再赘述：
高度为3
高度与存储数量量
上表格显示了B+树的威力，即在50多亿的数据中，根据索引键查询记录，只需要4次I/O，大概仅需要0.004秒，如果这些查询的页已经被缓存在缓冲池中，查询性能会更快。
可以通过命令EXPLAIN查看是否使用索引：
mysql> EXPLAIN SELECT * FROM users WHERE id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
在输出的 EXPLIAN 结果中，可以看到列 key 显示 PRIMARY，这表示根据主键索引进行查询。若没有根据索引进行查询，如根据性别进行查询，则会显示类似如下内容：
mysql> EXPLAIN SELECT * FROM users WHERE sex='M';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
讲到这儿，应该了解了 B+ 树索引的组织形式，以及为什么在上亿的数据中可以通过B+树索引快速定位查询的记录。但 B+ 树的查询高效是要付出代价的，就是我们前面说的插入性能问题，接下去咱们就来讨论一下。
优化B+树索引的插入性能
B+树在插入时就要对数据进行排序，但排序的开销其实没有想象的那么大，因为排序是CPU操作（当前1s中CPU能处理上亿条指令）。
真正的开销在于B+树索引的维护，保证数据排序，这里存在两种不同数据类型的插入情况：
- 数据顺序（或逆序）插入：B+树索引的维护代价非常小，叶子节点都是从左往右插入，比较典型的是自增ID的插入、时间的插入（若在自增ID上创建索引，时间列上创建索引，则B+树插入通常是比较快的）
- 数据无序插入：B+树为了维护排序，需要对页进行分裂，旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会受到较大影响。比较典型的是用户昵称，每个用户注册时，昵称是随意取得，若在昵称列上创建索引，插入是无序的，索引维护需要的开销会比较大。
你不能要求所有插入的数据都是有序的，因为索引本身就是用于数据的排序，插入的数据都已经是排序的，那么就不需要B+树索引进行数据查询了。
所以对于B+树索引，在MySQL数据库设计中，进要求主键的索引设计为顺序，比如使用自增ID或用函数UUID_TO_BIN排序的UUID，而不是无序值做主键。
所以，再次强调： 在表结构设计时，主键的设计一定要尽可能地使用顺序值，这样才能保证在海量并发业务场景下的性能。
MySQL中B+树索引的设计和管理
在 MySQL 数据库中，可以通过查询表 mysql.innodb_index_stats 查看每个索引的大致情况：
mysql> SELECT
table_name,
index_name,stat_name,
stat_value,stat_description
FROM mysql.innodb_index_stats
WHERE table_name = 'users' and index_name = 'PRIMARY';
+------------+------------+--------------+------------+-----------------------------------+
| table_name | index_name | stat_name | stat_value | stat_description |
+------------+------------+--------------+------------+-----------------------------------+
| users | PRIMARY | n_diff_pfx01 | 2 | id |
| users | PRIMARY | n_leaf_pages | 1 | Number of leaf pages in the index |
| users | PRIMARY | size | 1 | Number of pages in the index |
+------------+------------+--------------+------------+-----------------------------------+
3 rows in set (0.00 sec)
从上面的结果中可以看到，表users中的主键索引有两条记录，节点只有1个，B+树索引高度为1
另外，网上一些所谓的MySQL”军规“中写道”一张表的索引不能超过5个“。根本没有这样的说法，完全是无稽之谈。如果业务的却需要很多不同维度进行查询，就应该创建对应多的索引。真正在业务上遇到的问题是：由于业务开发同学对数据库不熟悉，创建N多索引，但实际上这些索引从创建之初到现在根本就没有使用过，因为优化器并不会选择这些低效的索引，这些无效索引占用了空间，又影响了插入的性能。
那如何知道哪些索引未被使用过呢？在 MySQL 数据库中，可以通过查询表sys.schema_unused_indexes，查看有哪些索引一直未被使用过，可以被废弃(可以通过WHERE条件过滤，只查看所需要的某个库中未使用的索引)：
mysql> SELECT * FROM sys.schema_unused_indexes;
+--------------------+--------------------------+----------------------------------+
| object_schema | object_name | index_name |
+--------------------+--------------------------+----------------------------------+
| performance_schema | data_lock_waits | BLOCKING_THREAD_ID |
| performance_schema | data_lock_waits | REQUESTING_THREAD_ID |
| performance_schema | data_lock_waits | BLOCKING_ENGINE_TRANSACTION_ID |
| performance_schema | data_lock_waits | REQUESTING_ENGINE_TRANSACTION_ID |
| performance_schema | data_lock_waits | BLOCKING_ENGINE_LOCK_ID |
| performance_schema | data_lock_waits | REQUESTING_ENGINE_LOCK_ID |
| performance_schema | data_locks | OBJECT_SCHEMA |
| performance_schema | data_locks | THREAD_ID |
| performance_schema | data_locks | ENGINE_TRANSACTION_ID |
| performance_schema | file_summary_by_instance | EVENT_NAME |
| performance_schema | file_summary_by_instance | FILE_NAME |
| performance_schema | metadata_locks | OWNER_THREAD_ID |
| performance_schema | metadata_locks | OBJECT_TYPE |
| performance_schema | threads | RESOURCE_GROUP |
| performance_schema | threads | PROCESSLIST_HOST |
| performance_schema | threads | PROCESSLIST_ACCOUNT |
| performance_schema | threads | NAME |
| performance_schema | threads | THREAD_OS_ID |
| performance_schema | threads | PROCESSLIST_ID |
+--------------------+--------------------------+----------------------------------+
19 rows in set, 1 warning (0.02 sec)
如果数据库运行时间比较长，而且索引的创建时间也比较久，索引还出现在上述结果中，DBA就可以考虑删除这些没有用的索引。
MySQL8.0版本中还推出了索引不可见功能。在删除废弃索引前，用户可以将索引设置为对优化器不可见，然后观察业务是否有影响。如果没有影响就可以更安心的删除这些索引：
ALTER TABLE t1 ALTER INDEX idx_name INVISIBLE/VISIBLE;
总结
- 索引是加快查询的一种数据结构，其原理是插入时对数据进行排序，缺点是会影响数据插入和更新的性能
- MySQL当前支持B+树索引、全文索引、R树索引，常用为B+树索引
- B+树索引的高度一般为3~4层，高度为4的B+树索引能存放约50亿左右的数据
- B+树的高度不高，查询效率极高，50亿的数据也只需要操作4次I/O
- MySQL单表的索引没有个数限制，业务查询有具体需要，那么就创建索引
- 可以通过表sys.schema_unused_indexes和索引不可见特性删除无用的索引
- 全文索引用于多个维度的查询，类似我们搜索引擎的查询，输入多个条件，然后输出结果
- R树索引用于地理空间的查询
网友评论