2018MySQL面试知识点整理

1. mysql 查询子句：

   1. group by 多个字段，group by 前可使用聚合函数，
   2. having: 对查询后结果的筛选 和where后面的语法类似
2. 字段别名 表别名 as

3. 子查询

   1. 子查询结果作为父查询的表 select xxx from (子查询) as 子查询结果命名
   2. 子查询作为字段使用： select xxx from table where id in(select id ...)
   3. 子查询可以跨多个表

---

1. 隔离级别：

   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; //当前会话

2. 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

3. 当前读：特殊的读操作，需要加锁：

   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事务操作多行记录是一条一条操作的，获取一条 -> 加锁 -> 操作完 -> 解锁； 继续下一条...

4. 行级锁是锁索引，的前提条件是要匹配索引，否则退化为表锁（锁聚集索引）；

   1. 如果只有二级索引，先锁二级索引，再锁聚集索引（可能会产生死锁）
5. mysql加锁顺序：不管事务有多少语句，只有两阶段锁，合并加锁和合并解锁
6. rr隔离级别能防止幻读：因为会加gap锁，其他事务不能插入记录
7. 幻读的例子：RR级别下面：

8. 死锁的例子：

   1. session1: select * from t1 where id=22 for update;

      1. empty set

   2. session2: select * from t1 where id=23 for update;

      1. empty set

   3. session1: insert into t1 values(22, ...);

      1. 一直未返回

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

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

   5. 分析：

      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)
      

---

1. 表连接

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

      1. 笛卡尔积的数量= num(A) * num(B)

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

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

   3. inner join：内连接，

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

   4. 外连接：

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

---

1. 联合查询： union

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

---

1. GTID复制：mysql5.6+

   1. GTID=source_id:transaction_id
   2. 从故障恢复：CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION

2. 主从复制

   1. 需要指定binglog的文件和pos

---

1. 索引：

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

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

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

   7. 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：使用临时文件排序

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

   9. null与索引的关系：

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

---

1. online ddl

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

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

   2. 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