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

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

点赞
有用
分享分享

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

Java

    复合索引

    在单列索引不能很好的过滤数据的时候,可以结合where条件中其他字段来创建复合索引,更好的去过滤数据,减少IO的扫描次数,举个例子:业务需要按照时间段来查询交易记录,有如下的SQL:

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

andcreate_time<='2020-10-0723: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>createindexidx_status_create_timeontrade_info(status,create_time);

    mysql>createindexidx_create_time_statusontrade_info(create_time,status);

    --查看SQL的执行计划

    mysql>explainselect*fromuserswherestatus=1andcreate_time>='2021-10-0100:00:00'andcreate

_time<='2021-10-0723: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|Usingindexcondition|

    从执行计划可以看到,两种不同顺序的复合索引都存在的情况,MySQL优化器选择的是idx_status_create_time索引,那为什么不选择idx_create_time_status,我们通过optimizer_trace来跟踪优化器的选择。

    --开启optimizer_trace跟踪

    mysql>setsessionoptimizer_trace="enabled=on",end_markers_in_json=on;

    --执行SQL语句

    mysql>select*fromtrade_infowherestatus=1andcreate_time>='2021-10-0100:00:00'andcreate_time<='2021-10-0723:59:59';

    --查看跟踪结果

    mysql>SELECTtraceFROMinformation_schema.OPTIMIZER_TRACE\G;

Java

    对比下两个索引的统计数据,如下所示:

 

复合索引 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-BasedOptimizer基于成本的优化器)总是选择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。

    这两个原则不是矛盾的,而是相辅相成的。

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

有用
分享