Mysql索引类型总结
按照数据布局维度划分：[*]BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是利用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
[*]哈希索引：雷同键值对的情势，一次即可定位。
[*]RTree 索引：一样平常不会利用，仅支持 geometry 数据类型，优势在于范围查找，服从较低，通常利用搜索引擎如 ElasticSearch 代替。
[*]全文索引：对文本的内容进行分词，进行搜索。现在只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一样平常不会利用，服从较低，通常利用搜索引擎如 ElasticSearch 代替。
按照底层存储方式角度划分：
[*]聚簇索引（聚集索引）：索引布局和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
[*]非聚簇索引（非聚集索引）：索引布局和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还黑白主键，利用的都黑白聚簇索引。
按照应用维度划分：
[*]主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
[*]平常索引：仅加速查询。
[*]唯一索引：加速查询 + 列值唯一（可以有 NULL）。
[*]覆盖索引：一个索引包含（或者说覆盖）全部必要查询的字段的值。
[*]团结索引：多列值构成一个索引，专门用于组合搜索，其服从大于索引合并。
[*]全文索引：对文本的内容进行分词，进行搜索。现在只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一样平常不会利用，服从较低，通常利用搜索引擎如 ElasticSearch 代替。
MySQL 8.x 中实现的索引新特性：
[*]隐蔽索引：也称为不可见索引，不会被优化器利用，但是仍旧必要维护，通常会软删除和灰度发布的场景中利用。主键不能设置为隐蔽（包罗显式设置或隐式设置）。
[*]降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍旧是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
[*]函数索引：从 MySQL 8.0.13 版本开始支持在索引中利用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。
主键索引(Primary Key)
数据表的主键列利用的就是主键索引。
一张数据表有只能有一个主键，而且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有表现的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
https://i-blog.csdnimg.cn/img_convert/c64658c10800f864e29b51344cdd7751.png
主键索引
二级索引
二级索引（Secondary Index）的叶子节点存储的数据是主键的值，也就是说，通过二级索引可以定位主键的位置，二级索引又称为辅助索引/非主键索引。
唯一索引，平常索引，前缀索引等索引都属于二级索引。
PS: 不懂的同学可以暂存疑，逐步往下看，后面会有答案的，也可以自行搜索。
[*]唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询服从。
[*]平常索引(Index):平常索引的唯一作用就是为了快速查询数据，一张表允许创建多个平常索引，并允许数据重复和 NULL。
[*]前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比平常索引建立的数据更小，由于只取前几个字符。
[*]全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字的信息，是现在搜索引擎数据库利用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
二级索引:
https://i-blog.csdnimg.cn/img_convert/641a987ae8401457b53cea2c883bad06.png
二级索引
聚簇索引与非聚簇索引
聚簇索引（聚集索引）
聚簇索引介绍
聚簇索引（Clustered Index）即索引布局和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。
在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
聚簇索引的优缺点
长处：
[*]查询速度非常快：聚簇索引的查询速度非常的快，由于整个 B+树本身就是一颗多叉均衡树，叶子节点也都是有序的，定位到索引的节点，就相称于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
[*]对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点：
[*]依赖于有序的数据：由于 B+树是多路均衡树，如果索引的数据不是有序的，那么就必要在插入时排序，如果数据是整型还好，否则雷同于字符串或 UUID 这种又长又难比力的数据，插入或查找的速度肯定比力慢。
[*]更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，以是对于主键索引来说，主键一样平常都是不可被修改的。
非聚簇索引（非聚集索引）
非聚簇索引介绍
非聚簇索引(Non-Clustered Index)即索引布局和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还黑白主键，利用的都黑白聚簇索引。
非聚簇索引的叶子节点并不肯定存放数据的指针，由于二级索引的叶子节点就存放的是主键，根据主键再回表查数据。
非聚簇索引的优缺点
长处：
更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。
缺点：
[*]依赖于有序的数据:跟聚簇索引一样，非聚簇索引也依赖于有序的数据
[*]大概会二次查询(回表):这应该黑白聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，大概还必要根据指针或主键再到数据文件或表中查询。
这是 MySQL 的表的文件截图:
https://i-blog.csdnimg.cn/img_convert/27ef9df89b8e656a856ae3b4b1aea94b.png
MySQL 表的文件
聚簇索引和非聚簇索引:
https://i-blog.csdnimg.cn/img_convert/d2c85e97c645588d8296b675eb558a65.png
聚簇索引和非聚簇索引
非聚簇索引肯定回表查询吗(覆盖索引)?
非聚簇索引不肯定回表查询。
试想一种环境，用户预备利用 SQL 查询用户名，而用户名字段正好建立了索引。
SELECT name FROM table WHERE name='guang19'; 那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。
纵然是 MYISAM 也是这样，固然 MYISAM 的主键索引确实必要回表，由于它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键呢?
SELECT id FROM table WHERE id=1; 主键索引本身的 key 就是主键，查到返回就行了。这种环境就称之为覆盖索引了。
覆盖索引和团结索引
覆盖索引
如果一个索引包含（或者说覆盖）全部必要查询的字段的值，我们就称之为 覆盖索引（Covering Index） 。
在 InnoDB 存储引擎中，非主键索引的叶子节点包含的是主键的值。这意味着，当利用非主键索引进行查询时，数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完备的行数据。这个过程被称为“回表”。
覆盖索引即必要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。
如主键索引，如果一条 SQL 必要查询主键，那么正好根据主键索引就可以查到主键。再如平常索引，如果一条 SQL 必要查询 name，name 字段正好有索引，
那么直接根据这个索引就可以查到数据，也无需回表。
https://i-blog.csdnimg.cn/img_convert/f2ed9a667c8b01020793dabf6d8707a0.png
覆盖索引
我们这里简朴演示一下覆盖索引的效果。
1、创建一个名为 cus_order 的表，来实际测试一下这种排序方式。为了测试方便， cus_order 这张表只有 id、score、name这 3 个字段。
CREATE TABLE `cus_order` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`score` int(11) NOT NULL,
`name` varchar(11) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4; 2、定义一个简朴的存储过程（PROCEDURE）来插入 100w 测试数据。
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `BatchinsertDataToCusOder`(IN start_num INT,IN max_num INT)
BEGIN
DECLARE i INT default start_num;
WHILE i < max_num DO
insert into `cus_order`(`id`, `score`, `name`)
values (i,RAND() * 1000000,CONCAT('user', i));
SET i = i + 1;
END WHILE;
END;;
DELIMITER ; 存储过程定义完成之后，我们实行存储过程即可！
CALL BatchinsertDataToCusOder(1, 1000000); # 插入100w+的随机数据 等待一会，100w 的测试数据就插入完成了！
3、创建覆盖索引并利用 EXPLAIN 下令分析。
为了能够对这 100w 数据按照 score 进行排序，我们必要实行下面的 SQL 语句。
#降序排序
SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC; 利用 EXPLAIN 下令分析这条 SQL 语句，通过 Extra 这一列的 Using filesort ，我们发现是没有效到覆盖索引的。
https://i-blog.csdnimg.cn/img_convert/4573f994d506e3fa0ff007a4deb48dd3.png
不外这也是理所应当，毕竟我们现在还没有创建索引呢！
我们这里以 score 和 name 两个字段建立团结索引：
ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name); 创建完成之后，再用 EXPLAIN 下令分析再次分析这条 SQL 语句。
https://i-blog.csdnimg.cn/img_convert/ac85ca581c1b37bc2ac3c0234969351d.png
通过 Extra 这一列的 Using index ，阐明这条 SQL 语句成功利用了覆盖索引。
关于 EXPLAIN 下令的详细介绍请看：MySQL 实行计划分析这篇文章。
团结索引
利用表中的多个字段创建索引，就是 团结索引，也叫 组合索引 或 复合索引。
以 score 和 name 两个字段建立团结索引：
ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name); 最左前缀匹配原则
最左前缀匹配原则指的是在利用团结索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会利用索引来过滤数据，这样可以提高查询服从。
最左匹配原则会不停向右匹配，直到碰到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会克制匹配（相干阅读：团结索引的最左匹配原则全网都在说的一个错误结论）。
假设有一个团结索引(column1, column2, column3)，其从左到右的全部前缀为(column1)、(column1, column2)、(column1, column2, column3)（创建 1 个团结索引相称于创建了 3 个索引），包含这些列的全部查询都会走索引而不会全表扫描。
我们在利用团结索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。
我们这里简朴演示一下最左前缀匹配的效果。
1、创建一个名为 student 的表，这张表只有 id、name、class这 3 个字段。
CREATE TABLE `student` (
`id` int NOT NULL,
`name` varchar(100) DEFAULT NULL,
`class` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_class_idx` (`name`,`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 2、下面我们分别测试三条不同的 SQL 语句。
https://i-blog.csdnimg.cn/img_convert/2d6f42efd8ffbad7eefe6da27c63ef2f.png
# 可以命中索引
SELECT * FROM student WHERE name = 'Anne Henry';
EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry' AND class = 'lIrm08RYVk';
# 无法命中索引
SELECT * FROM student WHERE class = 'lIrm08RYVk'; 再来看一个常见的口试题：如果有索引 团结索引（a，b，c），查询 a=1 AND c=1会走索引么？c=1 呢？b=1 AND c=1呢？
先不要往下看答案，给自己 3 分钟时间想一想。
[*]查询 a=1 AND c=1：根据最左前缀匹配原则，查询可以利用索引的前缀部分。因此，该查询仅在 a=1 上利用索引，然后对结果进行 c=1 的过滤。
[*]查询 c=1 ：由于查询中不包含最左列 a，根据最左前缀匹配原则，整个索引都无法被利用。
[*]查询b=1 AND c=1：和第二种一样的环境，整个索引都不会利用。
MySQL 8.0.13 版本引入了索引跳跃扫描（Index Skip Scan，简称 ISS），它可以在某些索引查询场景下提高查询服从。在没有 ISS 之前，不满足最左前缀匹配原则的团结索引查询中会实行全表扫描。而 ISS 允许 MySQL 在某些环境下避免全表扫描，纵然查询条件不符合最左前缀。不外，这个功能比力鸡肋， 和 Oracle 中的没法比，MySQL 8.0.31 还报告了一个 bug：Bug #109145 Using index for skip scan cause incorrect result（后续版本已经修复）。个人发起知道有这个东西就好，不必要深究，实际项目也不肯定能用上。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]