MySQL-索引详解_mysql索引规则详解-CSDN博客

页分裂：当前页存满时，重新分配下一页，或者主键连续递增时，增加一个中间值的主键，通过记录移动来保证下一个数据页中用户记录的主键值必须大于上一页。
目录项：由于主键递增，所以在增加数据时会出现页分裂情况，页与页之间通过双向链表形式链接，但是查找具体数据时依旧不确定该数据项在哪个数据页中，所以需要将所有数据页设置目录项，记录数据页的最小值和对应的数据页地址。

目录项记录的页：在对数据页进行删除和插入操作时，对应目录项需要删除，除此之外后面的目录项需要前移，成本高，这时可以把目录项再构成目录项记录的页。

更高级的目录：由于数据不断的增加，目录项记录的页达到一定存储大小时会生成新的目录项记录的页，此时查找数据可能会出现3次的IO，这时可以将多个目录项记录的页再次生成一个更高级的目录。
该模型为B+树，一般情况下不会超过4层，层数越小IO次数越小。实际开发中数据页存储16kb大小的数据，层数增加以指数的形式叠加，所以4层存储足够大的数据。

2、索引的设计原则

适合创建索引：

字段的数值有唯一性的限制，即使该字段是组合字段也必须建成唯一索引
频繁的作为where查询条件的字段
经常group by、order by的列，因为索引数据页中的数据是按照顺序连接存储的
update、delete的where条件列
distinct字段需要创建索引
对于join连接的where条件和连接字段创建索引，join连接的表尽量不要超过3张表，连接的字段类型必须一致，否则会涉及到函数转换导致索引失效
使用列的类型小的创建索引
使用字符串前缀创建索引，节点时占用空间小
区分度高（散列性高）的列适合作为索引，重复性强，计算区分度：select count(distinct a)/count(*) from t1
使用最频繁的列放到联合索引的左侧，增加联合索引的使用次数
多个字段都需要创建索引的情况下，联合索引优于单个索引

不适合创建索引：

where中使用不到的字段
数据量小的表最好不使用索引
有大量重复数据的列
避免对经常更新的表创建过多的索引
不建议使用无序的值作为索引
删除不再使用或者使用的索引
不要定义冗余或重复索引，例如：将联合索引的第一个字段定义成一个普通索引

限制索引数目：

每个索引都需要磁盘空间；
优化器执行时会对索引进行则优，索引过多会影响优化器生成执行计划的时间
索引会影响insert、update、delete等语句的性能，表中数据进行更改时，索引会对数据进行调整和更新，会造成负担

三、索引分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上分：普通索引、唯一索引、主键索引、全文索引
从物理实现方式分：聚簇索引、非聚簇索引
从作用字段上来分：单列索引、联合索引

3.1、普通索引

不附加任何条件，可以创建在任何数据类型中，对空和唯一没有要求。

3.2、唯一性索引

凡是声明UNIQUE参数都会自动生成一个唯一性索引。

3.3、主键索引

主键约束自动生成主键索引，也是聚簇索引，只能有一个，决定底层物理实现方式。

3.4、单列索引

在表中的单个字段上创建的索引，可以是唯一索引、全文索引，只要保证该索引只对应一个字段即可。

3.5、多列（组合、联合）索引

使用多个列的大小作为排序规则，比如为c1,c2创建一个联合索引，会先把各个记录和页按照c1进行排序，在c1相同的情况下按照c2进行排序

叶子节点由联合索引的字段数据和主键字段数据构成，也具有回表操作。使用时遵循最左前缀原则。

3.6、全文索引

搜索引擎使用关键技术，可以分析出文本文字中关键词的频率和重要性然后按照算法筛选出想要的搜索结果。FULLTEXT可以设置全文索引，只能创建在CHAR、VARCHAR或TEXT字段上，查询数据量较大的字符串类型字段时可以使用全文索引

3.7、聚簇索引

由主键构成的索引，一种数据存储方式，所有用户记录都存储在叶子节点，索引即数据，数据即索引。上面的例子就是聚簇索引结构。

B+树的叶子节点存储的是完整的用户记录。
使用主键值的大小进行记录和页的排序
页与页之间形成的是双向链表
即使自己不创建，innodb也会自动创建

优点：

数据访问快，索引和数据保存在同一个B+树中，获取数据比非聚簇索引快
排序查找和范围查找快
节省大量IO操作

缺点

插入速度依赖于插入顺序
更新主键代价高
二级索引访问需要两次索引查找，第一次查找主键值，第二次查找行数据

补充

MyISAM不支持聚簇索引
数据物理排序只能有一种方式，所以每个MySQL只能有一个聚簇索引，一般情况下是该表的主键
如果没有定义主键，innodb会自动选择一个非空的唯一索引代替，如果没有该索引，会隐式定义一个主键来作为聚簇索引
为了充分利用聚簇索引特性，Innodb表主键列尽量选用有序的顺序id，不建议使用无序、加密、字符串等列作为主键

3.8、二级索引（辅助索引、非聚簇索引）

一个表中只能有一个聚簇索引但是可以有多个二级索引，二级索引进行插入、删除、更新操作时比聚簇索引效率高

二级索引叶子节点只存储该索引字段下的值和主键的值，不建议存储其他字段，在可以设置多个二级索引的情况下，存储过多字段对空间进行浪费，会降低性能

二级索引除了该字段查找数据快速外，更大的作用是帮助其他字段对应的主键然后通过回表进行数据的查找

回表：此时如果查找其他字段，那么需要通过二级索引查找到对应的主键，重新再通过聚簇索引查找该主键下的其他字段值

四、InnoDB的B+树索引注意事项

4.1、根页面位置万年不动

当为某个表创建一个B+树索引时，会为这个索引创建一个根页面，此时没有目录项，根节点也没有用户记录。当插入一条数据时会先向根节点中增加，当根节点达到存储最大值时，会将该根节点数据复制到一个新的页，对这个页进行页分裂，得到新的页，插入的数据键值会存储到该页中，此时根节点升级为存储目录项记录的页。

4.2、内节点中目录项记录的唯一性

主要针对二级索引，目录项记录存储该索引的字段加主键加页号组成，没有主键的话索引字段可能会存在相同数值情况，无法向下进行查询，加入主键后在索引字段相同时，可以再查询主键，根据主键继续向下查询。

4.3、一个页面最少存储2条记录

五、MyISAM索引原理

在innodb中索引即数据，MyISAM中索引和数据是分开存储的，Innodb中数据页存储的是主键值+数据，MyISAM中存放的数数据记录的地址。

5.1、myisam与Innodb的区别：

Innodb中聚簇索引进行一次查找就可以获得数据，myisam中需要进行回表，可以认为myisam中索引全部都是二级索引。
innodb中索引即数据，myisam中索引和数据分在不同文件中存储，.myd和.myi
innodb数据页存储着主键值，myisam存储的是地址
myisam回表操作十分快速，直接拿地址进行回表，innodb是拿着主键进行回表
innodb必须有主键，没有会拿一个非空唯一值作为主键，即使没有也会自动生成一个隐式主键，myisam可以没有

5.2、总结

不建议使用过长字段作为主键，因为所有二级索引都引用主键，主键过长会导致二级索引过大
使用自增字段作为主键，维护方便

六、其他数据结构

6.1、全表遍历

6.2、Hash结构

Hash算法可以保证相同输入永远可以得到相同输出。
加速查找数据的数据结构，常见的有两类：

树，例如平衡二叉搜索树，查询、插入、修改、删除的平均时间复杂度都是O(log2N);

哈希，例如HashMap，查询、插入、修改、三处的平均时间复杂度都是O(1)

从效率上来说Hash比B+树快，但是Hash范围查找需要进行计算，适用于等值查找；数据存储是无序的，排序无法使用Hash特征；联合索引时Hash是合并一起计算，无法对单独一个或者几个索引进行查询；对于重复值比较多的列，会去遍历链表降低效率；innodb不支持此索引但支持自适应Hash索引、myisam不支持该结构，Memory支持；适用于Redis。