数据库总结

**

灵活使用join,group by,order by以及常见的聚合函数

**
Join

• inner join(内连接）：只连接（返回）两个表匹配的行
• left join(左连接）：LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
• right join(右连接）：RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。
• full outer join：FULL JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替。

group by

• “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
• group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数

order by
order by 类别 desc

Having与Where的区别

• where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚合函数，使用where条件过滤出特定的行。
• having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

索引的原理：b+树的好处，b+树和b树的区别；

• 索引相当于目录，存放了表中部分列的值以及记录对应的地址，并且把这些值存储到一个数据结构中。
• 索引是存在磁盘中的，读取的时候是一块一块的预读取到内存的，平衡二叉树的查找时间复杂度是O(log2N)，但是平衡二叉树只是逻辑结构，物理存储上可能相隔非常远。
• b树是有序数组+平衡多叉树。b树的每个节点都会放很多关键字（数量设置为磁盘一次I/O的值）,所以b树能提高磁盘I/O性能，但并没有解决元素遍历效率低下的问题
• b+树是有序数组链表+平衡多叉树。b+数只有叶子节点存放关键字，并且叶子节点有个指针指向下一个叶子节点，所以元素遍历时只需要遍历叶子节点就可以了。

联合索引的设计：比如（A,B,C）索引，在where a=1,b>1,c=1中哪些列可以用到索引？where b=1,c=1可不可以用到索引，为什么（联合索引的结构）？where a=1,c=1时可不可以用到索引？where a=1,b=1 order by c可不可以用到？**

• 比如（A,B,C）索引，在where a=1,b>1,c=1中哪些列可以用到索引？
  只有a,b可以用到索引，根据最左原则，创建（A,B,C）索引相当于创建了(A),(A,B),(A,B,C)索引。sql里遇到范围查询时就会停止匹配，所以c用不到索引。（如果建立的是(a,c,b)索引则可以全部用到）
• where b=1,c=1可不可以用到索引，为什么（联合索引的结构）？
  不能。有A的才会使用索引。
• where a=1,c=1时可不可以用到索引？
  a可以用到索引，c不可以。只有where的情况，遵从最左原则，条件必须有左边的字段，才会用到索引，中间如果断开了，则都不会用到后面的索引。
• where a=1,b=1 order by c可不可以用到？
  全部都用到了索引。 group by和order by 其实一样，也是遵从最左原则，可以看做继承where的条件顺序，但需要where作为基础铺垫，即没有where语句，单纯的group by | order by 也是不会使用任何索引的，并且需要和联合索引顺序一致才会使用索引。

索引设计：根据一些常用的查询条件设计索引；

• CREATE INDEX 索引名 on 表名（字段名1，字段名2）
• 索引名一般是：表名_字段名1_字段名2
• 索引字段最多三个
• 注意索引是全表加锁

范式

• 第一范式：当关系模式R中的所有属性都不能再分解为更基本的属性时，则R是满足第一范式的。
• 第二范式：当关系模式R满足第一范式后，所有非主属性都完全依赖于R的每一个候选关键属性时（由主键唯一确定），则R是满足第二范式的。
• 第三范式：当关系模式R满足第二范式后，X是R的任意属性集合，当X非传递依赖于R的任意一个候选关键属性时，则R是满足第三范式的。

MVCC

• Multi-Version Concurrency Control,翻译为中文即 多版本并发控制
• MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。
• 基本特征：每行数据都存在一个版本，每次数据更新时都更新该版本。修改时Copy出当前版本随意修改，各个事务之间无干扰。保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

MySQL中的MVCC

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。
每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

事务，相应的隔离级别，默认的隔离级别；

• 事务的ACID原则：原子性，一致性，隔离性，持久性。
• 事务的隔离级别有四个：读未提交，读已提交，可重复读，串行。
• 读未提交：一个事务可以读取到其他事务未提交的数据，会导致脏读。该隔离级别很少用于实际应用。
• 读已提交：一个事务要等到另一个事务提交后才能读取数据，可避免脏读，但导致的问题是不可重复读，即我们在相同的事务中执行同样的sql语句可能会出现不同的结果。导致这种情况的原因可能有：(1)有一个交叉的事务有新的commit，导致了数据的改变;(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit
• 可重复读：就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生。**MySQL默认隔离级别。**能保证同一事务的多个实例在并发读取实例时读到的是同样的数据行。但是此隔离级别可能导致“幻读”，即当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过MVCC机制解决了该问题。
• 串行：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。