MySQL中索引的存储类型有两种:BTREE和HASH
MYISAM和InnoDB存储引擎只支持BTREE索引;MEMORY和HEAP存储引擎支持BTREE和HASH索引。
一、使用索引的原因
1.减少了存储引擎需要扫描的数据量,加快查询速度
2.可以把随机I/O变为顺序I/O
3.对所搜结果进行排序,避免使用磁盘临时表
CREATE TABLE `manager` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '管理员序号id', `mid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员账号', `mname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员姓名', `mpassword` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员登录密码', `status` int(1) NOT NULL DEFAULT 1 COMMENT '状态、等级', PRIMARY KEY (`mid`) USING BTREE, INDEX `id_mid`(`id`, `mid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
二、BTREE索引
1.特点:
BTREE索引以B+树结构存储数据,大大加快了数据的查询速度
BTREE索引在范围查找的SQL语句中更加合适(顺序存储)
2.使用场景
- 全值匹配的查询,如where mid = ‘1004’
- 联合索引最左前缀匹配原则,如联合索引key id_mid(id,mid) USING BTREE,只要条件中使用了联合索引的第一列,就会用到该索引。如果使用的是第二列mid,则该SQL不会使用到该联合索引(除覆盖索引外)
- 右模糊查询,如where mid like '10%'(%10和%10%均会索引失效)
- 范围值查询,如where create_date > '451235' (not in和<>无法使用索引)
- 普通的全值匹配、最左前缀匹配、范围查询、order by排序等都能用到Btree索引;负向查询比如不等于和not in、非最左前缀匹配查询不会用到Btree索引;
三、HASH索引
特点:
- Hash索引基于Hash表实现,当查询条件精确匹配Hash索引中所有列才会用到Hash索引(非顺序存储)
- 存储引擎会计算Hash索引中每一列的hash码,因此每次读取都会进行两次查询
- Hash索引无法用于排序,使用于等值匹配查询
参考资料:
https://www.cnblogs.com/rxbook/p/10648796.html
https://blog.csdn.net/fanrenxiang/article/details/81781283