很多時候， 我們預(yù)期查詢的結(jié)果最多是1條記錄數(shù)據(jù)， 那么這個時候， 最好用上limit 1, 當(dāng)查到這條數(shù)據(jù)后， mysql會立即終止繼續(xù)查詢， 不進行更多的無用查詢， 從而提升了效率。
我們來實際測試一下， 在一個擁有10萬的mysql表中， 查找lily的分數(shù)（假設(shè)系統(tǒng)中只有1個lily, 而我們預(yù)期也只需要這條數(shù)據(jù)）。為了顯示出時間的差別， 我并不對表的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)
我們打開set profiling=1;的開關(guān)，執(zhí)行mysql語句來對比：
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)
可見，我們針對是否采用limit 1進行了5次對比測試， 來看看結(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)
可見，采用limit 1后， mysql語句的效率確實提升很多。 當(dāng)表更大時， 效率提升會更加明顯。
我們已經(jīng)從理論和實踐的腳本都說明了limit的優(yōu)點， 所以， 建議是：在可用limit的時候要用limit （當(dāng)然， 如果結(jié)果是多個，肯定不能limit 1?。?/p>
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請查看下面相關(guān)鏈接
您可能感興趣的文章:- MySQL之select in 子查詢優(yōu)化的實現(xiàn)
- 一篇文章掌握MySQL的索引查詢優(yōu)化技巧
- MySQL千萬級大數(shù)據(jù)SQL查詢優(yōu)化知識點總結(jié)
- 一步步教你MySQL查詢優(yōu)化分析教程
- Mysql慢查詢優(yōu)化方法及優(yōu)化原則
- MySQL查詢優(yōu)化之查詢慢原因和解決技巧
