(基础)MySQL实用SQL语句

最新推荐文章于 2023-10-26 14:18:40 发布
最新推荐文章于 2023-10-26 14:18:40 发布
阅读量159 收藏
点赞数
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/baidu_34007305/article/details/114288719
版权

背景

用于问题诊断

写在前面
(推荐阅读)原文链接为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;
场景 2：kill 掉下发 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)

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

请给我的爱人一杯mojito
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql简介及sql语句
哈哈
06-09 848
一、数据库的概述 Mysql: DBMS(数据库管理系统 DataBase Manager System) 概述：数据库(Database)是按照数据结构组织、存储和管理数据的仓库，它产生于距今六十多年前，数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统，在各个方面得到了广泛的应用。在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进
MySQL部署和SQL语句基础
04-12
MySQL数据系统部署及SQL语句基础 适合新手入门观看 详细解释基础命令的使用方式及含义
达梦数据库-运维相关SQL整理
wswxs1的博客
04-24 481
作为一个达梦的初学者，写这篇文章的目的是为了把学到的新知识做个总结梳理，在整理中查缺补漏，也希望大家看了我的文章能有收获，如有错误的地方欢迎指出。 --查询当前事务的ID SELECT TRX_ID FROM V$SESSIONS WHERE SESS_ID = SESSID(); --查询当前会话中运行已经2秒的SQL select * from (SELECT sess_id,sql_text,datediff(ss,last_send_time,sysdate) ss, SF_GET_SE
查询正在执行的sql语句
qq_38773692的博客
10-25 3803
查询正在执行的sql语句 SELECT * FROM information_schema.PROCESSLIST WHERE info IS NOT NULL 查看死否有死锁 SHOW PROCESSLIST; SELECT * FROM information_schema.innodb_trx; SHOW OPEN TABLES WHERE In_use > 0; SELEC...
MySQL查看正在运行的SQL
壹升茉莉清的博客
07-26 1万+
​在安装MySQL的时候会默认初始化几个MySQL运行所需的数据库mysql,sys,information_schema,performance_schema.这几个库存储了MySQL在运行过程中的配置信息，运行信息，参数配置，数据库信息，表信息等等。今天这个要查看正在运行的SQL主要用到的是information_schema和performance_schema这两个库。............
Mysql性能排查—结合操作系统线程 查看mysql中的sql资源消耗
翔之天空的专栏
12-20 2063
当系统资源紧张时，可以通过系统的线程id 来查找对应mysql的sql语句。 以便快速定位问题。 --一、结合操作系统线程 查看mysql中的sql资源消耗 (5.7才可以， 5.7时performance_schema.threads表才加入的thread_os_id系统线程字段) --1、top -H 查看具体线程的CPU消耗 [root@h...
Mysql数据库sql语句
小罗的博客
06-10 5595
目录一、MySQl概述关系型数据库(RDBMS)二、SQL2.3.DDL2.4.DML2.5.DQL2.6.DCL概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库特点：使用表存储数据，格式统一，便于维护使用SQL语言操作，标准统一，使用方便mysql启动 mysql客户端连接： MySQL数据模型数据库表2.1.SQL通用语法SQL语句可以单行或多行书写，以分号结尾SQL语句可以使用空格/缩进来增强语句的可读性MySQL数据库的SQL语句不区分大小写，关键字建议使用大写注释：单行注释：
MySQL常用SQL语句大全
热门推荐
江南极客
12-21 18万+
MySQL数据库是一个十分轻便的数据库管理系统，相比大型的数据库管理系统如Oracle、MS-SQL ，MySQL更拥有轻便、灵活、开发速度快的特色，更适用于中小型数据的存储与架构。MySQL之所以能够被数以万计的网站采用，也是由此而来。
MySQL常用sql语句生成器
03-22
SQL语句生成器则是帮助用户快速、准确地构建SQL查询的实用工具，尤其适合初学者和那些不熟悉复杂SQL语法的用户。下面将详细介绍MySQL中的SQL语句及其生成器的相关知识。 1. SQL基础概念 SQL，全称为结构化查询语言...
MySql数据库SQL语句.pdf
最新发布
05-21
根据提供的文件信息，本文将详细解析MySQL数据库中的SQL语句，特别是针对数据库表记录的CRUD操作（创建、读取、更新、删除），并探讨分页查询与LIMIT方言的使用方法，以及SELECT语句的执行顺序等内容。 ### MySQL...
mysql sql语句总结
09-11
MySQL SQL语句是数据库操作的核心，它用于在MySQL数据库中执行各种任务，如创建数据库、定义数据表结构、插入和更新数据以及查询信息。以下是对标题和描述中提及的SQL语句的详细解释： 1. **创建数据库**： 使用`...
MySQL学习基础-sql语句部分
05-18
用Xmind制作的思维导图
mysql 常用sql语句_Mysql常用SQL语句
weixin_39737233的博客
01-27 58
概念SQL分为四大类。分别是：DDL数据定义语句， 用于创建、修改和删除数据库内的数据结构。DQL数据查询语句，用于查询数据。DML数据操作语句，用于对数据的增，删，改。DCL数据控制语句，用于对数据的访问控制。DDL 数据定义CREATE语句，用来创建库，表和索引。ALTER语句，用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等更改现有的表结构操作。RENAME语句，用来重命名表。D...
MySQL初始化之后启动报错(mysqld: Table ‘mysql.plugin‘ doesn‘t exist）
一杯Mojito可以吗
10-26 4790
初始化之后，服务无法启动。
清理MySQL表碎片（Table Fragmentation）
一杯Mojito可以吗
11-03 2412
使用背景 表的 DATA_FREE 碎片较多需要清理，也可以理解为减少表碎片。 以下采用倒叙的方法(先介绍解决办法，再介绍原理)，读者可以按照自己的时间或是喜好阅读。 操作方法 建议使用：ALTER TABLE xxxx ENGINE=InnoDB; ANALYZE TABLE xxxx; 进行操作，这也是官方推荐的表碎片优化操作。 第三方工具 pt工具或者gh-ost（大数量的环境下工具更好）。 以下为原理和可能使用到的sql语句 在MySQL中，可以使用OPTIMIZE TABLE、ALTER
Mysql数据同步工具pt-table-sync
一杯Mojito可以吗
01-21 2404
背景 percona公司的 pt-table-checksum和pt-table-sync，前者用来实现主从复制数据一致性的校验，后者实现数据修复，将数据修复到一致。 本次讨论的是主从架构，不包括双主 对于是主主复制架构来说，变更操作必须在目标端数据库进行，在变更的同时需要指定选项–no-bin-log，即变更的操作不写入binlog中，否则变更操作会反向复制到另一台主库中执行变更操作，造成数据的不一致。 分为三种情况 有主键或是唯一键（本文内容） 没有主键或是唯一键(可以在主从库上添加) 有
flush privileges 命令
一杯Mojito可以吗
11-26 2113
背景 Mysql权限修改,是不影响已经建立的连接的。那什么时候使用flush privileges 命令呢？ 写在前面 参考链接为https://www.cnblogs.com/softidea/p/12897530.html link 原理 在Mysql中，保存用户权限有三个地方 （1）已经建立的数据库连接中。grant，revoke或直接改db，不影响已经建立连接的权限情况 （2）Mysql服务器内存中的acl_users 数组 （3）db的mysql.user表中 flush privilege
(基础)Mysql 的 create as 和create like 区别
一杯Mojito可以吗
02-20 1948
背景 复制表结构或是数据 写在前面 原文链接https://www.cnblogs.com/phpper/p/10773004.html link Mysql 5.6.51 GTID复制 例子 DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID，自增', `uid` bigint(11) UNSIGNED NOT NULL DEFAULT
(压测)sysbench1.0.20压测Mysql
一杯Mojito可以吗
01-29 1781
背景 sysbench是基于LuaJIT的可编写脚本的多线程基准测试工具。它最常用于数据库基准测试，但也可用于创建不涉及数据库服务器的任意复杂的工作负载。(本次我们只讨论对应Mysql的压力测试内容，对于它支持的postgresql不讨论) 写在前面 本文来自知数堂 https://github.com/zhishutech/mysqldba link 官方链接https://github.com/akopytov/sysbench link 详细内容请阅读原文文档 sysbench安装 https:/
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值