本篇內(nèi)容主要講解“有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫(kù)設(shè)計(jì)”,感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷,實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫(kù)設(shè)計(jì)”吧!
公司主營(yíng)業(yè)務(wù):成都網(wǎng)站建設(shè)、做網(wǎng)站、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳,提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化,感謝他們對(duì)我們的高要求,感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn),讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來(lái)驚喜。創(chuàng)新互聯(lián)推出宜都免費(fèi)做網(wǎng)站回饋大家。
MySQL作為關(guān)系型數(shù)據(jù)庫(kù)的典型代表,在國(guó)內(nèi)環(huán)境里經(jīng)歷風(fēng)雨磨礪,不斷地精進(jìn),已經(jīng)在開發(fā)和運(yùn)維方面,成型了一套的規(guī)范。這些規(guī)范讓了解和使用MySQL更加得心應(yīng)手,并對(duì)后期的一些問題起到了很好的預(yù)防作用。
統(tǒng)計(jì)某庫(kù)下各表大小,不要存在過大的表信息。本身分配內(nèi)存有限,過大的表會(huì)不停地刷新新舊數(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)
存儲(chǔ)引擎分布,innodb引擎最適合因?yàn)橹С质聞?wù),行鎖級(jí)別。
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 存儲(chǔ)引擎表 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 ;
無(wú)主鍵、無(wú)唯一鍵表。復(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';
生僻字成亂碼,表情符失效問題。
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');
表之間Join字符集不對(duì)稱,導(dǎo)致索引失效。
參看系統(tǒng)字符集:
mysql> show global variables like 'collation%';
跟系統(tǒng)字符集不一樣的數(shù)據(jù)庫(kù):
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 ;
存儲(chǔ)過程和函數(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;
統(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 ;
主要考慮自增鍵超出范圍,需要檢查一下。
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';
盡量避免分區(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ū)表具體信息,此處以庫(kù)名為 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)
在不自覺中,自動(dòng)執(zhí)行。確認(rèn)無(wú)法維護(hù)。
SELECT EVENT_SCHEMA,EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA not in ('mysql', 'information_schema', 'performance_schema', 'sys');
到此,相信大家對(duì)“有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫(kù)設(shè)計(jì)”有了更深的了解,不妨來(lái)實(shí)際操作一番吧!這里是創(chuàng)新互聯(lián)網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
分享文章:有哪些合規(guī)的MySQL檢查數(shù)據(jù)庫(kù)設(shè)計(jì)
當(dāng)前地址:http://muchs.cn/article32/ihsspc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供虛擬主機(jī)、定制開發(fā)、營(yíng)銷型網(wǎng)站建設(shè)、ChatGPT、靜態(tài)網(wǎng)站、App設(shè)計(jì)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)