mysql

mysql
    存数据
    取数据
    快（索引，各种缓存空间 change buffer），
    安全-->日志（redo log和binlog发生异常的时候，数据不会丢失），
    可拓展-->日志，事务（集群），
    事务（对多个数据行进行操作的时候.是一组操作集合，多条sql语句的集合。），
    锁（避免数据共享竞争）



数据库锁隔离级别的实现
    隔离级别的出现是为了满足相关的业务场景的使用。比如银行核对账户月初的账户总额和月底的的账户总额和消费数据。
    可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的

    
    数据库会创建一个视图。访问的时候（事务内的查询结果）以视图的逻辑结果为准。
        可重复读，事务启动的时候创建。
        读提交，每条sql语句开始执行时创建。
        读未提交，直接返回记录上的最新值。
        串行化，用加锁的方式，避免并行访问。
        
    可重复级别的实现，
        每条记录在更新的时候都会记录一条回滚操作。记录上最新的值，通过回滚操作，可以得到前一个状态的值。
        
        更具体的实现：
            每个事务都有唯一的事务id(transaction id)
            每行数据都会是有多个数据版本的，每次事务更新行数据的时候，都会把事务id赋值给数据版本row trx_id.
            
            每个事务启动时，会构造一个数组，记录所有已经启动但是未提交的事务id.找到这行的数据版本的row trx_id比这个数组的最小值，就是当前事务可见的数据。通过这行当前的数据，和undolog，就可以找到这个事务当前可见的数据。（事务的一致性视图。）
            
            补充：
            更新数据都是先读后写，只能使用当前读。当这行数据被另一个事务更新时，会加上写锁，在事务提交时，才会释放锁。两阶段锁协议（需要时加上，事务提交时才释放）。
            
            
锁，
    乐观锁， 读写锁不互斥。    相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

    悲观锁， 读写锁互斥
        共享锁（读锁）
            表锁
        排他锁（写锁）
            行锁
            表锁
        
    行锁
        select不加锁，但可通过如下语句加锁
            共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
            排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
        UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁    
        
        两阶段锁协议，更新时加锁，事务提交时才释放锁。
        
        行锁升级为表锁：
            如果不通过索引检索数据（update后面的where语句），那么innodb将会对表中所有的行家加锁，跟表锁一样。
            
        间隙锁：
            使用范围条件而非等值查询的时候(update后面的where语句)，并请求共享或者排他锁时。innodb会对已经存在的数据加锁，并且对于条件范围内不存在的数据记录(间隙)加间隙锁。
            
        
    表锁
        读锁阻塞写，不会阻塞读。写锁既会阻塞读，又会阻塞写。
        select 读锁
        UPDATE、DELETE、INSERT 写锁
        
    MDL锁
        作用是防止DDl和DML的并发冲突。读写数据与表格结构的冲突。
        增删改查，MDL读锁
        表结构变更，MDL写锁。
        读锁之间不阻塞，读写锁之间阻塞，
        当有写锁阻塞，后面请求的读取也会阻塞。（因此要避免长事务）
        
        online DDL
            拿MDl写锁。
            降级成MDL读锁。
            真正做DDL。
            升级成MDl写锁。
            释放MDl锁。
        
        
    全局锁
        FTWRL:Flush tables with read lock,数据库处于只读状态，数据更新语句，数据定义语句，事务提交语句都会被阻塞。用于全库逻辑备份。
        备份的另一种做法：使用mysqldump命令，mysql会启动一个事务，来确保拿到一致性视图。在备份的过程中可以正常的更新。
binlog and redlog    
    binlog
        记录的是这个语句的原始逻辑
    redo log更新语句
        先将记录写到redo log，再更新内存。
        记录的内容是：在某个数据页做了什么修改。
    redo log两阶段提交
        引擎写redo log，处于prepare状态
        执行器写binlog
        通知执行引擎将redo log将状态改为commit（提交事务）。
        
        （redo log 分为prepare 和 commit阶段提交。）
    如果不两阶段提交
        redo log没有提交成功，binlog提交成功，主库数据事务回滚，备库数据没有回滚
        redo log提交成功，binlog没有提交成功，主库数据正常，备库数据缺少。
        
    在不同的阶段，mysql发生异常重启是怎么保证数据的完成性的。
        prepare之后，binlog之前，恢复时，事务回滚，binlog没有完成，不会传到备库。
        binlog写完，redolog没有commit.redolog的prepare完成，如果binlog的事务完成，那么提交，否则回滚。
            mysql是怎么知道binlog是否完成？
                mysql可以通过checksum来判断binlog是否完成。statement格式的binlog，结尾会有commit,row格式的binlog最后会有XID event.
            redo log 和binlog是怎么关联起来的？
                有共同的数据字段，
                

    change buffer更新语句
        节省随机读磁盘的io消耗
    
        数据页在内存，直接更新，
        不在内存，将更新操作缓存在change bugger中。
        查询时，将数据页读取到内存，会meger“数据页和changge buffer”
        在内存有拷贝，也会写入到磁盘
        
        唯一索引不适用change buffer ,在索引校验唯一性的时候，会将数据页读取到内存，直接更新内存更快。

            
        在实际中，redo log和change buffer是不同概念。
            redo log记录的是改变change buffer这个动作。
            redo log主要节省的是随机写磁盘的io消耗，而change buffer主要节省的则是随机读磁盘的io消耗。
            
    buffer pool
        内存数据页存储在缓冲池中
        
            
    刷脏页
        脏页和干净页
            内存数据页跟磁盘数据不一致，这个内存页就叫脏页
            内存数据页跟磁盘数据页一直，这个内存页叫干净页。
        
        什么时候刷？
            redo log 满了.
                【mysql在更新数据的时候，写内存（数据页在内存上，直接更新内存，不在内存写change buffer），写redo log（记录的就是内存数据页的变更或者changebuffer的变更）】
                当redo log 满了，不再接受更新的请求，所有的更新请求会被堵住。
            系统内存不足，需要淘汰旧的数据页，
            系统空闲时
            mysql正常关闭
            
        什么情况下的刷脏页会影响性能？
            1.一次查询要淘汰的脏页太多。查询时，要把数据读入到内存中的。当内存不足时，就要刷新脏页。
            2.日志写满，更新全部堵住。写的性能为0.
            
            那有什么应对情况？
                控制刷盘速度。
                    脏页比例，上线75%
                    redo写盘的速度。
                    
        怎么刷脏页？
            数据页在内存中，直接将内存数据更新到磁盘中，
            数据页不在磁盘中，将数据读取到内存中，然后redo log更新内存的内容。更新完成之后写盘同上一个步骤。
            


        
    mysql在两阶段提交的的不同阶段提交发生异常重启，是如何保证数据完整性的？
        prepare之后，binlog之前，事务回滚
        binlog之后，commit之前，事务提交。（恢复数据时，mysql会判断redolog是否已经commit,如果没有的话，就会判断binlog是否完整，如果完整，那么就提交事务。 redo log 和binlog有个字段对应起来XID）
        
    为什么binlog没有crash_safe能力？
        1.mysql在设计之初，myisam就没有支持崩溃恢复的能力。而innodb既可以支持事务，又支持崩溃恢复。所以就干脆使用innodb的崩溃恢复。
        2.binlog没有能力恢复数据页。比如事务已经提交，binlog已经生成，但是更新的数据只在数据页，如果数据库崩溃了，那么数据页的数据就会丢失，但是binlog并不会重新应用这个事务的日志。
        
    redo log为什么不能代替binlog.
        binlog可以归档，redo log循环写，写完了会删除。
        mysql依赖binlog.高可用的基础就是binlog复制。异构架构系统靠消费binlog日志来更新自己的数据。
        
    redo log和binlog是怎么保证数据的完整性的？23节
        只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复
        binlog写入机制
            事务执行过程中，先写入到binlog cache,事务提交时，binlog cache写入到binlog中。
            一个事务的binlog是不能被拆开的，不管事务多大，也要保证一次写入。
            每个线程一个，参数 binlog_cache_size 控制 binlog cache 内存的大小。超过了这个参数规定的大小，就要暂存到磁盘。
            事务提交的时候，执行器会把binlog cache 写入到binlog中，分为两步
                write 写入到文件系统的page cache
                fsync 真正写盘 此时才会占用IOPS（衡量计算机存储设备的性能方式，每秒的读写次数）
            参数 sync_binlog 取不同的值时，write和fsync是有不同的表现的。
                sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
                sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
                sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。  生产上常见设置100~1000，风险是会丢失N个事务的binlog日志。
        redo log写入机制
            事务的执行过程中，先写到redo log buffer 后写入到redo log
            redo log的三种状态
                存在redo log buffer中。
                写到磁盘write,但是没有持久化fsync.物理上是在文件系统的page cache里面（写入很快）
                持久化到磁盘，也就是fsync.（写入很慢）
             innodb_flush_log_at_trx_commit 参数决定了redo log的行为
                设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
                设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
                设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
        WAL 机制是减少磁盘写主要得益于两个方面：
            redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
            组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
        MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？
                设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
                将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
                将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。
                
            
        
索引
    索引常见模型（这只是常见的索引模型，并不代表说mysql有这种类型的模型）：
        数据库的数据模型决定了使用场景。
        哈希表，有序数组，搜索树
        哈希表：
            数据结构，以key-vulue的形式存储.key就是你要的查询条件，value就是你要的查询数据。
            存储时，用hash函数把key转换成一个确定的位置，value就放在数组的这个位置，当有重复位置时，会拉出一个链表。
            查询时，用hash函数计算这个key,找到对应的具体位置，遍历链表的数据，找到需要的数据。
            优点：查找速度快
            缺点：只适合等值查询，不适合范围查询。
            使用场景：等值查询场景。Memcached 和 nosql引擎。mysql只有memory引擎支持。
        有序数组
            数据存储在有序数组中
            优点：适合等值查询和范围查询，查找非常快，使用二分法查找。
            缺点：更新数据成本高，比如往中间插入一个数据，在中间位置插入新的数据，并且要复制原来的数据。可以java的数组元素的增加。
            使用场景：静态存储引擎，比如2017年某个城市所有的人口信息。mysql没有这种索引类型。
        搜索树
            每个节点的左儿子节点比父节点小，右儿子比父节点大。查找的时间复杂度是O(long(N)).
            索引不止只存在内存中，还要写在磁盘上，为了减少读磁盘，因此查询过程中要尽量访问少的数据块）。因此不适用二叉树，而是使用N叉树（mysql中,在整形字段中，N的值差不多是1200.）
        
    innodb的btree索引
        表都是根据主键顺序以索引的形式存放，数据都是存储在B+树中。
        主键索引：key值是主键，value是整行数据。
        非主键索引：key值是索引列的值，value值是主键值。
        所以基于非主键索引的查询，都要先查询主键值，然后拿着主键值，到主键索引中查询出整行数据。
        
        从性能和空间上来看，表中要有自增索引，一般不适用业务字段做索引。
            如果新插入的键值不是递增，那么可以能导致数据页分裂，不但影响到性能，还会影响到数据页的利用率（比如，一个数据页，分裂成两个，那么利用率只有50%）
            因此，一般的表都需要一个自增主键，从而避免触发叶子节点的分裂。
            由于非主键索引的value值都是主键值，那么主键长度越小，普通索引的叶子节点就越小，占用的空间就越小（因此主键的字段类型越小越好，最好是整型）
        什么场景是适合业务字段做主键的？
            1.只有一个索引（不用考虑其他索引的叶子节点大小）
            2.索引必须是唯一索引。（避免回表，每次都要搜索两棵树）
        
        索引优化：
            自增唯一主键
            覆盖索引：
                从非主键索引上查询到主键的值，然后到主键索引上查询需要的数据，叫做回表。
                覆盖索引，索引上有我们需要的查询结果，不需要回表，这种索引叫做覆盖索引。使用覆盖索引会提升性能，缺点是浪费内存空间。
            最左前缀：
                几个字段组合成的索引，查询条件是联合索引的前几个字段，也是可以走索引查询的。爬楼梯式的例子。  这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
                如何安排字段的顺序？
                    第一原则是：通过调整顺序少维护一个索引，是优先考虑的。
                    第二原则是：空间原则。假如有ab两个字段都要建索引，a字段大，b字段小，那么建立联合索引ab,和索引b.
            索引下推：
                联合索引中，如果查询条件在联合索引中不满足。在mysql5.6之前是查询到了这个数据，还是会进行回表查询。在mysql5.6之后，如果不满足条件的直接过滤掉。不在回表查询。
                

高可用  24节
    mysql的高可用架构，是通过主备一致性，主备延迟小来得以保证的。
    主备复制的基本原理
        什么是主备：节点A负责更新，节点B同步A更新的数据，一般备库B设置只读的状态。只读状态对supper权限用户是无效的，可以继续同步。
        原理：
            备库B和主库A维持一个长连接，主库A有一个线程专门服务于备库B这个长连接。
            1.备库通过change master 命令，得知主库的IP,端口，账号，密码，以及这次要请求的位置。
            2.备库执行start slave命令，启动两个线程，io_thread和sql_thread,io_thread负责跟主库建立连接。
            3.主库校验完账号，密码，从指定的位置发送binlog给到B.
            4.备库B拿到binlog，写入中转日志（relay log）
            5.sql_thread读取中转日志，解析命令，执行sql语句
    
    binlog的三种格式
        statement
            记录的是sql原句。对哪个库哪个表，做了什么操作。还有xid（事务是否被正确的提交）. delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;
            可能会出现主备不一致的情况。在主库走的是A索引，在备库走的是B索引。mysql认为这是有风险的。
        row
            当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样
            row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。
            通过借助mysqlbinlog工具，可以查看具体的内容
                server id 1，表示这个事务是在 server_id=1 的这个库上执行的；
                Table_map event表示要操作哪个库，哪个表。
                可以得知各个字段的值。
                binlog_row_image 的默认配置是 FULL
                因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息
                Xid event，用于表示事务被正确地提交了
        mixed
            为什么有mixed格式？
                statement可能会导致主备不一致，row占用空间，mysql会判断语句是否会引起主备不一致，是的话就使用row格式，不是的话就使用statement。
                
        生产推荐使用row格式。好处是可以恢复数据。
            delete可以转为insert，因为保存了删除整行的数据
            insert可以转为delete,可以准确定位到刚才删除的信息
            update记录更新前和更新后的句子，只要把event对调即可。
            
        用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行
        
    循环复制问题的解决方案
        mysql中互为主备的双master结构，
            规定两个库库的server id不同，
            A传binlog给B B传回binlog给A的server id相同。
            A判断到B传回来的server id是自己，那么就丢弃这个日志。
            
    主备切换的场景，主备延迟的原因，主备切换的策略参考第25节。
    
    备库延迟好几个小时的原因 26节
        sql_thread如果用的是用单线程，那么就会有这个问题出现。因此有多线程复制。
        按表分发，按行分发，按库分发
        
    一主多从的主备切换流程 27节
        在备库执行命令change master,并指定位点，说明从库要从备库的什么文件的哪个地方开始，同步更新数据。
        但是会有一个问题，如果备库的同步位点之后的更新操作，在从库已经执行了，那么从库接收备库的binlog时，解析并执行语句会报错。
        那么此时，有两种办法解决。
            1.通过命令主动跳过事务，知道不再报错未知。
            2.设置 参数为slave_skip_errors 1062 1032 跳过指定错误。一般报错都是唯一键冲突1062，或者删除数据找不到行1032。