MySQL 数据库

MySQL 版本历史

  1. MySQL前世今生
  2. MySQL 5.5：10年Oracle收购MySQL后推出5.5，InnoDB成默认引擎
  3. MySQL 5.6：13年推出，里程碑的版本，生产环境使用最多的
  4. MySQL 5.7：15年推出
  5. MySQL 8.0：18年推出
  6. MariaDB：19年推出10.5，是MySQL的创始人担心MySQL在开源粒度上的下降而开发的（Oracle收购MySQL事件）
  7. Percona Server：由MySQL咨询公司Percona发布，此公司发布过工具包xtrabackup、percona-toolkit等
  8. 从全球数据库排行来看，MySQL 远领先于MariaDB和Percona。

MySQL事务并发执行遇到的问题

  1. 脏写：数据在一个事务中修改未提交，另一个事务把它修改了
  2. 脏读：数据在一个事务中修改未提交，另一个事务读到修改后的值
  3. 不可重复读：数据在一个事务中修改且提交了，另一个事务再次读能读到最新值。多次读取一条记录，发现结果不一样。
  4. 幻读：多次读取一个范围内的记录，发现结果不一样（记录增多或减少也算）。
  5. 不可重复读侧重表达读-读，幻读则是读-写，用写来证实读的是鬼影，幻读，可重复读

MySQL四种隔离级别

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED √ √ √
READ COMMITTED √ √
REPEATABLE READ* √
SERIALIZABLE  

MySQL的查询过程

  1. 客户端发送一条查询给服务器；
  2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。维护缓存开销太大，从MySQL 5.7.20开始，不推荐使用查询缓存；
  3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
  4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
  5. 将结果返回给客户端。

char 和 varchar 数据类型区别

  1. char：擅于存储经常改变的值，或者长度相对固定的值。比如 type、ip 地址或 md5 之类的数据，不容易产生碎片
  2. varchar：善于存储值的长短不一的列，也是用的最多的一种类型，节省磁盘空间，保存可变长度字符串。这也是 innodb 官方推荐的类型

InnoDB 和 MyISAM 的区别

  1. InnoDB，支持事务，支持行锁，采用 MVCC(并发版本控制) 来支持高并发，实现了四个标准的隔离级别。
  2. MyISAM，不支持事务、行锁、外键，select count性能好，写性能差，灾难恢复后可能丢数据。

B树、B+树、聚族索引、非聚族索引

  1. B树和B+树的区别
  2. 剖析MySQL的InnoDB索引
  3. B+树中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有。因而B+可以存更多的叶子。
  4. B+树中所有叶子节点都是通过指针连接在一起，而B树不会。因而B+的范围查询更快，而B树需要循环遍历。
  5. InnoDB 和 MyISAM 的索引均采用B+树。
  6. 聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点是指向数据块的指针。
  7. 聚簇索引按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
  8. InnoDB 的主键索引是用的聚簇索引，二级索引（辅助索引）是非聚簇索引。MyISAM 的主键索引和二级索引都是非聚簇索引。

InnoDB 的锁

  1. 行锁场景：教室的自习和维修
  2. S锁(表锁)场景：领导参观教学楼，可以自习不能维修
  3. X锁(表锁)场景：统考要用教学楼，不能自习不能维修
  4. InnoDB共有七种类型的锁：共享锁/独占锁，意向锁，插入意向锁，记录锁，间隙锁，临键锁，自增锁。
    1. 意向锁仅仅是为了后续加表锁时快速判断用的，因为加表锁前要保证没有行锁的存在
    2. 插入意向锁，插入时遇到间隙锁而生成的锁。
    3. 记录锁、间隙锁、临键锁。临键锁的主要目的是为了避免幻读，在RR级别下才会产生，MySQL的锁机制 - 记录锁、间隙锁、临键锁。
    4. 自增锁，表锁，一个事务insert操作时，其他事务的insert必须等待，以便第一个事务插入的行，是连续的主键值。
    5. 如果update where 没有索引的字段，会给聚簇索引所有记录加上临键锁，事务提交后才会释放。
  5. InnoDB的其他锁
    1. 元数据锁：执行DDL语句(ALTER、DROP)时CURD语句会阻塞，执行CURD语句时DDL语句会阻塞
    2. 全局锁，对整个数据库加锁，方便备份数据库时，可读但是不可写

Explain查看执行计划：

  1. type：const用到唯一索引；ref用到非唯一索引；index用到索引但是有where或group by；range范围查询；all全表扫描
  2. possible_keys：可能的索引
  3. key：用到的索引
  4. key_len：索引键的字节长度，可用来确认联合索引中用到的列的数目
  5. rows： MySQL 认为它执行查询时必须检查的行数
  6. extra：Using index condition，索引下推；Using index，覆盖索引；Using where，需在服务器层过滤或需在索引中过滤；Using filesort，不能用索引来排序，需在内存中(或磁盘中)排序；Using temporary，需要创建临时表来存储结果。
CREATE TABLE `tbl_name` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` char(32) NOT NULL DEFAULT '',
`b` char(32) NOT NULL DEFAULT '',
`c` char(32) NOT NULL DEFAULT '',
`d` char(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB;

// 以下三个可以用到索引，https://developer.aliyun.com/article/760998
from tbl_name where a=xx order by b,c;
from tbl_name where a=xx order by b;
from tbl_name where a>xx order by a,b                 #order by字段满足最左前缀

// 以下四个不能用到索引
from tbl_name where a=xx order by b desc,c asc;       #一列为升序一列为降序
from tbl_name where a=xx order by b,d;                #order by列引用了一个不在索引中的字段
from tbl_name where a=xx order by c;                  #无法组合成索引的最左前缀
from tbl_name where a=xx and b in (xx,xx) order by c; #存在范围查询

快照读和当前读有啥区别？幻读的问题怎么解决？

  1. 当前读和快照读
  2. MySQL 提供了两种事务隔离技术，第一个是mvcc，第二个是next-key技术。这个在使用不同的语句的时候可以动态选择。不加lock in share mode之类的就使用mvcc。否则使用next-key。
  3. 快照读历史数据－mvcc。如果是读-写场景是不能避免幻读的。
  4. 当前读最新数据－next-key。可以避免幻读

int占几个字节，int (3) 和 int (11) 有区别吗？

4个字节，最大范围差不多40亿；没有区别，默认占位的区别

数据库事务的四个特性ACID

  1. 原子性（Atomicity），要么都成功，要么都失败
  2. 一致性（Consistency），A转账给B，总金额不变
  3. 隔离性（Isolation），T1事务不影响T2事务，甚至感觉不到它的存在
  4. 持久性（Durability），事务一旦提交就永久保存

时间字段应该选用datetime（MySQL>5.6.4）

redo和 undo

  • CatKang - 数据库故障恢复机制的前世今生
  • 理解数据库中的undo日志、redo日志、检查点，文章的5、6、7介绍了undo和redo
  • 为什么只用 redo-log 或者只用 undo-log 不可以
  • 数据库的四种故障类型：
    • Transaction Failure可能是主动回滚或者冲突后强制Abort；
    • Process Failure指的是由于各种原因导致的进程退出，进程内存内容会丢失；
    • System Failure来源于操作系统或硬件故障；
    • Media Failure则是存储介质的不可恢复损坏。
  • 上一代：
    • 假设只有undo-log：undo -> Data落盘 -> commit。事务提交前Data必须落盘，如果2个事务写同一个Data页。。。
    • 假设只用redo-log：redo -> commit -> Data落盘。事务提交后Data落盘的时间越长，需要的内存越大；而时间越短，2个事务写同一个Data页。。。
  • 这一代：
    • 使用undo+redo：只需要undo、redo落盘即可（顺序IO）。唯一的缺点是写放大，一次事务三次IO。
  • 下一代：
    • NVM vs HDD和SSD：按字节寻址而不是Block；随机IO和顺序IO差不多

binlog 和 redo log 和 undo log

  1. 原来MySQL面试还会问这些
  2. binlog 主从同步/数据恢复，记载的是update/delete/insert这样的SQL语句
  3. redo log 为了灾难恢复用，InnoDB引擎才有，记载的是物理修改的内容（xx页修改了xx）。
    1. 默认日志文件是ib_logfile0和ib_logfile1，数据以512byte为单位
    2. 写入时机：事务提交时、定时1秒、log buffer内存不足
  4. undo log 为了事务回滚用，如果事务是insert，则记录一条delete供回滚用
    1. 默认存放在独立的undo表空间、5.7之前是系统表空间ibdata1
    2. InnoDB有128个回滚段，即128x1024个undo页面链表，即最多支持128x1024个读写事务
    3. undo log也会产生redo log，因为undo log也要实现持久性保护
    4. mvcc 需要用到undo log。记录的roll_pointer会指向undo log记录
  5. InnoDB recovery过程解析
    1. 数据库故障恢复机制的前世今生
    2. 先通过redo log恢复prepare阶段的redo信息
    3. 找到undo log的未提交事务，对比xid，凡是在binlog中存在的事务都需要被提交，不存在的需要被回滚
  6. MySQL delete/update操作：
    1. delete操作是标记为删除，最终的删除操作是purge线程完成的
    2. update非主键：普通update
    3. update主键：先删除该行，再插入一行目标行

binlog 的主从同步

  1. 浅谈 MySQL binlog 主从同步
  2. binLog 的三种格式：statement、row、mixed
  3. 主库接收到更新命令，执行更新操作，生成 binlog
  4. 从库在主从之间建立长连接
  5. 主库 dump_thread 从本地读取 binlog 传送刚给从库
  6. 从库从主库获取到 binlog 后存储到本地，成为 relay log（中继日志）
  7. sql_thread 线程读取 relay log 解析、执行命令更新数据

主从同步延迟的问题

  1. 彻底终结MySQL同步延迟问题
  2. 网络方面：带宽打满、网路延迟
  3. 性能方面：从库的负载很高或者磁盘性能差(iostat查看)
  4. 配置方面：innodb_flush_log_at_trx_commit、sync_binlog
  5. 大事务：批量写操作、DDL操作
  6. 锁冲突：导致执行慢
  7. 组提交：binlog_group_commit_sync_delay，设置多个事务同一时刻提交，有利于多线程复制
  8. 多线程复制：5.6之后可以多线程复制

怎么往大表中添加字段

  1. 建一个空表B，加字段；程序双写A、B表；同步A表数据；ALTER TABLE B RENAME TO A。
  2. 使用第三方工具，MySQL在线修改表结构大数据表的风险与解决办法归纳

大表的limit查询优化

  1. MySQL 近千万级数据表，在分页时有什么好的方案吗
  2. 排序字段只有一个且索引唯一：where 排序字段 > xxx limit 10
  3. 其他：先查id，再通过id获取记录

results matching ""

    No results matching ""