MySQL数据库limit查询优化

2013-8-8 liyangweb.com 李杨 MySQL

熟悉MySQL数据库的朋友都知道MySQL数据库的优化是相当重要的。其中最为常用也是最为需要优化的就是limit。MySQL的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降,本文主要是对MySQL数据库limit查询语句优化的技巧和具体内容的介绍。


首先来看一下下面的实例,同样是取10条数据:


select * from visitor limit 100000,10;


select * from visitor limit 0,10;


这两个limit查询语句,很明显就可以看出不是一个数量级别的查询,所以效率是肯定不一样的。


那么我们该如何针对这种情况进行查询优化呢?网上也很多关于limit的五条优化准则,但大多数都是翻译自MySQL手册,虽然正确但不实用。今天发现一篇文章写了些关于limit优化的,很不错。文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。(测试环境win2033+p4双核 (3GHZ) +4G内存MySQLlimit查询)


1、offset比较小的时候


select * from visitor limit 10,10


多次运行,时间保持在0.0004-0.0005之间


Select * From visitor Where vid >=(  Select vid From visitor Order By vid limit 10,1  ) limit 10


多次运行,时间保持在0.0005-0.0006之间,主要是0.0006


结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。


2、offset大的时候


select * from visitor limit 10000,10


多次运行,时间保持在0.0187左右


Select * From visitor Where vid >=( Select vid From yanxue8_visit Order By vid limit 10000,1  ) limit 10


多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。以后要注意改正自己的limit查询语句,优化一下MySQL了


 

标签: SQL mysql PHP

评论:

mikespothole
2019-01-20 22:39
<a href="http://www.injost.com/adidas-nmd-runner-europe-sneakere">adidas nmd runner europe</a> <a href="http://www.wonjuvally.com/puma-suede-staple-grigio-arancia-kurpesa">puma suede staple grigio arancia</a> <a href="http://www.carrollgrp.com/billig-nike-free-flyknit-5.0-dam%C3%A4nner-blau-sneakerb">billig nike free flyknit 5.0 dam盲nner blau</a> <a href="http://www.nrastogi.com/nike-air-max-ltd-2-weiss-dunkelgrau-silber-hellblau-shoesg">nike air max ltd 2 weiss dunkelgrau silber hellblau</a> <a href="http://www.annersroofing.com/patriots-new-era-knit-hats">patriots new era knit</a> <a href="http://www.orderollie.com/nike-free-run-5.0-vivid-pink-sneakerf">nike free run 5.0 vivid pink</a>
mikespothole http://www.mikespothole.com/
masterelief
2019-01-16 10:46
<a href="http://www.jupitertaxx.com/womens-nike-roshe-yeezy-boost-grey-black-shoesc">womens nike roshe yeezy boost grey black</a> <a href="http://www.juleefashion.com/billig-air-jordan-28-herre-bl%C3%A5-skog">billig air jordan 28 herre bl氓</a> <a href="http://www.nanainchief.com/purple-silver-mens-new-nike-free-shoes-sportsf">purple silver mens new nike free shoes</a> <a href="http://www.offgridac.com/nike-air-max-zero-olive-press-runningd">nike air max zero olive press</a> <a href="http://www.drgnatarajan.com/elite-domenik-hixon-youth-jersey-chicago-bears-87-home-navy-blue-nflg">elite domenik hixon youth jersey chicago bears 87 home navy blue</a> <a href="http://www.bedintime.com/cheap-nike-roshe-high-top-zebra-skoa">cheap nike roshe high top zebra</a>
masterelief http://www.masterelief.com/
freonsystem
2019-01-10 03:15
<a href="http://www.paranoiddevs.com/asics-gel-kinsei-4-lyser%C3%B8d-schuhed">asics gel kinsei 4 lyser酶d</a> <a href="http://www.trafassi.com/air-jordan-7-retro-herren-schuh-width-scarpea">air jordan 7 retro herren schuh width</a> <a href="http://www.charleyscafe.com/nike-air-max-2015-gold-lila-kurpese">nike air max 2015 gold lila</a> <a href="http://www.fikretkuskan.com/nfl-matt-schaub-mens-limited-camo-jersey-8-atlanta-falcons-nike-2018-salute-to-service-nflr">nfl matt schaub mens limited camo jersey 8 atlanta falcons nike 2018 salute to service</a> <a href="http://www.gnstaffing.com/air-jordan-13-retro-dark-lilla-bl%C3%A5-kvinners-sko-flats-kurpese">air jordan 13 retro dark lilla bl氓 kvinners sko flats</a> <a href="http://www.hellovaview.com/femminile-air-max-90-sneakerboot-bianca-schuhee">femminile air max 90 sneakerboot bianca</a>
freonsystem http://www.freonsystem.com/
mwsuperstore
2019-01-06 11:00
<a href="http://www.daddeaze.com/nike-lunarglide-8-kvinders-gul-s%C3%B8lv-kurpesb">nike lunarglide 8 kvinders gul s酶lv</a> <a href="http://www.debracousins.com/mizuno-wave-prophecy-5-running-shoes-mens-black-shoes-for-sa-zapatost">mizuno wave prophecy 5 running shoes mens black shoes for sa</a> <a href="http://www.yankaripark.com/custom-painted-converse-uk-shoesz">custom painted converse uk</a> <a href="http://www.yicailottery.com/nike-blazer-low-light-armory-bl%C3%A5-schuheb">nike blazer low light armory bl氓</a> <a href="http://www.calebcraw.com/nike-mens-marvin-jones-jr-limited-camo-jersey-detroit-lions-nfl-11-rush-realtree-nflu">nike mens marvin jones jr limited camo jersey detroit lions nfl 11 rush realtree</a> <a href="http://www.liquidputter.com/adidas-neo-2013-shoes-skou">adidas neo 2013 shoes</a>
mwsuperstore http://www.mwsuperstore.com/
newstarlines
2019-01-04 03:15
<a href="http://www.getunladylike.com/billig-nike-roshe-yeezy-boost-herre-s%C3%B8lv-skob">billig nike roshe yeezy boost herre s酶lv</a> <a href="http://www.pelumasvagina.com/adidas-superstar-2-light-blau-shoesv">adidas superstar 2 light blau</a> <a href="http://www.ariehonders.com/nike-free-5.0-womens-magenta-runninga">nike free 5.0 womens magenta</a> <a href="http://www.lindaeshop.com/ray-ban-wayfarer-sunglasses-original-sunglassesr">ray ban wayfarer sunglasses original</a> <a href="http://www.ventaasesoria.com/reebok-gl-6000-white-shoesv">reebok gl 6000 white</a> <a href="http://www.stalksdesign.com/adidas-ace-16.1-no-laces-skow">adidas ace 16.1 no laces</a>
newstarlines http://www.newstarlines.com/
tedxuon
2018-12-31 08:26
<a href="http://www.bhldchauhung.com/nike-shox-deliver-r%C3%B8d-and-hvid-house-scarped">nike shox deliver r酶d and hvid house</a> <a href="http://www.meknesmorocco.com/new-blance-996-barn-gr%C3%A5-schuhea">new blance 996 barn gr氓</a> <a href="http://www.abanigeria.com/nike-flynit-air-max-grigio-arancia-kurpesb">nike flynit air max grigio arancia</a> <a href="http://www.webdatingnews.com/nike-air-presto-mid-viola-gituttio-schuhef">nike air presto mid viola gituttio</a> <a href="http://www.cropclassix.com/air-jordan-retro-13-low-blanc-gris-butye">air jordan retro 13 low blanc gris</a> <a href="http://www.chibihentai.com/nike-air-max-zero-femmes-noir-vert-obuvd">nike air max zero femmes noir vert</a>
tedxuon http://www.tedxuon.com/
mylinkstv
2018-12-31 05:31
<a href="http://www.myfraimz.com/air-max-1-master-for-venta-uk-kurpesa">air max 1 master for venta uk</a> <a href="http://www.winnercells.com/black-white-yellow-air-max-1-sneakerb">black white yellow air max 1</a> <a href="http://www.atppest.com/billig-nike-kobe-9-dam%C3%A4nner-shoesg">billig nike kobe 9 dam盲nner</a> <a href="http://www.windriftbar.com/clemson-golf-hat-hats">clemson golf hat</a> <a href="http://www.kidtokidboise.com/nike-kobe-11-flyknit-black-white-sneakerf">nike kobe 11 flyknit black white</a> <a href="http://www.mesmertronic.com/air-jordan-4-retro-vivid-rosa-quartz-obuvd">air jordan 4 retro vivid rosa quartz</a>
[url=http://www.mylinkstv.com/]mylinkstv[/url]
allthatspool
2018-12-30 08:59
<a href="http://www.ictbel.com/asics-asics-gel-kinsei-2-runninge">asics asics gel kinsei 2</a> <a href="http://www.ioioland.com/puma-fenty-original-slippers-zapatosu">puma fenty original slippers</a> <a href="http://www.sesliesenyurt.com/nike-free-4.0-flyknit-crimson-wei%C3%9F-rot-t-chaussuresa">nike free 4.0 flyknit crimson wei脽 rot t</a> <a href="http://www.wethinkjerky.com/garcia-adonis-13-jersey-xl-nflc">garcia adonis 13 jersey xl</a> <a href="http://www.dadees.com/air-max-lunar-1-sale-mens-runningg">air max lunar 1 sale mens</a> <a href="http://www.pickwickcrc.com/women-2012-new-nfl-jerseys-san-francisco-49ers-53-navorro-bowman-red-jerseys-hall-of-fames-50th-patch-nflv">women 2012 new nfl jerseys san francisco 49ers 53 navorro bowman red jerseys hall of fames 50th patch</a>
allthatspool http://www.allthatspool.com/

发表评论:

Powered by emlog 冀ICP备13011830号-1