MySQL-原理

1、客户端
2、连接器：管理连接，权限验证
3、(查询缓存)：命中则直接返回，注意：自8.0版本开始 查询缓存功能被移除
4、分析器：词法分析，语法分析
5、优化器：生成执行计划，选择索引
6、执行器：操作引擎，返回结果
7、存储引擎：存储数据，提供读写接口

show processlist	//查询客户端连接状态
select * from information_schema.innodb_trx;	//查询长事务

参数相关

show variables like '%%';

wait_timeout：客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时
transaction_isolation：查看当前事务隔离级别
innodb_lock_wait_timeout：锁等待的超时时间，单位：秒
innodb_rollback_on_timeout：锁超时之后是否回滚事务，值：ON/OFF
sort_buffer_size：排序所需的内存容量，如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序
max_length_for_sort_data：控制用于排序的行数据的长度，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法：rowId排序（第一次按照条件要求取主键索引字段和排序字段，排好序后再根据主键索引字段回表取其他字段，返回客户端）

笔记

1、长连接

长链接占用资源只有等断开连接才会释放资源，随着时间推移会出现OOM，现象就是服务重启了
解决方法：

• 1、定期断开链接；
• 2、mysql5.7以后可以通过mysql_reset_connection来初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

2、两阶段提交

• 1、引擎将新数据写入内存，记录redo log，处于prepare状态
• 2、执行器生成这个操作的binlog，并写入磁盘
• 3、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成commit状态

3、事务隔离级别

• 读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到
• 读提交：一个事务提交之后，它做的变更才会被其他事务看到
• 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

4、多版本并发控制

每条记录在更新的时候都会同时记录一条回滚操作（回滚日志）。记录上的最新值，通过回滚操作，都可以得到前一个状态的值，但是在查询这条记录的时候，不同时刻启动的事务会有不同的视图，同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。
当系统中没有比回滚日志的版本更早的事务存在时，回滚日志会被删除。

5、页分裂

原本一个满页，如果要从中插入一条记录，则存储引擎会新建一个页，满页中一半的记录移动到新页上。 这两个页存储都只利用了约50%，并且可能造成页的错位（dislocation，落入不同的区）

6、页合并

当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

7、change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
只有普通索引会用到change buffer：唯一索引在更新/插入数据的时候必须检查唯一性，所以必须将数据页读入内存（buffer pool），此时直接更新内存就可以。使用普通索引做插入/更新操作时不需要检查索引的一致性，将操作先缓存到change buffer从而避免取磁盘页数据造成随机IO（随机IO是数据库里成本最高的操作之一），有利于提高性能。