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

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

[[394841]]

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

结合个人经验,下面具体讲解十个“合规检查”:

1. 数据库大表信息查看

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

  1. SELECT TABLE_SCHEMA, 
  2.        TABLE_NAME TABLE_NAME, TABLE_ROWS, 
  3.                   CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length, 
  4.                   CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length, 
  5.                   CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size, 
  6.                   engine 
  7. FROM INFORMATION_SCHEMA.TABLES 
  8. WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' , 
  9.                            'performance_schema'
  10.                            'sys'
  11.                            'mysql'
  12. ORDER BY (data_length + index_length) DESC LIMIT 10; 
  13. +--------------+-----------------------+------------+-------------+--------------+------------+--------+ 
  14. | TABLE_SCHEMA | TABLE_NAME            | TABLE_ROWS | data_length | index_length | total_size | ENGINE | 
  15. +--------------+-----------------------+------------+-------------+--------------+------------+--------+ 
  16. | employees    | salaries              |    1910497 | 64.59M      | 0.00M        | 64.59M     | InnoDB | 
  17. | employees    | employees             |     299556 | 14.52M      | 10.03M       | 24.55M     | InnoDB | 
  18. | employees    | employees01           |     101881 | 5.52M       | 8.55M        | 14.06M     | InnoDB | 
  19. | employees    | t_temp                |      95374 | 5.52M       | 5.52M        | 11.03M     | InnoDB | 
  20. | db3          | t_temp                |       1000 | 0.08M       | 0.13M        | 0.20M      | InnoDB | 
  21. | db3          | transportorder        |          3 | 0.02M       | 0.06M        | 0.08M      | InnoDB | 
  22. | db3          | transportorderwaybill |          3 | 0.02M       | 0.05M        | 0.06M      | InnoDB | 
  23. | db1          | pt1                   |         10 | 0.06M       | 0.00M        | 0.06M      | InnoDB | 
  24. | db1          | city                  |          2 | 0.02M       | 0.03M        | 0.05M      | InnoDB | 
  25. | db2          | tabname               |         30 | 0.02M       | 0.03M        | 0.05M      | InnoDB | 
  26. +--------------+-----------------------+------------+-------------+--------------+------------+--------+ 
  27. 10 rows in set (0.20 sec) 

2. 存储引擎

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

  1. SELECT TABLE_SCHEMA, 
  2.        ENGINE, 
  3.        COUNT(*) 
  4. FROM INFORMATION_SCHEMA.TABLES 
  5. WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA'
  6.                            'PERFORMANCE_SCHEMA'
  7.                            'SYS'
  8.                            'MYSQL'
  9.   AND TABLE_TYPE='BASE TABLE' 
  10. GROUP BY TABLE_SCHEMA, 
  11.          ENGINE; 
  12. 非 INNODB 存储引擎表 
  13. SELECT TABLE_SCHEMA, 
  14.        TABLE_NAME, 
  15.        TABLE_COLLATION, 
  16.        ENGINE, 
  17.        TABLE_ROWS 
  18. FROM INFORMATION_SCHEMA.TABLES 
  19. WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA'
  20.                            'SYS'
  21.                            'MYSQL'
  22.                            'PERFORMANCE_SCHEMA'
  23.   AND TABLE_TYPE='BASE TABLE' 
  24.   AND ENGINE NOT IN ('INNODB'
  25. ORDER BY TABLE_ROWS DESC ; 

3. 主键

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

  1. SELECT T1.TABLE_SCHEMA, 
  2.        T1.TABLE_NAME 
  3. FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME 
  4. WHERE T1.TABLE_SCHEMA NOT IN ('SYS'
  5.                            'MYSQL'
  6.                            'INFORMATION_SCHEMA'
  7.                            'PERFORMANCE_SCHEMA'
  8. AND   T2.TABLE_TYPE='BASE TABLE' 
  9. GROUP BY T1.TABLE_SCHEMA, 
  10.          T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI'

4. not utf8 table

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

  1. SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION 
  2.  from information_schema.TABLES 
  3. WHERE TABLE_COLLATION NOT LIKE 'utf8%' 
  4.  AND table_schema NOT IN ('information_schema' ,'mysql','performance_schema''sys'); 

5. 字符集验证

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

  • 参看系统字符集:
  1. mysql> show global variables like 'collation%'
  • 跟系统字符集不一样的数据库:
  1. SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME 
  2.   from information_schema.SCHEMATA  b 
  3. WHERE  b.SCHEMA_NAME not in ('information_schema' ,'mysql','performance_schema''sys'
  4.   AND b.DEFAULT_COLLATION_NAME<>@@collation_server  ; 
  • 跟系统字符集不一样的表和字段:
  1. select distinct tschema,tname,tcoll 
  2. from  
  3.  select  a.TABLE_SCHEMA as tschema , a.TABLE_NAME as tname,a.TABLE_COLLATION  as tcoll 
  4.  from information_schema.TABLES a  
  5.  WHERE  a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema''sys')  
  6.  and a.TABLE_COLLATION<>@@collation_server  
  7.  union  
  8.  select a.TABLE_SCHEMA as tschema, TABLE_NAME as tname, a.COLLATION_NAME  as tcoll 
  9.  from information_schema.COLUMNS a 
  10.  WHERE  a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema''sys'
  11.  and a.COLLATION_NAME<>@@collation_server ) as aa ; 

6. 存储过程&函数

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

  1. ##MySQL5.7 
  2. SELECT db,type,count(*) 
  3. FROM mysql.proc 
  4. WHERE db not in ('mysql','information_schema','performance_schema','sys'
  5. AND type='PROCEDURE' 
  6. GROUP BY db, type; 
  7. ##MySQL8.0 
  8. SELECT  Routine_schema, Routine_type 
  9. FROM information_schema.Routines 
  10. WHERE  Routine_schema not in ('mysql','information_schema','performance_schema','sys'
  11. AND ROUTINE_TYPE='PROCEDURE' 
  12. GROUP BY Routine_schema, Routine_type; 

7. 统计视图

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

  1. SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME) 
  2. FROM information_schema.VIEWS 
  3. WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys'
  4. GROUP BY TABLE_SCHEMA ; 

8. 自增主键查看

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

  1. SELECT  infotb.TABLE_SCHEMA  , 
  2. infotb.TABLE_NAME, 
  3. infotb.AUTO_INCREMENT, 
  4. infocl.COLUMN_TYPE  , 
  5. infocl.COLUMN_NAME 
  6. FROM information_schema.TABLES  as infotb INNER JOIN information_schema.COLUMNS infocl 
  7. ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA 
  8. AND infotb.TABLE_NAME = infocl.TABLE_NAME  
  9. AND infocl.EXTRA='auto_increment'

自增主键使用情况统计:

  1. SELECT  infotb.TABLE_SCHEMA  , infotb.TABLE_NAME, infotb.AUTO_INCREMENT,infocl.COLUMN_TYPE  
  2. FROM  information_schema.TABLES  as infotb INNER JOIN information_schema.COLUMNS infocl  
  3. ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA    
  4. AND infotb.TABLE_NAME = infocl.TABLE_NAME  
  5. AND infocl.EXTRA='auto_increment'

9. 分区表

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

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

  1. SELECT TABLE_SCHEMA, 
  2.        TABLE_NAME, 
  3.        count(PARTITION_NAME) AS PARTITION_COUNT, 
  4.        sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS, 
  5.        CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH, 
  6.        CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH, 
  7.        CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZE 
  8. FROM INFORMATION_SCHEMA.PARTITIONS 
  9. WHERE TABLE_SCHEMA NOT IN ('sys'
  10.                          'mysql'
  11.                          'INFORMATION_SCHEMA'
  12.                          'performance_schema'
  13.   AND PARTITION_NAME IS NOT NULL 
  14. GROUP BY TABLE_SCHEMA, 
  15.          TABLE_NAME 
  16. ORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ; 
  17. +--------------+------------------+-----------------+------------------+-------------+--------------+------------+ 
  18. | TABLE_SCHEMA | TABLE_NAME       | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE | 
  19. +--------------+------------------+-----------------+------------------+-------------+--------------+------------+ 
  20. | db           | t1               |             365 |                0 | 5.70M       | 17.11M       | 22.81M     | 
  21. | db           | t2               |             391 |                0 | 6.11M       | 0.00M        | 6.11M      | 
  22. | db           | t3               |               4 |            32556 | 2.28M       | 0.69M        | 2.97M      | 
  23. | db           | t4               |              26 |                0 | 0.41M       | 2.44M        | 2.84M      | 
  24. | db           | t5               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      | 
  25. | db           | t6               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      | 
  26. +--------------+------------------+-----------------+------------------+-------------+--------------+------------+ 
  27. rows in set (1.04 sec) 
  • 查看某分区表具体信息,此处以库名为 db、表名为 e 的分区表为例:
  1. SELECT TABLE_SCHEMA, 
  2.        TABLE_NAME, 
  3.        PARTITION_NAME, 
  4.        PARTITION_EXPRESSION, 
  5.        PARTITION_METHOD, 
  6.        PARTITION_DESCRIPTION, 
  7.        TABLE_ROWS, 
  8.        CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH, 
  9.        CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH, 
  10.        CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZE 
  11. FROM INFORMATION_SCHEMA.PARTITIONS 
  12. WHERE TABLE_SCHEMA NOT IN ('sys'
  13.                          'mysql'
  14.                          'INFORMATION_SCHEMA'
  15.                          'performance_schema'
  16.   AND PARTITION_NAME IS NOT NULL 
  17.   AND TABLE_SCHEMA='db' 
  18.   AND TABLE_NAME='e'
  19. +--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+ 
  20. | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE | 
  21. +--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+ 
  22. | db           | e          | p0             | id                   | RANGE            | 50                    |       4096 | 0.20M       | 0.09M        | 0.30M      | 
  23. | db           | e          | p1             | id                   | RANGE            | 100                   |       6144 | 0.28M       | 0.13M        | 0.41M      | 
  24. | db           | e          | p2             | id                   | RANGE            | 150                   |       6144 | 0.28M       | 0.13M        | 0.41M      | 
  25. | db           | e          | p3             | id                   | RANGE            | MAXVALUE              |      16172 | 1.52M       | 0.34M        | 1.86M      | 
  26. +--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+ 
  27. rows in set (0.00 sec) 

10. 计划任务

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

  1. SELECT EVENT_SCHEMA,EVENT_NAME 
  2. FROM information_schema.EVENTS 
  3. WHERE EVENT_SCHEMA not in ('mysql'
  4.                           'information_schema'
  5.                           'performance_schema'
  6.                           'sys'); 

总结

以上合规检查已经为后期排除了很多隐患。做好上线数据库设计方面的合规检查,是必不可少的流程。

关于作者

崔虎龙,云和恩墨MySQL技术顾问,长期服务于金融、游戏、物流等行业的数据中心,设计数据存储架构,并熟悉数据中心运营管理的流程及规范,自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。

责任编辑:武晓燕 来源: 数据和云
相关推荐

2009-05-19 10:14:44

Innodb字段MySQL

2011-10-28 09:53:50

数据库安全数据安全

2022-09-09 16:27:09

微服务架构数据存储

2011-07-27 09:17:20

.NET设计架构

2012-05-15 01:38:18

编程编程技巧编程观点

2013-01-08 09:25:36

移动应用产品设计

2022-02-14 00:16:17

数据安全云安全

2012-03-06 16:01:04

项目管理

2012-09-28 09:12:39

移动Web

2021-01-22 16:02:13

Linux命令安全

2017-06-16 21:56:43

2012-08-02 09:14:13

编程戒律

2021-10-09 09:00:00

SaaS数据安全隐私

2009-01-15 09:57:00

2021-03-18 09:00:00

微服务架构工具

2011-04-14 11:43:47

2011-08-02 21:16:56

查询SQL性能优化

2024-02-19 14:50:42

编码原则软件开发

2022-10-21 16:11:52

数据治理安全IT

2011-08-29 14:00:26

MySQL主从延时
点赞
收藏

51CTO技术栈公众号