数据库杂记

范式

数据库范式有

一范式：字段不可以再分
二范式：要有主键，其他字段都依赖于主键
三范式：消除传递性依赖，消除冗余
复制代码

三范式是用来学习参考的，设计时候根据实际情况，不一定要遵守

数据库事务和隔离级别

数据库事务，在一个事务中所有操作要么都执行失败，要么执行失败回滚。

事务有ACID特性

A:原子性，即一个事务是不可分割的一个原子单位
C：一致性。数据库的数据总能从一个一致性状态转到另一个一致性状态。比如你给小明转了200块，你和小明在银行的总的钱是一致的
I：隔离性：一般来说，一个事务里的操作对其他事务是不可见的
D：持久性：一旦事务已提交，对数据库的修改是持久的
复制代码

隔离级别： 事务隔离级别规定了一个事务的修改，对事务内和事务间哪些是可见的，哪些是不可见的。级别越低的隔离级别，其并发性更高，系统开销更低

未提交的读：一个事务可以读到另一个事务没有提交的数据，可能产生脏读
提交的读：一个事务只能读到另一个事务已经提交的数据，可能产生不可重复读，大多数数据库系统的默认隔离级别是提交读
可重复读：同一个事务中多次读得数据时一致的，但是如果当事务查询一个范围内的数据，而其他事务又插入了新行，则就可能产生幻读，MVCC来解决这个问题。MySQL默认隔离级别是可重复读
串行：事务排队一个一个的执行
复制代码

为什么需要锁，锁的分类，锁的粒度

多个线程对于同一资源的操作，并发操作可能会导致数据的不一致，所以需要通过加锁来保证数据的正确性。

数据库是多用户使用的共享资源。多个用户并发的存取数据库时，数据库中就会产生多个事务同时存取同一数据的请求，若不加以并发控制，或导致数据的一致性。

数据库使用锁是为了共享数据的并发访问控制，提供数据的完整性和一致性

锁分类

共享锁  S Lock,允许事务读一行的数据
排他锁 X Lock，允许事务删除或更新一行数据
复制代码

锁的粒度

行级锁
表锁
复制代码

乐观锁，悲观锁的概念及实现方式

乐观锁

比较乐观，总是认为不会发生并发问题。每次去取数据时总是认为其他线程不会对数据进行修改。所以不会上锁，但是在更新时会去判断其他线程在这之前是否对其进行了修改。

version方式：

数据库表中加上一个版本号，表示数据被修改的版本，数据被修改时，版本号会增加。当线程读取数据时，也会读取版本，更新会判断版本号是否为读时的版本号，若不等，则说明被其他线程修改过，这时可以可以放弃修改或者重试更新直至成功

update table set x=x+1,version = version+1 where id =1 and version = #{version}
复制代码

CAS方式：

compare and swap或compare and set。更新新值时判断旧值和数据库里的现有值是否相等，相等则更新，不等则自旋直至成功
复制代码

悲观锁

总是悲观的，每次拿数据时，总是认为别人会修改数据，所以在拿数据的时候就会加上锁。当其他线程想要访问数据时，则会阻塞挂起。

select ... for update

mysql如何实现分页

数据量比较少的时候，我们可以使用

select * from table where xx limit offset,size
复制代码

其实limit offset, size 是取前面的offset+size的数据，然后丢掉前面offset的记录，取Size大小的数据返回，如果offset越来越大，并发查询，每次查询扫描的数据越来越多，性能会越来越差

select * from table where  id>10 order by asc limit size
复制代码

这类查询每次只会扫size。虽然扫描的数据量少了，但是在某些需要跳转到多少也得时候就无法实现

mysql引擎

mysql的核心就是存储引擎。

数据库管理系统利用数据引擎进行创建、查询、更新和删除数据。

不同的存储引擎提供不同的存储机制、索引技巧和锁的功能。

SHOW ENGINES

InnoDB引擎

innodb引擎提供对数据库ACID事务的支持，提供了行锁和外键的约束，设计目标就是处理大数据量容量的数据库系统。不支持全文搜索。

当使用事务时，该引擎就是首选的。锁的粒度比较小。写操作是不会锁表的。在并发比较高的场景下会提升效率

innodb引擎是默认的Mysql引擎。

存储引擎是基于表，不是基于数据库的

Innodb支持事务、目标面向在线事务处理的应用，特点行级锁、支持外键，支持非锁定读，即默认读取操作不会产生锁。从mysql5.5.8开始，innodb引擎就是默认的存储引擎

innodb通过多版本并发控制mvcc获得高并发性，并且实现了sql的4个隔离级别，默认是可重复读级别。同时使用 next-key-locking的celve避免幻读

另外还提供了插入缓存、二次写、自适应哈希索引、预读

innodb采用可聚集方式存储数据，因此每张表的存储都是按主键顺序存放。若没有显示的在表定义时指定主键，inndb会为每行生成一个6字节的rowid，并以此为主键。
复制代码

myisam引擎

不支持事务、表锁设计、支持全文索引、面向olap数据库应用【联机分析处理】

缓存池只缓存索引文件，而不是缓存数据文件
复制代码

memroy引擎

默认使用哈希索引，只支持表锁、并发性差，不支持Text和blob类型
复制代码

InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引

MySQL语句优化

mysql提供了一个explain命令，可以对select语句进行分析，并输出select执行的详细信息，供开发人员针对性优化

id

select查询的标识符，每个select都会自动分配一个唯一的标识
复制代码

select_type

select查询的类型

SIMPLE：表示此查询不包含UNION查询或子查询
PRIMARY，表示此查询是最外层的查询
UNION：表示此查询时UNION的第二或随后的查询
SUBQUERY：子查询的第一个select

最常见的查询类型就是simple
复制代码

table

查询的哪张表
复制代码

partitions：

匹配的分区
复制代码

possible_keys

此次查询可能用到的索引
复制代码

type

join类型

这个字段比较重要，提供了判断查询是否高效的依据，通过type，可以判断此次查询时全表扫描还是索引扫描

常用类型

system：表中只有一条数据，这个类型是特殊的const类型

const：针对主键或唯一索引的等值查询，最多只返回一行数据。查询速度非常快，仅读一次即可

eq_ref:此类查询通常出现在多表join查询，表示对于前表的每一个结果，都只能匹配到后表的最后一行。并且查询的比较操作通常是=，查询效率高

ref：  此类型通常出现子啊多表的join查询，针对非唯一索引或非主键索引，或者使用了最左前缀规则的索引查询

range：表示使用索引范围查询，通过索引字段范围获取表中部分数据记录，通常出现在=、<>、>、>=、<、<=、is null、between、in()操作中

index：表示全索引扫描，和ALL类型类似，只不过ALL类型是全表扫描，index是扫描所有的索引，而不是数据。index经常出现在所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。这种情况时。Extra会显示Using index

ALL:全表扫描，这个类型的查询时性能最差的查询之一。这种查询在数据量大的情况下，对数据库的性能是巨大的灾难。

type性能比较：ALL<index<range-index~index_merge<ref<eq_ref<const<system
复制代码

key

此次查询用到的索引
复制代码

key_len

表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否被完全使用，或只有最左部分被适应
复制代码

ref

哪个字段和常数与key一起被使用
复制代码

rows

显示此查询一共扫描了多少行，估计值，原则上rows越少越好
复制代码

filtered

显示此查询条件所过滤数据的百分比
复制代码

extra

额外的信息。常见的有Using filesort，表示MySQL需要额外的排序操作，不能通过索引顺序达到排序结果。一般有Using filesort，都建议优化去掉，这样的查询CPU消耗大；

Using index 覆盖索引扫描，表示查询在索引树中就可以查到所需的数据，不用扫描表数据文件，通常性能不错

Using temporary 查询有使用临时表，一般出现于排序，分组合夺标join的情况，查询效率不高，建议优化。
复制代码

内连接，左连接，右连接作用及区别

左连接：

左边有的，右边没有的为null，以左边的为准
复制代码

右连接：

左边没有的，右边有的为null，以右边的为准
复制代码

内连接：

显示左边右边共有的
复制代码

数据库连接池

数据库连接池是程序启动时创建足够的数据库连接，并将这些连接组成一个连接池，有程序动态的对池中连接进行申请、使用、释放

传统连接数据库的方式：

1、装载数据库驱动
2、通过jdbc建立数据库连接
3、访问数据库、执行sql语句
4、断开连接
复制代码

使用数据库连接池连接数据库

1、程序初始化时创建连接池
2、使用时向连接池申请可用的连接
3、使用完毕，归还连接到池中
4、程序退出，断开所有连接，并释放资源
复制代码

为何使用数据库连接池

访问量大，数据库服务器为每次连接都创建一次数据库连接，极大浪费数据库资源，并容易造成数据库服务内存溢出，宕机等

数据库连接时一种昂贵的资源
复制代码