学习MySQL必须了解的13个关键字，你get了吗？

1、三范式

• 第一范式每个表的每一列都要保持它的原子性也就是表的每一列是不可分割的
• 第二范式在满足第一范式的基础上每个表都要保持唯一性也就是表的非主键字段完全依赖于主键字段
• 第三范式在满足第一范式和第二范式的基础上表中不能产生传递关系要消除表中的冗余性

2、字符集

字符集规定了字符在数据库中的存储格式比如占多少空间支持哪些字符等等。不同的字符集有不同的编码规则在有些情况下甚至还有校对规则的存校对规则是指一个字符集的排序在运维和使用MySQL数据库中选取合适的字符集非常重要如果选择不恰当轻则影响数据库性能严重的可能导致数据存储乱码。

常见的MySQl字符集主要有以下四种

字符集	长度	说明
GBK	2	支持中文但不是国际通用字符集
UTF-8	3	支持中英文混合场景是国际通用字符集
latin1	1	MySQL默认字符集
utf8mb4	4	完全兼容UTF-8用四个字节存储更多的字符

MySQL数据库在开发运维中字符集选用规则如下

1. 如果系统开发面向国外业务需要处理不同国家、不同语言则应该选择utf-8或者utf8mb4
2. 如果只需要支持中文没有国外业务则为了性能考虑可以采用GBK

3、自定义变量

自定义变量是一个用来存储内容的临时容器在连接MySQL的整个过程中都存在。可以使用set的方式定义。

SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

SELECT id,name from user where create_time > @last_week;

使用自定义变量的注意事项

1. 使用自定义变量的查询无法使用缓存
2. 不能在使用常量或标识符的地方使用自定义变量比如表名、列名和limit子句中
3. 自定义变量的生命周期实在一个连接中有效不能用它们做连接间的通信

避免重复查询刚刚更新的数据

如果在更新行的同时又想获得该行的信息要怎么做才能避免重复的查询呢

一般都这样做

update user set update_time = now() where id = 1;
select update_time from user where id = 1;

使用自定义变量可以对其进行优化

update user set update_time = now() where id = 1 and @now := now();
select @now;

看上去还是两个查询但是第二次查询无须访问任何数据表所以会快很多。

4、选择优化的数据类型

MySQL支持的数据类型非常多选择正确的数据类型对于获得高性能至关重要。

1更小的

一般情况下应该尽量使用较小的数据类型更小的数据类型通常更快因为占用更少的磁盘、内存和CPU缓存处理时需要的CPU周期更短。

2更简单的

简单的数据类型通常需要更少的CPU周期整形比字符串类型代价更低因为字符集和校验规则使字符比较比整形比较更复杂。

3尽量避免NULL

很多表都包含可为NULL的列即使应用程序并不需要保存NULL也是如此因为可为NULL是列的默认属性通常情况下最好指定列为NOT NULL。

如果查询中包含可为NULL的列对MySQL来说更难优化因为可为NULL的列使索引、索引统计和值的比较都更复杂。可为NULL的列会使用更多的存储空间在MySQL里也需要特殊处理可为NULL的列被索引时每个索引记录需要一个额外的字节在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引。

5、视图

视图view是一种虚拟存在的表是一个逻辑表本身并不包含数据。作为一个select语句保存在数据字典中的。对多张表的复杂查询使用视图可以简化查询当视图使用临时表时无法使用where条件也不能使用索引。

单表视图一般用于查询和修改会改变基本表的数据多表视图一般用于查询不会改变基本表的数据。

使用视图的目的是为了保障数据安全性提高查询效率。

视图的优势

1. 使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件对用户来说已经是过滤好的复合条件的结果集。
2. 使用视图的用户只能访问他们被允许查询的结果集对表的权限管理并不能限制到某个行某个列但是通过视图就可以简单的实现。
3. 一旦视图的结构确定了可以屏蔽表结构变化对用户的影响源表增加列对视图没有影响源表修改列名则可以通过修改视图来解决不会造成对访问者的影响。

6、缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据有时候还需要创建一张完全独立的汇总表或缓存表。

• 缓存表用来存储那些获取很简单但速度较慢的数据
• 汇总表用来保存使用group by语句聚合查询的数据

对于缓存表如果主表使用InnoDB用MyISAM作为缓存表的引擎将会得到更小的索引占用空间并且可以做全文检索。

在使用缓存表和汇总表时必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序但是定期重建并不只是节省资源也可以保持表不会有很多碎片以及有完全顺序组织的索引。

当重建汇总表和缓存表时通常需要保证数据在操作时依然可用这就需要通过使用影子表来实现影子表指的是一张在真实表背后创建的表当完成了建表操作后可以通过一个原子的重命名操作切换影子表和原表。

为了提升读的速度经常建一些额外索引增加冗余列甚至是创建缓存表和汇总表这些方法会增加写的负担妈也需要额外的维护任务但在设计高性能数据库时这些都是常见的技巧虽然写操作变慢了但更显著地提高了读的性能。

7、分区表

通常情况下同一张表的数据在物理层面都是存放在一起的。随着业务增长当同一张表的数据量过大时会带来管理上的不便。而分区特性可以将一张表从物理层面根据一定的规则将数据划分为多个分区多个分区可以单独管理甚至存放在不同的磁盘/文件系统上提升效率。

分区表的优势

1. 数据可以跨磁盘存储适合存储大量数据
2. 数据管理起来很方便以分区为单位操作数据不影响其他分区的正常运行
3. 查询时可以通过锁定分区的特性缩小查询范围提高查询性能

8、外键

外键通常都要求每次在修改数据时都要在另外一张表中进行一次额外的查询操作虽然InnoDB强制外键使用索引但还是无法消除这种约束检查的开销。如果外键的选择性很低则会导致一个选择性很低的索引。

不过在某些场景下外键会提升一些性能比如想确保两个相关表始终有一致的数据那么使用外键比在应用程序中检查一致性的性能要高的多此外。外键在相关数据的删除和更新上也比在应用中维护要更高效不过外键维护操作时逐行进行的这样的更新会比批量删除和更新要慢些。

外键约束使查询时额外访问一些别的表也就是需要额外的锁。如果向子表中写入一条记录外键约束会让InnoDB检查对应的父表的记录也就是需要对父表的对应记录进行加锁操作来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待甚至会导致一些死锁。因为没有直接访问这些表所以这类死锁问题很难排查。

所以在目前的很多项目中为了性能的考虑已经不使用外键了。

9、查询缓存

MySQL查询缓存保存查询返回的完整结果当查询命中该缓存MySQL会立刻返回结果跳过解析、优化和执行过程。

查询缓存系统会跟踪查询中涉及的每个表如果这些表发生变化那么和这个表相关的所有的缓存数据都将失效这种机制效率看起来比较低因为数据表变化时可能对查询结果并没有影响但是这种简单实现代价很小而这点对于一个非常繁忙的系统来说非常重要。

1MySQL如何判断缓存命中

判断是否命中时MySQL不会解析而是直接使用SQL语句和客户端发送过来的其它原始信息。任何字符上的不同例如空格、注释丢回导致缓存的不命中。通常使用统一的编码规则是一个好的习惯会让你的系统运行的更快。

当查询语句中有一些不确定的数据时不会被缓存比如函数now()。实际上如果缓存中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL系统表、或者任何包含列级别权限的表都不会被缓存。

2使用查询缓存需谨慎

打开查询缓存对读和写操作都会带来额外的消耗

1. 读查询在执行之前要先检查是否命中缓存
2. 如果读查询可以被缓存那么当完成执行后MySQL如果发现缓存中没有这个查询会将其结果存入查询缓存这会带来额外的系统消耗
3. 对写操作也有影响因为当向某个表写入数据的时候MySQL必须将对应表的所有缓存设置失效。如果查询缓存非常大或者碎片很多这个操作就可能会带来很大的系统消耗

虽然如此查询缓存仍然会给系统带来性能的提升。但是上述的额外消耗也可能不断增加再加上对查询缓存操作是一个加锁排它操作这个消耗也不小。

对InnoDB用户来说事务的一些特性会限制查询缓存的使用。当一个语句在事务中修改了某个表在事务提交前MySQL都会将这个表对应的查询缓存设置失效因此长时间运行的事务会大大降低查询缓存的命中率。

3如何分析和配置查询缓存

10、存储过程

存储过程是一组为了完成特定功能的SQL 语句集合经编译后保存在数据库中通过指定存储过程的名字并给出参数的值也可以返回结果。

存储过程的优点

1. 减少网络流量
2. 提高执行速度
3. 减少数据库连接次数
4. 安全性高
5. 复用性高

存储过程的缺点

1. 可移植性差

11、事务

事务内的语句要么全执行要么全不执行。事务具有ACID特性ACID表示原子性atomicity、一致性consistency、隔离性isolation、持久性durability。

1原子性atomicity

一个事务必须被视为一个不可分割的最小工作单元整个事务中的所有操作要么全执行提交成功要么全不失败回滚。

2一致性consistency

数据库总是从一个一致性的状态转换到另一个一致性的状态。

3隔离性isolation

一个事务所做的修改在最终提交以前对其它事务是不可见的。

4持久性durability

事务一旦提交则七所做的修改就会永久的保存在数据库中。

12、索引

索引是存储引擎用于快速查找记录的一种数据结构。我觉得数据库中最重要的知识点就是索引。

存储引擎以不同的方式使用B-Tree索引性能也各有不同各有优劣。例如MyISAM使用前缀压缩技术使得索引更小但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的并且每一个叶子页到根的距离相同。

B-Tree索引能够加快访问数据的速度因为存储引擎不再需要进行全表扫描来获取需要的数据取而代之的是从索引的根结点开始进行搜索。根结点的槽中存放了指向子结点的指针存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值要么该记录不存在。

叶子节点比较特别它们的指针指向的是被索引的数据而不是其他的节点页。B-Tree对索引列是顺序组织存储的所有很适合查找范围数据。B-Tree适用于全键值、键值范围或键前缀查找。
因为索引树中的节点是有序的所以除了按值查找之外索引还可以用于查询中的order by操作。一般来说如果B-Tree可以按照某种方式查找到值那么也可以按照这种方式用于排序。

13、全文索引

全文索引的目的是 通过关键字的匹配进行查询过滤基于相似度的查询而不是精确查询。

全文索引利用分词技术分析出文字中某关键字的频率和重要性并按照一定的算法智能的筛选出我们想要的结果。

全文索引一般用于字符串中某关键字的查询比如char、varchar、text也支持自然语言全文索引和布尔全文索引。

MySQL进阶实战系列文章

MySQL进阶实战 1数据类型与三范式

MySQL进阶实战 2那些年学过的事务

MySQL进阶实战 3mysql索引详解上篇

MySQL进阶实战4那些年学过的索引下篇

MySQL进阶实战5为什么查询速度会慢

SQL性能优化的21个小技巧

mysql索引详解

MySql基础知识总结SQL优化篇

哪吒精品系列文章

Java学习路线总结搬砖工逆袭Java架构师

10万字208道Java经典面试题总结(附答案)

Java基础教程系列

Spring Boot 进阶实战