show global variables;
set global connect_timeout = 10 ;
show processlist;
SHOW VARIABLES LIKE 'slow_query%'
SHOW VARIABLES LIKE 'long_query_time' ;
SET innodb_flush_log_at_trx_commit = 1
SET sync_binlog = 1 ;
show variables like 'transaction_isolation' ;
SET transaction - isolation = READ - COMMITTED ;
show variables like '%commit%' ;
SET autocommit= 1
select * from information_schema. innodb_trx where TIME_TO_SEC( timediff( now ( ) , trx_started) ) > 60 ;
alter table T drop index k;
alter table T add index ( k) ;
alter table T drop primary key ;
alter table T add primary key ( id) ;
show global variables like '%general%' ;
set global general_log = 1 ;
show variables like '%max_statement_time%' ;
set global max_statement_time = 10 ;
show global variables like '%innodb_undo_tablespaces%' ;
set global innodb_undo_tablespaces = 2
explain 语句
Flush tables with read lock ( FTWRL)
lock tables … read / write
lock tables t1 read , t2 write ;
unlock tables
set global innodb_lock_wait_timeout = 10
set global innodb_deadlock_detect = ON
ALTER TABLE tbl_name NOWAIT add column . . .
ALTER TABLE tbl_name WAIT N add column . . .
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
START TRANSACTION WITH CONSISTENT SNAPSHOT
SAVEPOINT sp;
ROLLBACK TO SAVEPOINT sp;
reset_connection
select @@autocommit ;
select k from t where id= 1 lock in share mode ;
select k from t where id= 1 for update ;
set global innodb_change_buffer_max_size = 50
1.连接器
你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
2.查询缓存
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。
key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
3.分析器
查询缓存失败 直接进入分析器
开始真正执行语句
A.分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。看看你要干啥
1.将SELECT等关键字找出来
2.找表名
3.找列名
B.做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法
找到不符合语法的地方然后报错
4.优化器。
在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
执行方法的逻辑结果是一样的，但是执行的效率会有不同。
优化器的作用就是决定选择使用哪一个方案。
优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段
5.执行器
先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)，全部都依赖在连接器中查询到的权限。
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
1.通过引擎接口取这个表第一行，不符合就跳过，符合就存在结果集当中
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
问题
MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了
解决方案
1.定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2.如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
一条Update语句出来，清空了查询缓存中所有和Update表相关的元素
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。
对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。
而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：
select SQL_CACHE * from T where ID=10；
在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。
更新流程还涉及两个重要的日志模块
redo log（重做日志）InnoDB 引擎特有的日志
Write-Ahead Logging，MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。
同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
binlog（归档日志）Server 层自己的日志
最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。
而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。
两种日志的区别
redo log 是 InnoDB 引擎特有的；
binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；
binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；
binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。
1.执行器先找引擎取 ID 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4.执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5.执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
为了让两份日志之间的逻辑一致
让数据库恢复到半个月内任意一秒的状态
binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式
备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。
如何找回数据
1.确定好时间点
2.找到最近的一次全量备份，可能就是昨天晚上的一个备份，从这个备份恢复到临时库
3.从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻
你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去
先redo log 后写 binlog
写完redo log 掉电 binlog 没写完就被crash了 binlog里面没有记录这个语句，备份日志的时候，存起来的binlog语句缺失。恢复临时库，binlog丢失，临时库少一次更新，恢复出来的数值是0，和原库的值不同。
先写binlog再写redo log
写完binlog之后crash 由于redo还没写 崩溃之后这个事务无效。之后用binlog恢复的时候就多了一个事务，和原库的值不同。
不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案，即使你不做数据库内核开发，日常开发中也有可能会用到
Redo log不是记录数据页“更新之后的状态”，而是记录这个页 “做了什么改动”
Row row格式会记录行的内容，记两条，更新前和更新后都有
Statement 记录SQL语句 但是恢复的时候 会出现问题
Mixed 自动处理
redolog是循环写的，不持久保存，binlog的“归档”这个功能，redolog是不具备
其实恢复数据只能恢复到误删之前到一刻，
误删之后的，不能只靠binlog来做，因为业务逻辑可能因为误删操作的行为，插入了逻辑错误的语句，
所以之后的，跟业务一起，从业务快速补数据的。只靠binlog补出来的往往不完整
447
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
