mysql索引详解

最新推荐文章于 2024-11-06 10:59:12 发布

万wu皆可爱

最新推荐文章于 2024-11-06 10:59:12 发布

阅读量1.1k

点赞数

分类专栏： MySQL从入门到精通文章标签： mysql b树数据库

本文链接：https://blog.csdn.net/m0_46090675/article/details/121308170

版权

MySQL从入门到精通专栏收录该内容

14 篇文章 5 订阅

订阅专栏

本文详细介绍了数据库索引的基础知识，包括索引的作用、类型、数据结构如B+树，并探讨了索引的优缺点及优化策略。强调了B+树在InnoDB存储引擎中的应用，解释了为何选择B+树而非其他数据结构，并提供了创建和删除索引的方法。

摘要由CSDN通过智能技术生成

一、索引简介

1.1 什么是索引

索引是存储引擎中一种数据结构，或者说数据的组织方式，又称之为键key，是存储引擎用于快速找到记录的一种数据结构。
为数据建立索引就好比是为书建目录，或者说是为字典创建音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

1.2 为什么要是用索引

实现数据库快速查询，提高查询速度

1.3 索引分类

a.普通索引
  最基本的索引，对字段数据的类型和值没有任何限制，数据类型可以任意，字段的值可以空也可以重复
b.主键索引
  给主键字段添加的索引
  主键特点：非空且唯一
c.唯一索引
  给唯一字段添加的索引
  和主键索引的区别：只有唯一，可以有空值，主键索引，唯一且非空
d.全文索引
  适用于一大串文本添加的索引，只可以给字符串数据类型添加（char varchar text）
e.空间索引
  给字段的数据类型必须是空间数据类型且该字段的值必须是非空 not null
  空间数据类型：geometry point linestring polygon
f.复合索引
  给多个字段添加的索引
  注意：如果添加了复合索引，查询条件只有使用了第一个字段，该索引才会被触发

1.4 索引的数据结构

B+树索引（等值查询与范围查询都快）
	二叉树->平衡二叉树->B树->B+树
	        
HASH索引(等值查询快，范围查询慢)
	将数据打散再去查询
	
FULLTEXT：全文索引 （只可以用在MyISAM引擎）
	通过关键字的匹配来进行查询，类似于like的模糊匹配
	like + %在文本比较少时是合适的
	但是对于大量的文本数据检索会非常的慢
	全文索引在大量的数据面前能比like快得多，但是准确度很低
	百度在搜索文章的时候使用的就是全文索引，但更有可能是ES

不同的存储引擎支持的索引类型也不一样

InnoDB存储引擎
支持事务，支持行级别锁定，支持 B+tree（默认）、Full-text 等索引，不支持 Hash 索引；

MyISAM存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

1.3.1 hash索引

Hash 索引是比较常见的一种索引，他的单条记录查询的效率很高，时间复杂度为1。但是，Hash索引并不是最常用的数据库索引类型，尤其是我们常用的Mysql Innodb引擎就是不支持hash索引的。主要有以下原因：
Hash索引适合精确查找，但是范围查找不适合

因为存储引擎都会为每一行计算一个hash码，hash码都是比较小的，并且不同键值行的hash码通常是不一样的，hash索引中存储的就是Hash码，hash 码彼此之间是没有规律的，且 Hash 操作并不能保证顺序性，所以值相近的两个数据，Hash值相差很远，被分到不同的桶中。这就是为什么hash索引只能进行全职匹配的查询，因为只有这样，hash码才能够匹配到数据。

1.3.2 二叉树时间复杂度为 O(n)

在这里插入图片描述

1、一个节点只能有两个子节点。即度不超过2
2、左子节点小于本节点，右子节点大于本节点

如果我们需要查找id=12的用户信息
select * from user where id=12;
利用我们创建的二叉查找树索引，查找流程如下：

1、将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，
接下来我们把当前节点>的右子节点作为当前节点。
2、继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
3、把12和当前节点的键值12对比，12等于12，满足条件，
我们从当前节点中取出data，即id=1>2,name=xm。

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到

1.3.3 平衡二叉树

任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
所以，依据二叉查找树的特点，二叉树可以是这样构造的
在这里插入图片描述
这个时候可以看到二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。

平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。下面是平衡二叉树和非平衡二叉树的对比：
在这里插入图片描述
平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

那么直接用平衡二叉树这种数据结构来构建索引有什么问题？
1、首先，因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。

2、另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

3、所以，如果我们单纯用平衡二叉树这种数据结构作为索引的数据结构，即每个磁盘块只放一个节点，每个节点中只存放一组键值对，此时如果数据量过大，二叉树的节点则会非常多，树的高度也随即变高，我们查找数据的也会进行很多次磁盘IO，查找数据的效率也会变得极低！
在这里插入图片描述
综上，如果我们能够在平衡二叉的树的基础上，把更多的节点放入一个磁盘块中，那么平衡二叉树的弊端也就解决了。即构建一个单节点可以存储多个键值对的平衡树，这就是B树。

1.3.4 B tree

在这里插入图片描述
注意：
1、图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。
2、图中的每个节点里面放入了多组键值对，一个节点也称为一页，一页即一个磁盘块，在mysql中数据读取的基本单位都是页，即一次io读取一个页的数据，所以我们这里叫做页更符合mysql中索引的底层数据结构。
3、B树也是平衡的，当增加或删除数据而导致B树不平衡时，也是需要进行节点调整的。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：

1、先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
2、将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。
3、将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

那么B树是否就是索引的最终结构了呢？答案是no，B树只擅长做等值查询，而对于范围查询（范围查询的本质就是n次等值查询），或者说排序操作，B树也帮不了我们

select * from user where id=3;  -- 擅长
select * from user where id>3;  -- 不擅长

1.3.5 B+tree

B+树是对B树的进一步优化
在这里插入图片描述
1、B+树非叶子节点non-leaf node上是不存储数据的，仅存储键，而B树的非叶子节点中不仅存储键，也会存储数据。B+树之所以这么做的意义在于：树一个节点就是一个页，而数据库中页的大小是固定的，innodb存储引擎默认一页为16KB，所以在页大小固定的前提下，能往一个页中放入更多的节点，相应的树的阶数（节点的子节点树）就会更大，那么树的高度必然更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2、B+树的阶数是等于键的数量的，例如上图，我们的B+树中每个节点可以存储3个键，3层B+树存可以存储3*3=9个数据。所以如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000=1千万个数据。而一般根节点是常驻内存的，所以一般我们查找1千万数据，只需要2次磁盘IO

3、因为B+树索引的所有数据均存储在叶子节点leaf node，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。而且B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引

通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

1.3.6 聚集索引

在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。这里我们主要介绍innodb存储引擎中的聚集索引和非聚集索引。

1、聚集索引（又称聚簇索引、主键索引，一张表必须有且只有一个）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键用的就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。

2、非聚集索引（又称非聚簇索引、辅助索引，一张表可以创建多个辅助索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

InnoDB中一颗的B+树可以存放多少行数据？

假设定义一颗B+树高度为2，即一个根节点和若干叶子节点。那么这棵B+树的存放总行记录数=根节点指针数*
单个叶子记录的行数。这里先计算叶子节点，B+树中的单个叶子节点的大小为16K，假设每一条目为1K，那么
记录数即为16(16k/1K=16)，然后计算非叶子节点能够存放多少个指针，假设主键ID为bigint类型，那么长
度为8字节，而指针大小在InnoDB中是设置为6个字节，这样加起来一共是14个字节。那么通过页大小/(主键
ID大小+指针大小），即16384/14=1170个指针，所以一颗高度为2的B+树能存放16*1170=18720条这样的
记录。根据这个原理就可以算出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录。所以在
InnoDB中B+树高度一般为2-3层，它就能满足千万级的数据存储

1.4 优点

索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O

1.5 缺点

占用额外空间，影响插入速度

为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？

回答：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；而且b+tree各个叶子节点都是用双向链表连接起来的，方便范围查找。

1.6 索引优化

1.独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的
参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数
操作和表达式操作）

2.左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的
记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
3.多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
4.选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
5.只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不
会使用索引
6.对于经常在where子句使用的列，最好设置索引
7.对于有多个列where或者order by子句，应该建立复合索引
8.对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
9.尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
10.不要使用RLIKE正则表达式会导致索引失效
11.查询时，能不要*就不用*，尽量写全字段名，比如:select id,name,age from students;
12.大部分情况连接效率远大于子查询
13.在有大量记录的表分页时使用limit
14.对于经常使用的查询，可以开启查询缓存
15.多使用explain和profile分析查询语句
16.查看慢查询日志，找出执行时间长的sql语句优化

二、创建索引

2.1 自动创建索引

如果创建表时，给表添加了主键约束和唯一约束，mysql数据库会自动的为主键约束和唯一约束
创建对应的主键索引和唯一索引
查看索引
语法：show index from 表名


系统默认会创建主键索引和唯一索引
create table stu (
id int primary key,
name varchar(20),
emil varchar(20) unique
)


mysql> show index from stu;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| stu   |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| stu   |          0 | emil     |            1 | emil        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2.2 手动创建索引

2.2.1 创建表时创建索普通索引

语法：

create table 表名(
	字段名1 字段类型1，
	字段名2 字段类型2
	.........,
	[index|key] [索引名][索引类型](字段名[长度][asc|desc] 升序将序可有可无)	   
	)



create table stu1 (
id int,
name varchar(20),
emil varchar(20),
index (emil)
)

# 普通索引Non_unique值为1
mysql> show index from stu1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| stu1  |          1 | emil     |            1 | emil        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

2.唯一索引的创建
1.创建表时创建唯一索引
语法：

create table 表名(
	字段名1 字段类型1，
	字段名2 字段类型2
	.........,
	unique [index|key] [索引名][索引类型](字段名[长度][asc|desc] 升序将序可有可无)	   
	)



create table stu2 (
id int,
name varchar(20),
emil varchar(20),
unique (emil)
)

# 唯一索引Non_unique为0
mysql> show index from stu2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| stu2  |          0 | emil     |            1 | emil        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

3.主键索引的创建
语法：

create table 表名(
	字段名1 字段类型1，
	字段名2 字段类型2
	.........,
	primary key [index|key] [索引名][索引类型](字段名[长度][asc|desc] 升序将序可有可无)	   
	)

# 主键索引Non_unique为0且不允许为空

4.全文索引的创建
注意：只能给字符串类型添加
语法：

create table 表名(
	字段名1 字段类型1，
	字段名2 字段类型2
	.........,
	fulltext [index|key] [索引名][索引类型](字段名[长度][asc|desc] 升序将序可有可无)	    
	)

5.空间索引的创建
注意：只能给空间类型数据添加，且该字段的数值不能为空
语法：

create table 表名(
	     字段名1 字段类型1，
	     字段名2 字段类型2
	     .........,
	     spatial [index|key] [索引名][索引类型](字段名[长度][asc|desc] 升序将序可有可无)	   
	     )

6.复合索引的创建
在多个字段上面添加索引
语法：

 create table 表名(
 字段名1 字段类型1，
 字段名2 字段类型2
 .........,
 index|key [索引名][索引类型](字段名1[长度][asc|desc] 升序将序可有可无，
 字段名2[长度][asc|desc] 升序将序可有可无，.....)	   
 
 )

2.2.2 创建表后使用’create index’创建索引

语法：

   create [unique|fulltext|spatical] index 索引名称 [索引类型]
            on 表名(字段名1[(长度)][asc|desc],字段名1[(长度)][asc|desc].....)

   注意：主键索引不能通过 create index 方式创建
   1.创建普通索引
     create index 索引名 on 表名()
   2.创建唯一索引
     create unique index 索引名 on 表名()
   3.创建全文索引
     create fulltext index 索引名 on 表名()
   4.创建空间索引
     create spatial index 索引名 on 表名()
   5.创建复合索引

3.给已有表添加索引’alter table’
1.创建普通索引

 语法：alter table 表名 add [primary key|unique|fulltext|spatical]index|key [索引名]
 [索引类型] (字段名[长度][asc|desc])

2.3 删除索引

alter table删除
语法：alter table 表名 drop index|key 索引名
2.drop index删除
语法：drop index 索引名 on 表名
注意：使用 alter table 表名 drop primary key 方式删除主键索引
或使用 drop index primary key