[toc] # 引言 本系列的文章是[《MySQL 是怎样运行的：从根儿上理解 MySQL》](https://juejin.im/book/5bffcbc9f265da614b11b731?referrer=5bab01c3f265da0a9c787a83)这本浅显易懂的掘金小册的读书笔记。 # 重新认识 MySQL ## 流程模块  ## 连接管理 连接的几种方式： - TCP/IP - 命名管道或共享内存 - Unix 域套接字 一个客户端一个线程，断开链接时不销毁线程，线程池。 ## 解析与优化 首先查询缓存： - 如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。 - 如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如`performance_schema`数据库中的表，那这个请求就不会被缓存。（例如用了`NOW`函数）。 - 如对该表使用了INSERT、 UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。 没有命中缓存，进入语法解析： - 本质上算是一个编译过程，涉及词法解析、语法分析、语义分析等阶段。 随后进入查询优化： - 外连接转换为内连接、表达式简化、子查询转为连接等。最后生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序。`EXPLAIN`语句可以用来查看某个语句的执行计划。 ## 存储引擎 - 常用的存储引擎： |存储引擎|描述| |:--:|:--:| |ARCHIVE|用于数据存档（行被插入后不能再修改）| |BLACKHOLE|丢弃写操作，读操作会返回空内容| |CSV|在存储数据时，以逗号分隔各个数据项| |FEDERATED|用来访问远程表| |InnoDB|具备外键支持功能的事务存储引擎| |MEMORY|置于内存的表| |MERGE|用来管理多个MyISAM表构成的表集合| |MyISAM|主要的非事务处理存储引擎| |NDB|MySQL集群专用存储引擎| - 最常用的就是`InnoDB`和`MyISAM`，有时会提一下`Memory`。 - `SHOW ENGINES;`命令用来查看当前服务端支持的存储引擎。 ``` +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ ``` - `Support`列表示该存储引擎是否可用。 - `DEFAULT`值代表是当前服务器程序的默认存储引擎。 - `Transactions`列代表该存储引擎是否支持事务处理 - `XA`列代表着该存储引擎是否支持分布式事务。 - `Savepoints`代表着该存储引擎是否支持部分事务回滚。 - 建表时可以指定存储引擎。 ```sql CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称; ``` # 启动选项和配置文件 ## 配置文件 类`UNIX`系统中，按照以下顺序寻找配置文件： - `/etc/my.cnf` - `/etc/mysql/my.cnf` - `SYSCONFDIR/my.cnf` - `$MYSQL_HOME/my.cnf` 在配置文件中添加参数： ``` [server] skip-networking default-storage-engine=MyISAM ``` 配置文件根据顺序读取，如果有相同的设置项会覆盖。 ## 系统变量 查看系统变量： ``` SHOW VARIABLES [LIKE 匹配的模式]; ``` ``` mysql> SHOW VARIABLES LIKE 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.01 sec) ``` 变量分为两种作用范围： - `GLOBAL` - `SESSION`（aka. `LOCAL`） 设置系统变量： ``` SET [GLOBAL|SESSION] 系统变量名 = 值; SET [@@(GLOBAL|SESSION).]var_name = XXX; ``` 在查看系统变量时也可以加上： ``` SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式]; ``` 修改`SESSION`的值不会改变`GLOBAL`的值。 ``` mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec) mysql> SET SESSION default_storage_engine = MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | MyISAM | +------------------------+--------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec) ``` 注意：并不是所有系统变量都具有`GLOBAL`和`SESSION`的作用范围，如`max_connections`等。 ## 状态变量 查看状态变量： ``` SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式]; ``` ``` mysql> SHOW STATUS LIKE 'thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 1 | | Threads_running | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) ``` 以上命令来查看所有`Thread`开头的`SESSION`作用范围的状态变量。 # 字符集和比较规则 ## 字符集 在`MySQL`中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以有以下两个概念： - `utf8mb3`：阉割过的`utf8`字符集，只使用1～3个字节表示字符。 - `utf8mb4`：正宗的`utf8`字符集，使用1～4个字节表示字符。 如果要存`emoji`之类的使用`utf8mb4`。 查看支持的字符集： ``` SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式]; ``` ``` mysql> SHOW CHARSET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | ... | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | ... | ascii | US ASCII | ascii_general_ci | 1 | ... | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | ... | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | ... | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | ... | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | ... | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | ... | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.01 sec) ``` 一种语言对应多种比较规则，如去不区分重音等。 MySQL 有4个级别的字符集和比较规则，分别是： - 服务器级别 - 数据库级别 - 表级别 - 列级别 ### 服务器级 查询默认字符集和默认比较规则。 ``` mysql> SHOW VARIABLES LIKE 'character_set_server'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | character_set_server | utf8 | +----------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'collation_server'; +------------------+-----------------+ | Variable_name | Value | +------------------+-----------------+ | collation_server | utf8_general_ci | +------------------+-----------------+ 1 row in set (0.00 sec) ``` 这两个变量可以在配置文件中进行修改。 ``` [server] character_set_server=gbk collation_server=gbk_chinese_ci ``` ### 数据库级 ``` CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; ``` 如不指定，会使用`character_set_database`和`collation_database`这两个系统变量。这两个系统变量是只读的。 ### 表级 ``` CREATE TABLE 表名 (列的信息) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]] ALTER TABLE 表名 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称] ``` 默认不写的话，会集成数据库的规则。 ### 列级 ``` CREATE TABLE 表名( 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列... ); ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称]; ``` 以上，仅修改字符集的情况下，比较规则也会跟着变，如不指定就使用默认。 ## 字符集转换 以下几个系统变量决定了字符集情况： - `character_set_client`：服务器解码请求时使用的字符集 - `character_set_connection`：服务器运行过程中使用的字符集 - `character_set_results`：服务器向客户端返回数据时使用的字符集 这些系统变量的默认值不同操作系统也许不同，类`UNIX`系统里是`utf8`，`Windows`下是`gbk`。 字符集转换过程：  - 服务器认为客户端发送的请求是`character_set_client`编码的。 - 服务器会把结果编码为`character_set_results`发送给客户端。 - `character_set_connection`只表示在服务器处理请求时使用的字符集，它是什么其实不重要，但一定要能涵盖结果集的字符，否则字符会丢失。 通常把这三个变量设置成和客户端一样，这样就减少了转换。 ``` SET NAMES 字符集名; ``` 等价于： ``` SET character_set_client = 字符集名; SET character_set_connection = 字符集名; SET character_set_results = 字符集名; ``` 客户端的设置可以通过配置文件： ``` [client] default-character-set=utf8 ```