内蒙古通辽网站建设,怎样建设网站公司,漳州网站建设哪家最权威,wordpress可视化编辑器排行title: MySQL使用技巧整理 date: 2021-04-11 00:00:00 tags:
MySQL categories:数据库 重建索引
索引可能因为删除#xff0c;或者页分裂等原因#xff0c;导致数据页有空洞#xff0c;重建索引的过程会创建一个新的索引#xff0c;把数据按顺序插入#xff0c;这样页面…
title: MySQL使用技巧整理 date: 2021-04-11 00:00:00 tags:
MySQL categories:数据库 重建索引
索引可能因为删除或者页分裂等原因导致数据页有空洞重建索引的过程会创建一个新的索引把数据按顺序插入这样页面的利用率最高也就是索引更紧凑、更省空间。使用语句alter table T engineInnoDB替换alter table T drop index k; alter table T add index(k);。
怎么删除表的前 10000 行
[X]直接执行 delete from T limit 10000单个语句占用时间长锁的时间也比较长而且大事务还会导致主从延迟。[V]在一个连接中循环执行 20 次 delete from T limit 500。[X]在 20 个连接中同时执行 delete from T limit 500人为造成锁冲突。
重新统计索引信息
如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大可以采用这个方法来处理。analyze table t解决统计错误扫描行数问题
紧急创建索引
假设你现在的服务是一主一备主库 A、备库 B这个方案的大致流程是这样的 在备库 B 上执行 set sql_log_binoff也就是不写 binlog然后执行 alter table 语句加上索引执行主备切换这时候主库是 B备库是 A。在 A 上执行 set sql_log_binoff然后执行 alter table 语句加上索引。 平时在做变更的时候你应该考虑类似 gh-ost 这样的方案更加稳妥。但是在需要紧急处理时上面这个方案的效率是最高的。 跳过权限验证的方法
重启数据库并使用–skip-grant-tables 参数启动。在 MySQL 8.0 版本里如果你启用–skip-grant-tables 参数MySQL 会默认把 --skip-networking 参数打开表示这时候数据库只能被本地的客户端连接。
开启慢查询日志特别留意 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 语句的返回结果。
基于binlog日志恢复数据
现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多我来给你举一个可以直接看出来的好处恢复数据。MariaDB 的Flashback工具就是基于上面介绍的原理来回滚数据的。用 binlog 来恢复数据的标准做法是用 mysqlbinlog 工具解析出来然后把解析结果整个发给 MySQL 执行。类似下面的命令mysqlbinlog master.000001 --start-position2738 --stop-position2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;这个命令的意思是将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来放到 MySQL 去执行。
循环复制问题双M结构即互为备份但不能双写
业务逻辑在节点 A 上更新了一条语句然后再把生成的 binlog 发给节点 B节点 B 执行完这条更新语句后也会生成 binlog。建议把参数 log_slave_updates 设置为 on表示备库执行 relay log 后生成 binlog。那么如果节点 A 同时是节点 B 的备库相当于又把节点 B 新生成的 binlog 拿过来执行了一次然后节点 A 和 B 间会不断地循环执行这个更新语句也就是循环复制了。这个要怎么解决呢 规定两个库的 server id 必须不同如果相同则它们之间不能设定为主备关系一个备库接到 binlog 并在重放的过程中生成与原 binlog 的 server id 相同的新的 binlog每个库在收到从自己的主库发过来的日志后先判断 server id如果跟自己的相同表示这个日志是自己生成的就直接丢弃这个日志。
“非双 1”的设置场景业务高峰期备库延迟为了让备库尽快赶上主库用备份恢复主库的副本应用 binlog 的过程批量导入数据的时候。
innodb_flush_log_at_trx_commit2 表示每次事务提交时都只是把 redo log 写到 page cache。sync_binlog1000 表示每次提交事务都 write但累积 1000个事务后才 fsync。
mysqldump逻辑导出数据
命令 mysqldump -h$host -P$port -u$user --add-locks0 --no-create-info --single-transaction --set-gtid-purgedOFF db1 t --wherea900 --result-file/client_tmp/t.sql。–single-transaction 在导出数据的时候不需要对表 db1.t 加表锁而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法–add-locks0 表示在输出的文件结果里不增加LOCK TABLES t WRITE;–no-create-info 不需要导出表结构–set-gtid-purgedoff 不输出跟 GTID 相关的信息如果你希望生成的文件中一条 INSERT 语句只插入一行数据的话可以在执行 mysqldump 命令时加上参数 –skip-extended-insert。
物理复制数据方式。直接复制.frm和.ibd文件是不行的如果是MyISAM引擎则可以仅复制两个文件即可。
假设我们现在的目标是在 db1 库下复制一个跟表 t 相同的表 r具体的执行步骤如下 执行 create table r like t创建一个相同表结构的空表执行 alter table r discard tablespace这时候 r.ibd 文件会被删除执行 flush table t for export这时候 db1 目录下会生成一个 t.cfg 文件在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd这两个命令这里需要注意的是拷贝得到的两个文件MySQL 进程要有读写权限执行 unlock tables这时候 t.cfg 文件会被删除执行 alter table r import tablespace将这个 r.ibd 文件作为表 r 的新的表空间由于这个文件的数据内容和 t.ibd 是相同的所以表 r 中就有了和表 t 相同的数据。 复制时的注意点 在第 3 步执行完 flsuh table 命令之后db1.t 整个表处于只读状态直到执行 unlock tables 命令后才释放读锁在执行 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;因为这种写法很容易就会不慎把密码给改了。
