最近的学习会围绕MySQL中较零散但是又不可或缺的点展开，例如MySQL的字符集相关，MySQL的表空间知识点，MySQL的行溢出，InnoDB引擎的存储相关，比较偏理论一些，但是在实际生产中，你会发现如果缺乏对这些知识的掌握，可能会一不小心踩到坑。
计算机只能存储二进制数据，因此，实际存储字符串时，需要建立字符与二进制数据的映射关系。将字符映射成二进制数据被称作编码，反过来则叫做解码，正所谓无规矩不成方圆，因此人们制定了各种编码规则，即字符集。由于不同的人群制定的规则不一样，字符集也有很多种，不必都了解，如下图所示，掌握这常用的几种即可。
确认某种字符集后，有时可能需要比较两个字符的大小，因此每种字符集会对应若干种比较规则。在MySQL中可分别使用如下命令查看字符集和比较规则:
查看字符集:
show character set like "%utf8%";或show charset like "%utf8%";
查看比较规则:
show collation like "%utf8%";
另外，MySQL支持4个级别的字符集和比较规则,分别是服务器级别，数据库级别，表级别，列级别。级别依次向下兼容，例如，假设某个库单独设置了数据库级别的字符集和比较规则，则以设置的字符集和比较规则为准，如果未设置，则默认使用服务器的字符集和比较规则。这类似于编程语言中的父类和子类之间的继承和重写。字符集和比较规则可以在线修改，修改其中一个，另一个也会对应变化。
修改不同级别字符集命令参考如下:
修改服务器字符集:
SET global character_set_server =utf8;
修改数据库级别字符集:
ALTER DATABASE test_db CHARACTER SET utf8 COLLATE utf8_general_ci;
修改表级别字符集:
ALTER TABLE student CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
修改列级别字符集:
ALTER TABLE student MODIFY name VARCHAR(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
MySQL中server层和存储层是分离的，因此可支持不同类型的存储引擎，比如InnoDB，MyISAM，Memory,但是现在最常用的还是InnoDB引擎。InnoDB存储引擎文件被称作表空间。是一个逻辑存储概念，可以有多个文件组成，支持裸设备。
不同版本的表空间有所区别，以MySQL5.7为例:
the System Tablespace(系统表空间)
系统表空间是Innodb数据字典，双写缓冲区，更改缓冲区和撤销日志的存储区域，如果表不是在单独表文件或通用表空间创建的，还可能包含表和索引数据。系统表空间一或多个数据文件，对应数据目录中的ibdataXX的文件。其大小和数量由innodb_data_file_path决定，示例:innodb_data_file_path=ibdata1:10M:autoextend，系统表空间不可以通过更改其大小属性来改变现有系统表空间数据文件的大小，对于增加系统表空间，可以通过设置配置自动扩展或者添加另外一个数据文件来增加系统表空间的大小。但是autoextend只能对最后一个数据文件设置。如果需要减少系统表空间大小，需要先转储所有数据库，通知服务器，删除数据库和InnoDB日志文件后重新启动服务器并导入转储文件。
File-Per-Table Tablespace(单独表空间)
每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。在MySQL5.6及以后默认启用单独表空间。由innodb_file_per_table变量控制，示例，innodb_file_per_table=ON，innodb_file如果禁止，则会在系统表空间中创建表。
General Tablespace(通用表空间)
通用表空间是InnoDB使用create tablespace语法创建的共享表空间。与系统表空间类似，是能够为多个表存储数据的共享表空间。相对于独立表空间而言，在存储表空间元数据时消耗更少的内存。MySQL不支持将通用表空间创建在数据目录的子目录下，需要提前在数据目录之外创建单独的目录。示例:create tablespace 'ts1' add datafile '/my/tablespace/ts1.ibd' engine=InnoDB。
Undo Tablespace(撤销表空间)
撤销表空间中包含撤销日志，是如何撤销事务对聚集索引记录的最新更改的信息的记录集合。撤销记录默认存储在系统表空间中，也可以存储在一个或多个撤销表空间中，使用撤销表空间可以减少任何一个表空间中撤销日志所需的空间量。撤销表空间数量由innodb_undo_tablespace选项控制，只能在初始化MySQL实例时配置。测试表空间相关的常见配置如下:
innodb_undo_tablespaces:定义撤销表空间的数量，如果不确定最佳值，建议设置偏高。
innodb_undo_directory：撤销表空间的目录位置，如果未指定，则会在数据目录中创建撤销表空间。
innode_rollback_segments：回滚段的数量，默认值为128，也是最大值，一个回滚段总是分配给系统表空间，32个回滚段保留给临时表空间，因此，如果要将回滚段分配给撤销表空间，需要设置该值大于33，添加撤销表空间时，系统表空间中的回滚段将变为非活动状态。
innodb_undo_log_truncate：设置该值为on时，开启截断撤销表空间功能。
innodb_max_undo_log_size：当开启截断撤销表空间功能时，如果撤销表空间文件大小超过该变量设置的值，撤销表空间会被截断。
innodb_purge_rseg_truncate_frequency：清除线程负责清空和截断撤销表空间，清除线程截断撤销表空间的频率由该变量控制，可动态设置，默认为128，即清除线程每调用128次，就会查找撤销表空间以截断一次，当撤销表空间被截断时，撤销表空间中的回滚段将被停用，其他undo表空间中的活动回滚段承担整个系统负载的责任。这可能会导致性能下降。
The Temporary Tablespace(临时表空间)
从MySQL5.7开始引入，非压缩，用户创建的临时表和磁盘内部临时表在共享临时表空间中创建，临时表空间数据文件路径和属性由innodb_temp_data_file_path参数定义，如果未制定，默认在innodb_data_home_dir目录下创建一个名为ibtmp1的自动扩展数据文件，大小略大于12MB,在MySQL5.6中，非压缩临时表的单个文件在临时文件目录中的每个表表空间中创建。如果禁用了innodb_file_per_table，则在数据目录中的系统表空间中创建。临时表空间在服务器正常关闭或中止初始化时被删除，并在每次服务器启动时重新创建。临时表被删除时，释放的表空间可以重新用于新的临时表，但是数据文件会保持扩展大小，因此使用临时表的长时间运行的查询可能会导致大型数据文件。可通过配置最大值进行限制，示例:innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
可使用alter table ... tablespace命令将表移动到现有的通用表空间，新的file-per-table表空间或系统表空间。
了解行溢出可以从三个方面着手，行溢出的定义，行溢出的原因，行溢出的处理方式。
行溢出指的是在数据存储到数据页的过程中，由于某一列数据非常多，只会存储其中的一部分数据到本页或者将数据存储到其他页面，只在本页存储其他页面地址的一个过程。
行溢出的原因，MySQL中规定一个页中至少存放两行记录，考虑极端情况，每行记录都只有一个列，在MySQL中，每个页存放额外信息的空间为136字节，每个记录需要的额外信息是27字节，假设页的大小为默认大小16KB，列中存储的数据字节数为x,则发生行溢出的条件为：136 + 2 * (27 + n) > 16384,即x > 8098,当然，这是极端情况，正常只需要知道如果一个行中存储了很大的数据，便有可能发生行溢出。
对于行溢出的处理方式，在compact和redundant行格式中，如果某列数据很大，会将该列的前768个字节的数据和指向其他页的地址存储在记录的真实数据处，剩下的数据存放到其他页中。在dynamic和compressed行格式中，会将所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。
独立表空间与共享表空间相比，有优势也有不足：
优势：truncate或者drop在独立表空间中创建的表后，磁盘空间会返还给操作系统，而对于共享表空间(系统表空间或通用表空间)不会。即共享表空间数据文件在表被删除或截断后不会缩小。另外，对独立表空间里的表执行alter操作时需要的额外的空间最终会释放给操作系统，共享表空间不会，另外，独立表空间对truncate操作更友好。更多的，对于数据损坏，表大小监控，并发写入等方法，独立表空间都更占优势。
不足:独立表空间里单个表中的空闲空间其他表无法使用，可能存在更多碎片空间。会导致fsync操作总数，文件句柄数以及文件描述符增多，如果表较多时，可能会影响性能。
我们知道，计算机只能识别0和1，我们操作的字符串会按照某种字符集规则在计算机上进行编码和解码，在MySQL中，客户端发送给服务器的请求和服务器返回给客户端的结果本质上都是字符串，从发送请求到返回结果这个过程中涉及多次字符集转换。这其中涉及到三个系统变量:
character_set_client:服务器解码请求时使用的字符集;
character_set_connection:服务器处理请求时会将请求字符串从character_set_client转换为character_set_connection;
character_set_results:服务器向客户端返回数据时使用的字符集;
整个发起请求到返回结果的流程如下:
1.客户端发起一个请求，使用操作系统的字符集编码向服务器发送一个请求字符串;
2.服务器将请求字符串的字符集从character_set_client转换为character_set_connecition;
3.如果列使用的字符集和character_set_connection不一致，将字符集转换为列使用的字符集;
4.将查询结果从具体的列的字符集转换为character_set_results返回请求结果给客户端;
5.客户端使用操作系统的字符集解码响应字符串;
因此，在此过程中，如果客户端使用的字符集和character_set_clients不一致，可能会导致请求失败，如果和character_set_results不一致的和，可能会出现乱码。
解决方法:
将character_set_client，character_set_connection, character_set_results三个变量设置为和客户端使用的字符集一致，可以单个设置，也可以通过set global names 字符集名一条命令设置，或者在配置文件中设置default-character-set=字符集名。
在compact行格式中，对于变长字段，会更加列的内容实际占用的字节数来决定是用一个还是两个字节来表示真实数据占用的字节数，那么InnoDB在读记录的变长字段列表时，如何区分正在读的某个字节时一个单独的字段长度还是半个呢，这个是通过该字节的第一个二进制为来判断的，如果第一位为0，则该字节就是一个单独的字段长度，如果为1，则该字节为半个字段长度。是不是很巧妙，01111111对于127，11111111对应255，与上面的规则相对应。
今天学习的内容是MySQL的字符集，表空间，行格式以及行溢出问题。对应的生产上可能遇到的问题有乱码问题，共享表空间过大占满磁盘空间等问题，好了，先就到这吧，下篇笔记会围绕InnoDB存储页的相关知识点展开，周末快乐。
END
原文作者：DBA修行之路；如有侵权，请联系管理员删除。
