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

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、索引的特点

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

索引的优点：

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

索引的缺点：

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；

2. 索引需要占用物理空间，数据量越大，占用空间越大；

   数据库中，根据某一列的数据创建索引的方式是，把这一列的数据的值取出来，然后放到一个特定的表里面，如果数据表中的数据增加了，那么索引表的数据也会增加很多，以此类推，数据量越大，索引表的数据量也随之增加，索引数据占用的物理空间也越大

3. 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

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

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

4、索引的适用场景

适合使用索引的场景：

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

1. 频繁作为查询条件的字段应该创建索引；

2. 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；

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

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

3. 查询中统计或者分组的字段；

不适合用索引的场景：

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

5、索引失效的原因

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

6、索引的类型

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

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) 
	)

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

1. 查看表中有哪些索引

   查看索引的语法：

   show index from 表名;
   

   本题案例：

   -- 查看表中有哪些索引
   show index from stuinfo;
   

   返回结果（主要关注的几个列）

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

2. 创建新的索引

   方法一：通过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个，分别是

   1. PRIMARY
   2. fk_cls
   3. 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个，分别是

   1. PRIMARY
   2. fk_cls
   3. stu_index**（新增的索引）**
   4. 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个，分别是

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

   说明索引创建成功了

3. 删除普通索引

   删除普通索引的语法：

   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语句返回的结果包含多个列，每列都提供了关于索引的特定信息。

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

2、关于create index和alter index语句中length参数的补充说明

在创建索引时，可以指定索引长度。

length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length

如果指定单列索引长度，length 必须小于这个字段所允许的最大字符个数。

3、关于组合索引中最左前缀原则补充说明

比如：使用表中的 name ，address ，salary 创建组合索引，那么想要组合索引生效， 我们只能使用如下组合：

1. name/address/salary
2. name/address
3. name/

如果使用 addrees/salary 或者是 salary ，那么索引不会生效。