MySQL作为关系型数据库的典型代表，在国内环境中历经坎坷，不断完善。在开发和运维方面已经形成了一套规范。.这些规范让大家更容易理解和使用MySQL，对以后出现一些问题起到很好的预防作用。结合个人经验，下面对十项“合规性检查”进行详细说明：1、对于大型数据库表信息，检查并统计某数据库中每个表的大小，不要有太大的表信息。分配的内存本身是有限的，如果表太大，会不断刷新新旧数据，IO传递频繁，导致性能下降。SELECTABLE_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|+-------------+----------------------+------------+------------+--------------+------------+--------+|员工|薪水|1910497|64.59M|0.00M|64.59M|InnoDB||员工|员工|299556|14.52M|10.03M|24.55M|InnoDB||员工|员工01|101881|5.52M|8.55M|14.06M|InnoDB||员工|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'GROUPBYTA,BENGINE;非INNODB存储引表SELECTTABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION,ENGINE,TABLE_ROWSFOROMINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMANOTIN('INFORMATION_SCHEMA','SYS','MYSQL','PERFORMANCE_SCHEMA')ANDTABLE_NOTY'ANDINPE='BASETABLEIN'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.不是utf8表生僻字变成乱码，表情失败。SELECTABLE_SCHEMA,TABLE_NAME,TABLE_COLLATIONfrominformation_schema.TABLESWHERETABLE_COLLATIONNOTLIKE'utf8%'ANDtable_schemaNOTIN('information_schema','mysql','performance_schema','sys');5、字符集校验表之间的join字符集不对称，导致索引失效。查看系统字符集：mysql>showglobalvariableslike'collat??ion%';与系统字符集不同的数据库：SELECTb.SCHEMA_NAME,b.DEFAULT_CHARACTER_SET_NAME,b.DEFAULT_COLLATION_NAMEfrominformation_schema.SCHEMATAbWHEREb.SCHEMA_NAMEnotin('information_schema','mysql','perform'sys')ANDb.DEFAULT_COLLATION_NAME<>@@collat??ion_server;与系统字符集不同的表和字段：selectdistincttschema,tname,tcollfrom(selecta.TABLE_SCHEMAastschema,a.TABLE_NAMEastname,a.TABLE_COLLATIONastcollnotfrominformation_schema.TABLESaWHEREa.TABLE_SCHEma(''mysql','performance_schema','sys')anda.TABLE_COLLATION<>@@collat??ion_serverunionselecta.TABLE_SCHEMAastschema,TABLE_NAMEastname,a.COLLATION_NAMEastcollfrominformation_schema.COLUMNSaWHEREa.TABLE_SCHEMAnotin('information_schema','mysql_formances','.COLLATION_NAME<>@@collat??ion_server)asaa;6.Storedprocedure&function存储过程和函数视图确实影响处理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的变化。SELECTABLE_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.分区表尽量避免分区表，分区表性能问题:Reflectedinthepartitionlock,theinitialaccessloadsallpartitions.查看实例中的分区表相关信息：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_NAMEISNOTNULLANDTABLEdb_SCHEMA='--------------+------------+---------------+-------------------+----------------+----------------------+------------+------------+--------------+------------+|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技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，熟悉数据中心运行管理、自动化运维和管理的流程和规范。维护等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障排除、备份恢复、升级迁移、性能优化。自学通过MySQLOCP5.6和MySQLOCP5.7认证。2年以上开发经验，10年数据库运维工作经验，其中8年全职MySQL工作；曾担任项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职位；相关行业：金融（银行业、财务管理）、物流、游戏、医疗、重工业等。
