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,
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技术顾问,长期服务于金融、游戏、物流等行业的数据中心,设计数据存储架构,并熟悉数据中心运营管理的流程及规范,自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。
墨天轮原文链接:https://www.modb.pro/db/51721(复制到浏览器或者点击“阅读原文”立即查看)
联系客服