[mysql]mysql常识-1

[58架构师]的mysql入门好文章

InnoDB，快照读，在RR和RC下有何差异

innodb并发为啥可以很高

• 锁控制得好
  • MVCC – 读快照 — 一致性不加锁的读（Consistent Nonlocking Read）
• 技术上如何控制并发
  • 加锁
  • 多版本
• 提高并发的演进思路
  • 普通锁，本质是串行执行
  • 读写锁，可以实现读读并发
  • 数据多版本，可以实现读写并发，【但没有 写写并发的说法，不要闹笑话】

并发插入使用意向锁?

插入InnoDB自增列，居然是表锁

InnoDB行锁，如何锁住一条不存在的记录？

-37.MHA

TODO

-36.为什么表数据删掉一半，表文件大小不变

• delete只是将记录标记为“已删除”，并不会将记录从磁盘上物理删除
• delete的记录对应的数据页可以被复用。如果可被复用，但是还没有被复用，就成了空洞。除了删除，插入也会形成空洞，比如数据随机插入，而不是按索引递增插入，可能造成页分裂，同时留下“空洞”
• 那如何收缩表空间呢：
  • 重建表 alter table t engine=innodb ; mysql5.6+版本后这个操作是online ddl；也就是说不会阻塞住增删改。
    主要原理是在重建时，在生成临时文件的同时还会记录增删改操作到row log ；临时文件生成完后，再将row log应用在临时文件上。
  • analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
  • optimize table t 等于 recreate+analyze

see also

【页分裂、页合并】
在一个多链表链接的多个数据页里面，页分裂会把一个主键较大的值挪动到新的的数据页，而新插入的主键较小的值会挪动到之前的数据页。页分裂影响了数据页的空间利用率, 而且在分裂过程中, 性能也会有所影响.
若相邻两个数据页因为删除导致利用率很低后, 那么会将这两个数据页的数据合并到一个数据页中, 这个过程被称为【页合并】，即页分裂的逆过程

-35. N种锁

• (1)共享/排它锁(Shared and Exclusive Locks)

• (2)意向锁(Intention Locks) 注意：意向锁是表级别锁

  • 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
  • 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；
  • 也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

    //先在表上加上意向共享锁，然后对读取的记录加共享锁
    select ... lock in share mode;
    
    //先表上加上意向独占锁，然后对读取的记录加独占锁
    select ... for update;
    

  意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

  如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

  那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

  所以，意向锁的目的是为了快速判断表里是否有记录被加锁

  see also 意向锁

  • (3)记录锁(Record Locks)
  • (4)间隙锁(Gap Locks)
  • (5)临键锁(Next-key Locks)
  • (6)插入意向锁(Insert Intention Locks)
    • 注意 插入意向锁是行级锁、特殊的间隙锁
    • 多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。
      插入意向锁 see also
  • (7)自增锁(Auto-inc Locks)
  • (8) 全局锁(Flush tables with read lock (FTWRL))
  • (9) 表级别锁
    • 表锁(lock tables...read/write)
    • MDL(metadata lock)
    • 自增锁也是表级别锁
    • 意向锁也是表级别锁

-34. 如何理解ACID中的C

• ACID中的A 、I 、D 是数据库自身特性，但是 C 并不是，而是个业务层的概念。
• 经典案例：“转账前后账户总额保持不变”，这个规定只对于「转账」这个特定的业务场景有效。
  如果换一个业务场景，「一致」的概念就不是这样规定了。ACID中的「一致性」，其实是体现了业务逻辑上的合理性。

see details（十分优秀的“一致性”的解释）

p.s. 要区分的 Consistency （一致性）和 Consensus（共识）的区别，有的文章将二者混淆。

-33. RR解决幻读了吗？

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，
  事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事
  务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

• 针对当前读（select ... for update等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读
  ，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

但是，有比较特殊的场景：

• 对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录（也就是事务A中有过当前读），那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。

• 对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

see also

-32.自增主键为何不连续

• 自增主键可以让主键索引尽量保持递增顺序插入，避免了 页分裂，索引更为紧凑；但innodb不能保证自增主键是连续的
• 8.0之前，innodb 自增值保存在内存里；8.0之后，增加了自增值持久化的能力（保存到了redo log）：如果mysql奔溃恢复，自增值会从奔溃前的值起算。
• 不连续的可能原因：
  • 唯一键冲突
  • 事务回滚
  • 批量插入数据，比如 insert … select、replace … select 和 load data ，可能会申请多余的Id,但是不使用
• 生产上批量插入数据的场景，从并发性能和数据一致性的角度看，推荐设置innodb_autoinc_lock_mode=2 ，并且 binlog_format=row。

see also

-31. 两阶段锁协议 V.S. 两阶段提交协议

1） 两阶段锁协议：

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

2）两阶段提交协议：
更新时的事务提交过程，将新行更新到内存后

• 先写入redo log，进入prepare阶段
• 写binlog
• 提交事务，事务处于commit状态

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致，所以将redo log的写入拆成了两个阶段。

see also

-30. 脏页、干净页

数据文件 + redo log + 内存 三者可能不一致。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

mysql可能“抖”一下，可能的情况:

• 1. redo log日志满了，需要把redo log对应内存脏页flush一部分到数据磁盘中（注意并不是redo log）。这时候mysql更新全部会堵住（因为redo log满了不能更新了
• 2. buffer pool满了，但是查询的数据不在内存中，就需要从磁盘中查询数据，然后 在内存中找到最久未使用的数据页淘汰掉，把新查询的数据写入进去。（如果要淘汰的数据页中包含过多脏页，那么就需要flush这些脏页到磁盘中，这样也就会造成查询时间长）

see also

-29.change buffer

• 只有普通索引才走change buffer，唯一索引不走。【重要！！！】但无论change buffer走不走，redo log该记录的还是会记录。change buffer和redo log没有关联。
• change buffer 是buffer pool的一部分，通过innodb_change_buffer_max_size可以配置其占buffer pool的比例。change buffer虽称buffer,却也可以落盘持久化
• change buffer本质是将更新的动作“延迟”了一些，当客户端需要查数据页的时候，还是会将change buffer应用到数据页（即merge）。“延迟”动作避免了磁盘IO，提高了效率。更新越频繁，“延迟”带来的效果更好。所以对于读少写多的场景，change buffer会带来可观的收益。
• redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer主要节省的则是随机读磁盘的 IO 消耗。

see also

-28. innodb如何创建“快照”

RR下，事务启动会“拍了个快照”，这个快照是基于整库的。听起来不可思议，因为整库数据以G 计算大小，innodb不可能来个RR事务就复制这么多数据出来。

事实上，“快照”是基于行数据的多版本和 版本的row_trx_id实现的，是个逻辑上的通俗表达。

• 事务启动时，innodb会把当前活跃事务（即：已创建但是还没有提交的事务）trx_id用数组记录下来。 取该数组最小trx_Id为低水位，最大trx_id + 1为高水位。这里的数组 和 高低水位就是我们常说的 ReadView
• 某行数据的多个版本，多个row_trx_id和高低水位对比形成三种情况：
  • 1）row_trx_id小于低水位：是已提交的，对当前事务可见
  • 2）row_trx_id 大于高水位：这是未来的事务，肯定不能看到未来，对当前事务不可见
  • 3）row_trx_id 位于高低水位之间：
    • a. row_trx_id 在数组中：未提交，对当前事务不可见
    • b. row_trx_id 不在数组中：已提交，对当前事务可见

RR中，一个事务执行过程中，该事务之外的其他事务的更改属于 2） 或者 3.a ，这样就实现了当前事务“看到的”是“静态的”了。

see also

-27. 一致性读和当前读

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。

• 普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
  • 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
  • 对于读提交，查询只承认在语句启动前就已经提交完成的数据；
• 而 当前读，总是读取已经提交完成的最新版本 。

see also

-26.begin=start trx V.S. start transaction with consistent snapshot

• 方式一：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。
• 方式二：如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；
第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

例：下面语句RR下，A2 是 可以看到 B2所添加的行的。而且，场景一和场景二效果是一致的。
在RC下，A2 也可看到 B2 添加的行，场景一和场景二效果也是一致的。

场景一：
A1: start transaction;
         B1: start transaction;
         B2: insert into t values (4, wangwu);
         B3: commit;
A2: select * from t;

场景二：
		B1: start transaction;
A1: start transaction;
         
         B2: insert into t values (4, wangwu);
         B3: commit;
A2: select * from t;

-25.mysql如何不丢数据

• 只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后数据可以恢复

• mysql crash-safe语义：

  • 如果客户端收到事务成功的消息，事务就一定持久化了；
  • 如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；
  • 如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。

• 通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。听起来每个事务两次刷盘会导致IO开销大，但 组提交(group commit) 机制会大大减少IO次数。

  see also

-24.长字符串如何加索引

• 1）直接加索引可能占用磁盘空间大，且由于能缓冲的索引少，IO概率增大而影响搜索效率
• 2）加前缀索引（取字符串的左起部分字符）【不支持index covering】
• 3）字符串倒序保存+前缀索引 【不支持范围查询，只支持等值查询】
• 4）添加hash字段+并在hash字段上加索引 【不支持范围查询，只支持等值查询】
• 5）字段拆分（一个字段可拆分为两个以上）

see also

-23.业务用读提交，可是逻辑备份的时候，mysqldump 为什么要把备份线程设置成可重复读

当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的

-22. 为啥mysql 默认RR 而不是RC？

历史原因：为了兼容历史上的那种statement(区别row）格式的bin log，必须使用RR保证一致性视图(?)，否则数据库主从数据会不一致。

see also

-21.间隙锁导致死锁

RR 隔离级别下（注意RC下不会重现），下图场景会导致死锁。间隙锁的存在导致同样的语句锁住更大的范围，这其实是影响了并发度的。

目前主流的MySQL配置是 binlog_format=row + rc 。

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,10),(15,15,15),(20,20,20),(25,25,25);

• 两个间隙锁之间是不冲突的。SessionA 和 B都拿到了 (5,10) gap lock。
• SessionB的 insert into 语句尝试拿 SessionA的 (5,10) gap lock，由于SessionA 没有提交事务，所以SessionB阻塞；
• 同理，SessionA 也阻塞在了SessionA；
• SessionA、B互等对方，形成死锁

-20. select for update

select ...for update 【写】 和 update xx 有着相同的锁语义

TODO: V.S. select in share mode

-19. 查一行也很慢

案例：mysql> select * from t where id=1; 其中id是主键，长时间不返回

原因1： 在等表锁

• show processlist 【有些锁住的线程是 sleep 状态，不好找，可用select * from sys.schema_table_lock_waits】
• 可能在等MDL锁

原因2：在等flush tables
flush tables t with read lock 这个命令是备用工具用于获取一致性备份（数据和Binlog位点匹配），属于超级重量级的操作，因其需要持有两把全局的MDL锁，并且还需要关闭所有表对象，极易导致库hang住。

原因3：等行锁

使用语句 select * from t sys.innodb_lock_waits where locked_table=??? 查到谁占着行锁。

原因四： 查询就是慢
mysql> select * from t where c=50000 limit 1;
主键id, 列c , 每一行id 和 c的值相等，注意， t.c 没有索引，那么这个SQL只能走主键id顺序扫描，所以需要扫描50000行。50000行未必就慢，但坏SQL并不是一定是慢SQL，随着数据量的增加，问题迟早暴露

原因五：查询时需读undo log链，反而比当前读慢

-18. sql 操作符执行顺序

select id,name from t where month(col_modified) >2 order by id desc limit 10

先 where, 再order by ，最后取limit。不需死记，简单想想也知道，必须要通过where查出来符合条件的数据集再排序才有意义。

-17.where 加函数为啥"不走索引"

select id,name from t where month(col_modified) =1 ，其中col_modified 有索引

这个不能说“不走索引“，而是对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，但是， 只是放弃走树搜索而已，并不是"不走索引"。这个sql 还是走col_modified索引，只不过全索引扫描而已。

类似的，索引字段的字符集隐式转换，索引字段类型的转换都可能导致树搜索失效。

特别的，mysql优化器有偷懒嫌疑，select * from t where id+10=20 这样的语句也会导致树搜索失效！

see also

-16. limit 深度分页

表 account.update_time有二级索引idx_update_time，id为自增主键，则执行sql:
select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

• 1. 通过update_time二级索引树，找到满足条件的记录id (100000+10行)
• 2. 根据id 回表
• 3. 扫描前100000行，扔掉，保留10行，返回客户端

sql变慢二个原因：

• 需要回表 100000+10次
• 需要扫描 100000+10行

解决思路：

• 查其他条件(update_time)转化为查id，即先查符合条件的id,再根据id select
  这里的两步，可以是利用sql子查询， 或 inner join ，也可以在应用代码中实现
• 结合业务实际，每次分页查询可以获取上一次查询的最大offset ，作为当前分页的起始条件

see also
see also :提出了归并排序的查询思路

-15.为什么DBA会紧张mysql连接数过多

长连接过多会导致mysql的内存使用上涨很快：mysql执行过程中临时使用的内存是管理在连接对象里的。这些资源只有在连接断开的时候才会释放。长连接的累积可能导致OOM，即mysql异常重启。
解决办法：

• 定期断开长连。客户端使用一段时间或发现执行过一个占用内存大连接后断开连接，之后查询前再连 （? druid之类的框架会做这种定期长连的动作？）
• 在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源(不需要重新认证用户）

P.S. 这里说的【临时内存】不是InnoDB 的buffer pool，而是【执行过程】中的临时内存，是在连接对象里

另一方面，连接数高意味着可能有更高的并发数，高并发数则意味着死锁检测的量级更大：假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源，最终表现为：CPU idle打到很低，但是秒事务数并不高

see also 1
see also 2

-14.mysql锁的简要认知

• next-key lock = gap lock + index-record lock
  That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record ; 翻译过来为 【临键锁=间隙锁+记录锁】

• mysql RR 是解决了 幻读问题的； RC还是可能幻读

  By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows

• gap lock 没有排他机制。两个事务可能对同一个 gap加锁

  It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap

• RC没有gap-lock，除非是外键或者唯一键检查

  Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking

see also: mysql doc

-13. between and

表T 的列 k 有普通索引，那么哪个SQL性能更好?

1.select * from T where k in(1,2,3,4,5) 
2.select * from T where k between  1 and 5

第二个sql性能更好，只需要搜索树一次；第一个sql需要搜索树五次

-12. 索引选择

问：表T 有2列 a,b ，均有普通索引，查询条件中有where a=? and b=?，innoDB如何选择索引？

答：优化器选择索引是根据数据分布来的。有三种可能：

• 1.只选a, 然后用b过滤
• 2.只选b ，然后用a过滤
• 3.选a和b,然后两个单独跑出来的结果取交集

-11.索引下推 ICP（index condition pushdown）

问：表T 有2列 a,b ，(a,b) 构成了联合索引。现搜索条件 a > 5 and a < 10 and b='123' 在ICP作用下的执行过程是什么样子的？

错答：首先引擎搜索索引树，得到 > 5 and a < 10，然后server 过滤出b='123'的。 【这个答案没有涉及到 ICP】

正解：

• 1. 引擎走 (a,b)联合索引，使用条件 > 5' 快速定位，若发现b <>'123'，下一个，直到满足b='123
• 2. 把找到的行返回给server层， server层根据a<10决定要不要取下一个。 【注意： 若server发现一个 a>10，则不会取这个，i.e.,结果是一个一个取的】

再举个(gpt)例子：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);
(age,department ) 有复合索引；
现在要查 SELECT * FROM employees WHERE age > 30 AND department = 'IT';

若无ICP:

- 1) Scan the index to locate entries where the age is greater than 30.
- 2) For each matching entry, retrieve the corresponding row from the table.
- 3) Filter out the rows where the department is not "IT".

若有ICP：

- 1) Scan the index and apply the condition age > 30 during the index scan itself.
- 2) Only retrieve the entries from the index where age > 30.
- 3) While scanning the index, for each matching entry, check if the department is "IT" and discard entries that do not meet this condition.
- 4) Retrieve the corresponding rows from the table for the remaining entries

-10. mySQL在执行一条SQL时，是如何选择使用哪个索引的。 possible keys有很多，根据什么选择用哪一个

根据 索引统计信息、临时表成本、排序成本

-9. SELECT LAST_INSERT_ID() 是如何处理并发插入问题的？

SELECT LAST_INSERT_ID()可以返回生成的主键。
这个SELECT LAST_INSERT_ID()是connection级别的

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot
be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

-8. relay log

备服务器从主获取Binlog后，会利用relay log记录binlog内容、记录备机应用的binlog位置和状态。备服务器就不需要每次都从主获取数据，而是可以在本地用relay log拿数据。提高了复制性能。

-7. int(11) varchar(1024)

• int(11) ： int 实际上的定长， int(11) 和 int(10) 没啥本质区别，11 和10 只是显示长度
• varchar(1024): varchar是变长，1024个字符数 是varchar最大长度了。

-6 is null V.S. col = null

写java代码写习惯了，不自觉地写成了 where col = null，这样的SQL不会报错！但是会查不到预期结果，而且半天看不出来问题在哪里。mysql的玩法其实是 where col is null。

-5. 花式 order by

我们常用 order by 某个字段来获取有序结果, 但是比较机械: 不能自定义排序规则. 其实order by是支持函数的.

SELECT * FROM `MyTable` 
WHERE `name` IN ('张三', '李四', '王五', '孙六') 
ORDER BY FIELD(`name`, '李四', '孙六', '张三', '王五')

另外除了使用`field`，还可以用`locate`和`instr`函数进行排序：
SELECT * FROM test_member ORDER BY locate(id,'5056,5052,5054,5050') desc;
SELECT * FROM test_member ORDER BY field(id,5056,5052,5054,5050) desc;
SELECT * FROM test_member ORDER BY INSTR('5056,5052,5054,5050',id) desc;

-4. UPDATE CURRENT_TIMESTAMP

mysql表的行经常会加上"更新时间"这样的字段:

create table test(
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间'
)

问题来了: 假如 update 了一行,但是数据实际没有修改,那么
update_time字段会更新吗?

答: 不会… (surprisingly)

具体可以看:stackOverFlow

-3.left join and; left join where

举个例子:
现有2个表tab1 tab2,


情况一注意id=2的有重复行:

select * from tab1
left join tab2 on tab1.size=tab2.size;

情况二:

select * from tab1
left join tab2 on tab1.size=tab2.size and tab1.size=30;

情况三:

select * from tab1
left join tab2 on tab1.size=tab2.size 
where tab1.size=30;

-2.如何删除mysql binlog file

事务多的时候binlog file可能很多,占据了云主机的大量磁盘空间,造成服务不可用. 手动rm -rf 可能引发未知的问题.正确的姿势是使用:
PURGE MASTER LOGS BEFORE '2022-08-05 00:00:00';
删除指定时间之前的binlog file. 云主机自维护的DB,就这样简单粗暴purge 即可.

也可以删除某个文件之前的所有binlog file:
PURGE MASTER LOGS TO 'binlog.000860'

-1.binlog-row-event-max-size

Specify the maximum size of a row-based binary log event, in bytes. Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256. The default is 8192。

对于上千万行的超大事务，可以拆分成通过几千或几万个binlog event，以每个binlog event为单位进行处理，能有效降低超大事务的影响。

对于使用程序解析抽取binlog的业务,比如canal，由于binlog中记录的是已提交的修改数据，如果不考虑数据一致性或对数据一致性要求较低的场景下(?)，可以考虑将超大事务按照binlog event进行拆分，按照每个binlog event进行事务解析。

0 如何批量插入数据

有时我们需要批量插入数据到DB方便某些测试, 除了使用代码去插, 使用存储过程更为方便. 这里给出一个简单的procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `new_procedure`(IN loop_count INT)
BEGIN
DECLARE var INT DEFAULT 0;
WHILE var < loop_count DO  
SET var=var+1;
INSERT INTO info (`id`,`locus`,`journal`) VALUES (
		null,
        var,
        var
);
END WHILE;  
END

1.binlog

1.1 看binlog

mysql> show binlog events;   #只查看第一个binlog文件的内容
mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容
mysql> show binary logs;  #获取binlog文件列表
mysql> show master status； #查看当前正在写入的binlog文件

1.2 解析binlog内容

参考别的博客

重要点:

• 二进制的日志 ,不仅包括日志文件(.00000*) ,还包括 索引文件 (.index) . 前者记录DDL DML ,后者记录 二进制文件.

2 partition关键字

十分坑…如果你的SQL中有 parition关键字,而同时它又没有加反引号,也就是 :

select `partition` from  table

那么MySQL GUI工具会一直提示你的SQL错误, 且MyBatis也会提示错误,但是都不写清楚这是为啥错…

这个字用在分组里,比如一个例子:

先把按照人名分组，然后在每个组里面按时间倒排，最后取出每组的第一条数据即可

 select * from
	(
		SELECT 
			ROW_NUMBER() over(partition by [姓名] order by [打卡时间] desc) as rowNum
			,[姓名]
			,[打卡时间]
		FROM [dbo].[打卡记录表]
	) temp
	where temp.rowNum = 1

3 如何避免count(*)

select SQL_CALC_FOUND_ROWS

4

5 group_concat()

5.1 扁平化一组记录集合

比如现在有原始数据:

现在来看两个SQL:按照一个字段分组,并输出了某字段统计值

SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;

SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus

5.2 select … group by

我们知道 distinct可以针对一个字段进行去重. group by可以分组,同时能够对一行记录进行去重.

这里一定要注意: group by也可以去重, 和distinct 倒是有点像. 现在mysql推荐用 group by 去重

select a,b from table_c group by c 

5.3 select sleep(1)

调试用的语句，可以人为降低语句的执行速度方便观察

-- 注意会【让每一行都睡1s】 ，十分危险！但是调试比较方便
select id ,sleep(1) from t_stu; 
select sleep(10);
show processlist;

6.常用的mysql命令行

 mysql -h localhost -u user1 -p -P 3308

7. 常见mysql参数

• show engine innodb status

• binlog_row_image=FULL 控制row before/after的，
  binlog_format=row

• sql_safe_updates=on

  • 如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话,会报错；
  • SQL要审计

• innodb_io_capacity
  see also

• mysql常用分析命令

  • mysqlbinlog 命令
  • show processlist;
  • set session transaction isolation level read uncommitted;
  • select @@session.tx_isolation;
  • select * from sys.schema_table_lock_waits; – 查看当前锁情况
  • select * from information_schema.processlist; – 和show processlist 类似，但更方便筛选线程
  • select * from sys.innodb_lock_waits; – 不确定和schema_table_lock_waits有何关联
  • select * from INFORMATION_SCHEMA.INNODB_LOCKS;
  • create table t2 like t – 快速创建新表