有哪些合规的MySQL检查数据库设计
本篇内容主要讲解“有哪些合规的MySQL检查数据库设计”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“有哪些合规的MySQL检查数据库设计”吧!
公司主营业务：成都网站建设、做网站、移动网站开发等业务。帮助企业客户真正实现互联网宣传，提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化，感谢他们对我们的高要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出宜都免费做网站回馈大家。
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检查数据库设计
网站链接：http://scyanting.com/article/ihsspc.html
