乐趣区

关于mysql:Mysql45讲关键知识

jiezi

为什么 Mysql 会抖一下

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

  1. redolog 写满到阈值后，需将对应的内存页数据刷到磁盘上。须要尽量避免，否则所有更新操作都会被 hang 主
  2. 内存不足，刷脏页到磁盘上。常态，最须要关怀的。
  3. 资源闲暇时，刷页。也会时不时的刷下脏页。资源闲暇期刷脏页，零碎不会有压力。
  4. Mysql 失常敞开的时候，刷脏页到磁盘。敞开时刷脏页，失常操作，也不会关怀性能。

影响性能的几种状况

  1. 一次刷脏页太多。
  2. 日志写满了，更新全副梗塞住。

刷脏控制策略

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

注意事项

  1. 比方查问操作，触发刷脏页时，会判断旁边的页是不是脏页，是的话一起刷掉，而且还能够向下传递。将相邻的脏页一起刷掉。这也就会导致 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 记录和表都不会开释表空间，会使得被开释的页被复用，也就是会产生空洞。

那如何去去掉空洞呢？

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

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

几种获取总数的形式

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

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

写 Binlog 后，commit 前解体掉怎么保证数据残缺。

解体复原的判断规定

  1. 如果 redo log 外面的事务是残缺的，也就是曾经有了 commit 标识，则间接提交；
  2. 如果 redo log 外面的事务只有残缺的 prepare，则判断对应的事务 binlog 是否存在并残缺。binglog 残缺则提交事务，否则回滚事务。

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

它们有一个独特的数据字段，叫 XID。解体复原的时候，会按程序扫描 redo log：

  1. 如果 redo log 中有 commit 则间接提交
  2. 如果 redo log 中只有 prepare，但没有 commit，则带着 XID 去 binlog 中寻找，在 binlog 中通过 checksum 判断 binlog 是否残缺

为什么还要两阶段提交呢？罗唆先 redo log 写完，再写 binlog。解体复原的时候，必须得两个日志都残缺才能够。是不是一样的逻辑？

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

能不能只用 redo log，不必 binlog

不能。

  1. 归档。redo log 循环写，起不到归档的作用。
  2. mysql 零碎依赖，mysql 的高可用根底就依赖 binlog 的复制

为什么这些 SQL 语句逻辑雷同，性能却差别微小？

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

变种状况包含：

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

同步公布博客: 疯狂小兵

退出移动版