KWAN的解忧杂货铺

# 1.删除缓存

#在服务器上登录
mysql -u root -p

#刷新mysql的缓存
FLUSH HOSTS;

#退出
quit
1
2
3
4
5
6
7
8

# 2.查看缓冲池的信息

SELECT * from information_schema.INNODB_BUFFER_POOL_STATS;
1
  • information_schema：这是 MySQL 中的一个系统数据库，包含有关数据库系统的元数据，例如表、列、索引、权限等信息。
  • INNODB_BUFFER_POOL_STATS：这是information_schema数据库中特定表的名称。如前所述，该表包含有关 MySQL 中 InnoDB 缓冲池的统计信息。

# 3.显示 ddl 频次

在 MySQL 中，"Com" 命令计数是指执行各种类型的 SQL 命令的次数。例如，"Com_select" 表示执行 SELECT 语句的次数，"Com_insert" 表示执行 INSERT 语句的次数，等等。通过这个查询，您可以查看与特定类型的 SQL 命令相关的计数信息。

SHOW GLOBAL STATUS LIKE 'Com_______';
1

# 4.查询库表列

#查看库
SELECT * FROM information_schema.schemata order by SCHEMA_NAME;

#查询表
select * from information_schema.tables
where  TABLE_SCHEMA ='information_schema'
order by TABLE_SCHEMA;

#查询列
select * from information_schema.columns order by TABLE_SCHEMA;
1
2
3
4
5
6
7
8
9
10

# 5.索引监控

#查询冗余索引
select * from sys.schema_redundant_indexes;

#查询未使用过的索引
select * from sys.schema_unused_indexes;

#查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics
where table_schema='kwan' ;

#查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;

#查询占用bufferpool较多的表
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;

#查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='kwan';

#监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis order by exec_count desc;

#监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1;

#监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

#查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes
order by avg_read limit 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 6.查看等待锁的语句

SELECT *
FROM performance_schema.events_statements_history
WHERE thread_id IN (
    SELECT b.`THREAD_ID`
    FROM sys.`innodb_lock_waits` AS a
       , performance_schema.threads AS b
    WHERE a.waiting_pid = b.`PROCESSLIST_ID`)
ORDER BY timer_start ASC;
1
2
3
4
5
6
7
8

# 7.SHOW PROCESSLIST

SHOW PROCESSLIST 是用于查看当前运行在 MySQL 服务器上的所有连接和它们的状态的 SQL 命令。它提供了有关每个连接的详细信息，包括以下参数：

  1. Id：连接的唯一标识符。每个连接都有一个唯一的 ID，可以用于识别和操作连接。

  2. User：连接的用户名。这是连接到 MySQL 服务器的用户的用户名。

  3. Host：连接的主机地址。这是连接到 MySQL 服务器的客户端主机的 IP 地址或主机名。

  4. db：当前使用的数据库。连接当前正在使用的数据库，如果没有使用任何数据库，则显示为 NULL。

  5. Command：客户端执行的命令。这可以是各种命令，如"Sleep"（空闲状态）、"Query"（执行 SQL 查询）、"Connect"（建立连接）等。

  6. Time：连接的运行时间（以秒为单位）。表示连接自上次查询或命令执行以来的运行时间。

  7. State：连接的当前状态。这个状态字段提供了连接当前正在执行的具体操作的信息。

  8. Info：连接正在执行的 SQL 查询或命令。如果连接正在执行 SQL 查询，这里将显示查询的文本。

通过执行 SHOW PROCESSLIST 命令，你可以了解到当前活动的 MySQL 连接以及它们的状态，这对于诊断和监视 MySQL 服务器的性能和运行状况非常有用。如果需要中断某个连接，你可以使用 KILL 命令，并提供连接的 ID 来终止该连接。

# 8.查询死锁

-- 行锁阻塞情况
select * from sys.innodb_lock_waits;
1
2

这个系统视图提供了有关 InnoDB 存储引擎中正在等待锁的事务的信息。

InnoDB 是 MySQL 中常用的存储引擎之一，它支持事务和行级锁定，以提供更好的数据完整性和并发性。在多个事务同时访问同一数据时，可能会出现锁定冲突。当一个事务在等待另一个事务持有的锁时，就会发生锁等待（lock wait）。

sys.innodb_lock_waits视图的目的是帮助您识别当前正在等待锁的事务，以便您能够进行性能监视和故障排除。该视图提供了以下重要的列：

  • waiting_trx_id：正在等待锁的事务 ID。
  • waiting_thread_id：正在等待锁的线程 ID。
  • waiting_query：正在等待锁的查询语句。
  • waiting_lock_id：正在等待的锁 ID。
  • waiting_lock_mode：正在等待的锁模式，例如共享锁或排他锁。
  • blocking_trx_id：正在持有正在等待锁的事务 ID。
  • blocking_thread_id：正在持有正在等待锁的线程 ID。
  • blocking_query：正在持有正在等待锁的查询语句。
  • blocking_lock_id：正在持有的锁 ID。
  • blocking_lock_mode：正在持有的锁模式。

通过查询sys.innodb_lock_waits视图，您可以查看当前存在的锁等待情况，并确定哪些事务在阻塞其他事务的进程，从而帮助您诊断和解决潜在的性能问题。

查看锁信息:

# 查看锁信息
SELECT *
FROM information_schema.INNODB_TRX;
SELECT *
FROM `sys`.`innodb_lock_waits`;
SELECT *
FROM performance_schema.data_locks;
SELECT *
FROM performance_schema.data_lock_waits;
1
2
3
4
5
6
7
8
9

# 9.查询主从信息

#查询主服务器状态
show master status\G;

#查询从服务器状态
show slave status\G;
1
2
3
4
5

image-20221216172959649

# 10.查看数据库支持的引擎

#查看引擎
show engines;
1
2

image-20231021174045844

# 12.Too many connections

报错信息如下：

"SQLState":"08004","vendorCode":1040,"detailMessage": "Data source rejected establishment of connection,message from server: "Too many connections""

解决方案

我们要知道，MySQL 版本的不同，可设置的最大连接数范围也是有所区别的：

  • MySQL5.5 ～ 5.7：默认的最大连接数都是 151，上限为：100000
  • MySQL5.0 版本：默认的最大连接数为 100，上限为 16384
  • MySQL8.0 版本: 默认的最大连接数是 151

查看 MySQL 版本

SELECT VERSION() ;
1

查看当前 MySQL 最大连接数限制

show variables like 'max_connections';
1
set global max_connections=1500;
1

修改 my.cnf 文件

MySQL 重启后会优先使用 my.cnf 配置文件中的配置，用上面命令设置后，若重启 MySQL 服务，则还是会回到 my.cnf 文件中配置的最大连接数（或者默认值）。

# 修改my.cnf文件，在文件中加入如下属性
max_connections=1500

# 然后重启mysql服务
$ service mysqld restart
1
2
3
4
5

再次去创建数据库连接，就没问题了！

# 13.时区查询

SHOW VARIABLES LIKE "%time_zone%";
1

输出

Variable_name Value

system_time_zone CST

time_zone SYSTEM

# 14.system_time_zone 参数

system_time_zone 是一个变量，用于表示系统的时区信息。在 Java 中，通过 java.util.TimeZone 类的静态方法 getAvailableIDs() 可以获取所有可用的时区标识符，其中包括 system_time_zone 可能的取值。

以下是一些 system_time_zone 可能的取值示例：

  1. "America/New_York": 美国东部时间
  2. "Europe/London": 伦敦时间
  3. "Asia/Tokyo": 东京时间
  4. "Pacific/Honolulu": 夏威夷时间
  5. "Australia/Sydney": 悉尼时间
  6. "Africa/Cairo": 开罗时间
  7. "America/Los_Angeles": 美国太平洋时间
  8. "Europe/Paris": 巴黎时间
  9. "Asia/Shanghai": 上海时间
  10. "America/Sao_Paulo": 圣保罗时间

# 15.CST 和 UTC 的区别?

CST 和 UTC 是两种常见的时间标准，它们之间的主要区别在于其基准点和使用方式：

  1. UTC（协调世界时）：是国际时间标准，基于原子钟的时间测量，以地球自转为基础。UTC 被认为是世界上的标准时间，不受地理位置的影响。UTC 不考虑夏令时的变化，因此在夏令时生效时，与当地时间可能会有偏差。

  2. CST（中部标准时间）：是美国中部地区的标准时间。CST 是以地区的地方时间为基础，通常是指 UTC-6 小时。在夏令时生效期间，中部地区会转换为 CDT（中部夏令时），相对于 UTC 是 UTC-5 小时。

总的来说，CST 和 UTC 的主要区别在于其基准点和使用方式。UTC 是国际标准时间，不受地理位置的影响，而 CST 是美国中部地区的标准时间，以地方时间为基准，可能会受到夏令时的影响。

# 16.解决方案

# 1.进入 mysql 控制台

mysql -u root -p ****
1

执行如下命令即可，这时候我们再执行 select now();发现时间并没有变回来

SET time_zone = "+08:00";
SET global time_zone = "+08:00";
flush privileges;
1
2
3

# 2.修改 my.cnf

我们同样的进入到，mysql 所在宿主机，编辑 mysql 配置文件

/etc/mysql/mysql.conf.d/mysqld.cnf

我们在[mysqld]下面加入这样的一句话：

default-time-zone=Asia/Shanghai

# 3.修改 mysql 链接信息

一般我们使用 springBoot 项目，需要在 application.properties 中配置 mysql connector 信息

在 jdbc 链接信息里设置 serverTimezone=Asia/Shanghai

# 17.Public Key Retrieval is not allowed

针对“Public Key Retrieval is not allowed”错误，我们可以采取一些解决方案来解决或避免这一问题的发生：

在 JDBC 连接串中加入 useSSL=false 参数

JDBC 是 Java Database Connectivity 的缩写，用于 Java 程序与数据库进行连接和操作。在 JDBC 连接数据库时，可以通过在连接串中添加参数来配置连接的属性。其中，将 useSSL 参数设置为 false 可以禁用 SSL/TLS 协议，从而避免“Public Key Retrieval is not allowed”错误的发生。

在 JDBC 连接串中加入 allowPublicKeyRetrieval=true 参数

除了禁用 SSL/TLS 协议外，还可以通过在 JDBC 连接串中添加 allowPublicKeyRetrieval 参数并设置为 true 来允许公钥检索。这样一来，即使禁用了 SSL/TLS 协议，客户端仍然可以通过此参数获取服务器的公钥，从而避免“Public Key Retrieval is not allowed”错误。

# 18.drop,truncate,delete 区别?

  1. DROP：
    • DROP 删除表和数据
    • DROP是用于删除数据库对象（表、视图、索引、存储过程等）的操作。
    • 例如，使用DROP TABLE语句可以删除一个表，以及与该表相关的数据、索引和约束等。
    • DROP操作是一个 DDL（Data Definition Language）命令，因此会立即提交事务，并且不能回滚。执行后数据和对象会永久删除。
  2. TRUNCATE：
    • TRUNCATE 清空表中的数据,会释放空间,重置主键
    • TRUNCATE用于快速删除表中的所有数据，但保留表结构。
    • 与DELETE相比，TRUNCATE操作通常更快，因为它不会逐行删除数据，而是直接删除表中的所有数据。
    • TRUNCATE操作是一个 DDL 命令，也会立即提交事务，并且不能回滚。执行后数据会永久删除，但表结构仍然保留。
  3. DELETE：
    • DELETE 仅仅只是删除表中的数据
    • DELETE用于从表中删除特定的行，可以根据条件删除满足条件的数据。
    • DELETE是一个 DML（Data Manipulation Language）命令，因此可以包含在一个事务中，并且可以回滚。
    • DELETE操作逐行删除数据，并在删除过程中生成事务日志，因此相对较慢，特别是对于大量数据的删除。
上次更新: 11/2/2024, 2:20:26 PM

02-插入死锁 →