时间戳

首页 > 技术文章 > MySQL的为什么

MySQL的为什么

gaffey 2021-08-04 20:58 原文

Reference

MySQL实战45讲

为什么 MySQL 偶尔会选错索引

原因: 选择索引是优化器的工作, 优化器会结合预计扫描行数, 是否使用临时表, 是否排序因素进行综合判断
解决: 扫描行数不准确可以使用 analyze table 解决, 可以使用 force index 强制使用指定索引, 或者修改 SQL 语句, 或者删除影响优化器判断的索引.

怎么给字符串加索引

字符串相比 int, 索引占用空间巨大, 影响 io 性能, 可以使用前缀索引, 比如字段 name 保存了 abc111, abc222, abc333 这类字符串, 建立 name(3) 的前缀索引
计算前缀索引长度

select count(distinct name) as L from User;

为什么 MySQL 会"抖"

原因: "抖" 意味着MySQL 后台在刷脏页.
MySQL 更新数据采用 WAL 技术(记录redo log, 再异步写入磁盘), 将随机写转化为顺序写, 提高系统性能, 代价就是会产生数据脏页, 脏页想变成干净页就需要在某个时候(mysql 空闲或脏页无被填满) flush 进磁盘, 这时候会占用系统资源影响正常的 SQL 执行, 称之为 "抖"
解决: 正确设置innodb_io_capacity 参数告诉 InnoDB 磁盘的 io 能力.
关注 innodb_io_capacity 参数脏页比例不能超过 75%

磁盘的 IOPS 可以通过 fio 这个工具来测试

 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

count(*) count(1) count(id) count(列) 原理

MyISAM 维护了总行数字段, 可以直接获取, 但是带 where 条件就不好使了
InnoDB 需要遍历每一行, count() 有优化过, 会分析占用空间比较小的索引来执行遍历, 因为一次 io 可以获取更多数据
count(1) count(id) count(列) 会多一次比对 null 值再统计, 理论上 count() 最优

为什么删除数据表大小不变

原因: delete 命令只会将数据标记为可复用,并不会删除物理空间,但是插入数据也不会使用这些可复用的空间, 除非显示指定 id = 你删除的 id 才能使用, 所以会造成数据空洞, 影响 mysql 性能
解决: 重建表

不走索引的情况

  1. 索引上使用函数
    SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc';
  2. 索引列参与计算
    SELECT `sname` FROM `t_stu` WHERE `age`+10=30;
  3. 模糊查找 Like %xxx
    SELECT * FROM `houdunwang` WHERE `uname` LIKE '%后缀'
  4. 隐式类型转换
    SELECT * FROM `t2` WHERE `a`=1; ### a 是 varchar

幻读

产生: 思考下面两个 mysql 连接 m1 和 m2

1 m1: BEGIN;
2 m1：SELECT * FROM test WHERE id = 6;  ### 结果是空
3 m2: INSERT INTO test(id, k) VALUES (6, 6) ### 返回插入成功一行
4 m1：INSERT INTO test(id, k) VALUES (6, 6); ### 返回主键重复

如果是按照 InnDB 行锁的机制, 第 4 步是会插入冲突的,因为 2,3 两步提前插入了相同的数据, 对 T1 事务来说, 先查询再插入, 再符合逻辑不过了, 结果居然失败了, 所以产生了幻读(幻行)

解决: 可以使用 InnoDB 的间隙锁, 或者给 第二步 m1 的查询语句加一个排他锁 SELECT * FROM test WHERE id = 6 FOR UPDATE

特别注意一点: 加锁, 只会加在索引上, 这也是为什么, 数据库不存在的记录也可以加上锁, 因为锁,不对应任何实际数据.

MySQL 如何保证数据不丢失

众所周知, MYSQL 中每条语句都是以一次事务来执行的
当数据写入或更新时, 先写 redo log buffer, redo log 准备好之后, 再写 binlog cache, 这些都是在内存中执行的, redo log buffer 和 bin log cache 写入完毕之后, flush 到各自的 redo log file 和 bin log file, 这两个文件由于是顺序写, 所以虽然是持久化的, 但是也很快, 当 file 也都写入完毕之后, 事务就可以提交了, 如果意外 carsh, 这两个文件也能帮助 mysql 恢复到事务提交后的样子.

读写分离数据延迟问题

原因: binlog 备份是需要时间的, 虽然很快, 假如遇到长事务或者从库查询压力过大影响了同步线程, 都会产生从库数据延迟问题
解决:

  1. 强制查主库 (low)
  2. 读之前 sleep 一下 (low)
  3. 从库查之前先判断 seconds_behind_master 是否等于 0
  4. Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成
  5. 对比 GTID 集合确保主备无延迟

MySQL查询出来数据是放在内存中一下返回客户端的吗?

是边读边发的.
假如 Table 表有 100G 数据, 执行一个全表扫描 select * from Table

  1. 获取一行, 写到 net_buffer 中, 这个 buffer 默认 16k
  2. 重复获取行, 直到 net_buffer 被写满, 调用网络接口发出
  3. 发送成功清空 net_buffer, 继续取下一行

能不能用 Join

不推荐用 Join, 一是 Join 会创建临时表, 二是数据量变大后, 性能急剧下降, 三是系统分库分表后, 所有的 Join 得重写
业务查询需要用到 Join ,完全可以用 where in 语句实现
如果使用的话, 让小表做驱动表, Join 语句尽量使用到索引字段

能不能用 分区表

尽量选择手动分表, 如果有比较成熟的分库分表中间件或者业务比较容易实现的话.
mysql 分区表优点是对业务透明, 但是 Mysql 在第一次打开分区表时,需要访问所有分区, 在 mysql server 层, 所有分区公用同一个 MDL 锁

自增主键到达上限了会发生什么

自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变, 然后插入失败
默认自增 id 是一个无符号4 字节的整数, 那么最大值是 2^32 -1 = 42 亿左右, 一般是够用了, 但是有极少数可能还是不够用, 那么可以定义无符号的 bigint,这个字段可以存放 8 字节, 妥妥够用了

建表没有创建主键会发生什么

InnoDB 会自动创建一个不可见的 row_id 当自增主键,但是这个主键如果到达上限的话, 不会保持不变, 下一个值会变成 0(好像是无符号整形溢出?), 从而覆盖掉以前的数据

二阶段提交时发生异常数据会一致吗

  1. 写入 redolog 处于 prepare 阶段, 写 binlog 时异常, 此时事务还未提交, 理论上这条 update 语句应该不生效, 由于 binlog 没有记录, 此时 binlog 是 ok 的, redolog 已经记录了,但是 redolog 没有 commit, 所以会回滚.
  2. redolog 处于 prepare, binlog 写完未提交, 此时异常, 重启后会判断 这个 update 事务的 binlog 和 redolog 是否一致, 一致则在恢复过程中提交这次事务. 不一致则回滚这次事务

redolog 和 binlog 如何关联起来的

他们有一个共同的数据字段 XID, 崩溃恢复时

  1. redolog 有 prepare 和 commit 标志, 则直接提交事务
  2. redolog 只有 prepare, 没有 commit, 就拿 XID 去 binlog 找对应的事务

** 单独的 binlog 为什么不能做异常恢复**

  1. 历史原因, InnoDB 在加入 MySQL 前就已经有 redolog, 而且 MySQL binlog 不具备崩溃恢复能力, 所以 InnoDB 用的就是原有的 redolog
  2. 实现上, InnoDB 使用 WAL 技术提升读写性能, 执行事务的时候写完内存和日志就算这次事务成功, binlog 无法修复日志中以前记录的 commit, 只会恢复最近一条 commit, 如果将 binlog 机制优化, 去判断前几页的数据 commit 情况进行恢复, 这样等于重新做了一个 redolog 出来.

推荐阅读