MySQL高级【索引分类】

1：索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

聚集索引和二级索引的具体结构如下：

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

具体过程如下:

①. 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

思考题： 以下两条SQL语句，那个执行效率高? 为什么?

A. select * from user where id = 10 ;

B. select * from user where name = ‘Arm’ ;

备注: id为主键，name字段创建的有索引；

解答： A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引，直接返回数据。因为聚集索引在建立B+TREE数据结构中，B+TREE叶子节点保存了对应的索引和挂载的这一行的数据，因此在查询的时候直接就可以在虚拟的索引表中取出对应的数据！

B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。因为二级索引在建立索引结构的时候，在叶子节点会保存对应的索引和该字段值对应的主键值。

1). 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

2). 查看索引

 SHOW INDEX FROM table_name ;

3). 删除索引

 DROP INDEX index_name ON table_name ;

案例演示:

A. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

 CREATE INDEX idx_user_name ON tb_user(name);

B. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

C. 为profession、age、status创建联合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

D. 为email建立合适的索引来提升查询效率。

CREATE INDEX idx_email ON tb_user(email);

查看tb_user表的所有的索引数据。

show index from tb_user;

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/120731.html