Mysql如何優(yōu)化查詢速度
對(duì)于需要排序的字段使用索引
當(dāng)查詢結(jié)果需要order by的時(shí)候,可以在order by 的字段加上索引,因?yàn)樗饕呀?jīng)排列好順序了,
所以可以更快的完成排序,而不需要每次對(duì)查詢結(jié)果進(jìn)行排序,耗費(fèi)大量?jī)?nèi)存和時(shí)間。
盡量使用union all 而不是union
除非確實(shí)需要服務(wù)器消除重復(fù)的行,否則一定要使用union all,因此沒(méi)有all關(guān)鍵字,
mysql會(huì)在查詢的時(shí)候給臨時(shí)表加上distinct的關(guān)鍵字,這個(gè)操作的代價(jià)很高。
exists 和 join如何選擇
join
需要多張表進(jìn)行連接,并且需要查詢的字段不是來(lái)自一張表的,比如a.name,b.dept 這種情況需要使用join
exists
要查詢的字段都在a表,但是有一個(gè)很復(fù)雜的條件,可以使用exists子句來(lái)描述后面復(fù)雜的查詢條件
現(xiàn)實(shí)情況中,exists用的比較少,大家都想不起來(lái)用哈哈哈哈
and優(yōu)先級(jí)高于or
比如一個(gè)查詢,需要篩選名字為AAA,并且年齡為20或者21的數(shù)據(jù),
sql語(yǔ)句如下:
select * from tab_a where name = 'AAA' and age = 20 or age = 21;
查詢結(jié)果會(huì)變?yōu)?/p>
name = ‘AAA’ and age = 20的數(shù)據(jù)和
age=21的數(shù)據(jù),查詢結(jié)果錯(cuò)誤。
需要改造sql語(yǔ)句如下:
select * from tab_a where name = 'AAA' and (age=20 or age=21)
盡量不要join超過(guò)三張表
單表索引控制在5個(gè)以內(nèi)
索引越多,b+樹(shù)越大,會(huì)影響插入、刪除效率
組合索引的字段不能超過(guò)5個(gè)
key(a,b,c,d,e)不宜過(guò)多,由于最左匹配原則,如果查詢的列在右邊,那個(gè)查詢的時(shí)候需要補(bǔ)充前幾列的條件
并且,索引的長(zhǎng)度過(guò)長(zhǎng)會(huì)使b+樹(shù)偏大,消耗存儲(chǔ)空間
limit優(yōu)化
limit語(yǔ)法如下:
從第一行開(kāi)始返回,返回前n條數(shù)據(jù)
select * from tab_a order by a limit n
從第m行數(shù)據(jù)開(kāi)始返回,返回后面的n條數(shù)據(jù)
select * from tab_a order by a limit m,n;
例如,如果要查詢tab_a表的第3頁(yè),每頁(yè)10條數(shù)據(jù),可以如下進(jìn)行:
select * from tab_a order by a limit 20,10;
limit相當(dāng)于是一個(gè)指針,在遍歷完前面的數(shù)據(jù)之后,找到需要的數(shù)據(jù),再返回給用戶,如果數(shù)據(jù)量非常大時(shí),例如limit(25000,20),會(huì)掃描全表,limit效率會(huì)變得非常低,
這種情況的調(diào)優(yōu)方式如下:
使用索引列子查詢進(jìn)行調(diào)優(yōu)
調(diào)優(yōu)前:
select film_id,description from film order by title limit 50,5
調(diào)優(yōu)后:
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 1500,5) as lim using(film_id);
調(diào)優(yōu)后,避免了limit指針掃描全表獲取數(shù)據(jù),而是先使用主鍵id進(jìn)行查詢,id在查詢時(shí),
只會(huì)使用b+樹(shù)去訪問(wèn)到數(shù)據(jù),不需要對(duì)整張表進(jìn)行io,
速度更快,并且不需要回表,再將查詢結(jié)果和主表做join,返回?cái)?shù)據(jù)。
避免向數(shù)據(jù)庫(kù)查詢不必要的數(shù)據(jù)
數(shù)據(jù)庫(kù)服務(wù)層會(huì)查詢所有的結(jié)果,形成結(jié)果集,獲取前面n條數(shù)據(jù)后關(guān)閉結(jié)果集,為了避免形成不必要的結(jié)果集,可以使用limit提升速率例如:
select * from tab_a where id = 'sdasafdf676d8' limit 1;
避免使用select *
如果需要不斷的重復(fù)查詢,使用redis進(jìn)行緩存
盡量使用關(guān)聯(lián)代替子查詢
因?yàn)樽硬樵冊(cè)趫?zhí)行的過(guò)程中會(huì)將子查詢結(jié)果放到臨時(shí)表,增加了io,對(duì)內(nèi)存的開(kāi)銷較大,而join可以利用join buffer進(jìn)行快速匹配,運(yùn)行速度較快。
group by, distinct, order by 時(shí)推薦使用索引列
使用自定義變量
什么是自定義變量?
set @one: = 1; set @current_actor: = select actor_id from actor order by last_update desc limit 1 ; set @last_week :=current_date-interval 1 week; -- 使用如下 select count(1) from actor where last_update < @last_week; select * from message where actor_id = @current_actor;
自定義變量使用場(chǎng)景:
優(yōu)化排序
set @rownum:= 0; select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
mysql中寫(xiě)判斷語(yǔ)句的方法總結(jié)
在本篇文章里小編給大家整理的是一篇關(guān)于mysql中寫(xiě)判斷語(yǔ)句的方法總結(jié)內(nèi)容,需要的朋友們可以學(xué)習(xí)參考下。2020-09-09MySQL 8.0 驅(qū)動(dòng)與阿里druid版本兼容問(wèn)題解決
MySQL 8.0 驅(qū)動(dòng)與阿里druid版本不兼容會(huì)導(dǎo)致有報(bào)錯(cuò)問(wèn)題,本文就詳細(xì)的介紹一下解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2021-07-07數(shù)據(jù)庫(kù)查詢優(yōu)化之子查詢優(yōu)化
今天小編就為大家分享一篇關(guān)于數(shù)據(jù)庫(kù)查詢優(yōu)化之子查詢優(yōu)化,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-01-01clickhouse復(fù)雜時(shí)間格式的轉(zhuǎn)換方式
這篇文章主要介紹了clickhouse復(fù)雜時(shí)間格式的轉(zhuǎn)換方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12SQL如何獲取目標(biāo)時(shí)間點(diǎn)或日期的方法實(shí)例
日期獲取在我們?nèi)粘i_(kāi)發(fā)中經(jīng)常會(huì)遇到,這篇文章主要給大家介紹了關(guān)于SQL如何獲取目標(biāo)時(shí)間點(diǎn)或日期的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-10-10windows環(huán)境中mysql忘記root密碼的解決方法詳解
本篇文章是對(duì)windows環(huán)境中mysql忘記root密碼的解決方法進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06