mysql慢查詢優(yōu)化之從理論和實踐說明limit的優(yōu)點
發(fā)布日期：2022-03-13 11:04 | 文章來源：源碼中國
很多時候， 我們預(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)鏈接
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信