首頁 > mysql教程 閱讀:0更新時間:2020-12-13 05:41:31

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

文章目錄

  • 6.0 前言
  • 6.1 為什么查詢速度會慢
  • 6.2 慢查詢基礎:優化數據訪問
    • 1. 是否向數據庫請求了不需要的數據
    • 2. mysql是否在掃描額外的記錄
      • 響應時間
      • 掃描的行數和返回的行數
      • 掃描的行數和訪問類型
  • 6.3 重構查詢的方式
    • 1. 一個復雜查詢 or 多個簡單查詢?
  • 2. 切分查詢
    • 3. 分解關聯查詢
  • 6.4 查詢執行的基礎
    • 3. 針對第3步:查詢優化處理
      • 3.1 查詢優化器
      • 3.2 MySQL如何執行關聯查詢
      • 3.3 執行計劃
      • 3.4 關聯查詢優化器
  • 6.5 MySQL查詢優化的局限性
    • 1.關聯子查詢
    • 2. union的限制
    • 3. 索引合并優化
    • 4. max() 和 min()優化
    • 5. 在同一個表上查詢和更新
  • 6.6 查詢優化器的提示(hint)
  • 6.7 優化特定類型的查詢
    • 1. 優化count()查詢
      • count()函數真正的作用?
      • MyISAM神話?
      • 簡單的優化
      • 使用近似值
      • 更復雜的優化
    • 2.優化關聯查詢
    • 3.優化GROUP BY 和 DISTINCT
      • 優化group by with rollup
    • 4.優化limit分頁
    • 5.優化SQL_CALC_FOUND_ROWS
    • 6.優化UNION查詢
    • 7.靜態查詢查詢
    • 8.使用用戶自定義變量
      • 優化排名語句
      • 避免重復查詢剛更新的數據
      • 統計更新和插入的數量
      • 確定取值的順序
      • 編寫偷懶的union
      • 其他用處
  • 6.8 案例學習
    • 1. 構建一個隊列表
    • 2. 計算兩點之間的距離
    • 3. 使用用戶自定義函數
  • 6.9 總結

6.0 前言

查詢優化、索引優化、庫表結構優化需要齊頭并進,一個不落!

chapter 5 學習了如何建立最好的索引,但是如果查詢寫得很糟糕,索引再好,也沒辦法實現高性能!

6.1 為什么查詢速度會慢

真正重要的是響應時間。

查詢可以看成一個任務,它由一系列子任務組成,每個子任務都會消耗一定的時間。如果要優化查詢,本質是優化子任務,或消除子任務,或減少子任務的執行次數,或讓子任務執行更快。

6.2 慢查詢基礎:優化數據訪問

查詢性能低下最基本的原因:訪問數據太多。

大部分性能低下的查詢都可以通過減少訪問數據進行優化。對于低效查詢,我們發現可以通過以下2步分析:

  1. 確認應用程序是否在檢索大量超過需要的數據(訪問了太多的行/列);
  2. 確認MySQL服務器層是否在分析超過需要的數據行。

1. 是否向數據庫請求了不需要的數據

有時候查詢會請求超過實際的數據,這些造成的后果有,MySQL服務器帶來額外的負擔、增加網絡開銷、消耗應用服務器的CPU和內存資源。

典型案例:

  • 查詢不需要的記錄:要記得在查詢后面加limit;
  • 多表關聯時返回全部列:盡量別寫select *;
  • 重復查詢相同的數據:初次查詢將這個數據緩存,需要的時候從緩存取出。

2. MySQL是否在掃描額外的記錄

在確定查詢返回需要的數據后,接下來應該看看查詢為了返回結果是否掃描了過多的數據,對于MySQL,最簡單的衡量查詢開銷的三個指標如下:

  • 響應時間
  • 掃描的行數
  • 返回的行數

沒有哪個指標能夠完美的平衡查詢的開銷,但是大致反映了查詢需要訪問多少數據,推算出查詢運行的時間。檢查慢日志是找出掃描行過多的方法。

響應時間

響應時間=服務時間+排隊時間

服務時間:指數據庫處理這個查詢真正花了多長時間。

排隊時間:服務器因為等待某些資源而沒有真正執行查詢的時間,可能是等I/O操作完成,也可能是等待鎖。
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

掃描的行數和返回的行數

查看掃描行數非常有幫助,一定程度上能說明效率!explainrows值。

理想情況下,掃描的行數和返回的行數應該相同的,但是實際卻難以達到,例如像關聯查詢時,服務器必須要掃描多行才能生成結果集中的一行。

詳見:【數據庫筆記】MySQL Explain解析

掃描的行數和訪問類型

EXPLAIN語句中的TYPE列反應了訪問類型,訪問類型有很多種,從全表掃描到索引掃描、范圍掃描、唯一索引查詢、常數引用等等。速度是從慢到快,掃描的行數也是從大到小。如果查詢沒有辦法找到合適的訪問類型,那么解決的最好辦法就是增加一個合適的索引。索引讓MySQL以最高效、掃描行最少的方式找到需要的記錄。

explain select * from sakila.film_actor where film_id=1\G

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
該查詢返回10行數據,從explain的結果可以看到,MySQL在索引idx_fk_film_id上使用了ref訪問類型來執行查詢

一般的,MySQL可以從三種方式應用WHERE條件,從好到壞依次為:

  • 在索引中使用where條件來過濾不匹配的記錄,這是存儲引擎層完成的;
  • 使用索引覆蓋查詢(在Extra列中出現了Using inex 來返回記錄)來返回記錄,直接從索引中過濾不需要的記錄并返回命中的結果;
  • 從數據表中返回數據,然后過濾不滿足條件的記錄(在Extra列中出現Using Where)。這在MySQL服務層完成,無須再回表查詢記錄。
select actor_id,count(*) 
from sakila.film_actor 
group by actor_id

這個查詢需要掃描大量數據,卻只返回10行。

如果發現查詢需要掃描大量的數據但是只返回少數的行,那么通??梢試L試下面的技巧優化它:

  • 使用索引覆蓋掃描,把所有需要用的列都放到索引中,這樣存儲引擎無需回表獲取對應的行就可以返回了;
  • 改變庫表結構。例如使用單獨的匯總表;
  • 重寫這個復雜的查詢,讓MySQL優化器能夠以更優化的方式執行這個查詢。

6.3 重構查詢的方式

1. 一個復雜查詢 or 多個簡單查詢?

有時候,將一個大查詢分解為多個小查詢是很多必要的,關鍵在于這么做會不會減少工作量。

不過在應用設計時,如果一個查詢能夠勝任多個獨立查詢,分解它是不明智!比如,對一個表做10次獨立的查詢來返回10行數據,每個查詢只返回一條結果,需要查詢10次。

2. 切分查詢

有時候,將一個大查詢需要“分而治之”,將它分解為多個小查詢。
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

delete from messages where created < date_sub(now(),interval 3 month);

可以用類似如下的方式完成:

rows_affected=0
do {
	rows_affected=do_query(
		'delete from messages where created < date_sub(now(),interval 3 month) limit 10000')
} while rows_affected>0

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

3. 分解關聯查詢

很多高性能應用會對關聯查詢進行分解。

簡單地說,可以對每一個表進行一次單表查詢,然后將結果在應用程序中進行關聯。

select * from tag
	join tag_post on tag_post.tag_id=tag.id
	join post on tag_post.post_id=post.id
where tag.tag='mysql'

可以分解成以下查詢來代替:

select * from tag where tag='mysql';
select * from tag_post where tag_id=1234;
select * from post where post.id in (123,456,567,9098,8904);

到底為什么要這樣做?咋一看,這樣做并沒有什么好處,原本一條查詢,這里卻變成了多條查詢,返回結果又是一模一樣。

事實上,用分解關聯查詢的方式重構查詢具有如下優勢:

  1. 讓緩存的效率更高。 許多應用程序可以方便地緩存單表查詢對應的結果對象。另外對于MySQL的查詢緩存來說,如果關聯中的某個表發生了變化,那么就無法使用查詢緩存了,而拆分后,如果某個表很少改變,那么基于該表的查詢就可以重復利用查詢緩存結果了。

  2. 將查詢分解后,執行單個查詢可以減少鎖的競爭。

  3. 在應用層做關聯,可以更容易對數據庫進行拆分,更容易做到高性能和可擴展。

  4. 查詢本身效率也可能會有所提升

  5. 可以減少冗余記錄的查詢。

  6. 更進一步,這樣做相當于在應用中實現了哈希關聯,而不是使用MySQL的嵌套環關聯,某些場景哈希關聯的效率更高很多。

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

6.4 查詢執行的基礎

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

3. 針對第3步:查詢優化處理

3.1 查詢優化器

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

explain select film.flim_id,film_actor.actor_id
from sakila.film
inner join sakila.film_ator using(film_id)
where film.flim_id=1;

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

3.2 MySQL如何執行關聯查詢

MySQL中“關聯”一詞包含的意義比一般意義上理解的更廣泛。總的來說,MySQL認為任何一個查詢都是一次“關聯”——并不僅僅是一個查詢需要兩個表匹配才叫關聯!。

MySQL中,每個查詢,每個片段(子查詢、單表select)都可能是關聯。

select * from t1 inner join t2 on t1.col3=t2.col3;
--等價于
select * from t1 inner join t2 using(col3);

MySQL的嵌套循環關聯操作

select tbl1.col1,tbl2.col2
from tbl1 inner join tbl2 using(col3)
where tbl1.col1 in (5,6);

假設MySQL按照查詢中的表順序進行關聯操作,則可以用偽代碼表示MySQL如何完成這個查詢:

outer_iter=iterator over tbl1 where col1 in (5,6)
outer_row=outer_iter.next
while outer_row
	inner_iter=iterator over tbl2 where col3=outer_row.col3
	inner_row=inner_iter.next
	while inner_row
		output [outer_row.col1,inner_row.col2]
		inner_row=inner_iter.next
	end
	outer_row=outer_iter.next
end

該執行計劃對于單表查詢和多表關聯查詢均適用,單表查詢只要完成上面的外層outer操作。

同理,我們把上面的查詢修改成外連接:

select tbl1.col1,tbl2.col2
from tbl1 left join tbl2 using(col3)
where tbl1.col1 in (5,6);

對應的偽代碼:

outer_iter=iterator over tbl1 where col1 in (5,6)
outer_row=outer_iter.next
while outer_row
	inner_iter=iterator over tbl2 where col3=outer_row.col3
	inner_row=inner_iter.next
	if inner_row # 唯一不同之處,加了if...else...end
		while inner_row
			output [outer_row.col1,inner_row.col2]
			inner_row=inner_iter.next
		end
	else # 唯一不同之處
		output [outer_row.col1,NULL]
	end # 唯一不同之處
	outer_row=outer_iter.next
end

可視化查詢執行計劃,繪制“泳道圖”,從左至右,從上至下地看圖:
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

從本質上說,MySQL對所有類型的查詢都以同樣的方式運行。有一個例外:全外連接,無法通過嵌套循環和回溯的方式完成,因為會發現關聯表中沒有找到任何匹配行的時候,可能是因為關聯是恰好從一個沒有任何匹配的表開始的。
詳見:【數據庫筆記】MySQL&Oracle JOIN方法圖碼總結

3.3 執行計劃

和很多其他關系數據庫不同,MySQL不會生成查詢字節碼來執行查詢,MySQL生成查詢的一顆指令樹,然后通過存儲引擎執行完成這顆指令樹并返回結果。

任何多表查詢都可以使用一顆樹表示,MySQL總是從一個表開始嵌套循環、回溯完成所有表關聯。因此,MySQL的執行計劃總是如下(一顆左側深度優先的樹)。
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

3.4 關聯查詢優化器

MySQL優化器最重要的一部分:關聯查詢優化,他決定了多個表關聯時的順序。

通常,多表關聯可以有多種不同的關聯順序,來獲得相同的執行結果。如下例子:

select film.film_id,film_title,film.release_year,actor.actor_id,actor.first_name,actor.last_name
from sakila.film
inner join sakila.film_actor using(film_id)
inner join sakila.actor using(actor_id);

容易看出,可以通過一些不同的執行計劃來完成上面的查詢。比如說,MySQL可以從film表開始,用film_actor表的索引film_id來找對應的actor_id值,再根據actor表的主鍵找到對應記錄。

explain查看MySQL如何執行這個查詢:
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
顯然,它的順序和剛計劃的不一樣,MySQL先從actor表開始,那么MySQL為啥這么做?我們可以用straight_join關鍵字,令MySQL按我們之前的順序執行:

explain select straight_join film.film_id,film_title,film.release_year,actor.actor_id,actor.first_name,actor.last_name
from sakila.film
inner join sakila.film_actor using(film_id)
inner join sakila.actor using(actor_id)\G

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
結果顯而易見:MySQL為啥倒轉順序呢?因為倒轉之后,第一個關聯表只要掃描很少的行數。第二個和第三個查詢都是根據索引查,速度都賊快。

為了驗證優化器的選擇是不是正確,我們可以單獨執行這兩個查詢,并看看對應的last_query_cost

show status like 'last_query_cost';

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

一般來說,人的判斷沒有優化器的準確。

如果有n個表要進行關聯,那么就要檢查n!(n的階乘)種關聯順序——所有可能的執行計劃的“搜索空間”,“搜索空間”增速非???。
此時,優化器選擇是有“貪婪”搜索的方式,找到“最優的關聯順序”

6.5 MySQL查詢優化的局限性

1.關聯子查詢

最糟糕的一類查詢:where條件中包含in()的子查詢語句。

實例:從sakila數據庫中,找到actor_id=1的演員參演過的所有影片信息。

很自然會這么寫:

select * from sakila.film
where film_id in (
select film_id from sakila.film_actor where actor_id=1);

因為MySQL對in()列表的選項有專門的優化策略,一般會認為MySQL會先執行子查詢,返回所有包含actor_id=1film_id

--select group_concat(film_id) from sakila.film_actor where actor_id=1;
--Result:1,23,25,106,140,166,277,361,438,499,506,605
select * from sakila.film
where film_id in (
1,23,25,106,140,166,277,361,438,499,506,605);

Unfortunately,u think too much!

MySQL不是這樣搞的,它會將相關的外層表壓到子查詢中,因為MySQL覺得這樣會效率更高:

select * from sakila.film
where exists (
select * from sakila.film_actor where actor_id=1
and film_actor.film_id=film.film_id);

這時,子查詢需要根據film_id來關聯外層表film,所以MySQL沒法先執行這個子查詢。不信,explain一下看看:

explain select * from sakila.film
where exists (
select * from sakila.film_actor where actor_id=1
and film_actor.film_id=film.film_id);

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
重寫查詢,方法1——使用內連接:

select film.* from sakila.film
	inner join sakila.film_actor using(film_id)
where actor_id=1);

重寫查詢,方法2——使用GROUP_CONCAT()[有時比內連接更快]:

select * from sakila.film
where film_id in (
	select group_concat(
		select film_id from sakila.film_actor where actor_id=1)
	from dual
);

詳見:mysql之group_concat函數詳解

重寫查詢,方法3——使用exists()
in() 加子查詢,性能經常很糟糕,索引通常建議用exists()來改寫查詢,得到更好的效率。

select * from sakila.film
where exists (
	select * from sakila.film_actor where actor_id=1
	and film_actor.film_id=film.film_id);

如何用好關聯子查詢?

explain select film_id,language_id from sakila.film
where not exists(
	select * from sakila.film_actor
	where film_actor.film_id=film.film_id
	)\G

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
一般用左外連接改寫,但執行計劃基本不會變:

explain select film.film_id,film.language_id from sakila.film
left join sakila.film_actor using(film_id)
where film_actor.film_id is not null\G

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

QPS(每秒Query量)
QPS = Questions(or Queries) / seconds

show global status like 'Question%';
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

通過上面兩個例子,我們可以得到:

  • 要嘗試才知道是關聯查詢更快,還是子查詢更快;
  • 測試來驗證對子查詢的執行計劃(explain)和響應時間(QPS)的假設。

2. union的限制

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

3. 索引合并優化

詳見chapter 5:【數據庫筆記】高性能MySQL:chapter 5 創建高性能的索引

4. max() 和 min()優化

MySQL在max()min()優化上做得不好。

select min(actor_id)
from sakila.actor
where first_name='mary';

因為在first_name沒有索引,所以MySQL會先全表掃面一次。如果MySQL能進行主鍵掃描,理論上當MySQL讀到第一個滿足條件的記錄就是我們需要的最小值,因為主鍵是嚴格按照actor_id字段的大小順序排列。

但是,MySQL現在要先全表掃描一次。我們可以通過show status的全表掃描計數器來驗證。

詳見:MySQL運行狀態show status中文詳解

優化方法——曲線救國,移除min()

select actor_id
from sakila.actor use index(primary)
where first_name='mary' limit 1;

--最大值
select actor_id
from sakila.actor use index(primary)
where first_name='mary' 
order by actor_id desc
limit 1;

這個策略能讓MySQL掃描盡可能少的行。其實是告訴MySQL如何去獲取我們的數據,通過sql確實無法一眼看出我們要的是min().
有時為了更高的性能,不得不放棄一些原則。

5. 在同一個表上查詢和更新

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

6.6 查詢優化器的提示(hint)

詳見:mysql查詢優化之三:查詢優化器提示(hint)

6.7 優化特定類型的查詢

1. 優化count()查詢

count()函數真正的作用?

  • 統計某個列值的數量(不計null
  • 統計行數 (確保count()括號內的表達式不為null時),或使用count(*)

MyISAM神話?

MyISAM的count()函數總是非???,然而這是有前提的!即只有沒有任何where條件的count(*)才快。 如果帶where,那么MyISAM沒有任何優勢了,就會和其他引擎一樣,或者更慢,受很多因素影響。

如果MySQL知道count(col)col不可能含有null,那么MySQL內部會把count(col)優化為count(*)。

簡單的優化

優化前:

select count(*) from world.city
where ID>5;

優化后(取反):

select (select count(*) from world.city)-count(*)
from world.city
where ID<=5;

這樣做可以大大減少需要掃描的行數,是因為在查詢優化階段會將其中的子查詢直接當一個常數處理。

不信就explain一下看看:
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
優化前:
在一個查詢中統計同一列的不同值的數量,此時不能用OR語句,如此操作無法區分不同顏色的商品數量,也不能在where中指定color。

select count(color=''blue' or color='red') 
from items;

優化后:

select  sum(if(color='blue',1,0)) as blue,
		sum(if(color='red',1,0)) as red
from items;

也可以用count,將滿條件設置為真,不滿足的設置成null

select  count(color='blue' or null) as blue,
		count(color='red' or null) as red
from items;

使用近似值

有時不要求完全精確的count值,也可以用近似值代替。

explain出來的優化器估算的行數就是一個不錯的近似值,執行explain并不需要真正地去執行查詢,所以成本很低。

更復雜的優化

count()需要掃描大量的行(意味著要訪問大量的數據)才能獲得精確的結果,因此是很難優化的。

除了前面的方法,MySQL還能做的只有索引覆蓋掃描。

再進一步,就要考慮修改應用的架構。

2.優化關聯查詢

  • 確保on或者using子句中的列上有索引。 在創建索引的時候就要考慮到關聯的順序:當表A和表B用列C關聯的時候,如果優化器的關聯順序是B、Aselect a.id,b.name from b left join a on b.id=a.id,那么就不需要在B的對應列上建立索引。沒用到的索引只會帶來額外負擔。除非有其他理由,否則只要在關聯順序中的第二個表上建立相應索引。
  • 確保任何的group by和order by中的表達式只涉及到一個表中的列。

3.優化GROUP BY 和 DISTINCT

最有效的優化方法:使用索引。

無法使用索引時,GROUP BY 使用兩種策略:

  • 使用臨時表
  • 文件排序做分組

可以通過使用提示SQL_BIG_RESULTSQL_SMALL_RESULT來讓優化器按照你希望的方式運行。

優化前:

select  actor.first_name,
		actor.last_name,
		count(*)
from sakila.film_actor
	inner join sakila.actor using(actor_id)
group by actor.first_name,actor.last_name;

優化后:

如果需要對關聯查詢做分組group by,并且是按照查找表中的某個列進行分組,那么 通常采用查找表的標識列 分組的效率會比其他列更高:

select  actor.first_name,
		actor.last_name,
		count(*)
from sakila.film_actor
	inner join sakila.actor using(actor_id)
group by actor.actor_id;

在分組查詢的select中,直接使用非分組列通常不是什么好主意,因此上述語句還能用min(),max() 改寫,這樣做需要列不在意這個值是啥,或者值唯一:

select  min(actor.first_name),
		max(actor.last_name),
		count(*)
from sakila.film_actor
	inner join sakila.actor using(actor_id)
group by actor.actor_id;

當使用group by的時候,結果集會自動按照分組的字段進行排序。如果不關心結果集的順序,而這種自動排序又導致了需要文件排序,則可以使用order by null,讓MySQL不再進行文件排序!

優化group by with rollup

分組查詢的另一個變種:對分組結果再做一次超級聚合。

可以使用with rollup子句來實現這種邏輯,但可能會不夠優化。

可以通過explain來觀察執行計劃,特別注意分組是否是通過文件排序或臨時表實現的。然后,再去掉with rollup來看執行計劃是不是相同。

很多時候,如果可以用超級聚合是更好的。

4.優化limit分頁

在偏移量大的時候,如limit 1000,20,需要查詢10020條記錄,然后只返回最后20條…其實是offset的問題,導致掃描大量不用的數據又給拋棄掉。

顯然,這樣的代價非常高…優化手段:

  • 限制分頁數量
  • 優化大偏移量的性能

最簡單的方法:盡可能用索引覆蓋掃描,而不是查詢所有列.

優化前:

select film_id,description
from sakila.film
order by title
limit 50,5;

優化后:

select film.film_id,film.description
from 	sakila.film
	inner join
		(select film_id 
	     from sakila.film
	     order by title 
	     limit 50,5) as lim
	using(film_id)

這里“延遲關聯”將大大提升查詢效率,它讓MySQL掃描盡可能少的頁面,獲取需要訪問的記錄后再根據關聯列回原表查詢需要的所有列。

或者預先知道了位置,且位置上有索引:

select film_id,description
from sakila.film
where position between 50 and 54
order by position;

5.優化SQL_CALC_FOUND_ROWS

limit語句中加上SQL_CALC_FOUND_ROWS提示就可以獲得去掉limit后滿足條件的行數,可以作為分頁的總數。

這個提示的代價非常高??!
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

6.優化UNION查詢

除非要去重,否則一定用union all。

7.靜態查詢查詢

pt-query-advisor能夠給出所有可能潛在問題和建議,像健康檢查。

8.使用用戶自定義變量

非常強大!

可以用setselect來定義它們。

set @one:=1;
set @min_actor:=(select min(actor_id) from sakila.actor);
set @last_week:=current_date-interval 1 week;

然后可以再任何使用表達式的地方使用它們:

select ... where col<=@last_week;

不能使用用戶自定義變量的情況:
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

優化排名語句

eg1.實現行號的功能:

set @rownum:=0;
select  actor_id,
		@rownum:=@rownum+1 as rownum
from sakila.actor limit 3;

eg2.更復雜的情況: 編寫一個查詢獲取演過最多電影的前10名演員,然后根據他們的出演次數做一個排名,如果出演的數量一樣則排名相同。

select 	actor_id,
		count(*) as cnt
from sakila.film_actor
group by actor_id
order by cnt desc
limit 10;

再把排名加上去:

set @curr_cnt:=0,@prev_cnt:=0,@rank:=0;

select 	actor_id,
		@curr_cnt:=count(*) as cnt,
		@rank	 :=if(@prev_cnt<>@curr_cnt,@rank+1,@rank) as rank,
		@prev_cnt:=@curr_cnt as dummy
from (
	select  actor_id,
			count(*) as cnt
	from sakila.film_actor
	group by actor_id
	order by cnt desc
	limit 10
) as der;

避免重復查詢剛更新的數據

客戶希望更高效地更新一個時間戳,同時希望查詢當前記錄中存放的時間戳是什么:

update t1 set lastupdated=now() where id=1;
select lastupdatedfrom t1 where id=1;

用變量寫:

update t1 set lastupdated=now() where id=1 and @now:=now();
select @now;

看上去要2次查詢,其實第二行無需訪問任何表,會快很多?。?!

統計更新和插入的數量

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

確定取值的順序

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

編寫偷懶的union

假設需要編寫一個UNION查詢,其第一個子查詢作為分支條件先執行,如果找到了匹配的行,則跳過第二個分支。例如先在一個頻繁訪問的表查找熱數據,找不到再去另外一個較少訪問的表查找冷數據。
案例:先在一個頻繁訪問的表中查找“熱”數據,找不到再去另一個較少訪問的表找“冷”數據。

select greatest(@found:=-1,id) as id,'users' as which_tbl
from users where id =1
union all
	select id,'users_archived' 
	from users_archived where id=1 and @found is null
union all
	select 1,'reset' from dual where (@found:=null) is not null;

其他用處

通過一些實踐,可以了解所有用戶自定義變量能夠做的有趣的事情,例如下面這些用法:

  • 查詢運行時計算總數和平均值
  • 模擬GROUP語句中的函數FIRST()LAST()
  • 對大量數據做一些數據計算
  • 計算一個大表的MD5散列值
  • 編寫一個樣本處理函數
  • 模擬讀/寫游標
  • SHOW語句的WHERE子句中加入變量值

6.8 案例學習

1. 構建一個隊列表

2. 計算兩點之間的距離

案例:查找某個點附近所有可以出租的房子

create table locations(
	id   int not null primary,
	name varchar(30),
	lat  float not null,
	lon  float not null
);
insert into locations(name,lat,lon)
	values('charlottesville,virginia',38.03,-78.48),
		  ('chicago,illinois',41.85,-87.65),
		  ('washington,DC',38.89,-77.04);

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

select * from locations
where lat between 38.03-degrees(0.0253) and 38.03+degrees(0.0253)
and lon between -78.48-degrees(0.0253) and -78.48+degrees(0.0253)

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

select  floor(38.03-degrees(0.0253)) as lat_lb,
		ceiling(38.03+degrees(0.0253)) as lat_ub,
		floor(-78.48-degrees(0.0253)) as lon_lb,
		ceiling(-78.48+degrees(0.0253)) as lon_ub;

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

select * from locations
where lat between 38.03-degrees(0.0253) and 38.03+degrees(0.0253)
and lon between -78.48-degrees(0.0253) and -78.48+degrees(0.0253)
and lat_floor in(36,37,38,39,40) and lon_floor in (-80,-79,-78,-77);

【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引
【數據庫筆記】高性能MySQL:chapter 6 創建高性能的索引

3. 使用用戶自定義函數

6.9 總結

優化:不做、少做、快速的做。

beylze編程學院,一個分享編程知識和seo優化知識的網站。跟著beylze一起學習,每天都有進步。

通俗易懂,深入淺出,一篇文章只講一個知識點。

文章不深奧,不需要鉆研,在公交、在地鐵、在廁所都可以閱讀,隨時隨地漲姿勢。

文章不涉及代碼,不燒腦細胞,人人都可以學習。

當你決定關注beylze(公眾號:beylze),你已然超越了90%的其他從業者!

相關文章

国产亚洲欧美日韩