MySQL面试知识点总结

MySQL知识点

myisam、innodb区别

• myisam不支持事务、不支持外键约束、索引文件和数据文件分开，这样可以在内存中可以缓存更多的索引，对查询的性能更好。
• Innodb 支持事务，走簇族索引，强制要求主键，支持外键约束、高并发、大数据量、高可用等成熟的数据库架构

in 与 exists区别

• in 先执行子句，得出结果后在外表查找。
• exists 循环外表所有数据，于子句中查询是否存在。

   DELETE FROM Products P1
     WHERE EXISTS ( SELECT ＊
                      FROM Products P2
                    WHERE P1.name = P2.name
                      AND P1.price = P2.price
                      AND P1.rowid < P2.rowid);

B树与B+树区别?

• B树 每一个节点都存储数据
• B+树，对B树做了优化，只在叶子节点存储数据，数据直接存在指针

索引分类？

• 聚簇索引
  • Innodb 主键索引，结构B+树，叶子节点存放数据，非叶子节点存放排好序的主键id。
• 非聚簇索引
  • 普通索引
    • 对某一个表里的字段，进行索引，根据这个字段生成一个B+树，存着这个字段、主键等信息，用于查找
  • 聚合索引
    • 多个字段建立联合索引

回表

• 回表: 当通过非聚簇索引查寻，查找的字段包含非索引字段，需要根据id回表查一下字段数据

判断索引是否生效？

• explain 做分析

联合索引失效的场景？

• 联合索引生效采用最左原则
• 在索引字段上使用“not”，“<>”，“!=”等范围查找会使范围查找后面的字段失去索引
• like查询以“%”开头的语句
• or语句连接非索引字段
• 在索引列上使用“IS NULL”或“IS NOT NULL”操作

聚簇索引与普通索引的区别？

• 聚族索引
  • 叶子结点根据主键排序，生成指针，行成一个数据链表
  • 每个叶子节点都存储主键值、事务ID、回滚指针以及余下的数据列.
• 普通索引
  • 也是B+树结构, 单独维护的一个目录业
  • 每个叶子节点存放的是主键值，如果查询数据为非索引字段，将会根据主键值进行回表查询

幻读、脏读、不可重复读

• 幻读:级别为可重复读，当你开启事务读一张表，进行范围查找，其他事务新增或者更新操作时，之后再以同样的条件进行范围查询，导致两次查询的数据出现不一致，造成幻读。
• 脏读:级别为读未提交，当一个事务在访问数据，并且对数据进行了修改, 而这种修改还没有提交，另一个事务也访问了这条数据，然后使用了这个数据。
• 不可重复读:在一个事务内，多次读同一条数据,两次读到的数据不一致。导致原因: 在两次读数据的时候，另一个事务修改了这条数据,导致两次数据不一致。

事务概述

• 事务：一组逻辑操作单元，使数据从一种状态到另一种状态
• 处理原则: 保证所有事务都作为一个工作单元来执行，即使出现故障，都不能改变这种执行方式。当一个事务执行多个操作时，要么所有事务都被提交，修改将永久的保存；要么数据库系统将所有的修改放弃，整个事务回滚到最初状态。

事务的特性

• A 原子性(Atomicity)
  • 操作整体，不可再分
• C 一致性(Consistency)
  • 从一个合法性状态到另一个合法性状态
• I 隔离性(Isolation)
  • 事务执行不能被其他事务干扰
• D 持久性(Durability)
  • 事务一旦提交，对数据库的改变就是永久性的
• 原子性是基础，隔离性是手段，一致性是约束条件，持久性是目的

四种隔离级别

• 读未提交
  • 未提交读隔离级别也叫脏读，事务可以读取其他未提交的数据
• 读已提交
  • 事务未提交之前所做的修改其他事务是不可见的
• 可重复读
  • 保证同一个事务多次相同的查询结果是一致的，mysql默认隔离级别
• 可串行化
  • 保证读取的范围内没有新的数据插入, 比如事务第一次查询到某个范围的数据，第二次查询也同样得到相同范围的数据，中间没有数据插入进来
• 事务隔离机制的实现是基于锁机制和并发调度(MVCC)

InnoDB MVCC

• 多版本并发控制，通过保存修改的旧版本的信息来支持并发一致性读和回滚等特性。
• 目的
  • 为了实现可重复读，高效的解决读写冲突
• 实现原理
  • 三个隐式字段
    • DB_ROW_ID
      • 隐含的自增ID(隐藏主键)
    • DB_TRX_ID
      • 最近修改事务ID
    • DB_ROLL_PTR
      • 回滚指针
    • DELETED_BIT
      • 记录被更新或删除
  • undo日志
    • 针对修改数据操作，为了回滚而记录的日志
    • insert undo日志
    • update undo日志
    • delete undo日志
  • read view(读视图)
  • 快照读
    • 读取数据时不加锁，每次都会读取当前的版本，可能读取到的数据不是当前最新的，但是保证同一个事务内读取的数据是一致性的

死锁问题

• 死锁
  • 形成原因
    • 事务A 对id=1数据加一个锁，事务B对id=2的数据加一个锁，事务A对id=2加锁(需要等待事务B释放),事务B对id=1数据加锁(需要等待事务A释放)，形成死锁
  • 解决办法
    • 第一种方法: 等待事务超时回滚,让其他事务继续进行
    • 第二种方法: 死锁检查，主动回滚某条事务
      • select * from information_schema.innodb_trx;
      • kill trx_id;

常见的数据库优化方案

• 查询优化

  • 适当的使用索引，保证语句命中索引
  • 多表查询使用 join 替代子查询(最好避免多表查询)
  • 避免 select *，指明字段
  • 尽量减少非索引字段排序
  • 避免使索引失效的语句
  • union all 替代 union
  • 合理利用分页

    select id,name from product limit 866613, 20;
    <!--可以替换为-->
    select id,name from product where id> 866612 limit 20;
    

  • InnoDB事务处理优化，合并多次操作
    • 多步相关操作使用事务，提高性能
    • InnoDB只读事务优化
      • 对一次执行多个查询语句，开启只读事务.只读事务无需启动回滚段、不记录回滚日志

• 优化表存储

  • 针对经常删除的表，会形成大量的数据碎片，占据磁盘
    • 使用 OPTIMIZE TABLE tablename; 语句优化表空间
    • OPTIMIZE会复制数据表的部分数据并且重建索引，减少表空间及磁盘碎片。

• 优化InnoDB重做日志

  • 具体操作可以查看资料

• InnoDB磁盘IO优化

  • 如果数据库设计也调优都遵循最急实践准则，由于大量I/O操作导致瓶颈，利用TOP查看数据库CPU使用率未达到70%，则可以考虑针对磁盘做优化
    • 1、增加缓冲池大小，减少I/O操作负载
    • 2、调整刷盘机制

redo log、bin log区别

• 重做日志
  • 作用: 确保事务的持久性
  • 内容: 物理格式的日志, 记录的是物理数据页面的修改信息，其redo log是顺序写入redo log file的物理文件中去的
• 归档日志(bin log)
  • 作用
    • 用于复制, 在主从复制中, 从库利用主库上的binlog进行重播，实现主从同步。
    • 用于数据库基于时间点的还原
  • 内容
    • 逻辑格式的日志, 可以简单认为就是执行过的事务中的sql语句
    • 包括sql(增删改)的信息及反向的信息
  • 三种模式
    • Statement
    • Row
    • Mixed