总结之数据库篇

---

前言

本篇博客是对数据库有关重要知识的总结，还在不断补充中。

---

一、数据库三范式

1NF
 数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性。
2NF
 满足1NF后，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情。
3NF
 满足第二范式，要求表中的每一列只与主键直接相关而不是间接相关（表中的每一列只能依赖于主键）。

二、事务（Transaction）

事务是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元。

1.使用

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;

2.事务的四大特性 ACID

 原子性（Atomicity）
  事务是一个不可分割的工作逻辑单元。

 一致性（Consistency）
  当事务完成时，数据必须处于一致状态。

 隔离性（Isolation）
  对某一数据进行修改的所有并发事务彼此隔离，事务之间必须是独立的，不应该以任何方式依赖或影响其他事务。

 永久性（Durability）
  事务完成之后，它对数据库的修改被永久保存，事务日志能够保持事务的永久性。

3.并发事务引发的问题

 更新丢失：两个事务读入同一个数据并修改，结果其中一个事务提交的结果覆盖了另一的结果，导致前一个事务的更新丢失。

 脏读：一个事务读到另外一个事务还没有提交的数据。

 不可重复读：一个事务先后读取同 一条 数据，但两次读取的数据不同。

 幻读：在同一事务中，当同一查询多次执行的时候，由于其他的事务对该数据集进行了修改，导致每次返回不同的结果集。

脏读 VS 不可重复读：
 脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是在同一个事务范围内多次查询同一条数据却返回了不同的结果，这是由于在隔离期间，该条数据被另一个事务修改并提交了。

幻读 VS 不可重复读：
 幻读和不可重复读都是读取了另一个事务中已经提交的数据。不同的是不可重复读多次查询的都是同一个数据项，针对的是同数据的修改或删除，而幻读针对的是一个数据整体，主要是插入或删除操作。

4.事务隔离级别

  解决并发事务所引发的问题。

 读未提交：在一个事务没有提交的情况下，其他事务可看到该事务对数据的修改，直接读取最新的版本。数据库本身已经具备阻止更新丢失的问题，所以最低级别的事务隔离也可以阻止更新丢失的问题，但还是可能出现脏读、不可重复读和幻读的问题。
 读已提交：在一个事务提交前，其他事务看不到改事务对数据的修改。解决了脏读的问题，但还有可能出现不可重复读和幻读的问题。
 可重复读：在同一事务中按照相同条件多次查询的结果都是相同的。解决了不可重复读的问题，但还可能出现幻读的问题。
 可串行化：事务串行化顺序执行。阻止了更新丢失、脏读、不可重复读和幻读的问题，但是消耗数据库性能，效率低下。

隔离级别	更新丢失	脏读	不可重复读	幻读
Read uncommitted（读未提交）		会出现	会出现	会出现
Read committed（读已提交，Oracle的默认事务隔离级别）		不会出现	会出现	会出现
Repeatable Read （可重复读，MySQL的默认事务隔离级别）		不会出现	不会出现	会出现
Serializable（可串行化）		不会出现	不会出现	不会出现

三、MySQL体系结构

连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、即相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：第二层主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
引擎层：存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储迎请具有不同的功能，就可以根据需求，选取合适的存储引擎。存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的。有InnoDB、MyISAM、Memory等。
存储层：主要是将数据存储在文件系统之上并完成存储引擎的交互。

+、常用的存储引擎

InnoDB

是一种兼顾高可靠性和高性能的存储引擎，是现在的MySQL默认存储引擎。
特点 ：
（1）DML操作遵循ACID，支持事务。
（2）行级锁，提高并发访问性能。
（3）支持外键约束，保证数据的完整性和一致性。
InnoDB逻辑存储结构：表空间、段、区、页、行。

MyISAM

是早期MySQL的默认存储引擎。
特点：
（1）不支持事务，不支持外键。
（2）支持表锁，不支持行锁。
（3）访问速度快。
MyISAM的空间使用和内存使用度都比较低，不支持事务安全。

Memory

Memory引擎的表数据是存储在内存中的，受硬件、断点等问题影响，只能将这些表作为临时表或者缓存使用。
特点：
（1）存放在内存中，速度快。
（2）默认hash索引。表锁。

存储引擎的适合场景

InnoDB：如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作。
MyISAM：如果应用以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性使得一致性要求不高。电商中的足迹和评论适合使用MyISAM引擎。
Memory：将所有数据保存在内存中，访问速度较快，通常用于临时表及缓存。缺陷是对表的大小有限制，太大的表无法缓存在内存中，无法保证数据的安全性。缓存适合使用Memory引擎。

四、索引

索引（index）是帮助MySQL 高效获取数据 的 数据结构（有序）。

1.索引的优缺点

 优点：1.提高数据检索的效率，降低数据库的IO成本。
    2.通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
 缺点：1.索引列也需要占用空间。
    2.索引大大提高了查询效率，同时却也降低更新表的速度。

2.索引结构

B+Tree索引

B-Tree（多路平衡查找树）

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量的情况下，层级较深，检索速度慢。可以使用自平衡的红黑树解决。
红黑树缺点：在存储大数据量的情况下，层级较深，检索速度慢。使用B-Tree解决。

n阶B-Tree实现原理：
 n阶B-Tree每个节点最多有n个指针。(n - 1个key，n个指针，n个子节点）节点内按顺序存储，整体按左小右大存储。
 当指针数大于n了，插入后将中间key向上分裂出去，将当前节点跟列成两部分。

B+Tree

 与B-Tree不同的是在分裂的时候，将中间key向上分裂，但叶子结点的中间key不删去，前一节点的next指向该节点，形成单链表。所以所有的数据都会出现在叶子节点，叶子节点形成了一个单向链表。

MySQL对B+Tree进行了优化，在原有基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

Hash索引

R-Tree（空间索引）

Full-text（全文索引）

是一种通过建立倒排索引，快速匹配文档的方式。

面试题：为什么InnoDB存储引擎选择使用B+Tree索引结构？

 1.相对于二叉树，层级更少，搜索效率高。
 2.对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
 3.对于hash索引，B+Tree支持范围匹配及排序操作。

3.索引的分类

分类	含义	特点	关键字
主键索引	针对于主键创建的索引	默认只能自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键字，而不是比较索引中的值	可以有多个	FILLTEXT

innoDB将索引分为聚集索引（主键索引，叶子节点下挂行数据），二级索引（非主键索引，需要会表查询更耗时、叶子节点下挂主键）。
聚焦索引的选取规则：
 （1）如果存在主键，主键索引就是聚焦索引。
 （2）如果不存在主键，将使用第一个唯一索引作为聚焦主键。
 （3）如果表没有主键或者合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚焦索引。

4.语法

创建索引：
CREATE [ UNIQUE | FILLTEXT ] INDEX indx_name ON table_name (index_col_name, …);

查看索引：
SHOW INDEX FROM table_name;

删除索引：
DROP INDEX index_name ON table_name;

5.性能分析

1.查看执行频次

查看当前数据库INSERT,UPDATE,DELETE,SELECT访问频次：
SELECT GLOBAL STATUS LIKE ‘Com_____’;

2.慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志，由它来查看哪些 SQL 超出了我们的最大忍耐时间值。

3.profile

show profile 能在做SQL优化的时候帮我们了解时间都耗费在哪里。通过SELETE @@have_profiling;能看到当前MySQL是否支持profile操作。通过show profiles；查看所有语句的耗时。

4.explain执行计划

通过explain或者desc命令获取MySQL如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序。
各字段含义：
 id：查询序列号，id相同执行顺序从上到下，id不同，值越大越先执行。
 select_type：表示select的类型。
 type：表示连接类型，性能由好到到差一次为NULL、system、const、eq_ref、ref、range、index、all.
 possible_key：可能应用在这张表上的索引，一个或多个。
 key：实际使用的索引，如果为NULL表示没有使用索引。
 Key_len：表示索引中使用的字节数。
 rows：MySQL认为必须要执行的行数。
 filtered：表示返回结果的行数占需读取行数的百分比，越大越好。

6.索引设计原则

 （1）选择唯一性索引：索引值是唯一的，可以更快速的通过该索引来确定某条记录。
 （2）为经常需要排序 、分组和联合操作的字段建立索引。
 （3）为常作为查询条件的字段建立索引。
 （4）限制索引的数目：索引越多，索引本身会占用存储空间，且会使得更新表的时间变得更长。
 （5）尽量使用数据量少的索引：如果索引值很长，查询的速度会受到影响。
 （6）如果索引字段很长，尽量使用前缀来索引。
 （7）删除不再使用或者很少使用的索引。
 （8）最左前缀匹配原则。