mysql -uroot -p'password'
mysql -uroot -p'password' -h 127.0.0.1 -P 3306
mysql -uroot -p'password' -S /path/to/mysql.sock
2. 查看当前数据库中的会话状态
show processlist;
3. 查看当前数据库中的活动会话（排除掉空闲Sleep状态的会话）
select * from information_schema.processlist where command <> 'Sleep';
--8.0以后版本建议使用performance_schema:
select * from performance_schema.processlist where command <> 'Sleep';
--排除掉自己的会话连接
select * from information_schema.processlist where command <> 'Sleep' and id <> connection_id();
select * from performance_schema.processlist where command <> 'Sleep' and id <> connection_id();
--也可以通过其他条件来排查掉自己不想要的会话信息：如user in 或者 db in ,host等查询条件来过滤。
4. 查看数据库的总大小
--数据库总大小
select round(sum(data_length+index_length)/1024/1024/1024,2) as 'DBSIZE_GB' from information_schema.tables;
5. 查看数据库中各个库的大小合计
--数据库大小信息：
select table_schema,round(sum(data_length+index_length)/1024/1024/1024,3) as 'SIZE_GB' from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') group by table_schema ;
6. 查看数据库中的TOP 30大表信息
--Top 30大表信息：
select table_schema,table_name,round((data_length+index_length)/1024/1024,2) as 'SIZE_MB',table_rows,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') order by 3 desc limit 30 ;
7. 查看表和索引的统计信息：
--表统计信息：
select * from mysql.innodb_table_stats where database_name='db_name' and table_name='table_name';
--索引统计信息：
select * from mysql.innodb_index_stats where database_name='' and table_name='' and index_name='idx_name';
8. 查询锁等待时持续间大于20秒的SQL信息
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 sys.innodb_lock_waits lw ON trx.trx_mysql_thread_id=lw.waiting_pid
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) >= 20 ;
9. 查询MySQL锁等待表的详细信息
-- sys库锁等待表：
select * from sys.innodb_lock_waits\G
10. 查询长事务SQL
--长事务（包含未关闭的事务）
SELECT thr.processlist_id AS mysql_thread_id,
concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
Command,
FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
current_statement as `latest_statement`
FROM performance_schema.events_transactions_current trx
INNER JOIN performance_schema.threads thr USING (thread_id)
LEFT JOIN sys.processlist p ON p.thd_id=thread_id
WHERE thr.processlist_id IS NOT NULL
AND PROCESSLIST_USER IS NOT NULL
AND trx.state = 'ACTIVE'
GROUP BY thread_id, timer_wait
ORDER BY TIMER_WAIT DESC LIMIT 10;
11. 查看当前DDL执行的进度
use performance_schema;
select * from setup_instruments where name like 'stage/innodb/alter%';
select * from setup_consumers where name like '%stages%';
--如果上面查询结果为NO，则需要做如下配置：
update set_instrucments set enabled = 'YES' where name like 'stage/innodb/alter%';
update set_consumers set enabled = 'YES' where name like '%stages%';
-- 查询DDL执行的进度：
select stmt.sql_text,
stage.event_name,
concat(work_completed, '/', work_estimated) as progress,
concat(round(100 * work_completed / work_estimated, 2), ' %') as processing_pct,
sys.format_time(stage.timer_wait) as time_costs,
concat(round((stage.timer_end - stmt.timer_start) / 1e12 *
(work_estimated - work_completed) / work_completed,
2),
' s') as remaining_seconds
from performance_schema.events_stages_current stage,
performance_schema.events_statements_current stmt
where stage.thread_id = stmt.thread_id
and stage.nesting_event_id = stmt.event_id\G
12. 执行次数最多的TOP 10 SQL
--执行次数前10 SQL
SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10;
13. 平均响应时间最长的TOP 10 SQL
--平均响应时间TOP 10 SQL
SELECT QUERY_SAMPLE_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10;
14. 排序次数最多的TOP 10 SQL
--排序此时最多TOP 10 SQL
SELECT QUERY_SAMPLE_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10;
15. 扫描记录数最多的 TOP 10 SQL
--扫描行最多的 TOP 10 SQL
SELECT QUERY_SAMPLE_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;
16. 使用临时表最多的TOP 10 SQL
--使用临时表最多的TOP 10 SQL
SELECT QUERY_SAMPLE_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES DESC LIMIT 10;
17. 查询从未使用过的索引
--从未使用过的索引：未使用索引建议直接删除，多余索引如不使用会影响增删改性能，且索引占用磁盘空间。
select * from schema_unused_indexes where object_schema not in ('performance_schema');
18. 查询冗余索引
--冗余索引建议删除
select * from schema_redundant_indexes;
19. 查询数据库中没有主键的表
--查询所有无主键表：
SELECT A.table_schema, A.table_name
FROM information_schema.tables AS A
LEFT JOIN (SELECT table_schema, table_name FROM information_schema.statistics WHERE index_name = 'PRIMARY') AS B
ON A.table_schema = B.table_schema AND A.table_name = B.table_name
WHERE A.table_schema NOT IN ('information_schema' , 'mysql','performance_schema', 'sys')
AND A.table_type='BASE TABLE'
AND B.table_name IS NULL;
20. 查询非InnoDB表
--非innodb表
SELECT table_schema,table_name,engine FROM information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema') and engine!='InnoDB';
