MySQL常用SQL

原创谭磊Terry 恩墨学院 2022-06-10

390

一、数据库实时状态查询

1、计算数据库字段的cardinality值

select count(distinct table_colum) / count(*) as cardinality  from table_name;

2、实时（每秒）查看数据库当前processlist线程状态

mysqladmin -h100.64.102.81 -P3307 -uroot -p -r -i 1 pr

3、实时(每秒)查看一次数据库当前的增删改查

mysqladmin -h100.64.102.81 -P3307 -uroot -p extended-status -r -i 1|grep -Ewi 'com_insert|com_delete|com_update|com_select'

4、实时(每秒)查看数据库QPS

mysqladmin -h100.64.102.81 -P3307 -uroot -p -i 1 -r extended-status|grep -Ewi 'questions'

5、实时查看MySQL状态信息

mysqladmin -h100.64.102.81 -P3307 -uroot -p -i 1 status

6、更改表的存储引擎

alter table table_name engine=innodb;

7、内存消耗查看

P_S监控数据，整体全局等多个角度查看内存消耗统计

SELECT event_name,COUNT_ALLOC,SUM_NUMBER_OF_BYTES_ALLOC/1024/1024/1024,CURRENT_COUNT_USED,CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024,HIGH_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name ORDER BY SUM_NUMBER_OF_BYTES_ALLOC DESC  LIMIT 10;

sys视图

SELECT event_name,current_alloc,high_alloc FROM sys.memory_global_by_current_bytes where current_count > 0 limit 10;

打开内存监控
update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';

查看监控
select * from sys.x$memory_by_host_by_current_bytes   ;
select * from sys.x$memory_by_thread_by_current_bytes ;
select * from sys.x$memory_by_user_by_current_bytes   ;
select * from sys.x$memory_global_by_current_bytes    ;
select * from sys.x$memory_global_total               ;

event_name 内存事件名称

current_count 事件发生的总次数。

current_alloc 事件尚未释放的已分配字节的当前数目。

current_avg_alloc
为事件的每个内存块分配的当前字节数。

high_count
分配给事件的内存块数量的最高标记

high_alloc
为事件分配的字节数的高水位标记。

high_avg_alloc
为事件分配的每个内存块的平均字节数的最高标记。

综合查看都有哪些内部线程消耗了更多内存

select pm.THREAD_ID,pm.event_name,pm.SUM_NUMBER_OF_BYTES_ALLOC/1024/1024/1024,sm.user,sm.total_allocated  
from  performance_schema.memory_summary_by_thread_by_event_name pm,sys.memory_by_thread_by_current_bytes sm where pm.THREAD_ID=sm.thread_id order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;

sys

select * from sys.memory_by_thread_by_current_bytes ORDER BY total_allocated desc LIMIT 10;

8、CPU高的线程定位

使用 performance_shema 时，需要注意 MySQL 用到了多个线程编号，源于不同视角：

PROCESSLIST_ID：在 processlist 中的编号是使用者视角的编号，使用者可以直接用 kill 命令
THREAD_ID：是 MySQL 内部使用的线程编号，是 MySQL 内部视角的编号
THREAD_OS_ID：在操作系统上，对应的线程编号，是操作系统视角的编号

# 用 top 命令找到 CPU 高的线程
top -H

use performance_shema;
select * from performance_shema.threads where thread_os_id = xxx\G;
select * from performance_shema.events_statement_current where THREAD_ID (select THREAD_ID from performance_shema.threads where thread_os_id = xxx);

9、远程获取MySQL数据库的Binlog日志解析后保留到本地

mysqlbinlog --read-from-remote-server -h10.186.30.38 -uapp -papp -P3306  mysql-bin.000004 >mysql-bin.000004.sql

10、连接相关

看某用户连接的会话级别参数设置及状态变量，用于观测其它会话连接行为，辅助定位连接类问题

例：查看用户连接 ID 为 19 的字符集设置，也可不指定 PROCESSLIST_ID 条件，查看所有用户连接

SELECT T1.VARIABLE_NAME,
       T1.VARIABLE_VALUE,
       T2.PROCESSLIST_ID,
       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST),
       T2.PROCESSLIST_DB,
       T2.PROCESSLIST_COMMAND
FROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1,
     PERFORMANCE_SCHEMA.THREADS T2
WHERE T1.THREAD_ID = T2.THREAD_ID
  AND T1.VARIABLE_NAME LIKE 'character%'
  AND PROCESSLIST_ID ='19';
+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+
| VARIABLE_NAME            | VARIABLE_VALUE | PROCESSLIST_ID | concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) | PROCESSLIST_DB | PROCESSLIST_COMMAND |
+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+
| character_set_client     | gbk            |             19 | root@localhost                                      | db             | Query               |
| character_set_connection | gbk            |             19 | root@localhost                                      | db             | Query               |
| character_set_database   | utf8mb4        |             19 | root@localhost                                      | db             | Query               |
| character_set_filesystem | binary         |             19 | root@localhost                                      | db             | Query               |
| character_set_results    | gbk            |             19 | root@localhost                                      | db             | Query               |
| character_set_server     | utf8mb4        |             19 | root@localhost                                      | db             | Query               |
+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+
6 rows in set (0.01 sec)

发现用户 ID 为 254 的连接关闭了 sql_log_bin 设置

SELECT T1.VARIABLE_NAME,
       T1.VARIABLE_VALUE,
       T2.PROCESSLIST_ID,
       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host',
       T2.PROCESSLIST_DB,
       T2.PROCESSLIST_COMMAND
FROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1,
     PERFORMANCE_SCHEMA.THREADS T2
WHERE T1.THREAD_ID = T2.THREAD_ID
  AND T1.VARIABLE_NAME LIKE 'sql_log_bin';
+---------------+----------------+----------------+------------------+----------------+---------------------+
| VARIABLE_NAME | VARIABLE_VALUE | PROCESSLIST_ID | User@Host        | PROCESSLIST_DB | PROCESSLIST_COMMAND |
+---------------+----------------+----------------+------------------+----------------+---------------------+
| sql_log_bin   | OFF            |            254 | root@localhost   | NULL           | Sleep               |
| sql_log_bin   | ON             |            256 | root@localhost   | NULL           | Sleep               |
| sql_log_bin   | ON             |            257 | root@10.211.55.2 | NULL           | Sleep               |
| sql_log_bin   | ON             |            258 | root@10.211.55.2 | NULL           | Sleep               |
| sql_log_bin   | ON             |            259 | root@localhost   | NULL           | Query               |
| sql_log_bin   | ON             |            261 | root@localhost   | NULL           | Sleep               |
+---------------+----------------+----------------+------------------+----------------+---------------------+
4 rows in set (0.00 sec)

例：查看用户连接 ID 为 24 的网络流量变化

SELECT T1.VARIABLE_NAME,
       T1.VARIABLE_VALUE,
       T2.PROCESSLIST_ID,
       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host',
       T2.PROCESSLIST_DB,
       T2.PROCESSLIST_COMMAND
FROM PERFORMANCE_SCHEMA.STATUS_BY_THREAD T1,
     PERFORMANCE_SCHEMA.THREADS T2
WHERE T1.THREAD_ID = T2.THREAD_ID
  AND T2.PROCESSLIST_USER = 'root'
  AND PROCESSLIST_ID= 24
  AND VARIABLE_NAME LIKE 'Byte%';
+----------------+----------------+----------------+----------------+----------------+---------------------+
| VARIABLE_NAME  | VARIABLE_VALUE | PROCESSLIST_ID | User@Host      | PROCESSLIST_DB | PROCESSLIST_COMMAND |
+----------------+----------------+----------------+----------------+----------------+---------------------+
| Bytes_received | 224            |             24 | root@127.0.0.1 | NULL           | Sleep               |
| Bytes_sent     | 182            |             24 | root@127.0.0.1 | NULL           | Sleep               |
+----------------+----------------+----------------+----------------+----------------+---------------------+
2 rows in set (0.00 sec)

11、实时负载

while true
do 
mysqladmin -uroot -pxxxxxxx extended-status -r -i 1 -c 30 --socket=/mysqldata/mysqld.sock 2>/dev/null|awk -F"|" "BEGIN{ count=0 ;}"'{ if($2 ~ /Variable_name/ && ++count == 1){\
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}' 
done

三、数据库信息查询

1、数据库大小

SELECT
table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'size(M)'
FROM
information_schema.tables
WHERE
table_schema NOT IN ('information_schema' , 'performance_schema', 'mysql', 'sys')
GROUP BY table_schema;

2、查看某库下表的基本信息

表行数、校验集、存储引擎、行数、更新时间

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       table_collation,
       engine,
       table_rows,
       UPDATE_TIME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'sys',
                           'mysql',
                           'performance_schema')
  AND TABLE_name='gls_acco_entries_today'
  AND TABLE_SCHEMA='prodccsdb'
ORDER BY table_rows DESC;

3、各数据库表大小

SELECT
    table_schema,
    table_name,
    table_rows,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'table_size(M)',
    ROUND(data_length / 1024 / 1024, 2) AS 'data_size(M)',
    ROUND(index_length / 1024 / 1024, 2) AS 'index_size(M)'
FROM
    information_schema.TABLES t
WHERE
    t.TABLE_SCHEMA NOT IN ('information_schema' , 'performance_schema', 'mysql', 'sys')
ORDER BY data_length DESC;
 
SELECT table_schema, table_name,table_rows, round((data_length + index_length) / 1024 / 1024 , 2) AS 'table_size(M)' , round(data_length / 1024 / 1024 , 2) AS 'data_size(M)' , round(index_length / 1024 / 1024 , 2) AS 'index_size(M)' FROM information_schema. TABLES t WHERE t.TABLE_SCHEMA NOT IN( 'information_schema' , 'performance_schema' , 'mysql' , 'sys') ORDER BY data_length DESC;

4、长时间未更新的表

UPDATE_TIME 为 NULL 表示实例启动后一直未更新过

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND TABLE_TYPE='BASE TABLE'
ORDER BY UPDATE_TIME ;

5、删除所有数据库下所有表

select concat('drop table ',table_schema,'.',table_name) from information_schema.tables where table_schema not in ('information_schema','sys','mysql','performance_schema');
可以直接使用
mysql -uroot -proot@cib -S /data/mysql/data/3306/mysql_3306.sock -e "select concat('drop database if exists ',table_schema,';') from information_schema.tables where table_schema not in ('information_schema','sys','mysql','performance_schema') group by table_schema;" > /root/drop_database.sql

6、表分区

查看表分区总体信息

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       count(PARTITION_NAME) AS PARTITION_COUNT,
       sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS,
       CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH,
       CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH,
       CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME NOT IN ('sys',
                         'mysql',
                         'INFORMATION_SCHEMA',
                         'performance_schema')
  AND PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA,
         TABLE_NAME
ORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;

查看某分区表具体信息

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       PARTITION_NAME,
       PARTITION_EXPRESSION,
       PARTITION_METHOD,
       PARTITION_DESCRIPTION,
       TABLE_ROWS,
       CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH,
       CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH,
       CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
                         'mysql',
                         'INFORMATION_SCHEMA',
                         'performance_schema')
  AND PARTITION_NAME IS NOT NULL
  AND TABLE_SCHEMA='prodccsdb'
  AND TABLE_NAME='gls_acco_entries_today'

新增表分区

ALTER TABLE ccs_loan_bal_rpt1 ADD PARTITION (PARTITION part367 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

删除分区

alter table ccs_loan_bal_rpt1 REMOVE PARTITION part367;

7、查看binlog

show binary logs;
mysqlbinlog -v --base64-output=decode-rows --start-datetime="2019-05-05 00:50" --stop-datetime="2019-05-05 01:10" master-bin.000020 > /tmp/0505.sql

8、导出所有表结构

mysqldump -S /xfjrdata/xfjrdb_32.sock -uroot -p -d -A --skip-lock-tables > ddl.sql

9、主键、索引

无主键、唯一键及二级索引基表

MySQL Innodb 存储引擎为索引组织表，因此设置合适的主键字段对性能至关重要

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND T2.TABLE_TYPE='BASE TABLE'
  AND T1.TABLE_SCHEMA='db'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING MAX(COLUMN_KEY)='';

无主键、唯一键，仅有二级索引表

类型表因无高效索引，因此从库回放时容易导致复制延迟

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS  T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND T2.TABLE_TYPE='BASE TABLE'
  AND T1.COLUMN_KEY != ''
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';

仅有主键、唯一键表

该类型表结构因无二级索引，可能导致应用 SQL 语句上线后频繁全表扫描出现性能抖动

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND T2.TABLE_TYPE='BASE TABLE'
  AND T1.COLUMN_KEY != ''
  AND T1.TABLE_SCHEMA='db'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'MUL';

无主键、唯一键表

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
AND   T2.TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';

10、存储引擎

存储引擎分布

SELECT TABLE_SCHEMA,
       ENGINE,
       COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'PERFORMANCE_SCHEMA',
                           'SYS',
                           'MYSQL')
  AND TABLE_TYPE='BASE TABLE'
GROUP BY TABLE_SCHEMA,
         ENGINE;

非 INNODB 存储引擎表

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_COLLATION,
       ENGINE,
       TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'SYS',
                           'MYSQL',
                           'PERFORMANCE_SCHEMA')
  AND TABLE_TYPE='BASE TABLE'
  AND ENGINE NOT IN ('INNODB')
ORDER BY TABLE_ROWS DESC ;

四、数据传输

1、使用create table like方式克隆表

create table actor_like like actor;

表除了没有数据之外，结构被进行了完整克隆

2、like方式的表插入数据

insert into actor_like select * from actor;

五、日常维护

5.1 查看大表update进度

use performance_schema;
# 查看 ROW_EXAMINED 
select * from events_statement_current where SQL_TEXT like 'update%';

5.2 获取binglog中事务大小

~mysql-bin.000001 \
| grep "GTID|(printf '\t')last committed" -B 1\
| grep -E '^# at' | awk '{print $3}' \
| awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp);tmp=$1}' \
| sort -n -r | head -n 10

5.3 大SQL回放限速

PV 工具既可以用于显示文件流的进度，也可用用于文件流的限速。

./use -e 'create database test3'
pv -p -a -L1m dump.sql | ./use test3

墨力计划 mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者