我在最近的幾個(gè)項(xiàng)目中深刻理解到,其實(shí)項(xiàng)目應(yīng)用的瓶頸還是在db端,在只有少量數(shù)據(jù)及極少并發(fā)的情況下,并不需要多少的技巧就可以得到我們想要的結(jié)果,但是當(dāng)數(shù)據(jù)量達(dá)到一定量級(jí)的時(shí)候,程序的每一個(gè)細(xì)節(jié),數(shù)據(jù)庫(kù)的設(shè)計(jì)都會(huì)影響到系統(tǒng)的性能。這里就數(shù)據(jù)庫(kù)開(kāi)發(fā)及優(yōu)化的話(huà)題和大家做個(gè)討論和分析,也請(qǐng)大家完善,這里就以下幾個(gè)話(huà)題,我先發(fā)表自己的見(jiàn)解。
1.存儲(chǔ)引擎的選擇
2.索引的設(shè)計(jì)及使用
3.大批量插入時(shí)SQL語(yǔ)句的優(yōu)化
文章來(lái)源: http://www.phpweblog.net/fuyongjie/archive/2009/02/23/6344.html
1.存儲(chǔ)引擎的選擇
2.索引的設(shè)計(jì)及使用
3.大批量插入時(shí)SQL語(yǔ)句的優(yōu)化
存儲(chǔ)引擎的選擇
聲明:本文所針對(duì)的數(shù)據(jù)庫(kù)版本都是MYSQL 5
? ?? ?這里我主要針對(duì)兩種存儲(chǔ)引擎進(jìn)行簡(jiǎn)單比較分別是MyISAM和InnoDB,首先比較下區(qū)別:
? ?? ?1. MyISAM不支持事務(wù),不支持外鍵,優(yōu)點(diǎn)是訪(fǎng)問(wèn)速度高,批量插入速度快。假設(shè)大量的操作是select、insert,建議采用該存儲(chǔ)引擎。但是在我的實(shí)際應(yīng)用中,出現(xiàn)過(guò)批量插入過(guò)于頻繁的時(shí)候,當(dāng)數(shù)據(jù)量到達(dá)一定級(jí)別,出現(xiàn)表?yè)p壞的情況。
? ?? ?2. InnoDB支持事務(wù)處理,但是相對(duì)于前者,處理效率低一些,并且其索引及數(shù)據(jù)也更占用磁盤(pán)空間。在存儲(chǔ)一些關(guān)鍵數(shù)據(jù),并需要對(duì)其進(jìn)行事務(wù)操作的時(shí)候,我們可以選擇innodb,當(dāng)然,我認(rèn)為他不應(yīng)該是訪(fǎng)問(wèn)量太大的。
1. 索引通常是設(shè)置where字句中的列,如果你設(shè)置select后的列,這是沒(méi)有任何意義的。當(dāng)然你需要對(duì)某列進(jìn)行排序,order by后的列也是可以建成索引的。
2. 使用唯一索引,主鍵就是最好的例子,假設(shè)你建的索引列,大量都是重復(fù)的,例如:性別,那么這樣的索引并不會(huì)加快搜索速度。至于為什么,請(qǐng)大家自行了解索引的工作原理。
3. 只要有可能,就要盡量限定索引的長(zhǎng)度,例如索引列為 char(100),在其前10個(gè)字符大部分都是唯一的,請(qǐng)?jiān)O(shè)置索引的長(zhǎng)度為10,使用短索引可以加快查詢(xún)速度,并節(jié)省硬盤(pán)空間。
4. 索引的左前綴特性,聯(lián)合索引實(shí)質(zhì)上也是建立了多個(gè)的索引,那么是建立聯(lián)合索引好還是分別建多個(gè)索引好呢?顯然前者更好,利用左前綴特性,只要聯(lián)合索引的最左的列被用到,那么索引都會(huì)被使用。
5. 當(dāng)然,最后要說(shuō)的是,不要過(guò)度使用索引,索引越多,插入的速度越慢,尤其到數(shù)據(jù)量龐大時(shí),同時(shí),大量的索引將耗費(fèi)很多硬盤(pán)空間,造成不必要的浪費(fèi)。
下面舉幾個(gè)列子來(lái)說(shuō)明索引的使用:
1.聯(lián)合索引的左前綴
先看索引結(jié)構(gòu):
2.關(guān)于like關(guān)鍵字
對(duì)于使用like的查詢(xún),需要注意的是只有列的%不在第一個(gè)字符索引才可能被使用。以下分別展示了使用like的查詢(xún),第一個(gè)是索引被使用的,第二個(gè)是索引未被使用的。
使用以下命令
myisam引擎默認(rèn)是支持4GB,innodb理論上可以到6TB,假設(shè)單張表容量超過(guò)4GB,可能導(dǎo)致表都無(wú)法訪(fǎng)問(wèn)了。可以通過(guò)以下命令增加表最大數(shù)據(jù)量:
? ?? ?這里我主要針對(duì)兩種存儲(chǔ)引擎進(jìn)行簡(jiǎn)單比較分別是MyISAM和InnoDB,首先比較下區(qū)別:
? ?? ?1. MyISAM不支持事務(wù),不支持外鍵,優(yōu)點(diǎn)是訪(fǎng)問(wèn)速度高,批量插入速度快。假設(shè)大量的操作是select、insert,建議采用該存儲(chǔ)引擎。但是在我的實(shí)際應(yīng)用中,出現(xiàn)過(guò)批量插入過(guò)于頻繁的時(shí)候,當(dāng)數(shù)據(jù)量到達(dá)一定級(jí)別,出現(xiàn)表?yè)p壞的情況。
? ?? ?2. InnoDB支持事務(wù)處理,但是相對(duì)于前者,處理效率低一些,并且其索引及數(shù)據(jù)也更占用磁盤(pán)空間。在存儲(chǔ)一些關(guān)鍵數(shù)據(jù),并需要對(duì)其進(jìn)行事務(wù)操作的時(shí)候,我們可以選擇innodb,當(dāng)然,我認(rèn)為他不應(yīng)該是訪(fǎng)問(wèn)量太大的。
索引的設(shè)計(jì)及使用
沒(méi)有索引的表是恐怖的,除非里頭沒(méi)多少數(shù)據(jù),但是怎么設(shè)計(jì)索引是合理的?恐怕不是所有人都明白,這里簡(jiǎn)要分析下索引的設(shè)計(jì)及使用。1. 索引通常是設(shè)置where字句中的列,如果你設(shè)置select后的列,這是沒(méi)有任何意義的。當(dāng)然你需要對(duì)某列進(jìn)行排序,order by后的列也是可以建成索引的。
2. 使用唯一索引,主鍵就是最好的例子,假設(shè)你建的索引列,大量都是重復(fù)的,例如:性別,那么這樣的索引并不會(huì)加快搜索速度。至于為什么,請(qǐng)大家自行了解索引的工作原理。
3. 只要有可能,就要盡量限定索引的長(zhǎng)度,例如索引列為 char(100),在其前10個(gè)字符大部分都是唯一的,請(qǐng)?jiān)O(shè)置索引的長(zhǎng)度為10,使用短索引可以加快查詢(xún)速度,并節(jié)省硬盤(pán)空間。
4. 索引的左前綴特性,聯(lián)合索引實(shí)質(zhì)上也是建立了多個(gè)的索引,那么是建立聯(lián)合索引好還是分別建多個(gè)索引好呢?顯然前者更好,利用左前綴特性,只要聯(lián)合索引的最左的列被用到,那么索引都會(huì)被使用。
5. 當(dāng)然,最后要說(shuō)的是,不要過(guò)度使用索引,索引越多,插入的速度越慢,尤其到數(shù)據(jù)量龐大時(shí),同時(shí),大量的索引將耗費(fèi)很多硬盤(pán)空間,造成不必要的浪費(fèi)。
下面舉幾個(gè)列子來(lái)說(shuō)明索引的使用:
1.聯(lián)合索引的左前綴
先看索引結(jié)構(gòu):
復(fù)制內(nèi)容到剪貼板
user是聯(lián)合索引的名稱(chēng),包含3個(gè)列,分別是username,order,email。接下來(lái)執(zhí)行以下sql,使用explain命令來(lái)分析下運(yùn)行結(jié)果。
代碼:
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)
復(fù)制內(nèi)容到剪貼板
在兩句sql中,我們可以發(fā)現(xiàn),第一個(gè)sql雖然沒(méi)用上,全部的索引列,但由于使用到了最左端的列,所以,聯(lián)合索引還是啟用了,第二句沒(méi)有使用到最左的列,所以索引沒(méi)有使用。
代碼:
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.關(guān)于like關(guān)鍵字
對(duì)于使用like的查詢(xún),需要注意的是只有列的%不在第一個(gè)字符索引才可能被使用。以下分別展示了使用like的查詢(xún),第一個(gè)是索引被使用的,第二個(gè)是索引未被使用的。
復(fù)制內(nèi)容到剪貼板
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)
使用以下命令
復(fù)制內(nèi)容到剪貼板
如果索引正在工作,那么Handler_read_key 會(huì)很高,如果查詢(xún)中出現(xiàn)Handler_read_rnd_next的值很高,則表明查詢(xún)低效,索引的應(yīng)用并不合理。
代碼:
mysql> show status like 'Handler_read_key';
+------------------+-------+
| Variable_name? ? | Value |
+------------------+-------+
| Handler_read_key | 0? ???|
+------------------+-------+
1 row in set (0.00 sec)
大批量插入時(shí)的SQL語(yǔ)句優(yōu)化
在大量插入時(shí),尤其是并發(fā)插入時(shí),mysql往往要承受更高的負(fù)載,使用mysql administortar的健康檢查就可以發(fā)現(xiàn),其avg的值相當(dāng)高,在這種情況下,首先要做的是sql語(yǔ)句的優(yōu)化,比較下面兩個(gè)句子,后者的速度比前者要快得多。因?yàn)闇p少大量的連接。
復(fù)制內(nèi)容到剪貼板
在 我的一個(gè)實(shí)際應(yīng)用中,由于需要經(jīng)常有數(shù)百個(gè)并發(fā)的插入,我還采用了insert delayed into來(lái)取代insert into,前者與后者的區(qū)別是在執(zhí)行插入語(yǔ)句時(shí),數(shù)據(jù)保存在內(nèi)存隊(duì)列中,待數(shù)據(jù)庫(kù)空閑時(shí)執(zhí)行,但是會(huì)立即返回一個(gè)插入成功的信息。使用insert delayed into時(shí)需要注意:此時(shí)不能使用mysql_insert_id(),因?yàn)榇藭r(shí)并沒(méi)有真正插入。對(duì)特別重要的數(shù)據(jù)不宜采用該語(yǔ)句,避免數(shù)據(jù)以外丟失。
代碼:
insert into test values(aa,bb)
insert into test values(cc,dd)
insert into test values (aa),(bb),(cc),(dd)
其他方面的雜談
1.mysql myisam 表超過(guò)4G無(wú)法訪(fǎng)問(wèn)的解決myisam引擎默認(rèn)是支持4GB,innodb理論上可以到6TB,假設(shè)單張表容量超過(guò)4GB,可能導(dǎo)致表都無(wú)法訪(fǎng)問(wèn)了。可以通過(guò)以下命令增加表最大數(shù)據(jù)量:
復(fù)制內(nèi)容到剪貼板
這樣修改后數(shù)據(jù)文件可以支持到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

bestmost
2009-02-23 13:33
發(fā)表評(píng)論
文章來(lái)源: http://www.phpweblog.net/fuyongjie/archive/2009/02/23/6344.html
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力,如果您喜歡我的文章,感覺(jué)我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
