我在最近的幾個項目中深刻理解到，其實項目應用的瓶頸還是在db端，在只有少量數據及極少并發的情況下，并不需要多少的技巧就可以得到我們想要的結果，但是當數據量達到一定量級的時候，程序的每一個細節，數據庫的設計都會影響到系統的性能。這里就數據庫開發及優化的話題和大家做個討論和分析，也請大家完善，這里就以下幾個話題，我先發表自己的見解。
1.存儲引擎的選擇
2.索引的設計及使用
3.大批量插入時SQL語句的優化
文章來源: http://www.phpweblog.net/fuyongjie/archive/2009/02/23/6344.html
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.聯合索引的左前綴
先看索引結構:
2.關于like關鍵字
對于使用like的查詢，需要注意的是只有列的%不在第一個字符索引才可能被使用。以下分別展示了使用like的查詢，第一個是索引被使用的，第二個是索引未被使用的。
使用以下命令
myisam引擎默認是支持4GB，innodb理論上可以到6TB，假設單張表容量超過4GB，可能導致表都無法訪問了。可以通過以下命令增加表最大數據量：
? ?? ?這里我主要針對兩種存儲引擎進行簡單比較分別是MyISAM和InnoDB，首先比較下區別：
? ?? ?1. MyISAM不支持事務，不支持外鍵，優點是訪問速度高，批量插入速度快。假設大量的操作是select、insert，建議采用該存儲引擎。但是在我的實際應用中，出現過批量插入過于頻繁的時候，當數據量到達一定級別，出現表損壞的情況。
? ?? ?2. InnoDB支持事務處理，但是相對于前者，處理效率低一些，并且其索引及數據也更占用磁盤空間。在存儲一些關鍵數據，并需要對其進行事務操作的時候，我們可以選擇innodb，當然，我認為他不應該是訪問量太大的。
索引的設計及使用
沒有索引的表是恐怖的，除非里頭沒多少數據，但是怎么設計索引是合理的？恐怕不是所有人都明白，這里簡要分析下索引的設計及使用。1. 索引通常是設置where字句中的列，如果你設置select后的列，這是沒有任何意義的。當然你需要對某列進行排序，order by后的列也是可以建成索引的。
2. 使用唯一索引，主鍵就是最好的例子，假設你建的索引列，大量都是重復的，例如：性別，那么這樣的索引并不會加快搜索速度。至于為什么，請大家自行了解索引的工作原理。
3. 只要有可能，就要盡量限定索引的長度，例如索引列為 char(100)，在其前10個字符大部分都是唯一的，請設置索引的長度為10，使用短索引可以加快查詢速度，并節省硬盤空間。
4. 索引的左前綴特性，聯合索引實質上也是建立了多個的索引，那么是建立聯合索引好還是分別建多個索引好呢？顯然前者更好，利用左前綴特性，只要聯合索引的最左的列被用到，那么索引都會被使用。
5. 當然，最后要說的是，不要過度使用索引，索引越多，插入的速度越慢，尤其到數據量龐大時，同時，大量的索引將耗費很多硬盤空間，造成不必要的浪費。
下面舉幾個列子來說明索引的使用：
1.聯合索引的左前綴
先看索引結構:
復制內容到剪貼板
user是聯合索引的名稱，包含3個列，分別是username,order,email。接下來執行以下sql，使用explain命令來分析下運行結果。
代碼:
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)
復制內容到剪貼板
在兩句sql中，我們可以發現，第一個sql雖然沒用上，全部的索引列，但由于使用到了最左端的列，所以，聯合索引還是啟用了，第二句沒有使用到最左的列，所以索引沒有使用。
代碼:
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)
2.關于like關鍵字
對于使用like的查詢，需要注意的是只有列的%不在第一個字符索引才可能被使用。以下分別展示了使用like的查詢，第一個是索引被使用的，第二個是索引未被使用的。
復制內容到剪貼板
3. 查看索引使用情況
代碼:
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)
使用以下命令
復制內容到剪貼板
如果索引正在工作，那么Handler_read_key 會很高，如果查詢中出現Handler_read_rnd_next的值很高，則表明查詢低效，索引的應用并不合理。
代碼:
mysql> show status like 'Handler_read_key';
+------------------+-------+
| Variable_name? ? | Value |
+------------------+-------+
| Handler_read_key | 0? ???|
+------------------+-------+
1 row in set (0.00 sec)
大批量插入時的SQL語句優化
在大量插入時，尤其是并發插入時，mysql往往要承受更高的負載，使用mysql administortar的健康檢查就可以發現，其avg的值相當高，在這種情況下，首先要做的是sql語句的優化，比較下面兩個句子，后者的速度比前者要快得多。因為減少大量的連接。
復制內容到剪貼板
在 我的一個實際應用中，由于需要經常有數百個并發的插入，我還采用了insert delayed into來取代insert into，前者與后者的區別是在執行插入語句時，數據保存在內存隊列中，待數據庫空閑時執行，但是會立即返回一個插入成功的信息。使用insert delayed into時需要注意：此時不能使用mysql_insert_id()，因為此時并沒有真正插入。對特別重要的數據不宜采用該語句，避免數據以外丟失。
代碼:
insert into test values(aa,bb)
insert into test values(cc,dd)
insert into test values (aa),(bb),(cc),(dd)
其他方面的雜談
1.mysql myisam 表超過4G無法訪問的解決myisam引擎默認是支持4GB，innodb理論上可以到6TB，假設單張表容量超過4GB，可能導致表都無法訪問了。可以通過以下命令增加表最大數據量：
復制內容到剪貼板
這樣修改后數據文件可以支持到208TB左右。
代碼:
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
文章來源: http://www.phpweblog.net/fuyongjie/archive/2009/02/23/6344.html
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
