Mysql
文章平均质量分 89
mysql相关知识
zuodaoyong
这个作者很懒,什么都没留下…
展开
-
缓冲池(buffer pool)详解
InnoDB存储引擎是以页为单位来管理空间的,我们进行的增删改查操作其实本质都是在访问页面(读页面,写页面,创建新页面)等,磁盘IO需要消耗的时间很多,而在内存中进行操作,效率会高,为了能让数据表或者索引中的数据随时被使用,DBMS会申请占用内存来作为数据缓冲池,在真正访问页面之前,需要把磁盘上的页缓存到内存中的buffer pool中之后才可以访问。这样做的好处可以让磁盘活动量最小,从而减少与磁盘直接进行IO,这种策略可以提升SQL语句的查询性能。如果索引的数据在缓冲池中,那么访问的成本就会降低很多。翻译 2023-04-01 18:33:22 · 2973 阅读 · 0 评论 -
SQL执行流程详解
既然是缓存,缓存失效也要考虑,MYSQL的缓存系统会检测涉及到的每张表,只要该表的结构或者数据被修改,出现如下操作(insert,update,delete,truncat table,alter table,drop table,drop database等)时所有缓存查询将变为无效,对于更新压力大的数据库来说,查询缓存命中率更低。key是查询语句,value是查询的结果,如果查询能够直接在缓存中找到key,那么这个value就会被直接返回给客户端,如果语句不在查询缓存中,就会继续后面的执行阶段。翻译 2023-04-01 16:33:05 · 2266 阅读 · 0 评论 -
主从复制一之概述
大部分应用对数据库而言都是“ 读多写少 ”,也就说对数据库读取数据的压力比较大,有一个思路就是采用数据库集群的方案,做 主从架构 、进行 读写分离 ,这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置,毕竟设置架构本身是有成本的。如果目的在于提升数据库高并发访问的效率,那么首先考虑的是如何 优化SQL和索引 ,这种方式简单有效;其次才是采用 缓存的策略 ,比如使用 Redis将热点数据保存在内存数据库中,提升读取的效率;最后才是对数据库采用 主从架构 ,进行读翻译 2022-05-15 22:20:18 · 556 阅读 · 0 评论 -
日志系列二之二进制日志原理篇
一、写入机制binlog的写入时机也非常简单,事务执行过程中,先把日志写到 binlog cache ,事务提交的时候,再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache。可以通过binlog_cache_size参数控制单个线程binlog cache大小,如果存储内容超过了这个参数,如果存储内容超过这个参数,会存储到磁盘中。write和fsync的时翻译 2022-05-08 22:19:28 · 351 阅读 · 0 评论 -
日志系列一之通用查询日志(general query log)
一、通用查询日志用来记录用户的所有操作 ,包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时,查看通用查询日志,还原操作时的具体场景,可以帮助我们准确定位问题。问题场景在电商系统中,购买商品并且使用微信支付完成后,却发现支付中心的记录并没有新增,此时用户再次使用支付宝支付,就会出现重复支付的问题。但是当天去数据库中查询数据的时候,就会发现只有一条记录存在,那么此时给到的现象就是只有一条支付记录,但是用户却支翻译 2022-05-01 12:23:11 · 783 阅读 · 0 评论 -
多版本并发控制(MVCC)
一、MVCCMVCC (Multiversion Concurrency Control),多版本并发控制。顾名思义,MVCC 是通过数据行的多个版本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之,就是为了查询一些正在被另一个事务更新的行,并且可以看到它们被更新之前的值,这样在做查询的时候就不用等待另一个事务释放锁。二、 快照读与当前读MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能,用更好的方式去处理 读-写翻译 2022-04-30 21:10:35 · 4176 阅读 · 2 评论 -
锁系列五之锁内存结构和锁监控
前面文章说对一条记录加锁的本质是在内存中创建一个锁结构与之关联,那么是不是一个事务对多条记录加锁,就要创建多个锁结构?比如下面的sqlselect * from user lock in share mode理论上创建多个锁结构没问题,但是如果一个事务获取10000条记录的锁,生成10000个锁结构也太浪费了。所以决定在对不同记录加锁时,如果符合下面的条件的记录会放到一个锁结构中。(1)在同一个事务中进行加锁操作(2)被加锁的记录在同一个页中(3)加锁的类型是一样的(4)等待状态翻译 2022-04-23 21:58:11 · 340 阅读 · 0 评论 -
锁系列四之行锁
行锁,也称为记录锁,mysql服务层没有实现行锁机制,行锁只存在存储引擎层实现。优点:锁粒度小,发生锁冲突的概率低,可以实现的并发度高。缺点:对于锁的开销比较大,加锁会比较慢,容易出现死锁的情况。案例一、记录锁(Record Locks)记录锁也就是仅仅把一条记录锁上,Lock_REC_NOT_GAP,比如把id=8的记录加上一个记录锁的示意图如下。记录锁是有S锁和X锁之分的,称之为 S型记录锁 和 X型记录锁(1)当一个事务获取了一条记录的S型记录锁后,其他事务也可..翻译 2022-04-09 16:32:57 · 4832 阅读 · 0 评论 -
事务系列三事务日志
事务有4种特性:原子性、一致性、隔离性和持久性。事务的隔离性由 锁机制 实现。事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。(1)REDO LOG 称为 重做日志 ,提供再写入操作,恢复提交事务修改的页操作,用来保证事务的持久性。redo log是存储引擎(innodb)生成的日志,记录的是物理级别上的页修改操作。比如页号xxx,偏移量yyy写入了zzz数据,主要为了保证数据的可靠性。(2)UNDO LOG 称为 回滚日志 ,回滚行记录到某个特定版本,用来保证事翻译 2022-03-13 16:09:34 · 1508 阅读 · 0 评论 -
事务系列二隔离级别
MySQL是一个 客户端/服务器 架构的软件,对于同一个服务器来说,可以有若干个客户端与之连接,每个客户端与服务器连接上之后,就可以称为一个会话( Session )。每个客户端都可以在自己的会话中向服务器发出请求语句,一个请求语句可能是某个事务的一部分,也就是对于服务器来说可能同时处理多个事务。事务有 隔离性 的特性,理论上在某个事务 对某个数据进行访问 时,其他事务应该进行 排队 ,当该事务提交之后,其他事务才可以继续访问这个数据。但是这样对 性能影响太大 ,我们既想保持事务的隔离性,又想让服务器在处理翻译 2022-03-13 09:23:13 · 333 阅读 · 0 评论 -
事务系列一事务基础概述
事务定义一组逻辑操作单元,使数据从一种状态变换到另一种状态一、存储引擎支持情况二、事务的ACID特性1、原子性(atomicity)原子性是指事务是一个不可分割的工作单位,要么全部提交,要么全部失败回滚。2、一致性(consistency)一致性是指事务执行前后,数据从一个合法性状态变换到另外一个合法性状态 。这种状态是语义上的而不是语法上的,跟具体的业务有关。通俗的说,这种状态是由自己定义的(比如要满足现实世界中的约束),满足这个状态,数据就是一致的,不满足这个状态,数据翻译 2022-03-10 00:31:56 · 317 阅读 · 0 评论 -
锁系列三之表锁
表锁(Table Lock)是mysql中最基本的锁策略,并不依赖存储引擎,表锁是开销最小的策略(因为粒度比较大)。由于表锁是锁住整张表,所以可以避免死锁。不过最大的负面问题是锁竞争概率最高。并发性最差。1、表级别的S锁、X锁在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时,InnoDB存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时,其他事务对这个表并发执行...翻译 2022-01-19 01:02:35 · 2077 阅读 · 0 评论 -
锁系列二之读写锁
从数据操作的类型划分对于 InnoDB 引擎来说,读锁和写锁可以加在表上,也可以加在行上。一、读锁也称为 共享锁 、英文用 S 表示。针对同一份数据,多个事务的读操作可以同时进行而不会互相影响,相互不阻塞的。二、写锁也称为 排他锁 、英文用 X 表示。当前写操作没有完成前,它会阻断其他写锁和读锁。这样就能确保在给定的时间里,只有一个事务能执行写入,并防止其他用户读取正在写入的同一资源。1、锁定读在采用加锁方式解决脏读,不可重复读,幻读这些问题的时候,读取一条记录时需要获取该记..翻译 2022-01-19 00:52:54 · 1112 阅读 · 0 评论 -
锁系列一之概述
一、MySQL并发事务访问相同记录1、读-读情况读-读 情况,即并发事务相继 读取相同的记录 。读取操作本身不会对记录有任何影响,并不会引起什么问题,所以允许这种情况的发生。2、写-写情况即并发事务相继对相同的记录做出改动在这种情况下会发生 脏写 的问题,任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时,需要让它们 排队执行 ,这个排队的过程其实是通过 锁 来实现的。这个所谓的锁其实是一个 内存中的结构 ,在事务执行前本来是没有锁的,也就是说一开始是没有翻译 2022-01-19 00:52:31 · 136 阅读 · 0 评论 -
MVCC解析
一、Undo Log介绍数据库事务开始之前,会将要修改的记录存放到Undo日志里,当事务回滚时或者数据库奔溃时,可以利用Undo日志,撤销未提交事务对数据库产生的影响。Undo Log属于逻辑日志,记录一个变化过程,例如执行一个delete,undolog会记录一个insert。执行一个update,undolog会记录一个相反的update。1、产生时机Undo Log在事务开始前产生2、销毁时机事务提交时,并不会立刻删除Undo Log。InnoDB会将该事务对应的undo log放翻译 2021-10-01 22:22:13 · 79 阅读 · 0 评论 -
MySql 之 left join注意点
left join在我们使用mysql查询的过程中可谓非常常见,比如博客里一篇文章有多少条评论、商城里一个货物有多少评论、一条评论有多少个赞等等。但是由于对join、on、where等关键字的不熟悉,有时候会导致查询结果与预期不符。假设有一个表classes,存了所有的班级;有一个表students,存了所有的学生,具体数据如下:那么现在有两个需求:1、找出每个班级的...翻译 2020-03-05 22:19:08 · 436 阅读 · 1 评论 -
Mysql查询优化
一、小表驱动大表1、当B表的数据集小于A表时,用in 优于existsselect * from A where id in (select id from B)等价于 for select id from B for select * from A where A.id=B.id2、当A表的数据集小于B表,用exists优于insele...翻译 2019-12-22 18:38:35 · 141 阅读 · 0 评论 -
Mysql之索引失效
一、数据准备建立一个index_test库:CREATE DATABASE index_test;在index_test库中创建一张user表:USE index_test;CREATE TABLE `user`( `id` BIGINT, `name` VARCHAR(50), `age` INT(4), `gender` VARCHAR(4),...原创 2019-12-22 11:49:15 · 115 阅读 · 0 评论 -
研磨Mysql之索引
1、索引定义索引是帮助Mysql高效获取数据的数据结构,即排好序的快速查找数据结构。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。2、索引的优势和劣势优势:(1)提高数据检索的效率,降低数据库的IO成本 (2)降低数据排序的成本,降低cpu的消耗劣势:(1...原创 2019-10-16 22:45:25 · 152 阅读 · 0 评论 -
研磨Mysql之SQL的JOIN
1、inner joinSELECT <select_list>FROM tableA aINNER JOIN tableB bON a.key=b.key2、left joinSELECT <select_list>FROM tableA aLEFT JOIN tableB bON a.key=b.key3、right join...原创 2019-10-02 21:59:03 · 114 阅读 · 0 评论 -
研磨Mysql之SQL的执行顺序
手写顺序:SELECT DISTINCT<select_list>FROM<left_table><join_type>JOIN <right_table> ON <join_condition>WHERE<where_condition>GROUP BY<group_by_list>...翻译 2019-10-02 21:37:23 · 97 阅读 · 0 评论 -
研磨Mysql之逻辑架构
mysql和其他数据库相比,mysql的架构可以在多种不同场景中应用并发挥良好的作用。插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离,业务可以根据需求下选择合适的存储引擎。(1)连接层最上层是一些客户端和连接服务,包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、相关安全方案。在该层上...翻译 2019-09-24 07:19:57 · 93 阅读 · 0 评论 -
研磨Mysql之Linux版安装
安装Linux版本Mysql步骤:(1)打开官网下载Mysql(rpm版本)(2)检查当前系统是否安装过mysql rpm -qa|grep -i mysql如果安装过会显示下图(3)卸载mysql 1)停止mysql服务、删除之前安装的mysql 2)rpm -e --noscripts MySQL-client-5.6.24-1.el6....原创 2019-09-20 07:22:39 · 165 阅读 · 0 评论