数据库面经知识点总结

1. 数据库两种引擎MyISAM和InnoDB的区别？
   1) count运算上的区别： 因为MyISAM缓存有表meta-data（行数等），因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。
   2) 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
   3)是否支持外键： MyISAM不支持，而InnoDB支持。
   MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。

2. 数据库索引有了解过嘛，解释一下两种引擎。
   MyISAM特点：

   1. 不支持行锁(MyISAM只有表锁)，读取时对需要读到的所有表加锁，写入时则对表加排他锁；
   2. 不支持事务；
   3. 不支持外键；
   4. 不支持崩溃后的安全恢复；
   5. 在表有读取查询的同时，支持往表中插入新纪录；
   6. 支持BLOB和TEXT的前500个字符索引，支持全文索引；
   7. 支持延迟更新索引，极大地提升了写入性能；

   8. 对于不会进行修改的表，支持压缩表，极大地减少了磁盘空间的占用；

      InnoDB特点：

      1. 支持行锁，采用MVCC来支持高并发，有可能死锁；  
      2. 支持事务；  
      3. 支持外键；  
      4. 支持崩溃后的安全恢复；  
      5. 不支持全文索引；   
      

3. 让介绍一下数据库索引？数据库索引有哪些，什么时候会失效，索引底层是怎么实现的？说一下InnoDB的索引结构？索引底层是怎么实现的?
   索引（Index）是帮助MySQL高效获取数据的数据结构。
   Mysql索引使用的数据结构主要有B树索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
   Mysql的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。
   MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
   InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

4. MySQL数据库索引有哪些?

   1. 从数据类型角度：B+树索引，哈希索引等；
   2. 从物理存储角度：聚集索引，非聚集索引；
   3. 从逻辑角度：主键索引，普通索引，组合索引，唯一索引，空间索引等；

5. 索引什么时候会失效？
   有5种索引失效的情况：
   （1）对单字段建了索引，但是where条件多字段。
   （2）建立联合索引，where条件单字段。与上面情况正好相反。
   （3）对索引列运算，运算包括（+、-、*、/、！、<>、%、like’%_’（%放在前面）、or、in、exist等），导致索引失效。
   （4）类型错误，如字段类型为varchar，where条件用number；
   （5）对索引应用内部函数，这种情况下应该建立基于函数的索引。

6. 聚集索引和非聚集索引的区别.
   整个表变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。
   非聚集索引， 也就是我们平时经常提起和使用的常规索引。非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。
   非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。

   1. 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个，这个跟没问题没差别，一般人都知道。
   2. 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续，这个大家也都知道。
   3. 聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

7. InnoDB和MyISAM索引的区别？
   MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做“非聚集”的。
   第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

8. mysql的联合索引
   用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)。

9. SQL优化思路，联合索引与底层树结构的映像关系，索引结构（B+、B），为什么用这样的结构
   SQL优化就是让SQL执行的更快。

   1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；
   2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；
   3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描；
   4. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；
   5. in 和 not in 也要慎用，否则会导致全表扫描；
   6. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描；
   7. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描；
   8. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引；
   9. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致
   10. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志；
   11. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差
   12. select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的；
   13. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些；
   14. 尽量避免大事务操作，提高系统并发能力；
   15. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改；

10. B树与B+树的区别？
    因为我们要考虑磁盘IO的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，它的每个节点最多包含m个孩子，m称为b树的阶，m的大小取决于磁盘页的大小。
    B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把节点大小限制和充分使用在磁盘快大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度;
    b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
    b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
    对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历。
    b+树在B树的基础上每个节点存储的关键字数更多，树的层级更少所以查询数据更快，所有指关键字指针都存在叶子节点，所以每次查找的次数都相同所以查询速度更稳定;

11. 查看索引使用情况需要哪个关键字？
    使用EXPLAIN关键字；查看索引的使用情况show global status like 'Handler_read%';
    Handler_read_key 数值越大的话，表示索引使用率高;
    Handler_read_rnd 查询直接操作了数据文件，是没有使用索引;
    Handler_read_rnd_next 数值越大的话，表示索引低效;
    Handler_read_next 此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数;

12. 索引为什么快？
    假如一张表有一亿条数据 ，需要查找其中某一条数据，按照常规逻辑， 一条一条的去匹配的话， 最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用， 因此，这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销，以现在磁盘的IO能力和CPU的运算能力， 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。换言之，查找次数是以树的分叉数为底，记录总数的对数，用程序来表示就是Math.Log(100000000,10)，100000000是记录数，10是树的分叉数（真实环境下分叉数远不止10）， 结果就是查找次数，这里的结果从亿降到了个位数。因此，利用索引会使数据库查询有惊人的性能提升。
    一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

13. 索引的缺点？
    索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

14. 数据库四种事务特性，隔离级别，死锁。数据事务脏读，不可重复读，幻读的解决方案?
    四种特性ACID：
    （1）原子性Atomicity：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
    （2）一致性Consistency：执行事务前后，数据保持一致；
    （3）隔离性Isolation：并发访问数据库时，一个用户的事物不被其他事物所干扰，各并发事务之间数据库是独立的；
    （4）持久性Durability: 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库 发生故障也不应该对其有任何影响；

---

为了达到上述事务特性，数据库定义了几种不同的事务隔离级别：
（1）未提交读（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其它事务也是可见的。最低级别，任何情况都无法保证。
（2）提交读（READ COMMITTED）：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的 修改在提交之前对其它事务是不可见的。可避免脏读的发生。
（3）可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样数据的结果是一样的。可避免脏读、不可重复读的发生。
（4）可串行化（SERIALIXABLE）：强制事务串行执行。可避免脏读、不可重复读、幻读的发生。
在MySQL数据库中，支持上面四种隔离级别，默认的为REPEATABLE READ(可重复读)。

15. 数据库的三范式？
    第一范式：数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性存在传递函数依赖关系。
    第二范式：完全依赖于主键，消除非主属性对主码的部分函数依赖
    第三范式：每个非关键字列都独立于其他非关键字列，并依赖于关键字，第三范式指数据库中不能

16. 数据库优化措施？数据库高并发下的优化思路？大量数据，高并发访问如何优化？
    当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：
    第一优化你的sql和索引；第一优化你的sql和索引；
    （1）限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
    （2）读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；
    （3）缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
    （4）垂直分区：根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
    简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
    （5）水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。
    水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
    注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。
    （6）（4）和（5）代表分库分表的两种形式。

17. 数据库分库分表，垂直和水平来分？
    分表解决的是，过大的数据表影响计算速度的问题，比如单表上亿，那么我拆成十个表，那么我必然会比一个表更快，原因是，用了一部分计算落表时间，来换取表计算的时间。
    分库更多解决的是机器的局限的问题，单机容量有限，单机容量实际上是由机器硬件决定，比如我的网卡打满，CPU打满，机器磁盘写满，这种case，必须将计算分布到其他机器上解决。先拆分进程，随后将进程拆分到不同的机器上。

18. 热点数据访问优化?
    实际应用中涉及热点数据访问时，Innodb是一个高性能的较好的选择，但前提是要能够预估热点数据的大小，只有当热点数据小于Innodb buffer pool（即热点数据全部能够放入内存）时，才能够获得高性能。

19. MySQL数据库表的连接方式？leftjoin和rightjoin的区别？
    1.内连接（等值连接）：只返回两个表中联接字段相等的记录。
    2.左连接：返回左表中的所有记录以及和右表中的联接字段相等的记录。
    3.右连接：返回右表中的所有记录以及和左表中的联接字段相等的记录。
    4.自连接（同一个表内）：

21. 数据库查询返回一列空值如何操作?
    执行下面的SQL语句就可以查询：SELECT * FROM 数据库表名 WHERE 某一个字段 IS NULL

22. 数据库百万条数据，用limit查询前十条和最后十条性能的区别，说明原因？
    前十条较快，最后十条较慢。
    因为数据库也并不知道最后十条的记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。可以使用create_time进行排序，然后用limit取出最后10条。

23. 修改MySQL字符集需要哪个关键字？
    1.修改mysql表的字符编码方式: alter table t_name convert to character set utf8;
    2.修改数据库的字符集: alter database mydb character set utf8;

24. 数据库分库分表解决大数据量查询效率问题怎么做？
    两种拆分方式：
    垂直拆分：是指按业务功能模块拆分，比如分为订单库、商品库、用户库，这种方式多个数据库之间的表结构不同。
    水平拆分：按照一定的分片算法将同一个表的数据进行分块保存到不同数据库的数据表中，这些数据库中的表结构完全相同。
    某种意义上讲，我们的网站系统是按照业务功能进行垂直拆分的，不同的业务系统负责管理不同的业务数据库。在同一个业务系统中，比如订单系统，当单库单表的数据量不断增长时，往往只需要考虑水平拆分。

25. 实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树实现的？
    红黑树这些二叉树结构的数据结构可以达到最高的查询效率这是毋庸置疑的。
    红黑树基本都是存储在内存中才会使用的数据结构，但是数据库要对磁盘进行操作。操作系统磁盘读写有一个最少内容的限制，即使我们只需要一个字节的内容，我们也要把一整个上的内容读完。现在问题就来了一个父节点只有2个子节点，并不能填满一个簇（cluster）上的所有内容啊。怎么才能把浪费的这部分内容利用起来呢？答案就是B+树。由于B+树分支比二叉树更多，所以相同数量的内容，B+树的深度更浅，深度代表磁盘 io次数.数据库设计的时候B+树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的。所以涉及到磁盘上查询的数据结构，一般都用B+树。

26. 数据库水平切分多库相交于切分成多表的好处？
    分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

27. MySQL的锁有哪些?乐观锁和悲观锁?
    锁的分类
    一、按锁的粒度划分，可分为表级锁、行级锁、页级锁（mysql）
    二、按锁级别划分，可分为共享锁、排他锁
    三、按使用方式划分，可分为乐观锁、悲观锁
    四、按加锁方式划分，可分为自动锁、显示锁

---

Mysql用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。显著的特点是不同的存储引擎支持不同的锁机制。
悲观锁：假定会发生并发冲突，则屏蔽一切可能违反数据完整性的操作。
乐观锁：假定不会发生并发冲突，只在数据提交时检查是否违反了数据完整性（不能解决脏读问题）。

---

乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。
共享锁相当于对于同一把门，它拥有多个钥匙一样。
排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。
行锁给某一行加上锁，也就是一条记录加上锁。
表锁和行锁相对应，给这个表加上锁。

28. 行锁、页面锁和表锁？
    Mysql的行锁和表锁（ 锁是计算机协调多个进程或纯线程并发访问某一资源的机制）
    表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
    行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
    页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
    仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用。（BDB）

1. 从数据库中主键字段取出上万条数据，用哪种排序好
2. redis在项目中用来干什么，基于什么考虑。讲了频繁访问的数据放到redis中，还有拿redis做异步任务队列。
3. 单机redis有可能挂掉，解决措施。
4. 你说的SQL优化，怎么优化的，怎么思考的？