【SQL应知应会】索引（一）• MySQL版

欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享，与更多的人进行学习交流

本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习，有基础也有进阶，有MySQL也有Oracle

索引 • MySQL版

前言

✅今天开始

SQL的索引

的篇章，同样我们会讲到MySQL和Oracle的索引，大家拭目以待吧
✳️今天这篇主要简单介绍了索引的概念和优缺点，同时直接进入主题——如何创建索引，同时我们会针对索引进行一些分类，而今天讲的是按照逻辑分类后的普通索引(单列索引)以及复合索引，分别讲了索引的创建和应用
✴️文章中提供了代码和很具体的截图，希望大家跟着一起学起来
希望文章的内容对大家有所帮助，如果有什么不足的地方，大家可以在评论区或者私信我，感谢大家的支持
💻那么，快拿出你的电脑，跟着文章一起学习起来吧

一、索引

1.简介

• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

1.1 索引的优点

• 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 索引可以帮助服务器避免排序和创建临时表
• 索引可以将随机IO变成顺序IO
• 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
• 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1.2 索引的缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
• 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 对于非常小的表，大部分情况下简单的全表扫描更高效

2.创建

2.1 索引类型之逻辑分类

2.1.1普通索引(单列索引) 的创建

• 单列索引是最基本的索引,没有任何的限制
• 方法1:直接创建索引

CREATEINDEX index_name ON table_name(col_name);

• 方法2：修改表结构的方式添加索引

ALTERTABLE table_name ADDINDEX index_name(col_name);

• 方法3:创建表的时候同时创建索引

CREATETABLE news (
    id int(11)NOTNULLAUTO_INCREMENT,
    title varchar(255)NOTNULL,
    content varchar(255)NULL,timevarchar(20)NULLDEFAULTNULL,PRIMARYKEY(id),-- 默认使用B树索引INDEX index_name (title(255))-- 默认使用B树索引)

2.1.2普通索引(单列索引) 的查看

• 可以通过show create table news 查看

CREATETABLE`news`(`id`intNOTNULLAUTO_INCREMENT,`title`varchar(255)NOTNULL,`content`varchar(255)DEFAULTNULL,`time`varchar(20)DEFAULTNULL,PRIMARYKEY(`id`),KEY`index_name`(`title`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb3

• 也可以通过转储SQL文件进行查看

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS =0;-- ------------------------------ Table structure for news-- ----------------------------DROPTABLEIFEXISTS`news`;CREATETABLE`news`(`id`int(0)NOTNULLAUTO_INCREMENT,`title`varchar(255)CHARACTERSET utf8 COLLATE utf8_general_ci NOTNULL,`content`varchar(255)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,`time`varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE,INDEX`index_name`(`title`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1CHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS =1;

2.1.3 复合索引(组合索引)的创建

• 复合索引是在多个字段上创建的索引
• 复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

• 方法1：创建一个复合索引

createindex index_name on table_name(col_name1,col_name2,...);

• 方法2：按照修改表结构的方式添加索引

altertable table_name addindex index_name(col_name,col_name2,...);

• 方法3：直接使用工具(此处是Navicat)进行添加

2.1.4 复合索引(组合索引)的应用

2.1.4.1 直接使用

select *

查询前面添加索引的表

• 发现并没有使用到索引 ，而是全表扫描

explainselect*from emp

2.1.4.2 查询具体的字段

• 使用到前面添加的复合索引，但是还是要扫描整个索引树

explainselect empno from emp

2.1.4.3 遵循

最左前缀原则

，对复合索引中的索引字段

按照顺序

进行查询

• 我们发现查询都使用到了索引

explainselect*from emp where empno ='7499'

explainselect*from emp where empno ='7499'and ename ='ALLEN'

explainselect*from emp where empno ='7499'and ename ='ALLEN'and deptno ='30'

2.1.4.4 遵循

最左前缀原则

，对复合索引中的索引字段

不按照顺序

进行查询

• 发现使用复合索引的第一个字段，与在SQL语句中的顺序无关

explainselect*from emp where ename ='ALLEN'and deptno ='30'and empno ='7499'

2.1.4.5

不

遵循

最左前缀原则

，对复合索引中的索引字段进行查询

• 发现并没有使用到索引

explainselect*from emp where ename ='ALLEN'and deptno ='30'

2.1.4.6 in & not in

• where in 也会用到索引

explainselect*from emp where empno in(7499,7521)

• not in 没有in的效率高

explainselect*from emp where empno notin(7499,7521)

小结

😘感谢大家耐心的看完这篇文章，这篇文章是

MySQL索引

的第

1

篇文章，我们在MySQL方面写了很多内容了，大家可以去我的专栏SQL应知应会 进行学习，如果大家觉着还算可以，那么就给个三连支持一下吧
🏡也可以加入我的社区一起学习呀
✅如果想要继续关注和学习后续更多的内容，那就关注一下爱书不爱输的程序猿吧，当然，如果大家还有什么其他方面的知识点想要看，可以在评论区或者私信我