架构师修炼之道

知识日新月异,唯有保持同步迭代,才能适应美好的未来!

0%

MySQL使用技巧整理

  1. 重建索引
  • 索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。
  • 使用语句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中增加下面配置
    1
    2
    3
    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 语句的耗时是非常短的。
  1. 授权
  • 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';,因为这种写法很容易就会不慎把密码给改了。