常用查询语句

查询没有主键的表

SELECT 
    TABLE_SCHEMA, TABLE_NAME
FROM
    information_schema.TABLES
WHERE
    TABLE_NAME NOT IN (SELECT DISTINCT
            (TABLE_NAME)
        FROM
            information_schema.COLUMNS
        WHERE
            COLUMN_KEY = 'PRI')
        AND TABLE_SCHEMA NOT IN ('information_schema' , 'performance_schema', 'mysql', 'sys');

 

查询线程参数大小

• MySQL 8

  SELECT 
      VARIABLE_NAME,
      VARIABLE_VALUE,
      CONCAT(VARIABLE_VALUE / 1024 / 1024, ' MB') AS VARIABLE_VALUE_MB
  FROM
      performance_schema.session_variables
  WHERE
      variable_name IN ('read_buffer_size' , 'read_rnd_buffer_size',
          'sort_buffer_size',
          'join_buffer_size',
          'binlog_cache_size',
          'tmp_table_size');

• MySQL 7

  SELECT 
      VARIABLE_NAME,
      VARIABLE_VALUE,
      CONCAT(VARIABLE_VALUE / 1024 / 1024, ' MB') AS VARIABLE_VALUE_MB
  FROM
      information_schema.SESSION_VARIABLES
  WHERE
      variable_name IN ('innodb_buffer_pool_size' , 'innodb_log_buffer_size',
          'innodb_additional_mem_pool_size',
          'key_buffer_size',
          'query_cache_size');

 

查询内存使用情况

• 根据事件

  SELECT 
      SUBSTRING_INDEX(event_name, '/', 2) AS code_area,
      FORMAT_BYTES(SUM(current_alloc)) AS current_alloc
  FROM
      sys.x$memory_global_by_current_bytes
  GROUP BY SUBSTRING_INDEX(event_name, '/', 2)
  ORDER BY SUM(current_alloc) DESC;

• 查询使用总量

  select * from sys.memory_global_total;

 

查询数据库大小

SELECT table_schema AS `Database`, 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`
FROM information_schema.tables
GROUP BY table_schema
ORDER BY `Size (MB)` DESC;

 

批量删除sleep状态的进程

SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE command = 'Sleep' AND time > 50 AND USER='jfrog';