有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫設(shè)計(jì)

本篇內(nèi)容主要講解“有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫設(shè)計(jì)”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫設(shè)計(jì)”吧!

MySQL作為關(guān)系型數(shù)據(jù)庫的典型代表，在國內(nèi)環(huán)境里經(jīng)歷風(fēng)雨磨礪，不斷地精進(jìn)，已經(jīng)在開發(fā)和運(yùn)維方面，成型了一套的規(guī)范。這些規(guī)范讓了解和使用MySQL更加得心應(yīng)手，并對后期的一些問題起到了很好的預(yù)防作用。

1. 數(shù)據(jù)庫大表信息查看

統(tǒng)計(jì)某庫下各表大小，不要存在過大的表信息。本身分配內(nèi)存有限，過大的表會不停地刷新新舊數(shù)據(jù)，IO交付頻繁，導(dǎo)致性能衰減。

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引擎最適合因?yàn)橹С质聞?wù)，行鎖級別。

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. 主鍵

無主鍵、無唯一鍵表。復(fù)制主鍵最重要，數(shù)據(jù)操作主鍵效率高。

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. 字符集驗(yàn)證

表之間Join字符集不對稱，導(dǎo)致索引失效。

• 參看系統(tǒng)字符集：

mysql> show global variables like 'collation%';

• 跟系統(tǒng)字符集不一樣的數(shù)據(jù)庫：

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  ;

• 跟系統(tǒng)字符集不一樣的表和字段:

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. 存儲過程&函數(shù)

存儲過程和函數(shù)查看，確實(shí)影響MySQL處理能力，后期也不好維護(hù)。

##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. 統(tǒng)計(jì)視圖

統(tǒng)計(jì)視圖確實(shí)影響MySQL處理能力，后期也不好維護(hù)。特別是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';

自增主鍵使用情況統(tǒng)計(jì)：

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. 分區(qū)表

盡量避免分區(qū)表，分區(qū)表性能問題：體現(xiàn)在分區(qū)鎖，初期訪問加載所有分區(qū)。

查看實(shí)例中的分區(qū)表相關(guān)信息：

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)

• 查看某分區(qū)表具體信息，此處以庫名為 db、表名為 e 的分區(qū)表為例：

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. 計(jì)劃任務(wù)

在不自覺中，自動執(zhí)行。確認(rèn)無法維護(hù)。

SELECT EVENT_SCHEMA,EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA not in ('mysql',                           'information_schema',                           'performance_schema',                           'sys');

到此，相信大家對“有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫設(shè)計(jì)”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！