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