数据库优化

文章目录

  • 一、如何定位慢查询
  • 二、分析慢SQL
    • (一)Explain
  • 三、索引介绍
    • (一)数据结构
      • 1、二叉树
      • 2、红黑树
      • 3、B树
      • 4、B+ 树
    • (二)聚集索引&二级索引
    • (三)索引的创建原则
    • (四)索引的失效场景
  • 四、sql优化经验
    • (一)表的设计优化
    • (二)索引优化
    • (三)SQL语句优化
    • (四)主从复制、读写分离
    • (五)分库分表
      • 1、拆分策略
        • 1.1 垂直分库
        • 1.2 垂直分表
        • 1.3 水平分库
        • 1.4 水平分表
      • 2、分库分表带来的新的问题和技术
  • 五、事务
    • (一)事物特性
    • (二)隔离级别
      • 1、并发事物问题
      • 2、隔离级别
    • (三)undo log & redo log
      • 1、缓冲池&数据页
      • 2、redo log
      • 3、undo log
    • (四)MVCC
      • 1、一行数据中的隐藏字段
      • 2、undo log
      • 3、undo log 版本链
      • 4、当前读
      • 5、快照读
      • 6、readview


一、如何定位慢查询

  1. 开源工具
    调试工具:Arthas
    运维工具:Prometheus,skywarking
  2. 开启mysql自带的慢日志查询
  3. sql执行计划

二、分析慢SQL

(一)Explain

在这里插入图片描述

  1. select_type

  2. table

  3. partiontions

  4. type:sql的连接类型
    在这里插入图片描述

  5. possible_keys : 当前sql可能用到的索引

  6. key:当前sql实际命中的索引

  7. key_len:使用到的索引占用大小;通过key、key_len查看是否命中索引

  8. ref

  9. rows

  10. filtered

  11. extra 额外的优化建议
    在这里插入图片描述

可以采用MySQL自带的分析工具 EXPLAIN

  • 通过key和key len检査是否命中了索引(索引本身存在是否有失效的情况)
  • 通过type字段査看sql是否有进一步的优化空间,是否存在全索引扫描或全盘扫描
  • 通过extra建议判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复

三、索引介绍

索引(index)是帮助MySQI高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定査找算法的数据结构(B+树),这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级査找算法这种数据结构就是索引。

(一)数据结构

1、二叉树

2、红黑树

3、B树

B-Tree,B树是一种多叉路衡査找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。以一颗最大度数(max-deqree)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key。
在这里插入图片描述

4、B+ 树

B+Tree是在BTree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构
在这里插入图片描述
B树与B+树对比:
②磁盘读写代价B+树更低;
②:查询效率B+树更加稳定;
③:B+树便于扫库和区间查询1:

了解过索引吗?(什么是索引)
索引(index)是帮助MySQL高效获取数据的数据结构(有序)提高数据检索的效率,降低数据库的I0成本(不需要全表扫描)
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

索引的底层数据结构了解过嘛 ?
MySQL的InnoDB引擎采用的B+树的数据结构来存储索引 阶数更多,路径更短
磁盘读写代价B+树更低,非叶子节点只存储指针,叶子阶段存储数据B+树便于扫库和区间查询,叶子节点是一个双向链表

(二)聚集索引&二级索引

在这里插入图片描述

聚集索引选取规则:

  • 如果存在主键,主键索引就是聚集索引。
  • 如果不存在主键,将使用第一个唯-(UNIQUE)索引作为聚集索引。》
  • 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

什么是聚簇索引什么是非聚簇索引?
聚簇索引(聚集索引):数据与索引放到一块,B+树的叶子节点保存了整行数据,有且只有一个非聚簇索引(二级索引):数据与索引分开存储,B+树的叶子节点保存对应的主键,可以有多个

什么是回表查询 ?
通过二级索引找到对应的主键值,到聚集索引中查找整行数据,这个过程就是回表

什么叫覆盖索引 ?
覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到

MYSQL超大分页处理
问题:在数据量比较大时,limit分页查询,需要对数据进行排序,效率低
解决方案:覆盖索引+子查询
优化思路: 一般分页查询时,通过创建 覆盖索引 能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化
在这里插入图片描述

(三)索引的创建原则

索引创建原则有哪些?
1).针对于数据量较大,且查询比较频繁的表建立索引。单表超过10万数据(增加用户体验)。
2).针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3).尽量选择区分度高的列作为索引,尽量建立唯索引,区分度越高,使用索引的效率越高。
4).如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
5).尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
6).要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7).如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。

(四)索引的失效场景

在这里插入图片描述

1).违反最左前缀法则如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。匹配最左前缀法则,走索引:
在这里插入图片描述
违法最左前缀法则, 索引失效:
在这里插入图片描述
如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:
在这里插入图片描述
2).范围查询右边的列,不能使用索引。
在这里插入图片描述
3).不要在索引列上进行运算操作, 索引将失效。
在这里插入图片描述
4).字符串不加单引号,造成索引失效。
在这里插入图片描述
5).以%开头的Like模糊査询,索引失效。如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
在这里插入图片描述

四、sql优化经验

(一)表的设计优化

表的设计优化(参考阿里开发手册《嵩山版》)
比如设置合适的数值(tinyint int bigint),要根据实际情况选择
比如设置合适的字符串类型(char和varchar)char定长效率高,varchar可变长度,效率稍低

(二)索引优化

参考三

(三)SQL语句优化

1、SELECT语句务必指明字段名称(避免直
2、SQL语句要避免造成索引失效的写法
3、尽量用union all代替union union会多一次过滤,效率低
4、避免在where子句中对字段进行表达式
5、Join优化 能用innerjoin 就不用left joinright join,如必须使用 一定要以小表为驱动,内连接会对两个表进行优化,优先把小表放到外边,把大表放到里边。leftjoin 或 right join,不会重新调整顺序

(四)主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候,为了避免写的操作所造成的性能影响 可以采用读写分离的架构。
读写分离解决的是,数据库的写入,影响了查询的效率。
在这里插入图片描述
MySQL主从复制的核心就是二进制日志
二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但不包括数据查询(SELECT、SHOW)语句。
在这里插入图片描述

复制分成三步:

  1. Master 主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中。
  2. 从库读取主库的二进制日志文件 Binlog ,写入到从库的中继日志 Relay Log 。
  3. slave重做中继日志中的事件,将改变反映它自己的数据。

主从同步原理
MySQL主从复制的核心就是二进制日志binlog(DDL(数据定义语言)语句和 DML(数据操纵语言)语句)
① 主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中。
② 从库读取主库的二进制日志文件 Binlog,写入到从库的中继日志 Relay Log.
③ 从库重做中继日志中的事件,将改变反映它自己的数据

(五)分库分表

在这里插入图片描述

主从复制只是分担访问压力,但是无法解决数量太大的问题
分库分表的时机:
1,前提,项目业务数据逐渐增多,或业务发展比较迅速单表的数据量达1000W或20G以后
2,优化已解决不了性能问题(主从读写分离、查询索引…)
3,10瓶颈(磁盘10、网络10)、CPU瓶颈(聚合查询、连接数太多)

1、拆分策略

1,水平分库,将一个库的数据拆分到多个库,解决海量数据存储和高并发的问题
2,水平分表,解决单表存储和性能的问题
3,垂直分库,根据业务进行拆分,高并发下高磁盘I0和网络连接数
4,垂直分表,冷热数据分离,多表互不影响

在这里插入图片描述

1.1 垂直分库

垂直分库:以表为依据,根据业务将不同表拆分到不同库中。

特点:

  1. 按业务对数据分级管理、维护、监控、扩展
  2. 在高并发下,提高磁盘I0和数据量连接数

在这里插入图片描述

1.2 垂直分表

在这里插入图片描述

垂直分表:以字段为依据,根据字段属性将不同字段拆分到不同表中。
拆分规则:

  1. 把不常用的字段单独放在一张表
  2. 把text,blob等大字段拆分出来放在附表中

特点:

  1. 冷热数据分离
  2. 减少I0过渡争抢,两表互不影响
1.3 水平分库

在这里插入图片描述

水平分库:将一个库的数据拆分到多个库中

路由规则

  1. 根据id节点取模
  2. 按id也就是范围路由,节点1(1-100万)节点2(100万-200万)
1.4 水平分表

水平分表:将一个表的数据拆分到多个表中(可以在同一个库内)。
在这里插入图片描述
特点:
1.优化单一表数据量过大而产生的性能问题,
2.避免I0争抢并减少锁表的几率;

2、分库分表带来的新的问题和技术

在这里插入图片描述

五、事务

(一)事物特性

  • 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
  • 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
  • 隔离性(lsolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
  • 在这里插入图片描述

(二)隔离级别

1、并发事物问题

在这里插入图片描述

2、隔离级别

在这里插入图片描述

事务中的隔离性是如何保证的呢?
锁:排他锁(如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁);共享锁
mvcc:多版本并发控制

(三)undo log & redo log

1、缓冲池&数据页

  • 缓冲池(buffer pool):主内存中的一个区域,里面可以缓存磁盘上经常操作的真实数据,在执行增删改査操作时,先操作缓冲池中的数据(若缓冲池没有数据,则从磁盘加载并缓存),以一定频率刷新到磁盘,从而减少磁盘10,加快处理速度.
  • 数据页(page):是InnoDB 存储引擎磁盘管理的最小单元,每个页的大小默认为 16KB。页中存储的是行数据.

2、redo log

重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用,

3、undo log

回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样,它是逻辑日志。

  • 可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然
  • 当update一条记录时,它记录一条对应相反的update记录。当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

(四)MVCC

全称 Multi-Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本,使得读写操作没有冲突

1、一行数据中的隐藏字段

在这里插入图片描述

2、undo log

回滚日志,在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候,产生的undo log日志只在回滚时需要,在事务提交后,可被立即删除。
而update、delete的时候,产生的undo log日志不仅在回滚时需要,mvcc版本访问也需要,不会立即被删除.

3、undo log 版本链

不同事务或相同事务对同一条记录进行修改,会导致该记录的undolog生成一条记录版本链表,链表的头部是最新的旧记录,链表尾部是最早的旧记录。
在这里插入图片描述

4、当前读

读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。对于我们日常的操作,如:select … lock in share mode(共享锁),select. . for update、update、insert、delete(排他锁)都是一种当前读。

5、快照读

简单的select(不加锁)就是快照读,快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读。

  • Read Committed:每次select,都生成一个快照读。
  • Repeatable Read:开启事务后第一个select语句才是快照读的地方。

6、readview

ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id.

事务中的隔离性是如何保证的呢?(你解释一下MVCC)
其中mycc的意思是多版本并发控制。指维护一个数据的多个版本,使得读写操作没有冲突,它的底层实现主要是分为了三个部分,第一个是隐藏字段,第二个是undolog日志,第三个是readView读视图
隐藏字段是指:在mysq!中给每个表都设置了隐藏字段,有一个是txid(事务id),记录每一次操作的事务id,是自增的:另一个字段是roll.pointer(回滚指针),指向上一个版本的事务版本记录地址
undo log主要的作用是记录回滚日志,存储老版本数据,在内部会形成一个版本链,在多个事务并行操作某一行记录,记录不同事务修改数据的版本,通过roll_pointer指针形成一个链表
readView解决的是一个事务查询选择版本的问题,在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据,不同的隔离级别快照读是不一样的,最终的访问的结果不一样。如果是r隔离级别,每一次执行快照读时生成ReadView,如果是rr隔离级别仅在事务中第一次执行快照读时生成ReadView,后续复用

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

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

相关文章

ElementUI元件库在Axure中使用

一、ElementUI元件库介绍 ElementUI 是一套为开发者、UI/UX设计师和产品经理准备的基于Vue 2.0的桌面端组件库。它以其优雅的设计和丰富的组件,极大地提升了Web应用的开发效率与用户体验。ElementUI的组件设计精致且符合现代UI规范,包括按钮、表单、弹窗…

指针(一)

指针变量:用来存放地址的变量。 这里讲一下为什么两位为一个字节:首先这是一个十六进制的地址,再十六进制中的F(15)用二进制表示是需要四位(1111),所以一位代表四个bit,两…

JavaEE 第7节 线程饥饿及其解决办法

目录 一、什么是线程饥饿? 二、线程饥饿的解决办法 *wait()与notify()方法解决线程饥饿 1、wait(等待) 2、notify(通知) 1)notify 2)notifyAll 3)关于wait方法的一些补充 1、wait的方法的三个功能是原子性的:…

【mysql 第二篇章】请求到真正执行 SQL 到底是一个怎么样的过程?

从用户调用到SQL执行的流程中间发生了什么事情 1、网络请求使用 线程 来处理,当数据库连接池中监听到有连接请求,这个时候会分配一个线程来处理。 2、SQL接口 负责接收 SQL 语句,当线程监听到有请求和读取数据的之后,将 SQL 语句…

鸿蒙(API 12 Beta3版)【视频解码】 音视频编码

开发者可以调用本模块的Native API接口,完成视频解码,即将媒体数据解码成YUV文件或送显。 当前支持的解码能力如下: 视频硬解类型视频软解类型AVC(H.264)、HEVC(H.265)AVC(H.264) 视频解码软/硬件解码存在差异,基于MimeType创建…

【实现100个unity特效之15】最简单的方法使用shader graphs实现2d非像素和像素树叶草的随风摇摆效果

文章目录 前言非像素树叶草飘动效果新建材质效果像素树叶草飘动效果参考完结 前言 本文只是实现一个简单版本的2d树叶草随风摇摆的效果,如果你想要实现更加复杂的效果,包括2d互动草,你可以参考我之前的文章: 【推荐100个unity插件…

Docker 入门全攻略:安装、操作与常用命令指南

目录 Docker 入门全攻略:安装、操作与常用命令指南 一、引言 二、Docker 下载与安装 2.1 Docker 的系统要求 2.2 安装步骤 ①对于 Windows 的安装指南 ②对于 macOS 的安装指南 ③对于 Linux 的安装指南 三、Docker 的基本概念 3.1 镜像(Image…

java消息队列ActiveMQ

安装 前置条件 activemq的运行依赖于jdk,需要提前安装jdk如果已经安装了jdk,需要根据jdk的版本来选择对应的版本进行安装activemq版本对应在官网上,使用java -version 看jdk的版本注意:jdk和mq的版本不一致会报错,电脑…

MySQL笔记-基础篇(一):查询

博客主页: 南来_北往 系列专栏:Spring Boot实战 MySQL是一种广泛使用的关系型数据库管理系统,它基于结构化查询语言(SQL)来管理和操作数据。下面将依次探讨MySQL查询的各个方面,确保理解扎实,能够在实…

计算机视觉——凸包计算

现在有一大堆点,然后你要找出一个可以围住这些点且面积最小的凸多边形,这个凸多边形称为凸包。 显而易见,如果要面积最小,那凸包的顶点势必得是这一大堆点的几个点,你也可以想成是用一条橡皮筋把这些点圈起来。 先把各…

Python中的 `break` 语句:掌握循环控制的艺术

Python中的 break 语句:掌握循环控制的艺术 下滑即可查看博客内容 🌈 欢迎莅临我的个人主页 👈这里是我静心耕耘深度学习领域、真诚分享知识与智慧的小天地!🎇 🎓 博主简介:985高校的普通本硕…

【大模型系列篇】论文解读:Transformer - Attention Is All You Need (2017)

Attention Is All You Need (Transformer) 是当今大模型初学者必读的一篇论文,已经有不少业内大佬都翻译解读过这篇论文,此处仅作为自己学习的记录。该论文是由谷歌机器翻译团队于2017年发表在NIPS ,提出了一个只基于attention的结构来处理序…

【iOS】OC关键字总结及底层原理(上)

目录 线程安全相关的关键字atomic&nonatomic 作用域相关的关键字static、extern、const&auto 读写权限相关和指定方法名的关键字内存管理相关的关键字(或方法)1. 引用计数的存储SideTableretain方法源码分析release方法源码分析dealloc方法源码分…

嵌入式初学-C语言-十九

指针的引入 为函数修改实参提供支持为动态内存管理提供支持为动态数据及结构提供支持为内存访问提供另一种途径 指针的概述 内存地址: 系统为了内存管理的方便将内存划分为一个个内存单元(一个内存单元占一个字节),并为每一个…

用Vue和Axios将数据库数据显示在前端页面

在本次实例中Vue只用在了前端部分&#xff0c;Axios用于向后端请求数据&#xff0c;我们这里要用到Ajax技术来访问后端数据。 HTML&#xff1a; <!DOCTYPE html> <html lang"en"> <head><meta charset"UTF-8"><meta name&quo…

全新博客X主题/简约WordPress主题模板/主题巴巴/免授权版源码+自适应设计

源码简介&#xff1a; 博客X这款超酷的Wordpress主题&#xff0c;是主题巴巴团队打造的设计杰作。想象一下&#xff0c;你的博客首页能展示那些炫酷的幻灯片置顶文章、还有各种精心策划的专题列表&#xff0c;这些内容模块的设计简直吸睛了&#xff0c;能让来访的用户眼前一亮…

数据结构和算法|递归算法那些事(递归算法的时间复杂度、尾递归优化、斐波那契数列)

对于文章的第一部分&#xff0c;递归算法的时间复杂度&#xff0c;来自于代码随想录文章:通过一道面试题目&#xff0c;讲一讲递归算法的时间复杂度&#xff01; 对于第二节尾递归优化来自于B站&#xff1a;尾递归优化&#xff1a;你的递归调用是如何被优化的&#xff1f; 文章…

XML(可扩展标记语言)

QDomDocument doc;QDomElement ss doc.createElement("root");//创建标签 //ss标签添加到文档对象doc.appendChild(ss);//doc.save()auto hero doc.createElement("hero");ss.appendChild(hero);hero.setAttribute("id",10086);//为hero添加属…

MySQL——数据表的基本操作(一)创建数据表

数据库创建成功后,就需要创建数据表。所谓创建数据表指的是在已存在的数据库中建立新表。需要注意的是&#xff0c;在操作数据表之前&#xff0c;应该使用 “ USE 数据库名 ” 指定操作是在哪个数据库中进行&#xff0c;否则会抛出 “ No database selected ” 错误。创建数据表…

Tomcat 使用和配置文件(详解)

一.tomcat 介绍 1. tomcat 概述 自从JSP发布之后&#xff0c;推出了各式各样的JSP引擎。Apache Group在完成GNUJSP1.0的开发以后&#xff0c;开始考虑在SUN的JSWDK基础上开发一个可以直接提供Web服务的JSP服务器&#xff0c;当然同时也支持 Servlet&#xff0c;这样Tomcat就诞…