Mysql 数据库巡检方案

巡检方案

本人工作中呕心沥血总结的方案，欢迎参考@！欢迎提出新技术！共勉！之后加脚本

1. 确认MySQL服务器是否正常运行

1.1.查看mysqld进程是否正常运行

使用以下命令来检查mysqld进程是否正在运行：

ps -ef | grep mysqld

看到类似以下的输出，说明mysql运行正常：

root      1846     1  0 10:53 ?        00:00:00 /usr/sbin/mysqld

如果mysqld进程没有运行，使用以下命令启动它：

sudo service mysql start

注意：这取决于操作系统和MySQL安装方式。如果你的系统是systemd，使用以下命令，手动启动数据库：

sudo systemctl start mysql

1.2.配置mysqld自动检测脚本

服务器最好配置自动检测mysqld进程并自动启动的脚本，将 MySQL 加入系统自启动项，系统重启后，就会自动自动mysql了，代码如下：

chkconfig --add mysqld

chkconfig --level 345 mysqld on

使用以下脚本检查 MySQL 是否启动，并在未启动时启动 MySQL：

#!/bin/bash

# 检查 MySQL 是否在运行中

if ! pgrep mysql &> /dev/nullthen

    # 启动 MySQL

service mysql start

else

echo "MySQL is already running"

fi

将上述脚本保存为一个名为 check_mysql.sh 的文件，然后在终端中运行 bash check_mysql.sh 即可执行脚本。如果 MySQL 未启动，脚本将启动 MySQL。如果 MySQL 已经在运行中，脚本将输出 "MySQL is already running"。

1.3.是否有重大错误或报警信息

使用 MySQL 的管理工具或者命令行工具来查看错误日志文件。 在错误日志文件中，你可以查找关键字“ERROR”或“WARNING”，以找到重大错误或报警信息。

MySQL 的错误日志文件默认存储在数据目录下的主机名.err 文件中。使用以下步骤来查看 MySQL 错误日志：

1. 打开 MySQL 配置文件 my.cnf 或 my.ini，找到 [mysqld] 段,其中 log_error 参数。例如：log_error=/var/log/mysql/error.log。
2. 或者通过如下命令在系统命令行获取：mysqladmin variables -u root -p | grep -w 'log_error'
3. 如下方式在mysql命令行获取：show variables like  'log_error'

MySQL 可以记录重大错误和报警信息。 重大错误是指无法继续正常操作的错误，例如数据库崩溃或无法启动 MySQL 服务器。 报警信息是指可能需要注意的问题，例如磁盘空间不足或者查询速度变慢。

MySQL 会将重大错误和报警信息记录在错误日志文件中。 错误日志文件的位置和名称取决于操作系统和 MySQL 配置。 你可以在 MySQL 配置文件中设置错误日志级别，以确定哪些消息会被写入错误日志文件。 通常情况下，错误日志级别设置为警告级别或更高级别，以确保重大错误和报警信息都会被记录下来。--如何配置呢？

2.查看集群状态是否正常

show status like 'wsrep%';

查看cluster sizes 是否为3 （以一主两从为例，集群中共有3台机器为例，MySQL Galera cluster）。

2.1、检查MySQL主从复制健康状态

2.1.1 判断当前主机的主从复制状态

#show slave status\G

……

Slave_IO_Running: Yes    

Slave_SQL_Running: Yes  

……

Slave_SQL_Running_State: Reading event from the relay log

Seconds_Behind_Master: 4508 ----主从延迟过大，正常为0。分析原因。

备注：Slave_IO_Running: Yes ，Slave_SQL_Running: Yes 说明主从同步正常。否则，需要在Slave_SQL_Running_State中，或者错误日志中检查异常中断原因。

2.1.2检查进程及数据库连接情况

#查看当前全部进程：

show processlist;  #或show full processlist;

#查看当前失败连接数

show global status like 'aborted_connects';

#查看有多少由于客户没有正确关闭连接而死掉的连接数。过多需要报警并分析原因。

show global status like 'aborted_clients';

#杀掉无用连接

#kill <连接ID>;

其中，<连接ID> 是要杀死的连接的ID。

#查看最大连接数，查看当前连接数，若当天连接已经达到最大连接数。则查看是否连接数不够用，或者过多连接没有被正常释放。

show status like 'Threads_connected';

show variables like '%max_connections%';

show global status like 'max_connections';

3.检查数据库的性能指标

3.1.IOPS

IOPS（Input/Output Operations Per Second）是衡量存储设备（如硬盘、固态硬盘等）性能的指标，指的是每秒钟能够完成的输入输出操作次数。IOPS越高，表示存储设备的性能越好。IOPS的计算方法是，将磁盘的读写操作次数（I/O）除以单位时间（秒），例如1秒钟内的读写操作次数为1000，那么IOPS就是1000。

使用iostat -x 5 查看IO情况：

-x:更详细的io设备统计信息

选项 说明

rrqm/s 每秒对该设备的读请求被合并次数，文件系统会对读取同块(block)的请求进行合并

wrqm/s 每秒对该设备的写请求被合并次数

r/s 每秒完成的读次数

w/s 每秒完成的写次数

rkB/s 每秒读数据量(kB为单位)

wkB/s 每秒写数据量(kB为单位)

avgrq-sz 平均每次IO操作的数据量(扇区数为单位)

avgqu-sz 平均等待处理的IO请求队列长度

await 平均每次IO请求等待时间(包括等待时间和处理时间，毫秒为单位)

svctm 平均每次IO请求的处理时间(毫秒为单位)

%util 采用周期内用于IO操作的时间比率，即IO队列非空的时间比率

%util: 一秒中有百分之多少的时间用于 I/O 操作，或者说一秒中有多少时间 I/O 队列是非空的。即 delta(use)/s/1000 (因为use的单位为毫秒)。如果 %util 接近 100%，说明产生的I/O请求太多，I/O系统已经满负荷，该磁盘可能存在瓶颈。

3.2.响应时间

最简单的响应时间查看方法，登录数据库后执行：

select 1,

数据库运行正常，这条语句很快返回结果。如果很慢或者卡住了，需要进一步分析IO, CPU，内存锁等使用情况。见本章其他内容。

对于更详细的SQL性能查询，步骤如下：

查看performance schema是否开启：

mysql> SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| performance_schema | ON    |

+--------------------+-------+

1 row in set (0.00 sec)

启动performance_schema:

SET GLOBAL performance_schema = ON;

关闭performance schema：

在配置文件中修改performance_schema的属性值，on表示开启，off表示关闭。

关于SQL维度的统计信息主要集中在events_statements_summary_by_digest表中，通过将SQL语句抽象出digest，可以统计某类SQL语句在各个维度的统计信息：

3.3.1哪个SQL执行次数最多：

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest

 ORDER BY COUNT_STAR desc LIMIT 1\G

3.3.2哪个SQL平均响应时间最多：

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest

ORDER BY AVG_TIMER_WAIT desc LIMIT 1\G

3.3.3哪个SQL扫描的行数最多：

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest

 ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1\G

3.3.4哪个SQL使用的临时表最多：

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest

ORDER BY SUM_CREATED_TMP_DISK_TABLES、SUM_CREATED_TMP_TABLES desc LIMIT 1\G

3.3.5哪个SQL返回的结果集最多：

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest

ORDER BY SUM_ROWS_SENT desc LIMIT 1\G

3.3.6哪个SQL排序数最多：

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest

ORDER BY SUM_SORT_ROWS desc LIMIT 1\G

3.3.7哪个表、文件逻辑IO最多（热数据）：

SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY

 SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2\G

3.3.8索引使用最多：

SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY

SUM_TIMER_WAIT DESC limit 1;

3.3.9未被使用过的索引：

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0

AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

3.3.10哪个等待事件消耗的时间最多：

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle'

ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;

3.4.CPU，内存利用率

可以使用命令 top 或 htop 来查看 Linux 系统的 CPU 使用率。top 命令会实时显示系统中各个进程的资源使用情况，包括 CPU 使用率、内存使用率等（htop 与 top 功能类似，但操作界面更加友好）。

如果发现CPU过高(100%), 内存使用率过高(>=50%)。

如果是mysqld进程导致，则进一步查看mysql中用户线程的资源使用情况。

在命令行输入以上命令即可查看CPU使用率, 如果系统没有安装htop可以使用 apt-get install htop 来安装。

3.5.是否存在没有主键的表

可以通过以下 SQL 语句查询数据库中哪些表没有主键：

SELECT table_nameFROM information_schema.tablesWHERE table_schema = 'your_database_name'AND table_type = 'BASE TABLE'AND table_name NOT IN (

  SELECT DISTINCT table_name

  FROM information_schema.statistics

  WHERE table_schema = 'your_database_name'

  AND index_type = 'PRIMARY'

);

其中，将 your_database_name 替换为你要查询的数据库名称。该 SQL 语句会返回该数据库中所有没有主键的表名。

4.空间使用情况查询

4.1.查看磁盘空间使用情况

使用df -h 查看，超过90%或者系统阈值，报警。

1. 检查数据库的版本和补丁

查看数据库当前版本，是否存在已知的漏洞。数据库升级操作比较复杂，所以要针对实际运行情况，是否遇到了因为版本漏洞存在的问题（具体每个版本情况可以参考mysql官方文档），选择是否升级。

要检查 MySQL 数据库的版本和补丁，请执行以下步骤：

打开 MySQL 命令行客户端。

输入以下命令并按回车键：

SELECT VERSION();

这将返回 MySQL 数据库的版本。

输入以下命令并按回车键：

SHOW VARIABLES LIKE '%version%';

这将返回 MySQL 数据库的详细版本信息和安装路径。

输入以下命令并按回车键：

SELECT @@version_compile_os;

这将返回 MySQL 数据库的操作系统类型和版本。

要检查 MySQL 数据库的补丁，请执行以下步骤：

a. 打开 MySQL 命令行客户端。

b. 输入以下命令并按回车键：

SELECT @@version_compile_patch;

这将返回 MySQL 数据库的补丁级别。

注意：如果你使用的是 MySQL 8.0 及以上版本，可以使用以下命令来检查补丁级别：

SHOW VARIABLES LIKE 'version_comment';

这将返回 MySQL 数据库的补丁级别和其他详细信息。

5.检查数据库的配置参数

5.1.缓存大小、连接数、日志大小和日志级别

MySQL 的缓存大小可以通过设置变量 innodb_buffer_pool_size 来调整，该变量定义了 InnoDB 存储引擎使用的内存缓存池的大小，单位为字节。建议配置80%内存，不低于系统内存的50%。

连接数可以通过修改 max_connections 变量来设置，该变量定义了 MySQL 服务器能够同时处理的最大连接数。日志大小可以通过修改 max_binlog_size 变量来设置，该变量定义了二进制日志文件的最大大小，单位为字节。日志级别可以通过修改 log_level 变量来设置，该变量定义了 MySQL 服务器的日志记录级别。常见的日志级别有 ERROR、WARNING、INFO、DEBUG 等。

查看全部配置参数的命令
复制SHOW VARIABLES ;

查看某个具体配置参数，如最大连接数。
复制SHOW VARIABLES LIKE 'max_connections';

5.2.二进制日志配置情况查看

5.2.1二进制日志状态查询：

show variables like ‘log_bin’

注意：默认是开启二进制日志的(ON)。

5.2.2二进制日志格式参数：binlog_format

binlog_format=ROW
定义binlog的日志写入格式。有三个选项可选，Statement, row, mixed。如果是主从复制，则不能使用statement格式，而应该使用row格式。

5.2.3单个二进制文件大小max_binlog_size

max_binlog_size=536870912

定义了binlog的文件大小，超过这个大小会关闭当前文件然后打开下一个，最大值是1GB。不会严格按照这个大小来切割，因为会优先保证一个事务不会分割到多个binlog中。

设置二进制日志格式为ROW:

mysql> SET GLOBAL binlog_format = 'ROW';

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

binlog_format：指定二进制日志的格式，ROW格式可以记录每行数据的变化，比其他格式更加详细。

运行以下命令查看全部二进制日志的配置，具体参数较多，此处不便赘述，可以参考实际业务以及官方文档配置看是否合理。

SHOW VARIABLES LIKE 'log_bin%';

6.检查数据库的安全性

数据库的安全性检查包含：如用户权限、访问控制、网络安全和备份策略等。

6.1.用户权限

查看用户权限配置是否合理。可以通过如下命令查看用户所拥有的权限。并分析是否合理。

例如，如果你想查看用户"myuser"的权限：

SHOW GRANTS FOR 'myuser'@'%';

这将显示针对"myuser"用户的所有权限，并告诉你他们是否具有对特定数据库或表的某些特定权限。

如果你发现某个用户拥有不必要的权限，则可以使用REVOKE命令来撤销它们。

出于安全考虑，建议遵循以下几个经验原则：

• 只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给 select 权限就可以了。
• 创建用户的时候限制用户的登录主机，一般是限制成指定 IP 或者内网 IP 段。
• 给各个服务单独创建数据库用户，单个用户最好只能操作单个库。
• 及时记录各数据库用户权限等信息，以免忘记。
• 若有外部系统调用，应配置只读用户，并且权限要精确到表或视图。
• 定期清理不需要的用户，回收权限或者删除用户。

6.2.访问控制

可以使用以下命令查看 MySQL 中的用户权限：

复制SHOW GRANTS FOR username;

其中，username 是你要查看权限的用户账号。

执行该命令后，MySQL 会返回该用户拥有的所有权限信息。如果你是以 root 账号登录 MySQL 的话，也可以通过以下命令查看所有用户的权限：

复制SELECT user, host, grant_priv, super_priv FROM mysql.user;

该命令会返回 MySQL 中所有用户的账号、主机、是否拥有 grant 权限和是否拥有 super 权限等信息。

MySQL 访问控制主要通过用户、主机和权限三个方面来实现。

用户管理：MySQL 使用用户名和密码进行身份认证。可以使用以下命令创建用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

主机管理：MySQL 的访问控制还可以根据来源主机进行限制。可以使用以下命令进行主机管理：

GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

可以使用以下命令授予用户权限：

复制GRANT 权限 ON 数据库名.表名 TO '用户名'@'主机名';

MySQL 还可以通过修改配置文件实现访问控制，例如可以通过 bind-address 选项限制 MySQL 只能接受来自某些 IP 的连接。

6.3.网络安全（目前暂时难度较大）

• 防火墙、安全路由器等网络设备查看？
• 使用强密码，MySQL 数据库的用户密码强度检查？
• 使用 SSL 连接：MySQL 数据库应该使用 SSL 加密连接，以保证数据传输的安全性。如何查看连接方式？
• 是否存在SQL 注入攻击：在编写 MySQL 查询时应该遵循最佳实践，避免被攻击者利用 SQL 注入攻击方式获取敏感信息。---sql注入？

6.4.备份策略

查看备份策略是否合理：冷备，全备+增量备份+日志，温备等等。是否按时有效执行了数据备份。数据备份的有效性。日志是否有效备份。

可以尝试恢复备份的数据，看数据库是否可用，检测数据备份的有效性。

数据备份有效性检测方法？

6.4.1 Mysqldump备份

检查备份策略：mysqldump备份参数介绍及建议：

# mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql

--single-transaction就能保证innodb的数据是完全一致的，而如果使用了myisam表必须加--lock-all-tables 才能保证数据是完全一直的。
--master-data=2  记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的
--databases hellodb 指定备份的数据库然后回到mysql服务器端（仅仅备份某个库）

真正在生产环境中，我们应该导出的是整个mysql服务器中的数据，而不是单个库，因此应该使用--all-databases
    在导出二进制日志的时候，可以直接复制文件即可，但是要注意的是，备份之前滚动下日志。

备份的过程中开启general-log, 确认备份时没有异常和报错。以保证备份文件的可用性。

定期查看备份文件是否可用，将备份文件恢复，查看是否可以正常恢复。

6.4.2其他备份方法

可依据实际情况具体分析备份执行情况以及备份文件的有效性，在此不赘述。

7.数据一致性检查

7.1.MySQL checksum

MySQL的checksum是一种用于检测数据完整性的机制。MySQL的checksum算法可以计算表中数据行的校验和，以检测数据是否被修改或损坏。MySQL的checksum算法基于InnoDB存储引擎，但是也可以用于MyISAM和其他存储引擎。执行以下命令。

checksum table test.tbname;

+-------------+------------+

| Table       | Checksum   |

+-------------+------------+

| test.tbname | 1105600045 |

+-------------+------------+

1 row in set (0.00 sec)

备注：在执行checksum命令时，表会被加一个读锁(read lock)，checksum table的原理是对表中的数据进行一行一行的较验和计算，因些对于大表，这是一个很耗时的过程。一般情况下，需要对存储非常重要的数据的表进行checksum，比如金融、电信等领域的数据表。此外，如果在使用MySQL进行数据迁移、备份或恢复时，也建议对表进行checksum以确保数据的完整性和一致性。

如果对于myisam表，建表时加上CHECKSUM=1选项，那么在对这样的表进行checksum table时将会非常快。

7.2.数据库结构比较

mysqldiff该工具是官方mysql-utilities工具集的一个脚本，可以用来对比不同数据库之间的表结构，或者同个数据库间的表结构。

下载地址：

https://dev.mysql.com/downloads/utilities/

示例一：检查server1,server2上的某个表结构是否一致

mysqldiff --server1=user:pwd@host:port --server2=user:pwd@host:port --changes-for=server2  dbname.tablename:dbname.tablename

示例二：检查server1,server2上的两个数据库中的表结构是否一致

mysqldiff --server1=user:pwd@host:port --server2=user:pwd@host:port dbname:dbname --changes-for=server2

注意：mysqldiff的应用场景如下：数据库升级：数据库迁移：数据库备份：数据库设计。

7.3.数据全能比较

mysqldbcompare是官方提供一个可以实现多库或单库比较数据一致的工具(全能比较)，有文件和数据，并生成差异性SQL语句，并生成差异SQL语句，但是对于表数据很大情况下，mysqldbcompare测试运行效率并不是很高，有时候会报异常超时等。同样如果要比较数据库表结构就使用mysqldiff。

示例：

mysqldbcompare --server1=user:pwd@192.168.56.110:3306 --server2=user:pwd@192.168.56.111:3306 --changes-for=server1 dbname:dbname

# mysqldbcompare --server1=root:'fxkj'@172.16.10.37 --server2=root:'fxkj'@192.168.56.37 fxkj:fxkj_test --run-all-test --changes-for=server2 --difftype=sql

7.4.在线主从数据一致性检查

pt-table-checksum是在线的主从数据一致性检查工具，能够对大数据量的数据库进行高效的主从数据一致性检查，能够自动控制检查数据量的大小，避免对线上业务造成较大的影响。使用实例如下：

示例一：对特定表的一致性进行检查

pt-table-checksum --host='192.168.56.110' --user='admin' --password='pwd' --port=3306 --recursion-method=processlist --replicate=percona.checksums --no-check-binlog-format --chunk-time=0.5 --tables='dbname.tb1,dbname2.tb2'

示例二：对特定数据库的一致性进行检查

pt-table-checksum --host=' 192.168.56.110' --user='admin' --password='pwd' --port=3306 --recursion-method=processlist --replicate=percona.checksums --no-check-binlog-format --chunk-time=0.5 --databases='dbname1,dbname2'

示例三：对所有数据库的一致性进行检查

pt-table-checksum --host=' 192.168.56.110' --user='admin' --password='pwd' --port=3306 --recursion-method=processlist --replicate=percona.checksums --no-check-binlog-format --chunk-time=0.5

1. 慢查询

8.2.慢查询查找

慢查询是指在数据库中执行时间较长的查询语句，通常会影响系统的性能和响应时间。慢查询通常是由于查询语句的复杂性、数据量过大、索引不合理、硬件资源不足等原因导致的。

• 打开MySQL慢查询日志
• 在MySQL的配置文件中（一般是my.cnf或者my.ini），将slow_query_log设置为1，并指定慢查询日志文件的路径，例如：
• slow_query_log = 1
• slow_query_log_file = /var/log/mysql/mysql-slow.log
• 重启MySQL,在修改完MySQL配置文件后，需要重启MySQL服务，使配置生效。
• 如果配置文件没有开启，也执行以下命令开启慢查询日志（如果已经开启可以跳过此步骤）
• SET GLOBAL slow_query_log = 'ON';
• 执行以下命令设置慢查询日志记录的阈值（单位为秒，此处设置为 5 秒）
• SET GLOBAL long_query_time = 5;
• 执行以下命令查看慢查询日志文件位置
• SHOW VARIABLES LIKE 'slow_query_log_file';
• 分析慢查询日志
• 在MySQL慢查询日志文件中，每一条慢查询记录都会包含SQL语句、执行时间、访问时间等信息。可以使用工具如mysqldumpslow、pt-query-digest等进行分析。
• 例如，使用mysqldumpslow来查看慢查询日志中访问次数最多的前10条SQL语句：
• mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
• 其中，-s c表示按照查询次数排序，-t 10表示只显示前10条结果。
• 注意：使用完后需要关闭慢查询日志，以免过多占用系统资源。
• SET GLOBAL slow_query_log = 'OFF';

8.2.优化慢查询SQL语句

可以通过explain 慢查询sql来查看SQL的执行计划，并找出SQL慢的根源，以优化SQL。优化方法可能包括但不限于：添加合适索引，减少不必要的字段查询，避免使用select *, where条件中最靠前的过滤条件是过滤性最强的，减少join，尽量使用索引以避免排序，等等。

EXPLAIN SELECT * FROM mytable WHERE id = 1;

• 执行该命令后，MySQL 会返回一个表格，其中包含了 MySQL 执行该查询语句的详细过程和执行计划。表格中的各个字段的含义如下：
• id：查询的唯一标识符，每个查询都有一个唯一的标识符；
• select_type：查询的类型，包括简单查询（SIMPLE）、联合查询（UNION）、子查询（SUBQUERY）等；
• table：查询涉及的表的名称；
• partitions：查询所涉及的分区；
• type：访问类型，即 MySQL 如何访问表，包括全表扫描（ALL）、索引扫描（index）、范围扫描（range）等；
• possible_keys：可能使用的索引列表；
• key：实际使用的索引；
• key_len：使用的索引的长度；
• ref：与索引比较的列或常量；
• rows：MySQL 预计要扫描的行数；
• filtered：返回结果的过滤程度；
• Extra：一些额外的信息，例如使用了哪个索引、使用了哪种算法等。

通过查看执行计划，我们可以了解 MySQL 在执行查询语句时的具体过程，从而进行优化。

通过分析慢查询日志，找出执行时间较长的SQL语句，通过

explain select * from * where ***** 找到本条SQL的执行计划。常用的优化方法包括：

• 添加索引：在查询条件和连接条件的列上添加索引，可以提高查询速度。
• 优化查询语句：避免使用SELECT *，只查询需要的列；避免使用子查询等复杂语句。
• 优化表结构：避免使用过多的JOIN操作；避免使用TEXT、BLOB等大字段类型。

9.当前数据库连接查询

9.1.查看当前是否有异常连接

show processlist 是一个 MySQL 的命令，用于显示当前正在运行的 MySQL 进程列表。执行该命令后，会返回一个表格，其中包含以下列：

• Id：连接的唯一标识符。
• User：连接的用户名。
• Host：连接的主机名。
• db：连接的数据库。
• Command：正在执行的 MySQL 命令。
• Time：连接已经存在的时间（以秒为单位）。
• State：MySQL 服务器中的线程状态。
• Info：正在执行的 SQL 语句。

以下是 show processlist 命令的一个示例输出：

+----+-------+-----------+------------------+---------+------+-------+------------------+

| Id | User  | Host      | db               | Command | Time | State | Info             |

+----+-------+-----------+------------------+---------+------+-------+------------------+

| 1  | root  | localhost | database_name    | Sleep   | 0    |       | NULL             |

| 2  | user1 | localhost | NULL             | Query   | 3    |       | SELECT * FROM foo|

| 3  | user1 | localhost | NULL             | Sleep   | 86400|       | NULL             |

+----+-------+-----------+------------------+---------+------+-------+------------------+

在上面的示例中，可以看到当前有三个连接处于活动状态：一个是 root 用户连接到 database_name 数据库，并且处于睡眠状态；第二个是 user1 用户正在执行一个 SELECT 查询；第三个连接也是 user1 用户的连接，但是它已经处于睡眠状态已经持续了一天。

9.2.查看用户连接来源是否正常：

登录到 MySQL 数据库，可以使用以下命令：

mysql -u username -p

进入 MySQL 数据库，可以使用以下命令：

use mysql;

查看连接信息，可以使用以下命令：

select user,host from user where user='your_username';

这里的 your_username 是你要查询连接信息的用户名。

如果要查看所有连接信息，可以使用以下命令：

select user,host from user;

想查看当前连接到 MySQL 数据库的所有用户和他们的连接信息，包括连接 ID、用户、主机、数据库、命令、时间和状态等，可以使用如下命令：

SHOW PROCESSLIST;

如果你需要查看某个特定用户的连接信息，可以使用以下命令：

复制SHOW PROCESSLISTWHERE USER='your_username';

10.查看Innodb死锁

在 MySQL 中，可以通过以下步骤查看死锁：

执行 SHOW ENGINE INNODB STATUS 命令，将返回 InnoDB 引擎的状态信息。

找到 LATEST DETECTED DEADLOCK 部分，其中包含最近检测到的死锁信息。在死锁信息中，可以看到哪些事务在运行、它们正在等待什么资源以及它们持有哪些资源。根据死锁信息来定位死锁的原因，并采取相应的措施来解决死锁问题。

另外，也可以通过 MySQL 的慢查询日志来查看死锁，具体操作可以参考 MySQL 官方文档。