MySQL必须了解的关键字

发布时间：2023-07-01 11:11:42 所属栏目：MySql教程来源：

导读：本篇内容主要讲解“MySQL必须了解的关键字有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL必须了解的关键字有哪些”吧!

1、三范

本篇内容主要讲解“MySQL必须了解的关键字有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL必须了解的关键字有哪些”吧!

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

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

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

MySQL数据库在开发运维中，字符集选用规则如下：
如果系统开发面向国外业务，需要处理不同国家、不同语言，则应该选择utf-8或者utf8mb4；
如果只需要支持中文，没有国外业务，则为了性能考虑，可以采用GBK；

3、自定义变量
自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。可以使用set的方式定义。
SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;SELECT id,name from user where create_time > @last_week;

使用自定义变量的注意事项：
使用自定义变量的查询，无法使用缓存；
不能在使用常量或标识符的地方使用自定义变量，比如表名、列名和limit子句中；
自定义变量的生命周期实在一个连接中有效，不能用它们做连接间的通信；
避免重复查询刚刚更新的数据

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

一般都这样做：
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条件，也不能使用索引。

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

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

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

6、缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据，有时候还需要创建一张完全独立的汇总表或缓存表。
缓存表用来存储那些获取很简单，但速度较慢的数据；
汇总表用来保存使用group by语句聚合查询的数据；

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

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

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

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

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

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

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

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

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

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

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

MySQL必须了解的关键字有哪些

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

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

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

（2）使用查询缓存需谨慎
打开查询缓存对读和写操作都会带来额外的消耗：
读查询在执行之前要先检查是否命中缓存；
如果读查询可以被缓存，那么当完成执行后，MySQL如果发现缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗；
对写操作也有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大的系统消耗；
虽然如此，查询缓存仍然会给系统带来性能的提升。但是，上述的额外消耗也可能不断增加，再加上对查询缓存操作是一个加锁排它操作，这个消耗也不小。

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

到此，相信大家对“MySQL必须了解的关键字有哪些”有了更深的了解，不妨来实际操作一番吧！更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!