MariaDB&MySQL数据库及操作系统字符集设置及关系

1 LINUX操作系统

1.1 系统LANG变量

可以使用locale -a查看系统支持的字符集。

使用export LANG=zh_CN.utf8或zh_CN.gbk进行设置。

如使用ssh客户端连接服务器，如SecrueCRT等，需要设置与服务器相同的字符集才能正常显示。

2 数据库

2.1 数据库字符集变量

查看数据库系统字符集信息：

这些变量都可以通过set 及set global进行修改，重启后失效。

以下为每个变量介绍：

character_set_client ：客户端字符集，可以通过修改my.cnf中[client]段中的default-character-set，重启客户端生效。
character_set_connection：连接字符集，可以通过修改my.cnf中[client]段中的default-character-set，重启客户端生效。
character_set_database：数据库默认字符集，创建数据库时指定，不指定则默认使用服务器字符集。建表时如不指定字符集则继承数据库默认字符集。Load data时数据文件字符集需与该值一致。
character_set_filesystem ：文件系统字符集，linux系统默认为binary。该值不会影响乱码。
character_set_results：结果字符集，可以通过修改my.cnf中[client]段中的default-character-set，重启客户端生效。
character_set_server：服务器默认字符集，可以通过修改my.cnf中[mysqld]段中的character-set-server，重启服务端生效。如果不配置，则以编译代码时的-DDEFAULT_CHARSET选择为默认值。默认为latin1。
character_set_system：MariaDB系统自用字符集，恒为utf8。该值不会影响乱码。

SET NAMES ‘utf8’; 它相当于下面的三句命令：

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

2.1.1 表以及列字符集

创建表时指定表和列的字符集，如果不指定，则列从表继承，表从character_set_database继承。后期也可通过alter table命令修改。

2.2 SQL脚本

一个用户请求字符集转换完整流程：

1、Mysql客户端以character_set_client解析SQL语句

2、转化为character_set_connection发送到服务端（character_set_connection字符集必须大于等于character_set_client，否则会丢失数据。如utf8>gbk>latin1）

3、服务端转化为内部字符集

（同理，内部字符集必须大于等于character_set_connection，否则会丢失数据。

按照如下规则：

A. 转化为每个数据字段的CHARACTER SET设定值；

B. 若上述值不存在，则继承对应数据表的CHARACTER SET设定值

C. 若上述值不存在，则继承对应数据库的 character_set_database设定值；

D. 若上述值不存在，则继承character_set_server设定值。）

4、最后服务端将操作结果从内部操作字符集转换为character_set_results返回客户端。

sql脚本编码必须与character_set_client一致，否则会出现乱码。

2.3 LOAD导入数据文件

LOAD命令字符集转换完整流程：

1、以LOAD命令里指定的CHARACTER SET解析数据文件（如没有指定，则以character_set_database设定值解析）

2、服务端转化为内部字符集（规则同2.2）

如不在LOAD命令中指定，则数据文件编码必须与character_set_database一致，否则会出现编码错误无法导入。

2.4 导出数据文件

如不在导出命令中指定字符集，则不进行转换，以数据列的字符集直接导出。（如果表的不同字段设置的字符集不同，会导致同一数据文件存在多种编码格式，如：字段a以utf8编码，b以gbk编码。）

如在命令中指定字符集，则按照指定的字符集导出到文件中。

MySQL&MariaDB临时表与临时文件

mysql何时会使用临时表

1、UNION查询；——最新版本的MariaDB10.1中UNION ALL不再使用临时表

2、用到TEMPTABLE算法或者是UNION查询中的视图；

3、ORDER BY和GROUP BY的子句不一样时；

4、表连接中，ORDER BY的列不是驱动表中的；

5、DISTINCT查询并且加上ORDER BY时；

6、SQL中用到SQL_SMALL_RESULT选项时；

7、FROM中的子查询；

8、子查询或者semi-join时创建的表；

可以通过explain查看extra列：Using temporary表示使用临时表

临时表何时会变成磁盘临时表写入临时文件

如果临时表中需要存储的数据量超过了上限（ tmp_table_size 或max_heap_table_size 中取其大者），这时候就需要生成磁盘临时表。

Mariadb的磁盘临时表默认是aria存储引擎的。

所以会在tmpdir目录下生成类似如下的文件：

-rw-rw—-. 1 mariadb mariadb 1.2G Aug 14 10:06 #sql_750a_0.MAD

-rw-rw—-. 1 mariadb mariadb 8.0K Aug 12 17:52 #sql_750a_0.MAI

其中MAD为aria表的数据文件，MAI为aria表的索引文件

在以下几种情况下，会直接创建磁盘临时表：

1、数据表中包含BLOB/TEXT列；

2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符的字符类型列（或者超过 512字节的二进制类型列）；

3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；

4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。

还有什么操作会使用临时文件

仅列出已知的一些操作：

Order by非索引字段
增加索引
创建分区表
Show create table 分区表

等

如何查看是否使用了临时表、磁盘临时表、临时文件

MariaDB [lzk]> show status like ‘Created_tmp%’;

+————————-+——-+

| Variable_name | Value |

+————————-+——-+

| Created_tmp_disk_tables | 3 | —-使用磁盘临时表次数

| Created_tmp_files | 38 | —-使用临时文件次数

| Created_tmp_tables | 9 | —-使用临时表次数

+————————-+——-+

3 rows in set (0.00 sec)

MariaDB 10.1主要新特性

1、10.1中默认包含Galera

2、可以对表、表空间、日志进行加密

需要安装file_key_management 插件

MariaDB > install plugin file_key_management soname 'file_key_management.so';

1	MariaDB > install plugin file_key_management soname 'file_key_management.so';

未安装加密插件的slave可以正确的复制加密的主

使用mysqlbinlog工具无法查看加密后的binlog

# The rest of the binlog is encrypted!
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 39, event_type: 154
ERROR: Could not read entry at offset 285: Error in log format or read error.

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 39, event_type: 154

ERROR: Could not read entry at offset 285: Error in log format or read error.

3、innodb/xtradb页压缩

区别于row_format=compressed，在缓存中会同时存在压缩页与非压缩页；新的页压缩功能只有在写入文件系统前进行压缩。

需要配置innodb-file-format=Barracuda 、 innodb-file-per-table=1

除zlib外，还支持lz4、lzo、lzma、bzip2、snappy压缩算法，但是默认版本不包含，需要先安装上述压缩算法后编译mariadb才能生效。

安装lzo后编译时能识别出该算法，但是编译报错。

但是实际测试page_compressed=1表数据文件并没有压缩，row_format=compressed可以正常压缩

4、复制：

基于domain_id复制过滤器

必须在MASTER_USE_GTID不为no的前提下使用，并且不能同时设置DO_DOMAIN_IDS 和 IGNORE_DOMAIN_IDS，只能选一个。例：

MariaDB [(none)]> CHANGE MASTER TO DO_DOMAIN_IDS=(1,2),MASTER_USE_GTID=current_pos;
Query OK, 0 rows affected (0.16 sec)

1 2	MariaDB [(none)]> CHANGE MASTER TO DO_DOMAIN_IDS=(1,2),MASTER_USE_GTID=current_pos; Query OK, 0 rows affected (0.16 sec)

乐观模式并行复制

增强的半同步复制，收到备机响应后提交事务

新增rpl_semi_sync_master_wait_point配置项：可配置为AFTER_SYNC或AFTER_COMMIT。

row模式下slave上的trigger可以生效

新增slave_run_triggers_for_rbr配置项，使row模式下的slave可以触发slave上独有的trigger。

增强dump线程，多个slave可以更快的并发读取binlog

在特定的并行场景下事务的commit立即完成，避免在很多事务锁冲突时降低吞吐量

MariaDB [lzk]> create table a (a int primary key,b int);
Query OK, 0 rows affected (0.34 sec)
 
MariaDB [lzk]> insert into a select 1,1;
Query OK, 1 row affected (1 min 0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
MariaDB [lzk]> insert into a select 2,1;
Query OK, 1 row affected (0.68 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
MariaDB [lzk]> insert into a select 3,1;
Query OK, 1 row affected (0.67 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
MariaDB [lzk]> insert into a select 4,1;
Query OK, 1 row affected (0.67 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
MariaDB [lzk]> insert into a select 5,1;
Query OK, 1 row affected (0.67 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
MariaDB [lzk]> set global binlog_commit_wait_count=10;
Query OK, 0 rows affected (0.02 sec)
 
MariaDB [lzk]> set global binlog_commit_wait_usec=60000000;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [lzk]> delete from a where a =1;
Query OK, 1 row affected (4.05 sec)—在会话2执行相同语句，则会话1会提前提交。
 
MariaDB [lzk]> delete from a where a =2;
Query OK, 1 row affected (1 min 0.06 sec)—未发生冲突，则等满binlog_commit_wait_usec时间。

MariaDB [lzk]> create table a (a int primary key,b int);

Query OK, 0 rows affected (0.34 sec)

MariaDB [lzk]> insert into a select 1,1;

Query OK, 1 row affected (1 min 0.07 sec)