【MYSQL】索引机制概述

 由于MySQL是作为存储层部署在业务系统的最后端,所有的业务数据最终都要入库落盘,但随着一个项目在线上运行的时间越来越久,数据库中的数据量自然会越来越多,而数据体积出现增长后,当需要从表查询一些数据时,效率会越发低下。在正常情况下,表的查询性能和数据量是成反比的,也就是数据越多,查询越慢。

这是什么原因导致的呢?由于MySQL默认的查询方式导致的,举个例子~

SELECT * FROM `zz_student`;
+------------+--------+------+--------+
| student_id | name   | sex  | height |
+------------+--------+------+--------+
|          1 | 竹子   | 男   | 185cm  |
|          2 | 熊猫   | 女   | 170cm  |
|          3 | 子竹   | 男   | 182cm  |
|          4 | 棕熊   | 男   | 187cm  |
|          5 | 黑豹   | 男   | 177cm  |
|          6 | 脑斧   | 男   | 178cm  |
|          7 | 兔纸   | 女   | 165cm  |
+------------+--------+------+--------+SELECT * FROM `zz_student`  WHERE name = "脑斧";

上面给出了一张学生表,其中有七位学生信息,而此时要查询姓名为「脑斧」的学生信息时,MySQL底层是如何检索数据的呢?会触发磁盘IO,对表中的数据进行逐条读取并判断,也就是说,在这里想要查找到符合要求的数据,至少要经过六次磁盘IO才能检索到目标(暂且先不考虑局部性读取原理与随机IO)。

  • 那假设这个表中有1000W条数据呢?要查的目标数据位于表的900W行以后怎么办?岂不是要触发几百万次磁盘IO才能检索到数据啊,如果真的这样去干,其效率大家可想而知。

在这种情况下,又该如何去提升数据库的查询性能呢?因为查询往往都是一个业务系统中最频繁的操作,一般项目的写/读请求比例都遵循三七定律,也就是30%的请求会涉及到写库操作,另外70%则属于查库类型的操作。

   在思考如何提升查询性能前,咱们不妨先回想一下小时候的场景,小时候由于刚接触汉字,很多字都不认识,所以通常每个人小时候都会拥有一本「新华字典」,但一本字典那么厚,我们是一页页去翻的吗?并不是,字典中有目录索引,我们可以根据音节、偏旁等方式查找不认识的字。

在「新华字典」中一页页翻找某个汉字,就类似于我们前面给出的全表扫描方式,效率特别特别低,而通过目录索引则能够在很短的时间内找到目标汉字。

   既然字典中都存在目录索引页,能帮助小时候的我们快速检索汉字,那这个思想能否应用到数据库中来呢?答案是当然可以,并且MySQL也提供了索引机制,索引是数据库中的核心组件之一,一张表中建立了合适的索引后,往往在面对海量数据查询时,能够事半功倍,接下来一起聊一聊MySQL的索引。

索引机制会分为上、中、下三篇进行阐述,大致内容如下:
《上篇:索引初识篇》主要讲解索引的概述、分类、使用与管理等;
《中篇:索引应用篇》主要阐述索引优劣分析、建立索引的原则、索引失效的场景、如何正确的使用索引、索引优化机制等;
《下篇:索引原理篇》则主要讲述索引的底层实现、B+Tree、Hash数据结构、聚簇索引和非聚簇索引实现、索引查询原理、索引管理实现等;

一、MySQL索引机制概述

   对于MySQL索引机制的作用,经过上述「新华字典」的案例后可得知:索引就是用来帮助表快速检索目标数据的。此时先来简单回顾一下MySQL中索引是如何使用的呢?首先需要创建索引,MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

1.1、MySQL索引的创建方式

  • ①使用CREATE语句创建

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

这种创建方式可以给一张已存在的表结构添加索引,其中需要指定几个值:

  • indexName:当前创建的索引,创建成功后叫啥名字。
  • tableName:要在哪张表上创建一个索引,这里指定表名。
  • columnName:要为表中的哪个字段创建索引,这里指定字段名。
  • length:如果字段存储的值过长,选用值的前多少个字符创建索引。
  • ASC|DESC:指定索引的排序方式,ASC是升序,DESC是降序,默认ASC

当然,上述语句中的INDEX也可更改为KEY,作用都是创建一个普通索引,而对于其他的索引类型,这点在后续的索引分类中再聊。

  • ②使用ALTER语句创建
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

这里的参数都相同,所以不再重复赘述。

  • ③建表时DDL语句中创建
CREATE TABLE tableName(  columnName1 INT(8) NOT NULL,   columnName2 ....,.....,INDEX [indexName] (columnName(length))  
);

这种方式就比较适合在库表设计时,已经确定了索引项的情况下建立。

1.2、查询、删除、指定索引

但不管通过哪种方式建立索引,本质上创建的索引都是相同的,当索引创建完成后,可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引,如下:

 
CREATE TABLE `zz_user`  (`user_id` int(8) NOT NULL AUTO_INCREMENT,`user_name` varchar(255) NULL DEFAULT "",`user_sex` varchar(255) NULL DEFAULT "",`user_phone` varchar(255) NULL DEFAULT "",PRIMARY KEY (`user_id`) USING BTREE
)ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

在上述的建表SQL中,为user_id创建了一个主键索引,然后来查一下当前表的索引信息:

简单的概述一下查询后,每个字段的含义:

  • Table:当前索引属于那张表。
  • Non_unique:目前索引是否属于唯一索引,0代表是的,1代表不是。
  • Key_name:当前索引的名字。
  • Seq_in_index:如果当前是联合索引,目前字段在联合索引中排第几个。
  • Column_name:当前索引是位于哪个字段上建立的。
  • Collation:字段值以什么方式存储在索引中,A表示有序存储,NULL表无序。
  • Cardinality:当前索引的散列程度,也就是索引中存储了多少个不同的值。
  • Sub_part:当前索引使用了字段值的多少个字符建立,NULL表示全部。
  • Packed:表示索引在存储字段值时,以什么方式压缩,NULL表示未压缩,
  • Null:当前作为索引字段的值中,是否存在NULL值,YES表示存在。
  • Index_type:当前索引的结构(BTREE, FULLTEXT, HASH, RTREE)。
  • Comment:创建索引时,是否对索引有备注信息。

这条命令在后续排除问题、性能调优时,会有不小的作用,比如可以通过分析其中的Cardinality字段值,如果该值少于数据的实际行数,那目前索引有可能失效(对于这些后续排查篇和SQL优化篇再聊)。

OK~,到这里了解了一下索引相关的创建、查询命令,接着再看看删除、强制使用命令。

MySQL中并未提供修改索引的命令,也就说当你建错了索引,只能先删再重新建立一次,删除索引的语句如下:

DROP INDEX indexName ON tableName;

当然,当建立了一条索引后,也可以强制性的为SELECT语句指定索引,如下:

SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;

FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询,但要牢记的是:如果当前的查询SQL压根不会走指定的索引字段,哪这种方式是行不通的,这个关键字的用法是:一条查询语句在有多个索引可以检索数据时,显式指定一个索引,减少优化器选择索引的耗时。

但要注意:如果你对于你整个业务系统十分熟悉,那可以这样干。但如果不熟悉的话,还是交给优化器来自行选择,否则会适得其反!

1.3、数据库索引的本质

   前面一直在聊创建、查看、删除、指定等一些索引的基本操作,但索引本质上在数据库中是什么呢?大家都知道,数据库是基于磁盘工作的,所有的数据都会放到磁盘上存储,而索引也是数据的一种,因此与表数据相同,最终创建出的索引也会在磁盘生成本地文件。

   不过索引文件在磁盘中究竟以何种方式存储,这是由索引的数据结构来决定的。同时,由于索引机制最终是由存储引擎实现,因此不同存储引擎下的索引文件,其保存在本地的格式也并不相同。

在这里有一个点需要注意:建立索引的工作在表数据越少时越好,如果你想要给一张百万、千万条数据级别的表新创建一个索引,那创建的耗时也不短,这是为什么呢?

因为刚刚聊过,索引本质上和表是一样的,都是磁盘中的文件,那也就代表着创建一个索引,并不像单纯的给一张表加个约束那么简单,而是会基于原有的表数据,重新在磁盘中创建新的本地索引文件。假设表中有一千万条数据,那创建索引时,就需要将索引字段上的1000W个值全部拷贝到本地索引文件中,同时做好排序并与表数据产生映射关系。

OK~,至此就对MySQL提供的索引机制做了简单回顾,下面再来说说数据库中“多样化”的索引类型。

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

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

相关文章

网络安全入门教程(非常详细)从零基础入门到精通

网络安全是一个庞大而不断发展的领域,它包含多个专业领域,如网络防御、网络攻击、数据加密等。介绍网络安全的基本概念、技术和工具,逐步深入,帮助您成为一名合格的网络安全从业人员。 一、网络安全基础知识 1.计算机基础知识 …

蓝桥杯省赛冲刺(3)广度优先搜索

广度优先搜索(Breadth-First Search, BFS)是一种在图或树等非线性数据结构中遍历节点的算法,它从起始节点开始,按层级逐步向外扩展,即先访问离起始节点最近的节点,再访问这些节点的邻居,然后是邻…

DCI-BOX 数据中心互联扩容设备

2U DCI-BOX是针对DCI数据互联开发的一款高性能、大容量DCI平台 恒通未来2U DCI-BOX 优势: 随着5G网络的演进,人们对大数据需求越来越旺盛,2U DCI-BOX是针对DCI数据互联开发的一款高性能、大容量DCI平台。 1. 单机箱最大容量6.4T,单100G功耗…

设计模式——简单工厂模式

设计模式——简单工厂模式 什么是简单工厂模式简单工厂模式的优点 我们今天接着来看设计模式的简单工厂模式,如果还没看过上一篇的单列模式的小伙伴可以点击这里: https://blog.csdn.net/qq_67693066/article/details/136603292 什么是简单工厂模式 简单…

【c语言】结构体的访问

🎈个人主页:豌豆射手^ 🎉欢迎 👍点赞✍评论⭐收藏 🤗收录专栏:C语言 🤝希望本文对您有所裨益,如有不足之处,欢迎在评论区提出指正,让我们共同学习、交流进步&…

宏景eHR customreport/tree SQL注入漏洞复现

0x01 产品简介 宏景eHR人力资源管理软件是一款人力资源管理与数字化应用相融合,满足动态化、协同化、流程化、战略化需求的软件。 0x02 漏洞概述 宏景eHR customreport/tree 接口处存在SQL注入漏洞,未经过身份认证的远程攻击者可利用此漏洞执行任意SQL指令,从而窃取数据库…

【cocos creator】【TS】贝塞尔曲线,地图之间显示曲线,顺着曲线移动

参考: https://blog.csdn.net/Ctrls_/article/details/108731313 https://blog.csdn.net/qq_28299311/article/details/104009804 const { ccclass, property } cc._decorator;ccclass export default class mapPanel extends cc.Component {property(cc.Node)pla…

力扣HOT100 - 41. 缺失的第一个正数

解题思路: 原地哈希 就相当于,让每个数字n都回到下标为n-1的家里。 而那些没有回到家里的就成了孤魂野鬼流浪在外,他们要么是根本就没有自己的家(数字小于等于0或者大于nums.size()),要么是自己的家被别…

试题 C: 质因数个数

萎了&#xff0c;整个人都萎了 快三天都没刷题了&#xff0c;想着明天就蓝桥杯了&#xff0c;就找了个真题做了下 可以看得出来这题很简单 但是没有测试点给我用&#xff0c;所以我的代码不保证正确性 代码如下&#xff1a; #include<cstdio> #include<cmath> …

014:vue3 van-list van-pull-refresh实现上拉加载,下拉刷新

文章目录 1. 实现上拉加载&#xff0c;下拉刷新效果2. van-list&#xff0c;van-pull-refresh组件详解2.1 van-list组件2.2 van-pull-refresh组件 3. 完整案例4. 坑点&#xff1a;加载页面会一直调用加载接口 1. 实现上拉加载&#xff0c;下拉刷新效果 通过下拉刷新加载下一页…

20240405,数据类型,运算符,程序流程结构

是我深夜爆炸&#xff0c;不能再去补救C了&#xff0c;真的来不及了&#xff0c;不能再三天打鱼两天晒网了&#xff0c;真的来不及了呜呜呜呜 我实在是不知道看什么课&#xff0c;那黑马吧……MOOC的北邮的C正在进行呜呜 #include <iostream> using namespace std; int…

力扣 | 54. 螺旋矩阵

注意按照顺时针方向进行访问元素&#xff0c;以及每次触发的条件只会满足一个&#xff01; public List<Integer> spiralOrder(int [][] matrix){List<Integer> result new ArrayList<>();int m matrix.length;int n matrix[0].length;int row0,col 0;//…

【汇编】计算机系统构成

计算机系统构成 计算机系统包括硬件和软件两部分 硬件 典型的计算机结构包括 中央处理器(CPU)、存储器和输入输出(I/O)子系统 三个主要组成部分&#xff0c;用系统总线把它们连接在一起 计算机硬件组成与各部分之间的联系 软件 计算机软件可以分为系统软件和用户软件两大类 …

react17中配置webpack:使用@代表src目录

在vue的项目中可以使用表示src目录&#xff0c;使用该符号表示绝对路径&#xff0c;那么在react中想要使用怎么办呢&#xff1f; 在react中使用表示src目录是需要在webpack中配置的&#xff0c;在核心模块node_modules-》react-scripts-》config-》webpack.config.js中搜索找到…

必应bing搜索广告推广国内能开户吗?

随着互联网广告市场的不断进化和细分化&#xff0c;必应Bing搜索广告已逐渐成为中国企业拓展国内市场、精准触达目标客户的重要渠道之一。2024年&#xff0c;必应Bing在国内市场的进一步布局&#xff0c;不仅彰显了其对本土企业的强大吸引力&#xff0c;更带来了全新的开户政策…

Java基础入门--第十一章--JDBC(Java Database Connection)Java数据库连接

JDBC 11.1 什么是JDBC11.1.1 JDBC概述11.1.2 JDBC驱动程序 11.2 JDBC的常用API11.3 JDBC编程11.3.1 JDBC 编程步骤11.3.2 实现第一个JDBC程序 我的MySQL的root密码: root 11.1 什么是JDBC 11.1.1 JDBC概述 JDBC的全称是Java数据库连接&#xff08;Java Database Connectivit…

React - 你知道props和state之间深层次的区别吗

难度级别:初级及以上 提问概率:60% 如果把React组件看做一个函数的话,props更像是外部传入的参数,而state更像是函数内部定义的变量。那么他们还有哪些更深层次的区别呢,我们来看一下。 首先说props,他是组件外部传入的参数,我们知道…

【React】React18+Typescript+craco配置最小化批量引入Svg并应用的组件

React18Typescriptcraco配置最小化批量引入Svg并应用的组件 前言创建React Typescript项目通过require.context实现批量引入Svg安装[types/webpack-env](https://github.com/DefinitelyTyped/DefinitelyTyped/blob/master/README.zh-Hans.md)解决类型报错安装[craco](https://…

数据中心的网络架构设计,打造高效、安全的数字底座

数据中心的网络架构设计 一、数据中心网络架构设计原则 网络,作为数据中心的核心支柱,其结构精妙,由众多二层接入设备与少量三层设备共同编织而成。过去,数据中心网络规模有限,仅凭数十台设备的简单互连便能实现信息的畅通无阻。然而,随着技术与应用需求的飞速增长,数据…

16. 网络编程(1)

Hi,大家好!从本节开始我们学习网络编程相关的知识。基于TCP服务器和客户端实现流程框架。 本节目录: 网络编程在软件开发中具有相当重要的作用,涉及到各方各面: 网络通信: Linux系统作为一个多用户、多任务的操作系统,网络通信是其重要的功能之一。通过网络编程,可以实现…