SQL必知必会（二）性能优化篇

这是博主工作闲时的一些日常学习记录，有些之前很熟悉的，但工作中不常用，慢慢就遗忘了，在这里记录，也是为了激励自己坚持复习，如果有能帮到你，那我将感到非常的荣幸~

---

数据库调优

数据库调优：目标？维度？具体如何分析和思考？

1目标：用户反馈、日志分析、服务器资源监控、数据库内部状况监控

2维度：
合适的DBMS（合适的数据库引擎）、
优化表设计（三范式、查询过多（反三范式））、
优化逻辑查询(子查询优化、视图重写等)、
优化物理查询(索引重复度高、计算字段、联合、多个索引等)、
使用 Redis 或 Memcached 作为缓存(kv数据库)
库级优化(读写分离、分库分表)

---

范式设计

范式NF–>规范 约束–>保证设计正确性和严谨性

目前关系型数据库一共有 6 种范式，按照范式级别，从低到高分别是：1NF（第一范式）、2NF（第二范式）、3NF（第三范式）、BCNF（巴斯 - 科德范式）、4NF（第四范式）和 5NF（第五范式，又叫做完美范式）。

数据库范式设计越高阶，冗余度越高，同时高阶一定满足低阶的要求。

超键：唯一标识元组的属性集
主键(也称主码，候选键中一个)、候选键(也称主属性，唯一，可以多个)、外键（别的表的主键）

INF： 任何属性不能再分

2NF: 一张表的非主属性要完全依赖主属性(一张表只能表达一个意思)

3NF: 非主属性和候选键不存在传递依赖(有依赖传递的也要拆表)

CNF，也叫做巴斯 - 科德范式，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。（商店名 管理员 物品名 数量）

反三范式：增加冗余字段 使用场景：数据仓库(分析数据、历史数据等)

---

索引

索引：帮助数据库管理系统高效获取数据的数据结构。因为索引也需要占用空间和时间，需要维护成本，所以在数据量不大和数据重复多大（不仅要看数值个数还需要看数值的分布情况）的情况下最好不要使用。

种类： 按业务逻辑、物理实现、字段个数等分

业务逻辑：
普通、唯一、主键（唯一+不为null）、全文(全文索引只支持英文、一般使用es和solr)

物理实现：
聚集索引：按主键顺序排序存储数据(内容放在索引后)，一个表只有一个，查询高，增删改效率低
非聚集索引(二级索引或者辅助索引)：单独有张表维护索引，内容不放在索引后面，一个表可以有多个

字段个数:
单一索引：一列
联合索引：多列 最左匹配原则(最左优先的方式进行索引的匹配，如果多个列检索，在定义索引的最左边那个列一定要出现在条件里，如果不出现索引就会失效 )

原理 B+树

B树(平衡的多路搜索树) ：相较于平衡二叉树(磁盘I/O次数太多)，可以有多个结点，高度远小于二叉树，查询效率更高。（文件系统和数据库系统索引结构常用B树实现）

B+树：主流的 DBMS 都支持 B+ 树的索引方式，在B树上做了更改，B树中间节点可以存储数据，而B+树中间节点只能存储索引，同时叶子节点构成一个有序双向链表，按关键字大小从小到大排序。

同样磁盘页大小，B+树可以存储更多节点关键字，单个查询和范围查询B+都是优于B树的，同时B+树查询性能更稳定。

Hash索引

hash算法使用场景：1.提高检索效率 一次计算就能找到 2.对比两个文件内容是否一致

hash索引与B+树索引区别：
1.hash索引不能进行范围查询，hash是无序的。
2.hash索引不支持联合索引的最左匹配原则，它是将所有值混合一起计算值的。
3.hash索引不支持order by,不支持LIKE模糊查询，而B+可以“XX%”优化查询。

另外 MySQL 的 InnoDB 存储引擎还有个“自适应 Hash 索引”的功能，就是当某个索引值使用非常频繁的时候，它会在 B+ 树索引的基础上再创建一个 Hash 索引，这样让 B+ 树也具备了 Hash 索引的优点。

索引使用场景

1. 字段的数值有唯一性的限制，比如用户名
2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
3. 需要经常 GROUP BY 和 ORDER BY 的列
4. UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引
5. DISTINCT 字段需要创建索引
6. 做多表 JOIN 连接操作时，创建索引需要注意以下的原则
   1）不要超过三张 2）最后在后面加where条件 3）连接字段(建立索引) 要求类型相同

索引失效的场景

1. 如果索引进行了表达式计算，则会失效
2. 如果对索引使用函数，也会造成失效
3. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。
4. 当我们使用 LIKE 进行模糊查询的时候，前面不能是 %(XX%)。
5. 索引列尽量设置为 NOT NULL 约束。

物理层面理解数据库

在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。

空间大小：行–>页–>区–>段–>表空间

页的连接：用链表的结构让数据页之间不需要是物理上的连续，而是逻辑上的连续。

磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。同时按照频率将更新的数据从缓冲池刷到磁盘中。

数据缓冲池和查询缓冲的区别

缓冲池不等于查询缓存，他们两个存在共同的特点就是都是通过缓存的机制来提升效率。
缓冲池是服务于数据库整体的IO操作，通过建立缓冲池机制来弥补存储引擎的磁盘文件与内存访问之间的效率鸿沟，同时缓冲池会采用“预读”的机器提前加载一些马上会用到的数据，以提升整体的数据库性能。
而查询缓存是服务于SQL查询和查询结果集的，因为命中条件苛刻，而且只要当数据表发生了变化，查询缓存就会失效，因此命中率低，在MySQL8.0版本中已经弃用了该功能。

选择合适的索引

锁

按锁粒度划分：行锁、页锁、表锁

InnoDB 和 Oracle 支持行锁和表锁。而 MyISAM 只支持表锁

按数据库管理划分：共享锁和排它锁

共享锁也叫读锁或 S 锁，共享锁锁定的资源可以被其他用户读取，但不能修改.。用户在读的时候就信息被锁了，其他用户不能做修改。

给某个表加共享锁(这个时候表就只能读，不能做修改)

LOCK TABLE table_name READ;

给某个表解锁

UNLOCK TABLE;

给某行加锁

select * from table where id = 11 LOCK IN SHARE MODE

排它锁也叫独占锁、写锁或 X 锁。排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。

给某表添加排它锁

LOCK TABLE table_name WRITE;

同样释放锁

UNLOCK TABLE;

给某行加锁

SELECT * FROM table_name WHERE id=2 FOR UPDATE;

解决获取某个范围的意向锁或排它锁，但如果出现包含的更小范围的锁，就获取不到所求的锁的问题

数据库中某个数据表内有锁会向一级的空间示意里面已经上过锁，即意向锁(intent lock)。
如果给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上排他意向锁。(同理，共享锁也是)

当有多个事务对同一数据获得读锁的时候，可能会出现死锁的情况

按程序员角度划分：乐观锁、悲观锁

乐观锁：认为同一数据的并发操作不容易发生，不用数据库的锁机制，采用版本号或者时间戳机制实现—适用于读更多的场景

悲观锁：则是觉得一定会发生冲突的情况，利用数据库自身的锁机制来实现，保证自身的排他性—适用于写更多的场景

以下情况是不会发生死锁的，比如采用乐观锁的方式。另外在 MySQL MyISAM 存储引擎中也不会出现死锁，这是因为 MyISAM 总是一次性获得全部的锁，这样的话要么全部满足可以执行，要么就需要全部等待。

MVCC

mysql默认的隔离等级 可重复读 (解决不可重复读的问题，但不能解读幻读)

不采用锁机制解决，通过乐观锁（MVCC+Next-key Lock）的方式来解决不可重复读和幻读问题

MVCC(Multiversion Concurrency Control)：多版本并发控制技术，会保存数据的历史版本，可以解决
1）读写阻塞，提高并发效率
2）降低死锁
3）解决一致性(快照读)读的问题

快照读：不加锁的正常读
当前读：加锁(共享锁、排它锁)的读取和DML操作

MVCC可以有多种实现方式。

分析一下InnoDB的MVCC实现

InnoDB 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段

InnoDB 将行记录快照保存在了 Undo Log 里，我们可以在回滚段中找到它们（多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo Log 里）
Read View 帮我们解决了行的可见性问题。Read View 保存了当前事务开启时所有活跃（还没有提交）的事务列表。

(在隔离级别为读已提交（Read Commit）时，一个事务中的每一次 SELECT 查询都会获取一次 Read View,而可重复读，则是每次SELECT查询都是用的第一次的Read View)

看到 InnoDB 中，MVCC 是通过 Undo Log （负责MV）+ Read View （负责CC）进行数据读取，Undo Log 保存了历史快照，而 Read View 规则判断当前版本的数据是否可见。

InnoDB的三种行锁

1. 记录锁：针对单个行记录添加锁。
2. 间隙锁：锁住一个范围（索引之间的空隙），但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。
3. Next-Key 锁：锁住一个范围，同时锁定记录本身，相当于间隙锁 + 记录锁，可以解决幻读的问题。
   (在隔离级别为可重复读时，InnoDB 会采用 Next-Key 锁的机制解决幻读问题)