【SQL应知应会】索引(一)• MySQL版

【SQL应知应会】索引(一)• MySQL版

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

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


前言

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

一、索引

1.简介

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

1.1 索引的优点

  • 索引大大减小了服务器需要扫描的数据量,从而大大加快数据的检索速度,这也是创建索引的最主要的原因。

  • 索引可以帮助服务器避免排序和创建临时表

  • 索引可以将随机IO变成顺序IO

  • 索引对于InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组,提高了表访问并发性

  • 关于InnoDB、索引和锁:InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

  • 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

  • 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

  • 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

1.2 索引的缺点

  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加

  • 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间,如果需要建立聚簇索引,那么需要占用的空间会更大

  • 对表中的数据进行增、删、改的时候,索引也要动态的维护,这就降低了整数的维护速度

  • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

  • 对于非常小的表,大部分情况下简单的全表扫描更高效

2. 索引类型之逻辑分类

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

  • 单列索引是最基本的索引,没有任何的限制

  • 方法1:直接创建索引

CREATE INDEX index_name ON table_name(col_name);
  • 方法2:修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name(col_name);
  • 方法3:创建表的时候同时创建索引
CREATE TABLE news (
    id int(11) NOT NULL AUTO_INCREMENT ,
    title varchar(255)  NOT NULL ,
    content varchar(255)  NULL ,
    time varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (id),  -- 默认使用B树索引
    INDEX index_name (title(255)) -- 默认使用B树索引
)

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

  • 可以通过show create table news 查看
CREATE TABLE `news` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` varchar(255) DEFAULT NULL,
  `time` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
  • 也可以通过转储SQL文件进行查看
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for news
-- ----------------------------
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `time` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_name`(`title`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

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

  • 复合索引是在多个字段上创建的索引
  • 复合索引遵守**“最左前缀”原则**,即在查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。
  • 方法1:创建一个复合索引
create index index_name on table_name(col_name1,col_name2,...);
  • 方法2:按照修改表结构的方式添加索引
alter table table_name add index index_name(col_name,col_name2,...);
  • 方法3:直接使用工具(此处是Navicat)进行添加

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

2.4.1 直接使用select *查询前面添加索引的表

  • 发现并没有使用到索引 ,而是全表扫描
explain
select * from emp

2.4.2 查询具体的字段

  • 使用到前面添加的复合索引,但是还是要扫描整个索引树
explain
select empno from emp

2.4.3 遵循最左前缀原则,对复合索引中的索引字段按照顺序进行查询

  • 我们发现查询都使用到了索引
explain
select * from emp where empno = '7499'
explain
select * from emp where empno = '7499' and ename = 'ALLEN'
explain
select * from emp where empno = '7499' and ename = 'ALLEN' and deptno = '30'

2.4.4 遵循最左前缀原则,对复合索引中的索引字段不按照顺序进行查询

  • 发现使用复合索引的第一个字段,与在SQL语句中的顺序无关
explain
select * from emp where ename = 'ALLEN' and deptno = '30' and empno = '7499' 

2.4.5 遵循最左前缀原则,对复合索引中的索引字段进行查询

  • 发现并没有使用到索引
explain
select * from emp where ename = 'ALLEN' and deptno = '30'

2.4.6 in & not in

  • where in 也会用到索引
explain
select * from emp where empno in (7499,7521)
  • not in 没有in的效率高
explain
select * from emp where empno not in (7499,7521)

小结

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

转载请说明出处内容投诉
CSS教程_站长资源网 » 【SQL应知应会】索引(一)• MySQL版

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买