Sql执行流程Server层1.连接器负责处理客户端的连接请求分配一个线程来处理该连接每个连接线程会创建一个会话session在这个会话中客户端可以发送SQL语句进行增删改查等操作。2.解析器解析树解析sql语法和含义3.优化器评估SQL语句不同的执行计划并选择最优的执行计划考虑哪些索引可用、哪种连接方法效率最高以及如何最小化查询的成本。4.执行器调用存储引擎的API来操作数据执行查询、更新、插入等操作。存储层存储引擎1.InnoDBacid事务2.MyISAM不支持事务和外键但提供了表级锁定机制。存储空间效率高。3.Memory数据存储在内存中读写速度极快。但数据会在服务器重启时丢失。4.CSV数据存储在CSV文件中每个表对应一个CSV文件数据交换方便excel我拿最复杂的update举列需要先查询到数据在更新数据。update table set nameA where id1先到mysql的server层过连接器再过查询缓存若果开了的话key/value类型的mapsql是key更新表的某一行就会全清除。再过词法分析器分析sql语法优化器选择索引到执行器调用底层的存储引擎innodb。所有的dml操作都是在bufferpool内存里面进行的。1.先查询bufferpool内存里面是否存在id为1的记录若无则从磁盘根据索引查询并且加载到内存。2.若有则记录这条记录原来的值至undolog buffer 等待内核函数fsync调用用于回滚。3.更新内存的值并且记录操作至redolog buffer再将数据写入page cache再调用内核函数fsync顺序写入磁盘标志为Prepare。4.等待server引擎顺序写binlog写完以后将redolog的Prepare改外commit此时就可向客户端返回提交成功。因为后台的io线程会以page页的方式随机写入磁盘也不怕mysql宕机因为redolog可以重放至于为什么需要预写redolog因为mysql执行很多情况下操作了不同的表都在不同的磁道上而redolog顺序写非常快等cpu不繁忙再进行处理这和很多金融系统面对额外的突发流量预写日志后台处理策略一样。此外这里还有一个redolog buffer写入磁盘的策略默认是一条sql写至page cache 再入磁盘可以配置调整为一条sql写page cache等待操作系统一秒一次的fsync函数写入磁盘。这样只要操作系统不是突然没电就算是数据库宕机了也不会丢数据压测update语句可以提示10%的性能有些评论日志系统可以这么使用。各类锁锁都是逻辑上锁索引的其次只要读读可以并行写锁上了都不能读mysql写锁上了可以读是因为有特别的mvcc机制。1.全局锁锁整个数据库在数据库mysqldump备份的时候所有写操作会阻塞所以 mysqldump --singletransition 参数必加会使用mvcc机制保证读到数据快照当然最好在备库进行数据备份。2.表锁锁住整张表不能进行写操作如update时候没有走索引全表扫描其他的写操作都进不来。3.意向锁某个表已经有行锁了会在这个表上标一个标记其他事务想要获取表锁就必须等待用了这个标记就不用扫描全部记录判断是否有行锁了。4.行锁锁主了唯一索引一行记录。5.间隙锁、临键锁。锁定一个范围比如 update的时候 id30,那就锁大于30的行称为间隙锁id3030也会被包含进去30这条记录就是临键锁。这么多锁实际在判断的时候可以采用mysqlSHOW PROCESSLIST查看主要看Host看客户端连接的ip、State看sql执行的状态是否锁定、info就是具体的sql就能排查是什么sql进行了长时间的占用操作是否存在没走索引全表扫描的情况。数据库只有两条记录id10,20id自带主键索引。那么修改id8的时候此时这记录不存在会锁定-无穷到存在的10这一区间无法新增或者修改id12则会索引10-20这一区间无法写。id10则会把10带进去锁定10至正无穷。日志机制undolog用于开启事务没有提交的时候直接回滚。redo Log用于数据库故障恢复记录了数据文件物理级别的修改那个page的地址的值修改了顺序写。binlog记录了全部的sql执行日志用于数据同步和恢复有Statement记录sql本身存储比较小但是类似now函数在进行同步重放会不一致。row默认模式记录数据行的变化修改前后的值都会记录存储比较大。fixed使用now函数会记录成raw否则记录Statement。要是接受日志格式不统一可以换成fixed。slow query log慢查询日志分析慢查询。事务传播行为REQUIRED (默认): 如果当前没有事务就新建一个事务如果已经存在一个事务中加入到这个事务中。这是最常见的选择。REQUIRES_NEW当你希望方法在一个全新的事务中运行时使用即使它被另一个已经运行的事务方法调用。spring 中采用TransactionManager实现当开启事务时将当前的连接信息放置Threadlocal事务状态设置为new调用数据库的begin当遇到REQUIRES_NEW时暂存当前的连接信息后开启新事物防至Threadlocal状态也设置为new当代码结束遇到commit时判断当前是否是newnew则提交跳出当前事务执行外层事务同样也遇到new执行提交这样就分开进行事务控制了要是require则内部的状态为new 状态为false内部的事务就和外部一起了。隔离级别读未提交一个事务可以读取到另一个事务未提交的数据。脏读、不可重复读、幻读。读已提交一个事务只能读取到另一个事务已经提交的数据。.不可重复读、幻读。可重复读一个事务在执行期间读取到的数据始终保持一致。幻读。MySQL通过mvcc实现默认。串行化所有事务必须按顺序依次执行。脏读事务A读取了事务B尚未提交的数据如果事务B发生错误并执行了回滚操作那么事务A读取到的数据就是脏数据。不可重复读在同一事务内多次读取同一数据返回的结果有所不同。这通常是因为另一个并发事务在两次读取之间修改了该数据。一行数据。幻读当事务A按一定条件读取数据后事务B在事务A再次读取之前插入了符合其查询条件的新数据导致事务A再次读取时出现了“幻影”般的记录。结果集行数不同。mysql通过了特别的mvcc机制保证了事务开启后完全的读快照直接解决了以上问题包括幻读但是任就存在脏写的问题。比如原金额为0A先到达、先开启设置金额为20。B后到达、后开启查询原金额在原金额的基础上加2后设置金额。理论上应该是22。但是最终是2。因为mvcc机制导致A事务设置的20还没提交b事务就能读取到原来的0之后A提交事务设置为20b也提交事务设置为2。最终结果2若串行化则统一条数据连同时读写都会加锁能解决。但是一般在代码层面上加互斥锁保证完全的先来先到或者相加操作直接使用sql的set 去相加。MVCC简单说使用undolog和readview实现事务之间读读、写读并行写写互斥。undolog版本控制readview只能看到自己的事务版本数据。id name txid unlogchain address1 Y y1begin1 Y 1 y11 A 1 y1 a11 B 1 a1 a2comitbegin1 Y 2 y11 Y 2 y11 C 2 y1 c1comitCanal和DataX进行在线和离线场景异构数据迁移同步业务场景上线两个月两亿条数据平均查询分页查询一页记录需要6秒太慢了加上选的是机械硬盘。mysql索引一二两层页表常驻内存。第三层叶子节点在硬盘。字段类型用的long8字节加上6字节的指针每条记录按照0.5kb计算三层b树1200个叶子指针两层16kb每页大小最多存放1200120016*2就是4千4百万两亿数据需要四层树。二层到三层一次磁盘io需要50ms三层到四层又需要400ms大打折扣。1.上线一个jar监听mysql的binlog发送至kafa先不消费。2.mysql分批次拉取历史数据发至mq。3.下游消费者直接hash分区入库。两亿条数据跑了一天半。4.等待一次性的任务处理完成后打开binlog变化的消费者做幂等性判断后入库。5.k8s滚动升级优雅停机无缝切换数据一条不丢经过count查询。6.若是更加关键业务请二次对比log日志。ClickHousev20.81.内存表批量写入mergetree。2.物化视图聚合结果。3.分区多线程分区并行查询结果。4.列式存储按照列存储方便数据压缩和聚合查询。同类型的有doris多里斯、startrocks。TDenginev2.8.11.类似ck列式存储按照tag可建立索引进行分区分区多线程并行查询。2.按照时间时序把各个列分开存储。并且时间既有b树索引又有hash索引范围和精确查找贼快。3.结合其存储特性不断地按照时序进行单列聚合运算效率很高。我们的业务场景是根据应用id和apiid做tag进行分区之后将所有的流量数据存入TDengine设定好流式计算函数按照一秒钟一次计算自动将结果存入结果表。如计算某一api每秒钟的请求流量大小的和。同类型的influxDB因为集群收费而且支持国产化。