丁奇45总结
查询语句执行流程：连接器-（缓存）-分析器-优化器-执行器-存储引擎
缓存缺点：对一个表执行一次更新操作后，整个表的缓存会被清空，8.0已弃用缓存功能
----02-------------------------------------------
更新语句执行流程：连接器-（缓存）-分析器-优化器-执行器-innodb-redolog-更新缓存-返回客户端......系统空闲时：将redolog-》磁盘
redolog（粉板）：innodb引擎特有日志
如果redolog满了-》将redolog写入磁盘腾出空间，涉及redolog两个坐标 writepos和checkpoint，checkpoint指哪擦哪，writepos指哪写哪，writepos追着checkpoint跑
WAL：write-ahead-logging，粉板与账本配合，先写日志再写磁盘的技术
crash-safe：有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
binlog：MySQL的Server层实现的，所有引擎都可以使用
redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
数据库备份是通过binlog，当从备份数据库恢复数据时用的是binlog
binlog和redolog的两阶段提交：
执行器调用innodb引擎查找待更新数据-innodb查看数据是否在缓存页，在则返回，不在则从磁盘读取到内存再返回-
执行器执行字段操作-写入存储引擎缓存并写入redolog（状态preparing）-告诉执行器可以提交了-执行器写binlog-执行器发送命令给innodb将redolog状态改为commit
如果不用两阶段提交会有什么问题？
先写binlog后写redolog：binlog中有，redolog中没有（磁盘中也没有），从备库恢复数据时磁盘中由没有变成有了
先写redolog后写binlog：redolog中有（磁盘中有），binlog中没有，从备库恢复数据时磁盘中由有变成没有了
redolog写完prepare后，写binlog前发生崩溃crash，重启后会回滚事务
redolog写完prepare，binlog写完后，redolog写commit之前发生崩溃crash，重启后校验：
redolog是否完整，prepare+commit，若完整则提交事务
redolog只有prepare，继续检查对应事务的binlog是否存在，存在则提交事务，不存在则回滚事务
----03-------------------------------------------
当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。
隔离级别：读未提交、读提交、可重复读、串行化
Oracle默认隔离级别是“读提交”，Mysql的Innodb默认隔离界别"可重复读"，对于一些从Oracle迁移到MySQL的应用，为保证数据库隔离级别的一致，你一定要记得将MySQL的隔离级别设置为“读提交”
事务视图、不同隔离级别下读数据结果不一样、事务回滚、长事务
MVCC:多版本并发控制，用于事务回滚。
假设一个值从1被按顺序改成了2、3、4，不同时刻启动的事务会有不同的read-view（一致性读视图），其中记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本。
-------04----------------------------------------
索引模型：hash表、数组、搜索树，各自场景及优缺点
redis使用hash表，因为不必保证有序，插入较快，直接在链表尾部插入即可，但不能做范围查询，比如查x<10的，必须遍历所有key。
innodb索引模型为B+Tree，聚集索引、二级索引
索引维护：B+树为了维护索引有序性，在插入新值的时候需要做必要的维护，涉及申请数据页、页分裂、页合并
哪些场景要使用自增主键？
考虑因素：
1.缺点：二级索引的叶节点存储的是主键的值，要考虑主键过长时对存储空间的影响
2.优点：使用自增主键插入数据时，不会涉及聚簇索引维护的问题，因为是有序追加插入，不涉及到页分裂、页合并
从存储空间的角度选择索引：身份证号做主键，则所有二级索引的叶节点都存储身份证号(String20字节)；若使用int型(4字节)做主键则所有二级索引所占的存储空间要小很多
为什么要重建索引？有什么好处？使索引文件更紧凑节省空间
---------05--------------------------------------
innodb回表查询、覆盖索引不回表
最左索引原则：联合索引的最左M个字段 / 字符串索引的最左N个字符
当创建联合索引（a,b）后，还需要创建一个（b）索引，这时需要考虑ab字段所占的空间，如果b占空间更大，可以考虑将索引换成（b,a）(a)
索引下推（5.6版本才引入）：索引（a,b）-》select x from y where a like "z%" and b="k" and c="m"; 在联合索引中先匹配到a，然后再下推比较b是否满足，然后再回表查c是否满足。减少回表次数。
---------06--------------------------------------
全局锁：
使整个数据库处于只读状态，所有更新、建表、更改表结构操作都会被阻塞，适用于对数据库做逻辑备份(binlog)时使用
FTWRL：mysql提供的加全局锁的命令Flush tables with read lock
表级锁：
表锁：一般是在数据库引擎不支持行锁的时候才会被用到
元数据锁（MDL）：当对一个表增加列、删除列等修改元数据的操作（DDL）时，会自动加上MDL写锁，如果同时有两个增加列的操作，则要排队获取锁；
当对表进行增删改查操作（DML）时要加上MDL读锁，这时修改元数据的操作由于读锁被未释放所以获取不到写锁，所以操作被阻塞，保证用户在增删改查时表结构不会被更改
为表增加/修改/删除字段、增加索引，这些操作会扫描全表，大表尽量不要轻易做这些操作，比如卡表。
session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是MDL读锁，因此可以正常执行。
事务中的MDL锁，在语句执行开始时申请，等到整个事务提交后再释放。
之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。
如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。
如何安全为小表增加字段？ kill长事务、为DDL命令增加等待时间
解决长事务，事务不提交，就会一直占着MDL锁。 在MySQL的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。 但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？ 这时候kill可能未必管用，因为新的请求马上就来了。比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。 MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAIT n这个语法。 ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...
-----07------------------------------------------
行锁：
innodb支持，myisam不支持
两阶段锁协议：行锁是在需要的时候才加上，但要等到事务结束时才释放。
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
死锁：
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁
应对策略：
1.直接进入等待，直到超时，默认50s
2.主动死锁检测（默认开启）：每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这期间要消耗大量的CPU资源。
怎么解决在 热点行更新时进行大量死锁检测，导致的性能问题呢？
考虑将热点行扩展为n行，每次选一行更新，冲突概率变成原来的1/n，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。
---08事务隔离性----知识点较多----------------------------------------
事务数组、低水位高水位、行锁
---09--------------------------------------------
普通索引与唯一索引对比
主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。 唯一性索引列允许空值，而主键列不允许为空值。 主键列在创建时，已经默认为非空值 + 唯一索引了。 主键可以被其他表引用为外键，而唯一索引不能。 一个表最多只能创建一个主键，但可以创建多个唯一索引。 主键和唯一索引都可以有多列。 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。 在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。
1.查询对比：性能差距不大。在普通索引中查到第一条满足条件的记录后还要继续向后查（如果查到数据页末尾，需要继续查下个数据页），直到查到不满足条件的记录为止；唯一索引查到第一条记录后就结束了。
用于被更新的数据页不在缓存中时，不将磁盘数据页读入缓存，而是将更新操作写入changeBuffer，减少磁盘IO和内存占用，提高更新效率
当下次有查询查到这个数据页时，再将它读到缓存，然后将change buffer中的更新操作merge到数据缓存页中，从而保证一致性。
后台线程会定期merge，正常关闭数据库也会merge
2.更新对比：
2.1 更新的数据页在内存中：插入数据时，唯一索引需要先判断唯一性，其他方面两种索引没啥区别。
2.2 更新的数据页不在内存中：
唯一索引：需要先判断保证数据在表中不存在，这个操作必须要将数据页读入缓存进行判断。数据页都已经在内存中了，changebuffer优化机制就用不上了。
普通索引：写入changebuffer就结束了。
changebuffer使用场景：
1.适用写多读少场景，如果更新完数据马上就要读，会频繁触发changebuffer的merge操作将数据页读入缓存，changebuffer机制反而成了负担
2.只对普通索引的更新操作有优化作用，对唯一索引没用
有个DBA的同学跟我反馈说，他负责的某个业务的库内存命中率突然从99%降低到了75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，我发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引，大量的插入操作都要先查找数据页是否在内存中，结果大量不在，都需要先将数据页读入内存，内存命中率降低，bufferpool中内存数据量暴涨导致更新停滞，必须进行清除内存数据页以腾出内存的操作。
changebuffer对比redolog：
redo log 主要节省的是随机写磁盘的IO消耗（随机写磁盘表数据 转成 顺序写redolog日志）
change buffer主要节省的则是随机读磁盘的IO消耗（当需要查询的时候才将数据页读入缓存）
现在要插入两条记录 insert into t(id,k) values(id1,k1),(id2,k2); (id1,k1)所在数据页在缓存中，(id2,k2)所在数据页不在缓存中
1.(id1,k1)插入缓存页，结束。(id2,k2)插入操作写入bufferpool中的changebuffer中，结束
2.两个插入操作均写入redolog
3.后台线程将changebuffer内存与磁盘中内容的同步、后台线程将bufferpool中缓存页与磁盘存储同步
现在要查询两条记录select * from t where k in (k1, k2)；
1.(id1,k1)所在数据页在缓存中，直接拿到
2.(id2,k2)所在数据页不在缓存中，需将数据页读入缓存，取changebuffer中更新操作更新缓存页，返回正确结果
---10--------------------------------------------
explain rows字段是预估待扫描行数，是根据 n个数据页中的平均条目数*数据页总数 得到的近似值
优化器选错索引，有时是由于rows预估错误
1.可使用analyze table xxx 重新统计索引信息
2.可在sql中使用force index("idx_xxx")强制指定索引
3.修改sql引导优化器使用目标索引
4.删除无用索引
----11-------------------------------------------
字符串前缀索引
使用字符串前n个字符创建索引，而不是整个字符串创建索引
优点：索引文件存储内容变少，节省空间
缺点：与全字符串索引相比可能会损失索引区分度、增加回表次数、使覆盖索引失效，所以需合理决定前缀长度
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
如何决定前缀长度？
对比不同前缀长度的去重记录数
select count(distinct email) as L, count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6 from SUser
如何优化前缀索引？
比如身份证号倒序存储，使用倒序索引增加区分度
使用hash索引，但会消耗CPU资源
---12--------------------------------------------
sql语句变慢，可能原因：
1.innodb内存爆满-》更新停滞-》淘汰数据页腾出内存-》如果数据页是脏叶则要进行刷盘同步操作（刷盘后操作对应的redolog就可以擦除了）
2.redolog中writepos追上checkpoint-》更新停滞-》将部分redolog日志刷盘同步到磁盘
InnoDB刷脏页的控制策略：
innodb_max_dirty_pages_pct 是脏页比例上限，默认值是75%
innodb_io_capacity 参数告诉Innodb磁盘的IO处理能力，建议设成磁盘的IOPS值，这个参数决定innodb将一个脏页刷盘时的速率【实战场景：MySQL的写入速度很慢，TPS很低，但是数据库主机的IO压力并不大。经过一番排查，发现罪魁祸首就是这个参数的设置出了问题。】
innodb_flush_neighbors 参数决定刷脏页时是否将相邻的脏页一起刷盘，优点是减少了多次刷盘时的随机IO消耗，缺点是如果是业务操作触发的刷盘，业务响应时间会变长。机械磁盘适合开启连坐刷盘，固态硬盘没必要开启。8.0中已默认关闭连坐刷盘
InnoDB怎么计算刷脏页的速度？
0. InnoDB全力刷脏页的速度 = innodb_io_capacity参数值（记为S）
1. InnoDB根据当前的脏页比例（脏页/总页数，假设为M），算出一个范围在0到100之间的数字F1，M与F1成正比，即脏页越多刷盘速度越快。
2. InnoDB根据redolog当前writepos和checkpoint对应日志序号之间的差值（假设为N，N越大说明两个指针之间的日志范围越大，又因为跨度是环形的，代表马上就要追上，需要更快的速率刷盘才行），也算出一个范围在0到100之间的数字F2。N与F2成正比。
3. InnoDB刷脏页的速度 = max(F1,F2)% * S
机械硬盘IOPS≈300，SSD IOPS≈[3000, 30000]，将机械硬盘换到固态硬盘时更改innodb_io_capacity参数值告诉innodb最新的最大刷盘速率、关闭连坐机制，因为对SSD来说没有必要节省这么点随机IO
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机IO。机械硬盘的随机IOPS一般只有几百，相同的逻辑操作减少随机IO就意味着系统性能的大幅度提升。
而如果使用的是SSD这类IOPS比较高的设备的话，我就建议你把innodb_flush_neighbors的值设置成0。因为这时候IOPS往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少SQL语句响应时间。
---14--------------------------------------------
Innodb中的count(*)需要全表扫描：
需要判断每行是不是对当前事务可见，以保证innodb默认的可重复读隔离级别。（比如启动事务A执行count(*)，执行完之前事务B插入了一条记录，那为了保证可重复读，这条记录对事务A是不可见的）
Innodb中对count(*)的优化：
对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。因此，MySQL优化器会优先使用普通索引树来遍历，但是如果有where语句，且where中的条件不在普通索引中，没法利用索引下推，还是他要回表，这种情况下innodb还是会走全表扫描（项目中count全表时带了delstate=“01”导致走全表扫描）
---15--------------------------------------------
正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的呢？
redo log更新缓存页，缓存页变脏页，脏页最终写入磁盘
---16--------------------------------------------
select city,name,age from t where city='杭州' order by name limit 1000;
orderby排序原理：
order_buffer 数据库分配的用于排序的内存空间
1.如果索引是联合索引(city,name)，那么order by name可以去掉，因为name本身就是有序存储
2.如果索引是(city)，有两种排序方式：
max_length_for_sort_data 参数：控制使用哪种排序方式，比如city、name、age 这三个字段的定义总长度是36，此参数>=36使用2.1，此参数<36使用2.2
2.1 全字段排序：通过city索引拿到主键后回表，取需返回的三个字段的值放到order_buffer中，
如果order_buffer内存够用，直接在内存中进行快排后返回。
如果order_buffer内存不够，需使用若干个临时文件一起排序，各自有序后通过归并算法排出最终结果后返回。
如果内存大，优先选此方式，效率高。
2.2 rowid排序：通过city索引拿到主键后回表，只取id和name放到order_buffer中，
如果order_buffer内存够用，直接在内存中进行快排后需要再通过id回表查city和age的值。
如果order_buffer内存不够，需使用若干个临时文件一起排序，各自有序后通过归并算法排出最终结果后，需要再通过id回表查city和age的值。
此方式节约内存，但是增加了一次回表查询，效率不如2.1。
--18 为什么sql语句逻辑相同，性能差异巨大-------------------
1. 若对索引字段做函数操作，此索引的快速搜索功能会失效，无法通过索引快速定位叶节点的记录，会触发全索引扫描【也可能不会选择此索引，会对比各索引树的大小，选择小的那个做全索引扫描，比如使用主键索引】
eg：统计2016-2018年的7月份的所有记录数，select count(*) from trade where month(trans_time)=7; trans_time字段上有索引，搜索功能会失效，会进行全索引扫描
2. 若索引字段触发隐式转换，比如从字符串转成数字，此索引的快速搜索功能会失效，无法通过索引快速定位叶节点的记录，会触发全索引扫描
eg：select * from trade where trade_id=111; trade_id是varchar类型，当与int比较时会使用一个CAST转换函数在trade_id上
---33讲我查这么多数据，会不会把数据库内存打爆--------------------------------------------
今天，我用“大查询会不会把内存用光”这个问题，和你介绍了MySQL的查询结果，发送给客户端的过程。
由于MySQL采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集。所以，如果客户端读结果不及时，会堵住MySQL的查询过程，但是不会把内存打爆。
而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于InnoDB对LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。
在InnoDB实现上，按照5:3的比例把整个LRU链表分成了young区域和old区域。
当然，我们前面文章有说过，全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。
---34--------------------------------------------
join的两种算法：
Index Nested-Loop Join：join字段是“被驱动表”的索引，驱动表全表扫描，每次拿一条数据-》取出join字段的值-》在被驱动表的索引中找-》回被驱动表取数据-》合并数据
Block Nested-Loop Join：join字段不是“被驱动表”的索引，驱动表将整表的数据读入join_buffer，如果join_buffer_size不足容纳整个表，就分多次读入，每次读入一批后-》对被驱动表全表扫描对比join_buffer中匹配-》每次取被驱动表的一条数据，拿到这条数据中join字段的值-》去join_buffer中匹配-》直到被驱动表全部匹配完，将join_buffer清空-》将驱动表剩余数据读入join_buffer-》再进行一遍被驱动表全表匹配
join优化点：
使用小表做驱动表
被驱动表的join字段要有索引
被驱动表的join字段没有索引只能使用Block Nested-Loop Join算法，很危险，如果硬要使用，调大join_buffer_size
explain中extra字段中若有 using join buffer(Block Nested Loop)，这种join语句会占用大量内存，而且被驱动表可能会进行多次全表扫描，尽量不要用
