丁奇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事务隔离性----知识点较多----------------------------------------

脏读：指的是一个事务读取了另一个事务尚未提交的数据

幻读：是指当两次相同的查询在事务期间执行时，第二次查询返回了一些新插入的行（其他事务提交的）；

　　   在可重复读隔离级别下不允许出现，如果是快照读，通过mvcc就能解决幻读，如果是当前读，通过引入 间隙锁 解决幻读；在都提交隔离级别下，允许幻读，没有间隙锁

　　　详见：《既然mvcc能解决可重复读隔离级别下的幻读，为啥还需要间隙锁》

　　　　

 

不可重复读：指的是一个事务在相同的查询中多次读取同一行数据时，得到了不同的结果

1. 读未提交（Read Uncommitted）：最低级别的隔离，允许脏读、幻读和不可重复读。事务可以读取其他事务未提交的数据，会导致数据的不一致性。
2. 读已提交（Read Committed）：事务只能读取已经提交的数据，解决了脏读问题。但仍可能出现幻读和不可重复读问题。
3. 可重复读（Repeatable Read）：事务能够多次读取同一数据，并保证在同一事务期间读取到的数据一致。这解决了幻读和不可重复读的问题。数据库使用锁机制（两阶段锁）来阻止其他事务对数据进行修改。
4. 可串行化（Serializable）：最高级别的隔离，确保事务串行执行，完全消除了脏读、幻读和不可重复读的问题。但并发性能可能会受到一定的影响。

多版本并发控制（MVCC）：为每个事务维护多个版本的数据，在事务执行期间，每个事务读取的是特定版本的数据，从而避免脏读、幻读和不可重复读的问题。MVCC能够提供较好的并发性能，但会增加存储空间的消耗。

undolog回滚日志：当需要向前找某行数据的历史版本时，通过 当前版本+undolog 计算出来，如果事务太长，会导致undolog占用空间过大

可重复读隔离级别下：

事务数组、低水位高水位、行锁

事务数组：Innodb为每个事务构造了一个数组，用来记录事务启动瞬间所有正在活跃的事务id，活跃即事务已开启未提交

一致性读、当前读、两阶段锁（行锁）

可重复读的核心是一致性读：一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

　　版本未提交，不可见；

　　版本已提交，但是是在视图创建后提交的，不可见；

　　版本已提交，而且是在视图创建前提交的，可见。

当事务更新数据的时候只能用当前读（读当前其他事务已提交的最新版本），如果当前数据的行锁被其他事务占用（其他事务中也在update同一行，在update语句执行时才加锁，在事务提交后才释放，即两阶段锁），就阻塞等待其他事务提交释放锁

 

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；

对于读提交，查询只承认在语句启动前就已经提交完成的数据；

---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中更新操作更新缓存页，返回正确结果

 

***查询全流程

连接器-分析器-优化器-执行器-》引擎接口-》

如果查询使用到二级索引

　　二级索引数据页在内存-》直接拿到索引中的主键

　　二级索引页不在内存=》将索引页读入内存-》拿到索引中的主键

　　然后看主键索引是否在内存中

　　　　主键索引页在内存-》获取行数据返回

　　　　主键索引页不在内存=》将索引页读入内存-》获取行数据返回

 

***更新全流程

连接器-分析器-优化器-执行器-》引擎接口-》

如果更新使用到二级索引

　　二级索引数据页在内存-》直接更新内存中的二级索引页-》写redolog

　　二级索引数据页不在内存-》

　　　　索引是普通索引-》将更新写入changebuffer》写redolog

　　　　索引是唯一索引-》将索引数据页读入内存-》修改内存中的数据页-》写redolog

　　拿到主键-》

　　　　主键索引页在内存-》修改内存中的数据页-》写redolog

　　　　主键索引页不在内存-》不是普通索引用不了changebuffer，必须将数据页读入内存-》修改内存中的数据页-》写redolog

　　--

　　上面写redolog的步骤，可能是二级索引和主键索引各自单独写，也可能是最后一起写，不确定，不重要

两阶段提交redolog prepare->binlog->redolog commit

 

---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语句响应时间。

---13--------------------------------------------

为什么表数据删了一半，表文件大小没变

B+树，按页存储，大量增删改会造成数据空洞，位置/页可复用，但还是占空间的

怎么解决：

1.重建表alter table A engine=InnoDB，期间不支持表更新

2.Online DDL，期间支持表更新，并记录期间的更新日志，结束后再次讲这些记录生效。alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了，锁降级是为了实现 Online，MDL 读锁不会阻塞增删改操作

问题：

想要收缩表空间，执行alter table t engine=InnoDB；后表空间反而增加了，原因有可能是：

1. 刚做过一次重建表操作，本身就已经没有空洞的了，在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞

2. 在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。

---14--------------------------------------------

由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的

Innodb中的count(*)需要全表扫描：

　　这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。

　　需要判断每行是不是对当前事务可见，以保证可重复读。

　　（比如启动事务A执行count(*)，执行完之前事务B插入了一条记录，那为了保证可重复读，这条记录对事务A是不可见的）

Innodb中对count(*)的优化：

　　在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一

　　对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。因此，MySQL优化器会优先使用普通索引树来遍历，但是如果有where语句，且where中的条件不在普通索引中，没法利用索引下推，还是他要回表，这种情况下innodb还是会走全表扫描（项目中count全表时带了delstate=“01”导致走全表扫描）

按照效率排序的话，count(字段)<count(主键id)<count(1) 约等于 count(*)。因为mysql对count(*)有优化，不取值取行数，不需要把字段取出来给server层判空

---15--------------------------------------------

正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的呢？

　　redo log更新缓存页，缓存页变脏页，脏页最终写入磁盘

redo log buffer 就是一块内存，在一个事物中的更新语句在提交事务前，会更新对应的数据页，也会更新redo log buffer。等到commit事务时才会将redo log buffer中的数据写到redo log文件中，这么做是为了避免在事务中进行磁盘IO操作

遗留问题：如果内存中的redolog buffer没有写入磁盘，崩溃了，那恢复的时候redolog里就没有prepare状态的记录，就不会去binlog里进行判断，怎么办？

崩溃恢复流程：

　　redo log/binlog有一个共同的数据字段 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，如果binlog存在并完整-》提交事务，否则回滚事务

---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. 若对索引字段做函数操作，此索引的快速搜索功能会失效，无法通过索引快速定位叶节点的记录，会触发全索引扫描【也可能不会选择此索引，会对比各索引树的大小，选择小的那个做全索引扫描，比如使用主键索引】

　　　　B+ 树提供的快速定位能力，来源于同一层兄弟节点的有序性，函数操作会利用不上这种特性快速确认路径

　　　　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上

--19 为什么仅查询一行语句执行慢-------------------

　　select * from t where id=1；

　　排查步骤：

　　1. 考虑被锁住：

　　　　等MDL元数据锁：show processlist 命令查看是否有 Waiting for table metadata lock，有表明有线程在对表进行DDL占据写锁：查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可

　　　　等flush：flush表的操作需要先关闭表，必须等所有事务完成所有读写表操作释放锁，如果在所有事务还没关闭时，进行查询，就会显示被flush操作阻塞，其实底层是flush操作被其他事务阻塞：show processlist 命令查看是否有 Waiting for table flush

　　　　等行锁：其他事务占用写锁，此时查询如果显式指定需要获取读锁，则需要等写锁释放才行

　　　　　　　　mysql> select k from t where id=1 lock in share mode; 加了读锁（S 锁，共享锁）

　　　　　　　　mysql> select k from t where id=1 for update; 加了写锁（X 锁，排他锁）

　　　　　　　　select 语句如果加锁，是当前读，需要获取最新的数据版本，需要阻塞等待写语句提交后释放写锁；

　　2. 可重复读，mvcc导致历史版本过多

　　　　在事务开启后，查询语句之前，其他事务对行进行大量更新操作，为了支持一致性读，在查询时需要从当前版本经过undolog一路回退到可读版本　　

             

　　

--20幻读---------------------------

幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的“新插入的行”。幻读仅专指“新插入的行”

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现：select...for update。

即使把所有的记录都加上锁，阻止读写，还是阻止不了新插入的记录，因为在上锁的时候还没有这些新的记录，也就锁不住，这也是为什么“幻读”会被单独拿出来解决的原因。

如何解决：

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”，引入间隙锁 (Gap Lock)。

间隙锁和行锁合称 next-key lock，前开后闭区间，不仅锁住行，也锁住间隙，禁止插入

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row

详见：《既然mvcc能解决可重复读隔离级别下的幻读，为啥还需要间隙锁》

　　　　

间隙锁缺点：

可能会导致同样的语句锁住更大的范围，影响并发度

 

---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 buffer，如果太大还要分批，分的批次越多，被驱动表需要走全表扫描的次数越多，不划算

　　被驱动表的join字段要有索引

　　被驱动表的join字段没有索引只能使用Block Nested-Loop Join算法，很危险，如果硬要使用，调大join_buffer_size

　　explain中extra字段中若有 using join buffer(Block Nested Loop)，这种join语句会占用大量内存，而且被驱动表可能会进行多次全表扫描，尽量不要用