SQL中的索引知识点复习文档

一个叫卡卡的球员

于 2024-08-19 00:03:33 发布

阅读量619

点赞数 11

分类专栏：数据库文章标签：笔记数据库 sql

本文链接：https://blog.csdn.net/qq_45008212/article/details/141306359

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQL中的索引知识点复习文档

此文档是数据分析课程入门篇关于索引知识点的复习文档，本次课程目标是回顾索引的分类、特点及常用操作，此课程代码练习较少，主要为理论知识的快速复习

复习时间：2024年8月18日

文档总结：2024年8月18日

学习时长：视频课程1小时+文档总结1小时

课程环境：

win10系统
mysql community 8.0.23.0
Navicat Premium 12.0.29

本次课程主要涉及的知识点：

SQL中索引的基本概念
SQL中索引的特点（优缺点）
SQL中索引的适用场景
SQL中索引的类型
SQL中索引失效的原因
SQL中索引的常用操作
其他关于索引的补充信息

本次课程复习总结：

索引的基本概念

1、索引是什么

索引是存储引擎用于提高数据库表的访问速度的一种数据结构，使用索引可以快速访问数据库表中的特定信息。

索引是一种特殊的文件，它们包含着对数据表里所有记录的位置信息。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

MySQL 索引的建立对于MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

2、索引有什么作用

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

3、索引的特点

总的来说，索引可以增强数据的规范性，加快数据表连接与数据查询的效率；缺点是：维护索引会人力成本，占用物理空间，降低数据增删改的效率

索引的优点：

通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；
可以加快数据的检索速度；
可以加速表与表之间的连接；
在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

索引的缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
索引需要占用物理空间，数据量越大，占用空间越大；

数据库中，根据某一列的数据创建索引的方式是，把这一列的数据的值取出来，然后放到一个特定的表里面，如果数据表中的数据增加了，那么索引表的数据也会增加很多，以此类推，数据量越大，索引表的数据量也随之增加，索引数据占用的物理空间也越大
会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

因为每次增删改，索引都需要动态维护，比如：如果索引列的值经常发生改变，比如从原本的A变成B，B再变成C，由于这个建立了索引的列已经放到索引表里了，主表的数据发生改变，索引表的数据也必须进行动态维护，否则就会出现数据不匹配的情况

如果数据表的数据频繁变化，索引表的数据也需要频繁进行动态维护，从而导致效率的降低；因此基于效率方面的考虑，频繁更新的字段是不适合创建索引的

4、索引的适用场景

适合使用索引的场景：

总的来说：频繁出现在where、聚合、order by、join on子句后面的字段适合使用索引

频繁作为查询条件的字段应该创建索引；
查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；

数据表中创建索引的列，放到索引表的过程中其实已经进行了一次排序，后面的索引就是根据一定的算法去快速查找到索引数据，再根据索引数据快速定位到主表数据的过程，所以，在对列进行排序时，就可以直接去索引表里找到已经经过排序的值，自然排序的速度就会很高

就像新华字典的汉字的目录也是根据26个字母的顺序进行了排序，在找一个Z字母开头的字的时候我们已经知道了这个在目录中的大概位置是在最后的
查询中统计或者分组的字段；

不适合用索引的场景：

频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；
where条件里用不到的字段，不创建索引；
表记录太少，不需要创建索引（一般来说几千条数据都数据数据量少的情况，这种数据量级数据库处理效率是很高的）；
参与列计算的，不创建索引；
经常增删改的表（因为每次增删改索引都需要进行动态维护）
区分度不高的字段不适合建立索引，例如性别字段，只有男女（数据包含大量重复数据，建立索引就没有太大的效果）；

5、索引失效的原因

对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
判断索引列是否不等于某个值时
对索引列进行运算
查询条件使用or连接，也会导致索引失效

6、索引的类型

索引的基本类型一般分为以下4种，具体的索引特性，需要结合索引的操作进行巩固

索引的类型	解释说明
普通索引	最基本的索引，它没有任何限制。
唯一索引	索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
主键索引	特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。
联合索引	组合索引，在多个字段上建立索引，能够加速查询到速度

7、索引的常用操作

以之前DML练习中创建的数据为例，设定用于测试各种索引操作方法的数据表结构如下：

create table stuinfo(
	 sid      int  				auto_increment
	,primary  key(sid)
	,sname    varchar(4)
	,sgender  char(1)
	,sbrithday date
	,clsid int
	-- 设置外键约束： constraint 约束名 foreign key(列名) references 参照的表名(参照的列名)
	,constraint fk_cls foreign key(clsid) references classes(clsid) 
	)

sid	snane	sex	sbirthday	clsid
1	张三丰	男	1990/01/01	1
2	张无忌	男	1992/12/12	1
3	周芷若	女	1992/10/10	2
4	赵敏	女	1992/7/7	2
5	蛛儿	女	1994/6/6	3
6	韦一笑	男	1972/8/19	3

普通索引的查看、创建与删除

查看表中有哪些索引
查看索引的语法：
```
show index from 表名;
```
本题案例：
```
-- 查看表中有哪些索引
show index from stuinfo;
```
返回结果（主要关注的几个列）

Table Key_name Column_name Index_type
stuinfo PRIMARY sid BTREE
stuinfo fk_cls clsid BTREE

Table	Key_name	Column_name	Index_type
stuinfo	PRIMARY	sid	BTREE
stuinfo	fk_cls	clsid	BTREE

创建新的索引

方法一：通过create index语句直接创建索引

创建索引的语法：
create index 索引名称 on table(列名(索引长度));
本题案例：
-- 创建索引方法一：通过create index创建索引
create index stu_index on stuinfo(sname);
返回结果：
create index stu_index on stuinfo(sname);
> OK
> 时间: 0.021s
再次通过show index查看学生信息表的索引，此时返回的索引有3个，分别是

PRIMARY
fk_cls
stu_index**（新增的索引）**

说明索引创建成功了

方法二：通过修改表添加索引创建索引

添加索引的语法：
alter table 表名 add index 索引名称 (列名(索引长度));
本题案例：
-- 创建索引方法二：通过alter table添加新索引
alter table stuinfo add index stuinfo2 (sname(4));
返回结果：
alter table stuinfo add index stuinfo2 (sname(4))
> OK
> 时间: 0.033s
再次通过show index查看学生信息表的索引，此时返回的索引有4个，分别是

PRIMARY
fk_cls
stu_index**（新增的索引）**
stuinfo2**（新增的索引2）**

说明索引创建成功了

方法三：在创建表时就设置指定索引列

创建表时设置索引的语法：通过index实现

create table 表名 (
	字段1 ,
	primary key (主键索引名),
	index 索引名 (列名(length))
);

本题案例：

-- 方法三：在创建表时就设置好指定索引列
create table stuinfo2(
	 sid      int  				auto_increment
	,primary  key(sid)
	,sname    varchar(4)
	,sgender  char(1)
	,sbrithday date
	,clsid int
	-- 设置索引： constraint 约束名 foreign key(列名) references 参照的表名(参照的列名)
	,index stu_index (sname(4))
	);

返回结果：

create table stuinfo2(
	 sid      int  				auto_increment
	,primary  key(sid)
	,sname    varchar(4)
	,sgender  char(1)
	,sbrithday date
	,clsid int
	-- 设置索引： constraint 约束名 foreign key(列名) references 参照的表名(参照的列名)
	,index stu_index (sname(4))
	)
> OK
> 时间: 0.026s

再次通过show index查看学生信息表的索引，此时返回的索引有2个，分别是

PRIMARY
stu_index（在创建表时设置的索引）

说明索引创建成功了

删除普通索引
删除普通索引的语法：
```
drop index 索引名称 on 表名;
```
本题案例：
```
-- 删除指定的索引
drop index stuinfo2 on stuinfo;
```
返回结果：
```
drop index stuinfo2 on stuinfo;
> OK
> 时间: 0.009s
```
再次通过show index查看学生信息表的索引，此时返回的索引有3个，分别是
1. PRIMARY
2. fk_cls
3. stu_index
  
  stuinfo2被删除了
说明索引删除成功了

唯一索引的创建

唯一索引和普通索引是非常类似的，不同的就是：索引列的值必须唯一，但允许有空值。

创建唯一索引的sql语法和普通索引是类似的，只是把普通索引的index改成unique index / unique，其他都一样

-- 创建索引方法一：通过create index创建索引
create unique index 索引名称 on table(列名(索引长度));

-- 创建索引方法二：通过alter table添加新索引(注意这里直接写unique不是unique index)
alter table 表名 add unique 索引名称 (列名(索引长度));

-- 创建方法三：在创建表时就设置指定索引列(注意这里直接写unique不是unique index)
create table 表名 (
	字段1 ,
	primary key (主键索引名),
	unique 索引名 (列名(length))
);

主键索引的创建与删除

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

一般是在建表的时候同时创建主键索引。

创建主键索引的sql语法和普通索引是类似的，主键是：primary key

-- 修改表添加主键索引
alter table  表名 add primary key(列名);

-- 创建表时指定主键索引
create table `table` (
column type ,
primary key(column)
);

组合索引的最左前缀原则与创建

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则，就是最左优先)。

创建组合索引和创建普通索引的sql语法类似

-- 创建组合索引方法一：通过alter table添加组合索引
alter table 表名 add index 索引名称 (字段1(长度),字段2(长度));
-- 创建组合索引方法二：通过alter table添加组合索引
create table 表名 (
字段1,
index 索引名称 (字段1(长度),字段2(长度))
);

补充信息

1、关于show index查询索引语句返回结果的补充说明

show index语句返回的结果包含多个列，每列都提供了关于索引的特定信息。

以下是结果中常见列的含义（此处以主键索引为例，说明返回结果的含义，仅做了解，不深入研究）

返回字段	中文释义	返回结果	解释说明
Table	表名	stuinfo
Non_unique	索引是否包含重复项	0	如果索引不能包含重复项，则为0；如果可以包含（如非唯一索引），则为1。注意，即使列上有唯一约束，但如果索引还包含其他列，则该索引仍可能被视为非唯一的（Non_unique=1）。
Key_name	索引的名称	PRIMARY	返回的这一列设置了主键索引
Seq_in_index		1	表示索引中每个列的位置。在复合索引中，这有助于理解索引是如何构建的。
Column_name	索引中包含的列名	sid	对于复合索引，这里将列出索引中的所有列
Collation	列的排序方式	A	在MySQL中，这通常是A（升序）
Cardinality	索引中唯一值的估计数量	6	只是一个估计值，并且可能不总是准确的
Sub_part	前缀索引		如果索引只是列的一部分（即前缀索引），则显示该列被索引的字符数。如果索引是整列，则为NULL。
Packed	相关于索引是如何在内部存储的		对于大多数用户来说，这个值通常不重要，并且对于InnoDB表，它通常总是NULL。
Null	索引是否可以包含NULL		如果列可以包含NULL，并且索引明确包括NULL值，则为’YES’。否则，如果索引不包含NULL值，则为’'（空字符串）。注意，即使列允许NULL，索引也可能不包含NULL值，这取决于索引创建时的选项。
Index_type	索引的类型	BTREE	通常是BTREE，表示索引是使用B±Tree数据结构实现的
Comment	索引的注释
Index_comment	索引的注释
Visible	表示索引是否对优化器可见	YES	在MySQL 8.0中引入了不可见索引的概念，允许你创建索引但不立即用于查询优化。如果索引是可见的，则为’YES’；如果索引是不可见的，则为’NO’。
Expression	不清楚		在MySQL 8.0和Navicat Premium 12.0.29的标准环境中，`SHOW INDEX FROM table_name;`语句的返回结果中不应该包含`Expression`列。如果看到了这个列，那么它很可能是由Navicat的自定义视图或报告添加的，或者是由于误解或混淆造成的。