MySQL索引介绍

最新推荐文章于 2024-06-12 15:28:19 发布

liangcheng0213

最新推荐文章于 2024-06-12 15:28:19 发布

阅读量86

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/liangcheng0213/article/details/117869383

版权

索引

索引是什么？

索引是一种数据结构，索引是创建在表上，是对数据库表中的一列或者多列的值进行排序的一种结果，索引能提高查询效率，避免全表查询

索引分类：

1.普通索引：没有任何限制条件，可以给表中的任何的字段创建索引
2.唯一性索引：使用unique修饰的字段，值是不能重复的，主键索引就属于唯一性索引
3.主键索引：使用primark key修饰的字段会自动添加主键索引
4.全文索引：使用fulltext修饰的字段可以设置为全文索引，只支持varchar\text等类型的字段

索引SQL

1、创建表时可以创建索引

create table table_name(
属性名属性类型
…
[约束条件 unique(唯一性约束) fulltext(全文约束)] index 约束名(属性名) );

create table test(
id1 int,
id2 int,
unique index idx_id(id1)//对id1属性创建一个唯一性索引
)

2、在已有表上添加索引

通过create语法创建

create[unique、fulltext、 spatial] index 索引名 on 表名（属性名）

create index idx_2 on test212(id2);

通过alter语法创建

alter table 表名 add 索引类型 index 索引名(属性名)

alter table test212 add index idx_id12(id1,id2);// (属性名,属性名) 属于联合约束

3、删除索引

drop index 索引名 on 表名

drop index idx_id12 on test212;

4、查看建表语句：

show create table 表名;

show create table student;

索引的执行过程

当未创建索引；分析查询过程，分析SQL执行过程是否使用索引是使用explain关键字加载查询语句的前面

mysql> explain select * from student where s_name = '盖伦'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

possible_keys和key的值都为null，说明当前没有命中索引，rows=6表示进行全表的匹配查询

对student表添加s_name的索引，再次通过explain关键字来分析查询操作过程，

mysql> explain select * from student where s_name = '盖伦'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ref
possible_keys: sname_1
          key: sname_1
      key_len: 202
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

possible_keys和key使用到了创建的索引sname_1，最终执行匹配的数据的行数1行

索引底层结构

MySQL支持两种索引，一种是B树索引，一直是哈希表索引，这两种索引的查询效率是比较高的
MySQL innoDB存储引擎，基于B树(实际MySQL采用的是B+树)的索引结构。

B树的特征

在这里插入图片描述

B树的特征：
1.根节点至少包含两个孩子
2.树中每个结点最多含有m个孩子(m >= 2)
3.除了根节点和叶结点外，其他每个结点至少含有	ceil(m/2)个孩子，ceil为向上取整
4.所有叶子结点位于同一层（高度相同）
5.假设每个非终端结点中包含有n个关键字信息，其中
 Ki(i = 1…n)为关键字，且按顺序升序排列
关键字的个数n必须满足：[ceil(m / 2) - 1] <= n <= m - 1
非叶子结点的指针P[1],P[2],…,P[M];其中K[1]指向关键字小于K[1]的子树，P[M - 1] 指向关键字大于K[M - 1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树，比如看图中关键字值为8的这个结点，P1所对应的这个子树，其值均小于8

查找效率为O(logn)，同时因为B-tree的这些上述特征，在增删改动数据时，根据一些策略，其结构可以保持，不会变为线性。

B+树结构（优于B树）

在这里插入图片描述

B+树是B树的变体，其基本的定义与B树相同,除了：
  非叶子结点的子树指针与关键字个数相同（所以相对于B树，B+树能够存储更多的关键字）
  非叶子结点的子树指针P[i]，指向关键字值**[K[i], K[i+1])**的子树，注意区间为左开右闭。
  非叶子结点仅仅用来索引，数据都保存在叶子结点中（B+树所有的检索都是从根部开始，检索到叶子结点才能结束，而且非叶子结点不存储数据的话就能存储更多关键字）
  B+树相对于B树更矮
  所有叶子结点均有一个链指针指向下一个叶子节点

综合上面的，B+Tree更适合用来做存储索引：
1、B+Tree的特点使得磁盘I/O代价更低，B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低
2、B+Tree的查询效率更加稳定（查询路径均为从根结点到叶子结点，查询效率均为O(logn)）
3、B+Tree更有利于对数据库的扫描（只需要遍历叶子结点就可以进行范围查询）

Hash结构

在这里插入图片描述

其查询效率高于B+Tree，只需经过一次定位，就可以查询到对应数据区
但是其也存在一些弊端，因而不能成为数据库的主流索引的扛把子：
1、仅仅能满足 “=” , “IN”，不能使用范围查询
2、无法被用来避免数据的排序操作
3、不能利用部分索引键查询，B+Tree可以利用组合索引中的部分索引查询
4、不能避免表扫描
5、因为Hash值是经过一定的算法得到的，所以存在相同的情况，当遇到大量Hash值相等的情况后性能不一定比B-Tree索引要高

另：哈希索引当然是由哈希表实现的，哈希表对数据并不排序，因此不适合做区间查找，效率非常低，需要搜索整个哈希表结构。

常用索引介绍

MySQL两种主流的数据库存储引擎MyISAM和innoDB

MyISAM存储引擎—主键索引

MyISAM引擎使用B+树作为索引结构、叶节点的data域存放的是数据记录地址
下图是MyISAM主键索引的原理图
在这里插入图片描述
MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM存储引擎-辅助索引

如果给其他字段创建辅助索引，结构图如下：
在这里插入图片描述
根据上面两张图，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，
则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

可以看出：MYISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据没有在一起存放，体现在磁盘上就是索引在一个文件存储，数据在另一个文件存储。
列如一个user表，会在磁盘上存储三个文件：user.frm(表结构文件) user.MYD(表的数据文件) user.MYI(表的索引文件)
MYISAM的索引方式也叫做非聚集索引，之所以这么称呼为了与InNoDB的聚集索引区分

INNODB存储引擎-主键索引

INNODB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，如图：
在这里插入图片描述
可以看到，索引关键字和数据一起存储在叶子节点上

INNODB存储引擎-辅助索引

INNODB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，如图：
在这里插入图片描述
辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。

从索引树上可以看到，INNODB的索引关键字和数据都是在一起存放的，体现在磁盘存储上。
例如创建一个user表，在磁盘上只存储两种结构，user.frm(存储表的结构),user.idb（存储索引和数据）
INNODB的索引树叶子节点包含了完整的数据记录，这种索引叫做聚集索引。因为INNODB的数据文件本身要按照主键聚集，
所以INNODB要求表必须有主键（MYISAM可以没有），如果没有显示指定，则MYISAM系统会自动选择一个可以唯一标识数据记录的列作为主键，
如果不存在这种列，则MYSQL自动为INNODB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型

左前缀原则

explain select * from emp where comm = 300;

liangcheng0213

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引介绍

文章目录索引索引是什么？索引分类：索引SQL1、创建表时可以创建索引2、在已有表上添加索引索引索引是什么？索引是一种数据结构，索引是创建在表上，是对数据库表中的一列或者多列的值进行排序的一种结果，索引能提高查询效率，避免全表查询索引分类：1.普通索引：没有任何限制条件，可以给表中的任何的字段创建索引2.唯一性索引：使用unique修饰的字段，值是不能重复的，主键索引就属于唯一性索引3.主键索引：使用primark key修饰的字段会自动添加主键索引4.全文索引：使用fulltext修..
复制链接

扫一扫