mysql DBA常用的sql

• 是否一般查询日志，默认关闭
  show variables like ‘general_log’;

• 是否开启慢日志查询 默认关闭
  show global variables like ‘slow_query_log’;

• 开启慢日志查询
  SET GLOBAL slow_query_log = ‘ON’;

• 默认是10 单位s
  SELECT @@long_query_time;

• 设置超过1s就算慢查
  SET GLOBAL long_query_time=1;

• 查询数量小于这个值，不计入慢查询，默认是 0
  SELECT @@min_examined_row_limit;

• 慢查默认不包括管理语句，比如创建表、创建索引等等
  SELECT @@log_slow_admin_statements;

• 默认也不记录不使用索引的慢查
  SELECT @@log_queries_not_using_indexes;

• 日志保存方式，FILE 或 TABLE， 也可以TABLE,FILE 或者NONE 代表禁用日志写入

• table是将慢日志添加至表中，FILE是将慢日志添加至慢日志文件

• 慢查存在哪里
  SELECT @@log_output;

• 比如我希望2边都保存
  SET GLOBAL log_output=‘table,file’;

• 为file，那么保存的文件路径为slow_query_log_file
  SELECT @@slow_query_log_file;
  SET GLOBAL slow_query_log_file=‘/data/soft/mysql/123.log’;

• 为表，则保存在mysql.slow_log表中
  select * from mysql.slow_log;

• 根据表结构创建空表
  CREATE TABLE new_table LIKE old_table;

• 复制表结构与数据到新表
  CREATE TABLE new_table AS SELECT * FROM old_table;

• 查看表结构
  DESCRIBE bfa.bfa_ic_invoice;

• 查询线程
  show status like ‘Threads%’;

Variable_name	Value	note
Threads_cached	4	缓存的线程数,thread_cache_size 默认-1,自动调整，最大是16384
Threads_connected	177	当前打开的线程数
Threads_created	195261	创建的线程总数
Threads_running	1	正在运行的线程数

• 查询thread_cache_size系统变量
  SELECT @@thread_cache_size;
• 显示用户正在运行的线程
  show FULL PROCESSLIST;

字段名	字段含义
user	操作的用户名
host	地址
db	操作的DB
command	当前连接执行的命令 休眠、查询 sleep为空闲连接
time	这个状态持续的时间,单位是秒
state	状态
info	执行信息

删除线程，阻塞线程之类的格式为
KILL command id;
例如
KILL sleep 1234;

• 查询最大使用链接
  show status like ‘Max_used_connections%’;

Variable_name	Value	note
Max_used_connections	464	自服务启动以来最大的连接数
Max_used_connections_time	2024-08-01 17:15:14	达到最大连接数的时间

• 最大的连接数，超过该值不允许建立连接 默认151 1~100000
  SELECT @@max_connections;

• 非交互连接等待的时间 （单位s） 默认28800 = 8小时 如果线程空闲8小时，就会断开
  SELECT @@wait_timeout;

• 查询当前服务器支持的存储引擎
  SHOW ENGINES;

• 临时表空间

• 创建临时表
  CREATE TEMPORARY TABLE temp_table (
  id INT,
  name VARCHAR(50)
  );

• 查询临时表,不同事务查不到，事务关闭后自动删除
  SELECT * FROM temp_table;

• 查询页大小，默认16KB
  SELECT @@innodb_page_size;

• 默认行格式 默认为 dynamic
  SELECT @@innodb_default_row_format;

• 更改默认行格式
  SET GLOBAL innodb_default_row_format=DYNAMIC;

• 查询bufferPool 默认134217728字节 /1024/1024
  SELECT @@innodb_buffer_pool_size;

• 设置bufferPool的大小
  SET GLOBAL innodb_buffer_pool_size=402653184;

• 刷脏线程数

• 默认是4 但是不能超过buffer-pool的实例数
  SELECT @@innodb_page_cleaners;

• buffer-pool的实例数
  SELECT @@innodb_buffer_pool_instances;

• 通过以下2个参数来控制我内存中的脏页量

• 当脏页数量低于特定阈值时InnoDB存储引擎开始刷新脏页的行为，默认10
  SELECT @@innodb_max_dirty_pages_pct_lwm;

• 内存中可以存在的脏页的最大百分比
  SELECT @@innodb_max_dirty_pages_pct;

• 表示在空闲时刷新脏页的百分比默认100 将全部脏页刷新 越高 跟磁盘的IO越多，因为要刷新的脏页也会越多
  SELECT @@innodb_idle_flush_pct;

• 双写缓存区

• 双写，就是page页刷新到磁盘的时候，把这个page数据写到不同的地方去，当出现问题是，有备份来达到持久性跟数据的一致性。

• 默认开启 会加强一致性，但是会影响一定的性能
  SELECT @@innodb_doublewrite;

• 默认为2 至少2个备份
  SELECT @@innodb_doublewrite_files;

• 双写磁盘的位置配置
  SELECT @@innodb_doublewrite_dir;
  SELECT @@innodb_data_home_dir;

• 没有配置则用默认
  SELECT @@datadir;

• 哪些操作需要用到bufferpool
  SELECT @@innodb_change_buffering;

• changebuffer的空间大小

• 默认是内存空间的25%
  SELECT @@innodb_change_buffer_max_size;

• logbuffer大小 默认16M
  select @@innodb_log_buffer_size;

• RedoLog同步方案默认设置为1
  SELECT @@innodb_flush_log_at_trx_commit;

• 查询表占用内存
  select TABLE_NAME, concat(truncate(data_length/1024/1024,2),’ MB’)as data_size,
  concat(truncate(index_length/1024/1024,2),’ MB’) as index_size
  from information_schema.tables where
  TABLE_SCHEMA = ‘数据库名’ group by TABLE_NAME order by data_length desc;

• 查看各个库的内存使用情况
  select ‘all’ as TABLE_SCHEMA ,
  truncate(sum(data_length)/1024/1024/1024,0) as data_size,
  truncate(sum(index_length)/1024/1024/1024,0) as index_size
  from information_schema.tables
  union all
  select TABLE_SCHEMA,
  truncate(sum(data_length)/1024/1024/1024,0) as data_size,
  truncate(sum(index_length)/1024/1024/1024,0) as index_size
  from information_schema.tables group by TABLE_SCHEMA order by data_size desc;

• 释放空间的两种方案
  1、optimize table 数据库.表;
  2、alter table 数据库.表 ENGINE = ‘InnoDB’;