(基础)MySQL实用SQL语句

背景

用于问题诊断

写在前面
(推荐阅读)原文链接为https://www.modb.pro/db/40447
link

操作

读者需要根据实际参数,以下参数5.7和8.0存在不同,修改以下模板。kill 的是PROCESSLIST_ID

1.连接相关

查看某用户连接的会话级别参数设置及状态变量,用于观测其它会话连接行为,辅助定位连接类问题
例:查看用户连接 ID 为 18 的字符集设置,也可不指定 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 T2.thread_id = T2.thread_id
  AND T1.variable_name LIKE 'character%'
  AND PROCESSLIST_ID ='18';

发现用户 ID 为 * 的 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';

2.长事务
事务开启后,超过 5s 未提交的用户连接
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx.trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx.trx_started) >= 5;
  
 3.元数据锁

MySQL 5.7 开启元数据锁追踪,以便追踪定位元数据锁相关的阻塞问题

// 临时开启,动态生效
select * from performance_schema.setup_consumers WHERE NAME ='global_instrumentation';
select * from performance_schema.setup_instruments WHERE NAME ='wait/lock/metadata/sql/mdl';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME ='global_instrumentation';

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME ='wait/lock/metadata/sql/mdl';

UPDATE performance_schema.setup_instruments
SET TIMED = 'YES'
WHERE NAME ='wait/lock/metadata/sql/mdl';

#配置文件中添加,重启生效
performance-schema-instrument = wait/lock/metadata/sql/mdl=ON

场景 1:杀掉持有 MDL 锁的会话,使 DDL 语句顺利执行。

DDL 语句被阻塞通常因为存在获取资源后未及时提交释放的长事务。因此,查找 kill 掉事务运行时间大于 DDL 运行时间的会话即可使 DDL 语句顺利下发,SQL 语句如下:
// 查找事务运行时间 >= DDL等待时间的线程
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >=
    (SELECT MAX(Time)
     FROM INFORMATION_SCHEMA.processlist
     WHERE STATE='Waiting for table metadata lock'
       AND INFO LIKE 'alter%table%' OR INFO LIKE 'truncate%table%') ;


// kill掉长事务,释放持有的MDL资源
kill 253;
注:因 MySQL 元数据信息记录有限,此处可能误杀无辜长事务,且误杀无法完全避免。

当 kill 掉阻塞源后,可能存在 DDL 语句与被阻塞的 SQL 语句同时加锁的情况,此时会出现事务开始时间等于 DDL 开始时间连接,此类事务也需 kill//查找事务开始时间 = DDL语句事务开始时间的线程
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND trx_started =
    (SELECT MIN(trx_started)
     FROM INFORMATION_SCHEMA.INNODB_TRX
     GROUP BY trx_started HAVING count(trx_started)>=2)
  AND TRX_QUERY NOT LIKE 'alter%table%'
  OR TRX_QUERY IS NULL;

//杀掉阻塞源
kill 255;
场景 2kill 掉下发 DDL 语句的用户连接,取消 DDL 语句下发,保障业务不被阻塞。
// 查找DDL语句所在用户连接

SELECT *
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE INFO LIKE 'ALTER%TABLE%'; 

+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| ID  | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                             |
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| 254 | root | localhost | NULL | Query   |  730 | Waiting for table metadata lock | alter table db.t1 add index (id) |
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
1 row in set (0.00 sec)
 

// 杀掉DDL语句所在用户连接
kill 254;

4.锁等待

查看锁等待相关的阻塞线程、被阻塞线程信息及相关用户、IP、PORT

SELECT locked_table,
       locked_index,
       locked_type,
       blocking_pid,
       concat(T2.USER,'@',T2.HOST) AS "blocking(user@ip:port)",
       blocking_lock_mode,
       blocking_trx_rows_modified,
       waiting_pid,
       concat(T3.USER,'@',T3.HOST) AS "waiting(user@ip:port)",
       waiting_lock_mode,
       waiting_trx_rows_modified,
       wait_age_secs,
       waiting_query
FROM sys.x$innodb_lock_waits T1
LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.ID
LEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;
+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+
| locked_table | locked_index | locked_type | blocking_pid | blocking(user@ip:port) | blocking_lock_mode | blocking_trx_rows_modified | waiting_pid | waiting(user@ip:port) | waiting_lock_mode | waiting_trx_rows_modified | wait_age_secs | waiting_query                   |
+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+
| `db`.`t1`    | PRIMARY      | RECORD      |          228 | dks@127.0.0.1:56724    | X                  |                          1 |         231 | root@127.0.0.1:50852  | S                 |                         0 |             1 | insert into db.t1(id) values(2) |
+--------------+--------------+-------------+--------------+------------------------+--------------------+------------------

若不关心阻塞相关的用户、IP、PORT,可直接查看 innodb_lock_waits 表信息。
select * from sys.x$innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2020-02-23 02:14:22
                    wait_age: 00:00:32
               wait_age_secs: 32
                locked_table: `db`.`t1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 7204404
         waiting_trx_started: 2020-02-23 02:14:18
             waiting_trx_age: 00:00:36
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 213
               waiting_query: delete from db.t1 where id=200
             waiting_lock_id: 7204404:1994:3:4
           waiting_lock_mode: X
             blocking_trx_id: 7204394
                blocking_pid: 207
              blocking_query: select * from   sys.x$innodb_lock_waits
            blocking_lock_id: 7204394:1994:3:4
          blocking_lock_mode: X
        blocking_trx_started: 2020-02-23 02:10:06
            blocking_trx_age: 00:04:48
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 207
sql_kill_blocking_connection: KILL 207
1 row in set, 3 warnings (0.00 sec)
影响锁等待超时的参数

5.全局读锁

PERFORMANCE_SCHEMA.METADATA_LOCKS 表 LOCK_DURATION 列为 EXPLICIT 状态表示 FTWRL 语句添加,OBJECT_TYPE 出现 COMMIT 状态表示已经加锁成功

场景 1:杀掉添加 FTWRL 的会话,恢复业务运行
SELECT processlist_id,
       mdl.OBJECT_TYPE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       LOCK_TYPE,
       LOCK_DURATION,
       LOCK_STATUS
FROM performance_schema.metadata_locks mdl
INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id
AND processlist_id <> connection_id()
AND LOCK_DURATION='EXPLICIT';
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS |
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
|            231 | GLOBAL      | NULL          | NULL        | SHARED    | EXPLICIT      | GRANTED     |
|            231 | COMMIT      | NULL          | NULL        | SHARED    | EXPLICIT      | GRANTED     |
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
2 rows in set (0.00 sec)
// 杀掉添加FTWRL的用户连接
kill 231;

场景 2:杀掉语句执行时间大于 FTWRL 执行时间的线程,确保 FTWRL 下发成功
SELECT T2.THREAD_ID,
       T1.ID AS PROCESSLIST_ID,
       T1.User,
       T1.Host,
       T1.db,
       T1.Time,
       T1.State,
       T1.Info,
       T3.TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME
FROM information_schema.processlist T1
LEFT JOIN performance_schema.threads T2 ON T1.ID=T2.PROCESSLIST_ID
LEFT JOIN information_schema.innodb_trx T3 ON T1.id=T3.trx_mysql_thread_id
WHERE T1.TIME >=
    (SELECT MAX(Time)
     FROM information_schema.processlist
     WHERE INFO LIKE 'flush%table%with%read%lock')
  AND Info IS NOT NULL;
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
| THREAD_ID | PROCESSLIST_ID | User | Host              | db   | Time | State                   | Info                                        | TRX_STARTED         | TRX_LAST_TIME |
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
|       284 |            246 | root | localhost         | NULL |  364 | User sleep              | select * from db.t1 where sleep(1000000000) | 2020-02-23 14:57:23 |           364 |
|       286 |            248 | root | 10.211.55.2:55435 | NULL |  232 | Waiting for table flush | flush table with read lock                  | NULL                |          NULL |
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+

内存使用监控

默认只对 performance_schema 库进行内存统计,对全局内存统计需要手工开启
//动态开启,开启后开始统计
update performance_schema.setup_instruments set
enabled = 'yes' where name like 'memory%';

select count(*) from performance_schema.setup_instruments;
select count(*) from performance_schema.setup_instruments where name like 'memory%';
//配置文件中添加,重启生效
performance-schema-instrument=‘memory/%=COUNTED’

查看实例内存消耗分布,sys 库下有多张 memory 相关视图用于协助用户定位分析内存溢出类问题

mysql8.0可以直接查:
SELECT event_name,
       current_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory%innodb%';
+-------------------------------------------+---------------+
| event_name                                | current_alloc |
+-------------------------------------------+---------------+
| memory/innodb/buf_buf_pool                | 134.31 MiB    |
| memory/innodb/log0log                     | 32.01 MiB     |
| memory/innodb/mem0mem                     | 15.71 MiB     |
| memory/innodb/lock0lock                   | 12.21 MiB     |
| memory/innodb/os0event                    | 8.37 MiB      |
| memory/innodb/hash0hash                   | 4.74 MiB      |
...

7.分区表

查看实例中的分区表相关信息
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 ;
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME       | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| db           | t1               |             365 |                0 | 5.70M       | 17.11M       | 22.81M     |
| db           | t2               |             391 |                0 | 6.11M       | 0.00M        | 6.11M      |
| db           | t3               |               4 |            32556 | 2.28M       | 0.69M        | 2.97M      |
| db           | t4               |              26 |                0 | 0.41M       | 2.44M        | 2.84M      |
| db           | t5               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      |
| db           | t6               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+

查看某分区表具体信息,此处以库名为 db、表名为 e 的分区表为例
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='db'
  AND TABLE_NAME='e';
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| db           | e          | p0             | id                   | RANGE            | 50                    |       4096 | 0.20M       | 0.09M        | 0.30M      |
| db           | e          | p1             | id                   | RANGE            | 100                   |       6144 | 0.28M       | 0.13M        | 0.41M      |
| db           | e          | p2             | id                   | RANGE            | 150                   |       6144 | 0.28M       | 0.13M        | 0.41M      |
| db           | e          | p3             | id                   | RANGE            | MAXVALUE              |      16172 | 1.52M       | 0.34M        | 1.86M      |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
4 rows in set (0.00 sec)

8.长时间未更新的表(可以先use 到库中,这样显示的就是当前库的表信息,否则是全部表)

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 ;
+--------------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | UPDATE_TIME         |
+--------------+------------+---------------------+
| ceshi100     | book       | NULL                |
| ceshi100     | tmp_table  | NULL                |
| ceshi100     | test       | NULL                |
| ceshi100     | t1_copy3   | NULL                |
| ceshi100     | t1_copy2   | NULL                |
| ceshi100     | t1         | 2021-03-02 14:04:10 |
+--------------+------------+---------------------+

验证:
insert into book values(12,'test01','test02',5.11);
+--------------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | UPDATE_TIME         |
+--------------+------------+---------------------+
| ceshi100     | t1_copy2   | NULL                |
| ceshi100     | tmp_table  | NULL                |
| ceshi100     | test       | NULL                |
| ceshi100     | t1_copy3   | NULL                |
| ceshi100     | t1         | 2021-03-02 14:04:10 |
| ceshi100     | book       | 2021-03-02 17:15:52 |
+--------------+------------+---------------------+

9.数据库信息
先use 库,显示的信息少
统计实例中各数据库大小
SELECT TABLE_SCHEMA,
       round(SUM(data_length+index_length)/1024/1024,2) AS TOTAL_MB,
       round(SUM(data_length)/1024/1024,2) AS DATA_MB,
       round(SUM(index_length)/1024/1024,2) AS INDEX_MB,
       COUNT(*) AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys',
                           'mysql',
                           'INFORMATION_SCHEMA',
                           'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;

+--------------+----------+---------+----------+--------+
| TABLE_SCHEMA | TOTAL_MB | DATA_MB | INDEX_MB | TABLES |
+--------------+----------+---------+----------+--------+
| ceshi100     |    28.70 |   28.64 |     0.06 |      6 |
+--------------+----------+---------+----------+--------+

统计某库下各表大小,修改TABLE_SCHEMA='ceshi100'
SELECT TABLE_SCHEMA,
       TABLE_NAME TABLE_NAME,
                  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,
                  engine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'performance_schema',
                           'sys',
                           'mysql')
  AND TABLE_SCHEMA='ceshi100'
ORDER BY (data_length + index_length) DESC LIMIT 10;

+--------------+------------+-------------+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | data_length | index_length | total_size | engine |
+--------------+------------+-------------+--------------+------------+--------+
| ceshi100     | tmp_table  | 28.56M      | 0.00M        | 28.56M     | InnoDB |
| ceshi100     | test       | 0.02M       | 0.02M        | 0.03M      | InnoDB |
| ceshi100     | t1_copy3   | 0.02M       | 0.02M        | 0.03M      | InnoDB |
| ceshi100     | t1_copy2   | 0.02M       | 0.02M        | 0.03M      | InnoDB |
| ceshi100     | t1         | 0.02M       | 0.02M        | 0.03M      | InnoDB |
| ceshi100     | book       | 0.02M       | 0.00M        | 0.02M      | 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 TABLE_SCHEMA='ceshi100'
ORDER BY table_rows DESC ;

+--------------+------------+-----------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME | table_collation | engine | table_rows |
+--------------+------------+-----------------+--------+------------+
| ceshi100     | tmp_table  | utf8_general_ci | InnoDB |     998568 |
| ceshi100     | book       | utf8_general_ci | InnoDB |         11 |
| ceshi100     | t1_copy3   | utf8_general_ci | InnoDB |          4 |
| ceshi100     | test       | utf8_general_ci | InnoDB |          3 |
| ceshi100     | t1_copy2   | utf8_general_ci | InnoDB |          0 |
| ceshi100     | t1         | utf8_general_ci | InnoDB |          0 |
+--------------+------------+-----------------+--------+------------+
6 rows in set (0.00 sec)

[root@localhost][ceshi100]> select count(*) from tmp_table;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+

存储引擎分布
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;

+--------------------+--------------------+----------+
| TABLE_SCHEMA       | ENGINE             | COUNT(*) |
+--------------------+--------------------+----------+
| mysql              | InnoDB             |       32 |
| performance_schema | PERFORMANCE_SCHEMA |      108 |
| ceshi100           | InnoDB             |        6 |
| mysql              | CSV                |        2 |
| sys                | InnoDB             |        1 |
+--------------------+--------------------+----------+
11.主键、索引

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

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 != ''
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';


无主键、唯一键表

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';


12.shell实时负载
新建一个1.sh
chmod +x 1.sh
./1.sh
脚本内容为:
mysqladmin  extended-status  -uroot -p123456 -i1|awk 'BEGIN{local_switch=0}
     $2 ~ /Queries$/            {q=$4-lq;lq=$4;}
     $2 ~ /com_commit$/         {c=$4-lc;lc=$4;}
     $2 ~ /Com_rollback$/       {r=$4-lr;lr=$4;}
     $2 ~ /Com_select$/       {s=$4-ls;ls=$4;}
     $2 ~ /Com_update$/       {u=$4-lu;lu=$4;}
     $2 ~ /Com_insert$/       {i=$4-li;li=$4;}
     $2 ~ /Com_delete$/       {d=$4-ld;ld=$4;}
     $2 ~ /Innodb_rows_read$/       {irr=$4-lirr;lirr=$4;}
     $2 ~ /Innodb_rows_deleted$/       {ird=$4-lird;lird=$4;}
     $2 ~ /Innodb_rows_inserted$/       {iri=$4-liri;liri=$4;}
     $2 ~ /Innodb_rows_updated$/       {iru=$4-liru;liru=$4;}
     $2 ~ /Innodb_buffer_pool_read_requests$/       {ibprr=$4-libprr;libprr=$4;}
     $2 ~ /Innodb_buffer_pool_reads$/       {ibpr=$4-libpr;libpr=$4;}
     $2 ~ /Threads_connected$/  {tc=$4;}
     $2 ~ /Threads_running$/    {tr=$4;
        if(local_switch==0)
                {local_switch=1; count=16}
        else {
                if(count>15) {
                    count=0;
                    print "----------------------------------------------------------------------------
-------------------------------------------------------- ";
                    print "Time-----|  QPS | Commit Rollback TPS | select insert update delete |  read 
inserted updated deleted | logical physical | Tcon Trun";
                    print "----------------------------------------------------------------------------
-------------------------------------------------------- ";
                }else{
                    count+=1;
                    printf "%s | %-5d| \n ",strftime("%H:%M:%S") , lq
                    printf "%s | %-5d| %-6d %-7d %-5d| %-7d %-7d %-5d %-6d| %-7d %-7d %-7d %-7d| %-6d  
%-9d| %-4d %-2d \n", strftime("%H:%M:%S"),q,c,r,c+r,s,u,i,d,irr,ird,iri,iru,ibprr,ibpr,tc,tr;
                }
        }
}'
13.binlog统计DDL&DML

统计DML:
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000006 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr

DELETE  `ceshi100`.`book`  3
INSERT  `ceshi100`.`book`  3
DELETE  `ceshi100`.`t1`    2

统计DDl:
mysqlbinlog  mysql-bin.000006 | awk 'BEGIN{IGNORECASE=1} {if($0~/alter/)count[$1" " $2" " $3" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3n
alter  table  t1  null  2

14.binlog并行复制统计(不好用)
mysqlbinlog mysql-bin.000001 --start-position=1000 | grep -o 'last_committed.*'  | sed 's/=/ /g' | awk '{print $2"\t"$4}' | awk '{count++;print $0;} END{print "total count is ",count}'
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 69271552, event_type: 10
total count is  

15.not utf8mb4 table

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
 from information_schema.TABLES 
WHERE TABLE_COLLATION not like 'utf8mb4'
 and table_schema not in ('information_schema' ,'mysql','performance_schema', 'sys');
+--------------+------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------+------------+-----------------+
| ceshi100     | book       | utf8_general_ci |
| ceshi100     | t1         | utf8_general_ci |
| ceshi100     | t1_copy2   | utf8_general_ci |
| ceshi100     | t1_copy3   | utf8_general_ci |
| ceshi100     | test       | utf8_general_ci |
| ceshi100     | tmp_table  | utf8_general_ci |
+--------------+------------+-----------------+
或者查看not utf8 table
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
 from information_schema.TABLES 
WHERE TABLE_COLLATION not like 'utf8%'
 and table_schema not in ('information_schema' ,'mysql','performance_schema', 'sys');


16.字符集验证

1.参看系统字符集:
show global variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

2.跟系统字符集不一样的数据库:Server characterset: utf8mb4
SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME  
from information_schema.SCHEMATA  b 
 WHERE  b.SCHEMA_NAME not in ('information_schema' ,'mysql','performance_schema', 'sys')  
and b.DEFAULT_COLLATION_NAME<>@@collation_server  ; 

+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| ceshi100    | utf8                       | utf8_general_ci        |
| ceshi555    | utf8                       | utf8_general_ci        |
| ceshi55555  | utf8                       | utf8_general_ci        |
| ceshi666    | utf8                       | utf8_general_ci        |
| ceshi66666  | utf8                       | utf8_general_ci        |
| ceshi888    | utf8                       | utf8_general_ci        |
| ceshi88888  | utf8                       | utf8_general_ci        |
+-------------+----------------------------+------------------------+

3.跟系统字符集不一样的表和字段:
select distinct tschema,tname,tcoll
 from 
 (
  select  a.TABLE_SCHEMA as tschema , a.TABLE_NAME as tname,a.TABLE_COLLATION  as tcoll 
 from information_schema.TABLES a 
 WHERE  a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema', 'sys') 
and a.TABLE_COLLATION<>@@collation_server 
union 
 select a.TABLE_SCHEMA as tschema, TABLE_NAME as tname, a.COLLATION_NAME  as tcoll  
 from information_schema.COLUMNS a
  WHERE  a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema', 'sys') 
and a.COLLATION_NAME<>@@collation_server ) as aa ;

+----------+-----------+-----------------+
| tschema  | tname     | tcoll           |
+----------+-----------+-----------------+
| ceshi100 | book      | utf8_general_ci |
| ceshi100 | t1        | utf8_general_ci |
| ceshi100 | t1_copy2  | utf8_general_ci |
| ceshi100 | t1_copy3  | utf8_general_ci |
| ceshi100 | test      | utf8_general_ci |
| ceshi100 | tmp_table | utf8_general_ci |
+----------+-----------+-----------------+



常用

17.CPU使用率过高


查看CPU飙高的mysql线程,top -H -p <mysqld进程id>
top -H -p 104406

根据具体PID,定位问题SQL

SELECT a.THREAD_OS_ID,b.id,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id and a.THREAD_OS_ID=<具体pid>;

18.kill应用会话
mysql>select concat('KILL ',id,';') from information_schema.processlist where time>10  and db is not null and command!='sleep' into outfile '/tmp/a.txt';
 OK,  rows affected (0.00 sec)
mysqQu2eryl>source  /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)


本文说明,主要技术内容来自互联网技术大佬的分享,还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问,请留言,将确认之后,执行侵权必删

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值