MySQL上线，检查数据库设计的“十条合规”

vlambda
2021-04-22

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、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年；曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务；涉及行业：金融（银行、理财）、物流、游戏、医疗、重工业等。

墨天轮原文链接：https://www.modb.pro/db/51721（复制到浏览器或者点击“阅读原文”立即查看）

END