mysql慢查询优化之从理论和实践说明limit的优点

很多时候， 我们预期查询的结果最多是1条记录数据， 那么这个时候， 最好用上limit 1, 当查到这条数据后， mysql会立即终止继续查询， 不进行更多的无用查询， 从而提升了效率。 我们

很多时候， 我们预期查询的结果最多是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啊）

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对每日运维的支持。如果你想了解更多相关内容请查看下面相关链接