本篇内容主要讲解“有哪些合规的MySQL检查数据库设计”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“有哪些合规的MySQL检查数据库设计”吧!

MySQL作为关系型数据库的典型代表，在国内环境里经历风雨磨砺，不断地精进，已经在开发和运维方面，成型了一套的规范。这些规范让了解和使用MySQL更加得心应手，并对后期的一些问题起到了很好的预防作用。

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

SELECTTABLE_SCHEMA,TABLE_NAMETABLE_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,engineFROMINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMANOTIN('INFORMATION_SCHEMA','performance_schema','sys','mysql')ORDERBY(data_length+index_length)DESCLIMIT10;+--------------+-----------------------+------------+-------------+--------------+------------+--------+|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|+--------------+-----------------------+------------+-------------+--------------+------------+--------+10rowsinset(0.20sec)2. 存储引擎

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

SELECTTABLE_SCHEMA,ENGINE,COUNT(*)FROMINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMANOTIN('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA','SYS','MYSQL')ANDTABLE_TYPE='BASETABLE'GROUPBYTABLE_SCHEMA,ENGINE;非INNODB存储引擎表SELECTTABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION,ENGINE,TABLE_ROWSFROMINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMANOTIN('INFORMATION_SCHEMA','SYS','MYSQL','PERFORMANCE_SCHEMA')ANDTABLE_TYPE='BASETABLE'ANDENGINENOTIN('INNODB')ORDERBYTABLE_ROWSDESC;3. 主键

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

SELECTT1.TABLE_SCHEMA,T1.TABLE_NAMEFROMINFORMATION_SCHEMA.COLUMNST1JOININFORMATION_SCHEMA.TABLEST2ONT1.TABLE_SCHEMA=T2.TABLE_SCHEMAANDT1.TABLE_NAME=T2.TABLE_NAMEWHERET1.TABLE_SCHEMANOTIN('SYS','MYSQL','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA')ANDT2.TABLE_TYPE='BASETABLE'GROUPBYT1.TABLE_SCHEMA,T1.TABLE_NAMEHAVINGgroup_concat(COLUMN_KEY)NOTREGEXP'PRI|UNI';4. not utf8 table

生僻字成乱码，表情符失效问题。

SELECTTABLE_SCHEMA,TABLE_NAME,TABLE_COLLATIONfrominformation_schema.TABLESWHERETABLE_COLLATIONNOTLIKE'utf8%'ANDtable_schemaNOTIN('information_schema','mysql','performance_schema','sys');5. 字符集验证

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

参看系统字符集：

mysql>showglobalvariableslike'collation%';

跟系统字符集不一样的数据库：

SELECTb.SCHEMA_NAME,b.DEFAULT_CHARACTER_SET_NAME,b.DEFAULT_COLLATION_NAMEfrominformation_schema.SCHEMATAbWHEREb.SCHEMA_NAMEnotin('information_schema','mysql','performance_schema','sys')ANDb.DEFAULT_COLLATION_NAME<>@@collation_server;

跟系统字符集不一样的表和字段:

selectdistincttschema,tname,tcollfrom(selecta.TABLE_SCHEMAastschema,a.TABLE_NAMEastname,a.TABLE_COLLATIONastcollfrominformation_schema.TABLESaWHEREa.TABLE_SCHEMAnotin('information_schema','mysql','performance_schema','sys')anda.TABLE_COLLATION<>@@collation_serverunionselecta.TABLE_SCHEMAastschema,TABLE_NAMEastname,a.COLLATION_NAMEastcollfrominformation_schema.COLUMNSaWHEREa.TABLE_SCHEMAnotin('information_schema','mysql','performance_schema','sys')anda.COLLATION_NAME<>@@collation_server)asaa;6. 存储过程&函数

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

##MySQL5.7SELECTdb,type,count(*)FROMmysql.procWHEREdbnotin('mysql','information_schema','performance_schema','sys')ANDtype='PROCEDURE'GROUPBYdb,type;##MySQL8.0SELECTRoutine_schema,Routine_typeFROMinformation_schema.RoutinesWHERERoutine_schemanotin('mysql','information_schema','performance_schema','sys')ANDROUTINE_TYPE='PROCEDURE'GROUPBYRoutine_schema,Routine_type;7. 统计视图

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

SELECTTABLE_SCHEMA,COUNT(TABLE_NAME)FROMinformation_schema.VIEWSWHERETABLE_SCHEMAnotin('mysql','information_schema','performance_schema','sys')GROUPBYTABLE_SCHEMA;8. 自增主键查看

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

SELECTinfotb.TABLE_SCHEMA,infotb.TABLE_NAME,infotb.AUTO_INCREMENT,infocl.COLUMN_TYPE,infocl.COLUMN_NAMEFROMinformation_schema.TABLESasinfotbINNERJOINinformation_schema.COLUMNSinfoclONinfotb.TABLE_SCHEMA=infocl.TABLE_SCHEMAANDinfotb.TABLE_NAME=infocl.TABLE_NAMEANDinfocl.EXTRA='auto_increment';

自增主键使用情况统计：

SELECTinfotb.TABLE_SCHEMA,infotb.TABLE_NAME,infotb.AUTO_INCREMENT,infocl.COLUMN_TYPEFROMinformation_schema.TABLESasinfotbINNERJOINinformation_schema.COLUMNSinfoclONinfotb.TABLE_SCHEMA=infocl.TABLE_SCHEMAANDinfotb.TABLE_NAME=infocl.TABLE_NAMEANDinfocl.EXTRA='auto_increment';9. 分区表

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

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

SELECTTABLE_SCHEMA,TABLE_NAME,count(PARTITION_NAME)ASPARTITION_COUNT,sum(TABLE_ROWS)ASTABLE_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_SIZEFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_SCHEMANOTIN('sys','mysql','INFORMATION_SCHEMA','performance_schema')ANDPARTITION_NAMEISNOTNULLGROUPBYTABLE_SCHEMA,TABLE_NAMEORDERBYsum(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|+--------------+------------------+-----------------+------------------+-------------+--------------+------------+6rowsinset(1.04sec)

查看某分区表具体信息，此处以库名为 db、表名为 e 的分区表为例：

SELECTTABLE_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_SIZEFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_SCHEMANOTIN('sys','mysql','INFORMATION_SCHEMA','performance_schema')ANDPARTITION_NAMEISNOTNULLANDTABLE_SCHEMA='db'ANDTABLE_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|+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+4rowsinset(0.00sec)10. 计划任务

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

SELECTEVENT_SCHEMA,EVENT_NAMEFROMinformation_schema.EVENTSWHEREEVENT_SCHEMAnotin('mysql','information_schema','performance_schema','sys');

到此，相信大家对“有哪些合规的MySQL检查数据库设计”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！