关于面试:阿里面试官MySQL如何设计索引更高效

有情怀，有干货，微信搜寻【三太子敖丙】关注这个不一样的程序员。

本文 GitHub https://github.com/JavaFamily 已收录，有一线大厂面试残缺考点、材料以及我的系列文章。

前言

数据库系列更新到当初我想大家对所有的概念都已有个大略意识了，这周我在看评论的时候我发现有个网友的发问我觉得很有意思：帅丙如何设计一个索引？你们都是怎么设计索引的？怎么设计更高效？

我一想索引我写过很多了呀，没道理读者还不会啊，然而我一回头看完，那的确，我就写了索引的概念，优劣势，没提到怎么设计，那这篇文章又这样应运而生了。

本文还是会有很多之前写过的反复概念，然而也是为了大家能更好的了解 MySQL 中几种索引设计的原理。

注释

咱们晓得，索引是一个基于链表实现的树状 Tree 构造，可能疾速的检索数据，目前简直所 RDBMS 数据库都实现了索引个性，比方 MySQL 的 B +Tree 索引，MongoDB 的 BTree 索引等。

在业务开发过程中，索引设计高效与否决定了接口对应 SQL 的执行效率，高效的索引能够升高接口的 Response Time，同时还能够降低成本，咱们要事实的指标是：索引设计 -> 升高接口响应工夫 -> 升高服务器配置 -> 降低成本，最终要落实到老本上来，因为老板最关怀的是老本。

明天就跟大家聊聊 MySQL 中的索引以及如何设计索引，应用索引能力提升高接口的 RT，进步用户体检。

MySQL 中的索引

MySQL 中的 InnoDB 引擎应用 B +Tree 构造来存储索引，能够尽量减少数据查问时磁盘 IO 次数，同时树的高度间接影响了查问的性能，个别树的高度维持在 3~4 层。

B+Tree 由三局部组成：根 root、枝 branch 以及 Leaf 叶子，其中 root 和 branch 不存储数据，只存储指针地址，数据全副存储在 Leaf Node，同时 Leaf Node 之间用双向链表链接，构造如下：

从下面能够看到，每个 Leaf Node 是三局部组成的，即前驱指针 p_prev，数据 data 以及后继指针 p_next，同时数据 data 是有序的，默认是升序 ASC，散布在 B +tree 左边的键值总是大于右边的，同时从 root 到每个 Leaf 的间隔是相等的，也就是拜访任何一个 Leaf Node 须要的 IO 是一样的，即索引树的高度 Level + 1 次 IO 操作。

咱们能够将 MySQL 中的索引能够看成一张小表，占用磁盘空间，创立索引的过程其实就是依照索引列排序的过程，先在 sort_buffer_size 进行排序，如果排序的数据量大，sort_buffer_size 容量不下，就须要通过临时文件来排序，最重要的是通过索引能够防止排序操作（distinct，group by，order by）。

汇集索引

MySQL 中的表是 IOT（Index Organization Table，索引组织表)，数据依照主键 id 顺序存储（逻辑上是间断，物理上不间断），而且主键 id 是汇集索引（clustered index），存储着整行数据，如果没有显示的指定主键，MySQL 会将所有的列组合起来结构一个 row_id 作为 primary key，例如表 users(id, user_id, user_name, phone, primary key(id))，id 是汇集索引，存储了 id, user_id, user_name, phone 整行的数据。

辅助索引

辅助索引也称为二级索引，索引中除了存储索引列外，还存储了主键 id，对于 user_name 的索引 idx_user_name(user_name)而言，其实等价于 idx_user_name(user_name, id)，MySQL 会主动在辅助索引的最初增加上主键 id，相熟 Oracle 数据库的都晓得，索引里除了索引列还存储了 row_id（代表数据的物理地位，由四局部组成：对象编号 + 数据文件号 + 数据块号 + 数据行号），咱们在创立辅助索引也能够显示增加主键 id。

-- 创立 user_name 列上的索引
mysql> create index idx_user_name on users(user_name);
-- 显示增加主键 id 创立索引
mysql> create index idx_user_name_id on users(user_name,id);
-- 比照两个索引的统计数据
mysql> select a.space as tbl_spaceid, a.table_id, a.name as table_name, row_format, space_type,  b.index_id , b.name as index_name, n_fields, page_no, b.type as index_type  from information_schema.INNODB_TABLES a left join information_schema.INNODB_INDEXES b  on a.table_id =b.table_id where a.name = 'test/users';
+-------------+----------+------------+------------+------------+----------+------------------+----------+------
| tbl_spaceid | table_id | table_name | row_format | space_type | index_id | index_name       | n_fields | page_no | index_type |
+-------------+----------+------------+------------+------------+----------+------------------+----------+------
|         518 |     1586 | test/users | Dynamic    | Single     |     1254 | PRIMARY          |        9 |       4 |          3 |
|         518 |     1586 | test/users | Dynamic    | Single     |     4003 | idx_user_name    |        2 |       5 |          0 |
|         518 |     1586 | test/users | Dynamic    | Single     |     4004 | idx_user_name_id |        2 |      45 |          0 |
mysql> select index_name, last_update, stat_name, stat_value, stat_description from mysql.innodb_index_stats where index_name in ('idx_user_name','idx_user_name_id');
+------------------+---------------------+--------------+------------+-----------------------------------+
| index_name       | last_update         | stat_name    | stat_value | stat_description                  |
+------------------+---------------------+--------------+------------+-----------------------------------+   
| idx_user_name    | 2021-01-02 17:14:48 | n_leaf_pages |       1358 | Number of leaf pages in the index |
| idx_user_name    | 2021-01-02 17:14:48 | size         |       1572 | Number of pages in the index      |
| idx_user_name_id | 2021-01-02 17:14:48 | n_leaf_pages |       1358 | Number of leaf pages in the index |
| idx_user_name_id | 2021-01-02 17:14:48 | size         |       1572 | Number of pages in the index      |

比照一下两个索引的后果，n_fields 示意索引中的列数，n_leaf_pages 示意索引中的叶子页数，size 示意索引中的总页数，通过数据比对就能够看到，辅助索引中的确蕴含了主键 id，也阐明了这两个索引时完全一致。

索引回表

下面证实了辅助索引蕴含主键 id，如果通过辅助索引列去过滤数据有可能须要回表，举个例子：业务须要通过用户名 user_name 去查问用户表 users 的信息，业务接口对应的 SQL：

select  user_id, user_name, phone from users where user_name = 'Laaa';

咱们晓得，对于索引 idx_user_name 而言，其实就是一个小表 idx_user_name(user_name, id)，如果只查问索引中的列，只须要扫描索引就能获取到所需数据，是不须要回表的，如下 SQL 语句：

SQL 1: select id, user_name from users where user_name = 'Laaa';

SQL 2: select id from users where user_name = 'Laaa';

mysql> explain select id, name from users where name = 'Laaa';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
|  1 | SIMPLE      | users | NULL       | ref  | idx_user_name | idx_user_name | 82      | const |    1 |   100.00 | Using index |
mysql> explain select id from users where name = 'Laaa';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
|  1 | SIMPLE      | users | NULL       | ref  | idx_user_name | idx_user_name | 82      | const |    1 |   100.00 | Using index |

SQL 1 和 SQL 2 的执行打算中的 Extra=Using index 示意应用笼罩索引扫描，不须要回表，再来看下面的业务 SQL：

select user_id, user_name, phone from users where user_name = 'Laaa';

能够看到 select 前面的 user_id，phone 列不在索引 idx_user_name 中，就须要通过主键 id 进行回表查找，MySQL 内局部如下两个阶段解决：

Section 1：select **id** from users where user_name = 'Laaa' //id = 100101

Section 2: select user_id, user_name, phone from users where id = 100101;

将 Section 2 的操作称为回表，即通过辅助索引中的主键 id 去原表中查找数据。

索引高度

MySQL 的索引时 B +tree 构造，即便表里有上亿条数据，索引的高度都不会很高，通常维持在 3 - 4 层左右，我来计算下索引 idx_name 的高度，从下面晓得索引信息：index_id = 4003，page_no = 5，它的偏移量 offset 就是 page_no x innodo_page_size + 64 = 81984，通过 hexdump 进行查看

$hexdump -s 81984 -n 10 /usr/local/var/mysql/test/users.ibd
0014040 00 02 00 00 00 00 00 00 0f a3                  
001404a

其中索引的 PAGE_LEVEL 为 00，即 idx_user_name 索引高度为 1，0f a3 代表索引编号，转换为十进制是 4003，正是 index_id。

数据扫描形式

全表扫描

从左到右顺次扫描整个 B +Tree 获取数据，扫描整个表数据，IO 开销大，速度慢，锁等重大，影响 MySQL 的并发。

对于 OLAP 的业务场景，须要扫描返回大量数据，这时候全表扫描的程序 IO 效率更高。

索引扫描

通常来讲索引比表小，扫描的数据量小，耗费的 IO 少，执行速度块，简直没有锁等，可能进步 MySQL 的并发。

对于 OLTP 零碎，心愿所有的 SQL 都能命中适合的索引总是美妙的。

次要区别就是扫描数据量大小以及 IO 的操作，全表扫描是程序 IO，索引扫描是随机 IO，MySQL 对此做了优化，减少了 change buffer 个性来进步 IO 性能。

索引优化案例

分页查问优化

业务要依据工夫范畴查问交易记录，接口原始的 SQL 如下：

select  * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;

表 trade_info 上有索引 idx_status_create_time(status,create_time)，通过下面剖析晓得，等价于索引（status,create_time,id)，对于典型的分页 limit m, n 来说，越往后翻页越慢，也就是 m 越大会越慢，因为要定位 m 地位须要扫描的数据越来越多，导致 IO 开销比拟大，这里能够利用辅助索引的笼罩扫描来进行优化，先获取 id，这一步就是索引笼罩扫描，不须要回表，而后通过 id 跟原表 trade_info 进行关联，改写后的 SQL 如下：

select * from trade_info a ,

(select  id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b   // 这一步走的是索引笼罩扫描，不须要回表
 where a.id = b.id;

很多同学只晓得这样写效率高，然而未必晓得为什么要这样改写，了解索引个性对编写高质量的 SQL 尤为重要。

分而治之总是不错的

营销零碎有一批过期的优惠卷要生效，外围 SQL 如下：

-- 须要更新的数据量 500w
update coupons set status = 1 where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';

在 Oracle 里更新 500w 数据是很快，因为能够利用多个 cpu core 去执行，然而 MySQL 就须要留神了，一个 SQL 只能应用一个 cpu core 去解决，如果 SQL 很简单或执行很慢，就会阻塞前面的 SQL 申请，造成流动连接数暴增，MySQL CPU 100%，相应的接口 Timeout，同时对于主从复制架构，而且做了业务读写拆散，更新 500w 数据须要 5 分钟，Master 上执行了 5 分钟，binlog 传到了 slave 也须要执行 5 分钟，那就是 Slave 提早 5 分钟，在这期间会造成业务脏数据，比方反复下单等。

优化思路：先获取 where 条件中的最小 id 和最大 id，而后分批次去更新，每个批次 1000 条，这样既能疾速实现更新，又能保障主从复制不会呈现提早。

优化如下：

1. 先获取要更新的数据范畴内的最小 id 和最大 id（表没有物理 delete，所以 id 是间断的）

mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; 
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
| id | select_type | table | partitions | type  | possible_keys          | key                    | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
|  1 | SIMPLE      | users | NULL       | range | idx_status_create_time | idx_status_create_time | 6       | NULL | 180300 |   100.00 | Using where; Using index |

​ Extra=Using where; Using index 应用了索引 idx_status_create_time，同时须要的数据都在索引中能找到，所以不须要回表查问数据。

1. 以每次 1000 条 commit 一次进行循环 update，次要代码如下：

current_id = min_id;
for  current_id < max_id do
  update coupons set status = 1 where id >=current_id and id <= current_id + 1000;  // 通过主键 id 更新 1000 条很快
commit;
current_id += 1000;
done

这两个案例通知咱们，要充分利用辅助索引蕴含主键 id 的个性，先通过索引获取主键 id 走笼罩索引扫描，不须要回表，而后再通过 id 去关联操作是高效的，同时依据 MySQL 的个性应用分而治之的思维既能高效实现操作，又能防止主从复制提早产生的业务数据凌乱。

MySQL 索引设计

相熟了索引的个性之后，就能够在业务开发过程中设计高质量的索引，升高接口的响应工夫。

前缀索引

对于应用 REDUNDANT 或者 COMPACT 格局的 InnoDB 表，索引键前缀长度限度为 767 字节。如果 TEXT 或 VARCHAR 列的列前缀索引超过 191 个字符，则可能会达到此限度，假设为 utf8mb4 字符集，每个字符最多 4 个字节。

能够通过设置参数 innodb_large_prefix 来开启或禁用索引前缀长度的限度，即是设置为 OFF，索引尽管能够创立胜利，也会有一个正告，次要是因为 index size 会很大，效率大量的 IO 的操作，即便 MySQL 优化器命中了该索引，效率也不会很高。

-- 设置 innodb_large_prefix=OFF 禁用索引前缀限度，尽管能够创立胜利，然而有正告。mysql> create index idx_nickname on users(nickname);    // `nickname` varchar(255)
Records: 0  Duplicates: 0  Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |

业务倒退初期，为了疾速实现性能，对一些数据表字段的长度定义都比拟宽松，比方用户表 users 的昵称 nickname 定义为 varchar(128)，而且有业务接口须要通过 nickname 查问，零碎运行了一段时间之后，查问 users 表最大的 nickname 长度为 30，这个时候就能够创立前缀索引来减小索引的长度晋升性能。

-- `nickname` varchar(128) DEFAULT NULL 定义的执行打算
mysql> explain select * from users where nickname = 'Laaa';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+--------
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+--------
|  1 | SIMPLE      | users | NULL       | ref  | idx_nickname  | idx_nickname | 515     | const |    1 |   100.00 | NULL  |

key_len=515，因为表和列都是 utf8mb4 字符集，每个字符占 4 个字节，变长数据类型 +2Bytes，容许 NULL 额定 +1Bytes，即 128 x 4 + 2 + 1 = 515Bytes。创立前缀索引，前缀长度也能够不是以后表的数据列最大值，应该是区分度最高的那局部长度，个别能达到 90% 以上即可，例如 email 字段存储都是相似这样的值 xxxx@yyy.com，前缀索引的最大长度能够是 xxxx 这部分的最大长度即可。

-- 创立前缀索引，前缀长度为 30
mysql> create index idx_nickname_part on users(nickname(30));
-- 查看执行打算
mysql> explain select * from users where nickname = 'Laaa';
+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-
| id | select_type | table | partitions | type | possible_keys                  | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-
|  1 | SIMPLE      | users | NULL       | ref  | idx_nickname_part,idx_nickname | idx_nickname_part | 123     | const |    1 |   100.00 | Using where |

能够看到优化器抉择了前缀索引，索引长度为 123，即 30 x 4 + 2 + 1 = 123 Bytes，大小不到原来的四分之。

前缀索引尽管能够减小索引的大小，然而不能打消排序。

mysql> explain select gender,count(*) from users where nickname like 'User100%' group by nickname limit 10;
+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----
| id | select_type | table | partitions | type  | possible_keys                  | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----
|  1 | SIMPLE      | users | NULL       | range | idx_nickname_part,idx_nickname | idx_nickname | 515     | NULL |  899 |   100.00 | Using index condition |
-- 能够看到 Extra= Using index condition 示意应用了索引，然而须要回表查问数据，没有产生排序操作。mysql> explain select gender,count(*) from users where nickname like  'User100%' group by nickname limit 10;
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------
| id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------
|  1 | SIMPLE      | users | NULL       | range | idx_nickname_part | idx_nickname_part | 123     | NULL |  899 |   100.00 | Using where; Using temporary |
-- 能够看到 Extra= Using where; Using temporaryn 示意在应用了索引的状况下，须要回表去查问所需的数据，同时产生了排序操作。

复合索引

在单列索引不能很好的过滤数据的时候，能够联合 where 条件中其余字段来创立复合索引，更好的去过滤数据，缩小 IO 的扫描次数，举个例子：业务须要依照时间段来查问交易记录，有如下的 SQL：

select  * from trade_info where status = 1 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';

开发同学依据以往复合索引的设计的教训：惟一值多选择性好的列作为复合索引的前导列 ，所以创立复合索 idx_create_time_status 是高效的，因为 create_time 是一秒一个值，惟一值很多，选择性很好，而 status 只有离散的 6 个值，所以认为这样创立是没问题的， 然而这个教训只适宜于等值条件过滤，不适宜有范畴条件过滤的状况 ，例如 idx_user_id_status(user_id，status) 这个是没问题的，然而对于蕴含有 create_time 范畴的复合索引来说，就不适应了，咱们来看下这两种不同索引程序的差别，即 idx_status_create_time 和 idx_create_time_status。

-- 别离创立两种不同的复合索引
mysql> create index idx_status_create_time on trade_info(status, create_time);
mysql> create index idx_create_time_status on trade_info(create_time,status);
-- 查看 SQL 的执行打算
mysql> explain select * from users where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';
+----+-------------+-------+------------+-------+-----------------------------------------------+---------------
| id | select_type | table | partitions | type  | possible_keys                                 | key                    | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------------------------------------+---------------
|  1 | SIMPLE      | trade_info | NULL       | range | idx_status_create_time,idx_create_time_status | idx_status_create_time | 6       | NULL | 98518 |   100.00 | Using index condition |

从执行打算能够看到，两种不同程序的复合索引都存在的状况，MySQL 优化器抉择的是 idx_status_create_time 索引，那为什么不抉择 idx_create_time_status，咱们通过 optimizer_trace 来跟踪优化器的抉择。

-- 开启 optimizer_trace 跟踪
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
-- 执行 SQL 语句
mysql> select * from trade_info where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';
-- 查看跟踪后果
mysql>SELECT trace FROM information_schema.OPTIMIZER_TRACE\G;

比照下两个索引的统计数据，如下所示：

MySQL 优化器是基于 Cost 的，COST 次要包含 IO_COST 和 CPU_COST，MySQL 的 CBO（Cost-Based Optimizer 基于老本的优化器）总是抉择 Cost 最小的作为最终的执行打算去执行，从下面的剖析，CBO 抉择的是复合索引 idx_status_create_time，因为该索引中的 status 和 create_time 都能参加了数据过滤，老本较低；而 idx_create_time_status 只有 create_time 参数数据过滤，status 被忽略了，其实 CBO 将其简化为单列索引 idx_create_time，选择性没有复合索引 idx_status_create_time 好。

复合索引设计准则

1. 将范畴查问的列放在复合索引的最初面，例如 idx_status_create_time。
2. 列过滤的频繁越高，选择性越好，应该作为复合索引的前导列，实用于等值查找，例如 idx_user_id_status。

这两个准则不是矛盾的，而是相辅相成的。

跳跃索引

个别状况下，如果表 users 有复合索引 idx_status_create_time，咱们都晓得，独自用 create_time 去查问，MySQL 优化器是不走索引，所以还须要再创立一个单列索引 idx_create_time。用过 Oracle 的同学都晓得，是能够走索引跳跃扫描（Index Skip Scan），在 MySQL 8.0 也实现 Oracle 相似的索引跳跃扫描，在优化器选项也能够看到 skip_scan=on。

| optimizer_switch             |use_invisible_indexes=off,skip_scan=on,hash_join=on |

适宜复合索引前导列惟一值少，后导列惟一值多的状况，如果前导列惟一值变多了，则 MySQL CBO 不会抉择索引跳跃扫描，取决于索引列的数据分表状况。

mysql> explain select id, user_id，status, phone from users where create_time >='2021-01-02 23:01:00' and create_time <= '2021-01-03 23:01:00';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----
|  1 | SIMPLE      | users | NULL       | range  | idx_status_create_time          | idx_status_create_time | NULL    | NULL | 15636 |    11.11 | Using where; Using index for skip scan|

也能够通过 optimizer_switch=’skip_scan=off’ 来敞开索引跳跃扫描个性。

总结

本位为大家介绍了 MySQL 中的索引，包含汇集索引和辅助索引，辅助索引蕴含了主键 id 用于回表操作，同时利用笼罩索引扫描能够更好的优化 SQL。

同时也介绍了如何更好做 MySQL 索引设计，包含前缀索引，复合索引的程序问题以及 MySQL 8.0 推出的索引跳跃扫描，咱们都晓得，索引能够放慢数据的检索，缩小 IO 开销，会占用磁盘空间，是一种用空间换工夫的优化伎俩，同时更新操作会导致索引频繁的合并决裂，影响索引性能，在理论的业务开发中，如何依据业务场景去设计适合的索引是十分重要的，明天就聊这么多，心愿对大家有所帮忙。

我是敖丙，你晓得的越多，你不晓得的越多，感激各位的三连，咱们下期见。

絮叨

敖丙把本人的面试文章整顿成了一本电子书，共 1630 页！

干货满满，字字精华。目录如下，还有我温习时总结的面试题以及简历模板，当初收费送给大家。

链接:https://pan.baidu.com/s/1ZQEKJBgtYle3v-1LimcSwg 明码:wjk6

我是敖丙，你晓得的越多，你不晓得的越多 ，感激各位人才的： 点赞 、 珍藏 和评论，咱们下期见！

文章继续更新，能够微信搜一搜「三太子敖丙 」第一工夫浏览，回复【 材料】有我筹备的一线大厂面试材料和简历模板，本文 GitHub https://github.com/JavaFamily 曾经收录，有大厂面试残缺考点，欢送 Star。