MySQL
黄鸭 3/13/2022 server
# mysql中sql语句的执行流程
查看答案
- server层：包含查询缓存，分析器，优化器
- 存储引擎：主要负责数据的存储和读取，innodb有自己的redolog模块
# mysql隔离级别 tips
查看答案
- 未提交读(Read Uncommitted)：允许脏读
- 提交读(Read Committed)：只能读取到已经提交的数据
- 可重复读(Repeated Read)：可重复读，会出现幻读
- 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
# mysql脏读
查看答案
读未提交，读缓存
# 提交读，不可重复读
查看答案
开启事务后，读取硬盘，硬盘有变化
# 可重复读
查看答案
开启事务后，读取redolog，由于查询可重复读，但是表实际发生了更改所以update和insert有异常
# mysql执行顺序 tips
查看答案
- undolog(开启事务)
- redolog（执行sql）
- 修改内存
- 写入硬盘
- 写入redolog
- 写入binlog
# redolog new
查看答案
- 日志先行，redolog的机制是double write
- innodb特有的日志，mysql写redolog内存，更新内存，闲时把redolog写到硬盘
- redolog在异常重启时会根据redolog日志恢复
# undolog
查看答案
保存了事务发生之前的数据，可以用于回滚，同时可以提供多版本并发控制下的读MVCC，也即非锁定读
# binlog
查看答案
属于mysqlserver层日志，用指令的方式记录，执行成功后追加日志
# mysql宕机恢复 new
查看答案
- mysql重启时如果没有commit的事务会因为超时而rollback
- 已经commit的数据会根据redolog的操作写入到表文件中
# MyISAM和InnoDB的区别
查看答案
- 本质区别：innodb是聚簇索引，myisam是非聚簇索引
- innodb支持事务，myisam不支持事务（因为myisam不支持redolog和undolog）
- InnoDB 支持外键，而 MyISAM 不支持
- innodb没有记录总行数，myisam支持（因为innodb返回的是开启事务的总行数，myisam并不清楚是否统计了脏读和幻读）
- innodb支持行锁，myisam支持表锁（innodb行锁是通过给索引上锁实现的，只有通过索引条件检索数据才会使用行锁）
- 表层现象：innodb用的两个文件，myisam分为三个文件，索引文件，表结构文件和数据文件
# mysql锁分类
查看答案
- 按照乐观/悲观分为：乐观锁和悲观锁
- 按照功能分为：互斥锁和排它锁
- 按照粒度分为：表锁、行锁和页面锁
- 无法分类的：自增锁和间隙锁
# mysql乐观锁
查看答案
mysql的乐观锁通常使用版本号或时间戳实现
# mysql锁的使用
查看答案
- 在myisam中执行update，delete，insert会自动加锁，执行完成自动解锁
- 在innodb中意向锁是innodb自己加的，在事务结束或回滚时释放
- innodb会在范围条件时使用间隙锁，防止幻读
# mysql死锁
查看答案
两个事务在同一资源相互占用，锁占用超时时数据库会检测死锁，innodb会回退一个事务干掉死锁
开发时应该遵循相同顺序避免死锁
innodb不能检测到外键死锁，需要手动干掉事务
# mysql为什么用自增列作为主键？
查看答案
插入索引时维护时间少
# innodb存储引擎四大特性
查看答案
# 插入缓存
执行更新操作时会先修改缓冲池，在写入磁盘，对非聚集索引有效
# 两次写入
在mysql从内存写入硬盘的过程中，先把数据拷贝到buffer中，顺序写到buffer文件中，最后写到存储文件中
# 自适应hash
当非聚簇索引被频繁使用，非聚簇索引会转换成hash索引
# 预读
当数据页被连续读时，会将下一个页加载到内存中，这个过程也叫线性预读
# 事务的四个特性
查看答案
原子性
一致性
隔离性
持久性
# MySQL原子性怎么保证 new
查看答案
undolog是回滚日志，提供回滚操作，保证事务的一致性。当操作失败会回滚到数据处理之前的状态
# undolog在宕机时怎么保证原子性 new
查看答案
mysql是日志先行,当事务超时的时候就会回滚
# mysql索引
查看答案
btree+结构，本质是对硬盘优化的搜索树，索引的内容区分度越高越好
# mysql索引及分类 tips
查看答案
普通索引
主键索引
唯一索引
联合索引
全文索引
空间索引
# 存储过程和触发器
查看答案
- 存储过程是打包sql记录的过程
- 触发器是当发生事件的时候，触发动作
- 视图是sql结果当做表继续使用
- 三者都因为不好维护，速度慢，不在项目中使用
# 说一说drop、delete与truncate的区别？
查看答案
- drop：删表，不支持回滚
- truncate：删表内容，不支持回滚，速度快
- delete：删某条内容，支持回滚，速度慢
# mysql数据类型
查看答案
- int：4字节
- char：255字节
- varchar：括号里为字符，65535字节
# varchar和char的区别
查看答案
char定长，varchar变长，会有有1到2位记录长度（猜测硬盘中不会有分隔符）
# mysql 3范式
查看答案
- 第一范式（1NF）：字段原子性
- 第二范式（2NF）：有主键
- 第三范式（3NF）：非主键依赖主键
# count(*)、count(1)、count(column)的区别
查看答案
- count(*)和count(1)等效
- count(column)中column为null不计数
# LIKE声明中的％和_是什么意思？
查看答案
％对应于0个或更多字符，_只是LIKE语句中的一个字符。
# 如何防止SQL注入漏洞？
查看答案
- 预处理INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)
- 然后传递参数
# hash索引缺点
查看答案
- 不支持范围查询
- 不支持排序
- 不支持模糊查询
# 超大分页怎么做
查看答案
先查询id再查询具体数据
# 主键与唯一索引的区别
查看答案
- 唯一索引允许为空，主键索引不允许为空
- 主键可以做外键，唯一索引不可以
- 一张表唯一索引可以有多个，主键只有一个
# mvcc 和 read view
查看答案
过程分为3步
- 表不可见字段
- DB_TRX_ID 事务id
- DB_ROW_ID 隐藏主键
- DB_ROLL_PTR 回滚指针指向undolog 2.undolog
- 保存历史版本状态,当不用事务对同一条记录做修改的时候会形成链表,链首是最新记录,尾部是最早记录 3.readview
- trx_list 系统活跃的事务id
- up_limit_id 列表中事务最小id 事务最小id前数据可被看见
- low_limit_id 系统尚未分配下的事务id 一定不可被看见
- (以上字段都是开启readview的状态,能看到开启事务前就完成,已提交的事务,不能看到之后新开启的事务)
- (更早的事务 up_limit_id<->trx_list<->low_limit_id 更晚的事务) 4.生成视图时机
- RC(不可重复读)每次在进行快照读的时候都会生成新的readvew
- RR(可重复读)只有在第一次进行快照读的时候才会生成readview,之后的读操作都会用第一次生成的readview
# mysql常用命令
查看答案
- show processlist查看线程
- show variabless查看配置
- show status查看状态
# mysql常用命令
查看答案
- 等价变换策略
- 5=5会被干掉
- a<b and b = 5 优化成a<5
- 基于联合索引调整位置
- 优化count min max函数
- min找索引最左
- max找索引最右
- 提前终止查询
- limit查询只取所需
- in优化
- 当使用简单in操作时优化器会分割成几个sql语句合并处理
- 如果使用in加子查询,sql会变更为连表查询
# MySQL的主键用uuid的坏处
查看答案
- uuid的最大坏处是不是有序递增,在innodb这种使用聚集索引的表上会导致叶子节点重排
- uuid维护树开销比较大,因为无序
- uuid比较长,占用空间
- uuid能保证唯一性,但是没有时间粗略有序性