Mysql做到高併發幾點注意事項

NO IMAGE

1、拆分大的DELETE或INSERT語句

 

如果你需要在一個線上的網站上去執行一個大的DELETE或INSERT查詢,你需要非常小心,要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的,表一鎖住了,別的操作都進不來了。

 

如果你把你的表鎖上一段時間,比如30秒鐘,那麼對於一個有很高訪問量的站點來說,這30秒所積累的訪問程序/執行緒,資料庫連結,開啟的檔案數,可能不僅僅會讓你泊WEB服務Crash,還可能會讓你的整臺伺服器馬上掛了。

所以,如果你有一個大的處理,你定你一定把其拆分,使用LIMIT條件是一個好的方法。下面是一個示例

 

 

2、垂直分割

“垂直分割”是一種把資料庫中的表按列變成幾張表的方法,這樣可以降低表的複雜度和欄位的數目,從而達到優化的目的。(以前,在銀行做過專案,見過一張表有100多個欄位,很恐怖)

示例一:在Users表中有一個欄位是家庭地址,這個欄位是可選欄位,相比起,而且你在資料庫操作的時候除了個人資訊外,你並不需要經常讀取或是改寫這個欄位。那麼,為什麼不把他放到另外一張表中呢?這樣會讓你的表有更好的效能,大家想想是不是,大量的時候,我對於使用者表來說,只有使用者ID,使用者名稱,口令,使用者角色等會被經常使用。小一點的表總是會有好的效能。

示例二:你有一個叫“last_login”的欄位,它會在每次使用者登入時被更新。但是,每次更新時會導致該表的查詢快取被清空。所以,你可以把這個欄位放到另一個表中,這樣就不會影響你對使用者ID,使用者名稱,使用者角色的不停地讀取了,因為查詢快取會幫你增加很多效能。hp程式設計師之家

另外,你需要注意的是,這些被分出去的欄位所形成的表,你不會經常性地去Join他們,不然的話,這樣的效能會比不分割時還要差,而且,會是極數級的下降

 

3、選擇正確的儲存引擎

在MySQL中有兩個儲存引擎MyISAM和InnoDB,每個引擎都有利有弊。酷殼以前文章《MySQL:
InnoDB 還是 MyISAM?》討論和這個事情。hp程式設計師之家

MyISAM適合於一些需要大量查詢的應用,但其對於有大量寫操作並不是很好。甚至你只是需要update一個欄位,整個表都會被鎖起來,而別的程序,就算是讀程序都無法操作直到讀操作完成。另外,MyISAM對於
SELECT COUNT(*) 這類的計算是超快無比的。 www~phperz~com

InnoDB的趨勢會是一個非常複雜的儲存引擎,對於一些小的應用,它會比
MyISAM還慢。他是它支援“行鎖”
,於是在寫操作比較多的時候,會更優秀。並且,他還支援更多的高階應用,比如:事務。

 

Myisam讀的效果好,寫的效率差,這和它資料儲存格式,索引的指標和鎖的策略有關的。

資料儲存格式:Myisam的資料是順序儲存的,innodb資料儲存方式是聚簇索引。

索引的指標:Myisam的索引btree上的節點是一個指向資料物理位置的指標,所以查詢起來很快,innodb索引節點存的則是資料的主鍵,所以需要根據主鍵二次查詢。

鎖的策略:Myisam鎖是表鎖,只有讀讀之間是併發的,寫寫之間和讀寫之間(讀和插入之間是可以併發的,去設定concurrent_insert引數,定期執行表優化操作,更新操作就沒有辦法了)是序列的,所以寫起來慢,並且預設的寫優先順序比讀優先順序高,高到寫操作來了後,可以馬上插入到讀操作前面去,如果批量寫,會導致讀請求餓死,所以要設定讀寫優先順序或設定多少寫操作後執行讀操作的策略;Myisam不要使用查詢時間太長的sql,如果策略使用不當,也會導致寫餓死,所以儘量去拆分查詢效率低的sql。

Innodb一般都是行鎖,這個一般指的是sql用到索引的時候,行鎖是加在索引上的,不是加在資料記錄上的,如果sql沒有用到索引,仍然會鎖定表。mysql的讀寫之間是可以併發的,普通的select是不需要鎖的,當查詢的記錄遇到鎖時,用的是一致性的非鎖定快照讀,也就是根據資料庫隔離級別策略,會去讀被鎖定行的快照,其它更新或加鎖讀語句用的是當前讀,讀取原始行;因為普通讀與寫不衝突,所以innodb不會出現讀寫餓死的情況,又因為在使用索引的時候用的是行鎖,鎖的粒度小,競爭相同鎖的情況就少,就增加了併發處理,所以併發讀寫的效率還是很優秀的,問題在於索引查詢後的根據主鍵的二次查詢導致效率低;

注意:

Innodb 聚集索引是按照主鍵(primarykey)進行聚集,被索引的列其實是主鍵列,如果沒定義主鍵,Innodb會試著使用唯一非空索引Unique
Index來代替。其他索引(普通索引)中不會儲存行的物理位置,而是儲存主鍵的值,所以通過”二級索引”進行查詢是先找到主鍵,再找到行,要進行二次索引查詢

組合索引和單列索引
組合索引在多列上建立,單列索引在一個列上建立。
查詢使用索引的條件不同一般組合索引需要按照“最左字首”來執行查詢,並不是每個列都需要覆蓋,只是從左邊的列開始組合。

原因:

多列索引是先按照第一列進行排序,然後在第一列排好序的基礎上再對第二列排序,如果沒有第一列的話,直接訪問第二列,那第二列肯定是無序的,直接訪問後面的列就用不到索引了。索引的最大好處就是:它必然是有序的。如果這個優點都不能利用,那索引也就沒什麼價值了。

例如有索引key(a,b,c)
³where a=xx and b=xx and c=xxx 此語句可以用到索引
³where b=xx and a=xx and c=xxx 同上,順序沒有關係,同樣能用到索引
³where a=xx and b=xx 可以用到索引
³where a=xx and c=xx 可以用到索引(這個以前一直理解錯了)
³where b=xx and c=xx 用不到索引
³where b=xx 用不到索引
³where c=xx 用不到索引