程序員必備MySQL數據庫開發常識

MySQL數據庫的應用非常普遍，本文為大家分享MySQL數據庫開發當中的一些常識，存儲引擎的選擇，索引的設計及使用和大批量插入時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