Mysql索引规范及原理分析

1 Mysql存储引擎

MySQL中的数据用各种不同的技术存储在文件中,每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力,这些不同的技术以及配套的功能在MySQL中称为存储引擎。

存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。

MylSAM存储引擎,5.5版本之前的默认存储引擎

MylSAM拥有较高的插入、查询速度,但不支持事物,也不支持外键,但是访问速度快。可以用来存储日志记录等功能。由于目前不再使用,大家大概了解下即可。

lnnoDB存储引擎,5.5版本之后的默认存储引擎

InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键。是大家必须知晓的内容。后续的内容均默认使用InnoDB存储引擎。

2 什么是索引

在mysql中,索引是存储引擎用于快速找到记录的一种数据结构。

索引的本质就是通过不断的缩小想要获取数据的范围来筛选出最终想要的结果。类似我们查字典,每一个字相当于一条数据,索引相当于目录,可以根据拼音或者偏旁部首快速查询出页码,进而查询到对应的信息。

总结来说,索引对于提升系统性能有较大的帮助,需要进一步的了解它。

问题1:为什么SQL查询很慢?

解答:未创建索引。

问题2:已经添加了索引,为什么SQL仍然很慢?

解答:未创建正确索引。索引无效。mysql优化器选择其他索引。mysql优化器不走索引,选择全表扫描。

问题3:已经添加了索引,也明确了SQL使用该索引,为什么SQL依旧很慢?

解答:1.单表数据量过多。即使添加了索引,仍然性能不高。2.Mysql分页机制,比如大数据分页场景下,即使使用了索引,仍然慢响应。3.使用性别等区分度小的字段作为索引,无法提高性能。

使用索引的注意事项:

1.表必须设置主键,建议使用系统自增的id主键。

2.索引名全部使用小写英文字母,采用下划线进行分割。

3.普通索引按照"idx_字段名”的格式进行命名。比如idx_driver_id。

4.唯一索引按照"unique字段名"的格式进行命名。比如unique_driver_id。

5.一张表中的索引数量建议不超过7个。(索引过多,影响写入性能,可能存在优化器选择错误索引的情况,具体数量视场景而定)

6.根据具体业务场景合理创建联合索引,可以有效减少索引数量。比如联合索引(a,b,c),相当于索引(a),(a,b),(a,b,c),因为其满足索引的最左匹配原则。

7.使用联表查询时,join列的数据类型必须相同,并且均需要创建索引。10.不在区分度低的字段上建立索引。

8.合理使用覆盖索引,可以有效减少回表lO。

MySQL为什么最终要去选择B+Tree?

  1. B+Tree是B TREE的变种,BTREE能解决的问题,B+TREE也能够解决(降低树的高度,增大节点存储数据量)

  1. B+Tree扫库和扫表能力更强。如果我们要根据索引去进行数据表的扫描,对B TREE进行扫描,需要把整棵树遍历一遍,而B+TREE只需要遍历他的所有叶子节点即可(叶子节点之间有引用)。

  2. B+TREE磁盘读写能力更强。他的根节点和支节点不保存数据区,所以根节点和支节点同样大小的情况下,保存的关键字要比B TREE要多。而叶子节点不保存子节点引用,能用于保存更多的关键字和数据。所以,B+TREE读写一次磁盘加载的关键字比B TREE更多。

  3. B+Tree排序能力更强。上面的图中可以看出,B+Tree天然具有排序功能

  4. B+Tree查询性能稳定。B+Tree数据只保存在叶子节点,每次查询数据,查询lo次数一定是稳定的。当然这个每个人的理解都不同,因为在BTREE如果根节点命中直接返回,确实效率更高。

B+树的数据结构是按照关键字进行比较的。

数值型:直接按照数值进行排序

字符型:按照每个字母的acsii值进行比较

(字符串也可以比较“大小”,有大小那就可以排序。 两个字符串自左向右逐个字符相比(按ASCIl值大小相比较),直到出现不同的字符或遇'\o'为止。)

3 索引最左匹配原则

当进行匹配的时候,会把字符串转换成ascll码,如abc变成97 、98、 99,然后从左往右一个字符一个字符进行对比。所以在sql查询中使用like %a时候索引会失效,因为%表示全匹配,如果已经全匹配就不需要索引,还不如直接全表扫描。

当关键字占用的空间越小,则每个节点保存的关键字个数就越多,每次加载进内存的关键字个数就越多,检索效率就越高。创建索引的关键字要尽可能占用空间小。

4 最少空间原则

主键是bigint,8bit;页号是4bit,一共12bit。一页总数:15K%12bit=1280条。

再假如每行数据大小为1Kb,每个叶子节点能存放数据就是15K%1K=15条。

一层的B+树存放数据是15

二层的B+树存放数据是1280*15

三层的B+树存放数据是1280乘以1280乘以15=2400万

因为考虑到磁盘IO性能问题,当超过3次时性能会急剧下降。如果每行数据只有250bit,单页数据可存放60条,三层B+树可存放接近1亿了,访问速度一样不会慢。

如果当前插入的数据是自增序列的话,只有右边的树形结构进行旋转变化,左侧并没有,因此Mysql主键建议用自增序列,不建议UUID自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。

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

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

相关文章

235 基于matlab的时频盲源分离(TFBSS)算法

基于matlab的时频盲源分离(TFBSS)算法,TFBSS用空间频率分布来分离非平稳信号,可以分离具有不同时频分布的源信号,也能够分离具有相同谱密度但时频分布不同的高斯源。同时,该算法在时频域上局域化源信号能量…

39岁TVB靓仔小生自曝恋情,曾沦为洗车工如今半年赚足7位数

39岁高钧贤自从2005年参加香港先生选举夺冠后,之后加入TVB拍摄过多套电视剧集,最近更有份参与《逆天奇案2》,日前他回到TVB电视城一厂与冯盈盈宣传剧集,更随即拍摄短片纪录放在网上分享,意外曝光TVB餐厅餐单&#xff0…

如何使用IDEA直接连接MySQL数据库

如何使用IDEA直接连接MySQL数据库 新建一个空项目打开DataBase窗口连接数据库第一次连接 需要先下载驱动上一步驱动下载太慢怎么办?下载好驱动后 测试连接 新建一个空项目 打开DataBase窗口 连接数据库 第一次连接 需要先下载驱动 如果这里下载的很慢 看下一步解决…

DaVinci Fusion Studio 19 for Mac/win:影视后期特效合成的巅峰之作

在影视后期制作的广袤天地里,一款强大的特效合成软件如同一位技艺高超的魔法师,能够化腐朽为神奇,将普通的影像素材转变为震撼人心的视觉盛宴。而DaVinci Fusion Studio 19,正是这样一款备受影视从业者推崇的巅峰之作。 无论是Ma…

矩阵按列相乘运算的并行化实现方法

这两天一直在琢磨如下矩阵计算问题。 已知dm矩阵X和hq矩阵Y,求如下矩阵: 其中X(:,i), Y(:,j)分别表示矩阵X, Y的第i列和第j列,易知Z为dh矩阵。 如果直接串行计算矩阵Z,两个循环共有mq,则会很慢,能不能并行化…

【b站李同学的Lee】Part 3 服务器开发 NodeJS+Gulp基础入门+实战

课程地址:【NodeJSGulp基础入门实战】 https://www.bilibili.com/video/BV1aE411n737/?share_sourcecopy_web&vd_sourceb1cb921b73fe3808550eaf2224d1c155 目录 9 服务器端基础概念 9.1 网站的组成 9.2 Node网站服务器 9.3 IP地址 9.4 域名 9.5 端口 9…

SCSS全局配置 vue项目(二)

目录 1、先要查看node版本 2、安装对应的node-sass、sass-loader版本 2.1根据项目使用的node版本安装对应的node-sass版本 2.2根据node-sass版本选择兼容的sass-loader版本,不然项目无法正常运行 3、在 vue.config.js 中配置: 4、在组件中…

刷题训练之前缀和

> 作者:დ旧言~ > 座右铭:松树千年终是朽,槿花一日自为荣。 > 目标:熟练掌握前缀和算法。 > 毒鸡汤:学习,学习,再学习 ! 学,然后知不足。 > 专栏选自:刷题…

物联网的基本功能及五大核心技术——青创智通

工业物联网解决方案-工业IOT-青创智通 物联网基本功能 物联网的最基本功能特征是提供“无处不在的连接和在线服务”,其具备十大基本功能。 (1)在线监测:这是物联网最基本的功能,物联网业务一般以集中监测为主、控制为…

Vitis HLS 学习笔记--C/C++ static 关键字的作用

目录 1. 简介 2. c/c共有性质 3. c独有性质 4. 示例说明 5. static 对于 HLS 工具的影响 6. 总结 1. 简介 在Vitis HLS中,偶尔会用到 static 关键字。考虑到Vitis HLS同时兼容C和C语言,有必要理解这两种语言中static关键字细微差异。本文旨在梳理…

Centos7.9系统MySQL5.7.32升级为5.7.44(生成环境操作)

1.背景 由于客户进行等保漏扫和渗透,生成环境mysql数据库被扫描出了 高危漏洞。 如图:部分漏洞 查看漏洞详细信息,建议升级到指定版本解决: 说明: 本文仅适合使用当前数据库为 RPM 安装方式 2.升级前准备 查看环…

串口服务器可以直接连接工业路由器吗

串口服务器可以直接连接工业路由器吗 在工业物联网的架构中,串口服务器和工业路由器都是不可或缺的重要组件。串口服务器的主要功能是将串口通信转换为网络通信,实现数据的远程传输和管理;而工业路由器则负责在工业环境中提供稳定、可靠的网…

QT入门:计算圆面积的QT开始以及日历相关

QT入门:计算圆面积的QT开始以及日历相关 使用的工具为Qt creator 如图所示的为Qt的一个基本目录,首先打开mainwindow.ui进行设计,首先是讲解日历的,可以完全不用写代码,只在mainwindow.ui即可实现。 这是最后的一个成…

YES-2000B数显压力试验机技术方案书

一、简介 本机采用主机与液压系统集于一体的结构形式,结构紧凑,小巧玲珑。采用液压加荷、电子测力,具有加荷数率显示,峰值保持等功能,并配有微型打印机。 外观示意图 二、 液压系统 油箱内的液压油通过电机带动高压…

带宽内存服务器爆满,阿里云木马排查过程

服务器的连接数和带宽都暴增,导致项目直接宕机,无法使用的解决方案。 查看服务器实时流量 服务器内执行命令: yum install iftop -y iftop -Pn查看日志,发现服务器在对外访问 .148.232.186 的443端口。 于是设置安全组出方…

前端HTML5学习1(新增布局,状态,列表,文本,表单控件标签)

前端HTML5学习1(新增布局,状态,列表,文本,表单控件标签) 新增布局标签新增状态标签新增列表标签新增文本标签新增表单控件属性input新增属性值 新增布局标签 HTML5 引入了许多新的语义化标签,用…

各省铁路里程、公路里程、交通网密度面板数据(2000-2022年)

01、数据简介 铁路里程是指铁路线从起点到终点的公里数,通常用于表示铁路线路的长度。 公路里程是指一定时期内实际达到《公路工程技术标准》规定的等级公路,并经公路主管部门正式验收交付使用的公路里程数。 交通网密度是指某一区域内交通线路的密集…

直播任我行,智享AI自动直播手机塑造直播新风潮,引领行业“风口”

直播任我行!智享AI自动直播手机塑造直播新风潮,引领行业“风口”! 直播作为一种受欢迎的互联网传播方式,如今在帮助商家推广产品并获得更多收益方面发挥着重要作用。 在直播电商领域,主播是连接品牌和用户之间的关键纽…

js如何点击生成4位随机数

效果图&#xff1a; 代码&#xff1a; <!DOCTYPE html> <html lang"en"> <head> <meta charset"UTF-8"> <meta name"viewport" content"widthdevice-width, initial-scale1.0"> <title>Generat…

15(第十四章,大数据和数据科学)

目录 概述 基本概念 数据仓库/传统商务智能与数据科学的比较 数据科学的过程 大数据 大数据来源 数据湖 机器学习 监督学习 无监督学习 强化学习 扩展 1、数据仓库&#xff08;Data Warehouse&#xff09; 2、数据湖(Data Lake) 3、大数据平台1.0 4、数据中台 …
最新文章