游戏服务端开发中的MySQL进阶知识(五)-常用命令语句

168 阅读12分钟

本系列文章主要是本人在游戏服务端开发过程中,遇到的一些不那么为人熟知但我又觉得比较重要的MySQL知识的介绍。希望里面浅薄的文字能为了提供一点点的帮助。

本系列文章:
1、系统库:                juejin.cn/post/700743…
2、数据回滚和备份:  juejin.cn/post/700780…
3、慢查询:                 juejin.cn/post/700817…
4、InnoDB锁的问题: juejin.cn/post/700853…
5、常用命令语句:      juejin.cn/post/700928…
6、其他:                     juejin.cn/post/700928…
7、学习MySQL的启发:juejin.cn/post/701039…

MySQL除了DDL语句以外,还需要使用其他的命令来辅助我们对MySQL进行操作。这里我介绍下我工作中会用到,并且我感觉比较重要的语句给大家。我将它们大致分为辅助性语句和系统操作语句,辅助性语句:看看线程状况、看看SQL写的怎么样;系统操作语句:杀线程、整理表空间。

show processlist

这个语句用于查看MySQL服务器中正在或准备工作的线程集信息(不同权限用户看到的范围是不一样的)。这个语句一般是在数据库出现一些异常的情况下才会使用,比如怀疑出现死锁、有SQL卡住很长时间等等。下图是我在本地执行的该命令的结果: image.png 根据上图我解释下各个字段的意思:

字段含义作用
Id该线程的idKill 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只能分析selectupdateinsertdeletereplace这几个对表内数据操作的语句。如果被分析的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 THREADMySQL的后台线程信息
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压力在哪种类型上

这里推荐一篇详细介绍如何看该命令输出结果的文章:cloud.tencent.com/developer/a…

mysqldump

该命令是数据库备份命令,备份的粒度有:指定表、指定库、全部库。就我的工作经验来看,这条命令大多数是用在测试和开发环境。线上环境大多会有从库或者是冷备库,一般有什么查询或者耗时耗性能操作都在冷备库上进行,线上需要进行备份的机会不多,即使有也是对从库或冷备库进行操作。

SELECT * FROM sys.innodb_lock_waits

这条命令是通过data_locksdata_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_dbmy_table表的SELECT权限(有些对外公开API的服务会这么开权限,保证数据的安全性)
    grant命令执行完之后,调用FLUSH PRIVILEGES刷新数据库生效。

权限系统是保证系统安全的基本手段。MySQL的权限系统模式是基于用户的访问控制,不同的用户有不同的权限。对线上服务器后台进程应该遵循最小权限原则,能开多小就开多小。比如客服后台只有查询功能,那就只开select权限;服务器进程不给DROP权限等等。
实际应用过程中,应该尽量确保各个服务器进程的user在开发、测试、线上环境的权限保持一致。如果做不到这一点可能会出现开发、测试环境执行某一个SQL没问题,但是到了线上就出问题的状况。我就遇到过一次在开发、测试环境对某个表执行truncate语句,但是线上环境因权限问题失败,最后延误发版。

暂时只能介绍这几个,后续如果遇到不错的语句我会继续加上。

下一章:游戏服务端开发中的MySQL进阶知识(六)--其他