聚集索引和辅助聚集索引-教会你正确的使用mysql索引

索引原理

当从数据库中读取数据时，由于磁盘的访问代价很高，一般的磁盘每秒做100次IO操作，2-3次的IO操作意味着查询时需要0.02~0.03秒。而数据库中的数据，都是基于B+树（B+树可以参考B+树-分分钟钟被安排地明明白白）的数据结构存储数据，存储数据的树层高一般都是2~3层，查询一般需要2~3IO操作。在mysql中，数据结构分为聚集索引（clustered index）和辅助聚集索引（secondary index）。索引的特性如下：

聚集索引

聚集索引作为数据表又是索引表，其特性如下：

在叶子节点，存储表中的数据行的整行记录数据（例如，一个叶子节点存放id为1，2，3的用户数据）。
数据按照主键的顺序存放，并且叶子点之间以双链表的形式进行链接。
非叶子点存放主键数据作为索引。

基于聚集索引的特性，特别适用于如下场景：

根据主键查询数据，因为行记录数据存储在叶子节点。
根据范围条件顺序查询数据，特性是包含数据最小端或者最大端，因为叶子节点是双链表的形式关联，可以快速地从链表头或聊表尾顺序的查询数据。

辅助聚集索引

作为索引使用，其特性与聚集索引类似，只是节点的存储的数据不一样。其区别如下：

叶子节点存放索引数据，和类似于id可以行数据的聚集索引键。
索引节点可以是组合索引，节点数据会根据索引列从左到右，从小到大的顺序排列。

在实际查询中，使用辅助聚集索引查询行数据，需要在非聚集索引中查询到对应的id（行数据的聚集索引键），通常会执行2~3IO操作；然后根据该索引键再到聚集索引中获取到对应的行数据，会再执行2~3IO操作，总共会执行4~6次IO操作。基于辅助聚集索引的特性，特别适用于通过组合索引就能覆盖查询列和查询条件的场景，因为辅助索引就能覆盖，避免了去聚集索引获取其他的行数据。

索引优化

在mysql的实际开发中，根据索引的特性，使用索引有如下优化点：

在数据查询时，避免字符串的左模糊或者全模糊查询。因为，索引是B+树，具有平衡二叉查询树的特性，如果查询关键字左边的值未确定，将无法使用索引。正确查询示例如下：

# name字段为索引字段

select * from table where name like 'ko*';

查询中，如果有order by的场景，应充分利用索引的有序性。如果是组合索引，order by最后的字段是组合索引的一部分，并且放在组合顺序的最后，避免出现file_sort的情况。示例如下：

# 组合索引为a_b_c,区别度最高的在左边
where a=? and b=? order by c;

查询中，利用索引覆盖进行查询，避免回表。用 explain 分析查询的结果， extra 列会出现using index。例如，有组合索引a_b_c，需要查询的字段就是列a，列b，列c的组合列数据，如果查询条件也正是组合索引列就更好。示例如下：

# 查询的列包含组合索引a_b_c的a，b，c三个列
select id,a,b,c from table;

# 查询的列包含组合索引a_b_c的a，b，c三个列，并且查询条件包含组合索引
select id,a,b,c from table wherr a=? and b=? and c=?;

建立组合索引的时候，把区分度最高的列放在最左边。例如，delivery_priority和create_time列需要建立组合索引，并且列delivery_priority数据的区分度高于列create_time的数据，在建立索引时，delivery_priority列在最左侧，示例如图：

查询时，利用延长关联或子优化查询。例如，根据用户条件查询订单的信息时，延迟关联订单。根据条件查询大分页数据时，通过子查询优化，示例如下：

# 用户表根据name查询到订单的id，然后，根据查询结果的订单id与order表关联查询订单的属性
select o.id,o.a,o.b,o.c from order o left join (select order_id from user where name=?) u on u.order_id = o.id;

# 使用子查询优化大分页查询
SELECT a.* FROM table 1 a, (select id from where a=? and b=? LIMIT 100000,20 ) b where a.id=b.id

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/13635.html

聚集索引和辅助聚集索引-教会你正确的使用mysql索引

索引原理

索引优化

相关推荐