MySQL：索引（Index）语句

索引的限制

  • 每个表最多可以有 16 个索引（InnoDB 表的限制）。

  • 单个索引最多可以包含 16 列。

  • 索引列的最大长度为 767 字节（对于 CHAR, VARCHAR, 和 BINARY 类型），3072 字节（对于 BLOB 类型）。

索引的使用场景

  • 普通索引：适用于一般的查询场景。

  • 主键索引：作为表的唯一标识符，用于快速查找记录。

  • 唯一索引：用于确保某一列或多列的值是唯一的，例如作为唯一标识符。

  • 全文索引：用于全文检索，例如搜索引擎。

  • 组合索引：用于加速涉及多个列的查询。

索引的性能考量

  • 写操作：索引会增加写操作的开销，因为每次插入或更新数据时都需要更新索引。

  • 读操作：合理的索引可以极大地提高查询速度，特别是对于大型表。

  • 索引维护：定期分析和优化索引以保持最佳性能。

创建索引

创建索引时的注意事项

  • 选择性高的列更适合创建索引。
  • 尽量避免在经常修改的列上创建索引。
  • 使用前缀索引来减少索引的大小和提高性能（适用于较长的字符串列）。

创建表时创建索引

CREATE TABLE table_name(-- 字段定义-- ...-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）PRIMARY KEY ('column1'),
-- 创建唯一索引UNIQUE INDEX ('column2'),
-- 创建单例索引INDEX ('column3'),
-- 创建组合索引INDEX ('column4', 'column5', ...)
)ENGINE = INNODB DEFAULT CHARSET = utf8

在已经存在的表中创建索引

使用 ALTER TABLE 语句为表创建索引

ALTER TABLE table_name
ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  
[index_name] (column [(长度)] [ASC | DESC]) [USING 索引方法]；

  • UNIQUE：可选。表示索引为唯一性索引。

  • FULLTEXT：可选。表示索引为全文索引。

  • SPATIAL：可选。表示索引为空间索引。

  • INDEX 和 KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。

  • index_name：可选。给创建的索引取一个新名称。索引名称应具有唯一性，以免与其他索引发生冲突。若不指定名称，MySQL 会默认生成一个索引名称。

  • column：指定索引对应的字段的名称，该字段必须是前面定义好的字段。

  • 长度：可选。指索引的长度，必须是字符串类型才可以使用。

  • ASC：可选。表示升序排列。

  • DESC：可选。表示降序排列。

  • 索引方法默认使用 B+Tree。

普通索引 INDEX

普通索引是最基本的索引类型，可以加速对表中任意列的查询。

  • 语法：
ALTER TABLE table_name
ADD INDEX index_name ('column');
主键索引 PRIMARY KEY

主键索引用于标识表中的每一行记录，它是唯一的且不允许为空。

  • 语法：
ALTER TABLE table_name
ADD 
PRIMARY KEY ('column');
唯一索引 UNIQUE

唯一索引确保索引列中的值是唯一的（除了NULL值，NULL值不受唯一性约束限制）。

  • 语法：
ALTER TABLE table_name
ADD 
UNIQUE (column_list);
全文索引 FULLTEXT

全文索引用于支持全文搜索，适用于文本内容的搜索。

  • 语法：
ALTER TABLE table_name
ADD 
FULLTEXT index_name (column_list);
组合索引

组合索引是在多个列上建立的索引，可以用于加速涉及这些列的查询。

ALTER TABLE table_name
ADD 
INDEX index_name ('column1','column2',...);
空间索引 SPATIAL

使用空间索引，需要确保 MySQL 数据库版本支持地理空间功能，并且已经安装了适当的地理空间库。从 MySQL 5.7 版本开始，InnoDB 开始提供对空间索引的支持。

ALTER TABLE table_name
ADD 
SPATIAL index_name (column_list);

添加索引

使用 CREATE INDEX 语句，能够在已存在的表中增加除主键索引之外的其他索引，必须为索引设置索引名，且不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  
index_name ON  table_name (column_list) 
[USING 索引方法]；

普通索引

CREATE INDEX index_name ON table_name ('column');

组合索引

CREATE INDEX index_name ON table_name ('column1','column2',...)

唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_list)

全文索引

CREATE FULLTEXT INDEX index_name ON table_name (column_list)

空间索引

空间索引用于地理空间数据，支持空间查询和地理坐标搜索。

CREATE SPATIAL INDEX index_name ON table_name (column_list)
  • 注意：空间索引通常应用于 GEOMETRY 类型的列。

删除索引

删除索引可以使用 ALTER TABLE 或 DROP INDEX 语句来实现，并且 DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理

DROP INDEX index_name on table_name;

-- 删除指定索引
ALTER TABLE table_name
DROP INDEX index_name;-- 删除主键索引
ALTER TABLE table_name
DROP PRIMARY KEY;

执行 DROP PRIMARY KEY 语句时，因为一个表只可能有一个主键索引，因此不需要指定索引名。如果没有创建主键索引，但表具有一个或多个 UNIQUE 索引，则 MySQL 将删除第一个 UNIQUE 索引。

如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

查看建立的索引

可以使用 SQL 语句或直接使用工具查看表中已经建立的索引

SHOW INDEX FROM table_name;

查看SQL语句对索引的使用情况

在 SELECT 语句前加上 EXPLAIN，可查看 SQL 语句对索引的使用情况，即查询 SQL 的查询执行计划。

执行如下语句：

EXPLAIN SELECT * FROM book WHERE price < 30;

查询结果：

![[EXPLAIN SELECT.png]]

也可以使用 SQL 工具查看，如：Navicat 中的“解释”选项即可查看。

使用索引

单例索引

  • 当查询只涉及到其中的一个字段时，会使用到该字段指定的索引

  • 使用多个索引时，先使用哪个索引后使用哪个索引，是由 MySQL 的优化器经过一些列计算后作出的抉择。

  • 涉及到多个索引字段时，如果这些索引字段中，存在主键索引，那么只会使用该索引（MYSQL 优化器会选出并先执行“最严”的索引）

  • 涉及到多个索引字段时，如果这些索引字段中，不存在主键索引的话，那么就会使用这些字段对应的索引（如果通过其中的部分索引就能准确定位的话，那么其余的索引就不再被使用）

  • 当对索引字段进行 > > >， < < <， > = >= >=， < = <= <=，NOT IN，BETWEEN …… AND ……，函数(索引字段)，LIKE 模糊查询 % 在字段前时，不会使用该索引。

组合索引

在实际使用时，如果查询涉及到多列，我们一般都不会将这些列一一创建为单列索引，而是将这些列创建为组合索引。

最左前缀原则

最左前缀原则是数据库中一个重要的索引规则，用于指导在有多个列上的复合索引中，如何使用索引进行查询。

最左前缀原则规定：在尝试使用复合索引进行查询时，必须按照索引中列的顺序依次指定，并且查询条件中连续的列必须是索引的左侧列，不能跳过左侧列，否则该索引将无法使用。

例如，如果一个复合索引包含了两个列 (A, B)。那么，如果我们要使用这个索引来查询数据，必须符合以下条件：

  1. 要查询的列至少包含前两个索引列 A 和 B。
  2. 查询条件中必须包含 A 列，否则无法使用索引。
  3. 如果查询条件中包含了 A 和 B 列，则可以使用复合索引完成查询操作。否则，只能使用单独的 A 列索引或者全表扫描。

最左前缀原则的意义在于，保证复合索引的效率。因为索引是按照列的顺序构建的，只有按照规则使用索引，才能最大限度地利用索引，提高查询效率。如果不遵循最左前缀原则，那么即使建立了复合索引，也无法使用该索引，会导致查询效率降低，需要花费更多的时间和资源。

MySQL 5.7 开始，会自动优化，将条件优化为按组合索引的顺序进行查询。
如：假设组合索引为 A,B,C，会把查询列中的 C,B,A 优化为 A,B,C 使之完全遵循最左原则；会把 C,A 优化为 A,C 使之部分遵循最左原则。即：SQL语句中的对应条件的先后顺序无关。

最左原则的使用情况

  • 完全满足最左原则：即时条件中字段的顺序与索引不同，也满足最左原则，此时会使用组合索引

  • 部分满足最左原则：假设索引为（A,B,C），查询列条件为 A,C，此时 A满足最左原则，而 C 不满足最左原则，此时 A 会使用组合索引，而 C 不使用组合索引。

  • 满足(部分满足)最左原则的字段里，有字段不满足“索引”自身的使用规范：如果 SQL 语句里的字段里，有字段满足了最左原则，这些字段会使用组合索引，但是某些字段不满足“索引”自身的使用规范（如使用比较操作符等），这些字段不会使用组合索引。

  • 不满足最左原则：假设索引为（A,B,C），查询列条件为 B,C，此时不满足最左原则，不会使用组合索引。

聚簇索引与非聚簇索引

每个 InnoDB 表具有一个特殊的索引称为 聚簇索引（主索引）（也叫聚集索引，聚类索引，簇集索引）。

  • 如果表上定义有主键，该主键索引就是聚簇索引。

  • 如果未定义主键，MySQL 取第一个唯一索引（UNIQUE）而且只含非空列（NOT NULL）作为主键，InnoDB 使用它作为聚簇索引。

  • 如果没有这样的列，InnoDB 就自己产生一个这样的 ID 值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫 非簇状索引（辅助索引）（secondary indexes）。

回表

当二级索引无法直接查询到 SQL 中 SELECT 需要的所有列的数据时，会通过二级索引查询到聚簇索引（即：一级索引）后，再根据聚簇索引，查询到二级索引中无法提供的数据。

这种通过二级索引查询出一级索引，再通过一级索引查询二级索引中无法提供的数据的过程，就叫做回表。

当无需回表时，不遵循最左原则也是会走组合索引

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

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

相关文章

浅谈取样器插件之bzm - Free-Form Arrivals Thread Group

浅谈取样器插件之bzm - Free-Form Arrivals Thread Group

浅谈取样器插件之bzm - Free-Form Arrivals Thread Group bzm - Free-Form Arrivals (Ultimate Thread Group) 是一个高级且灵活的线程组插件&#xff0c;专为Apache JMeter设计。它扩展了JMeter的标准线程组功能&#xff0c;允许用户以自由形式定义线程&#xff08;用户&…
阅读更多...
SSM项目学习：用xml配置文件或注解开发实现控制反转和依赖注入

SSM项目学习：用xml配置文件或注解开发实现控制反转和依赖注入

什么是SSM SSMSpring(Spring Framework)Spring MVC mybatis Spring Framework系统架构 Spring Framework学习线路 IoC(Inversion of Control)和DI(Dependency Injection) 他们解决的问题&#xff1a;代码耦合度高的问题&#xff0c;需要类自己new对象&#xff0c;修改部分代…
阅读更多...
03、DQL（数据查询语句）

03、DQL（数据查询语句）

目录 1、编写顺序 2、基本查询 3、条件查询 4、聚合函数 5、分组查询 6、排序查询 7、分页查询 8、执行顺序 1、编写顺序 SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数2、基本查…
阅读更多...
简单的docker学习 第11章 镜像中心

简单的docker学习 第11章 镜像中心

第11章 镜像中心 Docker Hub 与阿里云都是 Docker 的公网镜像中心&#xff0c;用户可以将自己的镜像 push 到公网镜像中心中自己的镜像仓库&#xff0c;并可将仓库设置为私有库&#xff0c;使他人无法看到&#xff0c;更无法 pull&#xff0c;以保证镜像的安全性。不过&#x…
阅读更多...
【LeetCode刷题笔记】LCR.27 回文链表

【LeetCode刷题笔记】LCR.27 回文链表

创作不易&#xff0c;本篇文章如果帮助到了你&#xff0c;还请点赞 关注支持一下♡>&#x16966;<)!! 主页专栏有更多知识&#xff0c;如有疑问欢迎大家指正讨论&#xff0c;共同进步&#xff01; 更多算法知识专栏&#xff1a;算法分析&#x1f525; 给大家跳段街舞感谢…
阅读更多...
为什么康耐视visionpro的C#二次开发调用的recorddisplay控件偶尔会显示白色的，偶尔又正常了？

为什么康耐视visionpro的C#二次开发调用的recorddisplay控件偶尔会显示白色的，偶尔又正常了？

recorddisplay控件正常显示 异常显示 原因分析&#xff1a; 没有完全加载recorddisplay控件&#xff0c;有可能是有bug没有完全加载&#xff0c;打断点调试控件是否完全加载。
阅读更多...
EMQX服务器安装MQTT测试

EMQX服务器安装MQTT测试

cd /usr/local/develop wget https://www.emqx.com/en/downloads/broker/5.7.1/emqx-5.7.1-el7-amd64.tar.gz mkdir -p emqx && tar -zxvf emqx-5.7.1-el7-amd64.tar.gz -C emqx ./emqx/bin/emqx start 重启 ./emqx/bin/emqx restart http://10.8.0.1:18083/ 账号ad…
阅读更多...
【Kubernetes】应用的部署（一）：金丝雀部署

【Kubernetes】应用的部署（一）：金丝雀部署

应用的部署&#xff08;一&#xff09;&#xff1a;金丝雀部署 在项目迭代开发过程中&#xff0c;经常需要对应用进行上线部署。上线部署策略主要有 3 种&#xff1a;金丝雀部署、蓝绿部署 和 滚动部署。 金丝雀部署 也被叫作 灰度部署。金丝雀部署过程&#xff1a;先让一部分…
阅读更多...
letcode 分类练习 哈希表 242.有效的字母异位词 349. 两个数组的交集 202. 快乐数 1. 两数之和

letcode 分类练习 哈希表 242.有效的字母异位词 349. 两个数组的交集 202. 快乐数 1. 两数之和

letcode 分类练习 哈希表 242.有效的字母异位词 349. 两个数组的交集 202. 快乐数 1. 两数之和 242.有效的字母异位词349.两个数组的交集202.快乐数1.两数之和 242.有效的字母异位词 分别定义两个字母哈希表就可以了 class Solution { public:bool isAnagram(string s, strin…
阅读更多...
搭建pxe网络安装环境

搭建pxe网络安装环境

实验目的&#xff1a; 搭建pxe网络安装环境实现服务器自动部署 实验原理&#xff1a; PXE 网络安装环境实现服务器自动部署的实验原理为&#xff1a; 待安装的服务器&#xff08;PXE 客户端&#xff09;开机时&#xff0c;BIOS 设置从网络启动&#xff0c;向网络发送请求。…
阅读更多...
科普文：JUC系列之ForkJoinPool源码解读ForkJoinWorkerThread

科普文：JUC系列之ForkJoinPool源码解读ForkJoinWorkerThread

科普文&#xff1a;JUC系列之ForkJoinPool基本使用及原理解读-CSDN博客 科普文&#xff1a;JUC系列之ForkJoinPool源码解读概叙-CSDN博客 科普文&#xff1a;JUC系列之ForkJoinPool源码解读WorkQueue-CSDN博客 科普文&#xff1a;JUC系列之ForkJoinPool源码解读ForkJoinTask…
阅读更多...
【第13章】Spring Cloud之Gateway全局异常处理

【第13章】Spring Cloud之Gateway全局异常处理

文章目录 前言一、异常处理1. 响应实体类2. 异常处理类 二、单元测试1. 无可用路由2. 服务不可用 总结 前言 网关作为我们对外服务的入口起着至关重要的作用&#xff0c;我们必须保证网关服务的稳定性&#xff0c;下面来为网关服务增加异常处理机制。 一、异常处理 1. 响应实…
阅读更多...
K个一组翻转链表（LeetCode）

K个一组翻转链表（LeetCode）

题目 给你链表的头节点 &#xff0c;每 个节点一组进行翻转&#xff0c;请你返回修改后的链表。 是一个正整数&#xff0c;它的值小于或等于链表的长度。如果节点总数不是 的整数倍&#xff0c;那么请将最后剩余的节点保持原有顺序。 你不能只是单纯的改变节点内部的值&…
阅读更多...
UE GAS学习

UE GAS学习

【Unreal】虚幻GAS系统快速入门-CSDN博客 GameplayTags FGameplayTags是一种层级标签&#xff0c;如Parent.Child.GrandChild。 通过GameplayTagManager进行注册。替代了原来的Bool&#xff0c;或Enum的结构&#xff0c;可以在玩法设计中更高效地标记对象的行为或状态。 Gamep…
阅读更多...
牛客周赛 Round 54 （A~E）

牛客周赛 Round 54 （A~E）

#牛客周赛 Round 54 &#xff08;A~E&#xff09; 前言&#xff1a; 以后会定时更新很多比赛的题解 希望借此让自己坚持赛后补题 要不然写完就结束 自己水平没有一点提高 本人很菜所以不会更新 太难的题 加油&#xff01;&#xff01;&#xff01;1. ​清楚姐姐的糖葫芦…
阅读更多...
C语言之递归函数

C语言之递归函数

文章目录 &#x1f34a;自我介绍&#x1f34a;递归函数 你的点赞评论就是对博主最大的鼓励 当然喜欢的小伙伴可以&#xff1a;点赞关注评论收藏&#xff08;一键四连&#xff09;哦~ &#x1f34a;自我介绍 Hello,大家好&#xff0c;我是小珑也要变强&#xff08;也是小珑&…
阅读更多...
C#学习笔记12：SYN6288语音模块_Winform上位机控制软件

C#学习笔记12：SYN6288语音模块_Winform上位机控制软件

今日尝试使用C# Winform写一个上位机软件控制 SYN6288语音模块 这里不讲什么基本原理(或者讲的比较略简)&#xff0c;直接讲实现了就...... 文章提供测试代码讲解、测试效果图、整体测试工程下载 目录 控件的摆放&#xff1a; SYN6288介绍: 代码编程&#xff1a; 对16进制发送…
阅读更多...
VUE.js

VUE.js

目录 一、什么是VUE.js 二、VUE.js优点 三、VUE安装 四、第一个VUE程序 五、Vue指令 v-text v-html v-on v-model v-show v-if v-bind v-for 六、VUE实例生命周期 七、Vue-CLI搭建项目 主要的功能 需要的环境 八、组件路由 搭建步骤: 1. 创建 router 目录 …
阅读更多...
ctfhub 命令注入

ctfhub 命令注入

知识点 1.常见的拼接符 1、A ; B 先执行A&#xff0c;再执行B 2、A & B 简单的拼接 3、A | B 显示B的执行结果 4、A&&B A执行成功之后才会执行B 5、A || B A执行失败之后才会执行B , 在特殊情况下可代替空格 2.常见的命令 &#…
阅读更多...
CentOS7 VMware虚拟机基于NAT配置网络IP

CentOS7 VMware虚拟机基于NAT配置网络IP

目录 前言 VMnet8 虚拟网络编辑 ens33 ping 防火墙 前言 平时学习时一直需要用到Linux服务器&#xff0c;一般都是在Windows上安装VMware来创建一个虚拟机。创建的虚拟机需要配置网络才能够访问外网&#xff0c;可以通过以下两种方式来配置虚拟机网络 桥接模式NAT模式&…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发