深入 MySQL (六) 常见问题汇总

本文汇总关于 MySQL 底层原理常见问题。

• 1.如何避免长事务对业务的影响？

  这个问题，我们可以从应用开发端和数据库端来看。

  • 首先，从应用开发端来看：确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
  • 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
  • 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

  其次，从数据库端来看：

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

• 2.如果针对InnoDB表进行索引重建，对二级索引执行：

    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);

  这两者有啥异同，或者存在什么问题？

  • 索引可能因为删除或页分裂，而使得数据页存在空洞，重建索引会重新创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
  • 对二级索引使用该SQL重建是可行的合理的。
  • 对于主键索引，无论是删除主键还是新建主键，都会导致整个表发生重建。而应该使用alter table T engine=InnoDB； 语句进行主键重建。

• 3.字段k上有二级索引，现有如下两个功能相同的SQL语句，哪个更优：

    select * from T where k in(1,2,3,4,5)
    select * from T where k between 1 and 5

  SQL1需要搜索5次，并回表5次；SQL2只需要搜索1次，并回表5次。SQL充分利用了B+树叶子节点顺序排序的特点，可以加速范围查询。

• 4.如何解决热点行更新导致的性能问题？

  • 1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
  • 2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
  • 3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。

• 5.MySQL出现抖动原因

  很有可能在刷脏页。刷脏页触发原因有：

  • redo log写满；
  • 内存不足（其实是buffer pool）的时候淘汰了脏页；
  • MySQL在认为空闲的时段，会自行刷脏页；
  • MySQL关闭前会刷脏页；
    刷脏页的策略调优：
  • 磁盘能力。能力越大，脏页刷的越快。参数：innodb_io_capacity；
  • 脏页比例上限。参数innodb_max_dirty_pages_pct，默认值:75%。生产中应该调控innodb_io_capacity使得脏页比例不要接近innodb_max_dirty_pages_pct 。查看脏页比例方法：

      set global show_compatibility_56=on;
     select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
     select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
     select @a/@b;

  • 刷相邻脏页。机械磁盘建议打开（1），SSD磁盘可以关闭（0）。参数：innodb_flush_neighbors

• 6.删除了数据，但是表文件没有缩小？

  表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

  • 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起，此时drop表不会回收表空间。
  • 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中，此时drop表会回收表空间。

  删除数据记录不一定回收空间，一般只是标记为可复用，此时数据页会留下空洞的，等待新的数据插入复用这些空洞。

  主动回收表空间的方法：

  • 使用alter table t engine = InnoDB；该命令会使用Online DDL重建新表（MySQL5.6+），因此可以在业务低峰期执行。；
  • 使用analyze table t 。该命令会对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
  • 使用optimize table t 相当于重建表+重新统计索引信息。

• 7.count效率

  按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)