mysql

索引下推

• 在没有索引下推的情况下，MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器，服务器再进行WHERE条件的判断，确认是否将数据行加入结果集。
• 开启索引下推，且WHERE条件部分可以仅使用索引中的列来评估，这时MySQL服务器会将这部分WHERE条件下推到存储引擎，接着存储引擎使用索引条目评估推送的索引条件，仅当满足该条件时才从表中进行读取

MyISAM ， InnoDB 区别
innodb索引和数据存储在一起，myisam没有
表级锁，行级锁
事务的支持，外键的支持
mvcc的支持，崩溃后的安全恢复

索引的建立意见
频繁修改的字段不作为索引（维护成本大）
频繁查询，条件查询（where）和频繁排序的，频繁用于连接的字段（外键列）作为索引
避免索引冗余，（联合索引）
字符串类型的字段上使用前缀索引代替普通索引
避免在索引列上使用内置函数

使用意见
特大型表的话维护开销会很大，不适合建索引
避免 where 子句中对字段施加函数，这会造成无法命中索引
逻辑自增主键，不用业务主键
删除长期未使用的索引

redolog 
用于记录对数据页的物理修改，主要用来恢复提交后的物理数据页
在数据修改的时候，提交的时候，修改的时候，发生事物的时候，都会进行提交
redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)（循环写）
mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file
三种将 redo log buffer 写入 redo log file 的时机
0：延迟写，丢失1秒数据
1： 实时写实时刷新，io性能差
2： 实时写延迟刷新，每次提交写入buffer，每1秒刷新一次。

undolog 
把所有没有commit的事物回滚到事物开始前的状态，当系统崩溃的时候，可以借助undo log 进行回滚
原子性 底层就是通过 undo log 实现的

binlog
记录mysql内部增删等对mysql数据库有更新的内容的记录（追加写）
主从复制：再Master端开启Binlog然后将Binlog发送到各个Slave端，Slave端重放binlog达到主从数据的一致性。
数据恢复：通过使用mysqlbinlog工具达到数据的恢复。
只有在事物提交的时候才会记录biglog此时记录还在内存中
mysql的sync_binlog参数
0：不去强制要求，由系统自行判断何时写入磁盘；
1：每次commit的时候都要将binlog写入磁盘；
N：每N个事务，才会将binlog写入磁盘

一条sql的执行过程
连接器， 查询缓存(MySQL 8.0 版本后移除)，分析器，优化器，执行器
连接，身份认证，查询缓存，词法分析 语法分析，是否符合 MySQL 的语法。优化器的作用就是它认为的最优的执行方案去执行
调用引擎的接口，返回接口执行的结果

explain

type 连接使用了何种类型

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

key  实际使用的索引

rows MYSQL认为必须检查的用来返回请求数据的行数

    未提交读   
无锁

    已提交读    
数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的
如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤
在MySQL Server过滤条件，发现不满足后，会把不满足条件的记录释放锁 
这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的

    可重复读   
乐观锁,mvcc,

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
INSERT时，保存当前事务版本号为行的创建版本号
DELETE时，保存当前事务版本号为行的删除版本号
UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行
(快照读)
快照读：就是select,不用加锁
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁
(通过mvcc添加乐观锁，其实并没有加锁，只是通过版本号的方式，完成了加锁的功能)
为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。

Gap锁通过B+树锁定一个区间
如果使用的是没有索引的字段,那么会给全表加入gap锁
行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题(innodb解决了RR级别的幻读问题)

    可串行化    
读加共享锁，写加排他锁，读写互斥，使用的悲观锁的理论

Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点
B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。
而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
同样的大小的磁盘页可以容纳更多节点元素，在相同的数据量下，Ｂ＋树更加“矮胖”，ＩＯ操作更少
b树性能很不稳定，而Ｂ＋树每次必须查找到叶子结点，性能稳定
B树的范围查找需要不断依赖中序遍历
通过二分查找，找到范围下限，然后同过叶子结点的链表顺序遍历，直至找到上限即可