随笔分类 - mysql实战45讲
该分类来自mysql实战45讲--丁奇
摘要：39 自增主键为什么不连续 Mysql的innodb的自增主键，由于自增主键可以让主键索引尽量得保持递增顺序插入，避免了页分裂，因此索引更紧凑。 在设计的时候，自增主键是不能保证连续的。 自增值保存在什么地方 上表插入一行值 看到AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生
阅读全文
摘要：38 是否要使用memory引擎的表 内存表的数据组织结构 可以看到，内存表t1的返回结果里面0在最后一行，在innodb表t2的返回结果，0在第一行，二者的差别要从他们的主键索引的组织方式说起。 表t2是innodb表，是主键索引id的方式，innodb表的数据放在主键索引树上，是一个B+ tre
阅读全文
摘要：37 什么时候使用内部临时表 前面介绍了sort buffer，内存临时表和join buffer，这3个数据结构都是用来存放语句执行过程中的中间数据，以辅助sql语句的执行的，在排序的时候用sort buffer，在使用join语句的时候使用了join_buffer。在mysql里，什么时候使用内
阅读全文
摘要：36 为什么临时表可以重名 在上一篇的join优化中，用到了临时表， 临时表的特征和适合场景 首先区分内存表和临时表： --内存表，指使用memory引擎，建表语法是engine=memory，内存表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在， --临时表，可以使用各种引擎，如果是
阅读全文
摘要：35 怎么优化join 上一篇介绍了join的两种算法：nlj和bnl Multi-Range Read优化 Multi-range read（MRR），优化的主要目的是尽量使用顺序读盘。 在介绍innodb的索引结构时，提到了”回表”的概念，回表是指，在innodb普通索引a上查到主键id的值后，
阅读全文
摘要：34 到底可不可以使用join？ 在实际生产中，关于join语句使用的问题，一般会集中在以下两类： --1 dba不让使用join，使用join有什么问题呢 --2 如果有两个大小不同的表做join，应该用哪个表做驱动表呢？ 创建2个表作为测试 这两个表都有一个主键索引id和一个索引a，字段b上没有
阅读全文
摘要：33 查询大量数据，是否会把内存撑爆 如果主机内存只有100g，现在要对一个200g的大表做全部扫描，会不会把数据库主机的内存用光了？ 全表扫描对server层的影响 假设，现在要对一个200g的innodb表db1.t33执行全表扫描，当然，要把全部扫描的结果保存在客户端，会用类似这样的命令： m
阅读全文
摘要：32 kill不掉的语句 在mysql中有两个kill命令：一个是kill query+线程id，表示终止这个线程正在执行的语句；一个是kill connection+线程id，缺省connection值，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句。 在大多数
阅读全文
摘要：31 数据误删的恢复 1 使用delete语句误删数据行 2 使用drop table或者truncate table语句误删除数据表 3 使用drop database语句误删数据库 4 使用rm命令误删整个mysql实例 误删行 如果使用delete语句误删了数据行，可以使用flushback工
阅读全文
摘要：答疑文章二，用动态的观点看加锁 回顾加锁原则： 原则1：加锁的基本单位是next-key lock,是一个前开后闭区间 原则2：查找过程中访问到的对象才加锁 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等
阅读全文
摘要：29 如何判断一个数据库是不是出了问题 在主备环境中，必不可少的是出现主从的切换，一种是主动切换，一种是被动切换，往往是因为主库出了问题，由HA系统发起的。 怎么判断一个主库出现了问题了呢 select 1 判断 实际上，select 1成功返回，只能说明这个库的进程还在，并不能说明主库没问题，下面
阅读全文
摘要：28 读写分离有哪里要注意的地方 在一主多从的架构中，读写分离，以及怎么处理主备延时导致的读写分离的问题。 读写分离的主要目标是分担主库压力，上图的结构是客户端主动做负载均衡，在这种模式下一般会把数据库的连接信息放在客户端的连接层，也就是由客户端来选择后端数据库进行查询。 还有一种架构，在mysql
阅读全文
摘要：大多数的互联网应用场景都是读多写少，在发展过程中可能会出现读性能问题，在数据库层解决读性能问题：一主多从 下面是多主从结构 虚线箭头表示主备关系，A与A’互为主备，从库B,C,D指向主库A，一主多从的设置，一般用于读写分离，主库复制所有的写入和一部分读，其他的读有从库分担。 在一主多从架构下，主库故
阅读全文
摘要：主备库并行复制策略 如果备库执行日志的速度低于主库日志生成的速度，那么这个延迟就有可能更长，而且对于一个压力持续比较高的主库来说，备库很可能永远追不上主库 下面介绍备库并行复制能力 谈到主备的并行能力，就关注图中2个黑色的箭头，一个代表主库并行写入，另外一个代表备库并行sql_thread执行rel
阅读全文
摘要：上一篇介绍了binlog的基本内容，在主备关系中，是每个备库接收主库的binlog并执行。 正常情况下，只要主库执行更新生成的所有的binlog，都可以传到备库并被正确执行，备库就能跟主库一致的状态，之就是最终一致性，但是，mysql要提供高可用能力，只有最终一致性是不够的 主备延时 主备切换可能是
阅读全文
摘要：Mysql主备的基本原理 在状态1中，客户端直接访问节点A，而节点B只是备库，只是将A的binlog全部同步过来并应用到本地，这样可以保持节点B和节点A的数据是相同的。 当需要切换的时候，就变成状态2，这时候客户端读取的是节点B，而A变成B的备库。 在状态1中，B节点没有被直接访问，建议设置为rea
阅读全文
摘要：MySQL的wal机制，得到的结论是：只要redo log和binlog 持久化到磁盘，就能确保mysql异常重新启动后，数据是可以恢复的。 binlog的写入机制 其实，binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cach
阅读全文
摘要：22 mysql有那些”饮鸩止渴”提高性能的方法？ 正常的短连接模式是连接到数据库后，执行很少的SQL语句就断开，下次需要的时候再重新连接。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。 Mysql建立连接的过程，成本是很高的，除了正常的网络连接的3次握手外，还需要做登录权
阅读全文
摘要：上一篇中介绍了间隙锁和next-key lock的概念，但是没有说明加锁规则 加锁规则两个前提说明： 1 mysql后面的版本可能会改变加锁策略，所以这个规则只限于截止到目前最新的版本，即5.x系列 <=5.7.24, 8.0系列 <=8.0.13. 2 如果大家在验证中发现有bad case的话，
阅读全文
摘要：例子： CREATE TABLE `t20` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB;
阅读全文
