欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-16 13:20:08
文章目录1、索引的概念2、索引的分类2.1普通索引2.2唯一索引2.3全文索引2.4单列索引2.5多列索引2.6空间索引3、索引的创建3.1在创建表的时候创建索引3.2在已存在的表上创建索引3.2.1创建普通索引1、索引的概念索引是创建在表上的,是对数据库表中一列或者多列的值进行排序的一种结构底层通过B+树实现优点:可以提高检索数据的速度缺点:创建和维护需要消耗一定的时间,耗时随数据的增加而增加,需要占用一定的物理空间,增加、删除和修改数据时,需要动态的维护索引2、索引的分类2.1普通索引没...



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个数据
在非叶子和叶子都存储数据
MySQL的索引类型的使用和底层实现原理

5.2B+树

特点:只有叶子节点存储数据,非叶子节点存储关键字
关键字的作用:帮助我们查找要寻找的数据
为所有的叶子节点增加了一个链指针,连成一个链表
MySQL的索引类型的使用和底层实现原理

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

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

5.3.1主键索引

.myd文件:存储表的数据
.myi文件:存储表的索引
注意:要将B+树的结构加载到内存中才能使用
并不是将整个B+树的结构加载到内存,而是一个节点一个节点的进行加载 ,首先加载根节点,然后比较大小判断加载那个节点
叶子节点:存储的是主键(建立索引的属性)和数据的地址
MySQL的索引类型的使用和底层实现原理

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

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

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

5.4.1主键索引

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

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

5.4.2辅助索引

建立在非主键上,
叶子节点存储,建立索引的属性和主键
所以先通过建立索引的属性通过B+树找到主键,然后在根据主键的值通过主键的B+树查找。
MySQL的索引类型的使用和底层实现原理

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

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,外键和事务的维护也需要耗费时间的
MySQL的索引类型的使用和底层实现原理

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