Java教程:MySQL如何设计索引更高效?(二)

开课吧开课吧锤锤2021-03-05 11:00

    Java编程语言是一种简单、面向对象、分布式、解释型、健壮安全、与系统无关、可移植、高性能、多线程和动态的语言。如今Java已经广泛应用于各个领域的编程开发。

Java

    索引高度

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

    $hexdump-s81984-n10/usr/local/var/mysql/test/users.ibd

    001404000020000000000000fa3

    001404a

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

    数据扫描方式

    全表扫描

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

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

    索引扫描

    通常来讲索引比表小,扫描的数据量小,消耗的IO少,执行速度块,几乎没有锁等,能够提高MySQL的并发。

    对于OLTP系统,希望所有的SQL都能命中合适的索引总是美好的。

    主要区别就是扫描数据量大小以及IO的操作,全表扫描是顺序IO,索引扫描是随机IO,MySQL对此做了优化,增加了changebuffer特性来提高IO性能。

    索引优化案例

    分页查询优化

    业务要根据时间范围查询交易记录,接口原始的SQL如下:

    select*fromtrade_infowherestatus=0andcreate_time>='2020-10-0100:00:00'andcreate_time<

='2020-10-0723:59:59'orderbyiddesclimit102120,20;

    表trade_info上有索引idx_status_create_time(status,create_time),通过上面分析知道,等价于索引**(status,create_time,id)**,对于典型的分页limitm,n来说,越往后翻页越慢,也就是m越大会越慢,因为要定位m位置需要扫描的数据越来越多,导致IO开销比较大,这里可以利用辅助索引的覆盖扫描来进行优化,先获取id,这一步就是索引覆盖扫描,不需要回表,然后通过id跟原表trade_info进行关联,改写后的SQL如下:

    select*fromtrade_infoa,

    (selectidfromtrade_infowherestatus=0andcreate_time>='2020-10-0100:00:00'andcreate_time

<='2020-10-0723:59:59'orderbyiddesclimit102120,20)asb//这一步走的是索引覆盖扫描,不需要回表

    wherea.id=b.id;

    很多同学只知道这样写效率高,但是未必知道为什么要这样改写,理解索引特性对编写高质量的SQL尤为重要。

    分而治之总是不错的

    营销系统有一批过期的优惠卷要失效,核心SQL如下:

    --需要更新的数据量500w

    updatecouponssetstatus=1wherestatus=0andcreate_time>='2020-10-0100:00:00'

andcreate_time<='2020-10-0723:59:59';

    在Oracle里更新500w数据是很快,因为可以利用多个cpucore去执行,但是MySQL就需要注意了,一个SQL只能使用一个cpucore去处理,如果SQL很复杂或执行很慢,就会阻塞后面的SQL请求,造成活动连接数暴增,MySQLCPU100%,相应的接口Timeout,同时对于主从复制架构,而且做了业务读写分离,更新500w数据需要5分钟,Master上执行了5分钟,binlog传到了slave也需要执行5分钟,那就是Slave延迟5分钟,在这期间会造成业务脏数据,比如重复下单等。

    优化思路:先获取where条件中的最小id和最大id,然后分批次去更新,每个批次1000条,这样既能快速完成更新,又能保证主从复制不会出现延迟。

    优化如下:

    先获取要更新的数据范围内的最小id和最大id(表没有物理delete,所以id是连续的)

    mysql>explainselectmin(id)min_id,max(id)max_idfromcouponswherestatus=0andcreate_time>

='2020-10-0100:00:00'andcreate_time<='2020-10-0723: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|

Usingwhere;Usingindex|

    ​Extra=Usingwhere;Usingindex使用了索引idx_status_create_time,同时需要的数据都在索引中能找到,所以不需要回表查询数据。

    以每次1000条commit一次进行循环update,主要代码如下:

    current_id=min_id;

    forcurrent_id<max_iddo

    updatecouponssetstatus=1whereid>=current_idandid<=current_id+1000;//通过主键id更新1000条很快

    commit;

    current_id+=1000;

    done

    这两个案例告诉我们,要充分利用辅助索引包含主键id的特性,先通过索引获取主键id走覆盖索引扫描,不需要回表,然后再通过id去关联操作是高效的,同时根据MySQL的特性使用分而治之的思想既能高效完成操作,又能避免主从复制延迟产生的业务数据混乱。

    以上内容由开课吧老师敖丙提供,更多Java教程尽在开课吧广场Java教程频道。

有用
分享