有哪些合规的MySQL检查数据库设计
本篇内容主要讲解“有哪些合规的MySQL检查数据库设计”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“有哪些合规的MySQL检查数据库设计”吧!
MySQL作为关系型数据库的典型代表,在国内环境里经历风雨磨砺,不断地精进,已经在开发和运维方面,成型了一套的规范。这些规范让了解和使用MySQL更加得心应手,并对后期的一些问题起到了很好的预防作用。
1. 数据库大表信息查看统计某库下各表大小,不要存在过大的表信息。本身分配内存有限,过大的表会不停地刷新新旧数据,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检查数据库设计”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。