游戏开发

MySQL进阶知识（五）-常用命令语句

2021-09-21  本文已影响0人  higher2017

本系列文章主要是本人在游戏服务端开发过程中，遇到的一些不那么为人熟知但我又觉得比较重要的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

创建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的使用：

权限系统是保证系统安全的基本手段。MySQL的权限系统模式是基于用户的访问控制，不同的用户有不同的权限。对线上服务器后台进程应该遵循最小权限原则，能开多小就开多小。比如客服后台只有查询功能，那就只开select权限；服务器进程不给DROP权限等等。

实际应用过程中，应该尽量确保各个服务器进程的user在开发、测试、线上环境的权限保持一致。如果做不到这一点可能会出现开发、测试环境执行某一个SQL没问题，但是到了线上就出问题的状况。我就遇到过一次在开发、测试环境对某个表执行truncate语句，但是线上环境因权限问题失败，最后延误发版。

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

上一篇下一篇

猜你喜欢

热点阅读