超全面试汇总——MySQL

数据库三大范式

• 第一范式：原子性，第一范式就是无重复的列，字段不可分。是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即每一个属性都是原子的，不能再分，也可以理解为不能表中套表。第一范式（1NF）是对关系模式的基本要求
• 第二范式：唯一性，要求每个非主属性完全依赖于主键，不存在对主键的部分函数依赖。
  • 学号不是主键，学号能决定姓名，年龄，地址，但是凭学号这一项，是决定不了成绩这一项，所以这张表的主键是(学号，课程号)
• 第三范式：不存在属性对主键的传递依赖
  • 有一员工信息表，属性分别是员工号，工资级别，工资。这张表的主键是员工号，一旦员工号确定了，工资级别也确定了，那么工资也就确定了。但是工资对员工号存在传递依赖

数据库四大原则 ACID

• 原子性 一个事务要么全部执行,要么不执行

• 一致性 事务开始之前和事务结束后，数据库的完整性约束没有被破坏，比如A向B转账，不可能A扣了钱，B却没收到。

• 隔离性 多个并发事务的执行互不干扰，A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

  • 脏读：A会话读到了B会话未提交的数据。
  • 不可重复读：一个事务范围内的两个相同查询返回了不同数据。A会话两次查询,查询的结果记录数是一致的,但是记录本身不一致。B会话在两次查询之间发生update语句。
  • 幻读: A会话两次查询,查询的结果记录数是不一致的。 B会话在两次查询之间发生insert delete语句
  • 事务隔离级别
    • RU（READ UNCOMMITTED）：读未提交 会产生脏读、不可重复读、幻读
    • RC（READ COOMITTED）：读已提交 会产生不可重复读、幻读
    • RR（REPEATBLE READ）：可重复读 MySQL的RR级别下，脏读问题不存在 可解决幻读问题
    • Serializable 不存在事务并发问题，但是效率不高，因为读写都加锁

• 持久性 事务执行成功后，该事务对数据库的更改是持久保存在数据库中的

• 无法保证原子性会怎么样：

  • A账户减去50元，而B账户增加50元操作失败。系统将无故丢失50元

• 如果无法保证一致性会怎么样？

• 例一:A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须大于0。

  • 例二:A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的余额必须不变。

• 如果无法保证隔离性会怎么样？

  • 假设A账户有200元，B账户0元。A账户往B账户转账两次，金额为50元，分别在两个事务中执行。如果无法保证隔离性，A可能就会出现扣款两次的情形，而B只加款一次，凭空消失了50元，依然出现了数据不一致的情形！

• 如果无法保证持久性会怎么样？

  • 在MySQL中，为了解决CPU和磁盘速度不一致问题，MySQL是将磁盘上的数据加载到内存，对内存进行操作，然后再回写磁盘。好，假设此时宕机了，在内存中修改的数据全部丢失了，持久性就无法保证。

数据库如何保证一致性？

• 从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。
• 从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

数据库如何保证原子性？undo log

• 主要是利用 Innodb 的undo log。undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的 SQL语句，他需要记录你要回滚的相应日志信息。例如

  • 当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
  • 当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
  • 当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

• undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

数据库如何保证持久性？redo log

• 主要是利用Innodb的redo log。重写日志， 正如之前说的，MySQL是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再写回到磁盘上。如果此时突然宕机，内存中的数据就会丢失。怎么解决这个问题？简单啊，事务提交前直接把数据写入磁盘就行啊。这么做有什么问题？

  • 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
  • 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

• 于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

• 采用redo log的好处？其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下：

  • redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
  • redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

数据库隔离级别

• 未提交读 ，事务中发生了修改，即使没有提交，其他事务也是可见的，比如对于一个数A原来50修改为100，但是我还没有提交修改，另一个事务看到这个修改，而这个时候原事务发生了回滚，这时候A还是50，但是另一个事务看到的A是100.可能会导致脏读、幻读或不可重复读
• 提交读 ，对于一个事务从开始直到提交之前，所做的任何修改是其他事务不可见的，举例就是对于一个数A原来是50，然后提交修改成100，这个时候另一个事务在A提交修改之前，读取的A是50，刚读取完，A就被修改成100，这个时候另一个事务再进行读取发现A就突然变成100了；可以阻止脏读，但是幻读或不可重复读仍有可能发生
• 重复读 ，就是对一个记录读取多次的记录是相同的，比如对于一个数A读取的话一直是A，前后两次读取的A是一致的；可以阻止脏读和不可重复读，但幻读仍有可能发生
• **可串行化读 **，在并发情况下，和串行化的读取的结果是一致的，没有什么不同，比如不会发生脏读和幻读；该级别可以防止脏读、不可重复读以及幻读

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED 未提交读	√	√	√
READ-COMMITTED 提交读	×	√	√
REPEATABLE-READ 重复读	×	×	√
SERIALIZABLE 可串行化读	×	×	×

• MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

  • 这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别 下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以 说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要 求，即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。
  • 因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内 容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

• InnoDB 存储引擎在分布式事务 的情况下一般会用到SERIALIZABLE**(可串行化)隔离级别**。

并发事务会带来哪些问题

• 脏读：A会话读到了B会话未提交的数据。是脏数据了。

• 不可重复读：：一个事务范围内的两个相同查询返回了不同数据。A会话两次查询,查询的结果记录数是一致的,但是记录本身不一致。B会话在两次查询之间发生update语句。

• 幻读：A会话两次查询,查询的结果记录数是不一致的。 B会话在两次查询之间发生insert delete语句。

• 丢弃修改：两个写事务T1 T2同时对A=0进行递增操作，结果T2覆盖T1，导致最终结果是1 而不是2，事务被覆盖

数据库中的锁

• 行锁，就是按照行的粒度对数据进行锁定。锁定力度小，发生锁冲突概率低，可以实现的并发度高，但是对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

  • 会出现死锁，发生锁冲突几率低，并发高，速度慢

  • 在MySQL的InnoDB引擎支持行锁，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

  • 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

  • 两个事务不能锁同一个索引。

  • insert，delete，update在事务中都会自动默认加上排它锁

  • 行锁的适用场景：

    A用户消费，service层先查询该用户的账户余额，若余额足够，则进行后续的扣款操作；这种情况查询的时候应该对该记录进行加锁。

    否则，B用户在A用户查询后消费前先一步将A用户账号上的钱转走，而此时A用户已经进行了用户余额是否足够的判断，则可能会出现余额已经不足但却扣款成功的情况。

    为了避免此情况，需要在A用户操作该记录的时候进行for update加锁

• 表锁，就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。

  • 不会出现死锁，发生锁冲突几率高，并发低，速度快
  • MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁
  • MySQL的表级锁有两种模式：表共享读锁和表独占写锁。
  • 对MyISAM表进行操作，读锁会阻塞写，写锁会阻塞读和写
  • MyISAM不适合做写为主表的引擎,因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

• 页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

• 当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级，锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了

• 共享锁，也叫读锁或 S 锁，共享锁锁定的资源可以被其他用户读取，但不能修改。在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

-- 加共享锁
LOCK TABLE product_comment READ;
-- 解共享锁
UNLOCK TABLE product_comment;
-- 加行锁
SELECT comment_id, product_id, comment_text, user_id FROM product_comment 
WHERE user id = 912178 LOCK IN SHARE MODE;

• 排它锁，也叫独占锁、写锁或 X 锁，排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。

-- 加写锁
LOCK TABLE product_comment WRITE;
-- 解锁
UNLOCK TABLE product_comment;
-- 加航锁
SELECT comment_id, product_id, comment_text, user_id FROM product_comment 
WHERE user id = 912178 FOR UPDATE;

• 当我们对数据进行更新的时候，也就是INSERT、DELETE或者UPDATE的时候，数据库也会自动使用排它锁，防止其他事务对该数据行进行操作。

• 乐观锁 (Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。

  • 先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。
  • 乐观锁的版本号机制。在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE … SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。
  • 乐观锁的时间戳机制。时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。
  • 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。

• 悲观锁（Pessimistic Locking），也是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

  • 悲观锁，先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。
  • 悲观锁适合写操作多的场景，因为写的操作具有排它性**。采用悲观锁的方式，可以在数据库层面**阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

存储引擎

• MyISAM 和 InnoDB 是形容表的 不是数据库的
• InnoDB
  • 是事务型的，可以使用commit和rollback语句
  • 支持表级锁和行级锁，支持外键
  • 四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读
  • 主索引是聚簇索引，B+树叶子存储数据，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
  • 默认存储引擎，5.5版本之后
  • 不存储总行数
  • 页作为磁盘和内存之间交互的基本单位，一页的数据为16kb
  • 应用场景：事务性、安全性操作较多的情况下，表更新和查询都相当的频繁， 大量的INSERT或UPDATE
  • 支持真正的在线热备份
• MyISAM
  • 不提供事务的支持
  • 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁（共享锁锁定的资源可以被其他用户读取，但不能修改，保证数据在读取时不被修改），写入时则对表加排它锁（排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改）
  • 不支持外键
  • 非聚簇索引，B+树叶子存储指向数据文件的指针
  • 存储表的总行数
  • 设计简单，数据以紧密格式存储，支持压缩表和空间数据索引
  • 应用场景：执行大量的select，插入不频繁
• Memory
  • 数据存储内存，表结构存储索引，访问效率高
  • 服务关闭，表中数据丢失
  • 应用场景：Mysql内存表做数据缓存

为什么使用索引

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

如何选取合适的索引

• 非空字段：将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描

• 取值离散大的字段：变量各个取值之间的差异程度

• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

• 使用频繁

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

MySQL中有四种索引类型

• FULLTEXT ：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
• HASH ：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
• BTREE ：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
• RTREE ：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

MySQL中有哪些索引？有什么特点？

• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 全文索引：对文本的内容进行分词，进行搜索
• 索引合并：使用多个单列索引组合搜索
• 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
• 聚集（聚簇）索引（Innodb）的叶节点就是数据节点，而非聚集（非聚簇）索引(MyisAM)的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。
• 聚合索引和非聚合索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。
  • 聚合索引就是按照拼音查询，非聚合索引就是按照偏旁等来进行查询。
  • 正文内容本身就是一种按照一定规则排列的目录称为"聚合索引" 就是按照拼音排好的
  • 这种目录纯粹是目录，正文纯粹是正文的排序方式称为"非聚合索引"。"张"字，我们可以看到在查部首之后的检字表中"张"的页码是672页，检字表中"张"的上面是"驰"字，但页码却是63 页，"张"的下面是"弩"字，页面是390页。

MyISAM和InnoDB实现B树索引方式的区别是什么？

• MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”。MyISAM 文件存储有三个文件 表信息frm 表数据MYD 表索引 MYI索引文件和数据文件是分离的 非聚集
• InnoDB，其数据文件本身就是索引文件，相比MyISAM索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。
• 在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

MySQL优化索引

• 不要在列上使用函数和计算，这将导致索引失效而进行全表扫描。

• 尽量避免使用 != 或 not in或 <> 等否定操作符，因为这会导致索引失效而进行全表扫描**

• 尽量避免在 where 子句中使用 or 来连接条件

• 多个单列索引并不是最佳选择 复合索引

• 复合索引的最左前缀原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用

• 覆盖索引的好处，如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。

• 范围查询对多列查询的影响，查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找

• 索引不会包含有NULL值的列 ，只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的

• 隐式转换的影响, date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换导致索引失效而进行全表扫描

• like 语句的索引失效问题，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，会全表扫描

  • select * from news where year(publish_time) < 2017
    为了使用索引，防止执行全表扫描，可以进行改造。
    select * from news where publish_time < '2017-01-01'
    还有一个建议，不要在列上进行运算，这也将导致索引失效而进行全表扫描。
    select * from news where id / 100 = 1
    为了使用索引，防止执行全表扫描，可以进行改造。
    select * from news where id = 1 * 100
    
    因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。
    假设，有两个单列索引，分别为 news_year_idx(news_year) 和 news_month_idx(news_month)。
    现在，有一个场景需要针对资讯的年份和月份进行查询，那么，SQL 语句可以写成：
    select * from news where news_year = 2017 and news_month = 1
    事实上，MySQL 只能使用一个单列索引。为了提高性能，
    可以使用复合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。
    
    如果不是按照索引的最左列开始查找，则无法使用索引。假设，有一个场景只需要针对资讯的月份进行查询，那么，SQL 语句可以写成：
    select * from news where news_month = 1
    此时，无法使用 news_year_month_idx(news_year, news_month) 索引，
    因为遵守“最左前缀”原则，在查询条件中没有使用复合索引的第一个字段，索引是不会被使用的。
    
    假设有一个场景需要查询本周发布的资讯文章，其中的条件是必须是启用状态，且发布时间在这周内。
    select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1
    这种情况下，因为范围查询对多列查询的影响，将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。
    换句话说，news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 。
    
    对于这种情况，我的建议：对于范围查询，务必要注意它带来的副作用，并且尽量少用范围查询，可以通过曲线救国的方式满足业务场景。例如，上面案例的需求是查询本周发布的资讯文章，
    因此可以创建一个news_weekth 字段用来存储资讯文章的周信息，使得范围查询变成普通的查询
    select * from news where news_weekth = 1 and enable = 1
    
    当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。
    select * from news where date_str = 201701
    因此，要谨记隐式转换的危害，时刻注意通过同类型进行比较
    

Innodb为什么要用自增id作为主键

• 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
• 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。

文件索引和数据库索引为什么使用B+树 不用hash表和B树?

• 索引，为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，构造一个多阶的B类树，保证层数（树的高度）尽量的少，磁盘的I/O操作也少一些
• B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
• B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确
• **B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。**B+树很好利用局部性原理
• Hash 表不好的地方 重要
  • 利用Hash需要把数据全部加载到内存中，如果数据量大，是一件很消耗内存的事，而采用B+树，是基于按照节点分段加载，由此减少内存消耗。
  • 对于唯一查找（查找一个值），Hash确实更快，但数据库中经常查询多条数据，这时候由于B+数据的有序性，与叶子节点又有链表相连，他的查询效率会比Hash快的多
• 方便扫库树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。
• 局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入

B-树 以及 B+树

• 都知道数据库索引采用B+树而不是B树，原因也有很多，主要原因是什么？

  • B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

• B-树和B+树的区别

  • B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
  • **B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。**B+树很好利用局部性原理
  • B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

• 表中是否可以没有聚集索引

  • 必须建主键，否则内部会自己构建，遍历每列数据找所有字段的值都不一样的作为聚集索引，若找不到，底层会构建一列隐藏列维护，不建的话效率太低

• 为什么推荐使用整形的自增型主键

  • 整形 比较方便比较 不用uuid
  • 自增型 能进行范围性查找 底层结点维护相邻结点地址 且 底层要排好序 如果不是自增的 是不是会进行分裂和树平衡

• B-树 （MongDB）

  • B-树 是一棵自平衡的搜索树，允许每个节点有更多的子节点，专门为外部存储器设计的，为了减少IO次数，读取和写入大块数据有良好的性能
  • 多叉的好处非常明显，有效的降低了B-树的高度，为底数很大的 log n，底数大小与节点的子节点数目有关，一般一棵B-树的高度在 3 层左右。层数低，每个节点区确定的范围更精确，范围缩小的速度越快。
  • 索引的原理其实是不断的缩小查找范围，B-树每次将范围分割为多个区间，区间越多，定位数据越快越精确，查字典
  • B-树的 key 和 data 是聚合在一起的,每个 key 值紧跟着 data 域
  • 叶子结点有相同深度，叶节点的指针为空，所有索引元素不重复，节点中的数据索引从左到右递增
  • 

• B+树 （Mysql）多路平衡树

  • 在B+树中，非叶子结点不存储data，只存储索引，可以放更多的索引，真正的 key 和 data 存储在叶子节点上 。

  • 叶子结点包含所有索引字段

  • 叶子结点用指针连接，提高区间访问的性能

  • 聚集索引 就是 叶子结点包含了完整的数据记录 效率比非聚集高-

  • innodb也存在非聚集索引，就是二级索引

  • 表中有且只有一个聚集索引

  • 在这里插入图片描述

  • n 个 key 值的节点指针域为 n 而不是 n+1。

  • 增加B+树的路数可以降低树的高度，那么无限增加树的路数是不是可以有最优的查找效率？不一定能一次性加载到内存中。有序数组没法一次性加载进内存，这时候B+树的多路存储威力就出来了，可以每次加载B+树的一个结点，然后一步步往下找

• 一页的数据为16kb，索引大小8b，指针6b，一层能放1600/14 最后一层 数据1k 能放16 高度为三的b+树放完索引 能存1170x1170x16

• MyISAM 和 InnoDB 是形容表的 不是数据库的

• MyISAM 文件存储有三个文件 表信息frm 表数据MYD 表索引 MYI索引文件和数据文件是分离的 非聚集

• InnoDB 表信息 frm和**表索引 ibd **表数据文件就是按照b+树组织的一个索引结构文件

• hash

  • 对索引的key进行一次hash可以定位数据存储的位置
  • 仅能满足 = 和 in 不支持范围查询
  • hash冲突

• 联合索引的底层存储结构长什么样

  • 排好序的B+树 （name, age,position）按照字典序 从前到后索引字段比较
  • 

MongoDB 用B-树，Mysql用B+树

• MongoDB 是一种 nosql，也存储在磁盘上，被设计用在 数据模型简单，性能要求高的场合，尽可能少的磁盘 IO 是提高性能的有效手段。MongoDB 是聚合型数据库，它使用类 Json 格式保存数据，而 B-树恰好 key 和 data 域聚合在一起。

• Mysql 是一种关系型数据库，区间访问是常见的一种情况，B+树由于数据全部存储在叶子节点，并且通过指针串在一起，这样就很容易的进行区间遍历甚至全部遍历。B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

数据库中的主键、超键、候选键、外键是什么？（很棒）

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键
• 候选键：不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！
• 主键：用户选作元组标识的一个候选键程序主键
• 外键：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

举例：

学号	姓名	性别	年龄	系别	专业
20020612	李辉	男	20	计算机	软件开发
20060613	张明	男	18	计算机	软件开发
20060614	王小玉	女	19	物理	力学
20060615	李淑华	女	17	生物	动物学
20060616	赵静	男	21	化学	食品化学
20060617	赵静	女	20	生物	植物学

• 超键：于是我们从例子中可以发现 学号是标识学生实体的唯一标识。那么该元组的超键就为学号。除此之外我们还可以把它跟其他属性组合起来，比如：(学号，性别)，(学号，年龄)
• 候选键：根据例子可知，学号是一个可以唯一标识元组的唯一标识，因此学号是一个候选键，实际上，候选键是超键的子集，比如 （学号，年龄）是超键，但是它不是候选键。因为它还有了额外的属性。
• 主键：简单的说，例子中的元组的候选键为学号，但是我们选定他作为该元组的唯一标识，那么学号就为主键。
• 外键是相对于主键的，比如在学生记录里，主键为学号，在成绩单表中也有学号字段，因此学号为成绩单表的外键，为学生表的主键。
• 主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

如何查询一条sql语句

• 连接器：管理连接，权限验证
• 查询缓存：命中缓存则直接返回
• 解析器（分析器）:将客户端传递的SQL文本，进行词法解析和与语法解析，并通过预处理器，检测是否合法，形成解析树
• 优化器:MySQL里使用的是基于成本模型的优化器，优化处理包括：
  • 选择合适的执行计划(索引的选择)
  • 一个语句有多表关联（join）时，决定各个表的连接顺序，以哪个表为基准表
• 执行器:先判断是否有权限，再调用存储引擎接口，提取数据

MySQL的内部构造吗？一般可以分为哪两个部分

• 服务层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认的存储引擎。

Mysql 磁盘文件

• 重做日志文件(redolog) ib_logfile0 ib_logfile1
• 回滚日志(undo log) ibdata1
• InnoDB数据文件
  • ibd文件，使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件
  • ibdata1文件，使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件
• 一个表空间–>多个数据段–>多个区–>多个页(B+树的叶子节点)–>行记录
• 一个区由64个连续的页组成，一个区的大小=1M=64个页（16k）

MySQL是如何实现事务的

• 原子性，持久性和一致性主要是通过redo log、undo log、Force Log at Commit和Double Write机制来完成的。
• redo log用于在崩溃时恢复数据
• undo log用于对事务回滚时进行撤销，也会用于隔离性的多版本控制。
• Force Log at Commit机制保证事务提交后redo log日志都已经持久化。
• Double Write机制用来提高数据库的可靠性，用来解决脏页落盘时部分写失效问题。

MySQL中为什么要有事务回滚机制？

• 而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。当事务已经被提交之后，就无法再次回滚了。
• 回滚日志作用：
  • 能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息
  • 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，
  • 这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

数据库结构优化的手段？

• 范式优化：比如消除冗余（节省空间。。）
• 反范式优化：比如适当加冗余等（减少join）
• 限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
• 读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；
• 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。可按月自动建表分区。

数据库为什么要进行分库和分表（数据拆分）呢

• 分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。

• 通过分表，可以减少数据库的单表负担，不同的表上的数据量少了，可以很大的缓解表锁的问题。分表策略可以归纳为垂直拆分和水平拆分:

• 拆分数据

  • 案例：简单购物系统暂设涉及如下表：

    1.产品表（数据量10w，稳定）

    2.订单表（数据量200w，且有增长趋势）

    3.用户表 （数据量100w，且有增长趋势）

    以 MySQL 为例讲述下水平拆分和垂直拆分，MySQL能容忍的数量级在百万静态数据可以到千万

  • 水平分表：

    • 解决问题：单表中数据量增长出现的压力
    • 不解决问题：表与表之间的io争夺
    • 方案：用户表 通过性别拆分为男用户表和女用户表，订单表 通过已完成和完成中拆分为已完成订单和未完成订单， 未完成订单放一个server上，已完成订单男用户表放一个server上，女用户表放一个server上(女的爱购物 哈哈)。
    • 取模分表就属于随机分表，而时间维度分表则属于连续分表。

  • 垂直拆分

    • 解决问题：表与表之间的io竞争
    • 不解决问题：单表中数据量增长出现的压力
    • 把产品表和用户表放到一个server上 订单表单独放到一个server上
    • 将不常用的字段单独拆分到另外一张扩展表.
    • 将大文本的字段单独拆分到另外一张扩展表, 和不经常修改的字段放在同一张表中
    • 将经常改变的字段放在另一张表中。
    • 对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

• 库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

MySQL执行顺序

• from… where…group by… having… select … over…order by… limit：

order by 和 group by 的区别

• order by 就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。
• group by是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。注意：聚合函数是—sum()、count()、avg()等都是“聚合函数”，在group by 中用count（*） 获取条数 ，获取的不是总条数，而是每个组的条数，
• where后面不能接聚合函数，聚合函数是针对结果集进行的，但是where条件并不是在查询出结果集之后运行，那么只能用另外一种方法：havaing。having就是专门为了应付这种情况而发明出来的

having 和where的用法区别

• having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)
• where肯定在group by 之前。
• where后的条件表达式里不允许使用聚合函数，而having可以。

简述sql中inner join、left join、right join 和cross join

• inner join(内连接)：只返回两个表中联结字段相等的行，只有两个元素表相匹配的才能在结果集中显示
• left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录
• right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录
• corss join(交叉连接)：生成来自多个表的行的笛卡尔乘积，将包括左表中的行与右表中的行的组合

drop，delete，truncate区别

• DELETE
  • 每次从表中删除一行，执行Delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除，会触发这个表上所有的delete触发器
  • 不会返回数据占用的空间
  • 可以对 table 和 view操作
  • delete语句为DML（Data Manipulation Language),会写到日志中
• TRUNCATE
  • 一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。不用提交，不能回滚
  • 返还数据所占空间
  • 只能对table操作
  • 在功能上与不带 WHERE 子句的 DELETE 语句相同，如果和事务无关可用truncate
  • 再次插入时自增id又从1开始
  • truncate是DDL（Data Define Language),操作立即生效，原数据不放到 rollback segment中
• DROP
  • 删除整个表 包括表结构、约束、索引和数据 ，不用提交，不能回滚
  • 表所占用的空间全部释放
  • drop是DDL（Data Define Language),操作立即生效，原数据不放到 rollback segment中

视图和游标

• 视图是一种虚拟的表，视图只包含使用时动态检索数据的查询；不包含任何列或数据。视图创建后，可以使用与表相同的方式利用它们。
• 使用视图可以简化复杂的 sql 操作，隐藏具体的细节，保护数据，并不用于更新
• 视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
• 游标是对查询出来的结果集作为一个单元来有效的处理。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

一天五万条以上的增量，预计运维三年，你有哪些优化手段？

• 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
• 选择合适的表字段数据类型和存储引擎，适当的添加索引。
• MySQL库主从读写分离。
• 找规律分表，减少单表中的数据量提高查询速度。
• 添加缓存机制，比如Memcached，Apc等。
• 不经常改动的页面，生成静态页面。
• 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

不是SQL查询的联接算法（D ）

A. Nested loop join 嵌套循环联接

B. Hash join Hash连接

C. Sort merge join 合并连接

D. Hash loop join

用法

• SQL中的NOW()和CURRENT_DATE()两个函数有什么区别
  • NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。CURRENT_DATE（）仅显示当前年份，月份和日期
• MySQL中CHAR和VARCHAR的区别有哪些？
  • char的长度是不可变的，用空格填充到指定长度大小，而varchar的长度是可变的。
  • char的存取速度还是要比varchar要快得多
  • char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
  • varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。