MySQL索引目录结构如下： MySQL索引原理 MySQL是如何查询数据 MySQL索引失效场景：设计索引原则：数

目录结构如下：

MySQL索引原理
MySQL是如何查询数据
MySQL索引失效场景：
设计索引原则：
数据库设计——三大范式：
数据库三大引擎——SQL引擎、存储引擎(InnoDB)、事务引擎
索引优缺点、使用场景
唯一索引和普通索引

0.索引分类

按照存储结构分：
- 聚簇/主键
- 非聚簇
按照数据结构分
- 哈希
- B+
按照类型分：
- 主键
- 唯一
- 联合
- 覆盖：用于减少回表操作
- 普通

1. MySQL索引原理

索引的本质是一种排好序的数据结构

1.1 Hash索引(InnoDB引擎不支持)

单条记录查询的效率很高，时间复杂度为1。Hash索引适合精确查找，但是范围查找不适合。因为存储引擎都会为每一行计算一个hash码，hash码都是比较小的，并且不同键值行的hash码通常是不一样的，hash索引中存储的就是Hash码，hash 码彼此之间是没有规律的并不能保证顺序性。所以值相近的两个数据，Hash值相差很远，被分到不同的桶中。不适合全表扫描。

总结：优点：单挑查询效率高，适合精确查找缺点：不适合范围查找。不适合全表扫描

1.2 B+树索引：

非叶子节点储存key(索引页：每个数据页的页号和其中的最小主键组成。)的信息，也就是上两层，第三层叶子节点存储数据页。

数据页之间是通过双向链表有序连接，而在数据页内部，每条数据之间由单向链表有序链接。

索引页+数据页组成的组成的B+树就是聚簇索引。聚簇索引是 MySQL 基于主键索引结构创建的。

索引页：维护每个数据页的页号和其中的最小主键信息。可通过二分法确定查询主键在哪个数据页，之后再数据页中定位主键数据。
数据页：里面是有序(根据主键排序)单向链表连接的每行数据。

优点：

层级少，查询速度更快
查询速度稳定：关键字数据地址都存在叶子节点上
天然具备排序功能：叶子节点数据构成了有序链表，数据紧密性高
全节点遍历快：无需遍历整棵树，只需遍历叶子节点即可，有利于全表扫描

与B树做索引区别：
B树的每个节点都包含键和对应值，而B+树的非叶子节点只包含键，所有的值都存储在叶子节点，这样的设计使得B+树的叶子节点形成有序链表，方便范围查询遍历。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

与B树做索引区别：场景举例：
假设有一个很大量的数据需要存储（比如100万个节点），内存上肯定无法全部存储，必然有很大部分在磁盘上。

如果使用B树进行存储，由于每个节点都存储数据，必然有一部分节点存储在内存中，一部分节点存储在磁盘上。
如果使用B+树存储，就有些不一样，由于B+树的内节点不存储具体数据，只做索引，所以B+树存储在内存中的节点数量会比B树多得多。所以，B+树做索引会更好，因为可以把所有的索引关系存储到内存中，然后通过一次性寻址找到存储具体数据的叶子节点。B树就无法做到这样，它只能一个节点一个节点的磁盘寻址。

2. MySQL是如何查询数据

主键索引(聚簇索引)：
MySQL 对于主键索引的维护是最简单的，就是根据主键去维护一个 B+ 树，因为主键的特点一般是递增的，也就是说是有序的，所以 MySQL 在维护的时候只需要将记录依次往数据页中追加即可，数据页满了就继续添加到下一个数据页。且每一条记录是完整的，即所有的列的值都维护。
非主键索引(二级索引)：
主键索引叶子节点存储了所有的字段信息，而非主键索引叶子节点存储的是索引字段和主键值。拿到主键值后会需要回表查询，效率有所折扣。

对于非主键索引，在维护 B+ 树的时候，会根据联合索引的字段依次去判断。eg：假设联合索引为：name + address + age，那么 MySQL 在维护该索引的 B+ 树的时候，首先会根据 name 进行排序，name 相同的话会根据第二个 address 排序，如果 address 也一样，那么就会根据 age 去排序，如果 age 也一样，那么就会根据主键字段值去排序（主键不可能是一样的），且对于非主键索引，MySQL 在维护 B+ 树的时候，仅仅是维护索引字段和主键字段。

3. MySQL索引失效场景：

4. 设计索引原则：

需要建立索引：

主键自动建立唯一索引
为频繁查询的字段建立索引：
提高查询效率
尽量为ORDER BY 和 GROUP BY 后面的字段建立索引：
查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在B+树中的记录都是排序好的。

不需要建立索引：
6. 不要建立太多的索引：
MySQL维护索引是需要空间和耗费性能的，MySQL会为每个索引字段维护一颗B+树。所以如果索引过多，这无疑是增加了MySQL的负担。

频繁增删改的字段不要建立索引某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响MySQL的性能。
表记录太少无需创建索引
避免为"大字段"建立索引：
因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。

7. 索引的优缺点，何时用到何时无需用到

优点：

大大加快数据的检索速度
加快表与表之间的链接

缺点：

创建索引和维护索引要耗费时间
索引需要占据物理空间
降低表的增删改效率，每次增删改索引都需要进行动态维护

怎么知道索引用没用上
通过explain查询sql执行计划，主要看key使用的是哪个索引。

8. 主键和唯一索引和普通索引

唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。

唯一索引和主键区别：
1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。

一个表中可以有多个唯一性索引，但只能有一个主键。
主键列不允许空值，而唯一性索引列允许空值。
唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下，创建唯一性的非聚簇索引，但是，也可以指定所创建的索引是聚簇索引。
建立主键的目的是让外键来引用.
普通索引
普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
唯一索引
普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
注意：

允许空值重复。
unique除了在插入重复数据的时候会报错，还会使auto_increment自动增长。

存在唯一键冲突时，避免策略：

insert ignore into：会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据.主键还是自动增长了。
replace into：删除再新增
insert into ... on duplicate key update：重复则update > 如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0

6. 数据库三大引擎——SQL引擎、存储引擎(InnoDB)、事务引擎

见 juejin.cn/post/720023…

9. E-R图

在E-R（实体-关系）图中，用来表示实体之间联系的图形通常是菱形。这个菱形表示一个关系或联系实体，它连接两个或多个实体，并表示这些实体之间的关系。

E-R图是一种用于可视化数据库设计的工具，用于描述现实世界中的实体（如人、物品、事件等）以及它们之间的关系。关系或联系实体（用菱形表示）通常包含与实体之间的关联或连接相关的属性。在E-R图中，还会使用矩形表示实体，用椭圆表示属性，以及使用直线或箭头表示关系之间的连接。

MySQL索引