2020年08月_wanger61

原创（十六）分区表，自增id用完

35 分区表对一个表进行n个分区后，对于引擎层来说，这是 n 个表；对于 Server 层来说，这是 1 个表。分区表的特点MySQL 在第一次打开分区表的时候，需要访问所有的分区；在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；（因此分区表，在做 DDL 的时候，影响会更大。）在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。分区表的应用场景分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用

2020-08-06 20:08:59 367

原创（十五）特殊insert语句加锁规则，快速复制一张表

33 特殊insert语句加锁规则insert … selectinsert into t2(c,d) select c,d from t;可重复读隔离级别下，binlog_format=statement 时执行该语句，会对表 t 的所有行和间隙加锁这是为了保证日志和数据的一致性：否则在复制的过程中，如果一个线程向t中插入了数据，可能会因为binlog记录顺序不同导致复制的行数不同对目标表t2不会锁全表，而是只锁住需要访问的资源。insert into t(c,d) (select c

2020-08-06 19:20:28 301

原创（十四）临时表，内存表，自增主键

临时表临时表的特点：建表语法是 create temporary table …。一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。临时表可以与普通表同名。session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。show tables 命令不显示临时表。由于临时表只能被创建它的 session 访问，所以在这个 session

2020-08-05 20:35:12 247

原创（十三）Join原理，Join优化

29 JoinIndex Nested-Loop Join联结两个表时，如果第二个表上有联结键索引，那么就会根据第一个表中查出的联结键值搜索索引，然后根据索引得到的主键id去第二张表找在这种情况下：使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；如果使用 join 语句的话，需要让小表做驱动表。Simple Nested-Loop Join联结两个表时，如果第二个表上没有联结键索引，那么就会根据第一个表中查出的联结键值，去第二张表上作全表扫描Block

2020-08-05 20:18:15 248 1

原创（十二）数据库误删，Kill不掉的语句，查询数据与内存

26 数据库误删预防误删账号分离。这样做的目的是，避免写错命令只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。制定操作规范，避免写错要删除的表名把 sql_safe_updates 参数设置为 on。这样一来，如果忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。代码上线前，必须经过 SQL 审计

2020-08-05 19:11:35 211

原创关于next-key范围查询加锁规则的实验

本实验基于MySQL8.0.18版本在《MySQL45讲》定义了如下加锁规则：原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。原则 2：查找过程中访问到的对象才会加锁。优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。初始化

2020-08-05 17:28:44 1299 5

原创（十一）读写分离，过期读，判断数据库出现问题

读写分离由于从库延迟是不能100%避免的，因此客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态，即过期读。过期读的解决方案：强制走主库方案：将查询请求分为这么两类：1）对于必须要拿到最新结果的请求，强制将其发到主库上。2）对于可以读到旧数据的请求，才将其发到从库上。该方案是用的最多的，但这个方案最大的问题在于，有时候你会碰到“所有查询都不能是过期读”的需求，比如一些金融类的业务。这样的话，你就要放弃读写分离，所有读写压力都在主库，等同于

2020-08-04 20:53:04 186

原创（十）主从复制，主备延迟，并行复制

21 主从复制binlog的三种格式statement: binlog 里面记录的就是 SQL 语句的原文,该格式下主从库有可能在执行同一条SQL时采用了不同的执行逻辑导致数据不一致row: binlog 里面记录了真正执行的行的主键 id和操作内容mixed：由于row占用空间太大，mixed 格式，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。因此，如果你的线上 MySQL 设置的 binlog 格式

2020-08-04 20:02:50 453

原创（九）临时提升性能，数据可靠性

19 临时提升性能的方法短连接风暴如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况解决方案：先处理掉那些占着连接但是不工作的线程，并且应该优先断开事务外空闲的连接步骤：1.先用show processlist找到处于Sleep状态的连接2.在information_schema 库的 innodb_trx 表中找到事务内的连接3.用kill connection + id命令来断开事务外的连接注意：一个客户端处于 sleep 状态时，它的连接被服务端主动断开

2020-08-04 18:28:02 1554

原创（八）索引失效，间隙锁，加锁规则

16 一些索引失效情况条件字段函数操作隐式类型转换如 select * from tradelog where tradeid=110717;如果tredeid是字符串类型，那么在比较时会隐性地将字符串转为数字，也就不会走索引来隐式字符编码转换当两个表的字符集不同，在关联时作字符集转换实际也是一个隐性类型转换，此时也不会走索引17 间隙锁为了避免幻读带来的数据不一致问题，当执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的

2020-08-03 20:41:38 590

原创（七）排序原理

14 排序全字段排序：包括在内存中排序和利用磁盘临时文件排序可以用下面的方法，来确定一个排序语句是否使用了临时文件。/* 打开optimizer_trace，只对本线程有效 */SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'I

2020-08-02 21:04:58 185

原创（六）重建表与count(*)原理

12 重建表数据删除流程delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。不止是删除数据会造成空洞，插入数据也会。经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。重建表可以使用 alter table A engine=InnoDB 命令来重建表：其本

2020-08-02 20:11:42 241

原创（五）字符串建索引，SQL 时快时慢

10 给字符串建索引直接创建完整索引，这样可能比较占用空间；创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。11 SQL 语句执行时快时慢InnoDB 在处理更新语句的时候，包括写入内存和写redo log磁盘操作。当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后

2020-08-02 19:17:52 262

原创（四）普通索引和唯一索引，选错索引

08 普通索引和唯一索引change buffer当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。将 change buffer 中的操作应用到原数据页，得到最新结果的

2020-08-02 18:40:46 95

原创（三）全局锁，表锁，行锁

05 全局锁顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做

2020-08-01 20:57:49 246

原创（二）事务隔离，索引

03 事务隔离事务隔离的实现在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作（undo log)。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次

2020-08-01 20:27:43 125

原创（一）基础架构与日志系统

01 基础架构连接器连接完成后，如果没有后续的动作，连接就处于空闲状态，可以在 show processlist 命令中查看状态。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个，应尽量使用长连接。但是使用长连接后，有些时候 MySQL 占用内存涨得特别快，这是因为 MyS

2020-08-01 19:18:55 183

wanger61的博客