MYSQL索引解析

1. 索引介绍

1.1 什么是索引：

索引（Index）是帮助MySQL高效获取数据的数据结构

1.2 为什么使用索引：

数据是存储在磁盘上的，如果没有索引，查询数据时就需要将所有的数据都加在到内存中，依次检索。且CPU一次加载的数据量是有限的，这样就需要多次IO,开销比较大。使用索引后，数据是按索引进行排序的，当通过索引字段进行排序或范围查找速度就比较快。且索引会按照一定的数据结构组织，比如innodb使用B+树存储索引，一般2~3层的B+树就可以存储8-10亿的数据，这样一般就只需2-3次的IO，较少了IO开销，同时查询数据时可按B+树的二分搜索快速检索的数据。

1.3 索引优缺点：

优点：
1)提高数据查询效率，降低数据库IO成本;
2)通过索引列对数据进行排序，加快数据排序分组的速度，降低了CPU消耗
3)加快表与表之间的连接
缺点：
1)索引也是一张表，需要占用磁盘空间;
2)增删改数据时需要维护索引，影响效率
1
2
3
4
5
6
7

1.4索引分类

a.主键索引： 设定主键后数据库自动为其建立的索引，innodb为聚簇索引

如果不指定主键的话，则会查看表中是否存在非空的唯一列，如果存在将此列作为主键。如果不存在，创建row_id作为主键

b.单值索引（单列索引 | 普通索引）： 除了主键以外，为表中的其他字段创建的索引

c.唯一索引： 索引列的值必须唯一，但允许有空值（主键索引和唯一索引的区别：主键索引不能为空，唯一索引可以存在多个null）

				*NULL 的定义 ，是指未知值。 所以多个NULL ，都是未知的，不能说它们是相等的，也不能说是不等，就是未知的。所以多个NULL的存在是不违反唯一约束的。*
1

d.复合索引： 基于表中的多个列共同创建的索引

上述都是innodb支持的索引，MYISAM存储引擎还支持全文索引

Full Text 全文索引：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。

1.5 索引的创建：

1.主键索引 自动创建

--建表 主键自动创建主键索引
create table t_user(id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user;
1
2
3
4
5
6

2.单列索引(普通索引|单值索引)
--建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),key(name)); 
'注意:随表一起建立的索引索引名同列名一致，没法起别名，可以在建表后设立别名'
	  
--建表后创建
create index name_index on t_user(name);

--删除索引
drop index 索引名 on 表名
1
2
3
4
5
6
7
8
9
10

3.唯一索引
--建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
 
--建表后创建
create unique index nameindex on t_user(name);
1
2
3
4
5
6

4.复合索引
---建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 
--建表后创建
 create index nameageindex on t_user(name,age);
 
1
2
3
4
5
6
7

总结：

1.6 什么情况适合 | 不适合创建索引

1)适合创建索引的情况

• 主键自动建立唯一索引
• 经常查询的字段
• 查询中经常用于连接的字段，通过外键关系建立索引，加快连接速度
• 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度
• 查询中统计或者分组字段

2)不适合创建索引的情况

• 表记录太少
• 经常增删改的表或者字段
• ·Where 条件里用不到的字段
• 区分度不高的字段不适合建立索引，如性别等
• 参与列计算的列不适合建索引

2、索引的底层原理

2.1 索引的数结构

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

Hash索引和B+树索引的区别？

• 哈希索引不支持排序，因为哈希表是无序的。
• 哈希索引不支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

2.1 思考

---建表
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
select * from t_emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

为什么上面数据明明没有按顺序插入,为什么查询时却是有顺序呢?
- 原因是:mysql底层为主键自动创建索引,在插入的时候会对数据进行排序，以便利用二分进行查找
- 也就是mysql底层真正存储是这样的
- 为什么要排序呢?因为排序之后在查询就相对比较快了 如查询 id=3的我只需要按照顺序找到3就行啦(如果没有排序就需要一个个的比较!)
1
2
3
4

2.2 B树 | B+树

MySQL使用B+Tree实现索引结构，所以首先来了解哈B树 | B+树这两种数据结构

B树：

所有每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

B+树

只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

2.3 为什么使用B+树：

从查询上来看，B+Tree因为数据都在叶子节点，查找元素时都要从根节点一路检索到叶子节点，而BTree每个节点都存储key和data，就可能中途就找到了数据，但看这样是比B+Tree高，但为什么还是用B+Tree呢？

1）首先CPU是按页将数据加载入内存的，每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。B+Tree是在B-Tree基础上的一种优化，在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低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操作。

2）BTree节点分布在各层，不方便范围查找。在B+Tree中，每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从9到19的所有数据记录，当找到9后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

3）B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

页目录中存了每一页的口头的节点的键值和指针，比如查找节点4数据，现在页目录中找是在哪一页，发现在第1页，就去第一页遍历

一般B+数就已经是上亿的数据量了
如int id,varchar(20),int age;
4+ 20+4 + 8=36 (一个指针一般占4-8个字节)
16KB*1024/36 = 455(条数据)
页目录 16 * 1024/12=1365.33333（页）
所以总数据量=621226.66667
三层可1365x1365x455=847974400能就8亿的数据量
1
2
3
4
5
6
7

2.4 MySQL索引实现

在MySQL中，最常用的两个存储引擎是MyISAM和InnoDB，它们对索引的实现方式是不同的。

1）MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据地址，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引

主索引就是数据按主键聚集的索引，辅助索引就是数据通过非主键以外的字段聚集的索引

上图中，以Col1为主键，Col2为辅助键。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，都是B+Tree,只是主索引B+树的节点存储了主键，要求key是唯一的，而辅助索引B+树存储了辅助键，key可以重复,data域保存真实数据的地址。

MyISAM中索引检索的算法为首先按照B+Tree搜索算法在对应索引表中搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，到内存中读取相应数据记录。

2）InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，叶子节点包括索引和完整数据记录，数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

将主键组织到一棵B+树中，而行数据就储存在叶子节点上，所以InnoDB的数据文件本身就是索引文件，这个索引的key是数据表的主键，数据按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。如下图中在Col3上的一个辅助索引。

若使用"where id = 15"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

所以InnoDB通过辅助索引检索时：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。而MyISAM由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

3. MySIAM | InnoDB

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，一切都不费解了，只是顺着逻辑推而已。

两种存储引擎的区别：
1、MyISAM是非事务安全的，而InnoDB是事务安全的
2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁
3、MyISAM支持全文类型索引，而InnoDB不支持全文索引
4、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM
5、MyISAM表保存成文件形式，跨平台使用更加方便
6、MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择
7、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。

MySQL主键基本int型，而不推荐用UUID作为主键原因？

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。UUID数据很离散，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整（插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质），十分低效，如果是int自增的，不仅方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。

什么不建议使用过长的字段作为主键？

因为所有辅助索引都引用主索引，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

什么情况索引失效?

• 在进行最左前缀比配时，中间有个索引列使用的是范围查询，则会导致后面的索引列失效
• 模糊查询若以%开头，使用 !=，is null,is not null，or会变成全文索引
• 对索引列计算–失效
• 字符串不加单引号会查询时需要类型转换–失效
• 查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

数据库的底层实现总结（索引底层实现）：

数据库使用B+树来组织索引，将主键组织到一棵B+树中，而所有数据基于主键排好序储存在叶子节点上，叶子节点之间使用双向指针连接，方便范围查询和按主键排序。非叶子节点只存储键值，所有数据按页存储，数据一默认一页的大小为16KB,就B+树这个数据结构而言，一个3层的B+树存储的数据是8亿-10亿左右，且顶层常驻内存，一般2~3次IO就可将想要检索的数据加载到内存中。

不同存储引擎对索引实现略有差别，MySIAM和InnoDB都是用B+树实现索引，但MySIAM使用非聚簇索引，主索引和辅助索引叶子节点都存储数据真实地址，而InnoDB主键索引叶子节点存储数据，所有辅助索引都引用主键作为data域。

所以InnoDB通过辅助索引检索时：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。而MyISAM由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

索引参考资料：
https://blog.csdn.net/qq_33599978/article/details/78368660?spm=1001.2101.3001.6650.12&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7Eessearch%7Evector-12.essearch_pc_relevant&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7Eessearch%7Evector-12.essearch_pc_relevant

什么是B+树,怎么增删节点?- 参考资料: https://www.cnblogs.com/lianzhilei/p/11250589.html