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双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们