点击上方"蓝字"
关注我们,享更多干货!
MySQL作为关系型数据库的典型代表,在国内环境里经历风雨磨砺,不断地精进,已经在开发和运维方面,成型了一套的规范。这些规范让了解和使用MySQL更加得心应手,并对后期的一些问题起到了很好的预防作用。
结合个人经验,下面具体讲解十个“合规检查”:
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,
ENG