我们经常看到PRIMARY KEY (`id`) USING BTREE,
是为了建立索引
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `sku_id` (`id`) USING BTREE,
KEY `id_order` (`order_id`) USING BTREE,
KEY `tb_sku_purchase_id_IDX` (`purchase_id`) USING BTREE,
KEY `tb_sku_stream_IDX` (`stream`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
MySQL中有四种Key: Primary Key, Unique Key, Key 和 Foreign Key。
除了Foreign Key最好理解外,其他的都要区分一下。
剩下的三种都要在原表上建立索引。
Primary Key和Unique Key之间的区别晚上说的最多。Primary Key的提出
就是为了唯一标示表中的字段,就像我们的身份证号一样。此外,所有字段都
必须是not null的Unique Key则是为了保证表中有些字段是唯一的。比如有
些单位领导叫“张三”,所以下面招人的时候是决不可招一个有同样名字的。至于
Key吗,网上说的比较少。其实某个字段标记为Key,是不能保证这个字段的值
在表中是唯一出现的。它的目的就是建立索引。
MySQL的BTREE索引和HASH索引
为什么要用索引?
- 使用索引后减少了存储引擎需要扫描的数据量,加快查询速度
- 索引可以把随机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不适用于区分度小的列上,如性别字段