MySQL的索引类型的使用和底层实现原理

1、索引的概念

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结构
底层通过B+树实现
优点：可以提高检索数据的速度
缺点：创建和维护需要消耗一定的时间，耗时随数据的增加而增加，需要占用一定的物理空间，增加、删除和修改数据时，需要动态的维护索引

2、索引的分类

2.1普通索引

没有任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。

2.2唯一索引

属性唯一，有唯一性约束或者主键约束

2.3全文索引

全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。查询数据量较大的字符串类型字段时，使用全文索引可以提高查询速度。

2.4单列索引

对一个字段建立索引索引。可以包括上述的三种索引方式。应用该索引的条件只需要保证该索引值对应一个字段即可。

2.5多列索引

在表的多个字段上创建一个索引。该索引只想创建时对应的多个字段，可以通过这几个字段进行查询。

2.6空间索引

使用SPATIAL参数可以设置控件索引。控件索引只能建立在控件数据类型（LINESTRING、POINT、GEOMETRY等）上，这样可以提高系统获取控件数据的效率。MySQL中只有MyISAM存储引擎支持空间索引，且该字段不能为空值。

3、索引的创建与删除

3.1在创建表的时候创建索引

 create table 表名(属性名 数据类型[完整性约束], 属性名 数据类型[完整性约束], …… [unique|fulltext|spatial] index  索引名(属性名1[长度] [asc|desc] ); 

例如：在创建一个表名为test的表，的id字段建立名为index——id的唯一性索引，升序排列

create table test(id int unique, name varchar(20), unique index index_id(id asc) ); 

3.2在已存在的表上创建索引

create [unique|fulltext|spatial] index 索引名
on 表名(属性名 [长度] [asc|desc]); 

3.2.1创建普通索引

例如：在test表的id字段建立名为index_id的索引

create index index_id on test(id); 

3.2.2创建唯一性索引

例如：在test表的id字段上建立名为index_id的唯一性索引

create unique index index_id on test(id); 

3.2.3创建全文索引

fulltext用来设置全文索引，表的存储引擎必须是MyISAM类型，创建索引的字段必须是char、varchar、text等类型
例如：在test表的info字段上建立名为index_info的索引

create fulltext index index_info on test(info); 

3.3用alter table语句创建索引

alter table 表名 add [unique|fulltext|spatial] index  索引名(属性名1[长度] [asc|desc]); 

3.4删除索引语句

drop index 索引名 on 表名; 

4、索引的设计原则——控制索引的数量

（1）数据量比较低的时候不需要设计索引
（2）选择唯一性索引。——属性是不重复的
distinct（属性）：属性不重复的数据
（3）为经常需要排序、分组和联合操作的字段建立索引
（4）为常作为查询条件的字段建立索引
（5）限制索引的数目
（6）尽量使用数据量少的索引
（7）尽量使用前缀来索引
（8）删除不在使用或者很少使用的索引

5、索引底层数据结构

5.1B树

特点：分支数M>2
每个节点最多存储M-1个数据
在非叶子和叶子都存储数据

5.2B+树

特点：只有叶子节点存储数据，非叶子节点存储关键字
关键字的作用：帮助我们查找要寻找的数据
为所有的叶子节点增加了一个链指针，连成一个链表

5.3在MYISAM引擎下—非聚集索引

索引还可以分为：
主键索引：（索引建立在主键上）MySQL会默认创建主键索引，所有表一定会 主键，如果不指定主键，MySQL会默认以行号为主键
辅助索引：（索引建立在非主键上）与主键索引形式相同，叶子节点存储建立索引的属性和数据的地址。

5.3.1主键索引

.myd文件：存储表的数据
.myi文件：存储表的索引
注意：要将B+树的结构加载到内存中才能使用
并不是将整个B+树的结构加载到内存，而是一个节点一个节点的进行加载 ，首先加载根节点，然后比较大小判断加载那个节点
叶子节点：存储的是主键（建立索引的属性）和数据的地址

对于多列索引比较原则：从左往右

5.4在InnoDB存储引擎下——聚集索引

MySQL会默认创建主键索引，所有表一定会有主键

5.4.1主键索引

叶子节点：存储主键和所有的数据，
原因：数据和索引在同一个文件下，数据和索引都存储在**.ibd文件**下

5.4.2辅助索引

建立在非主键上，
叶子节点存储，建立索引的属性和主键
所以先通过建立索引的属性通过B+树找到主键，然后在根据主键的值通过主键的B+树查找。

聚集和非聚集的区别：
叶子节点存储的是数据本身还是数据地址。

6、联合查询使用索引

6.1explain命令

id：选择标识符
select_type：表示查询的类型
table：输出结果集的表
partitions：匹配的分区
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度 字节数 和你建立索引的属性是有关的
ref：列与索引的比较
rows：扫描出的行数（估算的行数）
filtered：按表条件过滤的行百分比
Extra：执行情况的描述和说明

使用方法

explain select * from student where name = 'abc' and cid = 123\G 

mysql在执行SQL语句过程时对SQL语句进行优化，根据自己索引调整SQL语句的顺序，前提是不改变SQL语句本身的结果

6.2联合查询如何使用索引：

MySQL会首先判断a和b哪个表小，这里表小主要指的是行少，假设b表小，MySQL会对b表进行整表遍历，然后在a表上根据id字段进行查询,所以b表就是小表，无论如何都是要整表遍历的，是使用不到索引的，但是大表a表的id字段创建索引，就能使用到了! 所在在连接查询的时候，小表总是要整表搜索的，索引没有用，大表创建索引是能提高查询效率的，小表决定查询次数，大表决定查询时间能不能用到索引，用到是那张表的索引。

7、InnoDB和MyISAM的比较

两种引擎下的索引那个更好？？
只考虑查询效率：InnoDB引擎下的索引更好，数据和主键在同一个文件下
难以维护，数据和索引在同一文件下，那么数据的修改必然引起索引的修改
MYISAM： 数据和索引分开存储，更好维护，查询效率低

如何选择引擎？？
要求更高的查询效率并且数据的修改不频繁。——>InnoDB
数据修改频繁——>MYIASM
如果需要的功能更多（事务、外键）这时只能使用InnoDB，外键和事务的维护也需要耗费时间的

本文地址：https://blog.csdn.net/qq_43833476/article/details/107912189