MySQL使用技巧整理

MySQL使用技巧整理

• 索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
• 使用语句alter table T engine=InnoDB替换alter table T drop index k; alter table T add index(k);。

1. 怎么删除表的前 10000 行

• [X]直接执行 delete from T limit 10000：单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
• [V]在一个连接中循环执行 20 次 delete from T limit 500。
• [X]在 20 个连接中同时执行 delete from T limit 500：人为造成锁冲突。

1. 重新统计索引信息

• 如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。
• analyze table t：解决统计错误扫描行数问题

1. 紧急创建索引

• 假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：
  1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
  2. 执行主备切换；
  3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。
• 平时在做变更的时候，你应该考虑类似 gh-ost 这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。

1. 跳过权限验证的方法

• 重启数据库，并使用–skip-grant-tables 参数启动。
• 在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 –skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。

1. 开启慢查询日志，特别留意 Rows_examined 字段是否与预期一致。

• 在my.cnf中增加下面配置

  slow_query_log = 1
  slow-query_log_file = /var/log/mysql-slow.log
  long_query_time = 0 #确保每个语句都会被记录入慢查询日志
  

• 创建/var/log/mysql-slow.log文件并将其用户设置为mysql用户
• pt-query-digest工具帮你检查所有的 SQL 语句的返回结果。

1. 基于binlog日志恢复数据

• 现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据。
• MariaDB 的Flashback工具就是基于上面介绍的原理来回滚数据的。
• 用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;，这个命令的意思是，将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来，放到 MySQL 去执行。

1. 循环复制问题（双M结构，即互为备份，但不能双写）

• 业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（建议把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。
• 那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？
  1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
  2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
  3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

1. “非双 1”的设置场景：业务高峰期；备库延迟，为了让备库尽快赶上主库；用备份恢复主库的副本，应用 binlog 的过程；批量导入数据的时候。

• innodb_flush_log_at_trx_commit=2 表示每次事务提交时都只是把 redo log 写到 page cache。
• sync_binlog=1000 表示每次提交事务都 write，但累积 1000个事务后才 fsync。

1. mysqldump逻辑导出数据

• 命令 mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql。
• –single-transaction 在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
• –add-locks=0 表示在输出的文件结果里，不增加”LOCK TABLES t WRITE;”；
• –no-create-info 不需要导出表结构；
• –set-gtid-purged=off 不输出跟 GTID 相关的信息；
• 如果你希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数 –skip-extended-insert。

1. 物理复制数据方式。（直接复制.frm和.ibd文件是不行的，如果是MyISAM引擎则可以仅复制两个文件即可。）

• 假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：
  1. 执行 create table r like t，创建一个相同表结构的空表；
  2. 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
  3. 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
  4. 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
  5. 执行 unlock tables，这时候 t.cfg 文件会被删除；
  6. 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。
• 复制时的注意点
  1. 在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；
  2. 在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

• create user 'ua'@'%' identified by 'pa'; 创建一个用户；
• grant all privileges on *.* to 'ua'@'%' with grant option; 全局权限，作用于整个MySQL实例，这些权限信息保存在 mysql库的user表里；
• revoke all privileges on *.* from 'ua'@'%'; 回收上面的grant语句赋予的权限；
• grant all privileges on db1.* to 'ua'@'%' with grant option; 除了全局权限，MySQL 也支持库级别的权限定义。
• grant all privileges on db1.t1 to 'ua'@'%' with grant option; 表权限定义存放在表 mysql.tables_priv 中；
• grant SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option; 列权限定义存放在表 mysql.columns_priv 中；
• 不建议的写法 grant super on *.* to 'ua'@'%' identified by 'pa';，因为这种写法很容易就会不慎把密码给改了。