MySQL原理与实战
丁奇老师的专栏是我读过关于MySQL最棒的课程。
有感而发,记录一下自己的理解。
醋酸菌HaC
这个作者很懒,什么都没留下…
展开
-
41 | 复制表
建表:create database db1;use db1;create table t(id int primary key, a int, b int, index(a))engine=innodb;delimiter ;; create procedure idata() begin declare i int; set i=1; while(i&...原创 2020-03-23 18:04:27 · 229 阅读 · 0 评论 -
40 | insert 语句的锁
有一些特殊的insert语句,在执行过程中需要加锁,本文针对这些特殊都insert语句进行展开。insert … select建表:CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (...原创 2020-03-23 15:27:12 · 467 阅读 · 0 评论 -
39 | 自增主键不连续
自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑。建表:CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c...原创 2020-03-20 14:50:30 · 212 阅读 · 0 评论 -
38 | Memory引擎使用场景
内存表的数据组织结构建表:create table t1(id int primary key, c int) engine=Memory;create table t2(id int primary key, c int) engine=innodb;insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),...原创 2020-03-19 14:27:24 · 484 阅读 · 0 评论 -
37 | 内部临时表
使用临时表的情况:sort buffer、内存临时表和 join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据,以辅助 SQL 语句的执行的。其中,我们在排序的时候用到了 sort buffer,在使用 join 语句的时候用到了 join buffer。1. union 执行流程建表;create table t1(id int primary key, a int,...原创 2020-03-18 15:19:14 · 330 阅读 · 0 评论 -
36 | 临时表和临时表
内存表,指的是使用 Memory 引擎的表,建表语法是 create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。临时表,可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表,默认是MyISAM 引擎,写数据的时候是写到磁盘上的。当然,临时表也可以使用 Memory 引擎。临时表特点:...原创 2020-03-16 15:47:54 · 212 阅读 · 0 评论 -
35 | join语句优化
一般来说,使用join语句,会用到两种算法,分别是Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。在使用 NLJ 算法的时候,效果还是不错的,比通过应用层拆分成多个语句然后再拼接查询结果更方便,而且性能也不会差。BNL 算法在大表 join 的时候性能就差多了,比较次数等于两个表参与 join 的行数的乘积,很消耗 CPU ...原创 2020-03-06 11:08:12 · 264 阅读 · 0 评论 -
34 | join语句的使用
本文围绕连个问题展开:DBA不让使用join,使用join有什么问题?不同的大小的表,如何选择驱动表?创建表t1和t2:CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (...原创 2020-03-04 17:01:20 · 354 阅读 · 0 评论 -
33 | 我查这么多数据,会不会把数据库内存打爆?
本文围绕一个问题展开:假如主机内存只有 100G,现在要对一个 200G 的大表做全表扫描,会不会把数据库主机的内存用光了?原创 2020-03-03 19:32:06 · 309 阅读 · 0 评论 -
32 | kill不掉的语句
在 MySQL 中有两个 kill 命令:kill query + 线程 id 表示终止这个线程中正在执行的语句。kill connection + 线程 id 这里 connection 可缺省,表示断开这个线程的连接,当然如果这个线程有语句正在执行,也是要先停止正在执行的语句的。其实大多数情况下,kill query/connection 命令是有效的。比如,执行一个查询的过程中...原创 2020-02-29 16:36:03 · 207 阅读 · 0 评论 -
31 | 误删数据
误删数据的分类:使用 delete 语句误删数据行;使用 drop table 或者 truncate table 语句误删数据表;使用 drop database 语句误删数据库;使用 rm 命令误删整个 MySQL 实例。误删行如果是使用 delete 语句误删了数据行,可以用 Flashback 工具通过闪回把数据恢复回来。Flashback 恢复数据的原理,是修改 bin...原创 2020-02-28 17:25:40 · 203 阅读 · 1 评论 -
30 | 加锁的demo探析
原则:原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。原则 2:查找过程中访问到的对象才会加锁。优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。一个 bug:唯一索引...原创 2020-02-27 16:34:42 · 243 阅读 · 0 评论 -
29 | 如何判断一个数据库是不是出问题了?
前面提到在一主一备的双 M 架构里,主备切换只需要把客户端流量切到备库;而在一主多从架构里,主备切换除了要把客户端流量切到备库外,还需要把从库接到新主库上。主备切换有两种场景,一种是主动切换,一种是被动切换。而其中被动切换,往往是因为主库出问题了,由 HA 系统发起的。如何判断主库出问题?select 1 判断实际上,select 1 成功返回,只能说明这个库的进程还在,并不能说明...原创 2020-02-26 15:37:30 · 255 阅读 · 0 评论 -
28 | 读写分离有哪些坑?
上一篇说到了一主多从的结构以及切换流程。读写分离读写分离结构:读写分离的主要目标就是分摊主库的压力。图 1 中的结构是客户端(client)主动做负载均衡,这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说,由客户端来选择后端数据库进行查询。还有一种架构是,在 MySQL 和客户端之间有一个中间代理层 proxy,客户端只连接 proxy, 由 proxy 根据请求类型和上下...原创 2020-02-25 18:45:14 · 296 阅读 · 0 评论 -
27 | 主库出问题了,从库怎么办?
一主多从结构:图中,虚线箭头表示的是主备关系,也就是 A 和 A’互为主备, 从库 B、C、D 指向的是主库 A。一主多从的设置,一般用于读写分离,主库负责所有的写入和一部分读,其他的读请求则由从库分担。主库发生故障,主备切换的结果:相比于一主一备的切换流程,一主多从结构在切换完成后,A’会成为新的主库,从库 B、C、D 也要改接到 A’。正是由于多了从库 B、C、D 重新指向的这个过程,...原创 2020-02-24 16:26:09 · 196 阅读 · 0 评论 -
26 | 备库为什么会延迟好几个小时?
如果备库执行日志的速度持续低于主库生成日志的速度,那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说,备库很可能永远都追不上主库的节奏。主备同步流程图:在主库上,影响并发度的原因就是各种锁了。由于 InnoDB 引擎支持行锁,除了所有并发事务都在更新同一行(热点行)这种极端场景外,它对业务并发度的支持还是很友好的。所以,你在性能测试的时候会发现,并发压测线程 32 就比单线...原创 2020-02-22 15:48:12 · 239 阅读 · 0 评论 -
25 | MySQL是怎么保证高可用的?
正常情况下,只要主库执行更新生成的所有 binlog,都可以传到备库并被正确地执行,备库就能达到跟主库一致的状态,这就是最终一致性。MySQL 主备切换流程 – 双 M 结构:主备延迟主备切换可能是一个主动运维动作,比如软件升级、主库所在机器按计划下线等,也可能是被动操作,比如主库所在机器掉电。数据同步的有关时间点:主库 A 执行完成一个事务,写入 binlog,我们把这个时刻记为 ...原创 2020-02-22 11:35:53 · 258 阅读 · 0 评论 -
23 | MySQL是怎么保证数据不丢的?
binlog 的写入机制其实,binlog 的写入逻辑比较简单:事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。一个事务的 binlog 是不能被拆开的,因此不论这个事务多大,也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。系统给 binlog cache 分配了一片内存,每个线程一个...原创 2020-02-09 14:56:51 · 143 阅读 · 0 评论 -
21 | 为什么我只改一行的语句,锁这么多?
原创 2020-02-05 15:38:54 · 246 阅读 · 0 评论 -
20 | 幻读
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),(10,10,...原创 2020-02-02 19:07:52 · 121 阅读 · 0 评论 -
19 | 为什么我只查一行的语句,也执行这么慢?
第一类:查询长时间不返回1. 等 MDL 锁用 show processlist 命令查看 Waiting for table metadata lock出现这个状态表示的是,现在有一个线程正在表 t 上请求或者持有 MDL 写锁,把 select 语句堵住了。场景:2. 等 flushflush tables t with read lock;flush tables wit...原创 2020-01-31 14:30:03 · 179 阅读 · 0 评论 -
18 | 为啥SQL逻辑相同,性能差异大
第一种:隐式类型转换如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1;如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。情况一:此时cg_bidid的类型是bigint在 MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。EXPLAIN SELECT * FROM cg_bid WHERE id ="104";EXPLAIN SE...原创 2020-01-29 12:12:29 · 268 阅读 · 0 评论 -
MySQL关联大表与小表
老师,有道面试题困扰了很久,求指教!题目是这样的,a表有100条记录,b表有10000条记录,两张表做关联查询时,是将a表放前面效率高,还是b表放前面效率高?网上各种答案,但感觉都没有十分的说服力,期待老师的指点!作者回复: (这题目改成100万禾10000万比较好) 如果是考察语句写法,这两个表谁放前面都一样,优化器会调整顺序选择合适的驱动表;如果是考察优化器怎么实现的,你可以这么想,每次...原创 2020-01-28 23:59:29 · 7274 阅读 · 0 评论 -
03 | 事务隔离
MySQL是一个支持多引擎的系统,但并不是所有的引擎都支持事务,MyISAM引擎是不支持事务的。事务,即 ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性),这篇文章针对InnoDB引擎的“隔离性”展开讲述。当数据库上有多个事务同时执行的时候,就可能出现 脏读(dirty read)、不可重复读(non-repea...转载 2019-08-03 09:56:56 · 99 阅读 · 0 评论 -
07 | 锁
MySQL中,并不是所有的引擎都支持行锁的,myISAM不支持行锁,InnoDB是支持的。顾名思义,行锁就是针对数据表中一行的数据进行锁定,例如事务A更新了一行,这个时候事务B也要更新同一行,则必须等事务A完成后才能更新。...原创 2019-08-01 00:02:39 · 105 阅读 · 0 评论