Mysql45讲关键知识

同步发布博客: 疯狂小兵

为什么Mysql会抖一下

是因为Mysql更新数据只写到redo的log里，达到阈值后会刷脏页，占用CPU资源。脏页是指内存页数据和磁盘页数据不一致的情况。
发生场景

redolog写满到阈值后，需将对应的内存页数据刷到磁盘上。需要尽量避免，否则所有更新操作都会被hang主
内存不足，刷脏页到磁盘上。常态，最需要关心的。
资源空闲时，刷页。也会时不时的刷下脏页。资源空闲期刷脏页，系统不会有压力。
Mysql正常关闭的时候，刷脏页到磁盘。关闭时刷脏页，正常操作，也不会关心性能。

影响性能的几种情况

一次刷脏页太多。
日志写满了，更新全部堵塞住。

刷脏控制策略

设置innodb_io_capacity 参数，可通过fio工具测试磁盘IO的IOPS

注意事项

比如查询操作，触发刷脏页时，会判断旁边的页是不是脏页，是的话一起刷掉，而且还可以向下传递。将相邻的脏页一起刷掉。这也就会导致SQL操作时的rt可能被预期的更慢。可以通过参数innodb_flush_neighbors来控制，设置为1则会查找邻居脏页，设置为0则不查找邻居脏页。对于机械硬盘，建议innodb_flush_neighbors设置为1，对于SSD，建议设置为0。因为SSD的IOPS比机械硬盘高很多。

为什么表数据删掉一半，表文件大小不变

参数innodb_file_per_table控制着表数据存放为值,ON表示表数据放在.idb后缀文件中。OFF表示系统共享空间，默认值为ON。而且如果为OFF，则即使删除表，表空间也不会释放。推荐设置为ON。

delete 记录和表都不会释放表空间，会使得被释放的页被复用，也就是会产生空洞。

那如何去去掉空洞呢？

重建表。使用alter table A engine=InnoDB命令。其隐含意思是alter table t engine=innodb,ALGORITHM=inplace;
analyze table t 只是对表的索引信息做重新统计，没有修改数据
optimize table 等于 recreate+analyze

COUNT(*) 这么慢，我怎么办？

几种获取总数的方式

count(*) 会扫描全表，可能会影响性能。 Mysql做了优化，不取值，按行累加
count(字段) 表示满足条件的数据里，参数"字段"不为NULL的数量
count(id) 因为主键id不能为空，会按主键行累加
count(1) innodb遍历整个表，但不取值，返回给server后，server放入一个1，按行累加

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)

写Binlog后，commit前崩溃掉怎么保证数据完整。

崩溃恢复的判断规则

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整。 binglog完整则提交事务，否则回滚事务。

redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果redo log中有commit则直接提交
如果redo log中只有prepare，但没有commit，则带着XID去binlog中寻找，在binlog中通过checksum判断binlog是否完整

为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？

如果必须要举一个场景，来说明这么做的必要性的话，那就是事务的持久性问题。对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。

能不能只用redo log，不用binlog

不能。

归档。 redo log循环写，起不到归档的作用。
mysql系统依赖，mysql的高可用基础就依赖binlog的复制

为什么这些SQL语句逻辑相同，性能却差异巨大？

对索引字段做函数操作，会使得Mysql放弃走树搜索索引

变种情况包括：

隐式类型转换。 mysql支持字符串转数字的优化，是可以走索引的，反之则不走树索引。
select * from user where id = '123'; id为int类型，是可以走索引的。
隐式字符编码转化，如果两张表join，且其编码不同，则会进行编码转换，也不走所用了。

为什么我只查一行的语句，也执行这么慢？

查询长时间不返回

表被锁住了，可能在等

等 MDL 锁
等 flush
等行锁

查询慢

查询条件上无索引
一致性读和当前读。如果有大量更新，则一致性读需要执行n次undo日志才能获取到。而当前读直接获取最新的版本数据。

幻读是什么，幻读有什么问题？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。使用Gap锁可以解决幻读问题。有了Gap锁之后，如果想往符合gap条件的间隙加一条数据，会被阻塞。作用于insert操作。而Gap锁也只会在可重复读的隔离级别下才会生效。

不可重复读是：在同一事务中，两次查询获取到的同一条数据内容不一致。是因为其他事务更新了当前数据，悲观锁是对行加行锁，乐观锁方式是通过MVCC机制。作用于update和delete操作

Gap锁和行锁合成next-key锁.即不仅要锁住当前存在行记录，也要锁住行记录间的间隙，使得新的符合条件数据不能插入。

也就是说Mysql默认的可重复读级别，如果使用了Gap锁，会使得锁住范围变大，容易导致死锁，因此有部分公司会将Mysql隔离级别设置为读可提交，并将binlog设置为row方式配合使用。

select ... for update语句容易引起死锁。

"饮鸩止渴"的方式提高性能的办法

应对短连接风暴

先处理掉那些占着连接但是不工作的线程。

使用命令show processlist查看空闲连接.

如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

断开连接的命令： kill connection + id

减少连接过程的消耗

如果数据库是被连接打挂了，可以选择跳过权限验证环节。

方法是：重启数据库，并使用–skip-grant-tables 参数启动

慢查询性能问题

索引没设计好

在MySQL 5.6版本以后可直接执行 alter table 语句

语句没写好

改写语句，比如改掉隐式函数转换等

Mysql没选对索引

可以使用force index 强制指定索引

QPS 突增问题

业务上紧急下掉该功能
数据库上删掉该业务使用的数据库账号，使其不能访问数据库
语句改写，将访问大的QPS的SQL语句改写为select 1。仅仅只是用于止血，业务上的风险很高