2018MySQL面试知识点整理

tanyhuan

2019-07-01

关注关注

mysql 查询子句：
1. group by 多个字段，group by 前可使用聚合函数，
2. having: 对查询后结果的筛选和where后面的语法类似
字段别名表别名 as
子查询
1. 子查询结果作为父查询的表 select xxx from (子查询) as 子查询结果命名
2. 子查询作为字段使用： select xxx from table where id in(select id ...)
3. 子查询可以跨多个表

隔离级别：
1. 未提交读（Read uncommitted），可能产生：脏读，不可重复读，幻读
2. 读已提交（Read committed），可能产生：不可重复读，幻读
3. 可重复读（Repeatable read）：可能产生：幻读
4. 串行化（Serializable）：可能产生：无
5. mysql默认级别：可重复读
```
1. select @@global.tx_isolation;
2. select @@session.tx_isolation;
3. set global transaction isolation level read committed; //全局的
4. set session transaction isolation level read committed; //当前会话
```
mvcc(Multi Version Concurrency Control)：为了实现快照读（读写不冲突）
1. redo log: mysql将事务操作过程中产生redo log，事务提交时flush到硬盘（顺序的）
```
1. 当主机崩溃重启，可以从redo log获取日志恢复
```
2. undo log：事务操作过程中，记录修改的回滚操作，事务回滚可以用上
```
1. mysql根据 undo log 可以回溯到某个版本，实现mvcc
```
3. innodb是以聚集索引组织数据的。数据行中包含rowid（主键id），还包括：
```
1. trx_id：最近修改的事务id
2. db_roll_ptr:指向undo分段中的undo log
```
当前读：特殊的读操作，需要加锁：
1. select * from table where ? lock in share mode;（共享锁）
2. select * from table where ? for update;（互斥锁）
3. insert into table values(...);（互斥锁）
4. update table set ? where ?;（互斥锁）
5. delete from table where ?;（互斥锁）
6. mysql事务操作多行记录是一条一条操作的，获取一条 -> 加锁 -> 操作完 -> 解锁；继续下一条...
行级锁是锁索引，的前提条件是要匹配索引，否则退化为表锁（锁聚集索引）；
1. 如果只有二级索引，先锁二级索引，再锁聚集索引（可能会产生死锁）
mysql加锁顺序：不管事务有多少语句，只有两阶段锁，合并加锁和合并解锁
rr隔离级别能防止幻读：因为会加gap锁，其他事务不能插入记录
幻读的例子：RR级别下面：

死锁的例子：

session1: select * from t1 where id=22 for update;
```
1. empty set
```
session2: select * from t1 where id=23 for update;
```
1. empty set
```
session1: insert into t1 values(22, ...);
```
1. 一直未返回
```

session:2 insert into t1 values(23, ...);

1. Error 1213 (40001): Deadlock found when trying to get lock; ...

分析：

1. 当在存在的行进行锁的时候，mysql只有行锁
2. 当对为存在的行进行锁的时候，mysql会锁住一段范围（gap锁）
    1. 范围：
        1. 上述例子（id22,23都不存在）：
            1. 假设之前表有id（11,12），锁住的范围：(12，无穷大)
            2. 假设之前表有id（11,30），锁住的范围：(11，30)
            3. 假设之前表有id（50,xxx），锁住的范围：(无穷小，50)

表连接

笛卡尔积：A X B = { (a,b) | a ∈ A and b ∈ B }， (a,b) 叫做有序偶
```
1. 笛卡尔积的数量= num(A) * num(B)
```

不加任何规则的连接：select * from users,table2，结果是笛卡尔积

1. 如果带了where语句，只筛选出对应表的符合记录的行
2. 和不带连接规则的 inner join一致

inner join：内连接，

1. 如果不带连接规则和上面一样，mysql中 cross join 和 inner join类似
2. 如果带连接规则，结果为笛卡尔积中去掉不满足连接规则的记录
3. 连接规则的类型：
    1. 等值
    2. 不等值
    3. 自连接（相同表不同字段）

外连接：

1. left join: 左表全 右表没有就算null
2. right join:

联合查询： union
1. 联合查询将两个查询的结果组合在一起展示，后面的结果append前面sql的结果，并且字段名用签名sql的字段
2. 联合查询的两个sql 结果字段数量必须相等

GTID复制：mysql5.6+
1. GTID=source_id:transaction_id
2. 从故障恢复：CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION
主从复制
1. 需要指定binglog的文件和pos

索引：

B+Tree：B的含义：可能是名字
与BTree的区别：数据只保存在叶子节点，叶子节点顺序单链表
聚集索引：innodb组织数据的方式，innodb通过主键聚集数据
二级索引：innodb其他索引只存主键id
二级索引优化：覆盖索引，当查询的字段恰好在二级索引上，则不再查主键索引

联合索引的匹配规则：最左原则

1. 不满足，可能出现两个情况：
    1. 次级字段的所有记录是有序的，此时explain中的type=index，表示扫描全index
    2. 不是有序的，用不到索引

explain（关键字段）：

1. select_type：查询类型，
    1. simple：一般是简单查询，不使用union或子查询
    2. subquery：子查询中的第一个select
    3. primary：最外层的select
2. type：mysql找到所需行的方式，又叫“访问类型”，由差到好的顺序：
    1. type=ALL：全表扫描（一般需要进行优化）
    2. type=index：整个索引扫描（不满足最左匹配可能会引起）
    3. type=range：使用一个索引来检索给定范围的行
    4. type=ref：mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断
    5. type=eq_ref：类似ref，区别是使用的索引是唯一索引
    6. const、system：常量匹配，主键匹配
3. rows：找到所需记录需要读取的行数（估计值）
4. extra：详细信息，常见：
    1. Using index：只查询索引就能得到结果（覆盖索引）
    2. Using where：需要通过索引再检索实际数据进行过滤
    3. Using temporary：使用临时表
    4. Using filesort：使用临时文件排序

like "%xxx" 不满足最左匹配，会导致全表扫描

null与索引的关系：

1. is null 是可以使用索引的
2. is not null 不能
3. 但最好能不用null就不用

online ddl

一般情况通过新建临时表实现，参考：https://cloud.tencent.com/dev...

1. 新建临时表为最新的表结构，在业务低峰期加锁拷贝数据到新表，rename旧表和新表
2. 其他实现：
    1. Facebook工具pt-osc 通过触发器同步变化数据 不锁表
    2. gh-ost，通过主从同步binlog方式

mysql5.6+支持在线online ddl

1. 但执行开始也需要一个短暂锁表的过程，准备元数据
2. 分为 inplace 和 copy
    1. 添加字段 可能inplace 和 copy 但支持并发的dml
3. frm文件是表结构定义
4. mysiam： table.frm, table.MYD, table.MYI
5. innodb: table.frm, *.idb 
6. innodb_file_per_table

其他参考资料：
mysql加锁处理分析
http://hedengcheng.com/?p=771
MySQL基于GTID的复制实现详解
http://www.ywnds.com/?p=3898

mysql undo