MySQL上線，檢查數據庫設計的“十條合規”

作者：崔虎龍 2021-04-21 17:25:16

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等職務;涉及行業：金融(銀行、理財)、物流、游戲、醫療、重工業等。

責任編輯：武曉燕來源：數據和云

MySQL 關系型數據庫