詳解MySQL三項實用開發知識

其實項目應用的瓶頸還是在db端，在只有少量資料及極少並發的情況下，並不需要多少的技巧就可以得到我們想要的結果，但是當資料量達到一定量級的時候，程式的每一個細節，資料庫的設計都會影響到系統的效能。這裡就資料庫開發及最佳化的話題和大家做個討論和分析，也請大家完善，這裡就以下幾個話題，我先發表自己的見解。

1.儲存引擎的選擇

2.索引的設計及使用

3.大批量插入時SQL語句的最佳化

儲存引擎的選擇

聲明：本文所針對的資料庫版本都是MYSQL 5這裡我主要針對兩種儲存引擎進行簡單比較分別是MyISAM和InnoDB，首先比較下區別：

1. MyISAM不支援事務，不支援外鍵，優點是訪問速度高，批量插入速度快。假設大量的操作是select、insert，建議採用該儲存引擎。但是在我的實際應用中，出現過批量插入過於頻繁的時候，當資料量到達一定層級，出現表損壞的情況。

2. InnoDB支援交易處理，但是相對於前者，處理效率低一些，並且其索引及資料也更佔用磁碟空間。在儲存一些關鍵資料，並需要對其進行事務操作的時候，我們可以選擇innodb，當然，我認為他不應該是訪問量太大的。

索引的設計及使用

沒有索引的表是恐怖的，除非裡頭沒多少資料，但是怎麼設計索引是合理的？恐怕不是所有人都明白，這裡簡要分析下索引的設計及使用。

1. 索引通常是設定where字句中的列，如果你設定select後的列，這是沒有任何意義的。當然你需要對某列進行排序，order by後的列也是可以建成索引的。

2. 使用唯一索引，主鍵就是最好的例子，假設你建的索引列，大量都是重複的，例如：性別，那麼這樣的索引並不會加快搜尋速度。至於為什麼，請大家自行瞭解索引的工作原理。

3. 只要有可能，就要盡量限定索引的長度，例如索引列為 char(100)，在其前10個字元大部分都是唯一的，請設定索引的長度為10，使用短索引可以加快查詢速度，並節省硬碟空間。

4. 索引的左首碼特性，聯合索引實質上也是建立了多個的索引，那麼是建立聯合索引好還是分別建多個索引好呢？顯然前者更好，利用左首碼特性，只要聯合索引的最左的列被用到，那麼索引都會被使用。

5. 當然，最後要說的是，不要過度使用索引，索引越多，插入的速度越慢，尤其到資料量龐大時，同時，大量的索引將耗費很多硬碟空間，造成不必要的浪費。

下面舉幾個列子來說明索引的使用：

1.聯合索引的左首碼

先看索引結構:

代碼:

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user  |    0 | PRIMARY  |1 | user_id     | A   |     2 |     NULL | NULL   || BTREE|   |
| user  |    1 | user     |1 | username    | A   |  NULL |     NULL | NULL   || BTREE|   |
| user  |    1 | user     |2 | order | A   |  NULL |     NULL | NULL   || BTREE|   |
| user  |    1 | user     |3 | email | A   |  NULL |     NULL | NULL   | YES  | BTREE|   |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

user是聯合索引的名稱，包含3個列，分別是username,order,email。接下來執行以下sql，使用explain命令來分析下運行結果。

代碼:

mysql> explain select * from user where username='leehui'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ |  1 | SIMPLE| user  | ref  | user    | user | 152     | const |    1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ 1 row in set (0.00 sec) mysql> explain select * from user where pws='123'; +----+-------------+-------+------+---------------+------+---------+------+------+---------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+---------+ |  1 | SIMPLE| user  | ALL  | NULL    | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+---------+ 1 row in set (0.00 sec)

在兩句sql中，我們可以發現，第一個sql雖然沒用上，全部的索引列，但由於使用到了最左端的列，所以，聯合索引還是啟用了，第二句沒有使用到最左的列，所以索引沒有使用。

2.關於like關鍵字

對於使用like的查詢，需要注意的是只有列的%不在第一個字元索引才可能被使用。以下分別展示了使用like的查詢，第一個是索引被使用的，第二個是索引未被使用的。

代碼:

mysql> explain select * from user where username like'lee%'; +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ |  1 | SIMPLE| user  | range | user    | user | 152     | NULL |    1 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ 1 row in set (0.00 sec) mysql> explain select * from user where username like'%lee'; +----+-------------+-------+------+---------------+------+---------+------+------+----------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+ |  1 | SIMPLE| user  | ALL  | NULL    | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------+ 1 row in set (0.00 sec)

3. 查看索引使用方式

使用以下命令，代碼:

mysql> show status like 'Handler_read_key'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | Handler_read_key | 0     | +------------------+-------+ 1 row in set (0.00 sec)

如果索引正在工作，那麼Handler_read_key 會很高，如果查詢中出現Handler_read_rnd_next的值很高，則表明查詢低效，索引的應用並不合理。

大批量插入時的SQL語句最佳化

在大量插入時，尤其是並發插入時，mysql往往要承受更高的負載，使用mysql administortar的健全狀態檢查就可以發現，其avg的值相當高，在這種情況下，首先要做的是sql語句的最佳化，比較下面兩個句子，後者的速度比前者要快得多。因為減少大量的串連。

複製內容到剪貼簿代碼:

insert into test values(aa,bb) insert into test values(cc,dd) insert into test values (aa),(bb),(cc),(dd)

在我的一個實際應用中，由於需要經常有數百個並發的插入，我還採用了insert delayed into來取代insert into，前者與後者的區別是在執行插入語句時，資料儲存在記憶體隊列中，待資料庫空閑時執行，但是會立即返回一個插入成功的資訊。使用insert delayed into時需要注意：此時不能使用mysql_insert_id()，因為此時並沒有真正插入。對特別重要的資料不宜採用該語句，避免資料以外丟失。

其他方面的雜談

1.mysql myisam 表超過4G無法訪問的解決

myisam引擎預設是支援4GB，innodb理論上可以到6TB，假設單張表容量超過4GB，可能導致表都無法訪問了。可以通過以下命令增加表最大資料量：
複製內容到剪貼簿
代碼:

mysql> alter table user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000; Query OK, 2 rows affected (0.09 sec) Records: 2  Duplicates: 0  Warnings: 0

這樣修改後資料檔案可以支援到208TB左右。

1. 詳解MySQL資料庫提升效能的八種方法
2. MySQL中Order By實現原理分析
3. 詳解MySQL分組查詢Group By實現原理