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

发布时间 : 2021-02-07 14:21

字数:1.3k 阅读 :

评论:

问题汇总

本文汇总关于 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(*)

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 duval1024@gmail.com