Mysql——索引_mysql index.md-CSDN博客

本文链接：https://blog.csdn.net/qq_43117978/article/details/105118298

githHub地址https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/MySQL%20Index.md**

为什么要使用索引

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
帮助服务器避免排序和临时表。
将随机IO变为顺序IO
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

索引也有缺点

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

在InnoDB存储引擎中，是以主键为索引来组织数据的。在InnoDB存储引擎中，每张表都有个主键，如果再创建表时没有显示的定义主键，则InnoDB存储引擎会按如下方式选择或创建主键。

首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。
如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指正作为索引。
如果有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引作为主键。

MySql索引主要使用两种数据结构：B+ Tree和哈希表。

关于B+ Tree：这里的B代表的是平衡而不是二叉，其由平衡二叉树（AVLTree）到平衡多路查找树（B -Tree）演变优化而来。InnoDB存储引擎就是用B+Tree实现其索引结构。

B-Tree的每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

总结来说B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
在这里插入图片描述

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2_{4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

InnoDB页结构如图：
在这里插入图片描述

哈希表

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

InnoDB存储引擎会监控对表上各项索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引，DBA不能对建立哈希索引的过程进行干预，只能启动或禁用自适应哈希索引

数据库一般采用除法散列的方法，即取k除以m的余数，将关键词k映射到m个槽的某一个去，即哈希函数为h(k) = k mod m，当发生冲突时，即两个关键字可能映射到同一个槽上，采用链接法，即以链表的形式保存冲突的关键字，和HashMap类似

当对热点数据建立了哈希索引以后，省去在B+树上进行查找，可以极大地提高服务的性能
在这里插入图片描述

索引类型：

主键索引：数据库主键用的就是主键索引。

在mysql的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

辅助索引（二级索引）

二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。
2.1 唯一索引(Unique Key) ：唯一索引也是一种约束。**唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。**建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2.2 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
2.3前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
2.4 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

添加链接描述

覆盖索引，聚集索引，辅助索引，联合索引

覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！
具体请看

聚集索引和非聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
非聚集索引即索引结构和数据分开存放的索引。
二级索引属于非聚集索引。
两种都依赖与有序的数据
不同的是：聚簇索引更新代价大；非聚簇索引可能回表。

最左前缀原则！！重点！！

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中就肯定能命中，那么就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

MySQL 5.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

参考博客：

B+Tree在数据库索引上拥有独特优势的原因（为什么比红黑树更合适）：https://blog.csdn.net/qq_21993785/article/details/80580679?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树：https://blog.csdn.net/qq_21993785/article/details/80576642
MySQL索引为什么要用B+树实现？https://blog.csdn.net/zzti_erlie/article/details/82973742