学习mysql必须了解的13个关键字（总结分享）-百家乐凯发k8

php入门到就业线上直播课：进入学习

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

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;

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

• 使用自定义变量的查询，无法使用缓存；

• 不能在使用常量或标识符的地方使用自定义变量，比如表名、列名和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会立刻返回结果，跳过解析、优化和执行过程。

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

（1）mysql如何判断缓存命中

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

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

（2）使用查询缓存需谨慎

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

• 读查询在执行之前要先检查是否命中缓存；

• 如果读查询可以被缓存，那么当完成执行后，mysql如果发现缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗；

• 对写操作也有影响，因为当向某个表写入数据的时候，mysql必须将对应表的所有缓存设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大的系统消耗；

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

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

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

10、存储过程

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

存储过程的优点：

• 减少网络流量

• 提高执行速度

• 减少数据库连接次数

• 安全性高

• 复用性高

存储过程的缺点：

• 可移植性差

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，也支持自然语言全文索引和布尔全文索引。