MySQL总结一

一
MySQL：
总体架构：

server层：连接器、解析器、优化器、执行器
存储引擎层：提供数据的IO接口
连接器：
MySQL一个TCP连接长时间不用会断开，由wait_timeout参数控制。
解析器：
语法解析，对语句分析出具体你要干什么
优化器：
直到具体语义选择合适的索引、建立执行计划
执行器：
根据生生的执行计划调用存储引擎进行操作

二

MySQL针对更新操作会进行WAL（Write-Ahead Logging），避免频繁磁盘IO。

redolog ： 循环写、它是innodb特有的日志、二进制级别

binlog：追加写，它是Server层日志，所有引擎都拥有该日志，语句级别

更新过程：

update set a=a+1 where id = 1

1.通过存储引擎获取id为1的这行记录，如果该页在内存中直接返回，否则通过搜索树返回该页
2.执行器计算a+1，写入新行，再修改内存页，再写入redo日志（prepare阶段）
3.执行器写入binlog
4.通过存储引擎提交事务，redo日志修改为（commit）

最后3步属于2PC，类似于分布式事务，目的确保数据的一致性

对于写入redo、写入binglog也有写入内存的操作，可以通过innodb_flush_log_at_trx_commit、sync_binlog来分别修改落盘的时机

三
数据库的隔离性与隔离级别：
事务的特性：ACID
隔离性：ACID中的I，当多个事务操作时彼此独立，互不影响。
隔离性的级别：读未提交，读已提交，可重复读，串行。
隔离性通过MVCC来实现，MVCC指的是多版本并发控制（对于一行数据存在多个版本），对于更新来说，每个事务会有一个ReadView，对于当前行依次执行所有的回滚段得到的就是当前ReadView的值，

所以回滚日志会存储当前行的所有ReadView，如果当前系统中（当前所有事务）没有比会滚日志更早的ReadView，该会滚段会被清除，所以要避免长事务以免浪费空间。

四
常见索引数据结构：
哈希表：就是通过key使用hash函数映射到数组的指定位置，将值放入该位置，如果映射重叠，使用拉出一个链表。适合等值，由于键映射后的位置无序不适合范围查找（需全表查）。

有序数组：适合范围查找，通过二分找到起始位置一直遍历到指定条件结束。查找指定数据也可以通过二分复杂度(0(LogN))。但是添加的时候需要移动位置。

二叉树：添加删除都是o(logN)，很均衡。由二叉树可以引出多叉树避免二叉树的树高问题导致的IO次数的增加。

innodb的索引的数据结构是B+树。
主键索引（聚簇索引）：叶子节点存放具体数据
非主键索引（二级索引）：叶子节点存放主键索引的键，查询的时候需要回表。
在插入的数据的时候，如果不是递增的主键会导致页分裂，或者页内位置调整。
页分裂需要申请新的数据页，且会导致空间利用率下降，所以最好使用自增主键，避免也分裂和页内移动。
当然主键越小，非主键索引的叶子节点也越小，选择索引的时候可以考虑这点。

树的子节点（分叉数量）由索引字段的大小决定，MySQL数据页的大小为16K，数据页的地址为6byte，主键所以一般为bigint，也就是8byte，子节点的数量计算：16*1024/6+8 ≈1170个。

五
索引覆盖：
非主键索引如果不存在需要查询出的列，需要进行回表查询，此时建立联合索引，可直接返回
索引下推：
在查询非主键索引树时，5.6版本前不会对条件进行判断，直接回表查询条件是否满足，而后续版本则会在非主键索引查出数据后，先对条件进行判断，如果满足要求再进行回表。
例如:

 select * from tuser where name like '张%' and age=10 and ismale=1;

上图没有对age进行判断直接，找主键索引了

先找非主键索引的时候找到数据后先判断条件是否成立再找主键索，减少回表次数。

六

全局锁：Flush tables with read lock ，除了读查询，其他DML、DDL全部堵塞
该锁主要备份使用，但是会导致主从时，主从不同步
所以不常用，可以将隔离级别调整为可重复读，这样在当前视图下看到的都是不变的，可以备份数据。
但是对于不支持事务的引擎来说只能使用全局锁了。
readonly也可以将库变为只读库，但是不建议采用，因该字段用作主从标识，且当客户端设置该值后如果未进行复原会一直处于只读状态，但是对于Flush tables with read lock会自动释放锁。

表级锁：
表锁：通过 lock tables … read/write加锁，通过lock tables解锁。客户端异常自动释放锁。该语句对当前会话也生效。
MDL（metadata lock)：元数据锁，当一个会话修改表结构会加写锁，当一个会话CRUD时会加读锁，读读不互斥，读写互斥，写写也互斥，所以当一个会话遍历的时候无法进行DML，同理2个会话同时DML时会串行执行。该锁等待事务结束时才会释放。

如上图，A会一直持有读锁，B目前可以进行读操作，C因读写互斥会堵塞，D因为C的堵塞而堵塞所以没办法进行读写了。

七
行锁：MySQL的行锁是交给引擎层的，Innodb是支持行锁的。
行锁的释放条件和MDL锁一样都是在事务结束后释放，故将经常更新的行尽量放到事务的最后，减少独占时间，提升并发量。
死锁：2个事务都需要彼此的资源才能继续操作，但是资源都因为彼此被占用无法释放形成了循环依赖。
如：

事务A占据id为1的行锁，事务B占据id为2的行锁，接下来事务A需要id为2的行锁但是事务B并没有释放而是需要事务Aid为1的行锁，但是2个事务都死锁了。
解决方案：
1.innodb_lock_wait_timeout设置等待锁等待时间，超时默认回滚可以通过innodb_rollback_on_timeout设置
2.innodb_deadlock_detect死锁检测，当发现死锁自动会滚某一方。默认开启，每当一个事务进行加锁时会判断是否本身加入会导致死锁（依次检测所有当前行涉及的事务（O(N^2)））。当多个事务同时更新一条事务时会导致大量的判断。可以在5.7以后的版本通过改参数设置。
在实验的过程中，大量请求修改一行数据会导致锁等待，此时如果不做限流会导致，等待锁超时！

行锁如果where字段没有索引的话会导致锁全表

八
每行数据有多个版本，每个版本（trx_id）带有事务id，该id全局唯一。
U1,U2,U3就是undo log，而v1,v2…实际并不存在，在获取的时候需要根据u1,u2,u3来进行计算。
如果要获取v2的版本，需要经过u3，u2的计算。

在RR级别下每个事务开始前的read view，会产生一个数组，主要记录当前活跃的事务，也就是当前未提交事务的ID集合以及当前系统最大事务ID和自己的事务ID
当获取数据时，会判断当前行的版本链，如果当前行的版本号小于事务未提交的ID集合中最小值，那就是可见（绿色），如果大于当前事务的最大事务ID那就是不可见（红色），如果处于黄色区间，先判断是否在当前未提交的事务ID集合中，在的话说明不可见，因为未提交。不在话的说明，已提交，可见。

在MySQL进行更新时，会变成当前读，防止事务丢失（针对已提交），如果未提交的话，当前更新会堵塞，等待锁释放。

九
普通索引：查找到存在数据的数据页后，二分找到具体数据，继续向后查询直到遇到和条件不等才停止
唯一索引：查找到存在数据的数据页后，二分找到具体数据，就直接返回了。
对于2种索引的效率：速度相差不大，因为按页读取（对于biglong可以存放1170个），所以普通索引的那多一次判断在内存中执行。
change Buff：对于更新来说如果数据页在内存中，直接修改，如果不在内存的话，将操作缓存到changebuff中。等待下次读取该数据页时，将数据读入内存并执行changebuff中的操作。
change buff应用到数据页的操作称之merge，merge操作时机：在读取数据页时、关闭服务、定期merge。
优点：对于更新操作较少将数据页读入内存，避免了IO，节省了Buffer pool。
对于唯一索引，插入的时候必须判断是否唯一，这必须读入数据页，所以change buffer失效。
change buffer对于写多读少效率提升很大，但是对于读多写少也就是写入后立马需要读取的话，反而影响效率，因为每次都要进行merge还不如关闭change buffer。innodb_change_buffer_max_size可以查看change_buffer占据buffer pool的比例。

十
MySQL的优化器在选择索引的时候，汇综合考虑，它会优先考虑扫描较少的行数（cardinality），并且回表次数较少的。

MySQL在统计扫描行数的时候采取抽样，抽取索引的N个页数据提取不同的值，得到平均数，之后乘以页的数量这样会有误差。而且页的数量在不断变化，当时修改的数据行超过1/M重新统计。
innodb_stats_persistent该参数可以控制N，M。
analyze table t 修复扫描行数

总结
MySQL的底层基础，包括MySQL的架构，WAL（redolog,binlog），change buffer，MVCC原理（readview + undolog）主要应用快照读，各类锁（全局锁，表锁，行锁(仅innodb)）主要针对当前读，索引相关。