MySQL的BTREE索引和HASH索引

为什么要用索引？

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

CREATE TABLE `table_name` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `pdf_id` char(32) COLLATE utf8mb4_bin NOT NULL,
  `user_id` int(11) NOT NULL,
  `last_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_PROJECT_PAPER_USER` (`project_id`,`pdf_id`,`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=41836 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

B-TREE索引

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

B-TREE索引使用场景

• 全值匹配的查询SQL，如 where id= '11'
• 联合索引汇中匹配到最左前缀查询，如联合索引 KEY  `IDX_PROJECT_PAPER_USER` (`project_id`, `pdf_id`, `user_id`) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
• 匹配模糊查询的前匹配，如where progect_id like '11_%'
• 匹配范围值的SQL查询
• 覆盖索引的SQL查询，就是说select出来的字段都建立了索引

 

HASH索引

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