1. MySQL一棵B+树能存多少条数据？
2. 如果MySql 引起 CPU 消耗过大，怎么优化
3. MySql 死锁问题如何解决
4. MySql 数据存储在磁盘上是什么样子
5. 数据库连接池到底应该设多大
6. 为什么delete 表数据，磁盘空间却还是被占用
7. 主从复制解决了什么问题，出现同步延迟如何解决

MySQL一棵B+树能存多少条数据？

1 数据持久化存储磁盘里，磁盘的最小单元是扇区，一个扇区的大小是512 字节
2 文件系统最小单元是 块，一个块的大小是4K
3 InnoDB 存储引擎，有自己的最小单元，称之为页，一个页的大小是 16K

MySQL 的最小存储单元叫做“页”，这么多的页是如何构建一个庞大的数据组织，我们又如何知道数据存储在哪一个页中？
如果逐条遍历，性能肯定很差。为了提升查找速度，我们引入了 B+ 树，先来看下 B+ 树的存储结构：
页除了可以存放数据（叶子节点），还可以存放健值和指针（非叶子节点），当然他们是有序的。这样的数据组织形式，我们称为索引组织表。

如：上图中 page number=3 的页，该页存放键值和指向数据页的指针，这样的页由 N 个键值+指针组成。

B+ 树是如何检索记录？
1 首先找到根页，你怎么知道一张表的根页在哪呢？
2 其实每张表的根页位置在表空间文件中是固定的，即 page number=3 的页。
3 找到根页后通过二分查找法，定位到 id=5 的数据应该在指针 P5 指向的页中。
4 然后再去 page number=5 的页中查找，同样通过二分查询法即可找到 id=5 的记录。

1 查询数据库时，不论读一行，还是读多行，都是将这些行所在的整页数据加载，然后在内存中匹配过滤出最终结果。
2 表的检索速度跟树的深度有直接关系，毕竟一次页加载就是一次 IO，而磁盘 IO 又是比较费时间。对于一张千万级条数 B+ 树高度为 3 的表与几十万级 B+ 树高度也为 3 的表，其实查询效率相差不大。

一棵树可以存放多少行数据？

假设 B+ 树的深度为 2，这棵 B+ 树的存储总记录数 = 根节点指针数 * 单个叶子节点记录条数。

那么指针数如何计算？假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。

那么一个页中能存放多少这样的组合，就代表有多少指针，即 0。

那么可以算出一棵高度为 2 的 B+ 树，能存放 20 条这样的数据记录。

千万级的数据存储只需要约 3 层 B+ 树，查询数据时，每加载一页（page）代表一次 IO。所以说，根据主键 id 索引查询约 3 次 IO 便可以找到目标结果。

对于一些复杂的查询，可能需要走二级索引，那么通过二级索引查找记录最多需要花费多少次 IO 呢？
首先，从二级索引 B+ 树中，根据 name 找到对应的主键 id：

然后，再根据主键 id 从聚簇索引查找到对应的记录。如上图所示，二级索引有 3 层，聚簇索引有 3 层，那么最多花费的 IO 次数是：3+3=6。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。
如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。这也是为什么 InnoDB 表必须有主键，并且推荐使用整型的自增主键！！！
InnoDB 使用的是聚簇索引，将主键组织到一棵 B+ 树中，而行数据就储存在叶子节点上。

①若使用"where id=14"这样的条件查找记录，则按照 B+ 树的检索算法即可查找到对应的叶节点，之后获得行数据。
②若对 Name 列进行条件搜索，则需要两个步骤：
第一步在辅助索引 B+ 树中检索 Name，到达其叶子节点获取对应的主键值。
第二步使用主键值在主索引 B+ 树中再执行一次 B+ 树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

如果MySql 引起 CPU 消耗过大，怎么优化（数据库层面，非数据库层面）

1 减少等待（避免CPU上下文切换带来的消耗）

SQL/index，使用合适的索引减少扫描的行数（需平衡索引的正收益和维护开销，空间换时间）

避免使用函数，将运算转移至易扩展的应用服务器中 如substr等字符运算，dateadd/datesub等日期运算，abs等数学函数

减少排序，利用索引取得有序数据或避免不必要排序 如union all代替 union，order by 索引字段等

禁止类型转换，使用合适类型并保证传入参数类型与数据库字段类型绝对一致 如数字用tiny/int/bigint等，必需转换的在传入数据库之前在应用中转好

简单类型，尽量避免复杂类型，降低由于复杂类型带来的附加运算。更小的数据类型占用更少的磁盘、内存、cpu缓存和cpu周期

index，优化索引，减少不必要的表扫描 如增加索引，调整组合索引字段顺序，去除选择性很差的索引字段等等
table，合理拆分，适度冗余 如将很少使用的大字段拆分到独立表，非常频繁的小字段冗余到“引用表”
SQL，调整SQL写法，充分利用现有索引，避免不必要的扫描，排序及其他操作 如减少复杂join，减少order by，尽量union all，避免子查询等
数据类型，够用就好，减少不必要使用大字段 如tinyint够用就别总是int，int够用也别老bigint，date够用也别总是timestamp

4 减少query请求量（非数据库本身）
适当缓存，降低缓存数据粒度，对静态并被频繁请求的数据进行适当的缓存 如用户信息，商品信息等
优化实现，尽量去除不必要的重复请求 如禁止同一页面多次重复请求相同数据的问题，通过跨页面参数传递减少访问等
合理需求，评估需求产出比，对产出比极端底下的需求合理去除

5 升级cpu若经过减少计算和减少等待后还不能满足需求，cpu利用率还高T_T 是时候拿出最后的杀手锏了，升级cpu，是选择更快的cpu还是更多的cpu了？

低延迟（快速响应），需要更快的cpu（每个查询只能使用一个cpu）
高吞吐，同时运行很多查询语句，能从多个cpu处理查询中收益

MySql 死锁问题如何解决

1 两个 or 两个以上事务
2 每个事务都已经持有锁并且申请新的锁
3 锁资源同时只能被同一个事务持有或者不兼容
4 事务之间因为持有锁和申请锁导致彼此循环等待

出现死锁，看MySql 死锁日志来排查问题

经典MySql 死锁案例：

可以看到两个事务 update 不存在的记录，先后获得间隙锁( gap 锁)，gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁，然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候，当前会话申请不了插入意向锁，导致死锁。

事务之间对资源访问顺序的交替
一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源

1 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争
3 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
4 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
5 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
6尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
7 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。

减少锁粒度，减少锁时间，减少锁发生概率，避免SQL 语句太过复杂，避免修改顺序

MySql 数据存储在磁盘上是什么样子

每个 MyISAM 表都以3个文件存储在磁盘上。这些文件的名称以表名开头，以扩展名指示文件类型。

一张 InnoDB 表底层会对应2个文件在文件夹中进行数据存储。

数据库连接池到底应该设多大（中间层）

那么结论就非常简单。在一个8核的服务器上，设定连接/线程数为8能够提供最优的性能，再增加连接数就会因上下文切换的损耗导致性能下降。数据库通常把数据存储在磁盘上，磁盘又通常是由一些旋转着的金属碟片和一个装在步进马达上的读写头组成的。读/写头同一时刻只能出现在一个地方，然后它必须“寻址”到另外一个位置来执行另一次读写操作。所以就有了寻址的耗时，此外还有旋回耗时，读写头需要等待碟片上的目标数据“旋转到位”才能进行操作。使用缓存当然是能够提升性能的，但上述原理仍然成立。

在这一时间段（即"I/O等待"）内，线程是在“阻塞”着等待磁盘，此时操作系统可以将那个空闲的CPU核心用于服务其他线程。所以，由于线程总是在I/O上阻塞，我们可以让线程/连接数比CPU核心多一些，这样能够在同样的时间内完成更多的工作。

那么应该多多少呢？这要取决于磁盘。较新型的SSD不需要寻址，也没有旋转的碟片。可别想当然地认为“SSD速度更快，所以我们应该增加线程数”，恰恰相反，无需寻址和没有旋回耗时意味着更少的阻塞 ，所以更少的线程[更接近于CPU核心数]会发挥出更高的性能。只有当阻塞创造了更多的执行机会时，更多的线程数才能发挥出更好的性能

公理：你需要一个小连接池，和一个充满了等待连接的线程的队列

如果你有10000个并发用户，设置一个10000的连接池基本等于失了智。1000仍然很恐怖。即是100也太多了。你需要一个10来个连接的小连接池，然后让剩下的业务线程都在队列里等待。连接池中的连接数量应该等于你的数据库能够有效同时进行的查询任务数（通常不会高于2*CPU核心数）。

我们经常见到一些小规模的web应用，应付着大约十来个的并发用户，却使用着一个100连接数的连接池。这会对你的数据库造成极其不必要的负担。

按这个公式，你的4核i7数据库服务器的连接池大小应该为((4 * 2) + 1) = 9。取个整就算是是10吧。是不是觉得太小了？跑个性能测试试一下，我们保证它能轻松搞定3000用户以6000TPS的速率并发执行简单查询的场景。如果连接池大小超过10，你会看到响应时长开始增加，TPS开始下降。

连接池的大小最终与系统特性相关。

比如一个混合了长事务和短事务的系统，通常是任何连接池都难以进行调优的。最好的办法是创建两个连接池，一个服务于长事务，一个服务于短事务。

再例如一个系统执行一个任务队列，只允许一定数量的任务同时执行，此时并发任务数应该去适应连接池连接数，而不是反过来。

当超过这个时候， 会报主键冲突， 说明，当再次插入时，使用的自增ID还是 ，报主键冲突的错误
如果你的服务会经常性的插入和删除数据的话，还是存在用完的风险，建议采用bigint unsigned ，这个数字就大了

不过，还存在另一种情况，如果在创建表没有显示申明主键，会怎么办？

如果是这种情况，InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id，而且InnoDB 维护了一个全局的 dictsys.row_id，所以未定义主键的表都共享该row_id，每次插入一条数据，都把全局row_id当成主键id，然后全局row_id加1

该全局row_id在代码实现上使用的是bigint unsigned类型，但实际上只给row_id留了6字节，这种设计就会存在一个问题：

如果全局row_id一直涨，一直涨，直到2的48幂次-1时，这个时候再+1，row_id的低48位都为0，结果在插入新一行数据时，拿到的row_id就为0，存在主键冲突的可能性。

所以，为了避免这种隐患，每个表都需要定一个主键。

为什么delete 表数据，磁盘空间却还是被占用

凡是使用过mysql，对B+树肯定是有所耳闻的，MySQL InnoDB 中采用了 B+ 树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。因此在删除数据时，会有两种情况：

删除数据页中的某些记录

InnoDB 直接将 R4 这条记录标记为删除，称为可复用的位置。如果之后要插入 ID 在 300 到 700 间的记录时，就会复用该位置。由此可见，磁盘文件的大小并不会减少。

通用删除整页数据也将记录标记删除，数据就复用用该位置，与删除默写记录不同的是，删除整页记录，当后来插入的数据不在原来的范围时，都可以复用位置，而如果只是删除默写记录，是需要插入数据符合删除记录位置的时候才能复用。

因此，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。
delete 删除数据时，其实对应的数据行并不是真正的删除，仅仅是将其标记成可复用的状态，所以表空间不会变小。

DELETE只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间，可以使用OPTIMIZE TABLE来回收未使用的空间，并整理数据文件的碎片。

另外，也可以执行通过ALTER TABLE重建表

根据 hash 取模定位 DB，那模数为多少？模数要为所有此 group 组 DB 中的表数，上图总表数为 10。为什么要去表的总数？而不是 DB 总数 3 呢？

如 id=12，id%10=2；那值为 2，落到哪个 DB 库呢？这是设计是前期设定好的，那怎么设定的呢？

一旦设计定位哪个 DB 后，就需要确定落到 DB 中的哪张表呢？

我们看一下，id 在【0，1000 万】范围内的，根据上面的流程设计，1000 万以内的 id 都均匀的分配到 DB_0，DB_1，DB_2 三个数据库中的 Table_0 表中，为什么可以均匀，因为我们用了 hash 的方案，对 10 进行取模。

上面我们也提了疑问，为什么对表的总数 10 取模，而不是 DB 的总数 3 进行取模？我们看一下为什么 DB_0 是 4 张表，其他两个 DB_1 是 3 张表？

在我们安排服务器时，有些服务器的性能高，存储高，就可以安排多存放些数据，有些性能低的就少放点数据。

如果我们取模是按照 DB 总数 3，进行取模，那就代表着【0，4000 万】的数据是平均分配到 3 个 DB 中的，那就不能够实现按照服务器能力适当分配了。

按照 Table 总数 10 就能够达到，看如何达到。

上图中我们对 10 进行取模，如果值为【0，1，2，3】就路由到 DB_0，【4，5，6】路由到 DB_1，【7，8，9】路由到 DB_2。

现在小伙伴们有没有理解，这样的设计就可以把多一点的数据放到 DB_0 中，其他 2 个 DB 数据量就可以少一点。

注意：小伙伴千万不要被 DB_1 或 DB_2 中 table 的范围也是 0～4000 万疑惑了，这个是范围区间，也就是id在哪些范围内，落地到哪个表而已。

上面一大段的介绍，就解决了热点的问题，以及可以按照服务器指标，设计数据量的分配。

其实上面设计思路理解了，扩容就已经出来了；那就是扩容的时候再设计一个 group02 组，定义好此 group 的数据范围就 ok 了。
因为是新增的一个 group01 组，所以就没有什么数据迁移概念，完全是新增的 group 组，而且这个 group 组照样就防止了热点。

简单点的话，就凌晨配置，重启应用服务就行了。但如果是大型公司，是不允许的，因为凌晨也有订单的。那怎么办呢？本地 JVM 缓存怎么更新呢？

其实方案也很多，可以使用用 Zookeeper，也可以使用分布式配置，这里是比较推荐使用分布式配置中心的，可以将这些数据配置到分布式配置中心去。

这边隐含了一个关键点，那就是路由 key（如：id）的值是非常关键的，要求一定是有序的，自增的，这个就涉及到分布式唯一 id 的方案。

自增的主键的值是顺序的,所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16,会留出1/16的空间留作以后的 修改)：

①下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费
②新插入的行一定会在原有的最大数据行下一行,mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
③减少了页分裂和碎片的产生

因为uuid相对顺序的自增id来说是毫无规律可言的,新行的值不一定要比之前的主键的值要大,所以innodb无法做到总是把新行插入到索引的最后,而是需要为新行寻找新的合适的位置从而来分配新的空间

这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

①写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO

②因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据，一次插入最少需要修改三个页以上

所以如果我们设置的主键是乱序的，就有可能会导致数据页中的主键值大小不能满足索引使用的条件，此时就会产生页分裂
如果出现上图的数据页，我们可以发现后面数据页里的主键值比前一个数据页的主键值要小，里面的数据就会进行数据的挪动，也就是页分裂
通过页分裂，我们只要将主键为2的数据行与主键值为4的数据行互相挪动一下位置，就可以保证后面一个数据页的主键值比前一个数据页中的主键值大了，真正的页分裂可能比图中要复杂很多，但是都是通过这种形式来完成页分裂的

结论就是主键值最好是有序的，这样就可以不用页分裂，还能充分使用到索引，否则就必须进行页分裂来保证索引的使用

③由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片

所以 uuid 和 雪花算法 应该会有 调优操作：
在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后,有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

①别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况

②对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

③Auto_Increment锁机制会造成自增锁的抢夺,有一定的性能损失

1 读磁盘IO瓶颈，热点数据多（水平垂直都可以），每次查询大量磁盘IO，降低查询速度 ： 分库和垂直分表
2 网络带宽不够，请求过多，网络IO瓶颈并发量上不来，一个数据库最多并发2000，最好保持每秒1000左右 ：分库
3 CPU瓶颈：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算

4单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

offset 和Limit 有什么问题 以及代替方案是什么？

正如前面段落所说的那样，OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的。
但是，当数据库里的数据量超过服务器内存能够存储的能力，并且需要对所有数据进行分页，问题就会出现。
为了实现分页，每次收到分页请求时，数据库都需要进行低效的全表扫描。

这意味着，如果你有 1 亿个用户，OFFSET 是 5 千万，那么它需要获取所有这些记录 (包括那么多根本不需要的数据)，将它们放入内存，然后获取 LIMIT 指定的 20 条结果

也就是说，为了获取一页的数据：10万行中的第5万行到第5万零20行

需要先获取 5 万行。这么做是多么低效？

这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

这是一种基于指针的分页。

你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查询可能都与此类似。

为什么？因为通过显式告知数据库最新行，数据库就确切地知道从哪里开始搜索（基于有效的索引），而不需要考虑目标范围之外的记录。
要使用这种基于游标的分页，需要有一个惟一的序列字段 (或多个)，比如惟一的整数 ID 或时间戳，但在某些特定情况下可能无法满足这个条件。

我的建议是，不管怎样都要考虑每种解决方案的优缺点，以及需要执行哪种查询。

一般情况下，在数据库中建立表的时候，强制为每一张表添加 id 递增字段，这样方便查询。

如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候不建议使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

使用先使用范围查询****定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *；

这种方式已经不属于查询优化，这儿附带提一下。

对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

关于丢数据和返回不稳定的一些说明，最稳定的是第三种，其余不同数据库会有不同表现

方法1: 直接使用数据库提供的SQL语句
适应场景: 适用于数据量较少的情况(元组百/千级)
原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

方法2: 建立主键或唯一索引, 利用索引(假设每页10条)
适应场景: 适用于数据量多的情况(元组数上万)
原因: 索引扫描,速度会很快. 有朋友提出: 因为数据查询出来并不是按照pk_id排序的，所以会有漏掉数据的情况，只能方法3

MySql千万数据10秒批量插入

互联网项目中MySql 应该选用什么事务隔离级别

要从主从复制讲起,基于binlog 复制的，binlog是一个记录数据库更改的文件。
为什么 项目中不适用读未提交 和串行化？
互联网的分布式方案，多采用最终一致性的事务解决方案!

RR 和RC 该怎么选取？

MySql 如果每次更新操作都需要写进磁盘，然后磁盘也要先找到对应的那条数据，然后更新，整个过程IO成本，查找成本很高，为了解决这个问题，MySql在设计的时候就用了类似饭店记账的思路来更新效率。

其实就是MySQL里常说的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写小黑板，等不忙的时候再写账本。

需要注意的是，先写日志的写日志，其实也是写磁盘，只是写日志是顺序磁盘，速度非常快。

具体的情况就是，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往在系统比较空闲的时候做，就像打烊了以后写账本一样。

同时，将赊账记录在小黑板上，如果赊账的不多，可以等打烊了以后再记录账本，如果赊账的特别多，小黑板写满了，这个时候掌柜就要放下手上的活，先把黑板上的部分赊账记录更新到账本上，然后将记录好的信息从小黑板上擦掉，为记录新的赊账腾出地方。

MySQL于这个也是类似的，InnoDB的redo log是固定大小的，比如我们可以分配一组4个文件，每个文件的大小都是1GB，那么总共就可以记录4GB的操作，从头开始写，写到末尾就又从开头循环写，write pos是当前记录的位置，一边写一边后移，写到3号文件末尾后就回到0号文件开头，checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据库中。
write pos和checkpoint之间的是“小黑板”上还空着的部分，可以用来记录更新的操作，如果write pos追上了checkpoint，表示“黑板“写满了，这个时候不能再执行新的更新，得停下先擦掉一些记录，把check point推进一下。

redo log是InnoDB引擎所特有的，所以我们在使用InnoDB引擎创建表时，如果数据库发生异常重启，之前提交的记录都不会丢失，InnoDB就是因为有了redo log才有了crash-safe的能力。

crash-safe简单来讲，就好比饭店掌柜的把赊账记录在小黑板上或者账本上，之后饭店突然停业了几天，重新开业后，依然可以通过小黑板和账本上的数据核算赊账账目，

MySql 整体看来有两部分
一部分是Server 层，主要做的就是MySql功能层面的事情， Server 层的日志成为binlog。
还有一部分是引擎层，负责存储相关的具体事情。redo log 是innoDB 引擎特有的日志。

2 redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是罗技日志，记录的是这个语句的原始逻辑，比如“給ID=2 这一行的 c 字段加1”

3 redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

MySql 大体上来看可以分为Server 层和存储引擎两部分
server层：包含 连接器，查询缓存，分析器，优化器执行器等，覆盖了MySql的大部分核心服务功能，以及所有的内置函数，所有的跨存储引擎的功能都在这一层实现，比如存储过程，触发器，视图等

存储引擎层：负责数据的存储和提取，其架构模式是插件式的，支持InnoDB，MyISAM，Memory等多个存储引擎。

关键字：长连接以及短连接，内存溢出，发生异常重启 ， 解决办法。
关键字：缓存如何存放，怎么过期，如何使用缓存，版本过期问题

关键字： 执行方案也就定下来了

关键字：对表进行权限校验，而连接器验证的是用户的身份。

对于同一张表修改一批数据，等于另一批数据；或者对于两张表，修改A表一批数据，等于B表的一批数据。

oracle在这方面做到很好，SQL语法也比较简单，但Mysql做的不好，不是不能做，只是比较麻烦。

将emp表中 部门编号为10，主键empno等于myemp表中的myid，所有人员的【姓名，工资】批量同步过去，只能写一个语句。

实际上，这样mysql会报错。

意思是，要修改的表，就不能在后面子查询语句中使用。

对于题目中提到的动态 SQL，显然应该有一种静态 SQL 与之相对，那么两者应该怎么理解？我们先来个基本的认识。

静态 SQL 之“静态”，意味着在执行之前就已经明确了该 sql 在数据库执行后的业务含义，也就是对于做啥事儿我们是清楚的，只不过需要知道这事儿的具体内容有哪些。比如“select userID,username from users where deptName=’销售部’”，意思就是查销售部的所有用户 ID 及用户名。 再稍微灵活一点，可以写作“select userID,username from users where deptName=?”，此时语句中的“?”传递哪个部门名称，相对应的就查哪个部门的用户信息。但，sql 本身所要做的“查询某部门下的用户信息”这个事情我们是完全明确的，不确定的只是用户有多少，各自的信息是什么。

而动态 SQL 之所以称为“动态”，就是在是否“明确业务含义”这一点上是“不明确”的，也就是说 sql 应该执行出啥结果，我们本身并没有明确的预期，包括查什么、用哪些条件以及怎么组合这些条件等——原则上，这些都可以随意选择组合。

部门’”时，执行结果是查询某部门的用户信息；当传入“roleName=’技术主管’”时，则是按照角色筛选复合条件的用户信息。尽管都是返回用户信息，但不同条件下，我们可以认为是两个不同的业务，这里的 sql 我们就称之为是动态的，显然，这样的 sql 执行后得到的结果的业务含义也是完全不确定的。

同理，使用的物理表也可能不固定，例如定义为“select … from ${table} where …”时，”table”给定啥值就从哪个表查数据。

结合两者的具体含义，可以分析得到各自的优劣所在：

l 静态 SQL： 功能固定，比较传统，但相对比较安全。

l 动态SQL： 自由灵活，但同时不得不提到sql植入风险，一旦被攻击者利用可能的sql漏洞，会有相当严重的安全问题，如窃取用户信息、篡改数据等等。

而在常用的报表工具使用场景中，报表开发人员一般都没有DBA的功力，因此难以对sql的安全性问题考虑周全，动态SQL可能带来的风险也就很难排除。而要规避动态SQL风险，无非是下面两种方法：

1、 让dba配合工作，尽可能协助提供安全性高的sql；

2、 希望报表工具可以提供规避sql注入风险的方法。

方法 1 需要依赖外部DBA的配合，不是总能满足，所以，比较可行的，还是在考察报表产品时，考虑报表工具是否提供防sql植入风险的功能支持。不然，在安全第一的前提下，就只能不用动态sql，退而选择静态sql了，毕竟安全还是最重要的，一旦造成信息泄露，责任很难承担。

下面，我们就对比一下常见的两种报表工具，Birt和润乾报表，看看它们各自在动态SQL以及安全性方面的表现如何：

2、 应用程序来实现： 这个太麻烦，需要有Java开发能力，咱们就不介绍了，感兴趣的可以到网上搜索自行研究。

下面我们通过实例来讲解一下在数据集中通过script脚本拼接sql的方式。

注：例子以“员工表”为例，员工表内存放有“工资”及“应发工资”两个字段，我们通过动态SQL实现由终端用户选择应该查询“工资”列还是“应发工资”列

1、 新建报表并新增数据源（hsql）

如，数据集名称为“dt”,query text为“select * from 员工”。实际这里可以定义为空，但为了保存等操作不报错，这里需要随便写个 sql。

脚本如下（含义为当qField传进来是“工资”时，拼入工资字段查数据，为“应发工资”时则对应查询，默认查询工资列）：

前面 6 列为常规列，拖拽字段到 dataRow 即可，第 7 列位动态列，这里当根据传入参数的不同，显示不同的标题（header row）及数值（data row），其中

（1） 当输入qField参数为“应发工资”时

（2） 当输入qField参数为“工资”时

暂且不考虑上述操作涉及的复杂的制作过程，即便辛苦做出了效果，我们也不难发现其中留有很大漏洞，那就是这种拼串的方法很容易出现 sql 植入问题。

比如，当攻击者尝试给 qFiled 参数传入“身份证号”，而恰好又存在该字段的话，用户的身份证号信息就全部暴露了，同样，用类似的方法还可以猜到“电话号码”、“家庭住址”，等等等等，这些敏感信息的全面泄露，意味着安全保障的不堪一击。

而针对脚本这种做法，想要全面规避风险非常麻烦，可能的方式是在脚本里加入大量的判断，尽力排除所有 “身份证”、“电话号码”、“手机号码”等各种情况。对于一两个报表这么搞还行，多了肯定就费劲了，并且后期维护也得蒙圈。

润乾报表动态sql实现

同样以“员工表”为例，员工表内存放有“工资”及“应发工资”，我们来实现由终端用户选择应该查询“工资”列还是“应发工资”列。

润乾报表采用动态参数实现：

（1） 连接demo数据源

（2） 用向导生成报表

通过向导，一步生成如下网格式报表模板

（3） 修改数据集，增加动态列

A、增加报表参数“qFiled”

B、 数据集sql中增加动态列

采用宏的方式引入，sql改为如下

（4） 修改报表模板，增加动态列

A、 qFiled传入“应发工资”

很显然，仅从做法而言，润乾报表就比birt要简单的多，不需要写任何脚本即可实现，对开发人员的技术要求也不高，对应后期维护也就很轻松了。

当然这里也存在sql植入的风险，但是润乾报表作为一款商业软件，厂商已经为用户考虑到了，可以通过专门的配置规避风险，具体使用也很简单，但功能很强大。

关于sql植入及报表规避的有专门的文章做详细的介绍，可参考《报表的SQL植入风险及规避方法》url：** 报表的 SQL 植入风险及规避方法

不管是开源还是商业报表，可能对于某功能的实现都没什么问题，但制作方法的简便性、考虑问题是否周全（比如安全性问题）等方面的差距就可能会非常明晰。

开源报表固然有它的优点——不用花钱！！！ 但工作量相对要大很多，服务或支持也没有保障，只能靠用户自己埋头苦干去研究了。而商业报表则是一条捷径，虽然要花一点点 Money，但考虑到效率及安全性方面，那就真是完全可忽略的成本了。

更多报表及安全问题请查看:基础部署相关问题分类导航 报表类相关问题分类导航
* 报表的 SQL 植入风险及规避方法
* 动态交叉表头报表的制作
* 如何制作动态层分组报表