关于MySQL的一些零散的知识

索引下推:如果查询条件中有几个字段都命中了联合索引，根据联合索引里面的值提前进行判断条件，可以减少回表的次数。

mysql的内存是基于页的缓存，每一个页是16kb.更新会涉及到change buffer，对于普通索引，如果要操作的页不在内存中，需要在change buffer中纪录当前的操作，并记录在WAL里。后续通过merge操作将数据同步到磁盘。后续读数据时，内存中有数据，直接从内存中返回，如果内存中不存在的话，将磁盘中的数据读出来，再配合change buffer的变动去得到最新的数据。change buffer的优点时，将多次写磁盘IO操作减少，增加写数据的吞吐量。

 

-- 查询当前数据库实例中连接的用户
show processlist;

-- 查询执行时间超过60s的事务。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started)) > 60;

-- 重建表 (recreate)
alter table T engine=InnoDB;

analyze table t; -- 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；

optimize table t; -- recreate+analyze

-- 对整个数据库实例加锁，只能读不能写
Flush tables with read lock;

-- 整个数据库实例只能读
set global readonly=true;

-- 做表进行DDL,DML时，可以设置超时时间，避免将表长时间锁住
ALTER TABLE tbl_name NOWAIT add column ...   (没有获取到锁直接抛出异常，不等待)
ALTER TABLE tbl_name WAIT N add column ...  （没有获取到锁等待N秒，N秒内没有获取到锁，抛出异常）

-- 开始一个事务

start transaction; (需要到第一个执行sql时才会开启一个事务)
start transaction with consistent snapshot;（直接开启一个事务）

-- 数据更新时，读取数据为当前读，读取的是最新版本的那一条数据。
-- 如果select语句加上锁的话，也是当前读

-- mysql读取数据是基于数据页为单位的，一个数据页16kb，查询时会以数据页的形式返回。

redolog和binlog存在关联关系,通过XID来实现的，两个日志都保存了一个XID。

sort by 排序分为两种.如果要查询的字段的所有长度小于max_length_for_sort_data的话，就是用全字段排序，否则使用排序字段排序
排序时如果内存不够的话，需要开辟多个tmp_sort_file来进行排序，sort_buffer_size越小，sort_file越多。后续通过归并算法来将结果合并。
1.全字段排序: 将查询的接口都丢到排序内存或者排序文件中。后续基于要排序的字段排完后直接返给server层
2.rowid排序: 将需要排序的字段和主键id丢到内存或者排序文件中。

show index from t; 显示表的索引信息
analyze table t;  重新分析表的索引信息

对于普通索引的更新操作，当要更新的数据行在内存中存在时，就直接进行更新。如果不在内存中的话，将更新操作缓存在change buffer中，change buffer也会进行持久化操作。
在下次查询数据页的时候，将change buffer中作用于该数据页中的操作，这个操作叫做merge。除了访问数据会进行merge以外，系统后台线程会定期merge，在数据库正常shutdown时，
也会进行merge操作。
而唯一索引需要判断对应的数据是否要进行更新，必须要将数据行所在的数据页读到内存中，如果操作的数据不在内存中的话，效率比普通索引的更新操作要慢一点。
change buffer场景用在写多读少的场景比较好，不然更新过后就读，会导致频繁的merge操作。
我们在选择索引时，在业务允许的情况下。能用普通索引就用普通索引，尽量不用唯一索引

脏页进行刷盘的4个时机:
1.redolog满了，需要清除一些redolog，这时要把redolog相关的脏页页进行flush刷盘操作
2.内存不够了，需要淘汰掉一些数据页，如果被淘汰的数据页是脏页，这个时候需要进行刷盘
3.mysql在空闲的时候，也会进行脏页的刷盘
4.在数据库正常关闭的情况下，也需要进行脏页的刷盘

在需要随机显示数据库的几条数据时，不使用order by rand();通过计算所有数据的总条数来进行limit更好，表的扫描行数更低。
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
1.对字段进行了函数操作
2.隐式类型转换
3.隐式字符编码转换
上面三个本质都是对where a = 3中的a字段进行了处理，优化器就不会走a索引了。

查询缓慢的原因：
1.等MDL锁，可以通过show processlist来查看语句的执行状态
2.等待flush，flush锁住
3.等行锁
4.查询没有走到索引
5.低版本事务查询数据时，因为当前数据的事务版本过高，要执行更多个undolog进行数据还原

避免幻读可以将事务隔离级别设置为可重复读，通过引入间隙锁来锁住还没有插入数据的间隙，避免其他事务插入数据的问题。
可重复读用的是next-key lock,为前开后闭区间。使用可重复读容易导致死锁，一般使用的是读提交,并且将binlog格式设置为raw。

可重复读隔离级别下的加锁原则。
1.加锁的基本单位是next-key lock （左开右闭）
2.查找过程中访问到的对象（对象指索引，加锁时对索引加锁）才加锁 （索引覆盖的场景，非聚集索引锁住了不会影响到聚集索引）
3.索引上的等值查询，给唯一索引加锁时，next-key lock退化为行锁，因为不可能存在两个值同样的唯一索引，对这个索引值进行操作的话不会导致幻读
4.索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock退化为间隙锁（左开右开）
5.唯一索引上的范围查询会访问到不满足条件的第一个值为止
tips:lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

读提交隔离级别的一个优化，对应的行锁在语句执行完之后就会释放，不会等到事务提交之后再释放

binlog的写入机制：
在一个事务执行时，将本次事务中对数据的操作先写到binlog cache中，（binlog cache在每个线程中都有，如果binlog大小超过binlog cache，会写到磁盘上）
是事务提交时，将binlog write到磁盘的page cache中，然后再将page cache中的binlog fsync到磁盘中。
fsync的时机由参数sync_binlog控制。
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

redolog的写入机制：
事务在执行过程中，会先将redo log写在redo log buffer(redo log buffer是整个mysql共享的)中，后续再将buffer中的redo log write到文件系统缓存，最后再fsync到磁盘上。
可以通过innodb_flush_log_at_trx_commit参数来进行控制.
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中.
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘.
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache.

mysql中有个后台线程，每秒钟都会检测redo log buffer中的redo log，write到文件系统缓存，再fsync到磁盘中
如果redo log buffer中的redo log大小超过了innodb_log_buffer_size的一半，后台线程会主动写盘，将redo log buffer中的log write到文件系统缓存，最后再fsync到磁盘上。
如果innodb_flush_log_at_trx_commit配置的是1，另一个事务提交时，会把其他还未提交的事务的redo log也一并写到磁盘。
redo log的prepare文件会fsync到磁盘上，而commit文件只需要write到文件系统缓存，因为在进行crash recovery时，通过binlog和redo log的prepare文件就行了。

组提交机制:redolog和binlog在fync刷盘时，可能会将多个日志一起进行提交，这样可以大幅度降低磁盘的 IOPS 消耗
有两个fsync刷盘的参数策略:
binlog_group_commit_sync_delay:表示延迟多少微秒后才调用 fsync.
binlog_group_commit_sync_no_delay_count:表示累积多少次以后才调用 fsync.
这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync.

-- 相关参数
innodb_lock_wait_timeout (等待锁的超时时间)
innodb_deadlock_detect （死锁自动取消某一个死锁获得的锁资源）
innodb_change_buffer_max_size （change buffer占buffer pool的比例大小）
long_query_time 慢sql判断阈值
transaction_isolation  事务的隔离级别
innodb_io_capacity 默认200 磁盘io的能力
innodb_max_dirty_pages_pct  默认75% 脏页比例上限
innodb_flush_neighbors  刷脏页时是否一同刷掉旁边的脏页，数据页是用前后指针进行关联的，所有有前后两个脏页需要检测，并且是递归的。
innodb_file_per_table 表数据的存放位置，OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起。
ON表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。推荐设置成ON
sort_buffer_size 排序内存buffer的大小
max_length_for_sort_data 需要查询的字段的长度总和大小。 判断在sort_buffer中排序的字段是否是全字段
tmp_table_size 内存临时表的大小，超过这个大小的就要使用磁盘临时表
internal_tmp_disk_storage_engine 磁盘临时表的默认引擎
binlog_cache_size 单个线程的binlog缓存大小
sync_binlog  binlog的刷盘策略
innodb_log_buffer_size redo log的缓存大小
innodb_flush_log_at_trx_commit  redolog的刷盘策略

optimizer_trace: 查看整个执行计划以及对于多种索引方案是如何选择的。
相关参数参数:number_of_tmp_files 用于排序的临时文件的个数 examined_rows:满足条件的数据行数