2021-09-23

Mysql 部分知识整理

1.三大范式
第一范式：字段具有原子性，不可再分（字段单一职责）
第二范式：满足第一范式，每行应该被唯一区分，加一列存放每行的唯一标识符，称为 主键（都要依赖主键）
第三范式：满足一二范式，且一个表不能包含其他表已存在的非主键信息（不间接依赖-不存在其他表的非主键信息）
（1）简单归纳：
　　第一范式（1NF）：字段不可分；
　　第二范式（2NF）：有主键，非主键字段依赖主键；
　　第三范式（3NF）：非主键字段不能相互依赖。
（2）解释：
　　1NF：原子性。 字段不可再分,否则就不是关系数据库;；
　　2NF：唯一性 。一个表只说明一个事物；
3NF：每列都与主键有直接关系，不存在传递依赖。
1.1范式优点与缺点：
优点：范式化，重复冗余数据少，更新快，修改少，查询时更少的distinct
缺点：因为一个表不存在冗余重复数据，查询可能造成很多关联，效率变低，可能使一些索引策略无效，范式化将列存在不同表中，这些列若在同一个表中可以是一个索引。

2.、MyISAM与InnoDB区别
2.1 InnoDB聚簇索引，MyISAM非聚簇索引
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
2.2 InnoDB支持事务、外键、行锁表锁，MyISAM不支持事务、外键、只支持表锁
2.3 select count()
InnoDB引擎并不适合使用select count()查询总行数
MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count()的时候会直接返回这个数，效率很高(没有where查询条件)。
InnoDB引擎并没有直接将总数存在磁盘上，在执行count()函数的时候需要一行一行的将数据读出来，然后累计总数。
2.4 MyISAM查询更优，InnoDB更新更优
2.5 都是B+tree索引
2.6 MyISAM支持全文索引，InnoDB5.6后支持

1. 事务ACID特性
   原子性：一些列操作要么全部成功，要么全部失败
   隔离性：事务的结果只有提交了其他事务才可见
   一致性：数据库总时从一个一致状态变到另一个一致状态（事务修改前后的数据总体保证一致 转账）
   持久性：事务提交后，对数据修改永久的
2. 事务的并发问题：

• 脏读：读到未提交的数据

• 不可重复读：一个事务下，两次读取数据不一致（侧重内容数据的修改）

• 幻读：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B
  新插入的数据 称为幻读（侧重新增或删除，插入数据读到多了一行）

隔离级别原理及解决问题分析：

• 读未提交：原理：直接读取数据，不能解决任何并发问题

• 读已提交：读操作不加锁，写操作加排他锁，解决了脏读。原理：利用MVCC实现，每一句语句执行前都会生成Read View（一致性视图）

• 可重复读：MVCC实现，只有事务开始时会创建Read View，之后事务里的其他查询都用这个Read

  View。解决了脏读、不可重复读，快照读（普通查询，读取历史数据）使用MVCC解决了幻读，当前读（读取最新提交数据）通过间隙锁解决幻读（lock
  in share mode、for update、update、detete、insert），间隙锁在可重复读下才生效。

（默认隔离级别）
可串行化：原理：使用锁，读加共享锁，写加排他锁，串行执行
幻读注重你插入新数据都修改改到了新插入的数据，而不可重复读是你修改了某个数据，两次查询得到不一致结果

• 脏页？怎样刷新脏页？
  内存数据页和磁盘数据页不一致。

• 刷脏页情景：

• redo log写满了，停止所有更新操作，将checkpoint向前推进，推进那部分日志的脏页更新到磁盘。

• 系统内存不够，需要将一部分数据页淘汰，如果是干净页，直接淘汰就行了，脏页的话，需要全部同步到磁盘。

  mysql自认为空闲时
  mysql正常关闭之前

5.MYSQL调优篇

• 一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因
(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)、执行的时候，遇到锁，如表锁、行锁。
(3)、sql写的烂了
2、这条 SQL 语句一直执行的很慢，则有如下原因
(1)、没有用上索引或则索引失效：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)、有索引可能会走全表扫描
怎样判断是否走全表扫描：
索引区分度（索引的值不同越多，区分度越高），称为基数，而数据量大时不可能全部扫描一遍得到基数，而是采样部分数据进行预测，那有可能预测错了，导致走全表扫描。

• sql语句

1、分页查询优化
该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。

select * from tb_sku where id>20000 limit 10;
2、优化insert语句
多条插入语句写成一条
在事务中插数据
数据有序插入（主键索引）

• 数据库结构优化

1、将字段多的表分解成多个表
有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。
2、对于经常联合查询的表，可以考虑建立中间表