深入浅出MySQL-06-【索引的设计和使用】

news/2024/5/16 9:44:03

文章目录

  • 前言
  • 1.索引概述
  • 2.设计索引的原则
  • 3.索引设计的误区
  • 4.索引设计的一般步骤
  • 5.BTREE索引和HASH索引
  • 6.索引在MySQL 8.0中的改进
    • 6.1.不可见索引
    • 6.2.倒序索引
  • 7.总结

前言

环境:

  • Windows11
  • MySQL-8.0.35

1.索引概述

所有MySQL列类型都可以被索引,对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种存储引擎(如MyISAM、InnoDB、BDB、MEMORY等)对每个表至少支持16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。

MyISAM 和InnoDB存储引擎的表默认创建的都是BTREE索引。除了直接在单列或者多列上直接创建索引外,MySQL5.7之后可以通过虚拟列索引来实现函数索引的功能,同时MySQL也支持前缀索引,即对索引字段的前N个字符创建索引。前缀索引的长度跟存储引擎相关。

MySQL中还支持全文本(FULLTEXT)索引,该索引可以用于全文搜索。ImnnoDB和MyISAM存储引擎虽然支持FULLTEXT索引,但只限于 CHAR、VARCHAR 和 TEXT列。索引总是对整个列进行的,不支持局部(前缀)索引。

MySQL也可以为空间列类型创建索引,MySQL5.7中,InnoDB存储引擎也开始支持空间类型索引,索引以R-Trees的数据结构保存。

索引在创建表的时候可以同时创建,也可以随时增加新的索引。创建新索引的语法如下:

// 使用mysql的帮助文档查看创建索引的SQL语句
mysql> ? create index;
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name[index_type]ON tbl_name (key_part,...)[index_option][algorithm_option | lock_option] ...key_part: {col_name [(length)] | (expr)} [ASC | DESC]
... ...

也可以使用ALTER TABLE的语法来增加索引,语法类似。

索引的删除语句如下:

mysql> ? drop index;
Name: 'DROP INDEX'
Description:
Syntax:
DROP INDEX index_name ON tbl_name[algorithm_option | lock_option] ...... ...

2.设计索引的原则

索引的设计可以遵循一些已有的原则,创建索引的时候尽量考虑这些原则,便于提升索引的使用效率、更高效的使用索引:

  • 要在条件列上创建索引,而不是查询列。也就是说最适合索引的列是出现在 WHERE子句中的列,或连接子句中指定的列,而不是出现在 SELECT 关键字后的选择列表中的列。
  • 尽量使用唯一索引。考虑某列中值的分布。索引的列的基数越大,索引的效果越好。例如,存放出生日期的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“M”和“F”,则对此列进行索引没有多大用处,因为不管搜索哪个值,都会得出大约一半的行。
  • 使用短索引。如果对字符串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,有一个 CHAR(200)列,如果在前 10 个或 20 个字符内,多数值是唯一的,那么就不要对整个列进行索引。对前 10个或 20个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘IO较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
  • 利用最左前缀。在创建一个n列的索引时,实际相当于创建了MySQL可利用的n个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。例如以 a、b、c的顺序在3列上创建一个组合索引之后,利用 a=?或者 a=? and b=?或者 a=?and b=?andc=?这3种条件的查询,都可以使用这个索引。通过这种方式,可以有效的降低索引的数量,提高索引的使用效率。
  • 对于 InnoDB 存储引擎的表,尽量手工指定主键。记录默认会按照一定的顺序保存,如果有明确定义的主键,则按照主键顺序保存。如果没有主键,但是有唯一索引,那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的,所以 InnoDB 表尽量自己指定主键。当表中同时有几个列都是唯一的,都可以作为主键的时候,要选择最常作为访问条件的列作为主键,提高查询的效率。另外,还需要注意,InnoDB表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,有效地减少索引的磁盘占用,提高索引的缓存效果。
  • 对于 InnoDB 存储引擎的表,尽量手工指定主键。记录默认会按照一定的顺序保存,如果有明确定义的主键,则按照主键顺序保存。如果没有主键,但是有唯一索引,那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的,所以 ImnoDB 表尽量自己指定主键。当表中同时有几个列都是唯一的,都可以作为主键的时候,要选择最常作为访问条件的列作为主键,提高查询的效率。另外,还需要注意,InnoDB表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,有效地减少索引的磁盘占用,提高索引的缓存效果。

3.索引设计的误区

设计索引时,有一些常见的误区,总结如下:

  • 不是所有的表都需要创建索引。通常来说,数据量很小的表,除了主键外,再创建索引没有太大的意义,索引扫描和全表扫描相比,并不会带来性能的大幅提升。而大表的查询、更新、删除操作则要尽可能通过索引。对于大表来说,任何全表扫描对于系统来说都会是非常大的冲击,因此每个操作都尽可能通过索引进行。这类表要经常统计操作频率较高的 SQL,然后对这些 SQL进行分析,提取最常用的一些选择性高的列来创建索引。
  • 不要过度索引。不要以为索引“越多越好”,什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL选择不到所要使用的最好索引。因此,只保持所需的索引有利于查询优化。
  • 谨慎创建低选择度索引。对于选择性低并且数据分布均衡的列,因为过滤的结果集大,创建索引的效果通常不好;但如果列的选择性低但数据分布不均衡,比如男女比例为99%:1%,那么此时创建索引对于查询条件为’女"的过滤结果集就比较小,索引的效率较高,此时创建索引就比较合适。在MySQL8.0之后也可以使用直方图取得类似的优化效果。

4.索引设计的一般步骤

通过上面的介绍,当对一个大表做索引设计时,一般可以采取下面的步骤:

  1. 整理表上的所有SQL,重点时select、update 和 delete 操作的where条件所用到的列的组合、关联查询的关联条件等。
  2. 整理所有查询 SQL的预期执行频率。
  3. 整理所有涉及的列的选择度,列的不同值相比总非空行数的比例越大,选择度越好,比如全部都是唯一值的主键列选择度最高。当然,上面所提到的查询频率、选择度,都是估算的值,能够在设计索引时作为参考即可。
  4. 遵照之前提到的设计原则,给表选择合适的主键。
  5. 优先给那些执行频率最高的SQL创建索引,执行频率很高的SQL,使用到的索引的效率对整体性能影响也会比较大,选择其中选择度最高的列来创建索引,如果选择度都不够好,那么应该考虑是否可以使用其他选择度更好的条件,或者选择创建联合索引。
  6. 按执行频率排序,依次检查是否需要为每个SQL创建索引,可以复用之前已经创建的索引的SQL,无须再重复创建索引,除非SQL执行频率很高,新创建的索引,对选择度提升也很大。
  7. 索引合并,利用复合索引来降低索引的总数,充分利用最左前缀的原则,让索引可以被尽可能多地复用,同时在保证复用率的情况下,把选择度更高的列放到索引的更左侧。
  8. 上线之后,通过慢查询分析、执行计划分析、索引使用统计,来确定索引的实际使用情况,并根据情况做出调整。

5.BTREE索引和HASH索引

BTREE索引 和 HASH索引,这两种不同类型的索引各有其不同的适用范围。HASH索引有一些重要的特征在使用时需特别注意,如下所示:

  • 只用于使用=或<=>( NULL-safe 的等于操作符)操作符的等式比较。
  • 优化器不能使用 HASH 索引来加速 ORDER BY 操作。
  • MySQL 不能确定在两个值之间大约有多少行。如果将一个 MyISAM 表改为 HASH索引的MEMORY表,会影响一些查询的执行效率。
  • 只能使用整个关键字来搜索一行。

而对于 BTREE 索引,当使用>、<、>=、<=、BETWEEN、!=或者>,或者 LIKE‘pattern’(其中’pattern’不以通配符开始)操作符时,都可以使用相关列上的索引。例如下面这个查询适用于BTREE和HASH索引:

select * from t1 where key_col = 1 or key_col in (15, 18, 20);

下列的查询只适用于BTREE索引:

select * from t1 where key_col > 1 and key_col < 10;
select * from t1 where key_col like 'ab%' and key_col between 'zhangsan' and 'wangwu';

当对索引字段进行范围查询的时候,只有BTREE索引可以通过索引访问,而HASH索引实际上是全盘扫描的。

6.索引在MySQL 8.0中的改进

索引的正确使用,对于MySQL的性能优化,起着非常关键的作用。在MySQL8.0中索引也引人了不少新的特性。下面介绍几个比较重点的改进。

6.1.不可见索引

所谓不可见,指的是对于查询优化器不可见,SQL在执行时自然也就不会选择,但是在查看表结构的时候索引仍然看得见,也可以通过 information_schema.statistics 或 show index 来查看索引是否可见的状态。

索引默认是可见的,可以通过创建索引时指定 invisible 关键字来创建不可见索引:

CREATE TABLE t1 (col int,col2 intINDEX col_idx(col) INVISIBLE
) ENGINE = InnoDB

也可以通过命令来单独添加不可见索引:

CREATE INDEX col_idx(col) INVISIBLE;
ALTER TABLE t1 ADD INDEX_col_idx(col) INVISIBLE;

可以通过 alter table 命令来修改索引是否可见:

ALTER TABLE t1 ALTER INDEX col_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX col_idx VISIBLE;

为什么数据库中要设计这么一种消耗资源,却又不能够对SQL起到任何优化左右的索引呢?实际上,引入不可见索引的目的,主要是为了减小对于表上的索引进行调整时的潜在风险。

随着表的数据量增大,达到了几百GB几TB甚至更大的时候,如果此时对表上的索引进行调整,往往面临着很大的风险。例如,当删除一个认为不再需要的索引时,一旦系统中还存在个别使用这个索引的SOL,那么这些SOL的执行计划有可能会变成对这个大表的全表扫描,这会对数据库服务器造成巨大冲击或直接导致服务不可用。而由于表的数据量大,重建索引需要的时间和消耗的系统资源也会很大,很难马上通过重建索引解决问题。

有了不可见索引,当需要删除一个表上的冗余索引时,可以先将索引设置为不可见,而不是直接删除,一旦发现没有这个索引之后,对系统性能产生了负面影响,可以很方便地恢复这个索引,而不再需要重建索引。

同样,当增加一个索引之后,如果发现对系统带来了负面影响,可以首先将索引设置为不可见,待系统负载恢复正常后,再做索引的删除,避免了系统压力大的时候雪上加霜。

6.2.倒序索引

在 MySQL8.0中,正式增加了对于倒序索引(descending index)的支持,在之前的版本中虽然在创建索引的时候可以指定 desc关键字,但是实际上 MySQL仍然会保存为正序索引。

MySQL中的倒序索引起到的作用比较弱,这里不做讨论了。

7.总结

索引用于快速找出在某个列中有某个特定值的行。如果不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻数据文件的中间,没有必要看所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意如果需要访问大部分行,顺序读取要快得多,因为此时应避免磁盘搜索。

大多数 MySQL索引(如PRIMARY KEY、UNIQUE、INDEX和FULLTEXT等)在 BTREE中存储。只是空间列类型的索引使用BTREE,并目MEMORY表还支持HASH索引。


http://www.mrgr.cn/p/44244044

相关文章

如何调节电脑屏幕亮度?让你的眼睛更舒适!

电脑屏幕亮度的调节对于我们的视力保护和使用舒适度至关重要。不同的环境和使用习惯可能需要不同的亮度设置。可是如何调节电脑屏幕亮度呢&#xff1f;本文将介绍三种不同的电脑屏幕亮度调节方法&#xff0c;帮助您轻松调节电脑屏幕亮度&#xff0c;以满足您的需求。 方法1&…

CH592 CH582 CH573 蓝牙运行时调整RTC

前言: CH592芯片在使用蓝牙外部32K精度比较高(根据选择的外部32.768K晶体,精度一般在20ppm以内)。直接使用内部32K不校准误差约为百分之二,校准后可以做到0.1%-0.3%精度。 使用外部32K需要消耗一颗晶振的物料,同时芯片的相应GPIO会被占用。如果对于32K的误差要求不是很高…

Pod monitoring of Nodejs

一、Nodejs添加接口 1、nextjs用法 安装包prom-client,在ping同一目录层级创建接口api/ssr/metrics 比如首页https://mik.dev.platform.michaels.com/api/ssr/metrics dc项目https://mik.dev.platform.michaels.com/api/ssr/dc/metrics import { register, collectDefaultMet…

建立成功平台工程的关键:自助式 IaC

了解团队部署自助式IaC的实践方法从技术上讲,云一直都是自助式服务,但由于其在实践中的复杂性,许多开发人员并不喜欢。随着公司采用现代架构(云原生、无服务器等)和新的提供商(多云、SaaS 应用程序),以及云提供商发布更多服务,云变得更加难以使用。这就是为什么有竞争…

VS2022 配置OpenCV开发环境详细教程

OpenCV OpenCV&#xff08;Open Source Computer Vision Library&#xff09;是一个开源的计算机视觉和机器学习软件库&#xff0c;由Intel开发并首先发布于1999年。OpenCV被广泛用于实时图像处理、视频分析、物体检测、面部识别、机器人视觉以及许多其他领域。它支持C、Pytho…

WEBAPI传参及默认首页设置

开发工具:VS2017 创建WEBAPI, 1.选择ASP.NET Core Web应用程序2.选择如下,HTTPS配置勾选去掉,暂不配置3.“属性”中调试默认界面及launchsettings.json 4.调试以后默认页面 5.

传统FTP为何不好用了?替代FTP传文件的方式有哪些?

FTP(文件传输协议)是一种广泛使用的网络协议,用于在网络上的服务器和客户端之间传输文件。它具有一些明显的优点,比如易于使用、支持文件的上传和下载、以及能够处理多个文件和目录的传输。然而,随着时间的推移和技术的发展,FTP也暴露出一些局限性和缺点,这导致一些企业…

阿里云操作日记

昨天买了一个超级便宜的阿里云服务器&#xff0c;2核2G&#xff0c;3M固定带宽&#xff0c;40G ESSD Entry云盘&#xff0c;搭载一个简单的系统&#xff0c;就想到了docker轻量级&#xff0c;易于管理 其实docker很好用&#xff0c;第一步就是安装docker 一、docker安装与端口…

hmac测试

openssl命令: 生成hmac:验证:C语言代码实现: 代码如下:` #include #include #include #include int main() { // 这个例子中,我们将随机生成一个密钥 unsigned char key[EVP_MAX_MD_SIZE]; int key_len = 32; // OpenSSL 函数 RAND_bytes 用于生成强随机数 if (!RAND…

什么是跨域? 出现原因及解决方法

什么是跨域? 出现原因及解决方法 什么是跨域 跨域&#xff1a;浏览器对于javascript的同源策略的限制 。 同源政策的目的&#xff0c;是为了保证用户信息的安全&#xff0c;防止恶意的网站窃取数据。 设想这样一种情况&#xff1a;A 网站是一家银行&#xff0c;用户登录以后…

小程序微信及h5 发布流程

微信小程序 1 先打包代码 pnmp build:mp-wexin(package.json) 2 把代码上传到微信开发者工具 3 微信公众平台提交审核 条件编译及h5注意: 网页端不支持授权登录功能,通过条件编译进行不同平台 条件编译语法: #ifdef 或者#ifndef + 平台名称; 以#endif结尾(全局找wx. 或者…

日本宇宙航空研究“Int-Ball2”自由飞行相机机器人采用的Epson IMU

IMU有助于飞行的稳定控制和电池充电的自动对接- 精工爱普生公司&#xff08;TSE:6724&#xff0c;“Epson”&#xff09;很高兴地宣布&#xff0c;日本宇宙航空研究开发机构&#xff08;JAXA&#xff09;选择了爱普生M-G370系列的惯性测量单元&#xff08;IMU&#xff09;&…

自动化测试数据生成:Asp.Net Core单元测试利器AutoFixture详解

引言 在我们之前的文章中介绍过使用Bogus生成模拟测试数据,今天来讲解一下功能更加强大自动生成测试数据的工具的库"AutoFixture"。 什么是AutoFixture?AutoFixture 是一个针对 .NET 的开源库,旨在最大程度地减少单元测试中的“安排(Arrange)”阶段,以提高可维…

基于Springboot+Vue的Java项目-火车票订票系统开发实战(附演示视频+源码+LW)

大家好&#xff01;我是程序员一帆&#xff0c;感谢您阅读本文&#xff0c;欢迎一键三连哦。 &#x1f49e;当前专栏&#xff1a;Java毕业设计 精彩专栏推荐&#x1f447;&#x1f3fb;&#x1f447;&#x1f3fb;&#x1f447;&#x1f3fb; &#x1f380; Python毕业设计 &am…

测试数据不再难,人工智能批量生成给你用!

简介 测试数据是指一组专注于为测试服务的数据,既可以作为功能的输入去验证输出,也可以去触发各类异常场景。 测试数据的设计尤为重要,等价类、边界值、正交法等测试用例设计方法都是为了更全面的设计对应的测试数据集。 实践演练 在批量生成测试数据中,我们需要明确数据的…

[转载]一些人士论文的学术指标[2017.4.26 from sina blog]

大连海事大学贾欣乐教授博文。原文地址:一些人士论文的学术指标作者:贾欣乐

26-代码随想录383赎金信

383. 赎金信 简单 相关标签 相关企业 给你两个字符串&#xff1a;ransomNote 和 magazine &#xff0c;判断 ransomNote 能不能由 magazine 里面的字符构成。 如果可以&#xff0c;返回 true &#xff1b;否则返回 false 。 magazine 中的每个字符只能在 ransomNote 中使用一…

IC设计数据传输 如何能保障安全高效?

IC(集成电路)设计数据,对于IC设计企业来说,其重要性不言而喻。所以IC设计数据传输过程中,其安全性和效率,也需要有保障。 首先我们来看看IC设计数据为什么重要,其重要性体现在多个方面,主要包括: 1、知识产权:IC设计数据包含了企业的核心技术和创新成果,是公司知识…

OpenSSL测试-SM4

0. 在openEuler(推荐)或Ubuntu或Windows(不推荐)中完成下面任务 1. 使用OpenSSL的命令对你的8位学号(数字)进行加密解密,密钥的前8个字节为你的8位学号,提交过程截图(5) 2. 使用OpenSSL编程对对"你的8位学号(数字)"进行加密解密,提交代码和运行结果截图。(1…