精华内容
下载资源
问答
  • 林晓斌MySQL是怎么保证高可用的
  • mysql结构: 主要由server层和引擎两个部分组成,server层由连接器,查询缓存,分析器,优化器 执行器组成,引擎有innoDb,memory,myISAM等引擎 连接器主要与客户端建立连接,获取权限,使用show processlist显示...

    一.一条sql是如何运行得
    mysql结构:
    主要由server层和引擎两个部分组成,server层由连接器,查询缓存,分析器,优化器
    执行器组成,引擎有innoDb,memory,myISAM等引擎
    连接器主要与客户端建立连接,获取权限,使用show processlist显示当前有多少个连接。
    command这一列表示连接状态。如果客户端连接一直没有使用,一段时间后将会被干掉,由wait_timeout指定,
    默认8小时。
    mysql拿到查询请求后,会先请求查询缓存,查询缓存以key-value形式(key为sql,value为结果)保存了
    所有sql得查询结果。查询缓存失效频繁,只要有对一个表的更新,表上所有查询缓存都会失效,建议关闭查询缓存,使用
    query_cache_type关闭查询缓存。
    分析器校验sql语句得语法,识别表名,列名等。
    优化器决定索引得使用,join时决定表得执行顺序
    执行器执行器会先校验有无查询权限,有权限则调用引擎接口取数据。慢查询日志中row_examined字段代表
    查询扫描得行数(不一定准确)

    二.更新语句如何执行
    write-ahead-log(wal): 先写日志,再写磁盘

    redo log:
    innodb引擎特有,redo log保证mysql得crash-safe,异常重启记录不会丢,redo log大小固定,由四个文件构成,
    write pos记录当前位置,check point记录要擦除得位置,从头到尾循环写到开头,write pos追上
    check point时得先擦除日志,持久化到磁盘。

    binlog:
    一种归档日志,在server层,所有引擎都可使用,没有crash-safe能力,采用追加写入方式,
    通过binlog_format参数设置binlog格式,有row,statement,mix三种格式。

    为什么要有binlog日志?
    binlog是server层得所有引擎共用,redo log是引擎层得,innodb引擎特有。

    sql执行时得事务流程:
    两阶段提交:先写入redo log,处于prepare阶段,再写binlog,最后再提交事务,处于commit
    阶段,事务完成。
    如果先些完redo log再写binlog或者先写完binlog再写red log,都会发生主从不一致得问题。两阶段提交
    使得两个日志得提交不会产生中间状态,使得redo log和binlog保持一致。

    优化建议:
    innodb_flush_log_at_try_commit参数设置成1,每次事务都持久化到磁盘,sync_binlog也设置成1,
    保证mysql重启binlog不会丢失。

    三.事务隔离
    实现:
    每条更新记录都会生成一条回滚操作,生成一个read-view,记录上得最新值,
    通过回滚操作,都可以得到前一个状态得值。

    优化建议:
    1.auto_commit=0手动长时间不提交(如果是长连接)可能导致长事务,尽量不要使用长事务,
    长事务会导致undo log被保留很长时间,占用大量存储空间,所以auto_commit尽量设置为1,让事务自动提交,
    使用commit workand chian语法,提交事务时自动开启下一个事务。—长事务还会导致MDL读锁一直不释放
    2.通过set MAX_EXECUTION_TIME控制语句最长执行时间,防止某些事务因为意外执行太长时间。
    3.可以在information_schema库得innodb_trx中查询长事务,监控该表,设置长事务预警值超过就报警。
    4.innodb_undo_tablespaces设置成2或更大值,设置后清理回滚段更方便。为什么?。

    四.索引上
    常见索引类型:
    hash表:区间查询很慢,新增很快(联想ArrayList和linkedList),只适合等值查询场景
    hash冲突:拉链法解决

    有序数组:范围查询和等值查询性能好,使用二分法查找,更新麻烦。

    树:二叉树每个节点左儿子小于父节点,父节点小于右节点,树深度过高,不适合磁盘读写,
    mysql采用B+树,是一种N叉树,树深较小,读写性能好,

    mysql中的索引:
    主键索引:以id为树节点,节点上挂行数据。
    普通索引:叶子节点挂id值,也称二级索引

    索引维护:
    mysql数据以数据页存储,插入数据要维护索引,插入一页里中间值需要逻辑上移动后面的值,这时如果数据页满了要申请新的数据页(页分裂),
    页分裂会影响会影响空间利用率,当两个相邻页中由于删除了数据,利用率很低之后,会将数据页合并。

    建索引策略:
    a字段较大,b字段较小情况下,已经有(a,b)联合索引,单独用到b字段查询是否有必要建立b字段索引和a字段索引,此时已经
    没必要建立a索引了,如果通过调整联合索引顺序可以避免少创建一个索引,那么优先采用,如果
    必须要建立让b条件走索引,那么可以建立(b,a)索引和较小的b索引。

    索引下推:
    在索引遍历过程中,先找出符合索引字段的记录的id,减少了回表次数。

    索引重建:
    使用alter table T engine=innoDB。

    优化:尽量使用自增主键,可以防止页分裂。

    六.全局锁和表锁
    全局锁:全局锁可用作逻辑备份
    1.使用Flush tables with read lock(FTWRD),让整个库处于只读状态,增删改,事务提交,数据定一
    等语句将被阻塞。如果客户端发生异常,会自动释放全局锁,
    2.使用mysqldump工具,使用参数-single-transation时,导数据之前启动一个事务,开启一致性视图,但是myISAM
    不支持事务的引擎不能使用。
    3.使用set global readonly = true,这个逻辑一般会用来判断这个库是主库还是从库,而且如果
    客户端发生异常,将导致整个库一致处于不可写状态。

    表锁:

    1. 语法是lock tables 。。。 read/write,会限制别的线程也会限制自己。比如线程1执行
      lock tables t1 read,t2 write,t1加读锁,则线程1和其他线程都只能读t1,t2加写锁,其他线程不能读写t2,
      线程1可以读写t2.
      2.元数据锁(metedata lock,MDL):为了防止查询时多出或少列,对一个表增删改查时加
      MDL读锁,更改表结构是加MDL写锁。MDL读锁之间不互斥,两个写锁,读写锁之间互斥。
      MDL锁在语句执行完不释放,要等到事务结束后才释放,所以再加字段时先看下是否有长事务在
      执行。
      七.行锁
      行锁就是对数据行加锁,innodb事务中,行锁是在需要的时候才加上的,但要在事务结束才释放,这就是两阶段锁协议。如果说事务中需要锁多行,
      那么把最容易影响并发度的往后放,比如一条插入语句和一条更新语句,如果把更新语句放前面,那么一开始就加了行锁,直到事务提交时才释放,如
      果放后面,那么可以延迟加锁的时机,尽可能行锁的时间。

    死锁
    两个线程相互等待对方的锁会形成死锁,

    死锁解决:
    1.设置innodb_lock_wait_timeout,直接进入等待,直到超时。默认50s,

    2.设置innodb_deadlock_detect=on,开启主动死锁检测,默认开启,每个线程获取锁时都要判断自己所依赖的线程是否被别人锁住,
    会性能损耗,所以需要控制并发度,考虑中间件实现或者修改mysql源码。

    八.事务隔离还是不隔离

    例子:可重复读级别下,id=1行的k初始值为1,事务A由于一致性读,B和C事务都是在它启动之后才提交的,
    所以A事务看不见这两个更新,如果看见了那就是幻读了,而对于B事务来说,C事务是在B事务启动之后才提交的,B事务的更新语句,
    实际上是一个当前读,它是能看到C事务的更新的,所以B事务最后的查询结果是3,而如果B事务看不到C事务的更新,那C事务的更新就白白更新了,出现了数据错误。

                         事务A:                      事务B                                               事务C                                                
    
       
    ↓时间    start transation with consistent | 
                                                                                                     | 
                                               |    start transation with consistent                 | 
                                               |                                                     | update t set k=k+1 where id=1;   
    
                                               |                                                     |       
                                               | update t set k=k+1 where id=1;                      | 
    
                                               | select * from t where id=1;                         |
    
             select * from t where id=1;       |                                                     |
             commit;                           |                                                     |
                                               | commit;                                             |
    

    所以mysql的数据是有多个版本的,每个版本有自己的row_try_id,每个事务有自己的一致性视图。

    九.普通索引和唯一索引的选择
    普通索引和唯一索引在查询性能上相差不大,性能差异主要体现在更新时。
    更新一条数据时, 如果内存中有就直接更新,如果没有innoDb会将这些操作缓存到changeBuff中,
    ()changeBuff是poolBuff的一部分),这样就不用加载数据页了,mysql后台线程定期将changeBuff merge入磁盘。
    唯一索引因为要判断数据是否重复,必须将数据页全部读入内存,再更新内存,而普通索引只需将操作缓存在changeBUff中即可,
    可见在更新数据是唯一索引的性能差。要尽可能多得让更新操作缓存在changeBuffer中,提升更新性能。

    changeBuffer通过innodb_change_buffer_max_size来动态设置,代表占用poolBuffer的比例。

    优化建议:
    1.如果你的查询后面马上跟着要对这条数据进行查询,那么changeBUffer作用就发挥不出来了,这时候
    应该关掉changeBuffer。

    2.历史数据的库将changeBuffer尽量开大,在机械硬盘的情况下,提升写入性能。

    3.如果业务可以接受的情况下或者业务可以保证不会插入重复数据尽量选择普通索引。

    十.mysql如何选择索引
    选择索引的条件:
    1.扫描行数越少,消耗的cpu资源越少,也就越快

    2.列区分度:使用show index from t查看列的基数(不同值的个数),基数越接近总行数,性能越好,
    mysql采用采样的方式获取表的基数M,当更新数据数大于1/M是触发索引统计,所以在一边删除历史数据一边插入数据
    的业务场景下会发生重新索引统计,进而导致选错索引问题

    3.回表代价:mysql如果认为直接扫描数据行更快,则不会走索引。

    4.是否排序:如select * from t where a between 1 and 100 and b between 1 and 10000 order by b;
    这时候虽然b扫描行数较多,但是按照b排序,b本身就是索引,天然有序能省去排序,如果选择a,在a索引筛选出
    数据后回表查出的数据还要按照b字段排序,mysql认为这样代价更大,于是选择了索引b。

    解决mysql索引错误选择:
    1.analyze table t可以用来重新统计索引信息。
    2.sql中加上force index(a),不灵活,迁移数据库类型时可能语法不兼容。

    十一.给字符串加索引

    前缀索引:
    过程:先搜索索引,找到满足条件的字段值得id,回表再判断一次该字段。
    好处和问题:空间占用小,定义好长度,既能节省存储索引的空间,又能
    减少扫描行数,但是扫描行数会增加,还有可能减小列的区分度,继续增加扫描行数,
    必须回表所以无法使用覆盖索引的优化。

    例子:身份证号前6位都一样如果使用前缀索引,那么列的区分度会很低,那么注意到身份证号
    的后6位是区分度很大的,可以使用倒序存储方式,如:select field1,field2 from t where id_card = reverse(‘input_id’);
    或者建一个hash字段存储id_card的hash值,这两种方式共同的问题是都不支持范围查询

    十二.Mysql为什么会抖一下
    刷脏页:
    1.redo log写满了,此时拒绝所有更新请求
    2.查询请求太多,直接从bufferPol中内存页没有数据,需要从磁盘读取,此时要申请新的内存数据页
    ,然后bufferPool内存不足了,需要淘汰内存页,如果淘汰的是脏页,需将脏页flush到磁盘,会影响性能。
    3.mysql正常关闭时会把所有脏页都flush到磁盘
    4.mysql在空闲状态下也要见缝插针的刷脏页。

    1和2需要尽量避免,所以需要正确的设置innodb_io_capacity变量,需要知道磁盘的iops(磁盘的最大io能力,可以通过fio工具来测试),
    尽量不要接近75%。

    ps:刷脏页时如果邻近的数据页也是脏页,就会一起被刷掉,innodb_flush_neighbors=1时生效,为0时只刷自己的。
    mysql8.0后,这个配置已经默认是0了。

    十三.删除数据,表文件大小不变
    建议表文件放置单独一个文件xx.idb,即设置参数innodb_file_per_table=on,这样删除表时直接drop table,即可回收表空间,
    innodb_file_per_table=off时表数据存放在系统共享空间。

    对于删除数据行,mysql采用的是标记删除的方式,不会回收表空间,比如pageA中id=4到id=7之间的数据id=5被删除,
    该位置标记为可复用,插入数据id=6数据时,会复用这个位置,而如果是插入id=10,则复用不了,因为数据不在4-7之间;
    如果整个页都被delete了,则整个页被标记可复用,插入的id无限制;如果相邻的两个页利用率都低,mysql会合并页到一页,
    另一页标记为可复用。
    插入也会造成空洞,也即页分裂,当向一页插入数据时是按序存入的,如果当前页满,则会被插入下一页。

    所以增删改查都是会造成空洞,解决空洞使用alter table t engine = innodb重建表,mysql会新建临时表,
    然后自动完成转存数据,交换表名,删除旧表。转存数据会比较耗时,整个ddl过程不是online ddl,期间的任何
    旧表数据写入会丢失。
    mysql5.6后,这个操作支持online ddl,这个过程中允许日志写入和旧表的增删改查。

    十四.count()这么慢
    count(
    )实现方式,myISAM中存储了每张表总数,所以直接返回即可,而innodb需要需要一行行读取数据累计和。而innodb
    不存储总行数是因为MVCC多版本并发中,每一行记录都要判断是否对当前事务可见,所以只能一行行读,使用show table statue
    也可以统计行数,但是基于采样统计的,不是有一定误差。
    count(*),count(1)返回满足条件的总行数,不会判断是否为null,
    count(字段)返回满足条件的字段中,字段不为null的数据,count(id)同样是此逻辑。

    十五.事务和日志解答
    在redo log prepare之后mysql崩溃,事务会回滚,这时候binlog还没写入,并不会产生数据一致性问题;
    在写完binlog还未commit时mysql崩溃,这时如果binlog存在并且完整,则事务直接commit,否则回滚。此时mysql
    需要判断binlog是否完整,如果是row模式下binlog最后会有一个xid event,statement下会有commit;redo log和
    binlog之间存储的每个操作日志通过xid关联。

    十六.order by工作原理
    explain中的extra有using filesort代表需要排序,mysql为线程分配一个sort bffer用于排序,

    全字段排序:
    先初始化sort buffer,放入查询字段,从索引中找到满足条件的id(如果用不到索引,直接扫描主键索引),回表查询数据行,放入sort buffer,
    最后对索引字段做快速排序,返回客户端。其中按索引字段排序需要看sort buffer的大小(sort_buffer_size),
    如果要排序的数据量小于sort_buffer_size,则使用内存排序,如果大于则会使用磁盘排序,磁盘排序使用归并排序法,
    将排序数据分成12份,分别排序后再合并成一个大文件。

    row_id排序:
    如果单行太大,超过max_length_for_sort_data的值,mysql会使用row_id排序,
    与全字段排序相比多了一次回表操作,sort_buffer中只放入id和排序字段,拍好序后再回表查询其他字段。

    可以使用覆盖索引,优化,做到既不用临时表排序,也不需要回表。

    十八.sql逻辑相同,性能却差很大
    隐式的类型转换:
    比如select “9” > 8,mysql会将字符串转为数字,所以使用不到索引。

    隐式编码转换:
    两个表的编码不一样也会导致编码转换而不走索引

    对于给字段加表达式时,可以尝试将表达式优化到等式右边,避免直接对字段加表达式。

    十九.为什么只查一行,也会很慢

    1.长时间不返回
    大概率表被锁住了,执行show processlist,state列如果有waiting for table
    metedata lock,说明有线程持有MDL锁,这时候可以在mysql启动时指定performance_schema=on,通过查询
    sys.schema_table_lock_waits这张表,可以找出造成阻塞的processId,使用kill 命令断开。

    2.等flush
    show processlist,state列如果有waiting for table flush,flush执行都很快,所以大概率是
    其他长事务把flush堵住,flush又堵住了当前查询。

    3.等行锁
    更新语句事务一直不提交,导致当前更新被锁住,在mysql5.7中使用innodb_lock_waits查找占有
    锁的线程kill之。

    4。查询慢
    设置long_query_time的值为0,

    二十.幻读及其解决
    在可重复读级别下,mysql使用间隙所来解决幻读问题,间隙所之间不会冲突,因为间隙所的目的就是
    要禁止在间隙插入,间隙所和行锁合成next-key-lock,间隙锁是影响性能的。

    在读提交下,会存在幻读,所以也就没有间隙锁。

    二十一.加锁规则
    可重复读下next-key-lock加锁规则:
    1.索引上的等值查询,给唯一索引加锁时,next-key-lock退化为行锁。
    2.索引上的等值查询,向右遍历时且最后一个值不满足等值查询时,next-key lock退化为间隙锁。
    3.只有访问到的才加锁。
    bug:唯一索引上的范围查询,会访问到不满足条件的第一个值为止。

    锁是加在索引上的。

    二十二.mysql饮鸩止渴的提升性能的方法
    1.短连接
    提高max_connections的值,但是连接要经里tcp连接,权限校验等,是比较重的操作,
    提高连接数可能导致系统的负载更重。第一种方法可以考虑断开事务外空闲太久的线程,先用show
    processlist 查看会话状态,找到处于sleeping的连接,innodb下查看information_schema
    库的innodb_trx表查询具体的事务状态(字段为trx_state),事务外的可以用kill connection杀掉;
    第二种方法是重启mysql开启 -skip-grant-tables,跳过连接的权限校验,这样做有安全风险,这时如果mysql版本为8.0,mysql为了安全
    会加上–skip-network,此时,数据库只能被本地客户端连接。

    2.慢查询
    (1)索引没涉及好:
    古老的方案是:在备库设置sql_log_bin=off关闭写binlog,然后备库上加索引,然后
    主备切换,切换后在备库关掉binlog写。
    更好的方案是gh-ost。

    (2)语句没写好
    mysql选错索引可以用force index(x)强制走索引,或者用mysql5.7提供的sql重写功能,
    执行insert into query_rewrite_rules(pattern,replace,pattern_db_name) v; call query_rewrite.flush_rewrite_rules();

    上线前有必要做sql分析,在测试环境打开慢查询日志,设置long_query_time=0,留意慢查询日志中的rows_exmained是否与
    预期相符。

    二十三.mysql是怎么保证数据不丢失的

    1.binlog写入流程
    binlog是写写入binlog cache,每个线程都有自己的binlog cache,当事务提交时,会把binlog cache
    写入文件系统的page cache中,并清空当前线程的binlog cache(称为write),如果binlog cache超过了参数binlog_cache_size
    的大小,就会调用fsync暂存到磁盘(称为fsync)。
    sync_binlog=0时只write不fsync,为1时每次事务提交都会write+fsync,为n时代表n次事务提交才fsync(mysql异常时可能会丢失n次事务)

    2.redo log写入机制
    redo log写入由参数innodb_flush_log_at_try_commit,
    innodb_flush_log_at_try_commit=0,每次只写redo log buffer;
    innodb_flush_log_at_try_commit=1,每次直接持久化到磁盘。
    innodb_flush_log_at_try_commit=2,只写入page cache

    三种情况会将未提交事务的redo log写入磁盘或文件系统page cache
    (1)innodb有后台线程,每秒将redo log buffer写入redo log,再刷盘,这时会将没有提交的事务
    也持久化
    (2)redo log buffer占用空间即将达到innodb_log_buffer_size时,innob主动写盘,
    因为事务没提交,没有调用fsync,只会存在page cache中。
    (3)innodb_flush_log_at_try_commit=1时,事务并行时后一个已提交事务将前一个未提交事务的redo log buffer刷盘。

    所以双1配置需要等待一个binlog 刷盘,一次redo log刷盘。

    疑问?未提交的redo log被刷盘会不会导致脏数据??

    并发场景下mysql提升tps的group commit机制
    mysql会尽量延迟binlog和redo log fsync的时间,减少磁盘刷盘次数,
    可以设置binlog_group_commit_delay(表示延迟多少微秒才fsync)和
    binlog_group_commit_no_delay_count(表示积累多少次才fsync),这两个满足一个即可
    执行fsync,io压力大的系统可以适当提高这两个参数的值。

    二十四.mysql如何保持主备一致

    mysql主从
    主库负责读写,备库负责读,备库执行主库的binlog,保持主备一致,建议设置备库
    为只读,这样可以防止主备切换时的双写造成主从不一致,还有通过read only可以用来判断节点的主从
    状态。
    主备切换流程
    1.备库执行change master命令,设置主库的ip,端口,用户名,密码,以及开始执行binlog
    的位置和日志偏移量。
    2.备库执行start slave命令,备库启动一个io_thread用于维持和主库的长链接,一个sql_thread
    用于接收中继日志relay log
    3.主库校验备库传过来的用户名等,校验成功后从本地读取binlog传给备库
    4.备库拿到binlog后,写到本地文件,sql_thread执行中继日志中的命令。

    binlog的三种格式,由参数binlog_format控制:
    statement:记录sql原文,前后会有begin和commit,statement下回可能导致主备执行时选的索引不一样;
    row:前后同样有begin和commit,没有sql原文,替换成了Table_map(用于说明是哪张表)和delete_rows(用于定义删除行为,删除时有),
    最后会有xid用于和redo log日志关联事务。借助mysqlbinlog 工具还能看到server_id和start position。
    row格式很占用空间。但是能确保主备数据一致。
    mixed:结合statement和row格式的一种混合格式。

    双M模式下的循环复制通过设置每个节点不同的server_id来解决,每个节点传给对方时会把server_id传
    过去,对方执行后又传回来了,这是判断下server_id是自己就不用执行。

    疑问?备库不用开启binlog?

    主库生成的binlog还没来得及发给从库,会导致数据一致性问题,采用semi-sync解决(只能解决一注一从方式)。

    二十五.mysql怎么保证高可用

    mysql主库写入binlog,日志中记录写入时间,传送到备库,备库接收binlog日志到执行完成这个事
    务的时间减去主库传送binlog日志中的时间戳之差,称为主从延迟。在备库执行show slave status,
    会显示seconds behind master表示延迟了多少秒。即使主库的时间有误,备库也会判断主库的当前时间,如果
    有差值,则会减去这个差值。
    主库延迟的根源是备库执行中继日志的速度赶不上主库传送的速度,可能是备库的机器性能比主库差,
    或者备库查询请求太多,压力过大,抑或是大事务执行时间长,导致延迟。

    主从切换的策略:
    1.可靠性优先
    在双M下,在备库执行show slave status,判断seconds_behind_master,如果小于某个值,比如5秒,
    则进行切换,否则重试,把主库设置成read only,备库判断seconds_behind_master,直到为0为止,
    之后把备库设置成可读写,即read only设置为false。
    设置主库read only后整个mysql集群不可写状态。

    尽量使用可靠性优先策略,并且binlog_format设置成row

    二十六.备库为什么会延迟好几个小时

    如果主库生成日志的速度远大于备库执行中继日志的速度,就会导致从库延迟越来越长,永远追不上主库。

    主备并行复制能力:
    mysql5.6以后支持并行复制,即前面的sql_thread变成了coordinator,只负责读取中继日志和
    分发到各个worker,worker是多个线程,由slave_parallel_workers决定(建议设置为8-16比较好),

    coordinator在分配时要保证同一个事务的sql放到同一个worker。更新同一行的两个事务必须放到同一个worker。

    分发策略:
    按库分发,hash库名到一个worker 中,MySQL 5.6 版本的并行复制策略。
    按表分发,需将相同表hash到一个worker 中。
    按行分发,按“库名 + 表名 + 唯一索引 a 的名字 +a 的值”hash到一个worker 中。

    MariaDB 利用了redo log 组提交 (group commit)特性,因为能在一组中提交,一定不会修改同一行。
    MySQL 5.7 并行复制策略由参数 slave-parallel-type 来控制,配置成DATABASE使用5.6版本的策略,
    LOGICAL_CLOCK使用MariaDB 的策略,但进行了优化(针对两阶段提交)。MySQL 5.7.22 新增了一
    个并行复制策略,基于 WRITESET 的并行复制

    二十七.主库出问题了,从库怎么办

    基于位点的主备切换
    当我们把节点 B 设置成节点 A’的从库的时候,需要执行一条change master 命令:
    CHANGE MASTER TO
    //主库A’的信息
    MASTER_HOST=hostnameMASTERPORT=host_name MASTER_PORT=port
    MASTER_USER=usernameMASTERPASSWORD=user_name MASTER_PASSWORD=password
    //同步位点
    MASTER_LOG_FILE=masterlognameMASTERLOGPOS=master_log_name MASTER_LOG_POS=master_log_pos

    最后两个参数表示要从主库的 master_log_name 文件的 master_log_name 文件的
    master_log_pos 这个位置的日志继续同步。而这个位置就是我们所说的同步位点,也就是主库对应的文件名和日志偏移量。
    同步位点很难取到精确位置,因为不能丢数据,需要需要找一个“稍微靠前”的位点,然后判断跳过已经执行过的事务,但是主库异常前的那条语句
    发送给从节点后,从节点执行了一次,并记录了同步位点,当从节点执行change master时,又执行了一遍同步位点时那条语句,导致sql错误或脏数据,
    针对这个问题采用主动跳过事务(设置set global sql_slave_skip_counter=1;start slave;)或者 设置 slave_skip_errors 参数,直接设置跳过指定错误。
    “1032”删除找不到行,“1062”主键冲突。同步完成后,稳定一段时间,去掉该设置 。

    GTID
    MySQL 5.6 版本引入了 GTID,每个mysql实例都维护了一个gtid集合,存储已经执行过的事务
    GTID启动加上参数 gtid_mode=on 和 enforce_gtid_consistency=on。
    GTID 的全称是 Global Transaction Identifier,也就是全局事务 ID,是一个事务在提交的时候生成的,是这个事务的唯一标识。它由两部分组成,格式是:
    //server_uuid实例第一次启动时生成,全局唯一
    //gno一个整数,初始值是 1,每次提交事务的时候分配给这个事务,并+1
    //mysql文档中叫GTID=source_id:transaction_id
    GTID=server_uuid:gno

    如果从库中已经存在了某事务,使用以下方式跳过。前三句执行了一个空事务,并把GTID加到了从库的集合中。

    基于 GTID 的主备切换

    CHANGE MASTER TO
    MASTER_HOST=hostnameMASTERPORT=host_name MASTER_PORT=port
    MASTER_USER=usernameMASTERPASSWORD=user_name MASTER_PASSWORD=password

    //使用GTID协议
    master_auto_position=1
    从库会把自己的GTID集合传给切换的主库,主库会计算差集,然后把不同的同步给从库。现在备库执行
    start slave的逻辑为:
    1.从库设置新主库为a’
    2.备库b把Set_b 发给新主库a’ ,
    3.新主库a’算出set_a与set_b的差集,即存在于set_a但不存在于set_b中的日志,判断
    a‘本地是否包含差集中的所有事务,如果不包含,说明a’已经删除了这些binlog,返回错误,反之
    则发给b执行。之后就从这个事务开始按顺序发给b执行。

    二十八.读写分离有哪些坑

    过期读解决方案:
    1.对于必须读到最新数据的强制走主库,其他的走从库
    2.select sleep(1)让查询线程休眠一秒。用户体验不好,如果查询不足1秒,也要等1秒。

    判断主备无延迟方案
    判断show slave status 结果里的 seconds_behind_master 参数的值是否等于0,但该值精度为秒。
    对比位点确保主备无延迟,Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和
    Exec_Master_Log_Pos 这两组值完全相同,就表示接收到的日志已经同步完成。
    对比 GTID 集合确保主备无延迟,Retrieved_Gtid_Set、Executed_Gtid_Set是否相同。
    上述方案,只会判断从库已经收到的事务,是否执行完,但对于一些主库已执行,但从库还没收到的情况,这时
    Retrieved_Gtid_Set、Executed_Gtid_Set是相同的,但是主库穿过来的那个事务在新从库还是看不到还是存在主备延迟。

    配合semi-sync
    从库执行主库的binlog后,返回ack确认,主库收到ack才返回客户端确认事务完成。
    在一主多从下,如果查询请求落到了没有收到最新binlog日志的从库上,还是会有过期读问题。
    还有如果在业务更新的高峰期,主库的位点或者 GTID 集合更新很快,那么上面的两个位点等值判断就会一直不成立,
    很可能出现从库上迟迟无法响应查询请求的情况。

    等主库同步位点
    select master_pos_wait(file, pos[, timeout]);
    它是在从库执行的;参数 file 和 pos 指的是主库上的文件名和位置;timeout 可选,设置为正整数 N 表示这个函数最多等待N 秒。

    这个会返回一个正整数 M,表示从命令开始执行,到应用完 file 和 pos 表示的 binlog 位置,执行了多少事务。除了正常返回之外,还会返回:

    1.如果执行期间,备库同步线程发生异常,则返回 NULL;
    2.如果等待超过 N 秒,就返回 -1;
    3.如果刚开始执行的时候,就发现已经执行过这个位置了,则返回 0。

    所以可以这么判断:
    1.trx1 事务更新完成后,马上执行 show master status 得到当前主库执行到的File 和 Position;
    2.选定一个从库执行查询语句;
    3.在从库上执行 select master_pos_wait(File, Position, 1);
    4.如果返回值是 >=0 的正整数,则在这个从库执行查询语句;
    5.否则,到主库执行查询语句。
    所以可能存在将流量打到主库的情况,所以需要做好主库限流策略。

    GTID 方案
    select wait_for_executed_gtid_set(gtid_set, 1);
    等待,直到这个库执行的事务中包含传入的 gtid_set,返回 0;超时返回 1。

    MySQL 5.7.6 版本开始,允许在执行完更新类事务后,把这个事务的 GTID 返回给客户端,这样等 GTID 的方案就可以减少一次查询。

    1.trx1 事务更新完成后,从返回包直接获取这个事务的 GTID,记为 gtid1;
    2.选定一个从库执行查询语句;
    3.在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
    4.如果返回值是 0,则在这个从库执行查询语句;
    5.否则,到主库执行查询语句。
    同样请求可能打到主库,注意限流。

    二十九.如何判断一个数据库是不是出问题了?

    select 1 判断
    当前并发查询数超过innodb_thread_concurrency时, select 1会返回,但执行查询命令时会等待。
    该参数默认值是0,表示不限制并发查询数,建议把 innodb_thread_concurrency 设置为 64~128 之间的值。不是并发连接数。

    查表判断
    在系统库(mysql 库)里创建一个表,比如命名为 health_check,里面只放一行数据,然后定期执行:
    但有其他一个问题,更新事务要写 binlog,binlog 所在磁盘的空间占用率达到 100%,那么所有的更新
    语句和事务提交的 commit 语句就都会被堵住。但是,系统这时候还是可以正常读数据的。

    更新判断
    常见做法是放一个 timestamp 字段,用来表示最后一次执行检测的时间。但备库不能写同一行,
    所以需要使用多行,id为server_id。但有可能,机器的I/O已经100%,但刚好健康检查的sql拿到了资源,成功返回了。

    内部统计
    关于磁盘利用率100%的问题。
    MySQL 5.6 版本以后提供的 performance_schema 库,就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。
    老师比较倾向的方案,是优先考虑 update 系统表,,然后再配合增加检测 performance_schema的信息。

    三十.动态看待加锁

    三十三.查很多数据会不会把mysql内存打爆

    mysql边读边发
    执行查询语句时,重复获取行写入net_buffer(默认16k),net_buffer写满就调用网络接口发出去,
    发送成功后清空net_buffer,再重复获取行数据。net_buffer是mysql server层的。
    发出去的数据存在socket send buffer中,如果socket send buffer满了,就等待直到socket send buffer
    重写可写。
    show processlist命令中state显示sending to client就表示socket send buffer写满了,显示sending
    data其实是正在执行的意思,可能处于任意阶段,比如锁等待等。

    全表扫描对innodb的影响
    mysql的innodb层使用buffer_pool来管理内存数据页,采用lru(less recently use)算法,但是,如果对全表扫描,
    就会导致内存数据页一直读磁盘,然后淘汰最近最久不用的数据页,内存命中率很低(show engine innodb status查询),
    查询变慢。
    buffer_pool由参数innodb_buffer_pool_size设置,建议设置成物理内存的60%-80%
    mysql对lru算法进行了优化,将buffer_pool分成了两份,链表头部5/8是young区,链表尾部是old区,
    新申请的内存页都是放在old区,每次访问到old区数据页,判断其存在时间如果超过1秒,则将其移动至
    young区,否则保持不变,所以如果做全表扫描淘汰在old区就可以完成,young就是访问比较频繁的数据了。

    三十四.到底能不能使用join

    NLJ算法(Index Nested-Loop Join)
    如果被驱动表上有索引,采用的是NLJ算法,遍历驱动表取出每行数据,去被驱动表中
    查找符合条件的数据,这个过程对驱动表做了全表扫描,对被驱动表走索引树搜索,当然如果还要查询其他字段,
    需要回表。

    Simple Nestd-loop Join
    如果被驱动表没有使用索引,则每次取到驱动表的数据,都要去遍历被驱动表主键索引查找符合
    关联字段的数据,会很笨重,所以mysql使用的是Block Nested-Loop Join算法。

    BNJ(Block Nested-Loop Join)算法
    如果被驱动表没有使用索引,取到驱动表的数据所有数据放入join_buffer中,也是遍历被驱动表主键索引,
    被驱动表每行数据与join_buffer中驱动表的数据判断关联条件,这样就只用扫描一次被驱动表了,
    explain中extra显示using join buffer nested loop,虽然判断次数和Simple Nestd-loop Join算法一样,
    但是是内存操作,会很快。
    如果join buffer放不下所有驱动表的数据,就将驱动表分为几段,依序放入join buffer,block就是分块的
    意思。由此可得尽量使用NLJ算法,用上被驱动表的索引,如果用BNJ算法,应该用小表做驱动表。
    小表指的是where筛选之后数量比较少的查询结果的表。

    三十五.join语句怎么优化
    MRR(Multi-Range Read)优化
    MRR优化了回表操作,先根据普通索引定位到符合条件的数据,放入read_rnd_buffer中,根据id进行排序,
    直到read_rnd_buffer放满后,去主键索引中顺序查找数据,然后清空 ead_rnd_buffer,如此循环。
    设置参数optimizer_swith=“mrr_cost_based=off”,explain中extra显示using MRR代表开启MRR优化。
    MRR优化将磁盘随机读转化成了顺序读,要多值查询时才能体现这个优化的价值。

    BKA算法(Mysql5.6的Batched Key Access)
    mysql使用BKA算法对NLJ算法进行优化,在使用NLJ算法时在驱动表中就不止取一行数据,而是取多行数据,
    放入join_buffer中,如果join_buffer放不下时,会分段执行。
    这个算法依赖MRR优化,需要开启MRR,通过设置参数set optimizer_switch=“mrr=on,mrr_cost_based=off,batched_key_access=on”

    BNL算法性能问题
    BNL算法中频繁对被驱动表扫描,如果是一个冷表,不仅io的压力大,还会破坏buffer_pool的内存淘汰机制,
    多次扫描导致LRU链表头部总是放入冷表数据,如果冷表很大,还会导致,old区的数据页不到1秒就被淘汰,
    无法进入young区,young区也就没有了淘汰机制,进而降低了内存命中率和复用率。
    针对以上问题可以适当增大join_buffer的大小,减少被驱动表的扫描。

    BNL算法的优化的思路就是BNL算法的优化一般考虑在被驱动表上加索引,如果没可能建索引,可以考虑临时表,
    在临时表对应字段上建索引。

    hashJoin
    将驱动表全部存入hash数组,用被驱动表的关联字段去hash表中查询,这样速度也会很快,可以在
    业务端进行。
    mysql join优化:
    a left join b on a.f1 = b.f1,如果a.f1上没有索引,而b.f1上有索引,即使是a left join b,
    原意是想让a做驱动表,b做被驱动表,因为b.f1上有索引,mysql优化器会将a做为被驱动表。

    四十一.怎么最快地复制一张表?
    如果可以控制对源表的扫描行数和加锁范围很小的话,我们简单地使用 insert … select 语句即可实现。
    mysqldump -hhostPhost -Pport -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where=“a>900” --result-file=/client_tmp/t.sql

    1. –single-transaction 的作用是,在导出数据的时候不需要对表 db1.t 加表锁,而是使用
      START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法;
    2. –add-locks 设置为 0,表示在输出的文件结果里,不增加" LOCK TABLES t WRITE;"
    3. –no-create-info 的意思是,不需要导出表结构;
    4. –set-gtid-purged=off 表示的是,不输出跟 GTID 相关的信息;
    5. –result-file 指定了输出文件的路径,其中 client 表示生成的文件是在客户端机器上
      的。

    导出 CSV 文件
    //导出
    select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’;
    //导入
    load data infile ‘/server_tmp/t.csv’ into table db2.t;

    物理拷贝方法
    在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法,可以通过导出 + 导入表空间的方式,实现物理拷贝表的功能。
    假设我们现在的目标是在 db1 库下,复制一个跟表 t 相同的表 r:

    1.执行 create table r like t,创建一个相同表结构的空表;
    2.执行 alter table r discard tablespace,这时候 r.ibd 文件会被删除;
    3.执行 flush table t for export,这时候 db1 目录下会生成一个 t.cfg 文件;
    4.在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd;这两个命令;
    5.执行 unlock tables,这时候 t.cfg 文件会被删除;
    6.执行 alter table r import tablespace,将这个 r.ibd 文件作为表 r 的新的表空间,
    由于这个文件的数据内容和 t.ibd 是相同的,所以表 r 中就有了和表 t 相同的数据。

    四十五.自增id用完怎么办?

    表定义的id自增值id
    表定义的自增值达到上限后的逻辑是:再申请下一个 id 时,得到的值保持不变。
    这个情况下会报主键冲突了,可以采用长整型作为id,

    InnoDB 系统自增 row_id
    如果你创建的 InnoDB 表没有指定主键,那么 InnoDB 会给你创建一个不可见的,
    长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值,
    所有无主键的 InnoDB 表,每插入一行数据,都将当前的 dict_sys.row_id 值作为要插入数据的
    row_id,然后把 dict_sys.row_id 的值加 1。如果到达上限后,再有插入数据的行为要来申请 row_id,
    拿到以后再取最后 6 个字节的话就是 0,然后继续循环。所以会导致覆盖数据。

    Xid
    redo log 和 binlog 相配合的时候,它们有一个共同的字段叫作 Xid。它在 MySQL 中是用来对应事务的。
    MySQL 内部维护了一个全局变量 global_query_id,每次执行语句的时候将它赋值给 Query_id,然后给这个变量加 1。
    如果当前语句是这个事务执行的第一条语句,那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。
    而 global_query_id 是一个纯内存变量,重启之后就清零了。所以你就知道了,在同一个数据库实例中,
    不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件,这就保证了,
    同一个 binlog 文件里,Xid 一定是唯一的。不过 global_query_id 达到上限后,会继续从 0 开始计数,
    由于 global_query_id 为8个字节,所以一般不会出现到达上限的情况。

    max_trx_id
    Xid 是由 server 层维护的。InnoDB 内部使用 Xid ,就是为了能够在 InnoDB 事务和 server
    之间做关联。但是,InnoDB 自己的 trx_id,是另外维护的。InnoDB 内部维护了一个 max_trx_id
    全局变量,每次需要申请一个新的 trx_id 时,就获得 max_trx_id 的当前值,然后并将 max_trx_id 加 1。
    InnoDB 数据可见性的核心思想是:每一行数据都记录了更新它的 trx_id,当一个事务读到一行数据的时候,
    判断这个数据是否可见的方法,就是通过事务的一致性视图与这行数据的 trx_id 做对比。
    对于正在执行的事务,你可以从 information_schema.innodb_trx 表中看到事务的 trx_id。
    但是对于只读事务,InnoDB 并不会分配 trx_id。

    max_trx_id 会持久化存储,重启也不会重置为 0,那么从理论上讲,只要一个 MySQL 服务跑得足够久,就可能到达上限,
    然后从 0 开始的情况。然后就会导致脏读。但只存在理论上,如果一个 MySQL 实例的 TPS 是每秒 50 万,持续这个压力的话,
    在 17.8 年后,就会出现这个情况。

    事务
    当两个有事务注解的方法相互调用时,
    1.传播级别
    REQUEST_NEW,REQUESTED,NESTED:
    REQUIRED_NEW是来一个线程就开启一个事务,判断当前线程是否在事务中,在就挂起当前事务,
    执行新事务。
    REQUIRED是先判断当前线程是否在事务中,是就加入,不是就创建事务执行。
    NESTED和REQUESTED类似,是基于save point实现的。
    SUPPORT,NOT_SUPPORT:
    SUPPORT翻译为(不)支援其他事务,本身不创建事务,所以来一个线程判断当前线程是否在事务中
    ,级别为SUPPORT时加入事务,级别为NOT_SUPPORT时,抛出异常。
    MANDATORY,NERVER:
    MANDATORY:强制性的支持事务,当前线程无事务抛异常。
    NERVER:永远不使用事务,当前线程有事务抛异常。

    事务不生效:
    1.mysql不是innodb引擎。
    2.没有指定transationManage,或者一个事务涉及多个数据库。
    3.aop使用jdk动态代理,内部对象方法互相调用不会被spring aop拦截

    挂起事务:
    spring将当前事务名,隔离级别,事务信息等保存在一个变量中,将和事务相关的threadlocal
    变量设置为从未开启事务一样,挂起后当前线程就像是无事务状态。
    流程:
    1.获取事务属性(@transational注解中的配置)
    2.加载配置中的transationManager
    3.获取收集事务信息Transation
    4.执行目标方法
    5.出现异常,尝试处理
    6.清理事务相关信息
    7.提交事务

    doBegin方法:
    设置自动提交为false,用threadlocal存储每个数据库连接。

    关键对象:
    PlatformTransationManager:保存当前数据源dataSource,提供提交,
    回滚,挂起,获取事务信息接口。

    TransationInfo:存储关键对象TransationStatus,代表当前运行哪个事务。
    提供方法:isNewTranstion()判断是否新的事务,hasSavepoint()判断当前
    

    事务是否有保存点;setRollbackOnly()和isRollbackOnly()当子事务回滚时,并不会
    回滚事务,而是对子事务设置savePoint;isCOmpleted()事务是否完成。

    展开全文
  • MySQL提高性能的方法
  • 》中,和你讲到 binlog(归档日志)和 redo log(重做日志)配合崩溃恢复的时候,用的是反证法,说明了如果没有两阶段提交,会导致 MySQL 出现主备数据不一致等问题。 在这篇文章下面,很多同学在问,在两阶段提交...

    日志相关问题

    我在第 2 篇文章《MySQL深入学习第二篇 - 一条SQL更新语句是如何执行的?》中,和你讲到 binlog(归档日志)和 redo log(重做日志)配合崩溃恢复的时候,用的是反证法,说明了如果没有两阶段提交,会导致 MySQL 出现主备数据不一致等问题。

    在这篇文章下面,很多同学在问,在两阶段提交的不同瞬间,MySQL 如果发生异常重启,是怎么保证数据完整性的?

    现在,我们就从这个问题开始吧。

    我再放一次两阶段提交的图,方便你学习下面的内容。如下 图1 所示阶段提交示意图:

    这里,我要先和你解释一个误会式的问题。有同学在评论区问到,这个图不是一个 update 语句的执行流程吗,怎么还会调用 commit 语句?

    他产生这个疑问的原因,是把两个“commit”的概念混淆了:

    1. 他说的“commit 语句”,是指 MySQL 语法中,用于提交一个事务的命令。一般跟 begin/start transaction 配对使用。

    2. 而我们图中用到的这个“commit 步骤”,指的是事务提交过程中的一个小步骤,也是最后一步。当这个步骤执行完成后,这个事务就提交完成了。

    3. “commit 语句”执行的时候,会包含“commit 步骤”。

    而我们这个例子里面,没有显式地开启事务,因此这个 update 语句自己就是一个事务,在执行完成后提交事务时,就会用到这个“commit 步骤“。

    接下来,我们就一起分析一下在两阶段提交的不同时刻,MySQL 异常重启会出现什么现象。

    如果在图中时刻 A 的地方,也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前,发生了崩溃(crash),由于此时 binlog 还没写,redo log 也还没提交,所以崩溃恢复的时候,这个事务会回滚。这时候,binlog 还没写,所以也不会传到备库。到这里,大家都可以理解。

    大家出现问题的地方,主要集中在时刻 B,也就是 binlog 写完,redo log 还没 commit 前发生 crash,那崩溃恢复的时候 MySQL 会怎么处理?

    我们先来看一下崩溃恢复时的判断规则。

    1. 如果 redo log 里面的事务是完整的,也就是已经有了 commit 标识,则直接提交;

    2. 如果 redo log 里面的事务只有完整的 prepare,则判断对应的事务 binlog 是否存在并完整:

    a.  如果是,则提交事务;

    b.  否则,回滚事务。

    这里,时刻 B 发生 crash 对应的就是 2(a) 的情况,崩溃恢复过程中事务会被提交。

    现在,我们继续延展一下这个问题。

     

    追问 1:MySQL 怎么知道 binlog 是完整的?

    回答:一个事务的 binlog 是有完整格式的:

    1. statement 格式的 binlog,最后会有 COMMIT;

    2. row 格式的 binlog,最后会有一个 XID event。

    另外,在 MySQL 5.6.2 版本以后,还引入了 binlog-checksum 参数,用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因,可能会在日志中间出错的情况,MySQL 可以通过校验 checksum 的结果来发现。所以,MySQL 还是有办法验证事务 binlog 的完整性的。

     

    追问 2:redo log 和 binlog 是怎么关联起来的?

    回答:它们有一个共同的数据字段,叫 XID。崩溃恢复的时候,会按顺序扫描 redo log:

    1. 如果碰到既有 prepare、又有 commit 的 redo log,就直接提交;

    2. 如果碰到只有 parepare、而没有 commit 的 redo log,就拿着 XID 去 binlog 找对应的事务。

     

    追问 3:处于 prepare 阶段的 redo log 加上完整 binlog,重启就能恢复,MySQL 为什么要这么设计?

    回答:其实,这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在时刻 B,也就是 binlog 写完以后 MySQL 发生崩溃,这时候 binlog 已经写入了,之后就会被从库(或者用这个 binlog 恢复出来的库)使用。

    所以,在主库上也要提交这个事务。采用这个策略,主库和备库的数据就保证了一致性。

     

    追问 4:如果这样的话,为什么还要两阶段提交呢?干脆先 redo log 写完,再写 binlog。崩溃恢复的时候,必须得两个日志都完整才可以。是不是一样的逻辑?

    回答:其实,两阶段提交是经典的分布式系统问题,并不是 MySQL 独有的。

    如果必须要举一个场景,来说明这么做的必要性的话,那就是事务的持久性问题。

    对于 InnoDB 引擎来说,如果 redo log 提交完成了,事务就不能回滚(如果这还允许回滚,就可能覆盖掉别的事务的更新)。而如果 redo log 直接提交,然后 binlog 写入的时候失败,InnoDB 又回滚不了,数据和 binlog 日志又不一致了。

    两阶段提交就是为了给所有人一个机会,当每个人都说“我 ok”的时候,再一起提交。

     

    追问 5:不引入两个日志,也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复,又能支持归档,不就可以了?

    回答:这位同学的意思是,只保留 binlog,然后可以把提交流程改成这样:.... -> “数据更新到内存” -> “写 binlog” -> “提交事务”,是不是也可以提供崩溃恢复的能力?

    答案是不可以。

    如果说历史原因的话,那就是 InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM,设计之初就有没有支持崩溃恢复。

    InnoDB 在作为 MySQL 的插件加入 MySQL 引擎家族之前,就已经是一个提供了崩溃恢复和事务支持的引擎了。

    InnoDB 接入了 MySQL 后,发现既然 binlog 没有崩溃恢复的能力,那就用 InnoDB 原有的 redo log 好了。

    如下 图 2 所示为 只用 binlog 支持崩溃恢复:

    这样的流程下,binlog 还是不能支持崩溃恢复的。我说一个不支持的点吧:binlog 没有能力恢复“数据页”。

    如果在图中标的位置,也就是 binlog2 写完了,但是整个事务还没有 commit 的时候,MySQL 发生了 crash。

    重启后,引擎内部事务 2 会回滚,然后应用 binlog2 可以补回来;但是对于事务 1 来说,系统已经认为提交完成了,不会再应用一次 binlog1。

    但是,InnoDB 引擎使用的是 WAL 技术,执行事务的时候,写完内存和日志,事务就算完成了。如果之后崩溃,要依赖于日志来恢复数据页。

    也就是说在图中这个位置发生崩溃的话,事务 1 也是可能丢失了的,而且是数据页级的丢失。此时,binlog 里面并没有记录数据页的更新细节,是补不回来的。

    你如果要说,那我优化一下 binlog 的内容,让它来记录数据页的更改可以吗?但,这其实就是又做了一个 redo log 出来。

    所以,至少现在的 binlog 能力,还不能支持崩溃恢复。

     

    追问 6:那能不能反过来,只用 redo log,不要 binlog?

    回答:如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉,这样就没有两阶段提交了,但系统依然是 crash-safe 的。

    但是,如果你了解一下业界各个公司的使用场景的话,就会发现在正式的生产库上,binlog 都是开着的。因为 binlog 有着 redo log 无法替代的功能。

    一个是归档。redo log 是循环写,写到末尾是要回到开头继续写的。这样历史日志没法保留,redo log 也就起不到归档的作用。

    一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能,被用在了很多地方。其中,MySQL 系统高可用的基础,就是 binlog 复制。

    还有很多公司有异构系统(比如一些数据分析系统),这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话,这些下游系统就没法输入了。

    总之,由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog,所以“鸠占鹊巢”redo log 还做不到。你看,发展生态是多么重要。

     

    追问 7:redo log 一般设置多大?

    回答:redo log 太小的话,会导致很快就被写满,然后不得不强行刷 redo log,这样 WAL 机制的能力就发挥不出来了。

    所以,如果是现在常见的几个 TB 的磁盘的话,就不要太小气了,直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。

     

    追问 8:正常运行中的实例,数据写入后的最终落盘,是从 redo log 更新过来的还是从 buffer pool 更新过来的呢?

    回答:这个问题其实问得非常好。这里涉及到了,“redo log 里面到底是什么”的问题。

    实际上,redo log 并没有记录数据页的完整数据,所以它并没有能力自己去更新磁盘数据页,也就不存在“数据最终落盘,是由 redo log 更新过去”的情况。

    1. 如果是正常运行的实例的话,数据页被修改以后,跟磁盘的数据页不一致,称为脏页。最终数据落盘,就是把内存中的数据页写盘。这个过程,甚至与 redo log 毫无关系。

    2. 在崩溃恢复场景中,InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新,就会将它读到内存,然后让 redo log 更新内存内容。更新完成后,内存页变成脏页,就回到了第一种情况的状态。

     

    追问 9:redo log buffer 是什么?是先修改内存,还是先写 redo log 文件?

    回答:这两个问题可以一起回答。

    在一个事务的更新过程中,日志是要写多次的。比如下面这个事务:

    begin;
      insert into t1 ...
      insert into t2 ...
    commit;

    这个事务要往两个表中插入记录,插入数据的过程中,生成的日志都得先保存起来,但又不能在还没 commit 的时候就直接写到 redo log 文件里。

    所以,redo log buffer 就是一块内存,用来先存 redo 日志的。也就是说,在执行第一个 insert 的时候,数据的内存被修改了,redo log buffer 也写入了日志。

    但是,真正把日志写到 redo log 文件(文件名是 ib_logfile+ 数字),是在执行 commit 语句的时候做的。

    这里说的是事务执行过程中不会“主动去刷盘”,以减少不必要的 IO 消耗。但是可能会出现“被动写入磁盘”,比如内存不够、其他事务提交等情况。

    单独执行一个更新语句的时候,InnoDB 会自己启动一个事务,在语句执行完成的时候提交。过程跟上面是一样的,只不过是“压缩”到了一个语句里面完成。

    以上这些问题,就是把大家提过的关于 redo log 和 binlog 的问题串起来,做的一次集中回答。如果你还有问题,可以在评论区继续留言补充。

     

    业务设计问题

    接下来,我再和你分享 评论区提到的跟索引相关的一个问题。我觉得这个问题挺有趣、也挺实用的,其他同学也可能会碰上这样的场景,在这里解答和分享一下。问题是这样的:

    业务上有这样的需求,A、B 两个用户,如果互相关注,则成为好友。设计上是有两张表,一个是 like 表,一个是 friend 表,like 表有 user_id、liker_id 两个字段,我设置为复合唯一索引即 uk_user_id_liker_id。

    语句执行逻辑是这样的:以 A 关注 B 为例,第一步,先查询对方有没有关注自己(B 有没有关注 A)select * from like where user_id = B and liker_id = A;如果有,则成为好友 insert into friend;如果没有,则只是单向关注关系 insert into like;但是如果 A、B 同时关注对方,会出现不会成为好友的情况。因为上面第 1 步,双方都没关注对方。第 1 步即使使用了排他锁也不行,因为记录不存在,行锁无法生效。请问这种情况,在 MySQL 锁层面有没有办法处理?

    接下来,我把这个问题的表模拟出来,方便我们讨论:

    CREATE TABLE `like` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NOT NULL,
      `liker_id` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
    ) ENGINE=InnoDB;
    
    CREATE TABLE `friend` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `friend_1_id` int(11) NOT NULL,
      `friend_2_id` int(11) NOT NULL,
      UNIQUE KEY `uk_friend` (`friend_1_id`,`friend_2_id`),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

    虽然这个题干中,并没有说到 friend 表的索引结构。但我猜测 friend_1_id 和 friend_2_id 也有索引,为便于描述,我给加上唯一索引。

    顺便说明一下,“like”是关键字,我一般不建议使用关键字作为库名、表名、字段名或索引名。

    我把他的疑问翻译一下,在并发场景下,同时有两个人,设置为关注对方,就可能导致无法成功加为朋友关系。

    现在,我用你已经熟悉的时刻顺序表的形式,把这两个事务的执行语句列出来,如下 图3 所示为并发“喜欢”逻辑操作顺序:

    由于一开始 A 和 B 之间没有关注关系,所以两个事务里面的 select 语句查出来的结果都是空。

    因此,session 1 的逻辑就是“既然 B 没有关注 A,那就只插入一个单向关注关系”。session 2 也同样是这个逻辑。

    这个结果对业务来说就是 bug 了。因为在业务设定里面,这两个逻辑都执行完成以后,是应该在 friend 表里面插入一行记录的。

    如提问里面说的,“第 1 步即使使用了排他锁也不行,因为记录不存在,行锁无法生效”。不过,我想到了另外一个方法,来解决这个问题。

    首先,要给“like”表增加一个字段,比如叫作 relation_ship,并设为整型,取值 1、2、3。

    1. 值是 1 的时候,表示 user_id 关注 liker_id;

    2. 值是 2 的时候,表示 liker_id 关注 user_id;

    3. 值是 3 的时候,表示互相关注。

    然后,当 A 关注 B 的时候,逻辑改成如下所示的样子。

    应用代码里面,比较 A 和 B 的大小,如果 A<B,就执行下面的逻辑:

    begin; 
    insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
    select relation_ship from `like` where user_id=A and liker_id=B;
    
    /* 代码中判断返回的 relation_ship */
    /* 如果是1,事务结束,执行 commit */
    /* 如果是3,则执行下面这两个语句  */
    
    insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
    commit;

    如果 A>B,则执行下面的逻辑:

    begin; 
    insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
    select relation_ship from `like` where user_id=B and liker_id=A;
    
    /* 代码中判断返回的 relation_ship */
    /* 如果是2,事务结束,执行 commit */
    /* 如果是3,则执行下面这两个语句 */
    
    insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
    commit;

    这个设计里,让“like”表里的数据保证 user_id < liker_id,这样不论是 A 关注 B,还是 B 关注 A,在操作“like”表的时候,如果反向的关系已经存在,就会出现行锁冲突。

    然后,insert … on duplicate 语句,确保了在事务内部,执行了这个 SQL 语句后,就强行占住了这个行锁,之后的 select 判断 relation_ship 这个逻辑时就确保了是在行锁保护下的读操作。

    操作符 “|” 是按位或,连同最后一句 insert 语句里的 ignore,是为了保证重复调用时的幂等性。

    这样,即使在双方“同时”执行关注操作,最终数据库里的结果,也是 like 表里面有一条关于 A 和 B 的记录,而且 relation_ship 的值是 3, 并且 friend 表里面也有了 A 和 B 的这条记录。

    不知道你会不会吐槽:之前明明还说尽量不要使用唯一索引,结果这个例子一上来我就创建了两个。这里我要再和你说明一下,之前文章我们讨论的,是在“业务开发保证不会插入重复记录”的情况下,着重要解决性能问题的时候,才建议尽量使用普通索引。

    而像这个例子里,按照这个设计,业务根本就是保证“我一定会插入重复数据,数据库一定要要有唯一性约束”,这时就没啥好说的了,唯一索引建起来吧。

     

    小结

    我针对前 14 篇文章,大家在评论区中的留言,从中摘取了关于日志和索引的相关问题,串成了今天这篇文章。这里我也要再和你说一声,有些我答应在答疑文章中进行扩展的话题,今天这篇文章没来得及扩展,后续我会再找机会为你解答。所以,篇幅所限,评论区见吧。

    最后,虽然这篇是答疑文章,但课后问题还是要有的。

    我们创建了一个简单的表 t,并插入一行,然后对这一行做修改。

    CREATE TABLE `t` (
      `id` int(11) NOT NULL primary key auto_increment,
      `a` int(11) DEFAULT NULL
    ) ENGINE=InnoDB;
    insert into t values(1,2);

    这时候,表 t 里有唯一的一行数据 (1,2)。假设,我现在要执行:

    update t set a=2 where id=1;

    你会看到如下 图4 所示这样的结果:

    结果显示,匹配 (rows matched) 了一行,修改 (Changed) 了 0 行。

    仅从现象上看,MySQL 内部在处理这个命令的时候,可以有以下三种选择:

    1. 更新都是先读后写的,MySQL 读出数据,发现 a 的值本来就是 2,不更新,直接返回,执行结束;

    2. MySQL 调用了 InnoDB 引擎提供的“修改为 (1,2)”这个接口,但是引擎发现值与原来相同,不更新,直接返回;

    3. InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作,该加锁的加锁,该更新的更新。

    你觉得实际情况会是以上哪种呢?你可否用构造实验的方式,来证明你的结论?进一步地,可以思考一下,MySQL 为什么要选择这种策略呢?

    问题解答:实际情况为选项3。

    第一个选项是,MySQL 读出数据,发现值与原来相同,不更新,直接返回,执行结束。

    假设,当前表 t 里的值是 (1,2),这里我们可以用一个锁实验来确认,如下 图5 所示为锁的验证方式:

    session B 的 update 语句被 blocked 了,加锁这个动作是 InnoDB 才能做的,所以排除选项 1。

    第二个选项是,MySQL 调用了 InnoDB 引擎提供的接口,但是引擎发现值与原来相同,不更新,直接返回。

    有没有这种可能呢?假设当前表里的值是 (1,2),这里我用一个可见性实验来确认,如下 图6 所示为可见性验证方式:

    session A 的第二个 select 语句是一致性读(快照读),它是不能看见 session B 的更新的。

    现在它返回的是 (1,3),表示它看见了某个新的版本,这个版本只能是 session A 自己的 update 语句做更新的时候生成。

    所以,答案应该是选项 3,即:InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作,该加锁的加锁,该更新的更新。

    然后你会说,MySQL 怎么这么笨,就不会更新前判断一下值是不是相同吗?如果判断一下,不就不用浪费 InnoDB 操作,多去更新一次了?

    其实 MySQL 是确认了的,只是在这个语句里面,MySQL 认为读出来的值,只有一个确定的 (id=1), 而要写的是 (a=3),只从这两个信息是看不出来“不需要修改”的。

    作为验证,你可以看一下下面这个例子,如下 图7 为可见性验证方式 的对照:

    展开全文
  • 但是,MySQL 要提供高可用能力,只有最终一致性是不够的。为什么这么说呢?今天我就着重和你分析一下。 这里,我再放一次上一篇文章中讲到的双 M 结构的主备切换流程图。 如下图1 所示为MySQL 主备切换流程 -- 双 ...

    在上一篇文章中,介绍了 binlog 的基本内容,在一个主备关系中,每个备库接收主库的 binlog 并执行。

    正常情况下,只要主库执行更新生成的所有 binlog,都可以传到备库并被正确地执行,备库就能达到跟主库一致的状态,这就是最终一致性。

    但是,MySQL 要提供高可用能力,只有最终一致性是不够的。为什么这么说呢?今天我就着重和你分析一下。

    这里,我再放一次上一篇文章中讲到的双 M 结构的主备切换流程图。

    如下 图1 所示为 MySQL 主备切换流程 -- 双 M 结构图:

     

    主备延迟

    主备切换可能是一个主动运维动作,比如软件升级、主库所在机器按计划下线等,也可能是被动操作,比如主库所在机器掉电。

    接下来,我们先一起看看主动切换的场景。

    在介绍主动切换流程的详细步骤之前,我要先跟你说明一个概念,即“同步延迟”。与数据同步有关的时间点主要包括以下三个:

    1. 主库 A 执行完成一个事务,写入 binlog,我们把这个时刻记为 T1;

    2. 之后传给备库 B,我们把备库 B 接收完这个 binlog 的时刻记为 T2;

    3. 备库 B 执行完成这个事务,我们把这个时刻记为 T3。

    所谓主备延迟,就是同一个事务,在备库执行完成的时间和主库执行完成的时间之间的差值,也就是 T3-T1。

    你可以在备库上执行 show slave status 命令,它的返回结果里面会显示 seconds_behind_master,用于表示当前备库延迟了多少秒。

    seconds_behind_master 的计算方法是这样的:

    1. 每个事务的 binlog 里面都有一个时间字段,用于记录主库上写入的时间; 

    2. 备库取出当前正在执行的事务的时间字段的值,计算它与当前系统时间的差值,得到 seconds_behind_master。

    可以看到,其实 seconds_behind_master 这个参数计算的就是 T3-T1。所以,我们可以用 seconds_behind_master 来作为主备延迟的值,这个值的时间精度是秒。

    你可能会问,如果主备库机器的系统时间设置不一致,会不会导致主备延迟的值不准?

    其实不会的。因为,备库连接到主库的时候,会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致,备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值。

    需要说明的是,在网络正常的时候,日志从主库传给备库所需的时间是很短的,即 T2-T1 的值是非常小的。也就是说,网络正常情况下,主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差,即T3-T2。

    所以说,主备延迟最直接的表现是,备库消费中转日志(relay log)的速度,比主库生产 binlog 的速度要慢。接下来,我就和你一起分析下,这可能是由哪些原因导致的。

     

    主备延迟的来源

    首先,有些部署条件下,备库所在机器的性能要比主库所在的机器性能差。

    一般情况下,有人这么部署时的想法是,反正备库没有请求,所以可以用差一点儿的机器。或者,他们会把 20 个主库放在 4 台机器上,而把备库集中在一台机器上。

    其实我们都知道,更新请求对 IOPS 的压力,在主库和备库上是无差别的。所以,做这种部署时,一般都会将备库设置为“非双 1”的模式。

    但实际上,更新过程中也会触发大量的读操作。所以,当备库主机上的多个备库都在争抢资源的时候,就可能会导致主备延迟了。

    当然,这种部署现在比较少了。因为主备可能发生切换,备库随时可能变成主库,所以主备库选用相同规格的机器,并且做对称部署,是现在比较常见的情况。

    追问 1:但是,做了对称部署以后,还可能会有延迟,这是为什么呢?

    这就是第二种常见的可能了,即备库的压力大。一般的想法是,主库既然提供了写能力,那么备库可以提供一些读能力。或者一些运营后台需要的分析语句,不能影响正常业务,所以只能在备库上跑。

    我真就见过不少这样的情况。由于主库直接影响业务,大家使用起来会比较克制,反而忽视了备库的压力控制。结果就是,备库上的查询耗费了大量的 CPU 资源,影响了同步速度,造成主备延迟。

    这种情况,我们一般可以这么处理:

    1. 一主多从。除了备库外,可以多接几个从库,让这些从库来分担读的压力。

    2. 通过 binlog 输出到外部系统,比如 Hadoop 这类系统,让外部系统提供统计类查询的能力。

    其中,一主多从的方式大都会被采用。因为作为数据库系统,还必须保证有定期全量备份的能力,而从库,就很适合用来做备份。

    备注:这里需要说明一下,从库和备库在概念上其实差不多。在这个专栏里,为了方便描述,会在 HA 过程中被选成新主库的,称为备库,其他的称为从库。

    追问 2:采用了一主多从,保证备库的压力不会超过主库,还有什么情况可能导致主备延迟吗?

    这就是第三种可能了,即大事务。

    大事务这种情况很好理解。因为主库上必须等事务执行完成才会写入 binlog,再传给备库。所以,如果一个主库上的语句执行 10 分钟,那这个事务很可能就会导致从库延迟 10 分钟。

    不知道你所在公司的 DBA 有没有跟你这么说过:不要一次性地用 delete 语句删除太多数据。其实,这就是一个典型的大事务场景。

    比如,一些归档类的数据,平时没有注意删除历史数据,等到空间快满了,业务开发人员要一次性地删掉大量历史数据。同时,又因为要避免在高峰期操作会影响业务(至少有这个意识还是很不错的),所以会在晚上执行这些大量数据的删除操作。

    结果,负责的 DBA 同学半夜就会收到延迟报警。然后,DBA 团队就要求你后续再删除数据的时候,要控制每个事务删除的数据量,分成多次删除。

    另一种典型的大事务场景,就是大表 DDL。这个场景,在前面的文章中介绍过,处理方案就是,计划内的 DDL,建议使用 gh-ost 方案。这里,你可以再回顾下第 13 篇文章《为什么表数据删掉一半,表文件大小不变?》中的相关内容。

    追问 3:如果主库上也不做大事务了,还有什么原因会导致主备延迟吗?

    造成主备延迟还有一个大方向的原因,就是备库的并行复制能力。这个话题,我会留在下一篇文章再和你详细介绍。

    由于主备延迟的存在,所以在主备切换的时候,就相应的有不同的策略。

     

    可靠性优先策略

    在图 1 的双 M 结构下,从状态 1 到状态 2 切换的详细过程是这样的:

    1. 判断备库 B 现在的 seconds_behind_master,如果小于某个值(比如 5 秒)继续下一步,否则持续重试这一步;

    2. 把主库 A 改成只读状态,即把 readonly 设置为 true;

    3. 判断备库 B 的 seconds_behind_master 的值,直到这个值变成 0 为止;

    4. 把备库 B 改成可读写状态,也就是把 readonly 设置为 false;

    5. 把业务请求切到备库 B。

    这个切换流程,一般是由专门的 HA 系统来完成的,我们暂时称之为可靠性优先流程。

    如下图 2所示为MySQL 可靠性优先主备切换流程。

    备注:图中的 SBM,是 seconds_behind_master 参数的简写。

    可以看到,这个切换流程中是有不可用时间的。因为在步骤 2 之后,主库 A 和备库 B 都处于 readonly 状态,也就是说这时系统处于不可写状态,直到步骤 5 完成后才能恢复。

    在这个不可用状态中,比较耗费时间的是步骤 3,可能需要耗费好几秒的时间。这也是为什么需要在步骤 1 先做判断,确保 seconds_behind_master 的值足够小。

    试想如果一开始主备延迟就长达 30 分钟,而不先做判断直接切换的话,系统的不可用时间就会长达 30 分钟,这种情况一般业务都是不可接受的。

    当然,系统的不可用时间,是由这个数据可靠性优先的策略决定的。你也可以选择可用性优先的策略,来把这个不可用时间几乎降为 0。

     

    可用性优先策略

    如果我强行把步骤 4、5 调整到最开始执行,也就是说不等主备数据同步,直接把连接切到备库 B,并且让备库 B 可以读写,那么系统几乎就没有不可用时间了。

    我们把这个切换流程,暂时称作可用性优先流程。这个切换流程的代价,就是可能出现数据不一致的情况。

    接下来,我就和你分享一个可用性优先流程产生数据不一致的例子。假设有一个表 t:

    CREATE TABLE `t` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `c` int(11) unsigned DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    
    insert into t(c) values(1),(2),(3);

    这个表定义了一个自增主键 id,初始化数据后,主库和备库上都是 3 行数据。接下来,业务人员要继续在表 t 上执行两条插入语句的命令,依次是:

    insert into t(c) values(4);
    insert into t(c) values(5);

    假设,现在主库上其他的数据表有大量的更新,导致主备延迟达到 5 秒。在插入一条 c=4 的语句后,发起了主备切换。

    如下 图 3 是可用性优先策略,且 binlog_format=mixed 时的切换流程和数据结果。

    现在,我们一起分析下这个切换流程:

    1. 步骤 2 中,主库 A 执行完 insert 语句,插入了一行数据(4,4),之后开始进行主备切换。

    2. 步骤 3 中,由于主备之间有 5 秒的延迟,所以备库 B 还没来得及应用“插入 c=4”这个中转日志,就开始接收客户端“插入 c=5”的命令。

    3. 步骤 4 中,备库 B 插入了一行数据(4,5),并且把这个 binlog 发给主库 A。

    4. 步骤 5 中,备库 B 执行“插入 c=4”这个中转日志,插入了一行数据(5,4)。而直接在备库 B 执行的“插入 c=5”这个语句,传到主库 A,就插入了一行新数据(5,5)。

    最后的结果就是,主库 A 和备库 B 上出现了两行不一致的数据。可以看到,这个数据不一致,是由可用性优先流程导致的。

    那么,如果我还是用可用性优先策略,但设置 binlog_format=row,情况又会怎样呢?

    因为 row 格式在记录 binlog 的时候,会记录新插入的行的所有字段值,所以最后只会有一行不一致。而且,两边的主备同步的应用线程会报错 duplicate key error 并停止。也就是说,这种情况下,备库 B 的 (5,4) 和主库 A 的 (5,5) 这两行数据,都不会被对方执行。

    如下 图 4 为可用性优先策略,且 binlog_format=row ,我画出了详细过程,你可以自己再分析一下。

    从上面的分析中,你可以看到一些结论:

    1. 使用 row 格式的 binlog 时,数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时,数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题,很可能这时的数据不一致已经不可查,或者连带造成了更多的数据逻辑不一致。

    2. 主备切换的可用性优先策略会导致数据不一致。因此,大多数情况下,我都建议你使用可靠性优先策略。毕竟对数据服务来说的话,数据的可靠性一般还是要优于可用性的。

    但事无绝对,有没有哪种情况数据的可用性优先级更高呢?

    答案是,有的。

    我曾经碰到过这样的一个场景:

    1. 有一个库的作用是记录操作日志。这时候,如果数据不一致可以通过 binlog 来修补,而这个短暂的不一致也不会引发业务问题。

    2. 同时,业务系统依赖于这个日志写入逻辑,如果这个库不可写,会导致线上的业务操作无法执行。

    这时候,你可能就需要选择先强行切换,事后再补数据的策略。

    当然,事后复盘的时候,我们想到了一个改进措施就是,让业务逻辑不要依赖于这类日志的写入。也就是说,日志写入这个逻辑模块应该可以降级,比如写到本地文件,或者写到另外一个临时库里面。

    这样的话,这种场景就又可以使用可靠性优先策略了。

    接下来我们再看看,按照可靠性优先的思路,异常切换会是什么效果?

    假设,主库 A 和备库 B 间的主备延迟是 30 分钟,这时候主库 A 掉电了,HA 系统要切换 B 作为主库。我们在主动切换的时候,可以等到主备延迟小于 5 秒的时候再启动切换,但这时候已经别无选择了。

    如下 图5 所示为可靠性优先策略,主库不可用。

    采用可靠性优先策略的话,你就必须得等到备库 B 的 seconds_behind_master=0 之后,才能切换。但现在的情况比刚刚更严重,并不是系统只读、不可写的问题了,而是系统处于完全不可用的状态。因为,主库 A 掉电后,我们的连接还没有切到备库 B。

    你可能会问,那能不能直接切换到备库 B,但是保持 B 只读呢?

    这样也不行。

    因为,这段时间内,中转日志还没有应用完成,如果直接发起主备切换,客户端查询看不到之前执行完成的事务,会认为有“数据丢失”。

    虽然随着中转日志的继续应用,这些数据会恢复回来,但是对于一些业务来说,查询到“暂时丢失数据的状态”也是不能被接受的。

    聊到这里你就知道了,在满足数据可靠性的前提下,MySQL 高可用系统的可用性,是依赖于主备延迟的。延迟的时间越小,在主库故障的时候,服务恢复需要的时间就越短,可用性就越高。

     

    小结

    今天这篇文章,我先和你介绍了 MySQL 高可用系统的基础,就是主备切换逻辑。紧接着,我又和你讨论了几种会导致主备延迟的情况,以及相应的改进方向。

    然后,由于主备延迟的存在,切换策略就有不同的选择。所以,我又和你一起分析了可靠性优先和可用性优先策略的区别。

    在实际的应用中,我更建议使用可靠性优先的策略。毕竟保证数据准确,应该是数据库服务的底线。在这个基础上,通过减少主备延迟,提升系统的可用性。

    最后,我给你留下一个思考题吧。

    一般现在的数据库运维系统都有备库延迟监控,其实就是在备库上执行 show slave status,采集 seconds_behind_master 的值。

    假设,现在你看到你维护的一个备库,它的延迟监控的图像类似下 图 6,是一个 45°斜向上的线段,你觉得可能是什么原因导致呢?你又会怎么去确认这个原因呢?

    问题答案:

    产生这种现象典型的场景主要包括两种:

    1. 一种是大事务(包括大表 DDL、一个事务操作很多行);

    2. 还有一种情况比较隐蔽,就是备库起了一个长事务,比如

    begin; 
    select * from t limit 1;

    然后就不动了。

    这时候主库对表 t 做了一个加字段操作,即使这个表很小,这个 DDL 在备库应用的时候也会被堵住,也不能看到这个现象。

    有同学说是不是主库多线程、从库单线程,备库跟不上主库的更新节奏导致的?下篇文章中会讲到并行复制,你就会知道,这种情况会导致主备延迟,但不会表现为这种标准的呈 45 度的直线。

     

     

    展开全文
  • 前面我们介绍过索引,你已经知道了在 MySQL 中一张表其实是可以支持多个索引的。但是,你写 SQL 语句的时候,并没有主动指定使用哪个索引。也就是说,使用哪个索引是由 MySQL 来确定的。 不知道你有没有碰到过这种...

    前面我们介绍过索引,你已经知道了在 MySQL 中一张表其实是可以支持多个索引的。但是,你写 SQL 语句的时候,并没有主动指定使用哪个索引。也就是说,使用哪个索引是由 MySQL 来确定的。

    不知道你有没有碰到过这种情况,一条本来可以执行得很快的语句,却由于 MySQL 选错了索引,而导致执行速度变得很慢?

    我们一起来看一个例子吧。

    我们先建一个简单的表,表里有 a、b 两个字段,并分别建上索引:

    CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `a` (`a`),
      KEY `b` (`b`)
    ) ENGINE=InnoDB;

    然后,我们往表 t 中插入 10 万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。

    我是用存储过程来插入数据的,这里我贴出来方便你复现:

    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();

    接下来,我们分析一条 SQL 语句:

    select * from t where a between 10000 and 20000;

    你一定会说,这个语句还用分析吗,很简单呀,a 上有索引,肯定是要使用索引 a 的。

    你说得没错,下 图1 显示的就是使用 explain 命令看到的这条语句的执行情况。

    从上图看上去,这条查询语句的执行也确实符合预期,key 这个字段值是’a’,表示优化器选择了索引 a。

    不过别急,这个案例不会这么简单。在我们已经准备好的包含了 10 万行数据的表上,我们再做如下操作。如下 图2 所示为session A 和 session B 的执行流程。

    这里,session A 的操作你已经很熟悉了,它就是开启了一个事务。随后,session B 把数据都删除后,又调用了 idata 这个存储过程,插入了 10 万行数据。

    这时候,session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。我们可以通过慢查询日志(slow log)来查看一下具体的执行情况。

    为了说明优化器选择的结果是否正确,我增加了一个对照,即:使用 force index(a) 来让优化器强制使用索引 a(这部分内容,我还会在这篇文章的后半部分中提到)。

    下面的三条 SQL 语句,就是这个实验过程。

    set long_query_time=0;
    select * from t where a between 10000 and 20000; /*Q1*/
    select * from t force index(a) where a between 10000 and 20000;/*Q2*/

    第一句,是将慢查询日志的阈值设置为 0,表示这个线程接下来的语句都会被记录入慢查询日志中;

    第二句,Q1 是 session B 原来的查询;

    第三句,Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。

    如下 图3 所示是这三条 SQL 语句执行完成后的慢查询日志。

    可以看到,Q1 扫描了 10 万行,显然是走了全表扫描,执行时间是 40 毫秒。Q2 扫描了 10001 行,执行了 21 毫秒。也就是说,我们在没有使用 force index 的时候,MySQL 用错了索引,导致了更长的执行时间。

    这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。这时,MySQL 竟然会选错索引,是不是有点奇怪呢?今天,我们就从这个奇怪的结果说起吧。

     

    优化器的逻辑

    在第一篇文章中,我们就提到过,选择索引是优化器的工作。

    而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。

    当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

    我们这个简单的查询语句并没有涉及到临时表和排序,所以 MySQL 选错索引肯定是在判断扫描行数的时候出问题了。

    那么,问题就是:扫描行数是怎么判断的?

    MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。

    这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。

    我们可以使用 show index 方法,看到一个索引的基数。如下 图 4 所示,就是表 t 的 show index 的结果 。虽然这个表的每一行的三个字段值都是一样的,但是在统计信息中,这三个索引的基数值并不同,而且其实都不准确。

    那么,MySQL 是怎样得到索引的基数的呢?这里,我给你简单介绍一下 MySQL 采样统计的方法。

    为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。

    采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。

    而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。

    在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:

    1. 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。

    2. 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。

    由于是采样统计,所以不管 N 是 20 还是 8,这个基数都是很容易不准的。

    但,这还不是全部。

    你可以从上 图 4 中看到,这次的索引统计值(cardinality 列)虽然不够精确,但大体上还是差不多的,选错索引一定还有别的原因。

    其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行。

    接下来,我们再一起看看优化器预估的,这两个语句的扫描行数是多少。如下图 5 所示为 意外的 explain 结果。

    rows 这个字段表示的是预计扫描行数。 

    其中,Q1 的结果还是符合预期的,rows 的值是 104620;但是 Q2 的 rows 值是 37116,偏差就大了。而图 1 中我们用 explain 命令看到的 rows 是只有 10001 行,是这个偏差误导了优化器的判断。

    到这里,可能你的第一个疑问不是为什么不准,而是优化器为什么放着扫描 37000 行的执行计划不用,却选择了扫描行数是 100000 的执行计划呢?

    这是因为,如果使用索引 a,每次从索引 a 上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的。

    而如果选择扫描 10 万行,是直接在主键索引上扫描的,没有额外的代价。

    优化器会估算这两个选择的代价,从结果看来,优化器认为直接扫描主键索引更快。当然,从执行时间看来,这个选择并不是最优的。

    使用普通索引需要把回表的代价算进去,在图 1 执行 explain 的时候,也考虑了这个策略的代价 ,但图 1 的选择是对的。也就是说,这个策略并没有问题。

    所以冤有头债有主,MySQL 选错索引,这件事儿还得归咎到没能准确地判断出扫描行数。至于为什么会得到错误的扫描行数,这个原因就作为课后问题,留给你去分析了。

    既然是统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息。我们来看一下执行效果。如下图 6 所示为 执行 analyze table t 命令恢复的 explain 结果。

    这回对了。

    所以在实践中,如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以采用这个方法来处理。

    其实,如果只是索引统计不准确,通过 analyze 命令可以解决很多问题,但是前面我们说了,优化器可不止是看扫描行数。

    依然是基于这个表 t,我们看看另外一个语句:

    select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

    从条件上看,这个查询没有符合条件的记录,因此会返回空集合。

    在开始执行这条语句之前,你可以先设想一下,如果你来选择索引,会选择哪一个呢?

    为了便于分析,我们先来看一下 a、b 这两个索引的结构图。如下 图 7所示为 a、b 索引的结构图。

    如果使用索引 a 进行查询,那么就是扫描索引 a 的前 1000 个值,然后取到对应的 id,再到主键索引上去查出每一行,然后根据字段 b 来过滤,显然这样需要扫描 1000 行。

    如果使用索引 b 进行查询,那么就是扫描索引 b 的最后 50001 个值,与上面的执行过程相同,也是需要回到主键索引上取值再判断,所以需要扫描 50001 行。

    所以你一定会想,如果使用索引 a 的话,执行速度明显会快很多。那么,下面我们就来看看到底是不是这么一回事儿。

    explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

    如下 图8 所示为执行 explain 的结果。

    可以看到,返回结果中 key 字段显示,这次优化器选择了索引 b,而 rows 字段显示需要扫描的行数是 50198。

    从这个结果中,你可以得到两个结论:

    1. 扫描行数的估计值依然不准确;

    2. 这个例子里 MySQL 又选错了索引。

     

    索引选择异常和处理 

    其实大多数时候优化器都能找到正确的索引,但偶尔你还是会碰到我们上面举例的这两种情况:原本可以执行得很快的 SQL 语句,执行速度却比你预期的慢很多,你应该怎么办呢?

    一种方法是,像我们第一个例子一样,采用 force index 强行选择一个索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中,就直接选择这个索引,不再评估其他索引的执行代价。

    我们来看看第二个例子。刚开始分析时,我们认为选择索引 a 会更好。现在,我们就来看看执行效果,如下 图9 所示为 使用不同索引的语句执行耗时:

    可以看到,原本语句需要执行 2.23 秒,而当你使用 force index(a) 的时候,只用了 0.05 秒,比优化器的选择快了 40 多倍。

    也就是说,优化器没有选择正确的索引,force index 起到了“矫正”的作用。

    不过很多程序员不喜欢使用 force index,一来这么写不优美,二来如果索引改了名字,这个语句也得改,显得很麻烦。而且如果以后迁移到别的数据库的话,这个语法还可能会不兼容。

    但其实使用 force index 最主要的问题还是变更的及时性。因为选错索引的情况还是比较少出现的,所以开发的时候通常不会先写上 force index。而是等到线上出现问题的时候,你才会再去修改 SQL 语句、加上 force index。但是修改之后还要测试和发布,对于生产系统来说,这个过程不够敏捷。

    所以,数据库的问题最好还是在数据库内部来解决。那么,在数据库里面该怎样解决呢?

    既然优化器放弃了使用索引 a,说明 a 还不够合适,所以第二种方法就是,我们可以考虑修改语句,引导 MySQL 使用我们期望的索引。比如,在这个例子里,显然把“order by b limit 1” 改成 “order by b,a limit 1” ,语义的逻辑是相同的。

    我们来看看改之后的效果,如下 图10 所示为 order by b,a limit 1 执行结果:

    之前优化器选择使用索引 b,是因为它认为使用索引 b 可以避免排序(b 本身是索引,已经是有序的了,如果选择索引 b 的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。

    现在 order by b,a 这种写法,要求按照 b,a 排序,就意味着使用这两个索引都需要排序。因此,扫描行数成了影响决策的主要条件,于是此时优化器选了只需要扫描 1000 行的索引 a。

    当然,这种修改并不是通用的优化手段,只是刚好在这个语句里面有 limit 1,因此如果有满足条件的记录, order by b limit 1 和 order by b,a limit 1 都会返回 b 是最小的那一行,逻辑上一致,才可以这么做。

    如果你觉得修改语义这件事儿不太好,这里还有一种改法,下 图 11 是执行效果。

    select * from  (select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 100)alias limit 1;

    在这个例子里,我们用 limit 100 让优化器意识到,使用 b 索引代价是很高的。其实是我们根据数据特征诱导了一下优化器,也不具备通用性。

    第三种方法是,在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。

    不过,在这个例子中,我没有找到通过新增索引来改变优化器行为的方法。这种情况其实比较少,尤其是经过 DBA 索引优化过的库,再碰到这个 bug,找到一个更合适的索引一般比较难。

    如果我说还有一个方法是删掉索引 b,你可能会觉得好笑。但实际上我碰到过两次这样的例子,最终是 DBA 跟业务开发沟通后,发现这个优化器错误选择的索引其实根本没有必要存在,于是就删掉了这个索引,优化器也就重新选择到了正确的索引。

     

    小结

    今天我们一起聊了聊索引统计的更新机制,并提到了优化器存在选错索引的可能性。

    对于由于索引统计信息不准确导致的问题,你可以用 analyze table 来解决。

    而对于其他优化器误判的情况,你可以在应用端用 force index 来强行指定索引,也可以通过修改语句来引导优化器,还可以通过增加或者删除索引来绕过这个问题。

    你可能会说,今天这篇文章后面的几个例子,怎么都没有展开说明其原理。我要告诉你的是,今天的话题,我们面对的是 MySQL 的 bug,每一个展开都必须深入到一行行代码去量化,实在不是我们在这里应该做的事情。

    所以,我把我用过的解决方法跟你分享,希望你在碰到类似情况的时候,能够有一些思路。

    你平时在处理 MySQL 优化器 bug 的时候有什么别的方法,也发到评论区分享一下吧。

    最后,我给你留下一个思考题。前面我们在构造第一个例子的过程中,通过 session A 的配合,让 session B 删除数据后又重新插入了一遍数据,然后就发现 explain 结果中,rows 字段从 10001 变成 37000 多。

    而如果没有 session A 的配合,只是单独执行 delete from t 、call idata()、explain 这三句话,会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。

    这是什么原因呢?也请你分析一下吧。

     

    问题解答:评论区有几位同学说没有复现,大家要检查一下隔离级别是不是 RR(Repeatable Read,可重复读),创建的表 t 是不是 InnoDB 引擎。

    言归正传,我给你留的问题是,为什么经过这个操作序列,explain 的结果就不对了?这里,我来为你分析一下原因。

    delete 语句删掉了所有的数据,然后再通过 call idata() 插入了 10 万行数据,看上去是覆盖了原来的 10 万行。

    但是,session A 开启了事务并没有提交,所以之前插入的 10 万行数据是不能删除的。这样,之前的数据每一行数据都有两个版本,旧版本是 delete 之前的数据,新版本是标记为 deleted 的数据。

    这样,索引 a 上的数据其实就有两份。

    然后你会说,不对啊,主键上的数据也不能删,那没有使用 force index 的语句,使用 explain 命令看到的扫描行数为什么还是 100000 左右?(潜台词,如果这个也翻倍,也许优化器还会认为选字段 a 作为索引更合适)

    是的,不过这个是主键,主键是直接按照表的行数来估计的。而表的行数,优化器直接用的是 show table status 的值。

    这个值的计算方法,我会在后面有文章为你详细讲解。

     

     

    展开全文
  • 毫不夸张地说,MySQL 能够成为现下最流行的开源数据库,binlog 功不可没。 在最开始,MySQL 是以容易学习和方便的高可用架构,被开发人员青睐的。而它的几乎所有的高可用架构,都直接依赖于 binlog。虽然这些高可用...
  • 今天这篇文章,我就从这个性能问题说起,和你说说 MySQL 中的另外一种排序需求,希望能够加深你对 MySQL 排序逻辑的理解。 这个英语学习 App 首页有一个随机显示单词的功能,也就是根据每个用户的级别有一个单词表...
  • 在上一篇文章中,介绍了 InnoDB 索引的数据结构模型,今天我们再继续介绍一下 MySQL 索引有关的概念。 在开始这篇文章之前,我们先来看一下这个问题: 在下面这个表 T 中,如果我执行 select * from T where k ...
  • 从文章标题“MySQL 是怎么保证数据不丢的?”,你就可以看出来,今天我和你介绍的方法,跟数据的可靠性有关。 在专栏前面文章和答疑篇中,我都着重介绍了 WAL 机制(你可以再回顾下第 2 篇、第 9 篇、第 12 篇和第 ...
  • 由于 InnoDB 存储引擎在 MySQL 数据库中使用最为广泛,所以下面我就以 InnoDB 为例,和你分析一下其中的索引模型。 InnoDB 的索引模型 在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为...
  • 平时的工作中,不知道你有...在本栏第 2 篇文章《MySQL深入学习第二篇 - 一条SQL更新语句是如何执行的?》中,我为你介绍了 WAL 机制。现在你知道了,InnoDB 在处理更新语句的时候,只做了写日志这一个磁盘操作。这.
  • 同时,MySQL 是支持前缀索引的,也就是说,你可以定义字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。 比如,这两个在 email 字段上创建索引的语句: alter ...
  • 不知道你在实际运维过程中有没有碰到这样的情景:业务高峰期,生产环境的 MySQL 压力太大,没法正常响应,需要短期内、临时性地提升一些性能。 我以前做业务护航的时候,就偶尔会碰上这种场景。用户的开发负责人说...
  • 同样,对于 MySQL 的学习也是这样。平时我们使用数据库,看到的通常都是一个整体。比如,你有个最简单的表,表里只有一个 ID 字段,在执行下面这个查询语句时: mysql> select * from T where ID=10; 我们...
  • 需要说明的是,最后的“结果集”是一个逻辑概念,实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id,然后到原表查到 city、name 和 age 这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回...
  • 在前面的基础篇文章中,我给你介绍过索引的基本概念,相信你已经了解了唯一索引和普通索引的区别。今天我们就继续来谈谈,在不同的业务场景下,应该选择普通索引,还是唯一索引? 假设你在维护一个市民系统,每个人...
  • MySQL 里,有两个“视图”的概念: 1. 一个是 view。它是一个用查询语句定义的虚拟表,在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ,而它的查询方法与表一样。 2. 另一个是 InnoDB ...
  • 之前你可能经常听 DBA 同事说,MySQL 可以恢复到半个月内任意一秒的状态,惊叹的同时,你是不是心中也会不免会好奇,这是怎样做到的呢? 我们还是从一个表的一条更新语句说起,下面是这个表的创建语句,这个表有一...
  • 在前面的第24、25和26篇文章中,介绍了 MySQL 主备复制的基础结构,但这些都是一主一备的结构。 大多数的互联网应用场景都是读多写少,因此你负责的业务,在发展过程中很可能先会遇到读性能的问题。而在数据库层...
  • MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,...
  • 然后你可能就想了,MySQL 怎么这么笨啊,记个总数,每次要查的时候直接读出来,不就好了吗。 那么今天,我们就来聊聊 count(*) 语句到底是怎样实现的,以及 MySQL 为什么会这么实现。然后,我会再和你说说,如果...
  • MySQL 中,有很多看上去逻辑相同,但性能却差异巨大的 SQL 语句。对这些语句使用不当的话,就会不经意间导致整个数据库的压力变大。 我今天挑选了三个这样的案例和你分享。希望再遇到相似的问题时,你可以做到...
  • 如果只从第 8 篇文章《MySQL深入学习第八篇 - 事务到底是隔离的还是不隔离的?》我们学到的事务可见性规则来分析的话,上面这三条 SQL 语句的返回结果都没有问题。 因为这三个查询都是加了 for update,都是当前读...
  • 在上一篇文章中,我和你介绍了几种可能导致备库延迟的原因。你会发现,这些场景里,不论是偶发性的查询压力,...为了便于你理解,我们再一起看一下第 24 篇文章《MySQL 是怎么保证主备一致的?》的主备流程图。 如下
  • 这里,我们还是针对 MySQL 中应用最广泛的 InnoDB 引擎展开讨论。一个 InnoDB 表包含两部分,即:表结构定义和数据。在 MySQL 8.0 版本以前,表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本,则已经允许把表...
  • 今天的文章里,我将会以 InnoDB 为例,剖析 MySQL 在事务支持方面的特定实现,并基于原理给出相应的实践建议,希望这些案例能加深你对 MySQL 事务原理的理解。 隔离性与隔离级别 提到事务,你肯定会想到 ACID...
  • 在上一篇文章中,我和你介绍了间隙锁和 next-key lock 的概念,但是并没有说明加锁规则。间隙锁的概念理解起来确实有点儿难,尤其在配合上行锁以后,很容易在判断是否会出现锁等待的...1.MySQL 后面的版本可能会改变加.
  • 需要说明的是,如果 MySQL 数据库本身就有很大的压力,导致数据库服务器 CPU 占用率很高或 ioutil(IO 利用率)很高,这种情况下所有语句的执行都有可能变慢,不属于我们今天的讨论范围。 为了便于描述,我还是构造...
  • 根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。今天这篇文章,将会分享全局锁和表级锁。而关于行锁的内容,会在下一篇文章中详细介绍。 这里需要说明的是,锁的设计比较复杂,这两篇文章...

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 126
精华内容 50
热门标签
关键字:

林晓斌mysql

mysql 订阅