关于Mysql数据库，这些知识点你确定都会了吗？

说到Mysql, 大家都很熟悉，因为这是我们工作中不可避免会使用到的技术，但是你真正的掌握了它吗？还是每天在重复crud呢！那么怎么样告别crud呢！来到这里就对了。简单概念的上的东西我就不提了。直接上技术。

数据库三大范式

第一范式1NF:

数据表中的字段，必须是不可拆分的最小单元，也就是确保每一个字段的原子性。例如：

 

 

 

那么怎么去设计才是正确的呢？其中 address 可以再分为省、市、地区、街道、名牌号，违反了第一范式。

既然要满足原子性不可以分割，我们把这些可以拆分的数据都给拆分出来不就行了吗？以下是笔者拆分后的数据。

 

 

 

第二范式2NF:使用的时候只需与此表关联即可。

满足1NF的基础上，要求：表中的所以列，都必需依赖主键，而不能有任何一列与主键没有关系。言下之意就是一个表设计只能描述一件事情，不能把其它

 

 

 

无关的也嵌入进来。第二范式消除了表的无关数据。

那么怎么去设计才是正确的呢？其中 address 可以再分为省、市、地区、街道、名牌号，违反了第一范式。

此表中区域地址和你的用户心情毫无关系。正确的做法就是建立另外一张描述你情绪的表。

第三范式 3NF:

满足2NF的基础上，任何非主字段不依赖与其它非主字段，在2NF基础上消除传递依赖，也就是我们不允许设计的字段不能出现冗余现象。

 

 

 

我们从这几张表中可以看出province，city,district,street,detailaddress,doorid都是依赖于region_id，所以不满足第三范式。

笔者再多分享一点给大家，在实际工作当中，我们可能会反3NF,那么什么时候去反3NF呢？举个实际工作当中的例子吧！例如我们在一个订单中可能需要关联到用户，我们查看订单的时候需要显示出用户名，用户的其它信息就不用显示出来了，如果不反三范式我们 查询都需要关联用户表，如果查询很普遍的话，就会影响到性能，所以我们干脆就可以把用户名这个字段设计到订单中。这样就可以提高性能。在实际工作中灵活运用。

数据库五大约束

1、主键约束（primary key）

唯一性，非null性

2、唯一约束 (unique)

唯一性，可以空，单只能有一个

3、检查约束 (check)

对该列数据的范围、格式的限制（如：年龄、性别等）

4、默认约束（default）

该数据的默认值

5、外键约束（foreign key）

建立两表之间的关系

数据库事务

数据库事务是什么？

指的是单个逻辑工作单元执行的一系列操作，要么全部执行成功，要么完全不执行。

事务的四大特性

原子性

原子性是指事务包含的所有一系列操作要么全部成功提交，要么全部失败回滚。它是数据库事务最本质的特性。

一致性

一致性是指在事务开始之前和结束以后，数据库的完整性约束没有被破坏，这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性、这里有个容易混淆点，容易和数据一致性混淆。这里更多的强调单机下的事务一致性，必须是一个事务内部。

隔离性

每个事务都有各自的资源单位，事务与事务之间是互相隔离的、不可见，而事务的结果是对其它事务是可见的。这里可以理解为资源粒子度的划分与隔离。

持久性

持久性确保的是一旦提交了事务，出现系统故障，该事物的更新也不会丢失。可以理解为持久化到磁盘中。

事务的四种隔离级别

隔离级别由低到高分别为

读未提交（READ_UNCOMMITTED）

就是一个事务A去读取一个事务B未提交的数据。如果B事务出现回滚，那么事务A就会出现脏读的问题。

读已提交 （READ_COMMINTED）

一个事务A读取一个事务B已提交的数据，解决了脏读问题，但是在一个事务范围内两个相同的查询却返回了不同的数据，那么这就是不可重复读。

可重复读 (REPETABLE_READ)

事务开启时，不再允许其它的事务修改数据。这样就可以无限制读取没有被修改的数据，解决了不可重复读，当有并行插入操作时候就会出现幻读。

可串行化（SERIALIZABLE）

在可串行化的隔离级别下，将事务串行化顺序执行。那么事务不能进行并行操作，也就解决了幻读的问题。

MYSQL InnoDB 引擎默认事务隔离级别是可重复读（REPEATABLE_READ） ORACLE 引擎默认事务隔离级别是可串行化（SERIALIZABLE）

InnoDB 索引

innoDB特性

1. 完全的事务支持
2. 基于行存储的行级锁
3. 多版本并发控制
4. 原子死锁检测
5. 原子崩溃恢复

innodb 架构图

innodb 逻辑存储结构

在 innodb下，所有的数据都存储在一个表空间中，表空间又由段（segment）、 区（extent）、 页（page）、行（row）组成，页在有些文档中也成为块（block） 1 extent = 64 page

innodb 存储结构图

B-tree

定义：B树满足如下条件，即可称之为m阶B树：

1. 每个节点最多可以拥有m棵子树；
2. 根节点最少拥有2棵子树（存在子树的情况下）；
3. 除了根节点以外，其余每个分支节点至少拥有m/2棵子树；
4. 所有的页节点都在同一层上；
5. 有k棵子树的分支节点则存在k-1个关键码，关键码按照递增次序进行排列；
6. 关键子树量需要满足ceil(m/2)-1 <= n <= m-1;

b树图

B-tree 的特点是每个节点不仅存放键值，而且存放数据。

b+tree图

B+树特点：1. 所有的叶子节点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接。2. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树的优点：1. 单一节点存储更多的元素（因为不含有对应的值，仅仅含有键），使得查询的IO次数更少。2. 所有查询都要从跟节点查找到叶子节点，查询性能稳定，相对于B树更加稳定，因为B+树只有叶子节点存储了对应的值信息。3. 所有叶子节点形成有序双向链表，对于SQL的范围查询以及排序查询都很方便。4. b/b+树的共同优点：每个节点有更多的孩子，插入不需要改变树的高度，从而减少重新平衡的次数，非常适合做数据库索引这种需要持久化在磁盘，同时需要大量查询和插入的应用。树中节点存储这指向页的信息，可以快速定位到磁盘对应的页上面。

Mysql 锁总结

锁不仅是资源占有的一种处理机制，更是多线程或并发编程下对数据一致性的一种保证。加锁和释放锁本身也会消耗资源。了解并合理利用锁机制，能大大提升数据库的性能。锁的作用者是事务，也就是说，锁是针对事务使用而言。单个操作不显示的开启和提交/回滚事务，默认情况下每个操作会自动开启一个事务。

共享锁

一个事务对数据加共享锁，也可以允许其它事务对此交集数据加此锁。但阻止其它事务对此交集数据加排他锁。

加共享锁语句：SELECT * FRPM TABLE_NAME WHERE LOCK IN SHARE MODE;

排他锁

一个事务对数据加排他锁，会阻止其它事务对此交集数据加任和锁。

加排他锁语句：SELECT * FROM TABLE_NAME WHERE FOR UPDATE;

意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InoDB还有两种内部使用的意向锁，在这里的两种意向锁都是表锁。

意向共享锁

事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的意向共享锁。

意向排他锁

事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的意向排他锁。

意向锁是innodb 自动加的，不需要用户干预。

表级锁

每个事务操作会锁住整张表，粒子度最大，简单粗暴。优点是加锁和释放锁次数会大大减少。缺点是锁冲突的概率会大大增加，高并发情况下不可取。

页级锁

资源开销介于行级锁和表级锁，会出现死锁。

行级锁

每个事务仅会锁住被影响的行，也就是说，涉及到哪些行记录，哪些行才会被锁住，会出现死锁。优点是锁冲突概率小，并发度高。缺点是由于锁离子度小，加锁和释放锁的次数大大增加，资源开销大。

mysql的行级锁通过索引项上的索引来实现的，innodb这种行锁实现特点意味着只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb将使用表锁。

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享锁或排他锁时，innodb会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙，innodb也会对这个间隙加锁，这种锁机制不是所谓的间隙锁。InnoDb使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其它事务插入更改了任何记录，那么本事务再次执行上述语句，就会发生幻读；另一方面，是为了满足其恢复和复制的需要，有关恢复和复制机制的影响，以及不同隔离级别下innodb使用间隙锁的情况。

很显然，在使用范围条件检索并锁定记录时，innodb对这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际开发中，尤其是并发插入比较多的应用，我们尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

死锁

死锁是指两个或多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能产生死锁。

innodb避免死锁

1. 为了在单个innodb表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个记录（行） 使用SELECT ... FOR UPDATE 语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
2. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户在申请排他锁时，其它事务可能又已经获取了相同记录的共享锁，从而造成锁冲突，甚至死锁
3. 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低死锁的机会
4. 同过SELECT...LOCK IN SHARE MODE 获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很可能造成死锁。
5. 改变事务隔离级别

如果出现死锁，可以使用SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息。如引发的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

乐观锁

加锁是为了占用资源，我们上面说过加锁和释放锁都会有资源开销。在有些不需要加锁就能获取资源岂不是更好？乐观锁是乐观的认为在抢占资源是不用加锁就能获取资源（因为没有其它事务抢占资源或者发生的冲突概率小，稍稍尝试几次就能成功，美滋滋）。适用冲突概率小的情景下。

悲观锁

在冲突概率大的情况下，悲观的认为抢不到资源或者多次都抢不到资源。只能通过加锁的方式抢占资源，然后再做处理，最后释放资源。

SQL优化

优化必备的explain命令

explain命令是用来查询SQL的执行计划 用法：explain select filed from table;

会查询出以下重要字段：

+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | id | selecttype | table | partitions | type | possiblekeys | key | keylen | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t0 | NULL | range | idxtradeid | idxtrade_id | 8 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 1 row in set (0.02 sec)

重要字段说明：

select_type:使用的select查询类型，比如simple、primary、union、subquery等；

table:关于访问哪张表，如果是多表，则按访问的先后排序排列；

type:非常非常重要的指标，表示mysql在表中找到行记录的方式，又称访问类型。访问类型的性能指标从差到好依次是system > const > eqref > ref > fulltext > refornull ? indexmerge > uniquesubquery > indexsubquery > range > index > ALL, 一般来说，得保证查询至少达到range级别，最好能达到ref,否则可能出现性能问题；

possible_keys: 可能用到的所以，如果为null，表示没有可能用到的索引；

key:用到的索引，如果为null，表示没有使用索引；

key_len: 按字节计算的索引长度，值越小，表示越快；

ref:关联关系中另一个表的列名称；

rows: 查询数据返回的行数；

extra:与关联操作有关的信息

索引优化

1.禁止无边界范围查询 != , < , > , <= , >= 否则 不会命中索引。

2.禁止无边界范围查询 NOT IN ， 否则不会命中索引

3.禁止左模糊或全模糊查询，否则不会命中索引

4.字段的默认值不要为null，否则不会命中索引（使用默认约束Default Counstraint）填充数据默认值

5.在字段上计算后，不会命中索引

6.组合索引的最左前缀原则

7.关于number类型的字段不加单引号也会走索引

8.对于多表 JOIN 时的 ON 条件中 字段类型一定要一致，否则也不会命中索引

9.varchar 查询性能比 bigint 好，因为bigint类型字段上会全表扫描，而在varchar上每个字符判断会走索引，这样避免全表扫描。

10.小数类型使用decimal,禁止使用float与double float和double存储数据时，可能或损失精度，进而判断的时候导致结果不准，强制使用decimal数据类型。

11.表达是否的概念时，字段使用is_开头，数据类型使用unsigned tinyint类型， 1表示是 0 表示否。

12.任何非负数都必须声明为unsigned类型 比如年龄。状态吗等，这样最大容量正值会扩大一倍。

13.如果存储的字符串长度几乎相等，必须使用char定长字符串类型 比如手机号码11位。

14.有时候是不需要建索引 性别字段，状态，这种不同值很少的字段是不需要建立索引的。

15.单表行数超过500万行或单表容量超过2G，才推荐分表

16.进行update或delete时，必先select，避免出现误删数据

数据库拆分

数据库承载的数据以及请求负载较高时，我们就要考虑使用读写分离、数据缓存。但随着业务的增长，数据库的压力达到了承载的阀值米就要考虑分库分表，分解，分摊单个数据库压力。

垂直拆分

数据库的垂直拆分:通常将所有的数据按照不同的业务建立并存储不同的表（table）,垂直拆分是按照业务将一个数据库拆分多个数据库。原来每个业务对应一张表，垂直拆分后，是一个业务对应一个数据库（当然也有坑可能是多个业务对应一个数据库）。其核心是专库专用。达到的结果是将原来一个数据库系统的压力按照业务均摊到各个拆分后的数据库中。垂直拆分也是比较推荐的一种拆分方式。

垂直分片往往需要对架构和设计进行调整。在当前微服务化的进程中，对数据库的垂直拆分是非常友好的。

数据表的垂直拆分：单表的数据达到2GB或者500万行记录就要考虑拆分数据表，垂直拆分表就将热点列和不经常使用的列表拆分开，降低单表的大小。

水平拆分

当一般垂直拆分遇到瓶颈时，会对数据表进行水平拆分。这种方式与垂直拆分不同的地方是，他不会更改表结构。水平分表是将一个表拆分成多结构相同的多个表；并且这些表分布在不同的数据库。

分库分表中间件有两种 1.代理模式的分库分表中间件：MyCat; 2.客户端模式的分库分表中间件：ShardingJDBC 3.支持事务的分布式数据库（当然ShardingProxy也是代理分库分表中间件）

总结： 结合着微服务体系，一般会进行垂直拆分。当微服务中的数据库出现压力时，然后进行水平拆分。