MySQL數(shù)據(jù)庫優(yōu)化方案是怎樣的呢,針對這個問題,這篇文章詳細介紹了相對應(yīng)的分析和解答,希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
突泉網(wǎng)站制作公司哪家好,找創(chuàng)新互聯(lián)建站!從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、自適應(yīng)網(wǎng)站建設(shè)等網(wǎng)站項目制作,到程序開發(fā),運營維護。創(chuàng)新互聯(lián)建站成立與2013年到現(xiàn)在10年的時間,我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗,來保證我們的工作的順利進行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)建站。
一、方案概述
優(yōu)化現(xiàn)有mysql數(shù)據(jù)庫。
二、方案優(yōu)缺點
優(yōu)點:不影響現(xiàn)有業(yè)務(wù),源程序不需要修改代碼,成本最低。
缺點:有上限,數(shù)據(jù)量過億就不行了。
三、方案實施細則
1.數(shù)據(jù)庫設(shè)計和表創(chuàng)建時就要考慮性能
設(shè)計表時要注意:
(1)表字段避免null值出現(xiàn),null值很難查詢優(yōu)化且占用額外的索引空間,推薦默認數(shù)字0代替null。
(2)盡量使用INT而非BIGINT,如果非負則加上UNSIGNED(這樣數(shù)值容量會擴大一倍),當(dāng)然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
(3)使用枚舉或整數(shù)代替字符串類型
(4)盡量使用TIMESTAMP而非DATETIME
(5)單表不要有太多字段,建議在20以內(nèi)
(6)用整型來存IP
2.創(chuàng)建索引
(1)索引并不是越多越好,要根據(jù)查詢有針對性的創(chuàng)建,考慮在WHERE和ORDER BY命令上涉及的列建立索引,可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描
(2)應(yīng)盡量避免在WHERE子句中對字段進行NULL值判斷,否則將導(dǎo)致引擎放棄使用索引而進行全表掃描
(3)值分布很稀少的字段不適合建索引,例如"性別"這種只有兩三個值的字段
(4)字符字段只建前綴索引
(5)字符字段最好不要做主鍵
(6)不用外鍵,由程序保證約束
(7)盡量不用UNIQUE,由程序保證約束
(8)使用多列索引時主意順序和查詢條件保持一致,同時刪除不必要的單列索引
簡言之就是使用合適的數(shù)據(jù)類型,選擇合適的索引
選擇合適的數(shù)據(jù)類型
(1)使用可存下數(shù)據(jù)的最小的數(shù)據(jù)類型,整型 < date,time < char,varchar < blob
(2)使用簡單的數(shù)據(jù)類型,整型比字符處理開銷更小,因為字符串的比較更復(fù)雜。如,int類型存儲時間類型,bigint類型轉(zhuǎn)ip函數(shù)
(3)使用合理的字段屬性長度,固定長度的表會更快。使用enum、char而不是varchar
(4)盡可能使用not null定義字段
(5)盡量少用text,非用不可最好分表
選擇合適的索引列
(1)查詢頻繁的列,在where,group by,order by,on從句中出現(xiàn)的列
(2)where條件中<,<=,=,>,>=,between,in,以及l(fā)ike 字符串+通配符(%)出現(xiàn)的列
(3)長度小的列,索引字段越小越好,因為數(shù)據(jù)庫的存儲單位是頁,一頁中能存下的數(shù)據(jù)越多越好
(4)離散度大(不同的值多)的列,放在聯(lián)合索引前面。查看離散度,通過統(tǒng)計不同的列值來實現(xiàn),count越大,離散程度越高
3.sql的編寫需要注意優(yōu)化
(1)使用limit對查詢結(jié)果的記錄進行限定
(2)避免select *,將需要查找的字段列出來
(3)使用連接(join)來代替子查詢
(4)拆分大的delete或insert語句
(5)可通過開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL
(6)不做列運算:SELECT id WHERE age + 1 = 10,任何對列的操作都將導(dǎo)致表掃描,它包括數(shù)據(jù)庫教程函數(shù)、計算表達式等等,查詢時要盡可能將操作移至等號右邊
(7)sql語句盡可能簡單:一條sql只能在一個cpu運算;大語句拆小語句,減少鎖時間;一條大sql可以堵死整個庫
(8)OR改寫成IN:OR的效率是n級別,IN的效率是log(n)級別,in的個數(shù)建議控制在200以內(nèi)(9)不用函數(shù)和觸發(fā)器,在應(yīng)用程序?qū)崿F(xiàn)
(10)避免%xxx式查詢
(11)少用JOIN
(12)使用同類型進行比較,比如用'123'和'123'比,123和123比
(13)盡量避免在WHERE子句中使用!=或<>操作符,否則將引擎放棄使用索引而進行全表掃描
(14)對于連續(xù)數(shù)值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5
(15)列表數(shù)據(jù)不要拿全表,要使用LIMIT來分頁,每頁數(shù)量也不要太大
4.分區(qū)
MySQL在5.1版引入的分區(qū)是一種簡單的水平拆分,用戶需要在建表的時候加上分區(qū)參數(shù),對應(yīng)用是透明的無需修改代碼
對用戶來說,分區(qū)表是一個獨立的邏輯表,但是底層由多個物理子表組成,實現(xiàn)分區(qū)的代碼實際上是通過對一組底層表的對象封裝,但對SQL層來說是一個完全封裝底層的黑盒子。MySQL實現(xiàn)分區(qū)的方式也意味著索引也是按照分區(qū)的子表定義,沒有全局索引
用戶的SQL語句是需要針對分區(qū)表做優(yōu)化,SQL條件中要帶上分區(qū)條件的列,從而使查詢定位到少量的分區(qū)上,否則就會掃描全部分區(qū),可以通過EXPLAIN PARTITIONS來查看某條SQL語句會落在那些分區(qū)上,從而進行SQL優(yōu)化。
分區(qū)的好處是:
(1)可以讓單表存儲更多的數(shù)據(jù)
(2)分區(qū)表的數(shù)據(jù)更容易維護,可以通過清楚整個分區(qū)批量刪除大量數(shù)據(jù),也可以增加新的分區(qū)來支持新插入的數(shù)據(jù)。另外,還可以對一個獨立分區(qū)進行優(yōu)化、檢查、修復(fù)等操作
(3)部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上,速度會很快
(4)分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上,從而搞笑利用多個硬件設(shè)備
(5)可以使用分區(qū)表賴避免某些特殊瓶頸,例如InnoDB單個索引的互斥訪問、ext3文件系統(tǒng)的inode鎖競爭
(6)可以備份和恢復(fù)單個分區(qū)
分區(qū)的限制和缺點:
(1)一個表最多只能有1024個分區(qū)
(2)如果分區(qū)字段中有主鍵或者唯一索引的列,那么所有主鍵列和唯一索引列都必須包含進來
(3)分區(qū)表無法使用外鍵約束
(4)NULL值會使分區(qū)過濾無效
(5)所有分區(qū)必須使用相同的存儲引擎
分區(qū)的類型:
(1)RANGE分區(qū):基于屬于一個給定連續(xù)區(qū)間的列值,把多行分配給分區(qū)
(2)LIST分區(qū):類似于按RANGE分區(qū),區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇
(3)HASH分區(qū):基于用戶定義的表達式的返回值來進行選擇的分區(qū),該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL中有效的、產(chǎn)生非負整數(shù)值的任何表達式
(4)KEY分區(qū):類似于按HASH分區(qū),區(qū)別在于KEY分區(qū)只支持計算一列或多列,且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值
(5)具體關(guān)于mysql分區(qū)的概念請自行查詢官方文檔。
5.分表
分表就是把一張大表,按照如上過程都優(yōu)化了,還是查詢卡死,那就把這個表分成多張表,把一次查詢分成多次查詢,然后把結(jié)果組合返回給用戶。
分表分為垂直拆分和水平拆分,通常以某個字段做拆分項。比如以id字段拆分為100張表: 表名為 tableName_id%100
但:分表需要修改源程序代碼,會給開發(fā)帶來大量工作,極大的增加了開發(fā)成本,故:只適合在開發(fā)初期就考慮到了大量數(shù)據(jù)存在,做好了分表處理,不適合應(yīng)用上線了再做修改,成本太高!??!而且選擇這個方案,都不如選擇我提供的第二第三個方案的成本低!故不建議采用。
6.分庫
把一個數(shù)據(jù)庫分成多個,建議做個讀寫分離就行了,真正的做分庫也會帶來大量的開發(fā)成本,得不償失!不推薦使用。
此方案為個人綜合總結(jié),僅供參考!
關(guān)于MySql數(shù)據(jù)庫優(yōu)化方案是怎樣的呢問題的解答就分享到這里了,希望以上內(nèi)容可以對大家有一定的幫助,如果你還有很多疑惑沒有解開,可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識。