mysql索引详解（B+树，聚簇/非聚簇，PRIMARY/NORMAL/UNIQUE/FULLTEXT区别）

mysql索引结构分为哈希和b+树

（1）HASH
　　　　用于对等比较，如"=“和” <=>"，查询单条快，范围查询慢
（2）BTREE
　　　　b+树，层数越多，数据量指数级增长（mysql,innodb默认）用在像 "=，>，>=，<，<=、BETWEEN、Like"等操作符查询效率较高

当然Mysql默认就是BTREE方式。

b+树是基础，看其他博客学习：
漫画：什么是B+树？

如果是一般二叉树，遍历查到想要的数据时间长度是树的深度，而b+树又扁又长，遍历深度最大不会超过4层

关于b+树为什么会按照层数指数级增长，这也解释了为什么该数据结构更适合用于百万级数据库的查找

总结一下，其实通过查字典的方式可以更容易理解b树的思想：mysql中b+树中，所有节点都是以页为单位存储，一个页存储多行数据，最底层的叶节点就是存在表中的行数据，往上的非叶节点或内节点的其实都是页目录，层层往上，就是小目录被大目录嵌套的形式。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替或隐式定义一个主键来作为聚簇索引(mysql内部会维护一个隐藏id去作为主键)
聚集索引(clustered index)在查询方面,速度往往会更占优势。一个表中只能有一个，其他都是非聚簇索引

自增主键id作为聚簇会比较好，因为db的物理存储会按照索引排序，索引的键值逻辑顺序决定了表数据行的物理存储顺序。

非聚集索引(包括普通索引，唯一索引，全文索引等，下面会讲) 可以根据条件去自定规则，使用单独的b+树，只存该索引字段的数据。每次查询到该数据后，拿到其id就会去做回表操作，才能拿到完整的数据。一个表可以存在多个

每创建一个非聚簇就会多创建一个b+树，但在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，增删改都很慢，需谨慎使用

多个字段联合组成索引，如果有两个字段做联合索引，先按照字段1排序，再按照字段2排序。其本质也是非聚簇索引

create index `member_worker_idx` on `follow_worker_member_rel`(member_id,worker_id)

一般比较推荐使用联合索引，原因如下

MYSQL索引有四种PRIMARY、NORMAL、UNIQUE、FULLTEXT。navicat可以修改索引类型如下。常用的就是前三种

加速查找+约束（唯一且不为空）
一张表只能有一个主键索引（主键索引通常在建表的时候就指定，一般设置自增id作为主键）

CREATE TABLE `follow_worker_member_rel` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`worker_id` int(11) NOT NULL,
	`member_id` int(11) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

作用很简单，就是加速查找（可不唯一可不为空）

增删ddl如下

CREATE TABLE `follow_worker_member_rel` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`worker_id` int(11) NOT NULL,
	`member_id` int(11) NOT NULL,
	INDEX `idx_memberid`(`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `follow_worker_member_rel` ADD INDEX `idx_memberid` (`member_id`);

DROP INDEX `idx_memberid` ON `follow_worker_member_rel`;

加速查找+约束 （唯一且可以为空）

和普通索引的区别就是index改成了unique，删除sql一样

CREATE TABLE `follow_worker_member_rel` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`worker_id` int(11) NOT NULL,
	`member_id` int(11) NOT NULL,
	UNIQUE `idx_memberid`(`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `follow_worker_member_rel` ADD UNIQUE `idx_memberid` (`member_id`);

DROP INDEX `idx_memberid` ON `follow_worker_member_rel`;

关于唯一索引是否可以存null，官方文档也有给出定义

用于搜索很长一篇文章的时候，效果最好。

参考：
Mysql索引PRIMARY、NORMAL、UNIQUE、FULLTEXT 区别和使用场合
别踩坑！使用MySQL唯一索引请注意