MYSQL索引的分类和创建

1、聚簇索引和非聚簇索引

tips：

小问题：主键为什么建议使用自增id?

2、普通索引（常规索引）(normal)

3、唯一索引（UNIQUE ）

唯一索引和主键的区别：

唯一约束和唯一索引的区别：

4、多个二级索引的组合使用！

5、复合索引（联合索引）！

6、全文索引（FULLTEXT）

7、hash索引

8、空间索引（SPATIAL）

前言：学习需静心，不可急躁求成！

innodb采用的是一种名为【b+树】的数据结构。

B-树有如下特点:

所有键值分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
在关键字全集内做一次查找，性能逼近二分查找；

【B+树】是【B-树】的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

所有关键字存储在叶子节点
为所有叶子结点增加了一个双向指针

1、聚簇索引和非聚簇索引

我们在上边的例子中，【主键和数据】共存的索引被称之为【聚簇索引】，其他的，比如我们使用【姓名列+主键】建立的索引，可以称为【非聚簇索引】，或者【辅助索引】，或者【二级索引】，同时聚簇索引只有在innodb引擎中才存在，而在myIsam中是不存在的，如下图：

InnoDB使用的是【聚簇索引】，他会将【主键】组织到一棵B+树中，而【行数据】就储存在叶子节点上，若使用where id = 14这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，且name列已建立【索引】，则需要两个步骤：

第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。
第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

tips：

聚簇索引【默认使用主键】，如果表中没有定义主键，InnoDB 会选择一个【唯一且非空】的列代替。如果没有这样的列，InnoDB 会隐式定义一个主键【类似oracle中的RowId】rowid来作为聚簇索引的列。
如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyIsam占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

小问题：主键为什么建议使用自增id?

主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

2、普通索引（常规索引）(normal)

例如创建user_name列的索引：

create index idx_user_name on user(user_name);

创建索引是一个很费时间的操作。

其他创建索引的方法，如下：

（1）创建email列的索引，索引可以截取length长度，只使用这一列的前几个字符

create index idx_email on user(email(5));

有的列【数据量比较大】，使用前几个字符就能【很快标识】出来一行数据，那我们就可以使用这种方式建立索引，比如我们的邮箱，邮箱很多后缀是相同的我们完全可以忽略。

（2）使用修改表的方式添加索引

alter table user add index idx_email (email);

（3）建表时时，同时创建索引

create table tbl_name(tid int,tname varchar(20),gender varchar(1),index [indexName] (fieldName(length))
)

3、唯一索引（UNIQUE ）

对列的要求：索引列的值不能重复

创建表的同时，创建索引：

create table tbl_name(tid int,tname varchar(20),gender varchar(1),unique index unique_index_tname (tname)
)

独立的sql语句创建索引，我们的邮箱，用户名就应该创建唯一索引，姓名就应该是普通索引：

create unique index idx_email on user(email);

通过alter语句添加索引：

ALTER table mytable ADD UNIQUE [ux_indexName] (username(length))

唯一索引和主键的区别：

唯一索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为非空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

唯一约束和唯一索引的区别：

唯一约束和唯一索引，都可以实现列数据的唯一，列值可以为null。
创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据唯一。
创建一个唯一索引，这个索引就是独立的索引，可以单独删除。
如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。

4、多个二级索引的组合使用！

mysql在执行查询语句的时候一般只会使用【一个索引】，除非是使【用or连接的两个索引列】会产生索引合并。

我们针对某电商平台的检索功能做了优化，添加了三个索引，三个字段分别为【品牌】、【价格】、【销量】这三个的索引结构如下:

（1）品牌的索引结构：

（2）价格的索引结构：

（3）销量的索引结构：

针对以上的索引我们进行如下的查询，分析检索过程：

我们要检索品牌为阿玛尼（Armani）的包包

第一步：通过【品牌索引】检索出所有阿玛尼的商品id，回表查询，得到结果。

结论：会使用一个索引。
我们要检索名称为阿玛尼（Armani），价格为26800，且销量在50以上的包包

查询的步骤如下：

第一步：通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

第二步：直接回表扫描，根据剩余条件检索结果。

结论：只会使用第一个索引。
我们要检索名称为阿玛尼（Armani）或名称为LV的包包

第一步：通过【品牌索引】检索出所有阿玛尼的商品id，得到结果。

结论：像这样的【type =‘Armani’ or type = ‘LV’】，他相当于一个in关键字，会使用一个索引。
我们要检索名称为阿玛尼（Armani）或价格大于8000的包包

第一步：通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

第二步：通过【价格索引】检索出价格在5万到7万之间的商品id，这是一个连接条件带有【or的查询】，所以需要和上一步的结果进行【并集】，得到结果。

结论：这个过程叫【索引合并】当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】。
我们要检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包

第一步：通过【品牌索引】检索出所有阿玛尼的商品id。

第二步：直接回表扫描，根据剩余条件检索结果。

结论：只会使用第一个索引。
我们要检索名称为阿玛尼（Armani）或价格大于8000，或产地（该列无索引）在北京的包包

第一步：优化器发现【产地列】无索引，同时连接的逻辑是【or】没有办法利用【索引】优化，只能全表扫描，索引失效。

结论：发生全表扫描，索引失效，条件中有没建立索引的列，同时关联条件是or。

5、复合索引（联合索引）！

当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。

比如：我们经常按照A列、B列、C列进行查询时，通常的做法是建立一个由三个列共同组成的【复合索引】而不是对每一个列建立【普通索引】。

创建联合索引的方式如下：

alert table test add idx_a1_a2_a3 table (a1,a2,a3)

-- 28.531s
create index idx_user_nick_name on ydl_user(user_name,nick_name,email(7));

我们要检索名称为阿玛尼（Armani），价格在1万到3万之间的包包

第一步：通过【品牌索引】检索出所有阿玛尼的叶子节点。

第二步：在【满足上一步条件的叶子节点中】查询价格在1万到3万之间的包包的列，查询出对应的id，回表查询列数据。

结论：会使用复合索引的两个部分。
我们要检索名称为阿玛尼（Armani）或价格大于8000的包包

第一步：优化器发现我们并没有一个【价格列】的单独的二级索引，此时要查询价格大于8000的包，必须进行全表扫描。

结论：但凡查询的条件中没有【复合索引的第一部分】，索引直接【失效】，全表扫描。
我们要检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包

第一步：通过【品牌索引】检索出所有阿玛尼的叶子节点。

第二步：在【满足上一步条件的叶子节点中】查询价格大于8000元的包包的叶子节点。

第三步：因为【产地列】无索引，但是是【and】的关系，我们只需要将上一步得到的结果回表查询，在这个很小的范围内，检索产地是不是北京即可。

结论：可以使用复合索引的两个部分。
我们要检索名称为阿玛尼（Armani）和LV之间，价格为在1万到3万的包包

第一步：通过【品牌索引】检索出所有阿玛尼和LV的所有叶子节点。

第二步：我们本想在第一步的结果中，快速定位价格的范围，但是发现一个问题，由于第一步不是等值查询，会导致后边的结果不连续，必须对【上一步的结果】全部遍历，才能拿到对应的结果。

结论：只会使用复合索引的第一个部分，这个就引出了【复合索引中特别重要的一个概念】-【最左前缀原则】。

重点：最左前缀原则：

（1）最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（2）= 和 in 可以乱序，比如a = 1 and b < 2 and c = 3 ，咱们建立的索引就可以是（a,c,b）或者（c,a,b）。

思考：为什么联合索引的性能会比索引的组合使用效率高。

6、全文索引（FULLTEXT）

做全文检索（不如百度的搜索功能）使用的索引，但是这种场景，我们有更好的替代品，如：ElacticSearch，所以实际使用不多，只当了解。

7、hash索引

hash索引是Memory存储引擎的默认方式，而且只有memory引擎支持hash索引，memory的数据是放在内存中的，一旦服务关闭，表中的数据就会丢失，我们可以使用如下的sql创建一张表:

CREATE TABLE `hash_user`  (`user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',`user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',......
) ENGINE = Memory CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

合理的使用memory引擎可以极大的提升性能，针对memory引擎的特点重启丢失），我们最好在其中存储一些公共的、常用的、不经常发生改变的数据，比如一些字典数据、配置数据等。同时，这些数据最好持久化在一些其他的地方，比如配置文件、其他的表，在程序启动的时候，主动的进行加载，我们可以使用如下sql，将一张表的数据加载到内存中：

insert into hash_user select * from ydl_user where user_id < 2000000;

在执行的过程种，可能有如下错误：

他告诉我，这个表使用的内存满了，放不下了，我们只需要调节下边两个参数，修改配置文件重启即可：

tmp_table_size = 4096M
max_heap_table_size = 4096M

基础工作完成，写几个sql语句尝试一下，我们发现真的一个字：快。

我们执行一下的sql

select * from hash_user where email = 'i.jnoyelrsg@rpnglcvh.museum'  -- 0.189s

创建一个hash索引

create index hash_idx_user_name using hash on hash_user(email);

再次查询

select * from hash_user where email = 'i.jnoyelrsg@rpnglcvh.museum'  -- 0.017s

也有不错的效果。

8、空间索引（SPATIAL）

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。这是在地理位置领域使用的一种索引，其他场景用的很少，所以不需要深入学习。