mysql 高级特点

本是古典何须时尚 2023-07-09 07:26 38阅读 0赞

*  问题 1：执行一个 update 语句以后，我再去执行 hexdump 命令直接查看 ibd 文件内容，为什么没有看到数据有改变呢？回答：这可能是因为 WAL 机制的原因。update 语句执行完成后，InnoDB 只保证写完了 redo log、内存，可能还没来得及将数据写到磁盘。
 *  问题 2：为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？回答：MySQL 这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。
 *  问题 3：事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？回答：不会。因为这时候 binlog 也还在 binlog cache 里，没发给备库。crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。
 *  问题 4：如果 binlog 写完盘以后发生 crash，这时候还没给客户端答复就重启了。等客户端再重连进来，发现事务已经提交成功了，这是不是 bug？回答：不是。你可以设想一下更极端的情况，整个事务都提交成功了，redo log commit 完成了，备库也收到 binlog 并执行了。但是主库和客户端网络断开了，导致事务成功的包返回不回去，这时候客户端也会收到“网络断开”的异常。这种也只能算是事务成功的，不能认为是 bug。
 *  实际上数据库的 crash-safe 保证的是：如果客户端收到事务成功的消息，事务就一定持久化了；如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。
 *  如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

> 1.  设置 binlog\_group\_commit\_sync\_delay 和 binlog\_group\_commit\_sync\_no\_delay\_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
> 2.  将 sync\_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
> 3.  将 innodb\_flush\_log\_at\_trx\_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

*  主备延迟的相关问题

> 1、主备延迟，就是在同一个事务在备库执行完成的时间和主库执行完成的时间之间的差值，包括主库事务执行完成时间和将binlog发送给备库，备库事务的执行完成时间的差值。每个事务的seconds\_behind\_master延迟时间，每个事务的 binlog 里面都有一个时间字段，用于记录主库上的写入时间，备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时的差值。  
> 2、主备延迟的来源①首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差，原因多个备库部署在同一台机器上，大量的查询会导致io资源的竞争，解决办法是配置”双1“，redo log和binlog都只write fs page cache②备库的压力大，产生的原因大量的查询操作在备库操作，耗费了大量的cpu，导致同步延迟，解决办法，使用一主多从，多个从减少备的查询压力③大事务，因为如果一个大的事务的dml操作导致执行时间过长，将其事务binlog发送给备库，备库也需执行那么长时间，导致主备延迟，解决办法尽量减少大事务，比如delete操作，使用limit分批删除，可以防止大事务也可以减少锁的范围。  
> ④大表的ddl，会导致主库将其ddl binlog发送给备库，备库解析中转日志，同步，后续的dml binlog发送过来，需等待ddl的mdl写锁释放，导致主备延迟。  
> 3、可靠性优先策略，①判断备库 B 现在的 seconds\_behind\_master如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步，②把主库 A 改成只读状态，即把 readonly 设置为 true，③判断备库 B 的 seconds\_behind\_master的值，直到这个值变成 0 为止； 把备库 B 改成可读写也就是把 readonly 设置为 false； 把业务请求切换到备库，个人理解如果发送过来的binlog在中转日志中有多个事务，业务不可用的时间，就是多个事务被运用的总时间。如果非正常情况下，主库掉电，会导致出现的问题，如果备库和主库的延迟时间短，在中转日志运用完成，业务才能正常使用，如果在中转日志还未运用完成，切换为备库会导致之前完成的事务，”数据丢失“，但是在一些业务场景下不可接受。  
> 4、可用性策略，出现的问题：在双m，且binlog\_format=mixed，会导致主备数据不一致，使用使用 row 格式的 binlog 时，数据不一致的问题更容易发现，因为binlog row会记录字段的所有值。

*  innodb 和memory引擎的区别？

> InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同  
> 内存表的优势是速度快，其中的一个原因就是 Memory 引擎支持 hash 索引。当然，更重要的原因是，内存表的所有数据都保存在内存，而内存的读写速度总是比磁盘快。

*  为什么我不建议你在生产环境上使用内存表

> 内存表的锁的锁粒度问题。内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作。  
> 数据放在内存中，是内存表的优势，但也是一个劣势。因为，数据库重启的时候，所有的内存表都会被清空。

*  内存临时表的效果更好

> 相比于 InnoDB 表，使用内存表不需要写磁盘，往表 temp\_t 的写数据的速度更快；索引 b 使用 hash 索引，查找的速度比 B-Tree 索引快；临时表数据只有 2000 行，占用的内存有限

*  自增主键 id 不连续的原因？

> 唯一键冲突是导致自增主键 id 不连续的第一种原因。事务回滚也会产生类似的现象，这就是第二种原因。第三个原因是在批量插入数据时语句执行过程中，第一次申请自增 id，会分配 1 个；1 个用完以后，这个语句第二次申请自增 id，会分配 2 个； 2个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个。每次申请都是上一次申请个数的两倍，可能造成申请的ID没有用完的情况，就造成了ID不连续。

*  自增值为什么不能回退

> 假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。假设事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。事务 B 正确提交了，但事务 A 出现了唯一键冲突。如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。

*  怎么样最快的复制一张表？

> 1.  通过导出sql文件进行新建表然后导入数据。2.通过导出CSV文件，来复制表。3. 通过复制物理文件来达到复制表的目的。

*  使用 mysqldump 命令将数据导出成一组 INSERT 语句？

> 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 相关的信息；–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。如果你希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数–skip-extended-insert。

*  mysql 通过sql文件插入数据

> mysql -h127.0.0.1 -P13000 -uroot db2 -e “source /client\_tmp/t.sql”

*  mysql 导出csv文件

> select \* from db1.t where a>900 into outfile ‘/server\_tmp/t.csv’;  
> 这条语句会将结果保存在服务端。如果你执行命令的客户端和 MySQL 服务端不在同一个机器上，客户端机器的临时目录下是不会生成 t.csv 文件的。这条命令不会帮你覆盖文件，因此你需要确保 /server\_tmp/t.csv 这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。

*  向数据库中导入csv格式的数据

> load data infile ‘/server\_tmp/t.csv’ into table db2.t;  
> load data 命令有两种用法：不加“local”，是读取服务端的文件，这个文件必须在 secure\_file\_priv 指定的目录或子目录下；加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data 流程。  
> select …into outfile 方法不会生成表结构文件,  
> mysqldump -h$host -P$port -u$user —single-transaction --set-gtid-purged=OFF db1 t --where=“a>900” --tab=$secure\_file\_priv 这条命令会在 $secure\_file\_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据。

*  物理拷贝的方法

> 可以利用mysql5.6之后引入的 传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。假设我们现在的目标是在 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 相同的数据。

*  三种方法的优缺点？

> 我们来对比一下这三种方法的优缺点。物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：必须是全表拷贝，不能只拷贝部分数据；需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。前两种方式都是逻辑备份方式，是可以跨引擎使用的。

*  mysql 用户权限操作

> create user ‘ua’@’%’ identified by ‘pa’; 创建一个用户’ua’@’%’，密码是 pa。  
> grant all privileges on *.* to ‘ua’@’%’ with grant option;磁盘上，将 mysql.user 表里，用户’ua’@’%‘这一行的所有表示权限的字段的值都修改为‘Y’；  
> revoke all privileges on *.* from ‘ua’@’%’; 回收上个语句所赋予权限  
> grant all privileges on db1.\* to ‘ua’@’%’ with grant option;要让用户 ua 拥有库 db1 的所有权限

*  flush privileges 命令的使用场景?

> flush privileges会清空 acl\_users 数组，然后从 mysql.user 表中读取数据重新加载，重新构造一个 acl\_users 数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。grant 语句都是即时生效的，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。比如直接用 DML 语句操作系统权限表

*  mysql 的常用符号

> \\G 行转列并发送给 mysql server  
> \\g 等同于 ;  
> ! 执行系统命令  
> \\q exit  
> \\c 清除当前SQL（不执行）  
> \\s mysql status 信息

*  mysql分区表的注意事项

> MySQL 在第一次打开分区表的时候，需要访问所有的分区；在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。

*  分区表的应用场景

> 分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过 alter table t drop partition …这个语法删掉分区，从而删掉过期的历史数据。这个 alter table t drop partition …操作是直接删除分区文件，效果跟 drop 普通表类似。与使用 delete 语句删除数据相比，优势是速度快、对系统影响小

*  使用 left join 时，左边的表不一定是驱动表。如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面。
 *  distinct 和 group by 的性能

> 不需要执行聚合函数时，distinct 和 group by 这两条语句的语义和执行流程是相同的，因此执行性能也相同。执行流程是 创建一个临时表，临时表有一个字段 a，并且在这个字段 a 上创建一个唯一索引；遍历表 t，依次取数据插入临时表中：如果发现唯一键冲突，就跳过；否则插入成功；遍历完成后，将临时表作为结果集返回给客户端。

*  每种自增 id 用完之后怎么办?

> 在达到上限后的表现也不同：表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。row\_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row\_id，后写的数据会覆盖之前的数据。Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。InnoDB 的 max\_trx\_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。thread\_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。