本系列文章主要是本人在游戏服务端开发过程中，遇到的一些不那么为人熟知但我又觉得比较重要的MySQL知识的介绍。希望里面浅薄的文字能为了提供一点点的帮助。
MySQL除了DDL语句以外，还需要使用其他的命令来辅助我们对MySQL进行操作。这里我介绍下我工作中会用到，并且我感觉比较重要的语句给大家。我将它们大致分为辅助性语句和系统操作语句，辅助性语句：看看线程状况、看看SQL写的怎么样；系统操作语句：杀线程、整理表空间。
show processlist：
这个语句用于查看MySQL服务器中正在或准备工作的线程集信息（不同权限用户看到的范围是不一样的）。这个语句一般是在数据库出现一些异常的情况下才会使用，比如怀疑出现死锁、有SQL卡住很长时间等等。下图是我在本地执行的该命令的结果：
image.png
根据上图我解释下各个字段的意思：
| 字段 | 含义 | 作用 |
|---|---|---|
| Id | 该线程的id | Kill MySQL指定线程时就是用这个值 |
| User | 持有该线程的用户 | |
| Host | 持有该线程的主机 | 可以通过该值确定执行异常的语句是来自哪个客户端，帮助定位问题 |
| db | 线程的默认数据库 | |
| Command | 代表客户端执行命令的类型 | 当什么命令都没在执行就是Sleep表示空闲状态。更多命令类型请戳这里； |
| Time | 线程处于当前状态（State字段）的时间（秒） |
|
| State | 指示线程正在执行的操作的操作、事件或状态 | 如果线程在某状态下停留了很久（Time字段的值），那么可能需要调查一下是什么问题。更多线程状态请戳这里
|
| Info | 线程正在执行的语句，当为NULL表示没有执行任何语句。 |
确定执行异常的语句是什么，再通过Host帮助准确定位问题 |
关于MySQL的默认线程模型这里也顺便说一下。MySQL线程模型默认是来一个连接就创建一个线程为这个连接服务。这种模式和早期的Web服务器类似：来一个请求就创建一个线程为这个请求服务。大家可以使用MySQL客户端（我用的是Navicat）打开多个命令列界面确认一下。当你每多打开一个界面，show processlist命令的结果集就会多显示一条记录，表示MySQL服务器为你这个连接创建了一条新的线程。下图红色部分就是我打开一个新界面多出来的一条线程:
image.png
关于
show processlist的结果集你也可以在information_schema库的PROCESSLIST表找到这些线程信息（有些版本可能在performance_schema库中）。
explain：
explain语句是用于分析一条SQL如何在MySQL执行的。当我们写一些复杂SQL的时候，必须要使用这条语句分析一下自己写的SQL，比如索引用的怎么样、查询的范围如何、语句的结构等等。注意explain只能分析select、update、insert、delete、replace这几个对表内数据操作的语句。如果被分析的SQL是组合查询，那么返回结果可能含有多条数据。下图是我用explain分析一条简单select语句的结果：
image.png
user表的表结构：
image.png
select_type表示这条SQL只是一条简单SQL（没有子查询和UNION）；
key字段为PRIMARY表示这条SQL用了主键索引；rows为1表示扫描行数为1行。关于字段含义的详细解释，请戳这里。
这里单独说下一个有趣的问题，explain输出的Extra 在低版本可能有点不一样的意思：
image.png
image.png
这条SQL explain的结果和我想象的不一样。明明不遵守最左原则但是显示'Using index'。查了官网才知道'Using index'的含义：
image.png
image.png
如果还是有疑惑，再执行下面这一条你就完全明白了：
image.png
explain select * from AbnormalAccountInfo where Type=1;
explain select count(*) from AbnormalAccountInfo where Type=1;
show engine innodb status：
这条命令是用于查看MySQL服务器InnoDB存储引擎状态的，一般用在压测调优和性能监控上，云厂商会通过该命令来实现对InnoDB部分性能的监控和历史、实时展示。下图是执行该命令的输出样例（部分）：
image.png
输出的内容可以用来观察InnoDB的并发和性能状况，评估在不同在线用户数量量级下InnoDB的锁、IO、日志等压力情况（死锁也可以用这个来确定）。关于这条命令输出内容会分为多个部分，这里列个表格简单说明一下。
| 名称 | 说明 | 备注 |
|---|---|---|
| BACKGROUND THREAD | MySQL的后台线程信息 | |
| SEMAPHORES | 信号量、锁相关信息 | |
| LATEST FOREIGN KEY ERROR | 最近外键约束错误的信息 | 没有则不显示 |
| LATEST DETECTED DEADLOCK | 最近发生的死锁信息 | 没有则不显示 |
| TRANSACTIONS | 事务相关。包括：报告锁等待（锁争用） | 可以帮助跟踪事务死锁的原因（死锁是那些事务导致） |
| FILE I/O | 线程IO信息 | 读、写、刷盘的情况 |
| INSERT BUFFER AND ADAPTIVE HASH INDEX | 缓存更改和Hash索引自适应相关 | |
| LOG | 日志 | 写入情况 |
| BUFFER POOL AND MEMORY | 缓冲池和内存 | 缓存命中率、内存使用状况 |
| ROW OPERATIONS | 主线程在做什么 | 包括每种类型（insert、update、deleted、read）的行操作的数量和性能率。能判断MySQL压力在哪种类型上 |
这里推荐一篇详细介绍如何看该命令输出结果的文章：https://cloud.tencent.com/developer/article/1424670
mysqldump：
该命令是数据库备份命令，备份的粒度有：指定表、指定库、全部库。就我的工作经验来看，这条命令大多数是用在测试和开发环境。线上环境大多会有从库或者是冷备库，一般有什么查询或者耗时耗性能操作都在冷备库上进行，线上需要进行备份的机会不多，即使有也是对从库或冷备库进行操作。
SELECT * FROM sys.innodb_lock_waits：
这条命令是通过data_locks和data_lock_waits两个表来确定哪个事务阻塞了另一个事务的。这在死锁查询的时候经常用到的命令。
KILL：
Kill命令在MySQL中的作用是终止指定线程或这个线程当下正在执行的任务。在一些极端的线上环境下我们可能用到这条命令，比如死锁、慢查询卡住等等。这里简单介绍下kill query ${thread_id}和kill ${thread_id}两种模式。kill query ${thread_id}只会终止指定线程当下正在执行的任务，而不会将这个线程kill掉（连接也会得以保持）。下图我先在thread_id=2032的线程上执行了select sleep(100)这条命令，然后另外开一个连接用kill query ${thread_id}命令终止该线程正在执行的任务，而不kill掉这个线程。
image.png
image.png
kill ${thread_id}不仅会终止指定线程当下正在执行的任务，而且会线程和这个线程背后的连接。下图我先在thread_id=2032的线程上执行了select sleep(100)这条命令，然后另外开一个连接用kill ${thread_id}命令kill掉这个线程。最终该线程的连接也断开了：
image.png
image.png
OPTIMIZE TABLE：
MySQL在删除数据的时候会留下许多磁盘碎片，碎片空洞占据原来数据的空间。所以即使我们删除了许多数据，但是这个表的数据所占的磁盘空间大小也没有变小。
下图红色部分是要删除的数据：
image.png
删除之后出现的磁盘碎片：
image.png
关于表的磁盘碎片问题，各位可以自己做一个实验：创建一张Test表：
CREATE TABLE `Test` (
`id` int(11) NOT NULL,
`age` int(11) NOT NULL,
`desc` varchar(255) NOT NULL,
`class` varchar(255) NOT NULL,
`money` int(11) NULL,
PRIMARY KEY (`id`)
);
然后插入一百万条数据这时候你看看它的.ibd文件（存储表数据和索引的文件）大小，最后把这张表清空再看看.ibd文件的大小。你会发现这个文件大小没有变化。
当然这些碎片在后续插入数据的时候可能会被再利用起来。但是在没有被重新利用起来之前它的存在额外增加了存储代价，同时也会降低表的扫描效率。这种现象和JVM的老生代内存回收之后会有内存碎片很像，不过JVM会在特定的时机自动整理碎片提高内存利用效率。
MySQL整理一个表的磁盘碎片需要自己自动执行OPTIMIZE TABLE table_name这条命令，执行之后数据库会对表重新组织表数据和关联索引数据的物理存储，以减少访问表时的存储空间并提高I/O效率。注意：该命令会锁表（insert，delete，update语句堵住），如果是线上执行该命令需要做好DML语句分流的准备。这里做个额外说明：想OPTIMIZE TABLE table_name操作InnoDB的表有效的话，有一个前提条件——就是引擎开启了独立表空间（可以通过show variables like 'innodb_file_per_table'查看是否开启）。
CREATE USER：
不管是JDBC连接数据库，还是使用数据库客户端连接数据库。我们都需要指定我们登录的用户账号的用户名和密码（下图就是Navicat新建数据库连接的界面）：
image.png
一般我们自己搭的MySQL都是使用root超级用户直接进行测试。线上一般是不会对外提供root账号，而是针对不同进程（游戏服务器、web服务器）的实际需要提供拥有不同权限的用户账号（当然用户名和密码也不会相同）。
CREATE USER就是创建用户的命令。这里通过一个例子来说明（有些低版本MySQL可能不支持下面的语法，请前往官网确认对应版本的语法）：
CREATE USER
'Test'@'192.168.%' #1
IDENTIFIED WITH mysql_native_password BY 'password' #2
PASSWORD EXPIRE INTERVAL 180 DAY #3
-
'Test'@'192.168.%'：这行的指定了新创建的user的用户名和Host（格式：userName@Host），Host是能使用这个user连接数据库的ip地址，支持通配符模式（%表示任意字符串）。所以'Test'@'192.168.%'就是创建一个用户名为'Test'且只允许ip开头为192.168.的服务器连接。如果Host不做限制，配置就是'Test'@'%'。线上的MySQL服务器建议做Host限制，一般指定只能自己内网服务器集群网段才能连接（比如我们的服务器都部署在10.60.2.%网段，那Host就这样设置）。注意，是用户名和Host一起定位一个user，比如'Test'@'192.168.0.1'和'Test'@'192.168.0.2'是两个不同的user。 -
IDENTIFIED WITH mysql_native_password BY 'password'：这行用于定义该用户的密码，mysql_native_password是我们指定的密码认证机制，'password'是定义的密码。在MySQL8之前，密码认证机制默认值是mysql_native_password，MySQL8之后是caching_sha2_password。 -
PASSWORD EXPIRE INTERVAL 180 DAY：这行是设置密码过期时间，这里表示该user密码有效期是180天，时间到之后需要重新设置（过期之后重连才需要更新密码，不影响一直存活的连接）。一些安全性要求比较高的账号和库，会强制要设置（比如我们的支付数据库，就是3个月）。
创建user成功之后，可以在mysql.user表找到你新创建的user数据，这个表包含了所有user的相关信息（Host、加密后端密码字符串、权限、账户信息）。
image.png
执行
FLUSH PRIVILEGES刷新数据库，就可以使用新的user连接数据库。PS：线上创建user的SQL，建议使用脚本生成做到规范化管理。
DROP USER：
有创建就有销毁，销毁user的命令格式是：DROP USER 'user_name'@'Host'。比如我要销毁刚刚创建的Host是192.168.%的Test用户：DROP USER 'Test'@'192.168.%';。mysql.user表是由Host和User两个字段组成的联合主键的表。一个user是由Host和User来定位的，创建user的时候是这样，删除的时候也是这样。
image.png
GRANT：
创建完user还需要为user授予指定权限，不然新的user除了能和数据库连上连接就没有任何其他权限。MySQL授予user全面的语句就是grant。能授予的权限包括对指定库指定表的select、insert、update、delete等等。下面通过三条命令介绍一下grant的使用：
-
GRANT ALL ON *.* TO Test;：对Test用户授予全部库全部表所有权限，*号是通配符；
-
GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO Test：只对Test用户授予my_db库所有表的SELECT, INSERT, UPDATE, DELETE权限（这个是线上常见的授权格式）
-
GRANT SELECT ON my_db.my_table TO Test：只对Test用户授予my_db库my_table表的SELECT权限（有些对外公开API的服务会这么开权限，保证数据的安全性）
grant命令执行完之后，调用FLUSH PRIVILEGES刷新数据库生效。
权限系统是保证系统安全的基本手段。MySQL的权限系统模式是基于用户的访问控制，不同的用户有不同的权限。对线上服务器后台进程应该遵循最小权限原则，能开多小就开多小。比如客服后台只有查询功能，那就只开select权限；服务器进程不给DROP权限等等。
实际应用过程中，应该尽量确保各个服务器进程的user在开发、测试、线上环境的权限保持一致。如果做不到这一点可能会出现开发、测试环境执行某一个SQL没问题，但是到了线上就出问题的状况。我就遇到过一次在开发、测试环境对某个表执行truncate语句，但是线上环境因权限问题失败，最后延误发版。
网友评论