加入收藏 | 设为首页 | 会员中心 | 我要投稿 PHP编程网 - 黄冈站长网 (http://www.0713zz.com/)- 数据应用、建站、人体识别、智能机器人、语音技术!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

MySQL中怎么使索引更高效

发布时间:2022-01-21 03:11:54 所属栏目:MySql教程 来源:互联网
导读:本篇文章给大家分享的是有关MySQL中怎么让索引更高效,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。 MySQL中的InnoDB引擎使用B+Tree结构来存储索引,可以尽量减少数据查询时磁盘IO次数
        本篇文章给大家分享的是有关MySQL中怎么让索引更高效,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。
  
       MySQL中的InnoDB引擎使用B+Tree结构来存储索引,可以尽量减少数据查询时磁盘IO次数,同时树的高度直接影响了查询的性能,一般树的高度维持在 3~4 层。
 
       B+Tree由三部分组成:根root、枝branch以及Leaf叶子,其中root和branch不存储数据,只存储指针地址,数据全部存储在Leaf Node,同时Leaf Node之间用双向链表链接,结构如下:
 
我们可以将MySQL中的索引可以看成一张小表,占用磁盘空间,创建索引的过程其实就是按照索引列排序的过程,先在sort_buffer_size进行排序,如果排序的数据量大,sort_buffer_size容量不下,就需要通过临时文件来排序,最重要的是通过索引可以避免排序操作(distinct,group by,order by)。
 MySQL中怎么使索引更高效
聚集索引
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      |
对比一下两个索引的结果,n_fields表示索引中的列数,n_leaf_pages表示索引中的叶子页数,size表示索引中的总页数,通过数据比对就可以看到,辅助索引中确实包含了主键id,也说明了这两个索引时完全一致。

(编辑:PHP编程网 - 黄冈站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读