MySQL：索引02——使用索引

目录

引言

1、自动创建索引

 2、手动创建索引

2.1 主键索引

2.2 查看索引信息

2.3 唯一索引

2.4 普通索引

2.5 复合索引

 3、删除索引

3.1 主键索引

3.2 其他索引

4、查看执行计划

 4.1 不加条件，查询所有

4.2 使用主键查询

4.3 子查询使用索引

4.4 普通索引

4.5 复合索引

引言

在上篇文章中，详细介绍了有关索引的理论性知识，包含了索引底层的数据结构B+树、B+树与B树的对比、页、页的结构、索引分类......

数据库索引底层数据结构之B+树&MySQL中的页&索引分类【纯理论干货，面试必备】-CSDN博客

接下来的这篇文章，我将向大家讲解如何SQL使用索引。

1、自动创建索引

  •  当我们在表中为字段创建主键约束(PRIMARY KEY)，唯一约束(UNIQUE)，外检约束(FOREIGN KEY)时，MySQL就会为表中相应的列就会自动创建索引。
  • 如果表中没有指定任何索引时，MySQL会自动为每一列生成一个索引并用 ROW_ID 进行标识(隐藏的，无法查看且无法使用)

 2、手动创建索引

2.1 主键索引

创建主键索引的方式有三种：

  1. 在创建表时就直接创建主键
  2. 在创建表时单独指定主键列
  3. 创建完表后再添加主键列
-- 在创建表时就直接创建主键
CREATE TABLE t_pk1 (
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR(50)
);-- 在创建表时单独指定主键列
CREATE TABLE t_pk2 (
id BIGINT auto_increment,
name VARCHAR(50),
PRIMARY KEY (id)
);-- 创建完表后再添加主键列
CREATE TABLE t_pk3 (
id BIGINT,
name VARCHAR(50)
);
ALTER TABLE t_pk3 add PRIMARY KEY (id);
ALTER TABLE t_pk3 MODIFY id BIGINT auto_increment;

使用ALTER修改表内容，语法如下：

 alter table 表面 add|modify|drop 要修改的内容；

2.2 查看索引信息

创建完索引后，我们可以查看索引信息：

  1. desc 表名；//查看索引的简要信息
  2. show index from 表名；
  3. show keys from 表名；

主键索引的名称默认为PRIMARY。 

2.3 唯一索引

创建唯一索引的方式同样有三种：

  1. 在创建表时就直接指定唯一约束
  2. 在创建表时单独指定唯一约束
  3. 创建完表后再添加唯一约束
-- 在创建表时就直接指定唯一约束
CREATE TABLE t_uniq1(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50) UNIQUE
);-- 在创建表时单独指定唯一约束
CREATE TABLE t_uniq2(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
UNIQUE (NAME)
);-- 创建完表后再添加唯一约束
CREATE TABLE t_uniq3(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50)
);
ALTER TABLE t_uniq3 add UNIQUE (NAME);

创建完后可以查看索引信息：

2.4 普通索引

创建普通索引(索引)的方式有三种：

  1. 创建表时创建索引列
  2. 创建完表后使用alter创建索引
  3. 创建完表后使用 create index 索引名 on 表名(列名,...) 创建索引并指定索引名【最常用，可指定索引名】

 使用 create index 索引名 on 表名(列名[列名, ...])  为创建索引最常用的语法形式，且索引名推荐指定为 索引类型_表名_索引列 的形式。

-- 创建表时创建索引列
CREATE TABLE t_index1(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
INDEX (NAME)
);-- 创建完表后使用alter创建索引
CREATE TABLE t_index2(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50)
);
ALTER TABLE t_index2 add INDEX (name);-- 创建完表后使用create index创建索引并指定索引名【最常用，可指定索引名】
CREATE TABLE t_index3(
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR(50)
);
CREATE INDEX idx_t_index3_name on t_index3(name);

2.5 复合索引

复合索引的创建语法与创建普通索引相同，只不过指定多个列，列与列之间用逗号隔开：

  1. 创建表时指定复合索引列
  2. 创建完表后使用alter创建复合索引
  3. 创建完表后使用 create index 索引名 on 表名(列名,...) 创建索引并指定索引名【最常用，可指定索引名】
-- 创建表时指定复合索引列
CREATE TABLE t_index4(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
sn VARCHAR(50),
INDEX (NAME, sn)
);	-- 创建完表后使用alter创建索引
CREATE TABLE t_index5(
id BIGINT PRIMARY KEY auto_increment,
NAME VARCHAR(50),
sn VARCHAR(50)
);
ALTER TABLE t_index5 add INDEX (name, sn);-- 创建完表后使用create index创建索引并指定索引名【最常用，可指定索引名】
CREATE TABLE t_index6(
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR(50),
sn VARCHAR(50)
);
CREATE INDEX idx_t_index6_name_sn on t_index6(name, sn);#指定索引名

 3、删除索引

3.1 主键索引

因为主键索引是在我们创建主键约束时就自动创建的，不是我们手动人为指定的，并且主键索引只有一个，故删除主键索引语法如下：

alter table 表名 drop PRIMARY KEY； //删除主键索引的同时，删除主键约束 

删除主键索引，需要注意一点：

  • 主键列不能定义为auto_increment(自增类型)，否则无法删除主键索引
  • 如果主键列是自增类型，需要先修改掉自增类型，再进行主键索引的删除
  • 修改掉主键的自增类型：alter table t_pk1 modify id bigint；

 当然，如果主键不含自增类型，则可直接用 alter table t_pk1 modify id bigint 来删除主键索引。

3.2 其他索引

语法：alter table 表名 drop index 索引名；

4、查看执行计划

对于很多小白来说，虽然自己创建了索引，但不清楚自己写出的SQL到底走没走索引，接下来我将为大家介绍一个关键字(查看执行计划)：explain。 

在explain后加上我们写出的SQL语句，就能够查看该条语句的执行计划，判断到底走没走索引。

接下来的操作，我们均在student表中演示，先为name和sn列添加复合索引： 

 4.1 不加条件，查询所有

当我们直接使用 select * from student 时，此时为全表扫描（不走索引）。

我们可以使用explain select * from student；查看执行计划，发现type列中为ALL，说明该SQL没有走索引，是全表扫描得出的结果(效率低)。在生产环境中，如果出现了这样的情况(type为ALL)，此时我们就要考虑为该列加索引了。

4.2 使用主键查询

当我们使用主键索引进行查询时，很显然会走索引，根据主键索引树，会很快的查询到目标值(主键索引树中包含所有的数据)。

使用explain查看执行计划时，type为const，代表查询效率为常量级别，非常的快，说明走索引。

 4.3 子查询使用索引

4.4 普通索引

当我们要查询的列包含在索引中时，会发生索引覆盖，此时不需要回表查询。

当要查询的列不完全包含在索引中时，会发生回表查询。

Extra列若为：Using index ，则表示索引覆盖。

4.5 复合索引

因为sn列创建了唯一索引，为了避免影响复合索引的查询，先drop掉复合索引sn。 接下来，我们再来查看复合索引的执行计划：

我们创建的复合索引为index(name,sn)，name为复合索引的第一列，即name在前，sn在后，故使用name查询sn时，走索引，发生索引覆盖：

 但是若使用sn来查name，则不走索引：

当出现了Using where，说明可能进行了全表扫描(不走索引)，这时我们就需要判断我们的SQL语句是不是出现了问题，对SQL做出优化。

  • Extra:执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
  • Using index:表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，即发生索引覆盖。
  • Using where:表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where（可能有全表扫描的情况）。

注意，当使用AND或其他情况下，只要where条件中使用了索引包含的所有列，就会走索引，和顺序无关：

END

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

【架构设计】多级缓存：应用案例与问题解决策略

【架构设计】多级缓存：应用案例与问题解决策略

【架构设计】多级缓存：应用案例与问题解决策略 多级缓存系统的工作原理及其在提升应用性能方面的关键作用。通过对比本地缓存与分布式缓存的特点 | 原创作者/编辑：凯哥Java | 分类：架构设计系列教程 多级缓存…
阅读更多...
在基准测试和规划测试中选Flat还是Ramp-up？

在基准测试和规划测试中选Flat还是Ramp-up？

Flat测试和Ramp-up测试是各有优势的，下面我们就通过介绍几种实用的性能测试策略来分析这两种加压策略的着重方向。 基准测试 基准测试是一种测量和评估软件性能指标的活动，通过基准测试建立一个已知的性能水平（称为基准线）&…
阅读更多...
WPS生成目录

WPS生成目录

导航窗格：视图->导航窗格 可修改标题的样式，之后的标题直接套用即可 修改其他标题样式也是这样 添加编号：可以选上面的模版 也可自定义编号 生成目录：引用->目录->选用一个 但是我想把目录插到另一页 当我添加几个标题…
阅读更多...
Spark-RDD持久化

Spark-RDD持久化

一、Spark的三种持久化机制 1、cache 它是persist的一种简化方式，作用是将RDD缓存到内存中，以便后续快速访问，提高计算效率。cache操作是懒执行的，即执行action算子时才会触发。 2、persist 它提供了不同的存储级别&#xff0…
阅读更多...
无人机黑飞打击技术详解

无人机黑飞打击技术详解

随着无人机技术的普及，无人机“黑飞”（未经授权或违反规定的飞行）现象日益严重，对公共安全、隐私保护及重要设施安全构成了严重威胁。为有效应对这一挑战，各国政府和安全机构纷纷研发并部署了一系列无人机黑飞打击技术…
阅读更多...
HTML简介

HTML简介

HTML简介 1.HTML概述2.HTML元素3.HTML属性4.HTML 注释5.HTML颜色 1.HTML概述 HTML 是用来描述网页的一种语言。 HTML 指的是超文本标记语言HTML 不是一种编程语言&#xff0c;而是一种标记语言标记语言是一套标记标签HTML 使用标记标签来描述网页 例子&#xff1a; <htm…
阅读更多...
Kotlin cancel CoroutineScope.launch的任务后仍运行

Kotlin cancel CoroutineScope.launch的任务后仍运行

Kotlin cancel CoroutineScope.launch的任务后仍运行 import kotlinx.coroutines.*fun main() {runBlocking {val coroutineScope CoroutineScope(Dispatchers.IO)val job coroutineScope.launch {var i 0while (i < Int.MAX_VALUE) {iprintln(i)}}// 2ms 取消协程delay(…
阅读更多...
2.计算机网络基础

2.计算机网络基础

2. 计算机网络基础 (1) 计算机网络的定义 计算机网络是指将地理位置不同、具有独立功能的多个计算机系统通过通信线路和设备连接起来，以功能完善的网络软件实现网络中资源共享的系统。最简单的定义是：计算机网络是一些互相连接的、自治的计算机系统的集合。最庞大的计算机网…
阅读更多...
在 PostGIS 中进行千万级空间数据的空间查询和关键字查询

在 PostGIS 中进行千万级空间数据的空间查询和关键字查询

一、目的 本测试在探究在有限的计算机配置下&#xff0c;如何高效地对千万级的空间数据进行空间查询和关键字查询。通过实际操作和测试&#xff0c;评估不同查询策略的性能&#xff0c;为处理大规模空间数据提供可行的解决方案。 计算机配置如下&#xff1a; 内存&#xff0…
阅读更多...
声网SDK脚本运行错误

声网SDK脚本运行错误

文章目录 运行步骤无法运行.bat电脑出现警告--更改执行策略若无出现-更新power shell搜索最新版本的 PowerShell安装新版本 仍无法解决-手动下载第三方库 2024-9-9运行步骤 无法运行.bat 电脑出现警告–更改执行策略 若无出现-更新power shell 搜索最新版本的 PowerShell 在…
阅读更多...
记录｜如何对批量型的pictureBox组件进行批量Image设置

记录｜如何对批量型的pictureBox组件进行批量Image设置

目录 前言一、问题表述二、批量化处理更新时间 前言 参考文章&#xff1a; 一、问题表述 问题就是上图所示&#xff0c;这些的命名风格统一&#xff0c;只是最后的数字是不同的。所以存在可以批量化进行处理的可能性。 二、批量化处理 private void SetPictureBoxImages(){for…
阅读更多...
ElementPlus表单验证报错 formEl.validate is not a function

ElementPlus表单验证报错 formEl.validate is not a function

出现问题的代码 <!-- 密码重置弹框 --><el-dialog v-model"innerVisible" width"500" title"密码重置" append-to-body><el-form ref"ruleFormRef" style"max-width: 600px" :model"passForm" sta…
阅读更多...
HarmonyOS元服务与卡片

HarmonyOS元服务与卡片

元服务与卡片 文章目录 一、元服务1.介绍2.常见元服务项目步骤 二、卡片1.介绍2.卡片的创建3.卡片的数据的变更4.卡片的进程间通讯4.1使用工具包4.2使用步骤 5.卡片路由postCardAction&#xff1a;快速拉起后台5.1格式5.2快速拉起指定页面--router5.3调用后台功能--call5.3卡片…
阅读更多...
委托的注册和注销

委托的注册和注销

让我们来回顾一下委托的内容。 委托 是一种复杂的数据类型&#xff0c;需要我们先定义出来。当定义好类型后&#xff0c;声明委托变量来使用。 可以装载方法&#xff0c;只可以装载具有相同返回类型和参数列表的方法。 委托变量名&#xff08;参数列表&#xff09;&#xf…
阅读更多...
使用Webpack创建vue脚手架并搭建路由---详解

使用Webpack创建vue脚手架并搭建路由---详解

1.使用 vue 库 vue 是一个非常好用的 javascript 库&#xff0c;现在已经发行了 vue 3&#xff0c;我们可以直接导入使用库文件&#xff0c;也可以使用单文件&#xff08;SFC&#xff09;的形式&#xff0c;直接使用库文件会简单一点&#xff0c;我们先来试一下吧。 1.1安装 v…
阅读更多...
Qt 模型视图(二):模型类QAbstractItemModel

Qt 模型视图(二):模型类QAbstractItemModel

文章目录 Qt 模型视图(二)&#xff1a;模型类QAbstractItemModel1.基本概念1.1.模型的基本结构1.2.模型索引1.3.行号和列号1.4.父项1.5.项的角色1.6.总结 Qt 模型视图(二)&#xff1a;模型类QAbstractItemModel ​ 模型/视图结构是一种将数据存储和界面展示分离的编程方法。模…
阅读更多...
巡检管理系统软件：功能与逻辑的深度探索

巡检管理系统软件：功能与逻辑的深度探索

在现代企业管理中&#xff0c;巡检管理系统软件扮演着至关重要的角色。巡检管理系统不仅能提升巡检工作的效率和准确性&#xff0c;还能为企业的整体运营提供有力支持。下面将从功能与逻辑两个方面对巡检管理系统软件进行深入分析。 一、巡检管理系统软件的功能 巡检计划制定 …
阅读更多...
快速体验Linux发行版：DistroSea详解与操作指南

快速体验Linux发行版：DistroSea详解与操作指南

DistroSea 是一个功能强大的在线平台&#xff0c;允许用户在无需下载或安装的情况下&#xff0c;通过浏览器直接测试多种Linux和BSD发行版。该平台非常适合Linux爱好者、系统管理员和开发者&#xff0c;提供一个简便的方式来体验各种操作系统而无需影响本地设备。 为什么选择D…
阅读更多...
CleanMyMac 5 for Mac 最新中文破解版下载 系统优化垃圾清理工具

CleanMyMac 5 for Mac 最新中文破解版下载 系统优化垃圾清理工具

今天给大家带来的是CleanMyMac最新款CleanMyMac 5&#xff0c;它是一个全面的Mac清理和维护工具&#xff0c;通过提供多项强大的功能&#xff0c;帮助用户简化日常维护任务&#xff0c;提升系统性能&#xff0c;同时保护个人隐私和安全。无论是新手还是经验丰富的Mac用户&#…
阅读更多...
如何实现实时监控局域网计算机桌面？学会这5个妙招你就能搞定！

如何实现实时监控局域网计算机桌面？学会这5个妙招你就能搞定！

在现代企业环境中&#xff0c;实时监控局域网内的计算机桌面已成为确保工作效率、维护信息安全的重要手段。 无论是出于管理需求还是安全考虑&#xff0c;掌握这一技能对于IT管理员来说都至关重要。 本文将详细介绍五个妙招&#xff0c;帮助你轻松实现局域网内计算机桌面的实…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023