mysql慢查询优化之从理论和实践说明limit的优点
很多时候，我们预期查询的结果最多是1条记录数据，那么这个时候，最好用上limit1, 当查到这条数据后，mysql会立即终止继续查询，不进行更多的无用查询，从而提升了效率。
我们来实际测试一下，在一个拥有10万的mysql表中，查找lily的分数（假设系统中只有1个lily,而我们预期也只需要这条数据）。为了显示出时间的差别，我并不对表的name字段建索引。
先看看表结构：
mysql>showcreatetabletb_province; +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |tb_province|CREATETABLE`tb_province`( `id`bigint(10)unsignedNOTNULLAUTO_INCREMENT, `name`varchar(32)NOTNULL, `score`int(10)unsignedDEFAULT'0', `x`int(10)unsignedDEFAULT'0', `x1`int(10)unsignedDEFAULT'0', `x2`int(10)unsignedDEFAULT'0', `x3`int(10)unsignedDEFAULT'0', `x4`int(10)unsignedDEFAULT'0', `x5`int(10)unsignedDEFAULT'0', `x6`int(10)unsignedDEFAULT'0', `x7`int(10)unsignedDEFAULT'0', `x8`int(10)unsignedDEFAULT'0', `x9`int(10)unsignedDEFAULT'0', `x10`int(10)unsignedDEFAULT'0', PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=124178DEFAULTCHARSET=latin1| +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec)
我们打开setprofiling=1;的开关，执行mysql语句来对比：
mysql>selectscorefromtb_provincewherename='lily'; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.03sec) mysql>selectscorefromtb_provincewherename='lily'; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.03sec) mysql>selectscorefromtb_provincewherename='lily'; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.04sec) mysql>selectscorefromtb_provincewherename='lily'; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.02sec) mysql>selectscorefromtb_provincewherename='lily'; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.03sec) mysql>selectscorefromtb_provincewherename='lily'limit1; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.00sec) mysql>selectscorefromtb_provincewherename='lily'limit1; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.00sec) mysql>selectscorefromtb_provincewherename='lily'limit1; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.00sec) mysql>selectscorefromtb_provincewherename='lily'limit1; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.01sec) mysql>selectscorefromtb_provincewherename='lily'limit1; +-------+ |score| +-------+ |100| +-------+ 1rowinset(0.00sec)
可见，我们针对是否采用limit1进行了5次对比测试，来看看结果吧：
mysql>showprofiles; +----------+------------+---------------------------------------------------------+ |Query_ID|Duration|Query| +----------+------------+---------------------------------------------------------+ |5|0.02686000|selectscorefromtb_provincewherename='lily'| |6|0.02649050|selectscorefromtb_provincewherename='lily'| |7|0.03413500|selectscorefromtb_provincewherename='lily'| |8|0.02601350|selectscorefromtb_provincewherename='lily'| |9|0.02785775|selectscorefromtb_provincewherename='lily'| |10|0.00042300|selectscorefromtb_provincewherename='lily'limit1| |11|0.00043250|selectscorefromtb_provincewherename='lily'limit1| |12|0.00044350|selectscorefromtb_provincewherename='lily'limit1| |13|0.00053200|selectscorefromtb_provincewherename='lily'limit1| |14|0.00043250|selectscorefromtb_provincewherename='lily'limit1| +----------+------------+---------------------------------------------------------+ 14rowsinset,1warning(0.00sec)
可见，采用limit1后，mysql语句的效率确实提升很多。当表更大时，效率提升会更加明显。
我们已经从理论和实践的脚本都说明了limit的优点，所以，建议是：在可用limit的时候要用limit（当然，如果结果是多个，肯定不能limit1啊）
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。如果你想了解更多相关内容请查看下面相关链接
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。