很多時(shí)候， 我們預(yù)期查詢(xún)的結(jié)果最多是1條記錄數(shù)據(jù)， 那么這個(gè)時(shí)候， 最好用上limit 1, 當(dāng)查到這條數(shù)據(jù)后， mysql會(huì)立即終止繼續(xù)查詢(xún)， 不進(jìn)行更多的無(wú)用查詢(xún)， 從而提升了效率。
我們來(lái)實(shí)際測(cè)試一下， 在一個(gè)擁有10萬(wàn)的mysql表中， 查找lily的分?jǐn)?shù)（假設(shè)系統(tǒng)中只有1個(gè)lily, 而我們預(yù)期也只需要這條數(shù)據(jù)）。為了顯示出時(shí)間的差別， 我并不對(duì)表的name字段建索引。
先看看表結(jié)構(gòu)：
mysql> show create table tb_province;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_province | CREATE TABLE `tb_province` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`score` int(10) unsigned DEFAULT '0',
`x` int(10) unsigned DEFAULT '0',
`x1` int(10) unsigned DEFAULT '0',
`x2` int(10) unsigned DEFAULT '0',
`x3` int(10) unsigned DEFAULT '0',
`x4` int(10) unsigned DEFAULT '0',
`x5` int(10) unsigned DEFAULT '0',
`x6` int(10) unsigned DEFAULT '0',
`x7` int(10) unsigned DEFAULT '0',
`x8` int(10) unsigned DEFAULT '0',
`x9` int(10) unsigned DEFAULT '0',
`x10` int(10) unsigned DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=124178 DEFAULT CHARSET=latin1 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
我們打開(kāi)set profiling=1;的開(kāi)關(guān)，執(zhí)行mysql語(yǔ)句來(lái)對(duì)比：
mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.03 sec)
mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.03 sec)
mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.04 sec)
mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.02 sec)
mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.03 sec)
mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)
mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)
mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)
mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.01 sec)
mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)
可見(jiàn)，我們針對(duì)是否采用limit 1進(jìn)行了5次對(duì)比測(cè)試， 來(lái)看看結(jié)果吧：
mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
| 5 | 0.02686000 | select score from tb_province where name='lily' |
| 6 | 0.02649050 | select score from tb_province where name='lily' |
| 7 | 0.03413500 | select score from tb_province where name='lily' |
| 8 | 0.02601350 | select score from tb_province where name='lily' |
| 9 | 0.02785775 | select score from tb_province where name='lily' |
| 10 | 0.00042300 | select score from tb_province where name='lily' limit 1 |
| 11 | 0.00043250 | select score from tb_province where name='lily' limit 1 |
| 12 | 0.00044350 | select score from tb_province where name='lily' limit 1 |
| 13 | 0.00053200 | select score from tb_province where name='lily' limit 1 |
| 14 | 0.00043250 | select score from tb_province where name='lily' limit 1 |
+----------+------------+---------------------------------------------------------+
14 rows in set, 1 warning (0.00 sec)
可見(jiàn)，采用limit 1后， mysql語(yǔ)句的效率確實(shí)提升很多。 當(dāng)表更大時(shí)， 效率提升會(huì)更加明顯。
我們已經(jīng)從理論和實(shí)踐的腳本都說(shuō)明了limit的優(yōu)點(diǎn)， 所以， 建議是：在可用limit的時(shí)候要用limit （當(dāng)然， 如果結(jié)果是多個(gè)，肯定不能limit 1啊）
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接
您可能感興趣的文章:- MySQL之select in 子查詢(xún)優(yōu)化的實(shí)現(xiàn)
- 一篇文章掌握MySQL的索引查詢(xún)優(yōu)化技巧
- MySQL千萬(wàn)級(jí)大數(shù)據(jù)SQL查詢(xún)優(yōu)化知識(shí)點(diǎn)總結(jié)
- 一步步教你MySQL查詢(xún)優(yōu)化分析教程
- Mysql慢查詢(xún)優(yōu)化方法及優(yōu)化原則
- MySQL查詢(xún)優(yōu)化之查詢(xún)慢原因和解決技巧
