MySQL索引

二十Ⅳ桥明月夜

已于 2023-08-05 12:48:30 修改

阅读量51

点赞数

分类专栏： MySQL 文章标签： java 开发语言

于 2023-07-30 09:26:34 首次发布

本文链接：https://blog.csdn.net/weixin_42619556/article/details/132003495

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是B+树索引

聚簇索引

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。
InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。
innoDB中每个表有且仅有一个聚簇索引，默认使用主键，如果表没有定义主键，则第一个非空unique列作为聚簇索引，否则InnoDB会从建一个隐藏的row-id作为聚簇索引。

聚簇索引的优点

当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

插入速度严重依赖于插入顺序。
更新主键的代价很高，因为将会导致被更新的行移动。

非聚集索引（二级索引、辅助索引）

叶子节点仅存储主键，不存储行数据。

普通索引

最基本的索引类型，基于普通字段建立的索引，没有任何限制。

唯一索引

索引字段的值必须唯一，但允许有空值。

联合索引（复合索引）

在多个列上建立索引，联合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

防止索引失效，需要遵循最佳左前缀法则。

如图，查询时优先从联合索引的第一列进行匹配，然后在第一列的基础上再通过第二列进行匹配，之后再去匹配第三列。如果跳过第一列直接使用第二列或者第三列，则无法走索引。如果匹配了第一列和第三列，那么也只能使用部分索引。

回表

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引。

覆盖索引

name和age为联合索引

查询的列在二级索引中就可以找到，无需回表通过聚簇索引再次查找列数据时，这种情况就被称为索引覆盖（overing index）

explain select uid from user where name ='小明' and age = 21;

explain中extra为null，表示未覆盖索引，需要回表查询uid。

explain select name ,age from user where name ='小明' and age = 21;

explain中extra为Using index，表示发生了覆盖索引，无需回表。

查询的列在二级索引中不存在，则需要通过聚簇索引找到完整的行记录返回对应的列。即查询时使用到了2颗B+树，相比聚簇索引查询或者覆盖索引，多了一次io（一次辅助索引，一次聚集索引）。

索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

查询索引下推开关

show VARIABLES like '%optimizer_switch%';

user表建立了uk_name_age联合索引，name第一列，age第二列

手动关闭索引下推，执行下面的语句

set optimizer_switch='index_condition_pushdown=off';

explain select * from user where name like '小明%' and age = 21;

当使用非等值匹配时，name走了索引，但是age并没有走索引，extra Using where 表示优化器发生了回表，在聚簇索引中再次去匹配age。

手动开启索引下推，再次执行相同得查询

set optimizer_switch='index_condition_pushdown=on';

explain select * from user where name like '小明%' and age = 21;

此时 extra Using index condition，表示索引下推生效，未发生回表，仅一次io搞定。

哈希索引

哈希索引使用哈希算法，根据key计算的哈希值，映射到数组中的哈希槽，如果发生哈希冲突则使用链表连接。

在mysql中memory存储引擎支持哈希索引，在innodb中有一种自适应哈希功能（innodb的三大特性：buffer pool , adaptive_hash_index , double write buffer）

自适应即我们不需要自己处理，当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点，就会给其建立一个hash索引；
hash索引底层的数据结构是散列表（Hash表），其数据特点就是比较适合在内存中使用，自适应Hash索引存在于InnoDB架构中的缓存中（不存在于磁盘架构中），见下面的InnoDB架构图。
自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'，而对于其他查找类型，如范围查找，是不能使用的；

Adaptive Hash Index是针对B+树Search Path的优化，因此所有会涉及到Search Path的操作，均可使用此Hash索引进行优化.

根据索引键值(前缀)快速定位到叶子节点满足条件记录的Offset，减少了B+树Search Path的代价，将B+树从Root节点至Leaf节点的路径定位，优化为Hash Index的快速查询。

InnoDB的自适应Hash索引是默认开启的，可以通过配置下面的参数设置进行关闭

innodb_adaptive_hash_index = off

自适应Hash索引使用分片进行实现的，分片数可以使用配置参数设置

innodb_adaptive_hash_index_parts = 8

索引的优化建议

索引列的数据类型尽量小

列的数据类型越小，一个数据页存放的数据就越多，查询是就避免多余的磁盘io性能消耗。

列的离散度越高越适合建索引

离散度计算

select count(distinct name) / count(*) from user;

离散度=列不重复的个数/总行数，该值越接近1代表离散度越高，越接近0代表离散度越低。

包括前缀索引也是根据不同长度左前缀字符串对应的离散度来建立

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;