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

开课吧开课吧锤锤2021-03-05 10:55

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

Java

    正文

    我们知道,索引是一个基于链表实现的树状Tree结构,能够快速的检索数据,目前几乎所RDBMS数据库都实现了索引特性,比如MySQL的B+Tree索引,MongoDB的BTree索引等。

    在业务开发过程中,索引设计高效与否决定了接口对应SQL的执行效率,高效的索引可以降低接口的ResponseTime,同时还可以降低成本,我们要现实的目标是:索引设计->降低接口响应时间->降低服务器配置->降低成本,最终要落实到成本上来,因为老板最关心的是成本。

    今天就跟大家聊聊MySQL中的索引以及如何设计索引,使用索引才能提降低接口的RT,提高用户体检。

    MySQL中的索引

    MySQL中的InnoDB引擎使用B+Tree结构来存储索引,可以尽量减少数据查询时磁盘IO次数,同时树的高度直接影响了查询的性能,一般树的高度维持在3~4层。

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

Java

    从上面可以看到,每个LeafNode是三部分组成的,即前驱指针p_prev,数据data以及后继指针p_next,同时数据data是有序的,默认是升序ASC,分布在B+tree右边的键值总是大于左边的,同时从root到每个Leaf的距离是相等的,也就是访问任何一个LeafNode需要的IO是一样的,即索引树的高度Level+1次IO操作。

    我们可以将MySQL中的索引可以看成一张小表,占用磁盘空间,创建索引的过程其实就是按照索引列排序的过程,先在sort_buffer_size进行排序,如果排序的数据量大,sort_buffer_size容量不下,就需要通过临时文件来排序,最重要的是通过索引可以避免排序操作(distinct,groupby,orderby)。

    聚集索引

    MySQL中的表是IOT(IndexOrganizationTable,索引组织表),数据按照主键id顺序存储(逻辑上是连续,物理上不连续),而且主键id是聚集索引(clusteredindex),存储着整行数据,如果没有显示的指定主键,MySQL会将所有的列组合起来构造一个row_id作为primarykey,例如表users(id,user_id,user_name,phone,primarykey(id)),id是聚集索引,存储了id,user_id,user_name,phone整行的数据。

    辅助索引

Java

    辅助索引也称为二级索引,索引中除了存储索引列外,还存储了主键id,对于user_name的索引idx_user_name(user_name)而言,其实等价于idx_user_name(user_name,id),MySQL会自动在辅助索引的最后添加上主键id,熟悉Oracle数据库的都知道,索引里除了索引列还存储了row_id(代表数据的物理位置,由四部分组成:对象编号+数据文件号+数据块号+数据行号),我们在创建辅助索引也可以显示添加主键id。

    --创建user_name列上的索引

    mysql>createindexidx_user_nameonusers(user_name);

    --显示添加主键id创建索引

    mysql>createindexidx_user_name_idonusers(user_name,id);

    --对比两个索引的统计数据

    mysql>selecta.spaceastbl_spaceid,a.table_id,a.nameastable_name,row_format,space_type,b.index_id,

b.nameasindex_name,n_fields,page_no,b.typeasindex_typefrominformation_schema.

INNODB_TABLESaleftjoininformation_schema.INNODB_INDEXESbona.table_id=b.table_idwherea.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>selectindex_name,last_update,stat_name,stat_value,stat_descriptionfrommysql.

innodb_index_statswhereindex_namein('idx_user_name','idx_user_name_id');

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

    |index_name|last_update|stat_name|stat_value|stat_description|

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

    |idx_user_name|2021-01-0217:14:48|n_leaf_pages|1358|Numberofleafpagesintheindex|

    |idx_user_name|2021-01-0217:14:48|size|1572|Numberofpagesintheindex|

    |idx_user_name_id|2021-01-0217:14:48|n_leaf_pages|1358|Numberofleafpagesintheindex|

    |idx_user_name_id|2021-01-0217:14:48|size|1572|Numberofpagesintheindex|

    对比一下两个索引的结果,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:

    selectuser_id,user_name,phonefromuserswhereuser_name='Laaa';

    我们知道,对于索引idx_user_name而言,其实就是一个小表idx_user_name(user_name,id),如果只查询索引中的列,只需要扫描索引就能获取到所需数据,是不需要回表的,如下SQL语句:

    SQL1:selectid,user_namefromuserswhereuser_name='Laaa';

    SQL2:selectidfromuserswhereuser_name='Laaa';

    mysql>explainselectid,namefromuserswherename='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|Usingindex|

    mysql>explainselectidfromuserswherename='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|Usingindex|

    SQL1和SQL2的执行计划中的Extra=Usingindex表示使用覆盖索引扫描,不需要回表,再来看上面的业务SQL:

    selectuser_id,user_name,phonefromuserswhereuser_name='Laaa';

    可以看到select后面的user_id,phone列不在索引idx_user_name中,就需要通过主键id进行回表查找,MySQL内部分如下两个阶段处理:

    Section1:select**id**fromuserswhereuser_name='Laaa'//id=100101

    Section2:selectuser_id,user_name,phonefromuserswhereid=100101;

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

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

有用
分享