mysql闲谈

news/2024/4/17 6:53:28

如何定位慢查询
1、测试环境压测时,有的接口非常慢,响应时间超过2秒以上。当时系统部署了运维的监控系统Skywalking,在展示报表中可以看到是哪儿个接口慢,可以看到SQL具体执行时间。
2、如果没有类似的监控系统,在Mysql中也提供了慢日志查询功能,在mysql系统配置文件中开启慢日志的功能,设置超过多少时间记录到一个日志文件中,我记得配置的是2s,只要sql执行时间超过2s就会记录到日志文件中。

explain type
null(没有使用到表)、system(mysql内置表)、const(根据主键查询)、eq_ref(根据主键索引查询或唯一索引查询)、ref(索引查询)、range(走的索引but范围查询)、index(索引树扫描)、all(全盘扫描)

慢SQL如何分析
采用mysql自动执行计划explain来查看执行情况
通过key和key_len检查是否命中索引,如果本身已经添加了索引,可以判断索引是否失效
通过type关键字查看sql是否有进一步的优化空间,是否存在全索引或全盘扫描
通过extra建议判断,是否出现回表,如果出现可以尝试添加索引或修改返回字段

什么是索引
mysql高效访问数据的数据结构(有序)
提高检索数据的效率,降低数据库io成本(你需要全表扫描)
通过索引对数据进行排序,降低数据排序成本,降低CPU消耗

索引底层数据结构
Mysql的InnoDB引擎采用B+树的数据结构来存储索引
· 阶数更多,路径更短
· 磁盘读写代价B+树更低,非叶子节点只存储指针,叶子节点存储行数据
· B+便于扫库和区间查询,叶子节点是双向链表(B树非叶子和叶子都会存放数据)

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

回表查询
通过二级索引找到对应的主键值,到聚集索引中查找整行数据

覆盖索引
覆盖索引指的是查询使用了索引,返回的列,必须在索引中全部找到。
使用id查询,直接走聚簇索引,一次索引扫描,直接返回数据,性能高
如果返回的列没有创建索引,可能触发回表查询,尽量避免使用select*

Mysql超大分页怎么处理
问题:数据量较大,limit分页查询,需要对数据进行排序,效率低
解决方案:覆盖索引+子查询。先分页查询数据id字段,确定id之后使用子查询过滤,只查询这个id列表中的数据就可以了,查询id的时候,走的覆盖索引,提升效率。

select * 
from tb_sku t, (select id from tb_sku order by id limit 900000000, 10) a
where t.id = a.id;

索引创建原则
*数据量较大,且频繁查询的表
*常作为查询条件、排序、分组的字段
字段内容区分度高
内容较长,使用前缀索引
*尽量联合索引
*要控制索引数量
如果索引列不能存储null值,请在创建表时使用not null约束

什么情况下索引会失效
违反最左前缀法则,索引失效
索引abc_index:(a,b,c),只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用。其实这里说的有一点歧义,其实当where条件只有(a,c)时也会走,但是只走a字段索引,不会走c字段。
范围查询右边的列,索引失效
字符串类型的数值不加单引号,索引失效(类型转换)
以%开头的like模糊查询,索引失效
不要在索引列上进行运算操作,索引失效
索引包含有 NULL 值的列,索引失效(索引不会包含有 NULL 值的列)

sql优化
表设计优化,数据类型选择
索引优化,索引创建原则
sql语句优化,避免索引失效,避免使用select *
主从复制、读写分离、不让数据的写入影响读操作
分库分表
答:sql优化的话,建表使用索引,sql语句的编写、主从复制、读写分离,数据量比较大的话,可以考虑分库分表。建表的时候参考阿里开发手册《嵩山版》,比如,定义字段需要结合字段含义选择合适类型,如果是数值的话,像tinyint、int、bigint 根据实际情况选择。如果是字符串,char和varchar(可变长度)或者text类型。使用索引的时候,满足创建索引原则。sql语句,select 必须指明字段,不直接使用select * ,注意sql语句,避免索引失效写法,如果是聚合查询,尽量用union all 代替 union(多一层过滤,效率低)表关联尽量使用innerjoin 不要使用 left right ,必须使用的话最好小表驱动大表。

事务

特性
原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
A向B转账500元,转账成功,A扣500,B加500,原子性操作体现在要么都成功,要么都失败。
转账过程,数据要一致,A扣除500,B必须增加500
在转账过程,隔离性体现在不能受其他事物干扰
事务提交以后,要把数据持久化(落盘)

并发事务带来的问题
脏读:一个事物读到领一个事务没有提交的数据
不可重复读:一个事物前后读取同一条记录,两次读取数据不同
幻读:一个事物按照条件查询数据,没有对应数据行,在插入数据时,又发现这行数据存在。

解决问题:隔离级别 默认可重复读
(RU)未提交读 啥也没解决
(RC)读已提交 脏读
(RR)可重复读 脏读、不可重复读
(SB)串行化 脏读、不可重复读、幻读

undo log和redo log的区别
undo log:逻辑日志,记录与实际操作语句相反的操作。事务回滚时,通过你操作恢复原来的数据
redo log:记录数据页的物理变化,服务宕机可以用来同步数据
redo log 保证事务的持久性 ,undo log保证事务的 原子性一致性

事务中的隔离性是如何保证的
锁:排他锁,一个事务获取了一个数据行的排他锁,其他事物就不能在获取改行的其他锁
mvcc:多版本并发控制

MVCC
多版本并发控制:一个数据的多个版本,使得读写操作没有冲突。
底层实现分为三个部分:隐藏字段、undo log、readView
隐藏字段:DB_TRX_ID(记录每一次操作的事务id,自增)、DB_ROLL_PTR(回滚指针,指向上一个版本的事务版本记录地址)、DB_ROW_ID(隐藏主键)
undo log:
回滚日志—存储老版本数据
版本链—多个事务并行操作某一行记录,记录不同事物修改数据的版本,通过roll_pointer指针形成一个链表
readView:解决事务查询选择版本的问题,在内部定义了一些匹配规则和当前一些事务id判断该访问哪儿个版本的数据,不让的隔离级别快照读是不一样的,最终的访问的结果不一样。如果是RC隔离级别,每次之快照读生成readView,如果是RR隔离级别,仅在事务中第一次执行快照读时生成readView,后续复用。

主从同步原理
核心是二进制文件binlog(DDL数据定义语句和DML(增删改)语句)
1、Master主库在事务提交时,会把数据变更记录在二进制文件Binlog中
2、从库读取主库Binlog,吸入到从库的中继日志Relay log
3、从库重做中继日志中的事件,将改变反映它自己的数据

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


https://www.xjx100.cn/news/3366022.html

相关文章

网络原理 - HTTP / HTTPS(2)——http请求

目录 一、认识 “方法”(method) 1、GET方法 2、POST方法 (1)登录 (2)上传 (3)GET和POST使用习惯 3、GET方法和POST方法的区别 正确滴 关于一些网上的说法,错误滴…

*** ERROR L105: PUBLIC REFERS TO IGNORED SEGMENT

今天在使用KEIL编程的时候,出现了以下错误: *** ERROR L105: PUBLIC REFERS TO IGNORED SEGMENT 在不断检查代码逻辑和语法的情况下,并未发现任何错误。后来才发现是data空间已经不够用。在此记录一下解决方案。 出现以上错误的原因主要是…

全流程基于GIS、python机器学习技术的地质灾害风险评价与信息化建库实践应用

入门篇,ArcGIS软件的快速入门与GIS数据源的获取与理解;方法篇,致灾因子提取方法、灾害危险性因子分析指标体系的建立方法和灾害危险性评价模型构建方法;拓展篇,GIS在灾害重建中的应用方法;高阶篇&#xff1…

STM32学习笔记(11_2)- W25Q64简介和工作原理

无人问津也好,技不如人也罢,都应静下心来,去做该做的事。 最近在学STM32,所以也开贴记录一下主要内容,省的过目即忘。视频教程为江科大(改名江协科技),网站jiangxiekeji.com 本期学…

算法——逆波兰式

http://t.csdnimg.cn/Wg8vu 逆波兰式思路: 对于每个元素,它检查是否是一个操作符(“”、“-”、“*” 或 “/”)。如果是,它就从栈中弹出两个元素,执行相应的操作,然后将结果推回到栈中。如果…

day 1 将go基础知识复习一下

本文章主要是写自己在做这个项目时候遇到的一些困难,如果都是做这个项目的(后端),可以看看 这个是项目网址 gin-vue-admin : https://github.com/flipped-aurora/gin-vue-admin 在此表示对大神奇淼的敬佩 首先,我们…

hibernate OID映射对象标识符

OID映射对象标识符 OID存在的意义 关系型数据库通过主键来区分同一张表的不同数据,java语言使用内存地址来区分同一类的不同对象,hibernate则使用OID来同一两者之间的矛盾,在运行时,hibernate通过OID来维持java对象和数据库表中记…

解决Toad for Oracle显示乱中文码问题

更多ruoyi-nbcio功能请看演示系统 gitee源代码地址 前后端代码: https://gitee.com/nbacheng/ruoyi-nbcio 演示地址:RuoYi-Nbcio后台管理系统 http://122.227.135.243:9666/ 更多nbcio-boot功能请看演示系统 gitee源代码地址 后端代码&#xff1a…