快捷導(dǎo)航

mysql慢查詢優(yōu)化之從理論和實(shí)踐說明limit的優(yōu)點(diǎn)

更新時(shí)間：2019年04月04日 09:24:12 作者：stpeace

今天小編就為大家分享一篇關(guān)于mysql慢查詢優(yōu)化之從理論和實(shí)踐說明limit的優(yōu)點(diǎn)，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧

很多時(shí)候，我們預(yù)期查詢的結(jié)果最多是1條記錄數(shù)據(jù)，那么這個(gè)時(shí)候，最好用上limit 1, 當(dāng)查到這條數(shù)據(jù)后， mysql會立即終止繼續(xù)查詢，不進(jìn)行更多的無用查詢，從而提升了效率。

我們來實(shí)際測試一下，在一個(gè)擁有10萬的mysql表中，查找lily的分?jǐn)?shù)（假設(shè)系統(tǒng)中只有1個(gè)lily, 而我們預(yù)期也只需要這條數(shù)據(jù)）。為了顯示出時(shí)間的差別，我并不對表的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進(jìn)行了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語句的效率確實(shí)提升很多。當(dāng)表更大時(shí)，效率提升會更加明顯。

我們已經(jīng)從理論和實(shí)踐的腳本都說明了limit的優(yōu)點(diǎn)，所以，建議是：在可用limit的時(shí)候要用limit （當(dāng)然，如果結(jié)果是多個(gè)，肯定不能limit 1啊）

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請查看下面相關(guān)鏈接

您可能感興趣的文章: