mysql效能最佳化

標籤：blog   http   使用   os   strong   io   資料   for   

http://blog.csdn.net/uestc_huan/article/category/536350  博主首頁

http://blog.csdn.net/uestc_huan/article/details/6071081 博文地址

本文不從DBA的角度去講解mysql Server的參數如何設定，而是從程式員和架構師的角度，去說明在寫程式和設計系統的時候，需要注意的mysql的一些最佳化點。因此，沒有在本文中詳細論述伺服器參數的設定含義和調優。

 

 

    關於索引

1. mysql使用的B+tree的深度的計算：

B+樹每塊數值的大小 = 每個block的大小 / 索引值的大小

比如mysql每個block為4K，索引值設為4個位元組的int，那麼每個索引塊的大小為4k/4 = 1k。

那麼儲存一億行的資料，需要的B+樹的高度為

h = log1000(一億) =3 （以1000為底，一億的對數）。可見B+樹的高度不會很高，一般深度都不會超過5.

 

 

2. 為了避免隨機IO操作，可以建立聯合index來避免隨機IO操作。即用“索引-值”對來建立一個聯合index。這樣，就可以直接從索引中讀取資料，而不需要根據索引再去磁碟讀取實際的資料。

比如 ： select age from user_info where name=lisi;

那麼，建立name-age的聯合索引，那麼從索引就可以直接讀取資料。如果僅僅建立name的索引，那麼它需要根據name=lisi定位到這行資料的位置，再從這個位置讀取出age。

 

 

3. 如果僅僅是點查詢，而不需要範圍查詢，那麼使用hash索引會比B+tree索引更快。就是僅僅需要 where aaa=bbb這樣的查詢條件，而不需要where aaa> bbb這樣的查詢條件。

 

 4. 使用logloader或者dump的操作速度，會比簡單的sql語句快5倍以上。

 

 5.log 放在單獨的磁碟上，可緩解io瓶頸。

 

 6.慢查詢是影響mysql效能的主要因素。慢查詢主要通過分析慢查詢日誌來處理。很多現有的工具可以分析。諸如 mysqldumpslow,  mysql_slow_log_filter,  mysql_slow_log_parser, mysqlsal等。

 

 

關於事務的理解

1. 事務應該具有ACID屬性，（atomicity, consistency, isolation, durability），一致性和持久性比較好理解。原子性和隔離性需要做一些說明。

事務的原子性僅僅指同一個事務本身的原子性。一個事務未執行或者執行一半時，另外的事務（另外一個串連中）是可以並發執行的。這涉及到事務隔離度的概念（isolation）。可以做一個測試，用兩個用戶端去串連資料庫，分別開始事務，分別執行兩個進程。只有在事務中執行了upate，insert，delete的語句，並且affect的行數大於0的時候，才會阻塞另外一個事務。而且這個跟資料庫的隔離度等級也有關係。

下面是關於隔離度的解釋。

（1）.查看當前會話隔離等級

select @@tx_isolation;

 

（2）.查看系統當前隔離等級

select @@global.tx_isolation;

 

（3）.設定當前會話隔離等級

set session transaction isolatin level repeatable read;

 

（4）.設定系統當前隔離等級

set global transaction isolation level repeatable read;

 

（5）.命令列，開始事務時

set autocommit=off 或者 start transaction

 

關於隔離等級的理解

（1）read uncommitted

可以看到未提交的資料（髒讀），舉個例子：別人說的話你都相信了，但是可能他只是說說，並不實際做。

 

（2）read committed

讀取提交的資料。但是，可能多次讀取的資料結果不一致（不可重複讀取，幻讀）。用讀寫的觀點就是：讀取的行資料，可以寫。

 

（3）repeatable read(MySQL預設隔離等級)

在MySQL中，其他事務新增的資料，看不到，不會產生幻讀。採用多版本並發控制（MVCC）機制解決幻讀問題。

 

（4）serializable

可讀，不可寫。寫資料必須等待另一個事務結束。

 

 

7. 兩個事務是可能發生死結的，

舉個例子：

Transaction1

     Start transaction;

     Update stockprice set close=45.5 where stock_id =4;

     Update stockprice set close=47.7 where stock_id=3;

     Commit;

Transaction2

    Start transaction;

     Update stockprice set close=45.5 where stock_id =3;

     Update stockprice set close=47.7 where stock_id=4;

     Commit;

當兩個事務都執行了第一條而未執行第二條時，就發生了死結。

 

 

關於資料類型

1. 避免用DEFAULT  NULL數值，原因一方面是為了索引速度更快(儘管在高版本的mysql已經不存在這個問題)，另一方面是避免應用程式產生一些不必要的困惑和bug.

 

 

2. int(1)和int(20)的區別僅僅是在顯示上，對於計算和預存程序，是完全一樣的。

 

 

3. DECIMAL在4.1版本及之前，僅僅是儲存類型，它的效率比float和double都低。DEMICAL僅僅應該用在金融數字上，因為它可以指定所需要的計算精度。

 

4. Blob和text是最耗效能的。因為記憶體儲存機制（memory storage engine）不支援這兩個類型，因為查詢這兩個類型的資料都會用到磁碟暫存資料表。儘可能避免使用這兩種類型資料。如果要對這兩種類型的資料排序，用order by substring(column, length)轉成string類型。此時如果substring小，可以轉成記憶體暫存資料表，速度會快很多。額外插一句，字元匹配尋找演算法的最高效率，不會高於KMP演算法的效率。

 

 

5.可以用enum代替string類型。Enum最多可以存65535個字元。但此時排序是按enum值排序而非string值排序。

 

6. datetime 和timestamp的比較。

Datatime:  8位元組，與時區不轉換，預設值是NULL

Timestamp: 4位元組，與時區相關，預設插入值是目前時間。

如果沒有特別需要，建議用timestamp。別用整數型資料來儲存時間，儘管可以，但不推薦，因為得不到任何好處。

 

7.一般不推薦使用bit的資料類型，用tinyint會更利於擴充。Bit set也可以通過tiny int的位元運算來替代。

 

 

8. 不要濫用MySQL的類型自動轉換功能

  

 

查詢最佳化

 

1. 用跨庫動作陳述式，可能導致master和slaver不一致現象

就是對db.table這樣的語句，可能導致master和slaver不一致。

 

2.mysql用於處理串連和釋放串連的效率很高。它被設計成適合簡單快速的查詢方式。因此，如果能將很複雜的查詢分割成多個簡單的查詢，而在應用程式層將這些小查詢串連起來，效率會更高。主要原因是複雜查詢會導致比較大面積的鎖定，影響效率。而多個小查詢鎖的範圍就小很多。而串連的開銷，在mysql中基本可以忽略。

比如：

Select * from tag

Join tag_post on tag_post.tag_id=tag.id

Join post on tag_post.post_id=post.id

Where tag.tag=’mysql’;

拆分成

Select * from tag where tag=’mysql’;

Select * from tag_post where tag_id=1234;

Select * from post where post.id in (123,2343,4545);

如果應用程式層能處理這些資料的拆分和組合，那麼效率會更高。

這樣做的好處：

（1）.cache會更有效，不需要中間表暫存資料表的產生。

（2）在應用程式層做組合，更有利於擴充，可以把某些表單獨放在獨立的server上，分開放。

（3）鎖的範圍更小

（4）in的執行效率比join的執行效率高。Mysql的子查詢的效率很低。據說6.0會引入semijoin的計劃來解決。但目前能少用Join還是少用join.

 

 

3. 幾個原則

（1）最佳化更需要最佳化的query

(2) 定位最佳化對象的效能瓶頸，是io還是cpu還是記憶體

（3）明確最佳化目標

（4）從explain入手，可以用force index來檢查走不同索引的效率。SELECT * FROM TABLE1 FORCE INDEX (FIELD1).

（5）開啟profile: 用命令”set profiling”命令。用”show profile”查看概要資訊。其它profile操作這裡不詳細敘述。

 

（6）永遠用曉得結果集去驅動大的結果集

（7）儘可能在索引中完成排序

 

（8）不取多餘的資料

 

（9） 使用最有效過濾條件。

（10）儘可能避免使用複雜的join和子查詢

 

  

4. 查詢語句不要對錶格資料進行操作

比如不要使用select * from tablename where  from_unixtime(operatetime) < xxxxx;

而使用select * from tablename where operatetime < unixtimstamp(xxxxx);

 

 

 

 

表設計最佳化

1. 儲存的資料編碼最好和表的編碼一致。

 

2.根據業務特點合理利用冗餘資料，減少Join查詢。

 

3.合理利用資料類型，能簡單不要複雜，能定長不要變長，能不用字元型就別用字元型。

 

4.索引很重要，合理設計索引。

 

5.將不常用的大欄位單獨拆分出去。某個欄位很大，而且不常用，就不要跟常用的一些資訊儲存在一張表中。

 

  

一些語句

 

(1)Select for update

用於查詢-更新原子操作

(2)LAST_INSERT_ID()

用於保證唯一性，比如訂單編號