数据库

1 DDL、DML、DCL 分别指什么
ans: DML, Data Manipulation Language, 对数据进行操作的语句，如 select, insert, update
DDL，Data Definition Language，定义或改变表结构、数据类型、表之间的链接和约束等工作，如 create、alter、drop
DCL，Data Control Language，用来设置或更改数据库用户或角色权限的语句，如 GRANT, COMMIT, ROLLBACK
参考：https://blog.csdn.net/level_level/article/details/4248685

2 数据库的几大范式
ans: 第一范式（1NF）： 每个属性必须是原子项目
第二范式（2NF）：满足第一范式，且有唯一主键在表中保证每一行都具有唯一性。
第三范式（3NF）：满足第一、二范式，且每一列数据都和主键直接相关，而不能间接相关。
参考：http://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html

3 说说分库与分表设计，分库与分表带来的分布式困境与对应之策
ans：对于大型互联网应用，数据库的单表数据量可能达到千万级甚至亿级，此时即使采用主从模式的架构压力也会比较大，而且主从模式也只能提升读性能，写数据仍然受到master性能的限制。
通常会采用分库分表的手段优化这个问题，分表是将数据从逻辑上横向的拆分成多张表，这样可以减少每张表的记录条数，减少检索时间；分库是在物理层面将表横向拆分，每个库都有自己的master，从而提升写性能。分库分表也可以结合使用。
但是分库分表也带来几个问题：
（1）数据迁移与扩容，连续分表可能导致热点问题，而随机分表在扩展时又涉及到数据迁移问题。
（2）表关联问题，分库分表情况下，多表联合查询可能导致跨库关联，最好将数据放在程序中品种，或者采用反范式设计。
（3）分页与排序问题，与表关联同样存在跨库的问题。
（4）分布式事务，分库分表场景下如何保证数据的一致性就成为一个难题。目前分布式事务并没有很好的解决方案，难以满足数据强一致性，一般情况下，使存储数据尽可能达到用户一致，保证系统经过一段较短的时间的自我恢复和修正，数据最终达到一致。
（5）分布式全局唯一ID，在单库单表的情况下，直接使用数据库自增特性来生成主键ID已经不再适合，需要使用全局唯一 ID。
一般在项目一开始不采用分库与分表设计，而是随着业务的增长，在无法继续优化的情况下，再考虑分库与分表提高系统的性能。

4 存储引擎的 InnoDB 与 MyISAM 区别、优缺点、使用场景
ans: 区别：

• InnoDB支持事务、外部键等高级，MyISAM不支持
• MyISAM执行数度比InnoDB类型更快
• InnoDB不支持FULLTEXT类型的索引，不保存表的行数。因此在执行count(*) 时需要全表扫描
• 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
• 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
• InnoDB支持行锁。
  优缺点：
• MyISAM，查询数据相对较快，适合大量的select，可以全文索引；但不支持事务，不支持外键，并发量较小，不适合大量update
• InnoDB，支持事务，支持外键，并发量较大，适合大量update；查询数据相对较慢，不适合大量的select
  使用场景：MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。
  参考：https://www.cnblogs.com/wt645631686/p/6868678.html

5 SQL关键字的执行顺序
ans: FROM -> ON -> OUTER -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> TOP
参考：https://www.designcise.com/web/tutorial/what-is-the-order-of-execution-of-an-sql-query

6 explain 命令，包含哪些列，Type列有哪几种值。
ans: EXPLAIN命令是查看优化器如何决定执行查询的主要方法。
列信息包括：
（1）id，包含一组数字，表示查询中执行select子句或操作表的顺序；如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；id如果相同，可以认为是一组，从上往下顺序执行。
（2）select_type，查询中每个select子句的类型

• SIMPLE，查询中不包含子查询或者UNION
• 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
• 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
• 在FROM列表中包含的子查询被标记为：DERIVED（衍生）
• 若第二个SELECT出现在UNION之后，则被标记为UNION；
• 从UNION表获取结果的SELECT被标记为：UNION RESULT
  （3）type，表示MySQL在表中找到所需行的方式，又称“访问类型”，ALL->index->range->ref->eq_ref->const->system->NULL，性能依次提升。
  （4）possible_keys，指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
  （5）key，显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
  （6）key_len，表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
  （7）ref，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
  （8）rows，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
  （9）Extra，包含不适合在其他列中显示但十分重要的额外信息。
• Using index，该值表示相应的select操作中使用了覆盖索引（Covering Index）
• Using where 表示mysql服务器将在存储引擎检索行后再进行过滤。
• Using temporary 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
• Using filesort MySQL中无法利用索引完成的排序操作称为“文件排序”。
• Using join buffer 强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
• Impossible where 强调了where语句会导致没有符合条件的行。
• Select tables optimized away 这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。
• Index merges 当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。Using sort_union(...)、Using union(...)、Using intersect(...)
  参考：http://www.cnblogs.com/gomysql/p/3720123.html

7 SQL怎么进行优化
参考：https://blog.csdn.net/jie_liang/article/details/77340905

8 limit 20000 加载很慢怎么解决
ans: 记录上一次检索的最大值，加入本次检索的where条件中，利用索引快速定位到开始检索的位置。

9 有哪几种索引，什么时候该（不该）建索引，
ans: 主键索引（Primary Key）、唯一索引（UNIQUE index）、普通索引（index）、全文索引(FullText index)

• 当需要快速查找匹配where条件的时候，为了尽量避免全表检索，应当建立索引。
• 即使存在索引，但有些情况下数据库可能无法使用：带有or条件且部分条件无索引；存在组合索引但未使用第一个字段；like以“%”开头；对索引字段有数据计算或函数调用
• 不适合建立索引的几种场景：
  （1） 数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引
  （2）频繁更新的字段不要使用索引
  （3）字段不在where语句出现时不要添加索引, 或者含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引
  （4）where 子句里对索引列使用不等于（<>），使用索引效果一般
  参考：http://www.cnblogs.com/tommy-huang/p/4483684.html
  https://blog.csdn.net/kaka1121/article/details/53395628

10 聚集索引与非聚集索引的区别
ans: 聚集索引：索引中键值的逻辑顺序决定了表中相应行的物理顺序（索引中的数据物理存放地址和索引的顺序是一致的）
非聚集索引：索引的逻辑顺序与磁盘上的物理存储顺序不同。

• 对于MySQL的InnoDB引擎，如果一个主键被定义了，那么这个主键就是作为聚集索引
• 如果没有，那么该表的第一个唯一非空索引被作为聚集索引
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。
  参考：https://www.cnblogs.com/duzhentong/p/8639223.html

11 Mysql索引的数据结构，为什么要用 B+tree 作为 MySql
ans: B tree 是多叉查找树，对于一颗阶数为m的B-tree：

• 根结点要么是一个叶子结点，要么是一个至少有2个子结点的父结点
• 根结点的key个数至少1个，至多m-1个
• 非根结点key个数至少 m/2（向上取整）-1 个，至多m-1个
• 所有叶节点具有相同的深度
• 结点最左边的指针指向的子结点的key全部小于该结点，右边大于
  B+tree 是B tree的变种，主要区别在于：
• 每个节点如果有n个子结点，就有n个key
• 每个非叶子结点不保存数据，只用来索引，所有数据都保存在叶子节点。
• 带有顺序访问指针的B+Tree 还会在叶子结点维护一个指向相邻叶子节点的指针，这就形成了顺序访问的链表，目的是为了提高区间访问的性能。
  B tree适用于数据库存取和查找文件，这是由于索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。因此每次查询就需要从硬盘IO，而IO的性能是无法与内存相比的，因此查询硬盘次数越少，效率就越高。而B tree的复杂度为O(logdN)，树的高度比较低，适合用于这类场景。B+树由于其内部结点相对B树更小，一次读入内存的索引信息更多，所以更适合实际应用中操作系统的文件索引和数据库索引。
  MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，索引文件和数据文件是分离的。这种索引也被称为“非聚集索引”
  InnoDB的数据文件本身就是索引文件，这个索引的key是数据表的主键，即“聚集索引”。且InnoDB的辅助索引data域存储相应记录主键的值而不是地址，也就是说使用辅助索引检索数据后，还需要用主键再去查找需要的数据。

12 哈希索引，自适应哈希索引（AHI）
ans：哈希索引，就是采用哈希算法把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。但问题在于哈希索引对等值查询的速度很快，非等值查询就无能为力了，且不能利用索引完成排序。在有大量重复键值情况下，还会存在哈希碰撞问题。
自适应哈希： InnoDB存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，InnoDB就会使用索引键的前缀建立一个哈希索引。将索引值转换为一种指针，便于直接访问，带来速度的提升。
参考：https://www.cnblogs.com/bonelee/p/6224698.html
http://www.notedeep.com/note/38/page/221

13 mysql 最大链接数
ans：mysql可以自定义最大链接上限 max_connections，默认值是151
参考：https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html

14 数据库事务特性
ans: ACID:

• 原子性（Atomicity）：即事务是不可分割的最小工作单元，事务内的操作要么全做，要么全不做；
• 一致性（Consistency）：在事务执行前数据库的数据处于正确的状态，而事务执行完成后数据库的数据还是应该处于正确的状态，即数据完整性约束没有被破坏；如银行转帐，A转帐给B，必须保证A的钱一定转给B，一定不会出现A的钱转了但B没收到，否则数据库的数据就处于不一致（不正确）的状态。
• 隔离性（Isolation）：并发事务执行之间互不影响，在一个事务内部的操作对其他事务是不产生影响，这需要事务隔离级别来指定隔离性；
• 持久性（Durability）：事务一旦执行成功，它对数据库的数据的改变必须是永久的，不会因比如遇到系统故障或断电造成数据不一致或丢失。

15 MySQL如何保持事务隔离性
ans: MySQL的InnoDB引擎支持表锁、行锁，默认隔离级别是可重复读 （ REPEATABLE READ）
（1）MySQL实现的锁种类：

• InnoDB实现了标准的底层行级锁，共享锁（S）& 独占锁（X）
• 意向锁（Intention Locks），意图共享锁（IS）& 意图独占锁（IX），意图锁是表级锁，指示事务稍后对表中的行需要哪种类型的锁(共享或独占)，申请顺序必须为 IS -> S, IX -> X。
  使用意图锁定的主要目的，是为了向后到的锁请求显示当前表已被锁定了一行，或者准备锁定表中的一行。
• 记录锁，是索引记录上的锁，即使表没有定义任何索引。InnoDB会创建一个隐藏的索引来锁定记录。
• 间隙锁，对索引记录之间的间隙的锁，间隙可能跨越单个索引值、多个索引值，甚至是空的。使用间隙锁的目的是防止其他事务插入到已经锁定的间隙当中。
• next-key，记录锁和索引记录前的间隙上的间隙锁的组合。

（2）四种隔离级别&实现原理：

• READ UNCOMMITTED（未提交读）：事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读），所有操作都不加锁。
• READ_COMMITTED（提交读）：一个事务提交后才能被其他事务读取到。
  对于读操作，每次都会读最新版本的快照；
  对于update、delete等写操作仅锁定匹配的索引记录，而不锁定记录前的间隙，因此允许在锁定的记录旁边自由插入新记录。所以会造成幻读、不可重复读。
• REPEATABLE_READ（可重复读）：保证在一个事务中多次读取同一个数据时，其值都和事务开始时候的内容是一致。这是InnoDB的默认隔离级别。
  读操作每次读事务开始前版本的快找，写操作如果带唯一搜索条件且使用唯一索引，就只锁定找到的索引记录，而不锁定记录前的间隙。
  对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或next-key锁来阻止其他会话插入到范围覆盖的间隙中。会出现幻读（在 SQL 标准中，RR 是无法避免幻读问题的，但是 InnoDB 实现的 RR 避免了幻读问题）
• SERIALIZABLE（序列化）：读用共享锁，写用互斥锁，读锁和写锁互斥，代价最高最可靠的隔离级别（能防止脏读、不可重复读、幻读）。

（3）关于几种隔离级别下存在的问题

• 脏读：如果一个事务对数据进行了更新，尚未提交的情况下另一个事务“看到了”这个未提交的结果，这可能造成的问题是，当事务回滚，那么第二个事务看到的数据就是脏数据。
• 不可重复读：是指在一个事务过程中，对同一个数据进行多次读取，每次的结果都不同，如事务1第一次读取数据，事务2尚未更新，而第二次读物数据时事务2已经更新了数据。
• 幻读：幻读针对的是多笔记录，与不可重复读类似，都是在一个事务过程中多次读取结果不同，幻读针对的是结果集有差异，可能多或少了一些记录。
  需要注意的是，RR并没有完全解决幻读，参考（https://segmentfault.com/a/1190000012669504），完全解决幻读仍然需要采用加锁的方式，实际上是将操作序列化了。
  参考：https://zxytech.com/mysql/mysql5/storage-engines.html#innodb-transaction-model
  https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
  https://blog.csdn.net/matt8/article/details/53096405
  http://database.51cto.com/art/201901/591260.htm

16 mvcc 如何保证在RR级别下避免幻读
ans: MVCC 即 Multi-Version Concurrency Control，多版本的并发控制协议。
它的主要原理是：

image.png

• MySql InnoDB 引擎 的数据结构中，包含两列特殊的字段：DB_TRX_ID和DB_ROLL_PTR。其中DB_TRX_ID表示修改该行事务的事务ID，而DB_ROLL_PTR表示指向该行回滚段的指针，该行记录上所有版本数据，在undo中都通过链表形式组织，该值实际指向undo中该行的历史记录链表。
• 确定某个事务的影响是否在当前事务可见的原则：
  （1）如果这个事务的标号 < read_view 中 trx_id 最小的事务，那么认为此事务已经提交，更改可见
  （2）如果这个事务的标号 > read_view 中 trx_id 最大的事务，那么认为此事务是在当前事务之后开启的，更改不可见
  （3）如果这个事务的标号 在 read_view 的最大-最小 trx_id 区间内，那么再检查read_view，如果这个事务存在其中，说明它还没有提交，那么更改不可见；反之认为已经提交，更改可见。
  RC级别下，每次select时都会更新read_view，因此可能出现幻读的情况。
  RR级别下，只有第一次select才会生成read_view，后续不再变更，直到事务结束。此时当前事务不会感知到在事务过程中其他事务的变化，因此不会出现幻读（有争议）。
  RR级别下如果要严格意义避免幻读，那么应当使用“当前读”语句（select...for update）。例如一个带有unique key = id 的表，session A 读取 id>0 的数据，随后session B向表中插入id=2的数据，在RR级别，session A中不论什么时候读取数据，都不会看到session B插入的数据，但session A如果自己也插入插入id=2时就会发生Duplicate entry错误。使用“当前读”语句就可以避免这样的问题，这些语句会为id>0的数据加入间隙锁（gap lock），控制这个区间不被其他事务插入。
  参考：http://hbasefly.com/2017/08/19/mysql-transaction/

17 数据库连接池的作用
ans: 维护一定数量的连接，减少创建连接的时间
更快的响应时间
统一的管理
参考： https://blog.csdn.net/qq_22222499/article/details/79060495

18 最左匹配原则
ans ：最左匹配原则是针对索引的。
举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，查询条件where name=’xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换，这个sql也能利用到索引了

18 如何实现 update的乐观锁， select悲观锁
ans：
（1）乐观锁：mysql 语法不支持乐观锁，需要自己实现： 通过比较版本号／旧值
（2）悲观锁：select -> select ... for update
参考：https://www.cnblogs.com/zhiqian-ali/p/6200874.html
https://stackoverflow.com/questions/17431338/optimistic-locking-in-mysql