数据库面试知识点

数据库三大范式

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我
们经常会为了性能而妥协数据库的设计。

存储引擎

Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的
设计的目标就是处理大数据容量的数据库系统。insert、update、delete更优；支持哈希索引

MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。表级锁定；select更优；支持全文索引。

索引

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会
非常高效。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它
们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新
数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一
个文件，它是要占据物理空间的。
索引的原理很简单，就是把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
   BTree是常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和
   between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的
   常量，例如：

--只要它的查询条件是一个不以通配符开头的常量
2 select*fromuserwherenamelike'jack%';
3 --如果一通配符开头，或者没有使用常量，则不会使用索引，例如：
4 select*fromuserwherenamelike'%jack';

HashHash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索
引需要从根节点到枝节点，后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。
1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2.基数较小的类，索引效果较差，没有必要在此列建立索引
3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行
更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

索引虽好，但也不是无限制的使用，好符合一下几个原则
1）左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、
between、like)就停止匹配，比如a=1andb=2andc>3andd=4如果建立(a,b,c,d)顺序的索引，d是用不到
索引的，如果建立
(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改
原来的索引即可。
6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修
改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所
以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数
量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2. 然后删除其中无用数据（此过程需要不到两分钟）
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分
   钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要
按照建立索引时的字段顺序挨个使用，否则无法命中索引。

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

数据库锁

InnoDB是基于索引来完成行锁，例: select * from tab_with_index where id = 1 for update;for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id不是索引键那么InnoDB将
完成表锁，并发将无从谈起

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
常见的解决死锁的方法
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的
概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop
循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不
准确的。这个是要区分环境的。

1. 如果查询的两个表大小相当，那么用in和exists差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
3. not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而
   not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

MySQL的复制原理以及流程
主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这
些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。
主从复制的作用

1. 主数据库出现问题，可以切换到从数据库。
2. 可以进行数据库层面的读写分离。
3. 可以在从数据库上进行日常备份。
   MySQL主从复制解决的问题
   数据分布：随意开始或停止复制，并在不同地理位置分布数据备份负载均衡：降低单个服务器的压
   力高可用和故障切换：帮助应用程序避免单点失败升级测试：可以用更高版本的MySQL作为从库
   MySQL主从复制工作原理
   在主库上把数据更高记录到二进制日志从库将主库的日志复制到自己的中继日志
   从库读取中继日志的事件，将其重放到从库数据中基本原理流程，3个线程以及之间的关联
   主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的 binlog中；从：io线程——
   在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
   从：sql执行线程——执行relay log中的语句；
   Binary log：主数据库的二进制日志
   Relay log：从服务器的中继日志
   第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到 binlog文件中。
   第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump
   process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程
   终的目的是将这些事件写入到中继日志中。
   第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持
   一致。