1、ID自增主键

一张表有ID自增主键，当insert到17条记录之后，删除15，16，17的记录，再把MySQL重启，再insert一条记录，这条记录的ID是18还是15？

  • InnoDB : 如果不重启是15，重启后是18，因为InnoDB表将自增主键的最大ID记录到内存中，重启数据库数据库或者对表OPTIMEZE操作，都会使最大ID丢失
  • MyISAM：18，MyISAM将自增主键记录在数据文件中，不会丢失
  • 建议： 不建议物理删除

    2、InnoDB于MyISAM的区别

    | | InnoDB | MyISAM | | :—-: | :—-: | :—-: | | 事务 | 支持 | 不支持 | | 存储限制 | 64TB | 无 | | 锁粒度 | 行锁 | 表锁 | | 外键 | 支持 | 不支持 | | 崩溃恢复 | 支持 | 不支持 |

3、什么是索引？

索引是一种数据结构，数据库除了保存数据本身，还维护了一个满足特定查找算法的数据结构，这种数据结构就是索引。

4、索引的优缺点？

  • 优点：
    • 提高检索效率，降低IO成本
    • 降低数据排序的成本，降低CPU消耗

  • 缺点：

    • 占用存储空间
    • 数据更新需要维护索引，降低更新效率

      5、索引的创建原则

  • 出现在where子句中的列

  • 索引列基数越大，索引效果越好
  • 根据情况，使用复合索引
  • 避免创建过多索引
  • 主键选择较短的数据类型

  • 过长的字符串索引，应该定制一个前缀长度，节省空间

    6、索引使用的注意点

  • where子句中避免使用 !=,<,>，or导致全表扫描

  • 避免like ‘%….’
  • 避免对索引列进行表达式操作，函数操作
  • 注意复合索引的顺序

  • 避免索引列的值进行类型转换，如 varchar 的参数必须要加引号

    7、B-Tree树与B+树之间的区别

  • B-Tree : 每个节点存储key 和 数据data

  • B+Tree：非叶子节点只存储key的信息，数据记录都存放在叶子节点

  • 优化：B+Tree是对B-Tree树的优化，每页的存储空间是有序的，如果data的数据较大将会导致key的数量较小，当存储的数据量较大时会导致B-Tree深度较大增大了I/O次数。B+Tree树非叶子节点只存放key值，data按顺序存放在同一层的叶子节点上，达到降低树高度减少I/O的目的

    8、B-Tree树有哪些索引

  • 聚簇索引：叶子节点存放整条数据

  • 二级索引：叶子节点存放主键

    9、聚簇索引的注意点

  • 最好为自增主键

    • 避免出现页分裂问题
    • 避免数据移动，引起性能消耗
  • 二级索引查询数据要先查询到叶子节点的主键，然后根据主键查询数据
  • 主键ID建议使用整型，每个主键索引的B+Tree节点可以存储更多的key,减少二级索引叶子节点的内存占用

    10、最左匹配原则

    假如索引（a,b,c）那么 a 、（a,b）、（a,b,c）都可以用到索引，带头大哥不能死

    11、事务四种隔离级别

    | 级别 | 定义 | 存在的问题 | | —- | —- | —- | | READ UNCOMMITTED | 未提交的事务对其他事务也可见 | 脏读 | | READ COMMITTED | 一个事务从开始到结束，所做的任何修改对其他事务都不可见 | 不可重复读 | | REPEATABLE READ（MySQL默认级别） | 一个事务中多次读取同样的记录是一致的 | 幻读 | | SERIALIZABLE | 强制事务串行，读取每一行数据都加锁 | 超时和锁竞争 |

12、MVCC

  • 概念：多版本并发控制，用一种用来解决读写冲突的无锁并发控制，是通过保存数据在某个时间点的快照实现的。MVCC只在REPEATABLE READ 和 READ COMMITED两个隔离级别下工作
  • InnoDB的MVCC,通过在每行记录后面保存两个隐藏的列来实现，一列保存行的创建时间(系统版本号)，一个保存行的过期时间

  • 演示增删改查四个操作下MVCC干了什么

    • INSERT : 事务开始时，获取自身版本号,假设 == V1 | 数据记录 | 版本号 | 过期时间 | | —- | —- | —- | | 表记录A | V1 | undefined |

    • UPDATE: 事务开始时，获取自身版本号,假设 == V2 | 数据记录 | 版本号 | 过期时间 | | —- | —- | —- | | 表记录A | V1 | V2 | | 表记录A | V2 | undefined |

    • SELECT （RR级别下）: 事务开始时，获取自身版本号,假设 == V3，在V3开始后事务V4修改了表记录A | 数据记录 | 版本号 | 过期时间 | | —- | —- | —- | | 表记录A | V1 | V4 | | 表记录A | V4 | undefined |

      • InnoDB只查询版本号小于或等于当前版本的记录行（<= V3）
      • 行的过期时间要么未定义，要么大于当前版本( > V3)
      • 这样查询到的就是V1版本，保证了REPEATABLE READ,至于怎么读到V1的版本，是每次更新记录的时候，InnoDB都会保存一条undo log 可以回退到上一个版本，比如原本 a= 4 改为 3，就会记录一条3+1，那么4-3-2-1，只要通过undo log反推就可以获取前几个版本的数据
    • DELETE：事务开始时，获取自身版本号,假设 == V5 | 数据记录 | 版本号 | 过期时间 | | —- | —- | —- | | 表记录A | V1 | V5 |

13、锁的机制

  • Innodb 的行锁是怎么实现的？
    • Innodb 的锁的策略为 next-key 锁，即 record lock + gap lock ，是通过在 index 上加 lock 实现的。
      • 如果 index 为 unique index ，则降级为 record lock 行锁。
      • 如果是普通 index ，则为 next-key lock 。
      • 如果没有 index ，则直接锁住全表，即表锁
  • 操作类型分类：读锁（共享锁），写锁（排他锁）
    • 读锁：多个读锁可以共同访问互不影响
    • 写锁：操作未完成前，会阻塞其他读写操作
  • 粒度分类：表锁和行锁
  • 悲观锁：读取数据时加锁，修改数据也加锁
  • 乐观锁：大多数基本数据版本号实现，为数据库表增加一个version字段，读取的时候当版本号version读出，更新时version+1,如果新的版本号大于数据库内的版本号就更新，否则就判定为过期数据(CAS : Compare And Swap)
  • 死锁：两个或者多个事务相互等待对方已锁定的资源，而彼此都不为协助对方达成操作目而主动释放已锁定的资源，这样的情况就称为死锁，解决方法如下：
    • 减少锁的范围，合理设计索引
    • 设置锁超时时间
    • 锁检查机制 : InnoDB的死锁检测是通过等待图(Wait-For-Graph)的算法实现的，即检查所有在一个锁上等待的事务是否已经成环；如果成环了说明已经出现死锁，根据一定的策略将某个事务回退将环切断而解除死锁
  • 间隙锁：当我们使用范围条件检索数据，并请求共享排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在范围内但不存在的记录，叫做间隙（比如数据库本来存在id为1、3、4、5的记录，然后事务A范围更新1~5的记录，此时如果另一个事务插入id = 2的记录，在事务A调教事务之前会被阻塞）

    14、MySQL查询执行顺序

    | (7) SELECT
    (8) DISTINCT
    (1) FROM
    (3) JOIN
    (2) ON
    (4) WHERE
    (5) GROUP BY
    (6) HAVING
    (9) ORDER BY
    (10) LIMIT | | :—- |

15、sql优化

见MySQL学习二

16、MySQL日志（具体可见一条SQL更新语句如何执行）

  • InnoDB引擎独有的redo log、Service层的binlog

  • 两者的区别：

    • redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
    • redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
    • redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志3,。

      17、binlog几种日志录入格式

  • statment : 每条会修改数据的sql都会记录，如 update A set a = 1 where id in (1,3,5);

    • 优点：减少日志量，节约I/O，提高了性能
    • 缺点：额外记录语句执行时的相关信息，另外 MySQL 的复制像一些特定函数功能，slave 可与 master 上要保持一致会有很多相关问题(如 sleep() 函数，last_insert_id()，以及 user-defined functions(udf) 会出现问题)。
  • row : 会记录针对每行的修改，如上面的批量更新，就会记录三条记录
    • 优点：记录每条修改记录
    • 缺点：产生大量日志，如果存在alter语句，每行都发生了修改那么会产生大量的日志
  • mixedlevel：两种的混合使用
  • 总结：statement占用空间小，传输给slave快，但是没有row可靠

    18、主从复制的流程

  1. Master上的binlog dump将master的binlog event传给slave
  2. slave的I/O线程接受binlog event并写入relay log
  3. slave的SQL线程读取relay log执行

    19、mysqldump 和 xtrabackup

  • mysqldump 原理：在备份 InnoDB 表的时候，加上 –master-data=1 –single-transaction 选项，在事务开始时刻，记录下 binlog pos 点，然后利用 MVCC 来获取一致的数据，由于是一个长事务，在写入和更新量很大的数据库上，将产生非常多的 undo ，显著影响性能，所以要慎用
  • xtrabackup：实际上是物理备份+逻辑备份的组合
    • 在备份 InnoDB 表的时候，它拷贝 ibd 文件，并一刻不停的监视 redo log 的变化，append 到自己的事务日志文件。在拷贝 ibd 文件过程中，ibd文件本身可能被写”花”，这都不是问题，因为在拷贝完成后的第一个 prepare 阶段，xtrabackup 采用类似于 Innodb 崩溃恢复的方法，把数据文件恢复到与日志文件一致的状态，并把未提交的事务回滚。
    • 如果同时需要备份 MyISAM 表以及 InnoDB 表结构等文件，那么就需要用 flush tables with lock 来获得全局锁，开始拷贝这些不再变化的文件，同时获得 binlog 位置，拷贝结束后释放锁，也停止对 redo log 的监视