Mysql.txt

事务的特性
    原子性:指事务包含的所有操作要么全部成功，要么全部失败回滚
    一致性:指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
    隔离性:数据库开启的每一个事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
    持久性:一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的 (redo log保证)

数据库的隔离级别
    读未提交:一个事务还未提交，它所做的变更就可以被其它事务看到
    读提交:一个事务提交以后，它所做的变更才可以被别的事务看到
    可重复读:一个事务执行过程中看到的数据是一致的，未提交的更改对其它事务是不可见的。
    串行化:加锁串行化执行，后访问到的事务必须等前一个事务执行完成才能继续执行

事务隔离的实现
    前提：
        对于读提交，查询只承认在语句启动前就已经提交完成的数据
        对于可重复读，查询只承认在事务启动前就已经提交完成的数据（包括当前读，数据事务id与当前事务id一直的数据）
    实现原理:
        采用多版本控制协议
        每条记录在更新的时候都会同时记录一条回滚操作，事务数据由存储事务Id通过回滚日志获取之前的数据。
        create-version delete-version存储事务Id
        删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除
        创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在当前事务中
    可重复
    a、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题
    b、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题

为什么尽量不要使用长事务
    a)锁定太多的数据，造成大量的阻塞和锁超时，回滚所需要的时间比较长。
    b)执行时间长，容易造成主从延迟
    c)长事务以为这系统里面会存在很老的事务视图，会占用存储空间。

删除数据三种方式如何选择
    a.一次性删除100000条数据 （锁定行记录多，占用锁资源时间长，大事务）
    b.for循环多次少量删除 （资源分片使用，减少锁时间的占用,提高并发）
    c.同时启动多个个链接每条sql删除少量条数据 （死锁检测,浪费cpu资源;多个线程对同一行操作的容易造成死锁）

对比B+树索引与hash索引的区别
    hash索引采用的hash算法，无序，适用于等值查询，针对于唯一键的等值查询o1
    b+树索引是树形结构，有序，适用于范围查询如in like 排序等查询，同时支持联合索引的最左前缀匹配

普通索引与唯一索引的区别，写多读少如何选择索引
    对于普通索引的修改，会将修改操作记录到change buffer中，并同时记录到redo log，
    当需要读取数据或后台线程再将change buffer写入磁盘。(记录change buffer减少随机读）
    唯一索引因为要先读后写所以无法使用change buffer（判断索引的唯一性）
    （change buffer主要减少了随机读的io，redo log将随机写转化为顺序写）
    读数据时，会从磁盘读出数据merge change buffer数据传给应用，记录redo log，此时内存数据则
    和磁盘数据不一致，成为脏页，后续要走脏页刷盘的流程。

为什么mysql有时会抖一下？（除了后台线程还有什么其他情况刷脏页）
    脏页：内存数据与磁盘数据不一致
    1.redo log写满了，系统会停止所有的更新操作，将checkpoint往前推刷脏页，
    这样刷脏页后留下空间可以继续写日志(磁盘压力很小，但性能间歇性下降)
    2.内存不够了，脏页数据需要刷进内存（相邻如果也是脏页也会一起刷）

count 1/* / 主键id/普通字段 有什么区别？
    innodb主键索引的叶子节点是数据，普通索引的叶子节点是主键值，遍历普通索引树性能较好。
    innodb遍历 主键id/普通字段，需要遍历后判断数据(主键id/普通字段)是否为空，有性能消耗。
    1/* 是直接遍历数据后加入统计


如果突然重启，内存中change buffer丢失，数据如何保持一致性？


为什么表数据删除一半，表文件大小不变？

    今天这个 alter table T engine=InnoDB 让我想到了我们线上的一个表, 记录日志用的, 会定期删除过早之前的数据.
    最后这个表实际内容的大小才10G, 而他的索引却有30G. 在阿里云控制面板上看,就是占了40G空间. 这可花的是真金白银啊.
    后来了解到是 InnoDB 这种引擎导致的,虽然删除了表的部分记录,但是它的索引还在, 并未释放.
    只能是重新建表才能重建索引.

    delete只是做标记删除，空间不会回收。

crash safe是如何实现的？
    1.如果客户端收到事物成功的消息，事物就一定持久化了
    2.如果客户端收到事物失败的消息，事务就一定失败了
    3.如果客户端收到执行异常的消息，需要重连后通过查询当前状态来继续后续的逻辑

order by的原理是？如何进行排序的？
    1.根据索引排序，直接在索引树里顺序查找
    2.根据非索引字段排序，需要全部搂到内存中排序
    3.如果内存不够会创建临时文件进行排序


因为这两个表的字符集不同，一个是 utf8，一个是utf8mb4（utf8的超集）使用不上索引
    隐式类型转换编码也是做函数操作
    对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就就决定放弃走树搜索功能


排查sql慢的问题?
    select * from t where id=1;
    show processlist 查看sql状态
        1.Waiting for table metadata lock 等待mdl锁
            查询sys.schema_table_lock_waits表判断阻塞的pid
        2.等flush
        3.事务开启较早，由于需要通过大量undo log计算当前事务开始时的视图数据。

show processlist 参数意义？


幻读产生的原因         // 前提：可重复读 隔离级别下

    行锁无法控制insert
    即使把所有扫描到的行数据都加锁，依然无法控制新的insert，因为加锁时，insert数据行并不存在.

    所以控制幻读，说白了，就是扩大加锁范围，阻止在行与行之间的间隙里，执行insert操作。
    由此，引入了间隙锁，不仅所有扫描到的行加锁，行与行之间的间隙也加上锁。insert操作就被挂起了。


幻读的问题

    语义被破坏：
        select * from t where d=5 for update
        声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。
        而实际上，如果没有间隙锁，这个语义就被破坏了。

    数据一致性问题：
        数据和日志在逻辑上的一致性

        磁盘数据、binlog日志不一致问题


什么是幻读，如何解决幻读问题？
    a.幻读是指一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
    b.在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务提交的顺序的，幻读只能在当前读的情况下出现。
    c.采用 间隙锁gap lock + 行锁record lock 解决幻读问题 合称：next-key lock 前开后闭区间
        // 加 next-key lock(5,10] 操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。
        // 切换到已提交读隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。

        幻读在“当前读”下才会出现
        幻读仅专指“新插入的行”


间隙锁
    产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。
    因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

    间隙锁，锁的就是两个值之间的空隙。

    所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。



    执行 select * from t where d=5 for update 的时候，
    在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

    数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。


冲突关系
    跟行锁有冲突关系的是“另外一个行锁”

    但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

        间隙锁 冲突的是：insert这个动作

        两个间隙锁的加锁，是不存在冲突这一说法的.

        即：你可以加100个(5,10)的间隙锁，只要不涉及insert动作，这100个间隙锁之间就不存在冲突关系。
            它们有共同的目标，即：保护这个间隙(5,10)，不允许插入值。但，它们之间是不冲突的。


next-key lock
    间隙锁和行锁合称 next-key lock，每个 next-key lock 都是 前开后闭 区间。   // (5,10]


间隙锁造成死锁的问题？（可重复读隔离级别下）
    如果id = n 不存在则插入 （间隙锁相互不冲突）                  // insert ... on duplicate key update
    如果用for update 锁住，存在两个连接时，都会锁着间隙空间        // 间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的


mysql加锁规则（前提：InnoDB）
    1.加锁基本单位是next-key lock 前开后闭
    2.查询过程中访问到的对象才会加锁
    3.唯一索引上的等值查询，为唯一索引加锁时，next-key lock会退化为行锁
    4.非唯一索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next key lock会退化为间隙锁
    5.唯一索引上的范围查询会访问到不满足条件的第一个值为止

    // 锁 是加在 索引 上的，这是 InnoDB 的一个 基础设定，在分析问题的时候要一直记得。
    // 锁 加在 主键ID(特殊的唯一索引) 上，则就会加上行锁。


新的问题
    间隙锁 和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

    间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

        // 加锁：锁住id=9的行
        select * from t where id = 9 for update;
        // 不存在...则插入
        insert into t values (9,9,9);

        1、线程A执行加锁，发现 id=9 这行不存在时，行锁 转化为 间隙锁(5,10)
        2、线程B执行加锁，发现 id=9 这行不存在时，行锁 转化为 间隙锁(5,10)
        3、线程B执行insert动作，与线程A的间隙锁(5,10)冲突，线程B等待
        4、线程A执行insert动作，与线程B的间隙锁(5,10)冲突，线程A等待
        5、死锁
            两个 session 进入互相等待状态，形成死锁。
            当然，InnoDB 的 死锁检测 马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。

        --> 原本只想为一行数据加锁，而为了解决幻读引入了间隙锁，普通的加锁变成了：间隙锁 + 行锁。

            锁的范围变大，影响了并发。



性能问题
    1.由新出现的慢查询导致的
        1、索引没有设计好；
        2、SQL语句没写好；
        3、MySQL选错了索引。

    2.由QPS（每秒查询数）突增导致的
    3.更新语句导致的性能问题


write ahead log优势在哪里？
    1.redo log、binlog 都是顺序写，磁盘的顺序写比随机写速度要快
    2.组提交机制（延迟刷盘fsync），可以大幅度降低磁盘iops的消耗（通常配置双1）


binlog的格式有哪几种？
    1.statement 原生sql形式（省空间，存在主备执行不一致的问题）          // 原生SQL                 ->  逻辑
    2.row 存储的是数据的变更（占用空间大，数据能够保证一致性）            // 状态与状态之间的变更      ->  结果
    3.mixed 混合机制


主备延迟可能产生的原因
    1.备库机器性能不如主库
    2.备注被读给占满了cpu影响了备份
    3.有大事务提交，影响了备库的commit(包含大表的DDL)
    4.备库的并行复制能力

kill语句后仍在执行？
    1.线程没有执行到 判断线程状态 的逻辑
    2.终止逻辑耗时较长
        a.超大事务执行被kill，需要回滚数据
        b.大查询回滚，可能生成了大临时文件，删除临时文件io过程较长

buffer pool的淘汰设计应对全表扫描
    buffer pool按照5：3分为old区 young区
        1.查询buffer pool young已有数据时，会将数据放入young区头部，淘汰old尾部
        2.查询buffer pool不存在数据时，插入young区尾部、old区头部，会淘汰old区尾部。
        3.查询old区已有数据时，会判断存在old区存在一定时间存在，则移动到young区头部

join的过程？（小表作驱动表有利）
    1.被驱动表有索引的情况
        扫描驱动表数据后，根据索引去查询被驱动表数据
        （需要根据索引数查询数据）
    2.被驱动表无索引的情况
        将驱动表数据放入join buffer中，顺序扫描被驱动表比较数据
        如果join buffer不够大，会放入一部分数据进入join表，比较后
        清空join buffer继续放入另一部分数据，再继续顺序比较，反复操作

如果join查询被驱动表是一个大表并且是一个冷数据库？
    如果join buffer 不够，驱动表分段就会导致被驱动表多次读，被驱动表是大表
    导致循环读取时间间隔长，会导致buffer pool认为数据时热点数据，清空young区，
    导致buffer pool hit rate命中率极低，别的请求大量读磁盘，阻塞请求。

join优化
    1.被驱动表有索引:将数据批量获取到join buffer 按照id递增排序，顺序读数据
    2.被驱动表无索引:扫描数据 创建符合条件数据的临时表,创建合适索引走第一步索引连表


B+数索引的特点
    1.每个节点中子节点的个数不能超过m，超过后自下向上分裂节点
    2.每个节点中子节点的个数不能少于m/2，少于会合并兄弟节点
    3.叶子节点存储数据，其它节点存储索引，类似跳表
    4.通过双向链表讲叶子节点串行，方便前后区间查询
    5.跟节点可以存储到内存中，其它节点存储在磁盘，节约内存数据


redo log :
    1.物理日志,记录物理页的数据修改
    2.事物prepare写日志,事物提交再写日志 2阶段提交,保证事物的持久性
    3.通过redo log数据恢复到最后一次事物提交的状态
    4. WAL（write ahead log）方式redo log的磁盘顺序写入,后面再异步刷新fsync磁盘数据(随机io)

undo log :
    1.记录数据被修改前的值，可以用来在事务失败时进行rollback；
    2.保证事物的原子性,和隔离性(mvcc)

binlog :
    1.mysql的逻辑日志,用来进行主从复制或数据恢复
    2.redo log和bin log的一致性用两阶段保证
        某事物xid中redo log prepare成功,但bin log不包含此事物的xid,则执行回滚操作
        某事物xid中redo log prepare成功,且bin log包含此事物的xid,则认为该事物成功

如果只有redo log没有undo log
    redo log不够时,数据先刷入到磁盘但是事物失败时无法回滚
如果只有undo log没有redo log
    无法进行异步刷磁盘,必须每次提交时都需要同步将数据刷入磁盘


binlog 的写入机制
    日志 -> binlog cache(内存) -> page cache(文件系统) -> fsync(磁盘)

    事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中.

    write：把日志写入到文件系统的 page cache     // binlog cache(内存) -> page cache(文件系统)
    fsync：持久化到刷盘                         // page cache(文件系统) -> fsync(磁盘)
        sync_binlog 设置为 N，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
        对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。


redo log 的写入机制
    日志 -> redo log buffer(内存) --write--> page cache(文件系统) --fsync--> hard disk(磁盘)

    InnoDB 有一个后台线程
        每隔 1 秒，就会把 redo log buffer 中的日志，
        调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

    日志丢失：
        由于事务并没有提交，所以这时日志丢了也不会有损失。

    事务还没提交的时候，redo log buffer 中的部分日志 有可能被持久化到磁盘      // 组提交 / 后台线程  ->  被别的线程触发提交，顺带着提交了



两阶段 提交           // 重点还是 “提交”!!!        ->   事务提交时，分 “两个阶段” 提交

    事务提交流程，分为 “两个阶段” 提交：

        redo log prepare    ->  binlog  ->  redo log commit
        -----------------------------------------------------------------------------------

        所谓的 prepare、binlog、commit

        都只是 “当前事务-提交” 的一个阶段！！！



并发连接和并发查询
    show processlist
    看到的几千个连接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询。

    并发连接数达到几千个影响并不大，就是多占一些内存而已。我们应该关注的是并发查询，因为并发查询太高才是 CPU 杀手。

    innodb_thread_concurrency 默认0，不限制并发线程(并发查询)。

加锁过程
    锁是一个一个加的，不是一次性加的

避免死锁
    1.由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；

        begin;
        select id from t where c in(5,20,10) lock in share mode;                    // 5 10 20

        select id from t where c in(5,20,10) order by c desc for update;            // 20 10 5

    2.在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，
        所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。


        --------------------------------------------------------------------
        覆盖索引 优化：
            lock in share mode     ->      不回表      ->    主键索引 不加锁
            for update             ->      强制回表    ->    主键索引 加锁      ==> 等效为 update/delete -> 需要更新，所以必须上锁






-----------



MySQL读
    普通读     // 快照读（InnoDB默认：可重复读）
    当前读     // 加锁   lock in share mode / for update
        当前读的规则，就是要能读到所有已经提交的记录的最新值

        update的加锁语义 和 select …for update 是一致的

    间隙锁
        新insert的记录，写锁无法锁住，需要间隙锁
        可重读读的条件下，才会有间隙锁。
        已提交读，是没有间隙锁的。

        加锁单位是 next-key lock（InnoDB默认：可重复读）



MySQL规定
    1.如果对字段做了函数计算，就用不上索引了
    2.有数据类型转换，需要走全索引扫描
    3.字符串和数字做比较，是将字符串转换成数字
    4.表的字符集不同就用不上索引(utf8/utf8mb4)   // 不同字符集会自动做字符集转换 -->  CONVERT(trade_id USING utf8mb4)   函数计算



order by的原理？如何进行排序的？
    1、先查找符合条件的记录
        a.根据索引排序，直接在索引树里顺序查找
        b.根据非索引字段排序，全表扫描
    2、放到内存/文件中排序
        a.如果内存够，将找到的记录行，全部放到内存中排序
        b.如果内存不够，会创建临时文件进行排序
            内存够：    全字段 排序              // sort_buffer
            内存不够：  rowid 排序               // sort_buffer + 临时文件（归并排序）




查询慢
    表锁、行锁和一致性读

        1.查询长时间不返回
            a.等MDL锁
                // 找到谁持有MDL写锁，然后把它kill掉
                SELECT blocking_pid FROM sys.schema_table_lock_waits        // kill process_id

            b.等flush
            c.等行锁
                sys.innodb_lock_waits
        2.查询慢
            select * from t where id=1     是 一致性读   需要到undo log里往回找100W次（InnoDB默认：可重复读）
            ~ lock in share mode 的SQL语句  是 当前读     直接返回当前最新记录



Mysql扩展：

    1、虽然执行过程中可能经过函数操作，但是最终在拿到结果后，server层还是要做一轮判断的.

        table -> `b` varchar(10) DEFAULT NULL       有10W条：b = '1234567890'

        select * from table where b = '1234567890abcd';

            这条 SQL 语句的执行很慢，流程是这样的：

                在传给引擎执行的时候，做了字符截断。

                因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；

                这样满足条件的数据有 10 万行；

                因为是 select *， 所以要做 10 万次回表；

                但是每次回表以后查出整行，到 server 层一判断，b 的值都不是'1234567890abcd';

                返回结果是空。

    2、


监控
    1.服务状态的监控，一般都可以用外部系统来实现；而服务的质量的监控，就要通过接口的响应时间来统计。
    2.healthCheck
    3.按照监控的对象，将监控分成 基础监控、服务监控和业务监控。

大查询会不会把内存用光
    MySQL 是“边读边发的”：
        这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

    对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。
        InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。


能不能使用 join 语句？
    1.如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
    2.如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。
        尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
    1.如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
    2.如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

    结论就是，总是应该使用 小表 做驱动表。

        更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，
        过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。


内存表 和 临时表
    内存表
        指的是使用 Memory 引擎的表，建表语法是 create table ... engine=memory
        这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
        除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。

        内存表不支持行锁，只支持表锁。     // 内存操作，很快，没必要在 锁范围 上纠结！！！

    临时表
        可以使用各种引擎类型。
        如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。
        当然，临时表也可以使用 Memory 引擎。

        session级，对其他线程不可见，不用担心线程之间的重名冲突；session关闭，临时表自动回收。

        在实际应用中，临时表一般用于处理比较复杂的计算逻辑。


分库分表-跨库查询
    1.避免跨库，proxy通过分表规则（比如，N%1024)计算数据所在分表。
    2.无法避免，只能到所有的分区中去查找满足条件的所有行，然后统一做 order by 的操作。
        有两种比较常用的思路：
            1.在 proxy 层的进程代码中实现排序。
                优点：内存操作，处理速度快。
                缺点：需要的开发工作量比较大，复杂，group by，甚至 join 这样的操作，对中间层的开发能力要求比较高。
                    对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。

            2.把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。
                在汇总库(分库中的某一个)上创建一个临时表 temp_t，把分库执行的结果插入到 temp_t 表中 汇总，再执行SQL。


自增主键不连续
    1.唯一键冲突，导致insert失败，自增值并不会改回去。
    2.事务回滚 ~

    // 自增值不回退，MySQL这么设计是为了 提升性能。

    // MySQL 对自增主键锁做了优化，尽量在申请到自增 id 以后，就释放自增锁。


分区表
    MySQL自带的 "分表" 功能

    // 实际生产  -->  成熟的分库分表中间件，用业务分表。



InnoDB无主键表
    InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。  // 代码层面是8字节，表结构只给了6个字节

    InnoDB 维护了一个全局的 dict_sys.row_id 值，
    所有无主键的 InnoDB 表，每插入一行数据，
    都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后把 dict_sys.row_id 的值加 1。

    row_id：0 ~ 2^48 -1，到上限后，从0重新开始，循环...

Xid



---------
边读边发

    MySQL 是“边读边发的”

    如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。