MySQL的BTREE索引和HASH索引

短命女 2023-02-25 10:29 17阅读 0赞

为什么要用索引？

*  使用索引后减少了存储引擎需要扫描的数据量，加快查询速度
 *  索引可以把随机I/O变为顺序I/O
 *  索引可以帮助我们对所搜结果进行排序以避免使用磁盘临时表

Mysql支持的索引类型：B-TREE索引与HASH索引，两者有不同的使用场景，下面来简单剖析下这两者的区别。

CREATE TABLE act_info(
    id BIGINT NOT NULL AUTO_INCREMENT,
    act_id VARCHAR(50) NOT NULL COMMENT "活动id",
    act_name VARCHAR(50) NOT NULL COMMENT "活动名称",
    act_date datetime NOT NULL,
    PRIMARY KEY(id),
    KEY idx_actid_name(act_id,act_name) USING BTREE
    ) ENGINE=INNODB DEFAULT CHARSET=UTF8 ROW_FORMAT=COMPACT COMMENT "活动记录表";

### B-TREE索引 ###

B-TREE索引的特点

*  B-TREEB-TREE以B+树结构存储数据，大大加快了数据的查询速度
 *  B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）

B-TREE索引使用场景

*  全值匹配的查询SQL，如 where act\_id= '1111\_act'
 *  联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx\_actid\_name(act\_id,act\_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act\_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
 *  匹配模糊查询的前匹配，如where act\_name like '11\_act%'
 *  匹配范围值的SQL查询，如where act\_date > '9865123547215'（not in和<>无法使用索引）
 *  覆盖索引的SQL查询，就是说select出来的字段都建立了索引

### HASH索引 ###

HASH的特点

*  Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引
 *  存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询
 *  Hash索引无法用于排序
 *  Hash不适用于区分度小的列上，如性别字段

###    总结 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>Btree索引</td> 
   <td>其一使用B+Tree实现，根据主键引用被索引的行；其二使用btree索引能加快查询速度，因为存储引擎不需要全表扫描来获取所需要的数据，而是从索引的根节点开始搜索；其三btree索引是顺序存储的，很适合范围查询<br> &nbsp;<br> 普通的全值匹配、最左前缀匹配、范围查询、order by排序等都能用到Btree索引；负向查询比如不等于和not in、非最左前缀匹配查询不会用到Btree索引；</td> 
  </tr> 
  <tr> 
   <td>Hash索引</td> 
   <td>使用hash表实现，只有精确匹配所有列的查询才有效；其二hash索引是非顺序存储的<br> &nbsp;<br> hash索引适用于等值匹配查询，order&nbsp;排序中无法使用</td> 
  </tr> 
 </tbody> 
</table>