思考题归档

索引

如何重建二级索引或主键索引

如果重建索引 k，可以通过

• alter table T drop index k;
• alter table T add index(k);

如果重建主键索引，可以通过：

• alter table T engine=InnoDB;

  重建主键索引时不要连续执行，alter table T drop primary key; alter table T add primary key(id); 因为不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了

是否需要创建索引 “ca”

表结构定义如下，由于历史原因，这个表需要a、b做联合主键。但既然主键包含了a、b这两个字段，那意味着单独在字段c上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？同事告诉他，是因为他们的业务里面有这样的两种语句：

1. select * from geek where c=N order by a limit 1;
2. select * from geek where c=N order by b limit 1;

   CREATE TABLE `geek` (
   `a` int(11) NOT NULL,
   `b` int(11) NOT NULL,
   `c` int(11) NOT NULL,
   `d` int(11) NOT NULL,
   PRIMARY KEY (`a`,`b`),
   KEY `c` (`c`),
   KEY `ca` (`c`,`a`),
   KEY `cb` (`c`,`b`)
   ) ENGINE=InnoDB;

   答：ca不是必须的，cb是必须的，因为主键 a，b的聚簇索引组织顺序相当于 order by a,b ，也就是先按a排序，再按b排序，c无序；而已经建立了c索引，第一条语句就会命中，然后再走a，b的聚簇索引

   为什么会走不到索引a？（第10节）

   往表t中插入10万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000) ``sql CREATE TABLEt(idint(11) NOT NULL AUTO_INCREMENT,aint(11) DEFAULT NULL,bint(11) DEFAULT NULL, PRIMARY KEY (id), KEYa(a), KEYb(b`) ) ENGINE=InnoDB;

delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=100000)do insert into t values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata(); ``` | session A | session B | | —- | —- | | start transaction with consistent snapshot | | | | delete from t;
call idata(); | | | explain select * from t where a between 10000 and 20000; | | commit; | |

此时，执行计划显示走的是全表扫描，为什么？
答：我理解 session A 开启的事务对 session B的delete操作后的索引数据的统计时效产生了影响，因为需要保证事务A的重复读，在数据页没有实际删除，而索引的统计选择了N个数据页，这部分数据页不收到前台事务的影响，所以整体统计值会变大，直接影响了索引选择的准确性；

如何设计登录名这个索引？（第11节）

如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com”, 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。
系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？
答：由于这个学号的规则，无论是正向还是反向的前缀索引，重复度都比较高。因为维护的只是一个学校的，因此前面6位（其中，前三位是所在城市编号、第四到第六位是学校编号）其实是固定的，邮箱后缀都是@gamil.com，因此可以只存入学年份加顺序编号，它们的长度是9位。
而其实在此基础上，可以用数字类型来存这9位数字。比如201100001，这样只需要占4个字节。其实这个就是一种hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性。
评论区中，也有其他一些很不错的见解。
评论用户@封建的风 说，一个学校的总人数这种数据量，50年才100万学生，这个表肯定是小表。为了业务简单，直接存原来的字符串。这个答复里面包含了“优化成本和收益”的思想，我觉得值得at出来。
@小潘 同学提了另外一个极致的方向。如果碰到表数据量特别大的场景，通过这种方式的收益是很不错的。
评论区留言点赞板：
@lttzzlll ，提到了用整型存“四位年份+五位编号”的方法；
由于整个学号的值超过了int上限，@老杨同志 也提到了用8个字节的bigint来存的方法。

锁

逻辑备份时，DDL 对备库的影响

在用 –single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

删除一个表里面的前10000行数据，有以下三种方法可以做到，哪种最好

• 第一种，直接执行delete from T limit 10000;
• 第二种，在一个连接中循环执行20次 delete from T limit 500;
• 第三种，在20个连接中同时执行delete from T limit 500。

答：第二种是最好的

一条更新语句的执行过程？

1. 首先根据执行计划，走索引还是全表扫将数据所在页面加载到 buffer pool 中（如果该记录所在的页面已经在内存的buffer pool中，那就可以直接读取）；
2. 检查更新前后当前记录是否是一样的，如果一样则跳过该条记录，如果不一样则继续处理；
3. 记录 undo log，并记录 undo log 对应的 redo log：
   • MTR 提交时，先将这个过程产生的 redo log 写入到 redo log buffer；
   • 再将这个过程修改的 undo 页面加入到 buffer pool 的 flush 链表中；
4. 更新聚簇索引记录，并记录对应的 redo log（MTR 提交时，先 redo log 写入到 redo log buffer，然后将修改的页面加入到 flush 链表）
5. 更新二级索引记录，并记录对应的redo log（同上）；
6. 记录 binlog，此时记录的binlog日志并不会被写到binlog日志文件中，而是被暂时保存到内存的某个地方，等之后事务提交的时候才会真正将该事物执行过程中产生的所有binlog统一写入 binlog 日志文件

假设你的表里面已经有了city_name(city, name)这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前100条记录。如果SQL查询语句是这么写的 ：
mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;

1. 那么，这个语句执行的时候会有排序过程吗，为什么？
2. 如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？
3. 进一步地，如果有分页需求，要显示第101页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？

虽然有(city,name)联合索引，对于单个city内部，name是递增的。但是由于这条SQL语句不是要单独地查一个city的值，而是同时查了”杭州”和” 苏州 “两个城市，因此所有满足条件的name就不是递增的了。也就是说，这条SQL语句需要排序。
那怎么避免排序呢？
这里，我们要用到(city,name)联合索引的特性，把这一条语句拆成两条语句，执行流程如下：

1. 执行select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的，客户端用一个长度为100的内存数组A保存结果。
2. 执行select * from t where city=“苏州” order by name limit 100; 用相同的方法，假设结果被存进了内存数组B。
3. 现在A和B是两个有序数组，然后你可以用归并排序的思想，得到name最小的前100值，就是我们需要的结果了。

如果把这条SQL语句里“limit 100”改成“limit 10000,100”的话，处理方式其实也差不多，即：要把上面的两条语句改成写：

• select * from t where city=”杭州” order by name limit 10100;
• select * from t where city=”苏州” order by name limit 10100。

这时候数据量较大，可以同时起两个连接一行行读结果，用归并排序算法拿到这两个结果集里，按顺序取第10001~10100的name值，就是需要的结果了。
当然这个方案有一个明显的损失，就是从数据库返回给客户端的数据量变大了。所以，如果数据的单行比较大的话，可以考虑把这两条SQL语句改成下面这种写法：

• select id,name from t where city=”杭州” order by name limit 10100;
• select id,name from t where city=”苏州” order by name limit 10100。

然后，再用归并排序的方法取得按name顺序第10001~10100的name、id的值，然后拿着这100个id到数据库中去查出所有记录。
上面这些方法，需要你根据性能需求和开发的复杂度做出权衡。
评论区留言点赞板：
评论区很多同学都提到不能排序，说明各位对索引的存储都理解对了。
@峰 同学提到了归并排序，是我们这个问题解法的核心思想；
@老杨同志 的回答中提到了“从业务上砍掉功能”，这个也确实是在业务设计中可以考虑的一个方向；
@某、人 帮忙回答了@发条橙子同学的问题，尤其是对问题一的回答，非常精彩。

日志系统

如果 redo log 设置过小会发生生么？（第12节）

一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例，正常会建议你将redo log设置成4个1GB的文件。
但如果你在配置的时候不慎将redo log设置成了1个100M的文件，会发生什么情况呢？又为什么会出现这样的情况呢？
redo log是关系型数据库的核心啊,保证了ACID里的D。所以redo log是牵一发而动全身的操作
按照老师说的当内存数据页跟磁盘数据页不一致的时候,把内存页称为’脏页’。如果redo log
设置得太小,redo log写满.那么会涉及到哪些操作呢,我认为是以下几点:
1.把相对应的数据页中的脏页持久化到磁盘,checkpoint往前推
2.由于redo log还记录了undo的变化,undo log buffer也要持久化进undo log
3.当innodb_flush_log_at_trx_commit设置为非1,还要把内存里的redo log持久化到磁盘上
4.redo log还记录了change buffer的改变,那么还要把change buffer purge到idb
以及merge change buffer.merge生成的数据页也是脏页,也要持久化到磁盘
上述4种操作,都是占用系统I/O,影响DML,如果操作频繁,会导致’抖’得向现在我们过冬一样。
但是对于select操作来说,查询时间相对会更快。因为系统脏页变少了,不用去淘汰脏页,直接复用
干净页即可。还有就是对于宕机恢复,速度也更快,因为checkpoint很接近LSN,恢复的数据页相对较少
所以要控制刷脏的频率,频率快了,影响DML I/O,频率慢了,会导致读操作耗时长。
每次事务提交都要写redo log，如果设置太小，很快就会被写满，也就是下面这个图的状态，这个“环”将很快被写满，write pos一直追着CP。

这时候系统不得不停止所有更新，去推进checkpoint。
这时，你看到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌。
评论区留言点赞板：
@某、人 给了一个形象的描述，而且提到了，在这种情况下，连change buffer的优化也失效了。因为checkpoint一直要往前推，这个操作就会触发merge操作，然后又进一步地触发刷脏页操作；
有几个同学提到了内存淘汰脏页，对应的redo log的操作，这个我们会在后面的文章中展开，大家可以先看一下 @melon 同学的描述了解一下；
@算不出流源 提到了“动态平衡”，其实只要出现了这种“平衡”，意味着本应该后台的操作，就已经影响了业务应用，属于有损失的平衡。

事务相关

如何避免出现或者处理长事务？

首先，从应用开发端来看：

1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL的general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。
2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务可以去掉。
3. 业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）

其次，从数据库端来看：

1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警/或者kill；
2. Percona的pt-kill这个工具不错，推荐使用；
3. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
4. 如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

   实战解答

   问题一：Mysql怎么保证一致性的？
   OK，这个问题分为两个层面来说。
   从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。
   但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。
   从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！
   问题二: Mysql怎么保证原子性的？
   OK，是利用Innodb的undo log。
   undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。
   例如

• (1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
• (2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
• (3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
ps:具体的undo log日志长啥样，这个可以写一篇文章了。而且写出来，看的人也不多，姑且先这么简单的理解吧。
问题三: Mysql怎么保证持久性的？
OK，是利用Innodb的redo log。
正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。
怎么解决这个问题？
简单啊，事务提交前直接把数据写入磁盘就行啊。
这么做有什么问题？

• 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
• 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。
采用redo log的好处？
其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下

• redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
• redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

ps:不想具体去谈redo log具体长什么样，因为内容太多了。
问题四: Mysql怎么保证隔离性的？
OK,利用的是锁和MVCC机制。还是拿转账例子来说明，有一个账户表如下
表名t_balance

其中id是主键，user_id为账户名，balance为余额。还是以转账两次为例，如下图所示

至于MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。
如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。
由于MVCC机制在可重复读(Repeateable Read)和读已提交(Read Commited)的MVCC表现形式不同，就不赘述了。
但是有一点说明一下，在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。

表相关

假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：

• 一个表t文件大小为1TB；
• 对这个表执行 alter table t engine=InnoDB；
• 发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了1.01TB。

你觉得可能是什么原因呢 ？
答：
当这个表本身就已经没有空洞的了，比如说刚刚做过一次重建表操作。在DDL期间，如果刚好有外部的DML在执行，这期间可能会引入一些新的空洞。
@飞翔 提到了一个更深刻的机制，是我们在文章中没说的。在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。假如是这么一个过程：

1. 将表t重建一次；
2. 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；
3. 这种情况下，再重建一次表t，就可能会出现问题中的现象。