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

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

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

Java

    MySQL索引设计

    熟悉了索引的特性之后,就可以在业务开发过程中设计高质量的索引,降低接口的响应时间。

    前缀索引

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

    可以通过设置参数innodb_large_prefix来开启或禁用索引前缀长度的限制,即是设置为OFF,索引虽然可以创建成功,也会有一个警告,主要是因为indexsize会很大,效率大量的IO的操作,即使MySQL优化器命中了该索引,效率也不会很高。

    --设置innodb_large_prefix=OFF禁用索引前缀限制,虽然可以创建成功,但是有警告。

    mysql>createindexidx_nicknameonusers(nickname);//`nickname`varchar(255)

    Records:0Duplicates:0Warnings:1

    mysql>showwarnings;

    +---------+------+---------------------------------------------------------+

    |Level|Code|Message|

    +---------+------+---------------------------------------------------------+

    |Warning|1071|Specifiedkeywastoolong;maxkeylengthis767bytes|

    业务发展初期,为了快速实现功能,对一些数据表字段的长度定义都比较宽松,比如用户表users的昵称nickname定义为varchar(128),而且有业务接口需要通过nickname查询,系统运行了一段时间之后,查询users表最大的nickname长度为30,这个时候就可以创建前缀索引来减小索引的长度提升性能。

    --`nickname`varchar(128)DEFAULTNULL定义的执行计划

    mysql>explainselect*fromuserswherenickname='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,即128x4+2+1=515Bytes。创建前缀索引,前缀长度也可以不是当前表的数据列最大值,应该是区分度最高的那部分长度,一般能达到90%以上即可,例如email字段存储都是类似这样的值xxxx@yyy.com,前缀索引的最大长度可以是xxxx这部分的最大长度即可。

    --创建前缀索引,前缀长度为30

    mysql>createindexidx_nickname_partonusers(nickname(30));

    --查看执行计划

    mysql>explainselect*fromuserswherenickname='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|Usingwhere|

    可以看到优化器选择了前缀索引,索引长度为123,即30x4+2+1=123Bytes,大小不到原来的四分之。

    前缀索引虽然可以减小索引的大小,但是不能消除排序。

    mysql>explainselectgender,count(*)fromuserswherenicknamelike'User100%'groupbynicknamelimit10;

    +----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----

    |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|Usingindexcondition|

    --可以看到Extra=Usingindexcondition表示使用了索引,但是需要回表查询数据,没有发生排序操作。

    mysql>explainselectgender,count(*)fromuserswherenicknamelike'User100%'groupbynicknamelimit10;

    +----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------

    |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|Usingwhere;Usingtemporary|

    --可以看到Extra=Usingwhere;Usingtemporaryn表示在使用了索引的情况下,需要回表去查询所需的数据,同时发生

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

有用
分享
全部评论快来秀出你的观点
登录 后可发表观点…
发表
暂无评论,快来抢沙发!
高并发编程训练营