MySQL進階實戰6,快取表、檢視、計數器表

哪吒程式設計 發表於 2022-12-04
MySQL

一、快取表和彙總表

有時提升效能最好的方法是在同一張表中儲存衍生的冗餘資料,有時候還需要建立一張完全獨立的彙總表或快取表。

  • 快取表用來儲存那些獲取很簡單,但速度較慢的資料;
  • 彙總表用來儲存使用group by語句聚合查詢的資料;

對於快取表,如果主表使用InnoDB,用MyISAM作為快取表的引擎將會得到更小的索引佔用空間,並且可以做全文檢索。

在使用快取表和彙總表時,必須決定是實時維護資料還是定期重建。哪個更好依賴於應用程式,但是定期重建並不只是節省資源,也可以保持表不會有很多碎片,以及有完全順序組織的索引。

當重建彙總表和快取表時,通常需要保證資料在操作時依然可用,這就需要透過使用影子表來實現,影子表指的是一張在真實表背後建立的表,當完成了建表操作後,可以透過一個原子的重新命名操作切換影子表和原表。

為了提升讀的速度,經常建一些額外索引,增加冗餘列,甚至是建立快取表和彙總表,這些方法會增加寫的負擔媽也需要額外的維護任務,但在設計高效能資料庫時,這些都是常見的技巧,雖然寫操作變慢了,但更顯著地提高了讀的效能。

二、檢視與物化檢視

1、檢視

檢視可以理解為一張表或多張表的與計算,它可以將所需要查詢的結果封裝成一張虛擬表,基於它建立時指定的查詢語句返回的結果集。

查詢者並不知道使用了哪些表、哪些欄位,只是將預編譯好的SQL執行,返回結果集。每次查詢檢視都需要執行查詢語句。

2、物化檢視

為了防止每次都查詢,先將結果集儲存起來,這種有真實資料的檢視,稱為物化檢視。

MySQL並不原生支援物化檢視,可以使用Justin Swanhart的開源工具Flexviews實現。

相對於傳統的臨時表和彙總表,Flexviews可以透過提取對源表的更改,增量地重新計算物化檢視的內容。

三、加快alter table操作的速度

MySQL的alter table 操作的效能對大表來說是個大問題。MySQL執行大部分修改表結構的操作的方法使用新的結構建立一個空表,從舊錶中查出所有資料插入新表,然後刪除舊錶。
這樣操作可能需要花費很長時間,如果記憶體不足而表又很大,而且還有很多索引的情況下更為嚴重。

改善的方法有兩種:

  • 第一種是先在一臺不提供服務的機器上執行alter table操作,然後和提供服務的主表進行切換;
  • 第二種方式是透過影子複製,影子複製的技巧是用要求的表結構建立一張和源表無關的新表,然後透過重新命名和刪表的操作交換兩張表。

四、計數器表

通常建立一張表來儲存使用者的點贊數、網站訪問數等。

create table like_count(num int unsigned not null) engine=InnoDB;

每次點贊都會導致計數器進行更新:

update like_count set num = num + 1;

問題在於,對於任何想要更新這一行的事務來說,這條記錄上都有一個全域性的互斥鎖mutex。這會使這些事務都只能序列執行,要獲得更高的併發更新效能,可以將計數器儲存在多行中,每次隨機選擇一行進行更新。

create table like_count(
	slot tinyint unsigned not null primary key,
	num int unsigned not null
) engine=InnoDB;

預先在這張表中新增10條資料,然後選擇一個隨機的槽slot進行更新:

注意:為了研究之後遇到的問題,後來又插入了一條~

在這裡插入圖片描述

update like_count set num = num + 1 where slot = floor(rand() * 10);

更新了兩行,這是為什麼呢?
在這裡插入圖片描述
在這裡插入圖片描述
select一下,查詢結果,有的時候0條,有的時候1條,有的時候2條,有的時候3條,驚呆了,這麼有趣的事情,我怎麼能放過,讓我們一起一探究竟。
在這裡插入圖片描述
在這裡插入圖片描述
在這裡插入圖片描述
讓我們一起一探究竟:

  • floor() 函式的作用:返回小於等於該值的最大整數;
  • rand()函式的作用:獲得0到1之間的隨機值;

在ORDER BY或GROUP BY子句中使用帶有RAND()值的列可能會產生意想不到的結果,因為對於這兩個子句,RAND()表示式都可以對同一行計算多次,每次返回不同的結果。要從一組行中隨機選擇一個樣本,將ORDER BY RAND()和LIMIT配合使用。

在MySQL的官方手冊裡,針對RAND()的提示大概意思就是,在ORDER BY從句裡面不能使用RAND()函式,因為這樣會導致資料列被多次掃描。

這就完了?