2018MySQL面试知识点整理

栏目: 数据库 · 发布时间: 5年前

  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级别下面: 2018 <a href='https://www.codercto.com/topics/18746.html'>MySQL</a> 面试知识点整理
  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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Web程序设计

Web程序设计

(美)塞巴斯塔 / 王春智、刘伟梅 / 清华大学出版社 / 2011-1 / 69.00元

《Web程序设计(第6版)》浓墨重彩地描述客户端和服务器端Web开发技术,深入分析Web站点构造和维护工具,并透彻讲解主流Web编程语言。《Web程序设计(第6版)》对上一版内容做了全面细致的修改,并融入了NetBeans 6.7、Visual Studio 8和ASP.NET Web服务等最新技术。《Web程序设计(第6版)》既可以作为高校教材,也可供专业Web编程人员参考使用。一起来看看 《Web程序设计》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

MD5 加密
MD5 加密

MD5 加密工具