MySQL日常监控及sys库的使用

墨蓝 2024-03-25 22:17 35阅读 0赞

\# MySQL日常监控及sys库的使用

\#\# 一、统计信息（SQL维度）

1、统计执行次数最多的SQL语句：

\`\`\`sql  
SELECT  
DIGEST\_TEXT,  
COUNT\_STAR,  
FIRST\_SEEN,  
LAST\_SEEN  
FROM  
\`performance\_schema\`.events\_statements\_summary\_by\_digest  
ORDER BY  
COUNT\_STAR DESC;  
\`\`\`

2、查看平均响应时间最多的sql语句：

\`\`\`sql  
SELECT  
DIGEST\_TEXT,  
AVG\_TIMER\_WAIT,  
COUNT\_STAR,  
FIRST\_SEEN,  
LAST\_SEEN  
FROM  
\`performance\_schema\`.events\_statements\_summary\_by\_digest  
ORDER BY  
AVG\_TIMER\_WAIT DESC;  
\`\`\`

3、查看排序记录数最多的sql

\`\`\`sql  
SELECT  
DIGEST\_TEXT,  
SUM\_SORT\_ROWS,  
COUNT\_STAR,  
FIRST\_SEEN,  
LAST\_SEEN  
FROM  
\`performance\_schema\`.events\_statements\_summary\_by\_digest  
ORDER BY  
SUM\_SORT\_ROWS DESC;  
\`\`\`

4、扫描记录数最多的sql

\`\`\`sql  
SELECT  
DIGEST\_TEXT,  
SUM\_ROWS\_EXAMINED,  
COUNT\_STAR,  
FIRST\_SEEN,  
LAST\_SEEN  
FROM  
\`performance\_schema\`.events\_statements\_summary\_by\_digest  
ORDER BY  
SUM\_ROWS\_EXAMINED DESC;  
\`\`\`

5、查看使用临时表最多的sql

\`\`\`sql  
SELECT  
DIGEST\_TEXT,  
SUM\_CREATED\_TMP\_TABLES,  
SUM\_CREATED\_TMP\_DISK\_TABLES,  
COUNT\_STAR,  
FIRST\_SEEN,  
LAST\_SEEN  
FROM  
\`performance\_schema\`.events\_statements\_summary\_by\_digest  
ORDER BY  
SUM\_CREATED\_TMP\_TABLES desc,SUM\_CREATED\_TMP\_DISK\_TABLES desc;  
\`\`\`

6、查看返回结果集最多的SQL

\`\`\`sql  
SELECT  
DIGEST\_TEXT,  
SUM\_ROWS\_SENT,  
COUNT\_STAR,  
FIRST\_SEEN,  
LAST\_SEEN  
FROM  
\`performance\_schema\`.events\_statements\_summary\_by\_digest  
ORDER BY  
SUM\_ROWS\_SENT desc;  
\`\`\`

\#\# 二、统计信息(对象维度)

1、查看哪个表物理IO最多？

\`\`\`sql  
SELECT  
file\_name,  
event\_name,  
SUM\_NUMBER\_OF\_BYTES\_READ,  
SUM\_NUMBER\_OF\_BYTES\_WRITE  
FROM  
\`performance\_schema\`.file\_summary\_by\_instance  
ORDER BY  
SUM\_NUMBER\_OF\_BYTES\_READ + SUM\_NUMBER\_OF\_BYTES\_WRITE DESC;  
\`\`\`

2、查看哪个表逻辑IO最多?

\`\`\`sql  
SELECT  
object\_schema,  
object\_name,  
COUNT\_READ,  
COUNT\_WRITE,  
COUNT\_FETCH,  
SUM\_TIMER\_WAIT  
FROM  
\`performance\_schema\`.table\_io\_waits\_summary\_by\_table  
ORDER BY  
sum\_timer\_wait DESC;  
\`\`\`

3、查看哪个索引访问最多？

\`\`\`sql  
SELECT  
OBJECT\_SCHEMA,  
OBJECT\_NAME,  
INDEX\_NAME,  
COUNT\_FETCH,  
COUNT\_INSERT,  
COUNT\_UPDATE,  
COUNT\_DELETE  
FROM  
\`performance\_schema\`.table\_io\_waits\_summary\_by\_index\_usage  
ORDER BY  
SUM\_TIMER\_WAIT DESC;  
\`\`\`

4、查看哪个索引从来没有使用过？

\`\`\`sql  
SELECT  
OBJECT\_SCHEMA,  
OBJECT\_NAME,  
INDEX\_NAME  
FROM  
\`performance\_schema\`.table\_io\_waits\_summary\_by\_index\_usage  
WHERE  
INDEX\_NAME IS NOT NULL  
AND COUNT\_STAR = 0  
AND OBJECT\_SCHEMA <> 'mysql'  
ORDER BY  
OBJECT\_SCHEMA,  
OBJECT\_NAME;  
\`\`\`

\#\# 三、统计信息(等待事件维度)

1、查看哪个等待事件消耗的时间最多？

\`\`\`sql  
SELECT  
EVENT\_NAME,  
COUNT\_STAR,  
SUM\_TIMER\_WAIT,  
AVG\_TIMER\_WAIT  
FROM  
\`performance\_schema\`.events\_waits\_summary\_global\_by\_event\_name  
WHERE  
event\_name != 'idle'  
ORDER BY  
SUM\_TIMER\_WAIT DESC;  
\`\`\`

\#\# 四、用户、连接类

1、查看每个客户端IP过来的连接消耗资源情况。

\`\`\`sql  
select \* from sys.host\_summary;  
\`\`\`

2、查看每个用户消耗资源情况

\`\`\`sql  
select \* from sys.user\_summary;  
\`\`\`

3、查看当前连接情况（有多少连接就应该有多少行）

\`\`\`sql  
select host,current\_connections,statements from sys.host\_summary;  
\`\`\`

4、查看当前正在执行的SQL和执行show full processlist的结果差不多

\`\`\`sql  
select conn\_id,pid,user,db,command,current\_statement,last\_statement,time,lock\_latency from sys.session;  
\`\`\`

5、查看总共分配了多少内存

\`\`\`sql  
select \* from sys.memory\_global\_total;  
select \* from sys.memory\_global\_by\_current\_bytes;  
\`\`\`

6、每个库（database）占用多少buffer pool

\`\`\`sql  
select \* from sys.innodb\_buffer\_stats\_by\_schema order by allocated desc;  
\`\`\`

7、统计每张表具体在InnoDB中具体的情况，比如占多少页？

\`\`\`sql  
select \* from sys.innodb\_buffer\_stats\_by\_table;  
\`\`\`

8、查询每个连接分配了多少内存利用session表和memory\_by\_thread\_by\_current\_bytes分配表进行关联查询。

\`\`\`sql  
SELECT  
b.USER,  
current\_count\_used,  
current\_allocated,  
current\_avg\_alloc,  
current\_max\_alloc,  
total\_allocated,  
current\_statement  
FROM  
sys.memory\_by\_thread\_by\_current\_bytes a,  
sys.SESSION b  
WHERE  
a.thread\_id = b.thd\_id;  
\`\`\`

9、查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考

\`\`\`sql  
select \* from sys.schema\_auto\_increment\_columns;  
\`\`\`

10、MySQL索引使用情况统计

\`\`\`sql  
select \* from sys.schema\_index\_statistics order by rows\_selected desc;  
\`\`\`

11、MySQL中有哪些冗余索引和无用索引若库中展示没有冗余索引，则没有数据；当有联合索引idx\_abc(a,b,c)和idx\_a(a)，那么idx\_a就算冗余索引了。

\`\`\`sql  
select \* from sys.schema\_redundant\_indexes;  
\`\`\`

12、查看库级别的锁信息，这个需要先打开MDL锁的监控：

\`\`\`sql  
\--打开MDL锁监控  
update performance\_schema.setup\_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';  
select \* from sys.schema\_table\_lock\_waits;  
\`\`\`

13、MySQL内部有多个线程在运行，线程类型及数量

\`\`\`sql  
select user,count(\*) from sys.\`processlist\` group by user;  
\`\`\`

14、查看MySQL自增id的使用情况

\`\`\`sql  
SELECT  
table\_schema,  
table\_name,  
ENGINE,  
Auto\_increment  
FROM  
information\_schema.TABLES  
WHERE  
TABLE\_SCHEMA NOT IN ( "INFORMATION\_SCHEMA", "PERFORMANCE\_SCHEMA", "MYSQL", "SYS" );  
\`\`\`

15、找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 （此处 5分钟 可根据自己的需要调整SQL标红处）

可复制查询结果到控制台，直接执行，杀死堵塞进程

\`\`\`sql  
select\` \`concat(\`\`'kill '\`\`, id, \`\`';'\`\`) \`\`from\` \`information\_schema.processlist \`\`where\` \`Command != \`\`'Sleep'\` \`and\` \`Time\` \`> 300 \`\`order\` \`by\` \`Time\` \`desc\`\`;  
\`\`\`

16、按客户端 IP 分组，看哪个客户端的链接数最多

\`\`\`sql  
select\` \`client\_ip,\`\`count\`\`(client\_ip) \`\`as\` \`client\_num \`\`from\` \`(\`\`select\` \`substring\_index(host,\`\`':'\` \`,1) \`\`as\` \`client\_ip \`\`from\` \`information\_schema.processlist ) \`\`as\` \`connect\_info \`\`group\` \`by\` \`client\_ip \`\`order\` \`by\` \`client\_num \`\`desc\`\`;  
\`\`\`

17、查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

\`\`\`sql  
select\` \`\* \`\`from\` \`information\_schema.processlist \`\`where\` \`Command != \`\`'Sleep'\` \`order\` \`by\` \`Time\` \`desc\`\`;  
\`\`\`

18\\MySQL查看正在运行的SQL

完整SQL  
SELECT a.\*, c.thread\_id, c.sql\_text from information\_schema.processlist a  
LEFT JOIN performance\_schema.threads b on a.id = b.PROCESSLIST\_ID  
LEFT JOIN performance\_schema.events\_statements\_current c on c.THREAD\_ID = b.THREAD\_ID;

19\\mysql 查询没有主键的表

SELECT a.TABLE\_SCHEMA, a.TABLE\_NAME FROM ( SELECT TABLE\_SCHEMA, TABLE\_NAME FROM information\_schema.TABLES WHERE TABLE\_SCHEMA NOT IN ( 'mysql', 'information\_schema', 'performance\_schema', 'sys', 'sysdb' )) AS a LEFT JOIN ( SELECT TABLE\_SCHEMA, TABLE\_NAME FROM information\_schema.TABLE\_CONSTRAINTS WHERE CONSTRAINT\_TYPE = 'PRIMARY KEY' AND TABLE\_SCHEMA NOT IN ( 'mysql', 'information\_schema', 'performance\_schema', 'sys', 'sysdb' )) AS b ON a.TABLE\_SCHEMA = b.TABLE\_SCHEMA AND a.TABLE\_NAME = b.TABLE\_NAME WHERE b.TABLE\_NAME IS NULL;

1、查看数据库中不为 InnoDB 引擎的表

SELECT TABLE\_SCHEMA, TABLE\_NAME, ENGINE FROM information\_schema.TABLES WHERE

TABLE\_SCHEMANOT IN ('sys', 'mysql', 'performance\_schema', 'information\_schema',

'test') AND ENGINE != 'InnoDB';

2、查看数据库中表的大小及数据量

SELECT TABLE\_SCHEMA, TABLE\_NAME, TABLE\_ROWS, CONCAT(round((DATA\_LENGTH +

INDEX\_LENGTH) /1024 / 1024, 2), 'MB') as data FROM information\_schema.TABLES

WHERE TABLE\_SCHEMA NOT IN ('sys', 'mysql', 'performance\_schema',

'information\_schema', 'test') ORDER BY DATA\_LENGTH + INDEX\_LENGTH DESC;

3、查找数据库中无显式主键索引的表

SELECT t.TABLE\_SCHEMA, t.TABLE\_NAME FROM information\_schema.TABLES t WHERE

(t.TABLE\_SCHEMA, t.TABLE\_NAME)NOT IN (SELECT DISTINCT TABLE\_SCHEMA, TABLE\_NAME

FROM information\_schema.COLUMNS WHERE COLUMN\_KEY = 'PRI') AND t.TABLE\_SCHEMA NOT

IN ('sys', 'mysql', 'performance\_schema', 'information\_schema', 'test');

4、查找数据库中主键为联合主键的表

SELECT TABLE\_SCHEMA, TABLE\_NAME, GROUP\_CONCAT(COLUMN\_NAME ORDER BY

SEQ\_IN\_INDEX SEPARATOR',') cols, MAX(SEQ\_IN\_INDEX) len FROM

information\_schema.STATISTICSWHERE INDEX\_NAME = 'PRIMARY' AND TABLE\_SCHEMA NOT

IN ('sys', 'mysql', 'performance\_schema', 'information\_schema', 'test') GROUP BY

TABLE\_SCHEMA, TABLE\_NAMEHAVING len > 1;

5、查找数据库中不为自增主键的表

SELECT TABLE\_SCHEMA, TABLE\_NAME FROM information\_schema.TABLES WHERE

TABLE\_SCHEMANOT IN ('sys', 'mysql', 'performance\_schema', 'information\_schema',

'test') AND (TABLE\_SCHEMA,TABLE\_NAME) NOT IN (SELECT TABLE\_SCHEMA, TABLE\_NAME

FROM information\_schema.COLUMNS WHERE TABLE\_SCHEMA NOT IN ('sys', 'mysql',

'performance\_schema', 'information\_schema', 'test') AND IS\_NULLABLE = 'NO' AND

COLUMN\_TYPELIKE '%int%' AND COLUMN\_KEY = 'PRI' AND EXTRA = 'auto\_increment');

6、查看数据库中存在外键约束的表

SELECT c.TABLE\_SCHEMA, c.REFERENCED\_TABLE\_NAME, c.REFERENCED\_COLUMN\_NAME, c

.TABLE\_NAME, c.COLUMN\_NAME, c.CONSTRAINT\_NAME, t.TABLE\_COMMENT, r.UPDATE\_RULE, r

.DELETE\_RULE FROM information\_schema.KEY\_COLUMN\_USAGE c JOIN information\_schema

.TABLES t ON t.TABLE\_NAME = c.TABLE\_NAME JOIN information\_schema.REFERENTIAL

\_CONSTRAINTS r ON r.TABLE\_NAME = c.TABLE\_NAME AND r.CONSTRAINT\_NAME = c

.CONSTRAINT\_NAME AND r.REFERENCED\_TABLE\_NAME = c.REFERENCED\_TABLE\_NAME WHERE c

.REFERENCED\_TABLE\_NAME IS NOT NULL;

7、查找数据库中低区分度索引(区分度小于0.1)

SELECT p.TABLE\_SCHEMA, p.TABLE\_NAME, c.INDEX\_NAME, c.car, p.car total FROM (

SELECT TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME, MAX(CARDINALITY) car FROM

information\_schema.STATISTICSWHERE INDEX\_NAME != 'PRIMARY' AND TABLE\_SCHEMA NOT

IN ('sys', 'mysql', 'performance\_schema', 'information\_schema', 'test') GROUP BY

TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME) cINNER JOIN (SELECT TABLE\_SCHEMA,

TABLE\_NAME,MAX(CARDINALITY) car from information\_schema.STATISTICS WHERE

INDEX\_NAME ='PRIMARY' AND TABLE\_SCHEMA NOT IN ('sys', 'mysql',

'performance\_schema', 'information\_schema', 'test') GROUP BY TABLE\_SCHEMA,

TABLE\_NAME) pON c.TABLE\_NAME = p.TABLE\_NAME AND c.TABLE\_SCHEMA = p.TABLE\_SCHEMA

WHERE p.car > 0 AND c.car / p.car < 0.1;

8、查找数据库中重复索引前缀的索引

SELECT a.TABLE\_SCHEMA, a.TABLE\_NAME, a.INDEX\_NAME, a.cols, b.INDEX\_NAME, b.cols

FROM (SELECT TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME, CONCAT('| ',

GROUP\_CONCAT(COLUMN\_NAMEORDER BY SEQ\_IN\_INDEX SEPARATOR ' | '), ' |') AS cols

FROM information\_schema.STATISTICS WHERE TABLE\_SCHEMA NOT IN ('sys', 'mysql',

'performance\_schema', 'information\_schema', 'test') AND INDEX\_NAME != 'PRIMARY'

GROUP BY TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME) a INNER JOIN (SELECT

TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME, CONCAT('| ', GROUP\_CONCAT(COLUMN\_NAME

ORDER BY SEQ\_IN\_INDEX SEPARATOR ' | '), ' |') AS cols FROM

information\_schema.STATISTICSWHERE TABLE\_SCHEMA NOT IN ('sys', 'mysql',

'performance\_schema', 'information\_schema', 'test') AND INDEX\_NAME != 'PRIMARY'

GROUP BY TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME) b ON a.TABLE\_NAME = b.TABLE\_NAME

AND a.TABLE\_SCHEMA = b.TABLE\_SCHEMA AND a.cols LIKE CONCAT(b.cols, '%') AND

a.INDEX\_NAME != b.INDEX\_NAME;

9、查找数据库中包索引重复包含主键列的索引

SELECT a.\*, b.pk FROM (SELECT TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME, CONCAT('| '

, GROUP\_CONCAT(COLUMN\_NAMEORDER BY SEQ\_IN\_INDEX SEPARATOR ' | '), ' |') cols

FROM information\_schema.STATISTICS WHERE INDEX\_NAME != 'PRIMARY' AND

TABLE\_SCHEMANOT IN ('sys', 'mysql', 'performance\_schema', 'information\_schema',

'test') GROUP BY TABLE\_SCHEMA, TABLE\_NAME, INDEX\_NAME) a INNER JOIN (SELECT

TABLE\_SCHEMA, TABLE\_NAME, CONCAT('| ', GROUP\_CONCAT(COLUMN\_NAME ORDER BY

SEQ\_IN\_INDEX SEPARATOR' | '), ' |') pk FROM information\_schema.STATISTICS WHERE

INDEX\_NAME ='PRIMARY' AND TABLE\_SCHEMA NOT IN ('sys', 'mysql',

'performance\_schema', 'information\_schema', 'test') GROUP BY TABLE\_SCHEMA,

TABLE\_NAME) bON a.TABLE\_NAME = b.TABLE\_NAME AND a.TABLE\_SCHEMA = b.TABLE\_SCHEMA

AND a.cols LIKE CONCAT('%', b.pk, '%');

10、查找数据库中没有被使用的索引

SELECT a.OBJECT\_SCHEMA, a.OBJECT\_NAME, a.INDEX\_NAME, b.TABLE\_ROWS FROM

performance\_schema.TABLE\_IO\_WAITS\_SUMMARY\_BY\_INDEX\_USAGE aINNER JOIN

information\_schema.TABLES bON a.OBJECT\_SCHEMA = b.TABLE\_SCHEMA AND

a.OBJECT\_NAME = b.TABLE\_SCHEMAWHERE a.INDEX\_NAME IS NOT NULL AND a.INDEX\_NAME !=

'PRIMARY' AND a.COUNT\_STAR = 0 AND OBJECT\_SCHEMA NOT IN ('sys', 'mysql',

'performance\_schema', 'information\_schema') ORDER BY OBJECT\_SCHEMA, OBJECT\_NAME;

11、查看数据库中的锁请求信息

SELECT r.TRX\_ISOLATION\_LEVEL, r.TRX\_ID WAITING\_TRX\_ID, r.TRX\_MYSQL\_THREAD\_ID

WAITING\_TRX\_THREAD, r.TRX\_STATE WAITING\_TRX\_STATE, lr.LOCK\_MODE

WAITING\_TRX\_LOCK\_MODE, lr.LOCK\_TYPE WAITING\_TRX\_LOCK\_TYPE, lr.LOCK\_TABLE

WAITING\_TRX\_LOCK\_TABLE, lr.LOCK\_INDEX WAITING\_TRX\_LOCK\_INDEX, r.TRX\_QUERY

WAITING\_TRX\_QUERY, b.TRX\_ID BLOCKING\_TRX\_ID, b.TRX\_MYSQL\_THREAD\_ID

BLOCKING\_TRX\_THREAD, b.TRX\_STATE BLOCKING\_TRX\_STATE, lb.LOCK\_MODE

BLOCKING\_TRX\_LOCK\_MODE, lb.LOCK\_TYPE BLOCKING\_TRX\_LOCK\_TYPE, lb.LOCK\_TABLE

BLOCKING\_TRX\_LOCK\_TABLE, lb.LOCK\_INDEX BLOCKING\_TRX\_LOCK\_INDEX, b.TRX\_QUERY

BLOCKING\_QUERY FROM information\_schema.INNODB\_LOCK\_WAITS w INNER JOIN

INFORMATION\_SCHEMA.INNODB\_TRX b ON b.TRX\_ID = W.BLOCKING\_TRX\_ID INNER JOIN

information\_schema.INNODB\_TRX R ON r.TRX\_ID = W.REQUESTING\_TRX\_ID INNER JOIN

information\_schema.INNODB\_LOCKS lb ON lb.LOCK\_TRX\_ID = W.BLOCKING\_TRX\_ID INNER

JOIN information\_schema.INNODB\_LOCKS lr ON lr.LOCK\_TRX\_ID = W.REQUESTING\_TRX\_ID;

注：本文提供的 SQL 脚本只适应特定的数据库版本，不代表所有数据库版本。

1.数据库大表信息查看

统计某库下各表大小 不要存在过大的表信息。本身分配内存有限，过大的表会不停地刷新新旧数据，IO交付频繁，导致性能衰减。

SELECT TABLE\_SCHEMA,  
TABLE\_NAME TABLE\_NAME, 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,  
engine  
FROM INFORMATION\_SCHEMA.TABLES  
WHERE TABLE\_SCHEMA NOT IN ('INFORMATION\_SCHEMA' ,  
'performance\_schema',  
'sys',  
'mysql')  
ORDER BY (data\_length + index\_length) DESC LIMIT 10;  
  
  
\+--------------+-----------------------+------------+-------------+--------------+------------+--------+  
| TABLE\_SCHEMA | TABLE\_NAME | TABLE\_ROWS | data\_length | index\_length | total\_size | ENGINE |  
\+--------------+-----------------------+------------+-------------+--------------+------------+--------+  
| employees | salaries | 1910497 | 64.59M | 0.00M | 64.59M | InnoDB |  
| employees | employees | 299556 | 14.52M | 10.03M | 24.55M | InnoDB |  
| employees | employees01 | 101881 | 5.52M | 8.55M | 14.06M | InnoDB |  
| employees | t\_temp | 95374 | 5.52M | 5.52M | 11.03M | InnoDB |  
| db3 | t\_temp | 1000 | 0.08M | 0.13M | 0.20M | InnoDB |  
| db3 | transportorder | 3 | 0.02M | 0.06M | 0.08M | InnoDB |  
| db3 | transportorderwaybill | 3 | 0.02M | 0.05M | 0.06M | InnoDB |  
| db1 | pt1 | 10 | 0.06M | 0.00M | 0.06M | InnoDB |  
| db1 | city | 2 | 0.02M | 0.03M | 0.05M | InnoDB |  
| db2 | tabname | 30 | 0.02M | 0.03M | 0.05M | InnoDB |  
\+--------------+-----------------------+------------+-------------+--------------+------------+--------+  
10 rows in set (0.20 sec)  
2.存储引擎

存储引擎分布，innodb引擎最适合因为支持事务，行锁级别。

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;  
非 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 ENGINE NOT IN ('INNODB')  
ORDER BY TABLE\_ROWS DESC ;  
3.主键

无主键、无唯一键表。复制主键最重要，数据操作主键效率高。

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';  
4.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');  
5.字符集验证

表之间Join字符集不对称，导致索引失效。

参看系统字符集：  
mysql> show global variables like 'collation%';  
跟系统字符集不一样的数据库：  
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 ;  
跟系统字符集不一样的表和字段:  
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 ;  
6.存储过程&函数

存储过程 函数查看，确实影响mysql 处理能力，后期也不好维护。

\#\#MySQL5.7  
SELECT db,type,count(\*)  
FROM mysql.proc  
WHERE db not in ('mysql','information\_schema','performance\_schema','sys')  
AND type='PROCEDURE'  
GROUP BY db, type;  
\#\#MySQL8.0  
SELECT Routine\_schema, Routine\_type  
FROM information\_schema.Routines  
WHERE Routine\_schema not in ('mysql','information\_schema','performance\_schema','sys')  
AND ROUTINE\_TYPE='PROCEDURE'  
GROUP BY Routine\_schema, Routine\_type;  
7.统计视图

确实影响mysql 处理能力，后期也不好维护。特别是ddl变更要注意

SELECT TABLE\_SCHEMA , COUNT(TABLE\_NAME)  
FROM information\_schema.VIEWS  
WHERE TABLE\_SCHEMA not in ('mysql','information\_schema','performance\_schema','sys')  
GROUP BY TABLE\_SCHEMA ;  
8.自增主键查看

主要考虑自增键超出范围，需要检查一下

SELECT infotb.TABLE\_SCHEMA ,  
infotb.TABLE\_NAME,  
infotb.AUTO\_INCREMENT,  
infocl.COLUMN\_TYPE ,  
infocl.COLUMN\_NAME  
FROM information\_schema.TABLES as infotb INNER JOIN information\_schema.COLUMNS infocl  
ON infotb.TABLE\_SCHEMA = infocl.TABLE\_SCHEMA  
AND infotb.TABLE\_NAME = infocl.TABLE\_NAME  
AND infocl.EXTRA='auto\_increment';  
自增主键使用情况统计：

SELECT infotb.TABLE\_SCHEMA , infotb.TABLE\_NAME, infotb.AUTO\_INCREMENT,infocl.COLUMN\_TYPE  
FROM information\_schema.TABLES as infotb INNER JOIN information\_schema.COLUMNS infocl  
ON infotb.TABLE\_SCHEMA = infocl.TABLE\_SCHEMA  
AND infotb.TABLE\_NAME = infocl.TABLE\_NAME  
AND infocl.EXTRA='auto\_increment';  
9.分区表

尽量避免分区表，分区表性能问题：体现在分区锁，初期访问加载所有分区

查看实例中的分区表相关信息

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\_SCHEMA 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 |  
\+--------------+------------------+-----------------+------------------+-------------+--------------+------------+  
6 rows in set (1.04 sec)  
查看某分区表具体信息，此处以库名为 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)  
10.计划任务

在不自觉中，自动执行。确认无法维护。

SELECT EVENT\_SCHEMA,EVENT\_NAME  
FROM information\_schema.EVENTS  
WHERE EVENT\_SCHEMA not in ('mysql','information\_schema','performance\_schema','sys') ;  
总结

以上合规检查已经为后期排除了很多隐患。做好上线数据库设计方面合规检查，必不可少的流程。

## 优化、分析Mysql表读写、索引等操作的sql语句效率优化问题 ##

为什么要优化：

随着实际项目的启动，数据库经过一段时间的运行，最初的数据库设置，会与实际数据库运行性能会有一些差异，这时我们 就需要做一个优化调整。

数据库优化这个课题较大，可分为四大类：

》主机性能

》内存使用性能

》网络传输性能

》SQL语句执行性能【软件工程师】

下面列出一些数据库SQL优化方案：

（01）选择最有效率的表名顺序（笔试常考）

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表放在最后，如果有3个以上的表连接查询,那就需要选择那个被其他表所引用的表放在最后。

例如：查询员工的编号，姓名，工资，工资等级，部门名

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname  
from salgrade,dept,emp  
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

1)如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推

2)如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推

（02）WHERE子句中的连接顺序（笔试常考）

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

例如：查询员工的编号，姓名，工资，部门名

select emp.empno,emp.ename,emp.sal,dept.dname  
from emp,dept  
where (emp.deptno = dept.deptno) and (emp.sal > 1500)

（03）SELECT子句中避免使用\*号

数据库在解析的过程中,会将\*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间

select empno,ename from emp;

（04）用TRUNCATE替代DELETE

（05）尽量多使用COMMIT

因为COMMIT会释放回滚点

（06）用WHERE子句替换HAVING子句

WHERE先执行，HAVING后执行

（07）多使用内部函数提高SQL效率

（08）使用表的别名

salgrade s

（09）使用列的别名

ename e

直接上代码：

反映表的读写压力

SELECT file\_name AS file,  
count\_read,  
sum\_number\_of\_bytes\_read AS total\_read,  
count\_write,  
sum\_number\_of\_bytes\_write AS total\_written,  
(sum\_number\_of\_bytes\_read + sum\_number\_of\_bytes\_write) AS total  
FROM performance\_schema.file\_summary\_by\_instance  
ORDER BY sum\_number\_of\_bytes\_read+ sum\_number\_of\_bytes\_write DESC;  
  
反映文件的延迟

SELECT (file\_name) AS file,  
count\_star AS total,  
CONCAT(ROUND(sum\_timer\_wait / 3600000000000000, 2), 'h') AS total\_latency,  
count\_read,  
CONCAT(ROUND(sum\_timer\_read / 1000000000000, 2), 's') AS read\_latency,  
count\_write,  
CONCAT(ROUND(sum\_timer\_write / 3600000000000000, 2), 'h')AS write\_latency  
FROM performance\_schema.file\_summary\_by\_instance  
ORDER BY sum\_timer\_wait DESC;  
  
table 的读写延迟

SELECT object\_schema AS table\_schema,  
object\_name AS table\_name,  
count\_star AS total,  
CONCAT(ROUND(sum\_timer\_wait / 3600000000000000, 2), 'h') as total\_latency,  
CONCAT(ROUND((sum\_timer\_wait / count\_star) / 1000000, 2), 'us') AS avg\_latency,  
CONCAT(ROUND(max\_timer\_wait / 1000000000, 2), 'ms') AS max\_latency  
FROM performance\_schema.objects\_summary\_global\_by\_type  
ORDER BY sum\_timer\_wait DESC;  
  
查看表操作频度

SELECT object\_schema AS table\_schema,  
object\_name AS table\_name,  
count\_star AS rows\_io\_total,  
count\_read AS rows\_read,  
count\_write AS rows\_write,  
count\_fetch AS rows\_fetchs,  
count\_insert AS rows\_inserts,  
count\_update AS rows\_updates,  
count\_delete AS rows\_deletes,  
CONCAT(ROUND(sum\_timer\_fetch / 3600000000000000, 2), 'h') AS fetch\_latency,  
CONCAT(ROUND(sum\_timer\_insert / 3600000000000000, 2), 'h') AS insert\_latency,  
CONCAT(ROUND(sum\_timer\_update / 3600000000000000, 2), 'h') AS update\_latency,  
CONCAT(ROUND(sum\_timer\_delete / 3600000000000000, 2), 'h') AS delete\_latency  
FROM performance\_schema.table\_io\_waits\_summary\_by\_table  
ORDER BY sum\_timer\_wait DESC ;  
  
索引状况

SELECT OBJECT\_SCHEMA AS table\_schema,  
OBJECT\_NAME AS table\_name,  
INDEX\_NAME as index\_name,  
COUNT\_FETCH AS rows\_fetched,  
CONCAT(ROUND(SUM\_TIMER\_FETCH / 3600000000000000, 2), 'h') AS select\_latency,  
COUNT\_INSERT AS rows\_inserted,  
CONCAT(ROUND(SUM\_TIMER\_INSERT / 3600000000000000, 2), 'h') AS insert\_latency,  
COUNT\_UPDATE AS rows\_updated,  
CONCAT(ROUND(SUM\_TIMER\_UPDATE / 3600000000000000, 2), 'h') AS update\_latency,  
COUNT\_DELETE AS rows\_deleted,  
CONCAT(ROUND(SUM\_TIMER\_DELETE / 3600000000000000, 2), 'h')AS delete\_latency  
FROM performance\_schema.table\_io\_waits\_summary\_by\_index\_usage  
WHERE index\_name IS NOT NULL  
ORDER BY sum\_timer\_wait DESC;  
  
全表扫描情况

SELECT object\_schema,  
object\_name,  
count\_read AS rows\_full\_scanned  
FROM performance\_schema.table\_io\_waits\_summary\_by\_index\_usage  
WHERE index\_name IS NULL  
AND count\_read > 0  
ORDER BY count\_read DESC;  
没有使用的index

SELECT object\_schema,  
object\_name,  
index\_name  
FROM performance\_schema.table\_io\_waits\_summary\_by\_index\_usage  
WHERE index\_name IS NOT NULL  
AND count\_star = 0  
AND object\_schema not in ('mysql','v\_monitor')  
AND index\_name <> 'PRIMARY'  
ORDER BY object\_schema, object\_name;  
  
糟糕的sql问题摘要

SELECT (DIGEST\_TEXT) AS query,  
SCHEMA\_NAME AS db,  
IF(SUM\_NO\_GOOD\_INDEX\_USED > 0 OR SUM\_NO\_INDEX\_USED > 0, '\*', '') AS full\_scan,  
COUNT\_STAR AS exec\_count,  
SUM\_ERRORS AS err\_count,  
SUM\_WARNINGS AS warn\_count,  
(SUM\_TIMER\_WAIT) AS total\_latency,  
(MAX\_TIMER\_WAIT) AS max\_latency,  
(AVG\_TIMER\_WAIT) AS avg\_latency,  
(SUM\_LOCK\_TIME) AS lock\_latency,  
format(SUM\_ROWS\_SENT,0) AS rows\_sent,  
ROUND(IFNULL(SUM\_ROWS\_SENT / NULLIF(COUNT\_STAR, 0), 0)) AS rows\_sent\_avg,  
SUM\_ROWS\_EXAMINED AS rows\_examined,  
ROUND(IFNULL(SUM\_ROWS\_EXAMINED / NULLIF(COUNT\_STAR, 0), 0)) AS rows\_examined\_avg,  
SUM\_CREATED\_TMP\_TABLES AS tmp\_tables,  
SUM\_CREATED\_TMP\_DISK\_TABLES AS tmp\_disk\_tables,  
SUM\_SORT\_ROWS AS rows\_sorted,  
SUM\_SORT\_MERGE\_PASSES AS sort\_merge\_passes,  
DIGEST AS digest,  
FIRST\_SEEN AS first\_seen,  
LAST\_SEEN as last\_seen  
FROM performance\_schema.events\_statements\_summary\_by\_digest d  
where d  
ORDER BY SUM\_TIMER\_WAIT DESC  
limit 20;