mysql数据库详解（四）：基础知识

作为一个稀有的Java妹子，所写的所有博客都只是当作自己的笔记，留下证据自己之前是有用心学习的～哈哈哈哈（如果有不对的地方，也请大家指出，不要悄悄咪咪的不告诉我）

一、数据库三大范式

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

二、MySQL存储引擎MyISAM与InnoDB区别

1、常用的存储引擎有以下：

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

/	MyIASM	Innodb
存储结构	每张表被存储为三个文件，frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	存储为一个文件，
文件格式	数据和索引是分别存储的，数据.MYD，索引.MY	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
select	更优
insert，update，delete		更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
哈希索引	不支持	支持
全文索引	支持	不支持

2、MyISAM索引与InnoDB索引的区别

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

三、事务

1、事务的四大特性(ACID)

A：原子性，事务是不可分隔的，最小单位
C:一致性，一次事务要么全部执行，要么全部不执行
I：隔离性，不同的事务之间是隔离的，一个事务看不到其他事务提交的结果
D:持久性，事务执行完后，数据是永久保存

2、事务的术语

开启事务
结束事务
提交事务
回滚事务：出现异常时，为了保证一致性

3、什么是脏读？幻读？不可重复读？

脏读：一个事务还未提交时，另一个事务读取到还未提交的数据，因为事务可能会回滚，所以在没提交之前的数据都是脏数据，读到这些脏数据就是脏读。
不可重复读：事务之间不隔离导致的，在同一个事务里多次查询一个字段，结果不一样，这是因为在多次查询之间另外的事务提交了更新了该字段。
幻读：事务之间不隔离导致，在同一事务里多次查询表的行数，结果不一样，因为在查询之间另外的事务提交插入或者删除了新的记录。

4、事务的几种隔离级别

读未提交(READ-UNCOMMITTED)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
读提交(READ-COMMITTED)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
可重复读(REPEATABLE-READ)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
串行化(SERIALIZABLE)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

mysql默认的是可重复读级别。

5、事务实现的原理

参考链接：https://blog.youkuaiyun.com/whoamiyang/article/details/51901888
数据库使用MVCC(多版本并发控制)提高并发性能，在不使用锁的情况下，处理读写冲突，也是乐观锁的一种实现。使用的是快照读的方式，保存数据在某个时间点的快照。

数据库的每行记录都有四个隐式字段：
1、创建时间，其实存放的是事务ID
2、更新时间，存放的是更新或删除事务的ID
3、回滚指针，指向上一个版本
4、隐藏主键（表未指定主键时使用）
每个事务ID可以看作是一个版本，事务ID是从1开始递增。

select：查询出创建时间版本<=当前事务ID的数据，或者删除时间版本>当前事务ID的数据
这样就能保证多次查询出来的数据不会因为其他事务的提交而不同

update：将原来数据的更新时间版本设为当前事务ID，然后插入一条数据，创建时间版本是当前事务ID

delete：将数据的更新时间版本设为当前事务ID

6、springboot如何开启事务

有两种，一种是声明式事务，一种是注解式
声明式我用的比较少，一般是用的注解式，只需要在类或者方法上使用@Transactional即可，Spring的aop会自动的在加了该注解的方法里添加事务的开启、提交和回滚。

四、锁

锁是为了协调在高并发情况下，多个线程对同一资源操作的机制，使得竞争的线程可以分先后顺序的来访问修改同一资源。

1.锁的分类

共享锁：其他事务可以读，不能写
排他锁：其他事务不能读不能写

表锁：锁整张表，开销小，加锁快，不会出现死锁
行锁：锁某一行，开销大，加锁慢，会出现死锁

意向共享锁：innodb里的表锁，在加行共享锁时自动加这个锁
意向排他锁：innodb里的表锁，在加行排他锁时自动加这个锁

间隙锁：where后面不是等值时，是范围时就会加这个锁

2.不同引擎的加锁方式

MyISAM：
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，再执行更新操作
（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

InnoDB：
InnoDB即支持行锁也支持表锁，为了使这两种锁同时存在，表锁使用意向锁命名
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
加锁方式：
1、意向锁是 InnoDB 自动加的， 不需用户干预。
2、对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
3、对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁：
共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

3.锁是否用到

InnoDB 是通过给索引上的索引项加锁来实现的，所以如果没有使用到索引，则不会加锁，可以通过explain来看执行计划中sql是否有用到索引

4.死锁

1、当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁

2、发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

5.避免死锁

1、如果有写操作时，可以先使用select for update加排他锁，即使这些行的更改语句是在之后才执行的。
2、通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

五、慢日志

1.定义

慢日志是mysql可以监控运行时间超过阈值的sql语句，并且记录到文件或者表中。默认是关闭的，因为监控慢查询是比较消耗性能的，阈值是long_query_time这个参数设置的值，默认是10s。

2.相关的参数

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。