MySQL 很实用的 SQL 语句清单（排障与日常运维）

这篇只放“能直接用”的 SQL小而实用适合收藏。一、查看连接与慢查询-- 当前连接SHOWPROCESSLIST;-- 只看活跃中的 SQLSELECT*FROMinformation_schema.PROCESSLISTWHERECOMMANDSleepORDERBYTIMEDESC;-- 慢查询是否开启SHOWVARIABLESLIKEslow_query_log;SHOWVARIABLESLIKElong_query_time;-- 最近慢查询文件位置SHOWVARIABLESLIKEslow_query_log_file;二、查看表大小与空间占用SELECTtable_schema,table_name,ROUND((data_lengthindex_length)/1024/1024,2)ASsize_mbFROMinformation_schema.TABLESWHEREtable_schemayour_dbORDERBYsize_mbDESC;三、查看索引使用情况-- 某表索引SHOWINDEXFROMyour_table;-- 索引列顺序组合索引很关键SELECTindex_name,seq_in_index,column_nameFROMinformation_schema.STATISTICSWHEREtable_schemayour_dbANDtable_nameyour_tableORDERBYindex_name,seq_in_index;四、找出无主键表SELECTt.table_schema,t.table_nameFROMinformation_schema.TABLEStLEFTJOINinformation_schema.TABLE_CONSTRAINTS cONt.table_schemac.table_schemaANDt.table_namec.table_nameANDc.constraint_typePRIMARY KEYWHEREt.table_schemayour_dbANDc.constraint_nameISNULL;五、查看锁等待排查阻塞-- InnoDB 锁等待SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_queryFROMinformation_schema.INNODB_LOCK_WAITS wJOINinformation_schema.INNODB_TRX rONr.trx_idw.requesting_trx_idJOINinformation_schema.INNODB_TRX bONb.trx_idw.blocking_trx_id;六、查重复数据最常用-- 查 user_id mobile 的重复SELECTuser_id,mobile,COUNT(*)AScntFROMuserGROUPBYuser_id,mobileHAVINGcnt1;七、批量去重保留最小 idDELETEt1FROMusert1JOINusert2ONt1.user_idt2.user_idANDt1.mobilet2.mobileANDt1.idt2.id;八、找出最近 N 天未更新的数据SELECT*FROMordersWHEREupdate_timeNOW()-INTERVAL30DAY;九、分页优化模板避免深分页-- 基于游标翻页SELECTid,create_timeFROMordersWHERE(create_time2026-04-01 10:00:00OR(create_time2026-04-01 10:00:00ANDid9527))ORDERBYcreate_timeDESC,idDESCLIMIT20;十、快速生成批量插入语句INSERTINTOdict(type,code,name)VALUES(status,0,禁用),(status,1,启用),(status,2,删除);十一、查看表行数估算SELECTtable_name,table_rowsFROMinformation_schema.TABLESWHEREtable_schemayour_dbORDERBYtable_rowsDESC;十二、找出表里“最常见的值”SELECTstatus,COUNT(*)AScntFROMordersGROUPBYstatusORDERBYcntDESCLIMIT5;最后总结这份 SQL 清单不是“概念”而是我日常排障和运维最常用的那一批。你可以直接复制用遇到具体场景再精细化调整。