怎樣分析數(shù)據(jù)庫緩沖池與SQL查詢成本

數(shù)據(jù)庫緩沖池

磁盤 I/O 需要消耗的時間很多，而在內(nèi)存中進(jìn)行操作，效率則會高很多，為了能讓數(shù)據(jù)表或者索引中的數(shù)據(jù)隨時被我們所用，DBMS 會申請占用內(nèi)存來作為數(shù)據(jù)緩沖池，這樣做的好處是可以讓磁盤活動最小化，從而減少與磁盤直接進(jìn)行 I/O 的時間。要知道，這種策略對提升 SQL 語句的查詢性能來說至關(guān)重要。如果索引的數(shù)據(jù)在緩沖池里，那么訪問的成本就會降低很多。

 那么緩沖池如何讀取數(shù)據(jù)呢？

緩沖池管理器會盡量將經(jīng)常使用的數(shù)據(jù)保存起來，在數(shù)據(jù)庫進(jìn)行頁面讀操作的時候，首先會判斷該頁面是否在緩沖池中，如果存在就直接讀取，如果不存在，就會通過內(nèi)存或磁盤將頁面存放到緩沖池中再進(jìn)行讀取。

查看緩沖池大小

如果使用的是 MyISAM 存儲引擎(只緩存索引，不緩存數(shù)據(jù))，對應(yīng)的鍵緩存參數(shù)為 key_buffer_size，可以用它進(jìn)行查看。

如果使用的是 InnoDB 存儲引擎，可以通過查看 innodb_buffer_pool_size 變量來查看緩沖池的大小，命令如下：

mysql> show variables like 'innodb_buffer_pool_size';

mysql> set global innodb_buffer_pool_size = 1073741824;

在 InnoDB 存儲引擎中，可以同時開啟多個緩沖池，查看緩沖池的個數(shù)，使用命令：

mysql> show variables like 'innodb_buffer_pool_instances'; 

查看SQL語句的查詢成本

 一條 SQL 查詢語句在執(zhí)行前需要確定查詢計劃，如果存在多種查詢計劃的話，MySQL 會計算每個查詢計劃所需要的成本，從中選擇成本最小的一個作為最終執(zhí)行的查詢計劃。

如果查看某條 SQL 語句的查詢成本，可以在執(zhí)行完這條 SQL 語句之后，通過查看當(dāng)前會話中的 last_query_cost 變量值來得到當(dāng)前查詢的成本。這個查詢成本對應(yīng)的是 SQL 語句所需要讀取的頁(page)的數(shù)量。

mysql> show status like 'last_query_cost'

example  

mysql> select userid,rating from movierating where userid = 4169;

結(jié)果：2313 rows in set (0.05 sec)

mysql> show status like 'last_query_cost'; 

mysql> select userid,rating from movierating where userid between 4168 and 4175; 

結(jié)果：2643 rows in set (0.01 sec)

mysql> show status like 'last_query_cost';

你能看到頁的數(shù)量是剛才的 1.4 倍，但是查詢的效率并沒有明顯的變化，實際上這兩個 SQL 查詢的時間基本上一樣，就是因為采用了順序讀取的方式將頁面一次性加載到緩沖池中，然后再進(jìn)行查找。雖然頁數(shù)量（last_query_cost）增加了不少，但是通過緩沖池的機(jī)制，并沒有增加多少查詢時間。