mysql实战笔记

查询缓存

mysql 5.7
除非都是静态表,配置表,读大于写的情况下才开启查询缓存,其他大多数情况下不建议使用查询缓存,可以通过配置my.ini

query_cache_type = 2;  // 0 OFF 永久关闭  1 ON 永久开启 select SQL_NO_CACHE  时关闭  2 select SQL_CACHE 时开启

mysql 8.0
查询缓存功能整块被移除,彻底没有这个功能了

redo log 重做日志

WAL write-ahead logging 先写日志,再写磁盘
当有一条记录需要更新的时候,innodb先把记录写到redo log,并更新内存,然后在适当的时候将操作记录更新到磁盘里面

image.png

write pos是当前记录的位置,checkpoint是当前要擦除的位置,如果write pos快追上checkpoint表示redolog日志区满了,得停下来把部分日志更新到磁盘,把checkpoint推进一下
有了redolog,innodb可以保证数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe

最佳实践
redo log 大小设置为 4 个文件、每个文件 1GB 。

binlog 二进制日志

mysql自带引擎myisam没有crash-safe的能力,binlog日志只能用于归档,后来innodb是另一个公司以插件形势引入mysql,使用redolog实现了crash-safe能力,新版本的mysql默认引擎也改成innodb

redolog binlog 不同

redolog是innodb引擎特有的,myisam引擎没有redolog,binlog是mysql server层实现的,所有引擎都可以使用
redolog是物理日志,记录在某个数据页上做了什么修改(运算后),binlog是逻辑日志,记录了语句的原始逻辑(运算前)
redolog是循环写,空间固定会用完,binlig是可以追加写入的,写道一定大小后会切换到下一个,并不会覆盖之前的日志

执行update语句内部流程

update T set c=c+1 where ID=2;

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

两阶段提交

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

innodb_flush_log_at_trx_commit  = 1; //建议设置成1,每次事务的redolog都直接持久化到磁盘
sync_binlog = 1; // 建议设置成1;保证每次事务都持久化到磁盘

事务隔离

事务是在引擎层实现的

隔离级别
读未提交 read uncommitted : 事务没提交时,变更能被其他事务看到
读提交 read committed : 事务提交之后,变更才会被其他事务看到
可重复读 repeatable read : 事务执行过程中看到的数据总是跟启动时一致
串行化 serializable : 写加写锁,读加读锁,必须等待前一个事务完成才能继续执行

长事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60 //查询超过60秒的长事务

通过设置 SET MAX_EXECUTION_TIME 命令控制每个语句执行的最长时间
监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
Percona 的 pt-kill 这个工具不错，推荐使用；
在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

索引

主键索引的叶子节点存的是整行数据,非主键索引叶子节点存的是主键的值
回表: 通过普通索引查询主键,再到主键索引树搜索数据
主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小,所以自增主键往往是更合理的选择

当只有一个索引的时候,且索引必须为唯一索引的时候,可以不使用自增主键索引,这是典型的KV场景

覆盖索引

普通索引的叶子节点已经覆盖了查询需求,不需要回表,我们称为覆盖索引,减少树的搜索次数,显著提升查询性能,是一个常用的性能优化手段

最左前缀原则

where name like '张%'  // 也能够用上索引

当已经有了(a,b)这个联合索引后,就不需要维护(a) 这个索引了
如果既有联合查询,又要基于(a)(b)各自的查询,也就是同时维护(a,b),(b),则a选择占用空间相对大的字段

全局锁

全局读锁: 适合Myisam这种没有隔离级别和事务的驱动做逻辑备份,上锁之后,数据增删改,建表,修改表结构,事务提交语句将被阻塞

FLUSH TABLES WITH READ LOCK
SET GLOBAL read_only = ON;  //不建议使用

mysqldump --single-transaction --skip-lock-tables //适用于innodb这种支持事务和MVCC的驱动,导出的备份保持一致性的同时避免锁表

表级锁

lock tables T read/write // 行锁出来之前都用这个命令处理并发,现在不常用了,毕竟锁住一个表的代价还是太大
unlock tables

MDL (metadata lock) : 元数据锁,当对一个表做增删改查操作的时候会自动加MDL读锁,当要对表做结构变更操作的时候加MDL写锁,不需要显式适用,
如果上了MDL读锁,给表加字段的命令要等到读锁释放才能执行
如果有两个线程要同时给表加字段,其中一个要等另一个执行完才执行
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。

如何安全地给小表加字段?
如果要做DDL变更,需要查看一下有没有长事务正在执行,要考虑先暂停 DDL，或者 kill 掉这个长事务。

以下命令可以指定修改DDL请求写锁等待时间,拿不到就先放弃

ALTER TABLE tbl_name WAIT N add column ...  //MariaDB 支持WAIT N , Mysql8不支持

mysql 还是要用到pt-online-schema-change 或 gh-ost 这样的工具来执行在线表结构更改，这些工具可以在后台执行表结构更改操作，而不会阻塞表的读写操作。

行锁

行锁实在引擎层实现的,mysiam不支持行锁,innodb支持

两阶段锁协议: 在innodb事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放,

一个事务里面,把最可能造成锁冲突的语句往后放

死锁

不同线程出现循环依赖,互相等待对方释放行锁称为死锁

解决死锁策略

# 不建议使用
innodb_lock_wait_timeout = 50s   //当死锁检测关闭时,设置等待锁超时时间,默认50秒,
# 建议使用,默认开启
innodb_deadlock_detect = on //发起死锁检测,主动回滚死锁链条中权重较小的事务,由该事务影响的行数决定

死锁检测每次被锁都要检查是不是死锁造成额外负担,时间负责度为O(n^2),假设有1000个并发线程同时更新一行,虽然最终检测没有死锁,但是非常消耗CPU资源,导致CPU利用率极高,只有在上述特殊的业务场景才需要关闭或者如果确定业务一定不会出现死锁,可以把死锁检测关掉

出现死锁了就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

事务ID

每行数据都有个trx_id 记录最新的事务ID
语句更新会生成undo log, 事务回滚根据事务ID和undo log计算出来

线程只认小于等于当前事务ID的数据

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

当前读一致性读

普通查询时一致性读,加锁查询是当前读
当前读:读取已经提交完成的最新版本。
select 语句如果加锁,也是当前读

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

change buffer

更新操作缓存在change buffer,可持久化,将change buffer的操作应用到源数据页的过程称为merge,后台线程每个几秒会定期merge,数据库shutdown的过程中也会执行merge操作
唯一索引不能使用change buffer
普通索引可以使用change buffer

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

show variables like '%innodb_change_buffer_max_size%'

innodb_change_buffer_max_size   25 //最多占用buffer pool 25%

普通索引唯一索引

如果要在这张表中插入一个新记录 (4,400)

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：
对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。
第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：
对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

结论:大部分情况下普通索引可以使用change buffer,减少了IO,性能比唯一索引好,建议尽量选择普通索引,如果业务代码保证不了不会写入重复数据,没办法只能用唯一索引

Mysql优化器选错索引

analyze table t   //对于由于索引统计信息不准确导致的问题，重新统计索引基数解决
select  8 from t force index(a)  //强制使用索引a

前缀索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。缺点是前缀索引用不上覆盖索引
比如邮箱字段建索引

 alter table SUser add index index2(email(7)); // 使用前7位字符做索引
// 查看保留4/5/6/7位仍然唯一的条数,选取不小于L*95%的位数最佳
mysql> select 
  count(distinct email) as L,
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

如果前面字段区分度不高,比如身份证,则可以使用倒序存储,把身份证倒过来存储

创建一个新的hash字段,使用crc32算法算出长字符串的hash值,节省空间,增加计算消耗

flush 脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

触发flush时机

redo log写满了,这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
内存不足,就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。这种情况是常态
空闲的时候
正常关闭的时候

脏页控制策略
正确配置 innodb_io_capacity 这个参数,告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest  //查询磁盘的IOPS

innodb_io_capacity  // 建议设置成磁盘的IOPS
innodb_flush_neighbors  = 0; //Mysql8 默认0

重建表

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

alter table A engine=InnoDB  //重建表命令,Mysql 5.5前  会锁表,Mysql 5.6以后不会
optimize table t  //重建表 + analyze table t 对索引信息重新统计

count(*) 实现

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；但是加了where条件还得全表查询
Innodb在保证逻辑正确的前提下，检索普通索引,尽量减少扫描的数据
count()、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。 count(*)性能最好

最佳实践

CREATE TABLE `rows_stat` (
  `table_name` varchar(64) NOT NULL,
  `row_count` int(10) unsigned NOT NULL,
  PRIMARY KEY (`table_name`)
) ENGINE=InnoDB;

计数直接放到数据库里单独的一张计数表 rows_stat 中,使用事务实现,应该先插入操作记录，再更新计数表。避免行锁等待时间过长

全字段排序

Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

查看排序语句是否使用了磁盘临时文件

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算 Innodb_rows_read 差值 */
select @b-@a;

// number_of_tmp_files : 12 表示将数据分为12份,使用归并算法排序,sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

// number_of_tmp_files : 0 表示在内存排序

select city,name,age from T where city = '杭州' order by name limit 1000
可以使用联合索引 (city,name,age)

row id

对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的,长度为 6 字节；

order by rand() limit 3

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。查询的执行代价往往是比较大的。所以，在设计的时候你要量避开这种写法。
tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。
磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。

随机排序取3个值最佳实践

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； // 在应用代码里面取 Y1、Y2、Y3 值，拼出 SQL 后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

mysql> select count(*) from tradelog where month(t_modified)=7; //效率差

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1'); //效率高

// varchar类型字段需要加引号   隐式类型转换不走索引
mysql> select * from tradelog where tradeid=110717; //int导致全表扫描

// 字符集不同的联表操作也不走索引

innodb 如何解决幻读?

间隙锁 Gap Lock,确保间隙无法插入新的记录
间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row

如果读提交隔离级别够用，业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

短连接风暴

max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

show variables like '%max_connections%'

慢查询

MySQL 5.6 版本以后，创建索引都支持 Online DDL 了
高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句添加索引。

操作流程

比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：
在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
执行主备切换；
这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

最佳实践

上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
在测试表里插入模拟线上的数据，做一遍回归测试；
观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。（我们在前面文章中已经多次用到过 Rows_examined 方法了，相信你已经动手尝试过了。如果还有不明白的，欢迎给我留言，我们一起讨论）。

全量回归测试工具

pt-query-digest

gh-ost

GitHub 的 MySQL 在线架构迁移

主库和读库同步流程

在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令，并执行。

binlog 三种格式

statement 不占空间,有可能导致主备不一致
记录sql原文
row 占空间 目前是主流
记录行数据变化,记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。
mixed 用得不多
mysql判断会导致主备不一致的语句用row,否则用statement

使用binlog恢复数据

// 将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来，放到 MySQL 去执行。
mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

主备延迟

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。接下来，我就和你一起分析下，这可能是由哪些原因导致的。

mysql> show slave status; //seconds_behind_master，用于表示当前备库延迟了多少秒。

延迟原因:

备库性能差
这种部署现在比较少了。因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署，是现在比较常见的情况。
备库压力大
执行慢查询CPU占满的时候导致延迟,一主多从一备方案解决
大事务
不要一次性地用 delete 语句删除太多数据,典型的大事务
大表DDL,使用gh-ost方案解决大表DDL

双主切换过程主备切换

判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
把主库 A 改成只读状态，即把 readonly 设置为 true；
判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
把业务请求切到备库 B。

主备延迟解决方案

Mysql5.5不支持并行复制,MYSQL5.6以上备库设置slave_parallel_workers为 8~16 之间最好（32 核物理机的情况,多线程消化中转日志）,毕竟备库还有可能要提供读查询，不能把 CPU 都吃光了。多worker遵循两个原则

不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
同一个事务不能被拆开，必须放到同一个 worker 中。

MySQL5.7 版本由参数 slave-parallel-type 来控制并行复制策略：
配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
配置为LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略。

MySQL 5.7.22增加了一个新的并行复制策略，基于 WRITESET 的并行复制。

大事务拆解成小事务

一主多从