您现在的位置是:主页 > 数据库技术 > 数据库技术

如何使MySQL的索引更高效

IDCBT2021-12-22服务器技术人已围观

简介这篇文章主要介绍如何使MySQL的索引更高效,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完! MySQL中的索引 MySQL中的InnoDB引擎使用B+Tree结构来存储索引,可以

这篇文章主要介绍如何使MySQL的索引更高效,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!

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      |

标签:

很赞哦! ()

本栏推荐