有情怀,有干货,微信搜寻【三太子敖丙】关注这个不一样的程序员。本文 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,也阐明了这两个索引时完全一致。
Index_name | n_fields | n_leaf_pages | size |
---|---|---|---|
idx_user_name | 2 | 1358 | 1572 |
idx_user_name_id | 2 | 1358 | 1572 |
索引回表
下面证实了辅助索引蕴含主键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.ibd0014040 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如下:
-- 须要更新的数据量500wupdate 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条,这样既能疾速实现更新,又能保障主从复制不会呈现提早。
优化如下:
- 先获取要更新的数据范畴内的最小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,同时须要的数据都在索引中能找到,所以不须要回表查问数据。
- 以每次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: 1mysql> 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这部分的最大长度即可。
-- 创立前缀索引,前缀长度为30mysql> 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;
比照下两个索引的统计数据,如下所示:
复合索引 | Type | Rows | 参加过滤索引列 | Chosen | Cause |
---|---|---|---|---|---|
idx_status_create_time | Index Range Scan | 98518 | status AND create_time | True | Cost低 |
idx_create_time_status | Index Range Scan | 98518 | create_time | False | Cost高 |
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好。
复合索引设计准则
- 将范畴查问的列放在复合索引的最初面,例如idx_status_create_time。
- 列过滤的频繁越高,选择性越好,应该作为复合索引的前导列,实用于等值查找,例如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。