MySQL问题排查

SHOW STATUS LIKE 'Threads_connected';

或

SELECT user, COUNT(*) as '当前连接数' FROM information_schema.processlist group by user;

正在执行命令

select id,command,state,info from information_schema.processlist where db='cmdb';

正在执行的事物

SELECT p.*
FROM information_schema.processlist p
JOIN information_schema.innodb_trx t ON p.id = t.trx_mysql_thread_id
WHERE p.db = 'cmdb';

查询general_log

select * from general_log
where thread_id in (
	select id from information_schema.processlist
	where db = 'cmdb'
	and time > 600
	and id in (
		SELECT trx_mysql_thread_id from information_schema.INNODB_TRX
	)
)
order by thread_id
limit
  1000

分析行锁的争夺情况

show status like 'innodb_row_lock%';

确认表是否在被使用

show open tables where in_use > 0;

当前出现的锁

# mysql 8.0.* 之前的版本使用该方式查询
SELECT * FROM information_schema.INNODB_LOCKS\G

# mysql 8.0.1 之后的版本使用
SELECT * FROM performance_schema.data_locks\G

由于锁占用导致等待的表

# mysql 8.0.1 之前的版本使用该方式查询
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G

# mysql 8.0.1 之后的版本使用
SELECT * FROM performance_schema.data_lock_waits\G

查看当前被锁的语句

SELECT * FROM performance_schema.events_statements_history
WHERE thread_id IN(
	SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a ,
	performance_schema.threads AS b 
	WHERE a.waiting_pid = b.`PROCESSLIST_ID`
)
ORDER BY timer_start ASC\G

看持有锁的语句

SELECT * FROM performance_schema.events_statements_history
WHERE thread_id IN(
	SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a ,
	performance_schema.threads AS b 
	WHERE a.`blocking_pid` = b.`PROCESSLIST_ID`
)
ORDER BY timer_start ASC\G

-- 查询用户
SELECT * FROM mysql.`user` WHERE USER LIKE '%apollo%';
SHOW GRANTS FOR apollo_portal;

-- 活动会话
SELECT * FROM information_schema.`PROCESSLIST` WHERE command<>'Sleep';

-- sql执行进度
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 AS COMPLETED FROM performance_schema.events_stages_current;

-- 事务查询
SELECT * FROM information_schema.`INNODB_TRX`

-- 查询会话轨迹
SELECT * FROM information_schema.`PROCESSLIST` WHERE USER='dba_jinlei';


SELECT c.* FROM information_schema.`PROCESSLIST` a JOIN
performance_schema.`threads` b ON a.id=b.`PROCESSLIST_ID`
JOIN performance_schema.`events_statements_history` c
ON b.`THREAD_ID`=c.`THREAD_ID`
WHERE a.id=186724037 ORDER BY c.`EVENT_ID` DESC;

-- sql逻辑读查询
FLUSH STATUS;
SELECT * FROM T WHERE uk_col='84b4da5889488167e66b';
SHOW STATUS WHERE variable_name LIKE '%handler%' OR variable_name LIKE '%created%';
SHOW  STATUS  LIKE  'last_query_cost';

-- 用户连接数汇总o
SELECT SUBSTR(HOST,1,INSTR(HOST,':')-1),COUNT(1) FROM information_schema.`PROCESSLIST` WHERE USER='pay_order' GROUP BY SUBSTR(HOST,1,INSTR(HOST,':')-1)

-- 查询字符集非utf8mb4的表
SELECT a.* FROM information_schema.`TABLES`  a
JOIN 
(SELECT DISTINCT table_schema,table_name FROM information_schema.`COLUMNS` t WHERE t.character_set_name<>'utf8mb4' AND table_schema NOT IN ('information_schema','performance_schema','sys','mysql'))
b
ON a.table_schema=b.table_schema
AND a.table_name=b.table_name

-- 修改字符集为utf8mb4
SELECT 
  CONCAT(
    'alter table ',
    table_schema,
    '.',
    table_name,
    ' convert to charset utf8mb4 collate utf8mb4_bin;'
  ) 
FROM
  information_schema.`TABLES` a 
WHERE a.`TABLE_SCHEMA` LIKE 'saict%' 
  AND EXISTS 
  (SELECT 
    1 
  FROM
    information_schema.`COLUMNS` b 
  WHERE a.`TABLE_NAME` = b.`TABLE_NAME` 
    AND a.`TABLE_SCHEMA` = b.`TABLE_SCHEMA` 
    AND b.`CHARACTER_SET_NAME` <> 'utf8mb4')
AND a.table_type='BASE TABLE'    
     ;

-- 行锁查询
SELECT * FROM sys.`innodb_lock_waits`;

-- 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';
SELECT * FROM performance_schema.`metadata_locks`;

-- 脏页比例
SELECT VARIABLE_VALUE INTO @a FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
SELECT VARIABLE_VALUE INTO @b FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
SELECT @a/@b;

-- 查询sql执行频率
DROP TABLE IF EXISTS dba.events_statements_summary_by_digest1;
DROP TABLE IF EXISTS dba.events_statements_summary_by_digest2;
CREATE TABLE `dba`.`events_statements_summary_by_digest1` (
   `schema_name` VARCHAR(64) NOT NULL,
   `digest` VARCHAR(32) NOT NULL,
   `digest_text` LONGTEXT,
   `count_star` BIGINT(20) SIGNED NOT NULL,
   `sum_timer_wait` BIGINT(20) SIGNED NOT NULL,
   `avg_timer_wait` BIGINT(20) SIGNED NOT NULL,
   `sum_lock_time` BIGINT(20) SIGNED NOT NULL,
   `sum_rows_affected` BIGINT(20) SIGNED NOT NULL,
   `sum_rows_sent` BIGINT(20) SIGNED NOT NULL,
   `sum_rows_examined` BIGINT(20) SIGNED NOT NULL,
   `statime` DATETIME NOT NULL,
   `first_seen` DATETIME NOT NULL,
   `last_seen` DATETIME NOT NULL,
   PRIMARY KEY (schema_name,`digest`, first_seen)
 );
 CREATE TABLE `dba`.`events_statements_summary_by_digest2` (
   `schema_name` VARCHAR(64) NOT NULL,
   `digest` VARCHAR(32) NOT NULL,
   `digest_text` LONGTEXT,
   `count_star` BIGINT(20) SIGNED NOT NULL,
   `sum_timer_wait` BIGINT(20) SIGNED NOT NULL,
   `avg_timer_wait` BIGINT(20) SIGNED NOT NULL,
   `sum_lock_time` BIGINT(20) SIGNED NOT NULL,
   `sum_rows_affected` BIGINT(20) SIGNED NOT NULL,
   `sum_rows_sent` BIGINT(20) SIGNED NOT NULL,
   `sum_rows_examined` BIGINT(20) SIGNED NOT NULL,
   `statime` DATETIME NOT NULL,
   `first_seen` DATETIME NOT NULL,
   `last_seen` DATETIME NOT NULL,
   PRIMARY KEY (schema_name,`digest`, first_seen)
 );
 
INSERT INTO dba.events_statements_summary_by_digest1
SELECT IFNULL(schema_name,''),digest,digest_text,count_star,sum_timer_wait,avg_timer_wait,sum_lock_time,sum_rows_affected,sum_rows_sent,sum_rows_examined,NOW() AS statime,first_seen,last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE digest IS NOT NULL;
 
SELECT SLEEP (10);
 
INSERT INTO dba.events_statements_summary_by_digest2
SELECT IFNULL(schema_name,''),digest,digest_text,count_star,sum_timer_wait,avg_timer_wait,sum_lock_time,sum_rows_affected,sum_rows_sent,sum_rows_examined,NOW() AS statime,first_seen,last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE digest IS NOT NULL;
 
 
SELECT a.schema_name,a.digest,a.digest_text,
        b.count_star - a.count_star AS exec_count,
        (b.count_star - a.count_star)/TIME_TO_SEC(TIMEDIFF(b.statime,a.statime)) AS QPS,
        (b.sum_timer_wait - a.sum_timer_wait)/POWER(10,12) AS waited_s,
        b.avg_timer_wait/POWER(10,12) AS avg_waited_s,
        (b.sum_lock_time - a.sum_lock_time)/POWER(10,12) AS locked_s,
        b.sum_rows_affected - a.sum_rows_affected AS rows_affected,
        b.sum_rows_sent - a.sum_rows_sent AS rows_sent,
        b.sum_rows_examined - a.sum_rows_examined AS rows_examined,
        TIME_TO_SEC(TIMEDIFF(b.statime,a.statime)) AS delta_secs,
        b.first_seen,b.last_seen
        ,b.sum_lock_time, a.sum_lock_time
FROM dba.events_statements_summary_by_digest1 a ,dba.events_statements_summary_by_digest2 b
WHERE a.digest = b.digest AND a.schema_name = b.schema_name AND b.count_star <> a.count_star AND a.first_seen = b.first_seen
ORDER BY (b.count_star - a.count_star) DESC;
 
DROP TABLE IF EXISTS dba.events_statements_summary_by_digest1;
DROP TABLE IF EXISTS dba.events_statements_summary_by_digest2;

-- 跟踪sql
SHOW VARIABLES	LIKE '%trace%';
SET optimizer_trace='enabled=on';
SELECT GROUP_CONCAT(`key`) FROM `item` ;
SELECT trace FROM information_schema.OPTIMIZER_TRACE;

-- 宽表与多索引表
SELECT  a.`TABLE_SCHEMA`,a.`TABLE_NAME`,a.col_cnt,b.idx_cnt FROM
(SELECT t.`TABLE_SCHEMA`,t.`TABLE_NAME`,COUNT(1) col_cnt FROM information_schema.`COLUMNS` t GROUP BY t.`TABLE_SCHEMA`,t.`TABLE_NAME`) a
JOIN 
(SELECT t.`TABLE_SCHEMA`,t.`TABLE_NAME`,COUNT(DISTINCT index_name) idx_cnt FROM information_schema.`STATISTICS` t GROUP BY t.`TABLE_SCHEMA`,t.`TABLE_NAME`) b
ON a.`TABLE_SCHEMA` =b.`TABLE_SCHEMA`
AND a.`TABLE_NAME`=b.`TABLE_NAME`
AND a.table_schema NOT IN ('dba','mysql')
JOIN information_schema.`TABLES` c
ON a.`TABLE_SCHEMA` =c.`TABLE_SCHEMA`
AND a.`TABLE_NAME`=c.`TABLE_NAME` 
AND C.`DATA_LENGTH`>1*POWER(1024,3)
ORDER BY 4 DESC,3 DESC;

--索引信息查询
SELECT * FROM mysql.`innodb_index_stats` ;

-- 索引使用情况
SELECT object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH FROM performance_schema.table_io_waits_summary_by_index_usage;

--未使用索引情况
SELECT * FROM sys.`schema_unused_indexes`

--查询使用排序的sql
select * from sys.x$statement_analysis where db not in ('sys','mysql') and 	rows_sorted <>0

--8.0索引不可见
alter table t1 alter index idx_name invisible/visible

-- 查询并清理digest
SHOW VARIABLES LIKE '%performance_schema_digests_size%';
SELECT COUNT(1) FROM performance_schema.`events_statements_summary_by_digest`;
TRUNCATE TABLE performance_schema.`events_statements_summary_by_digest`;

--重复索引查询
WITH TMP AS (
SELECT 
  table_schema,
  table_name,
  index_name,
  GROUP_CONCAT(column_name ORDER BY SEQ_IN_INDEX) cols,
  GROUP_CONCAT(SEQ_IN_INDEX ORDER BY SEQ_IN_INDEX)  seqs
FROM
  information_schema.`STATISTICS` a 
GROUP BY table_schema,
  table_name,
  index_name)
 SELECT A.* FROM TMP A
 JOIN TMP B
 ON A.table_schema=B.table_schema
 AND A.table_name=B.table_name
 AND A.cols=B.cols
 AND A.index_name<>B.index_name
 AND A.seqs=b.SEQS;

--列直方图信息
SELECT 
v value, 
CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') ratio
FROM information_schema.column_statistics, 
JSON_TABLE(histogram->'$.buckets','$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist
WHERE column_name = 'o_orderstatus';

-- 查询sql历史使用资源
SELECT * FROM performance_schema.`events_statements_summary_by_digest` t WHERE t.`DIGEST_TEXT` LIKE '%t_push_msg%';

--逗号分隔行转列
SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(a.mSize,',',b.help_topic_id+1),',',-1) 
FROM 
(SELECT '123,456,789' mSize  )a
JOIN
mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.mSize) - LENGTH(REPLACE(a.mSize,',',''))+1);

-- 表碎片率
SELECT table_schema,t.table_name,CONCAT(table_schema,'.',t.table_name) tb,file_size DIV POWER(2,30) size,
t.table_rows,data_free DIV POWER(2,30) free,data_free*100 DIV file_size pct
FROM information_schema.TABLES t,
(SELECT SUBSTRING_INDEX(NAME,'#',1) AS TABLE_NAME,SUM(file_size) AS file_size FROM information_schema.innodb_tablespaces
GROUP BY SUBSTRING_INDEX(NAME,'#',1)
HAVING SUM(file_size) > POWER(2,30) ) ts
WHERE ts.table_name = CONCAT(t.table_schema,'/',t.table_name)
-- and t.table_schema = 'saic_monitor_performance'
ORDER BY size DESC;	

--查询服务号
SELECT DISTINCT 
  a1.ATTR_VALUE AS serviceIp,
  a2.ATTR_VALUE AS serviceId 
FROM
  information_schema.PROCESSLIST p 
  LEFT JOIN performance_schema.session_connect_attrs a1 
    ON p.ID  =  a1.PROCESSLIST_ID 
    AND a1.ATTR_NAME  =   'serviceIp' 
  LEFT JOIN performance_schema.session_connect_attrs a2 
    ON p.ID  =  a2.PROCESSLIST_ID 
    AND a2.ATTR_NAME  =   'serviceId' ;

--生成序列化数据
select @rownum:=@rownum+1   from mysql.user t,mysql.user t2, (SELECT @rownum:=0) r  

--ipv4正则
SELECT 'jdbc:postgresql://172.16.81.13:5210/dentification?socketTimeout=60&connectTimeout=60' REGEXP  '((([[:digit:]]{1,2})|(1[[:digit:]]{2})|(2[0-4][[:digit:]])|(25[0-5]))\\.){3}(([[:digit:]]{1,2})|(1[[:digit:]]{2})|(2[0-4][[:digit:]])|(25[0-5]))([^[:digit:]]|$)'