MySQL索引教程(01):创建索引

文章目录

  • MySQL 创建索引
  • 索引介绍
  • MySQL CREATE INDEX 语法
  • MySQL 索引类型
  • MySQL `CREATE INDEX` 实例
  • 结论

MySQL 创建索引

对于一个具有大量数据行的表,如果你根据某个查询条件检索数据时很慢,可能是因为你没有在检索条件相关的列上创建索引。

索引类似于词典中的目录。如果您想要在词典中查询一个词,正确的做法是先查看目录,再根据目录中的指示到指定的页面找到相关的词。正确的索引可以显著提高从数据库表中检索数据行的速度。

MySQL 允许您使用 CREATE INDEX 语句在指定的表上为指定的列创建索引。

索引介绍

索引是一种数据结构,例如 B-Tree,它提高了从表中检索数据行的速度,但需要额外的写入和存储来维护它。

查询优化器可以使用索引来快速定位数据,而不必针对给定查询扫描表中的每一行。

当您使用主键 或唯一键创建表时,MySQL 会自动创建一个名为 PRIMARY 的索引。 该索引称为聚集索引。

PRIMARY 索引是特殊的,因为索引本身与数据一起存储在同一个表中。聚集索引强制执行表中行的顺序。

PRIMARY 索引以外的其他索引称为二级索引或非聚集索引。

MySQL CREATE INDEX 语法

您应该按照如下的语法使用 CREATE INDEX 为一个表添加一个索引:

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

这里,

  • UNIQUE 关键字表明此索引为唯一索引。它是可选的。
  • index_name 是索引的名字。一个表中不应该出现两个相同名字的索引。
  • table_name 是表的名字。
  • column_list 是表中的列名。多个列名使用逗号分隔。
  • USING 子句指定索引的类型。可选值:BTREE,HASH。 它是可选的。
  • algorithm_option 指定删除索引的算法。它使用以下的语法:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}

ALGORITHM 子句是可选的。默认为 INSTANT。如果不支持 INSTANT,则使用 INPLACE。

使用 DEFAULT 和省略 ALGORITHM 子句效果相同。

以下是对各个算法的说明:

  • COPY:对原表的副本进行操作,将原表中的表数据逐行复制到新表中。
  • 不允许并发 DML。
  • INPLACE: 操作避免复制表数据,但可能会就地重建表。在操作的准备和执行阶段,可能会短暂地对表进行独占元数据锁定。通常,支持并发 DML。
  • INSTANT: 操作只修改数据字典中的元数据。在操作的执行阶段,可能会短暂地对表进行独占元数据锁定。表数据不受影响,使操作瞬间完成。允许并发 DML。(在 MySQL 8.0.12 中引入)

lock_option 指定删除索引的并发控制策略。它使用以下的语法:

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

LOCK 子句是可选的。以下是对各个并发策略的说明:

DEFAULT
给定 ALGORITHM 子句(如果有)和 ALTER TABLE 操作的最大并发级别:如果支持,则允许并发读取和写入。如果不是,则允许并发读取(如果支持)。如果不是,则强制执行独占访问。
NONE
如果支持,允许并发读取和写入。否则,会发生错误。
SHARED
如果支持,允许并发读取但阻止写入。即使存储引擎支持给定 ALGORITHM 子句(如果有)和 ALTER TABLE 操作的并发写入,写入也会被阻止。如果不支持并发读取,则会发生错误。
EXCLUSIVE
强制执行独占访问。即使存储引擎支持给定 ALGORITHM 子句(如果有)和 ALTER TABLE 操作的并发读/写,也会这样做。
在 MySQL 内部,CREATE INDEX 语句被映射为 ALTER TABLE … ADD INDEX … 语句。

MySQL 索引类型

默认情况下,如果您不指定索引类型,MySQL 将创建 B-Tree 索引。下面显示了基于表的存储引擎允许的索引类型:
在这里插入图片描述

MySQL CREATE INDEX 实例

在以下实例中,我们使用students表进行演示。

创建students表,并添加数据

CREATE TABLE `students`  (
  `student_id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  INDEX `age`(`age` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '安靖', 18);
INSERT INTO `students` VALUES (2, '平安', 19);
INSERT INTO `students` VALUES (3, '总结', 20);
INSERT INTO `students` VALUES (3, '周杰伦', 18);
INSERT INTO `students` VALUES (4, '张三', 18);
INSERT INTO `students` VALUES (5, '李四', 18);
INSERT INTO `students` VALUES (6, '钟意', 19);
INSERT INTO `students` VALUES (7, '张宇', 18);
INSERT INTO `students` VALUES (8, '刘浪', 18);

以下语句查找年龄等于18的学生信息:

SELECT * FROM students WHERE age = 18;

查询结果如下:

在这里插入图片描述

您可以是使用 EXPLAIN 来查看以上 SELECT 语句的语句的执行计划,以了解 MySQL 在内部如何执行此查询,如下所示:

EXPLAIN SELECT * FROM students WHERE age = 18;

在这里插入图片描述
如您所见,MySQL 必须扫描由9行组成的整个表才能找到具有符合条件的行。

现在,使用以下 CREATE INDEX 语句为该列 age 创建索引 :

 CREATE INDEX age ON students(age);

创建结果:
在这里插入图片描述

要查看索引是否创建成功,请使用以下 SHOW INDEXES 语句显示表 actor 的索引,例如:

SHOW INDEXES FROM students;

查询结果

在这里插入图片描述

然后,再次执行上面的 EXPLAIN 语句:

EXPLAIN SELECT * FROM students WHERE age = 18;

查询结果如下:

在这里插入图片描述
如您所见,MySQL 只需从 age 指示的索引中定位其中的 6 行, 而无需扫描整个表。所以会提高查询速度。

结论

在 MySQL 中,索引能提高从表中查询数据的效率。您可以使用 CREATE INDEX 为表创建索引。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/776674.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

平价猫粮新选择!福派斯鲜肉猫粮,让猫咪享受美味大餐!

福派斯鲜肉猫粮,作为一款备受铲屎官们青睐的猫粮品牌,凭借其卓越的品质和高性价比,为众多猫主带来了健康与美味的双重享受。接下来,我们将从多个维度对这款猫粮进行解析,让各位铲屎官更加全面地了解它的魅力所在。 1️…

查看电脑显卡(NVIDIA)应该匹配什么版本的CUDA Toolkit

被串行计算逼到要吐时,决定重拾CUDa了,想想那光速般的处理感觉(夸张了)不要太爽,记下我的闯关记录。正好我的电脑配了NVIDIA独显,GTX1650,有菜可以炒呀,没有英伟达的要绕道了。回到正…

详细分析SQL语句中的硬解析、软解析、软软解析基本知识

目录 前言1. 基本知识2. Demo 前言 从实战中探索 图为全局搜索且在高并发下,会引发硬解析,导致CPU崩溃 1. 基本知识 解析 (parsing) 是数据库在处理 SQL 语句时必不可少的一步,它将 SQL 语句转换为数据库可以执行的低级指令 硬解析 (Hard…

昇思25天学习打卡营第18天|Pix2Pix实现图像转换

Pix2Pix概述 Pix2Pix是基于条件生成对抗网络实现的一种深度学习图像转换模型。Pix2Pix是将cGAN应用于有监督的图像到图像翻译,包括生成器和判别器。 基础原理 cGAN的生成器是将输入图片作为指导信息,由输入图像不断尝试生成用于迷惑判别器的“假”图像…

c++ 附赠课程的知识点记录

(1) 静态变量的赋值 再一个例子: (2) 一般在定义类的赋值运算符函数时, operator ( const A& a ) 函数,应避免自赋值的情况,就是把对象 a 又赋值给 对象a 如同 a a 这样的情况…

类和对象深入理解

目录 static成员概念静态成员变量面试题补充代码1代码2代码3如何访问private中的成员变量 静态成员函数静态成员函数没有this指针 特性 友元友元函数友元类 内部类特性1特性2 匿名对象拷贝对象时的一些编译器优化 感谢各位大佬对我的支持,如果我的文章对你有用,欢迎点击以下链接…

C++ | Leetcode C++题解之第217题存在重复元素

题目&#xff1a; 题解&#xff1a; class Solution { public:bool containsDuplicate(vector<int>& nums) {unordered_set<int> s;for (int x: nums) {if (s.find(x) ! s.end()) {return true;}s.insert(x);}return false;} };

【PB案例学习笔记】-27制作一个控制任务栏显示与隐藏的小程序

写在前面 这是PB案例学习笔记系列文章的第27篇&#xff0c;该系列文章适合具有一定PB基础的读者。 通过一个个由浅入深的编程实战案例学习&#xff0c;提高编程技巧&#xff0c;以保证小伙伴们能应付公司的各种开发需求。 文章中设计到的源码&#xff0c;小凡都上传到了gite…

视频参考帧和重构帧复用

1、 视频编码中的参考帧和重构帧 从下图的编码框架可以看出&#xff0c;每编码一帧需要先使用当前帧CU(n)减去当前帧的参考帧CU&#xff08;n&#xff09;得到残差。同时&#xff0c;需要将当前帧的重构帧CU*&#xff08;n&#xff09;输出&#xff0c;然后再读取重构帧进行预测…

Pandas数据可视化详解:大案例解析(第27天)

系列文章目录 Pandas数据可视化解决不显示中文和负号问题matplotlib数据可视化seaborn数据可视化pyecharts数据可视化优衣库数据分析案例 文章目录 系列文章目录前言1. Pandas数据可视化1.1 案例解析&#xff1a;代码实现 2. 解决不显示中文和负号问题3. matplotlib数据可视化…

HTTP代理服务器:深度解析与应用

“随着互联网的飞速发展&#xff0c;HTTP代理服务器在网络通信中扮演着越来越重要的角色。它们作为客户端和服务器之间的中介&#xff0c;不仅优化了网络性能&#xff0c;还提供了强大的安全性和隐私保护功能。” 一、HTTP代理服务器的概念与作用 HTTP代理服务器是一种能够接…

Qt扫盲-QRect矩形描述类

QRect矩形描述总结 一、概述二、常用函数1. 移动类2. 属性函数3. 判断4. 比较计算 三、渲染三、坐标 一、概述 QRect类使用整数精度在平面中定义一个矩形。在绘图的时候经常使用&#xff0c;作为一个二维的参数描述类。 一个矩形主要有两个重要属性&#xff0c;一个是坐标&am…

前端面试题16(跨域问题)

跨域问题源于浏览器的同源策略&#xff08;Same-origin policy&#xff09;&#xff0c;这一策略限制了来自不同源的“写”操作&#xff08;比如更新、删除数据等&#xff09;&#xff0c;同时也限制了读操作。当一个网页尝试请求与自身来源不同的资源时&#xff0c;浏览器会阻…

设计模式探索:代理模式

1. 什么是代理模式 定义 代理模式是一种结构型设计模式&#xff0c;通过为其他对象提供一种代理以控制对这个对象的访问。代理对象在客户端和实际对象之间起到中介作用&#xff0c;可以在不改变真实对象的情况下增强或控制对真实对象的访问。 目的 代理模式的主要目的是隐…

着急,为啥AI叫好不叫座啊?

关注卢松松&#xff0c;会经常给你分享一些我的经验和观点。 李彦宏在2024世界人工智能大会上说&#xff1a; 没有应用&#xff0c;光有基础模型&#xff0c;不管是开源还是闭源都一文不值&#xff0c;所以我从去年下半年开始讲&#xff0c;大家不要卷模型了&#xff0c;要去…

MySQL---事务管理

1.关于事务 理解和学习事务&#xff0c;不能只站在程序猿的角度来理解事务&#xff0c;而是要站在使用者&#xff08;用户&#xff09;的角度来理解事务。 比如支付宝转账&#xff0c;A转了B100块前&#xff0c;在程序猿的角度来看&#xff0c;是两条update操作&#xff0c;A …

PCDN技术如何提高内容分发效率?(贰)

PCDN技术通过以下方式提高内容分发效率: 1.利用用户设备作为分发节点:与传统的 CDN技术主要依赖中心化服务器不同&#xff0c; PCDN技术利用用户的设备作为内容分发的节点。当用户下载内容时&#xff0c;他们的设备也会成为内容分发的一部分&#xff0c;将已下载的内容传递给其…

项目部署_持续集成_Jenkins

1 今日内容介绍 1.1 什么是持续集成 持续集成&#xff08; Continuous integration &#xff0c; 简称 CI &#xff09;指的是&#xff0c;频繁地&#xff08;一天多次&#xff09;将代码集成到主干 持续集成的组成要素 一个自动构建过程&#xff0c; 从检出代码、 编译构建…

树状数组实现 查找逆序对

题意&#xff1a; 输入一个整数n。 接下来输入一行n个整数 。 1< < n ,且每个数字只会出现一次 题解&#xff1a; 按每个数字的大小存入树状数组 #include<bits/stdc.h> using namespace std; #define ll long long const int N10000; int arr[N]; ll a[N];…

Java中关于构造代码块和静态代码块的解析

构造代码块 特点&#xff1a;优先于构造方法执行,每new一次,就会执行一次 public class Person {public Person(){System.out.println("我是无参构造方法");}{System.out.println("我是构造代码块"); //构造代码块} }public class Test {public stati…