mysql闲谈

2024-04-03 09:36
文章标签 mysql database 闲谈

本文主要是介绍mysql闲谈,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

如何定位慢查询
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和网络连接数
垂直分表:冷热数据分离,多表互不影响

这篇关于mysql闲谈的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/872554

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七