MySQL 內(nèi)部處處皆緩存，等什么時(shí)候看了MySQL的源碼，再來(lái)詳細(xì)的分析緩存的是如何利用的。這部分主要將各種顯式的緩存優(yōu)化：

1. 查詢(xún)緩存優(yōu)化
2. 結(jié)果集緩存
3. 排序緩存
4. join 連接緩存
5. 表緩存Cache 與表結(jié)構(gòu)定義緩存Cache
6. 表掃描緩存buffer
7. MyISAM索引緩存buffer
8. 日志緩存
9. 預(yù)讀機(jī)制
10. 延遲表與臨時(shí)表
    

1、查詢(xún)緩存優(yōu)化

查詢(xún)緩存不僅將查詢(xún)語(yǔ)句結(jié)構(gòu)緩存起來(lái)，還將查詢(xún)結(jié)果緩存起來(lái)。一段時(shí)間內(nèi)，如果是同樣的SQL,則直接從緩存中讀取結(jié)果，提高查找數(shù)據(jù)的效率。但當(dāng)緩存中的數(shù)據(jù)與硬盤(pán)中的數(shù)據(jù)不一致時(shí)，緩存就會(huì)失效。

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name        | Value  |
+------------------------------+---------+
| have_query_cache       | YES   |
| query_cache_limit      | 1048576 |
| query_cache_min_res_unit   | 4096  |
| query_cache_size       | 1048576 |
| query_cache_type       | OFF   |
| query_cache_wlock_invalidate | OFF   |
+------------------------------+---------+

have_query_cache 是否支持查詢(xún)緩存。

query_cache_limit 如果某條select語(yǔ)句的結(jié)果集大小超過(guò)了querycachelimit的值時(shí)，這個(gè)結(jié)果集將不會(huì)被添加到查詢(xún)緩存。

query_cache_min_res_unit 查詢(xún)緩存是以塊來(lái)申請(qǐng)內(nèi)存空間的，每次申請(qǐng)的塊大小為設(shè)定值。4K 是非常一個(gè)合理的值，不必修改。

query_cache_size 查詢(xún)緩存的大小。

query_cache_type 查詢(xún)緩存的類(lèi)型，值有 0（OFF）、1（ON）、2（DEMOND）。OFF表示查詢(xún)緩存是關(guān)閉的。ON 表示查詢(xún)總是先到查詢(xún)緩存中去查找，除非在select 語(yǔ)句中包含sql_no_cache選項(xiàng)。 DEMOND 表示不適用緩存，除非在select 語(yǔ)句中包含sql_cache選項(xiàng)。

query_cache_wlock_invalidate 該參數(shù)用于設(shè)置行級(jí)排它鎖與查詢(xún)緩存之間的關(guān)系，默認(rèn)為為0（OFF）,表示施加行級(jí)排它所的同時(shí)，該表的所有查詢(xún)緩存依然有效。如果設(shè)置為1（ON）,表示事假行級(jí)排它鎖的同時(shí)，該表的所有查詢(xún)緩存失效。

查看查詢(xún)緩存的命中率

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name      | Value  |
+-------------------------+---------+
| Qcache_free_blocks   | 1    |
| Qcache_free_memory   | 1031360 |
| Qcache_hits       | 0    |
| Qcache_inserts     | 0    |
| Qcache_lowmem_prunes  | 0    |
| Qcache_not_cached    | 0    |
| Qcache_queries_in_cache | 0    |
| Qcache_total_blocks   | 1    |
+-------------------------+---------+

查看當(dāng)前緩存的狀態(tài)信息：

Qcache_free_blocks

表示查詢(xún)緩存中處以重現(xiàn)狀態(tài)的內(nèi)存塊數(shù)（碎片數(shù)量）。如果Qcache_free_blocks 的值較大，則意味著查詢(xún)緩存中碎片比較多，表明查詢(xún)結(jié)果集較小，此時(shí)可以減小query_cache_min_res_unit的值。使用flush query cache 會(huì)對(duì)緩存中的若干個(gè)碎片進(jìn)行整理，從而得到一個(gè)比較大的空閑塊。緩存碎片率 = Qcache_free_blocks/ Qcache_total_blocks * 100%

Qcache_free_memory

表示當(dāng)前MySQL服務(wù)實(shí)例的查詢(xún)緩存還有多少可用內(nèi)存。

Qcache_hits

表示使用查詢(xún)緩存的次數(shù)，該值會(huì)依次增加。如果Qcache_hits比較大，則說(shuō)明查詢(xún)緩存使用的非常頻繁，此時(shí)需要增加查詢(xún)緩存。

Qcache_inserts

表示查詢(xún)緩存中此前總共緩存過(guò)多少條select 語(yǔ)句的結(jié)果集。

Qcache_lowmen_prunes

表示因?yàn)椴樵?xún)緩存已滿(mǎn)而溢出，導(dǎo)致MySQL刪除的查詢(xún)結(jié)果個(gè)數(shù)。如果該值比較大，則表明查詢(xún)緩存過(guò)小。

Qcache_not_cached

表示沒(méi)有進(jìn)入查詢(xún)緩存的select個(gè)數(shù)

Qcache_queryies_in_cache

表示查詢(xún)緩存中緩存這多少條select語(yǔ)句的結(jié)果集

Qcache_total_blocks

查詢(xún)緩存的總個(gè)數(shù)

緩存命中率的計(jì)算方式： 查詢(xún)緩存的命中率 = Qcache_hits / Com_select * 100%

其中Com_select為當(dāng)前MySQL實(shí)例執(zhí)行select 語(yǔ)句的個(gè)數(shù)。一般情況下Com_select = Qcache_insert + Qcache_not_cached。而 Qcache_not_cached中包含有數(shù)據(jù)頻繁變化而導(dǎo)致查詢(xún)緩存失效的select語(yǔ)句，因此命中率一般來(lái)說(shuō)較低。如果拋開(kāi)失效的因素，查詢(xún)緩存的命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts) 如果使用這種公式計(jì)算出查詢(xún)緩存的命中率比較高的話(huà)，這就意味著大部分select語(yǔ)句都命中了查詢(xún)緩存。

通過(guò)如下命令查看當(dāng)前系統(tǒng)一共執(zhí)行了多少條select語(yǔ)句

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select  | 1   |
+---------------+-------+

2、結(jié)果集緩存

結(jié)果集緩存是會(huì)話(huà)緩存，MySQL客戶(hù)機(jī)成功連接服務(wù)器之后。MySQL服務(wù)器會(huì)為每個(gè)MySQL客戶(hù)機(jī)保留結(jié)果集緩存。緩存MySQL客戶(hù)機(jī)連接線(xiàn)程的連接信息以及緩存返回MySQL客戶(hù)機(jī)的結(jié)果集信息,當(dāng)MySQL客戶(hù)機(jī)向服務(wù)器發(fā)送select 語(yǔ)句時(shí)，MySQL將select語(yǔ)句的執(zhí)行結(jié)果暫存在結(jié)果集緩存中。結(jié)果集的緩存大小由 net_buffer_length 參數(shù)值定義:

mysql> show variables like 'net_buffer_length';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+

如果結(jié)果集超過(guò)net_buffer_length設(shè)置的值，則自動(dòng)擴(kuò)充容量，但不超過(guò)：max_allowd_packet的閾限值：

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name   | Value  |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

3、排序緩存

MySQL 常用的有InnoDB 和MyISAM 兩種數(shù)據(jù)存儲(chǔ)引擎。因此在優(yōu)化的時(shí)候，每種引擎都會(huì)采用適合自己引擎的優(yōu)化方法。關(guān)于MySQL 與InnoDB 表結(jié)構(gòu)文件和數(shù)據(jù)日志文件的不同，可以先看本人的博客MySQL 日志系統(tǒng),以便對(duì)這些基礎(chǔ)概念有足夠的了解，接下來(lái)看引擎的優(yōu)化的方法才能如魚(yú)得水，不覺(jué)得枯燥。

1、普通排序緩存

排序緩存是會(huì)話(huà)緩存, 如果客戶(hù)機(jī)向服務(wù)端發(fā)送的SQL語(yǔ)句中含有設(shè)計(jì)排序的order by 或者group by 子句。MySQL就會(huì)選擇相應(yīng)的排序算法，在普通排序索引上進(jìn)行排序，提升排序速度。普通排序索引的大小由sort_buffer_size 參數(shù)定義，如果要提升排序的速度，首先應(yīng)該添加合適的索引，此后則應(yīng)該增大排序索引緩存sort_buffer_size.

mysql> select @@global.sort_buffer_size / 1024;
+----------------------------------+
| @@global.sort_buffer_size / 1024 |
+----------------------------------+
|             256.0000 |
+----------------------------------+
1 row in set (0.00 sec)

接下來(lái)我們來(lái)看下與排序緩存相關(guān)的參數(shù)有哪些：

mysql> show variables like '%sort%';
+--------------------------------+---------------------+
| Variable_name         | Value        |
+--------------------------------+---------------------+
| innodb_disable_sort_file_cache | OFF         |
| innodb_ft_sort_pll_degree   | 2          |
| innodb_sort_buffer_size    | 1048576       |
| max_length_for_sort_data    | 1024        |
| max_sort_length        | 1024        |
| myisam_max_sort_file_size   | 9223372036853727232 |
| myisam_sort_buffer_size    | 8388608       |
| sort_buffer_size        | 262144       |
+--------------------------------+---------------------+

mysql> show status like '%sort%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Sort_merge_passes | 0   |
| Sort_range    | 0   |
| Sort_rows     | 0   |
| Sort_scan     | 0   |
+-------------------+-------+

max_length_for_sort_data

默認(rèn)大小為1024字節(jié)，對(duì)每一列的進(jìn)行排序操作是，如果該列的值長(zhǎng)度較長(zhǎng)，通過(guò)增加該參數(shù)來(lái)提升MySQL性能。

max_sort_length

order by 或者 group by 的時(shí)候使用該列的前 max_sort_length字節(jié)進(jìn)行排序，排序操作完成后，會(huì)將此次排序的信息記錄到本次會(huì)話(huà)的狀態(tài)里。

Sort_merge_passes

使用臨時(shí)文件完成排序操作的次數(shù)。MySQL在進(jìn)行排序操作時(shí)，首先嘗試在普通排序緩存中完成排序。如果緩存空間不夠用，MySQL將利用緩存進(jìn)行多次排序。并把每次的排序結(jié)果存放到臨時(shí)文件中，最后再把臨時(shí)文件中的數(shù)據(jù)做一次排序。Sort_merge_passes值就是記錄了使用文件進(jìn)行排序的次數(shù)。由于文件排序要牽涉到讀文件，打開(kāi)文件句柄，然后關(guān)閉文件等操作。所以讀取文件的系統(tǒng)消耗比較大，通過(guò)增大普通排序緩存sort_buffer_size來(lái)減少使用臨時(shí)文件排序的次數(shù)，從而增加排序的性能。

Sort_range

使用范圍排序的次數(shù)

Sort_rows

已經(jīng)排序的記錄行數(shù)

Sort_scan

通過(guò)全表掃描完成排序的次數(shù)

2、MyISAM排序緩存

當(dāng)我們使用alter table 語(yǔ)句或者create index 語(yǔ)句創(chuàng)建MyISAM表的索引，或者導(dǎo)入一部分?jǐn)?shù)據(jù)使用load data infile path，這些操作都會(huì)導(dǎo)致索引被重建，重建索引時(shí)需要對(duì)索引字段進(jìn)行排序操作，為了加快重建索引的效率，MyISAM提供了排序緩存用于實(shí)現(xiàn)索引的排序工作，這些方法都是盡量是排序的工作在內(nèi)存中完成。MyISAM排序緩存的大小由myisam_sort_buffer_size定義。索引重建之后，該緩存立馬釋放。

但是當(dāng)排序的緩存超過(guò)myisam_sort_buffer_size的閾限時(shí)，此時(shí)就需要在臨時(shí)文件中完成索引字段的排序工作，外存臨時(shí)文件的大小由myisam_max_sort_file_size參數(shù)設(shè)定，索引重建后，臨時(shí)文件立即刪除。

mysql> select @@global.myisam_sort_buffer_size/1024;
+---------------------------------------+
| @@global.myisam_sort_buffer_size/1024 |
+---------------------------------------+
|               8192.0000 |
+---------------------------------------+

mysql> select @@global.myisam_max_sort_file_size /1024;
+------------------------------------------+
| @@global.myisam_max_sort_file_size /1024 |
+------------------------------------------+
|          9007199254739967.7734 |
+------------------------------------------+

3、InnoDB 排序緩存

和MyISAM引擎類(lèi)似，當(dāng)執(zhí)行alter table 、create index 創(chuàng)建索引是，InnoDB提供了3個(gè)InnoDB排序緩存用于實(shí)現(xiàn)索引的排序，每個(gè)緩存的大小由innodb_sort_buffer_size定義。

mysql> select @@global.innodb_sort_buffer_size/1024;
+---------------------------------------+
| @@global.innodb_sort_buffer_size/1024 |
+---------------------------------------+
|               1024.0000 |
+---------------------------------------+

4、join 連接緩存

join緩存是會(huì)話(huà)緩存，如果兩張表相連，但是卻無(wú)法使用索引（這時(shí)使用join連接緩存的前提），MySQL將為每張表分配join 連接緩存。

mysql> select @@global.join_buffer_size/1024;
+--------------------------------+
| @@global.join_buffer_size/1024 |
+--------------------------------+
|            256.0000 |
+--------------------------------+

join_buffer_size 定義了連接緩存的大小，如上圖，默認(rèn)為256;

5、表緩存Cache 與表結(jié)構(gòu)定義緩存Cache

MySQL 服務(wù)訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)中的表時(shí)，實(shí)際上MySQL是做的文件的讀取操作。MySQL的數(shù)據(jù)都是存在硬盤(pán)上的一個(gè)個(gè)文件，這個(gè)和一些內(nèi)存的型的數(shù)據(jù)庫(kù)不同。當(dāng)我們查詢(xún)一張表，使用select 語(yǔ)句時(shí)，不考慮使用查詢(xún)緩存，首先要操作系統(tǒng)打開(kāi)該文件，產(chǎn)生該文件的描述符。操作系統(tǒng)將文件描述符交給MySQL,MySQL才能對(duì)數(shù)據(jù)庫(kù)進(jìn)行CURD的操作。打開(kāi)文件、產(chǎn)生文件描述符都需要消耗系統(tǒng)資源，造成訪(fǎng)問(wèn)延時(shí)。MySQL將已經(jīng)打開(kāi)的文件，包括文件描述符緩存起來(lái)，以后再次訪(fǎng)問(wèn)該文件時(shí)，就無(wú)需打開(kāi)該文件，提高了讀取文件的效率。

表結(jié)構(gòu)并不經(jīng)常變化，當(dāng)對(duì)表進(jìn)行訪(fǎng)問(wèn)的時(shí)候，除了將該表植入MySQL的表緩存外，MySQL還將表結(jié)構(gòu)放入了表結(jié)構(gòu)定義緩存中，供下次使用。

mysql> show variables like 'table%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| table_definition_cache   | 1400 |
| table_open_cache      | 2000 |
| table_open_cache_instances | 1   |
+----------------------------+-------+

mysql> show variables like '%open%';
+----------------------------+----------+
| Variable_name       | Value  |
+----------------------------+----------+
| have_openssl        | DISABLED |
| innodb_open_files     | 2000   |
| open_files_limit      | 65535  |
| table_open_cache      | 2000   |
| table_open_cache_instances | 1    |
+----------------------------+----------+

table_open_cache

設(shè)定了可以緩存表以及視圖的數(shù)量限制

table_definition_cache

設(shè)定了可以存儲(chǔ)多少?gòu)坒rm 表結(jié)構(gòu)

對(duì)于MySQL MyISAM引擎來(lái)說(shuō)，表結(jié)構(gòu)包含MYI 和MYD 以及表結(jié)構(gòu)frm, 當(dāng)訪(fǎng)問(wèn)MyISAM 引擎的時(shí)候，需要一次性打開(kāi)兩個(gè)文件（MYI 、MYD）,產(chǎn)生兩個(gè)文件描述符。

open_files_limit

打開(kāi)文件的上限

innodb_open_files

如果InnoDB 表使用的是獨(dú)立表空間文件（ibd），該參數(shù)設(shè)定同一時(shí)間能夠打開(kāi)的文件數(shù)量。

以下是和打開(kāi)表相關(guān)的狀態(tài)值：

mysql> show status like 'Open%';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| Open_files        | 18  |
| Open_streams       | 0   |
| Open_table_definitions  | 70  |
| Open_tables       | 63  |
| Opened_files       | 125  |
| Opened_table_definitions | 0   |
| Opened_tables      | 0   |
+--------------------------+-------+

6、表掃描緩存buffer

表掃描分為順序掃描（Sequential Scan）以及隨機(jī)掃描（Random Scan） 兩種方式

順序掃描 當(dāng)MyISAM表沒(méi)有建索引時(shí)，查詢(xún)速度將進(jìn)行全表掃描，效率很低。為了提升全表掃描的速度，MySQL提供了順序掃描緩存（read buffer）。此時(shí)MySQL按照存儲(chǔ)數(shù)據(jù)的存儲(chǔ)順序因此讀出全部的數(shù)據(jù)塊，每次讀取的數(shù)據(jù)塊緩存在順序掃描緩存中，當(dāng)read buffer寫(xiě)滿(mǎn)之后，將數(shù)據(jù)返還給上層調(diào)用者。

隨機(jī)掃描

當(dāng)表里有緩存，掃描表的時(shí)候，會(huì)將表的索引字段放進(jìn)內(nèi)存里先拍好序，然后按照已經(jīng)拍好的順序去硬盤(pán)中查找數(shù)據(jù)。

7、MyISAM索引緩存buffer

通過(guò)緩存MYI索引文件的內(nèi)容，可以加快讀取索引的速度以及索引的速度。索引緩存只對(duì)MyISAM表起作用，且被所有線(xiàn)程共享。查詢(xún)語(yǔ)句或者更新索引通過(guò)索引訪(fǎng)問(wèn)表數(shù)據(jù)的時(shí)候，MySQL首先檢查索引緩存中是否已經(jīng)存在需要的索引信息，如果有通過(guò)緩存中的索引可以直接訪(fǎng)問(wèn)到索引對(duì)應(yīng)的MYD文件。如果沒(méi)有，則會(huì)讀取MYI文件，并將相應(yīng)的索引數(shù)據(jù)讀取到緩存中。索引緩存對(duì)MyISAM表的訪(fǎng)問(wèn)性能起到了至關(guān)重要的作用。

mysql> show variables like 'key%';
+--------------------------+---------+
| Variable_name      | Value  |
+--------------------------+---------+
| key_buffer_size     | 8388608 (8M)| 
| key_cache_age_threshold | 300   |
| key_cache_block_size   | 1024  |
| key_cache_division_limit | 100   |
+--------------------------+---------+

key_buffer_size

設(shè)置索引緩存的大小，默認(rèn)是8M。建議提升。

key_cache_block_size

指定每個(gè)索引緩存的區(qū)塊大小，建議設(shè)置為4K,即4096

key_cache_division_limit

為了有效的使用緩存。默認(rèn)情況下MySQL降緩存劃分為兩個(gè)索引緩存區(qū)，溫區(qū)（warm area） 以及熱區(qū)（hot area）。key_cache_division_limit參數(shù)以百分比的形式向曾哥索引緩存劃分為多個(gè)區(qū)域。當(dāng)默認(rèn)值是100的時(shí)候，表示索引緩存只有溫區(qū)，將啟用LRU算法淘汰索引緩存中的索引。

key_cahe_age_threshold

控制溫區(qū)域熱區(qū)中的索引何時(shí)升級(jí)何時(shí)降級(jí)。如果該值小于100，則有熱區(qū)。移動(dòng)算法大致類(lèi)似與LRU算法。

查看當(dāng)前MySQL服務(wù)實(shí)例索引讀以及索引寫(xiě)的狀態(tài)值：

mysql> show status like 'Key%';
+------------------------+-------+
| Variable_name     | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0   |
| Key_blocks_unused   | 6698 |
| Key_blocks_used    | 0   |
| Key_read_requests   | 0   |
| Key_reads       | 0   |
| Key_write_requests   | 0   |
| Key_writes       | 0   |
+------------------------+-------+

8、日志緩存

日志緩存分為二進(jìn)制日志緩存以及InnoDB重做日志緩存

1、二進(jìn)制日志緩存

mysql> show variables like '%binlog%cache%';
+----------------------------+----------------------+
| Variable_name       | Value        |
+----------------------------+----------------------+
| binlog_cache_size     | 32768        |
| binlog_stmt_cache_size   | 32768        |
| max_binlog_cache_size   | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+

mysql> show status like '%binlog%cache%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| Binlog_cache_disk_use   | 0   |
| Binlog_cache_use      | 0   |
| Binlog_stmt_cache_disk_use | 0   |
| Binlog_stmt_cache_use   | 0   |
+----------------------------+-------+

Mysql 進(jìn)行創(chuàng)建或者更新的數(shù)據(jù)的時(shí)候，會(huì)記錄一條二進(jìn)制日志。然而頻繁的進(jìn)行I/O操作將對(duì)MySQL造成較大的性能影響。因此MySQL開(kāi)辟了一個(gè)二進(jìn)制日志緩存binlog_cache_size。首先將操作寫(xiě)入二進(jìn)制日志，當(dāng)操作成功之后，將二進(jìn)制日志寫(xiě)入硬盤(pán)。

2、InnoDB重做日志緩存

事務(wù)在commit前，會(huì)將產(chǎn)生的重做日志寫(xiě)入InnoDB重做日志緩存，然后InnoDB【擇機(jī)】執(zhí)行輪詢(xún)策略，將緩存中的重做日志文件寫(xiě)入ib_logfile0 以及ib_logfile1重做日志中。

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+---------+
| Variable_name     | Value  |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+

InnoDB重做日志緩存可以確保事務(wù)提交前，事務(wù)運(yùn)行期間產(chǎn)生的重做日志保存在InnoDB的日志緩存中，但并不寫(xiě)入重做日志文件中。寫(xiě)入時(shí)機(jī)由innodb_flush_log_at_trx_commit參數(shù)控制。

mysql> show variables like 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name         | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout  | 1   |
| innodb_flush_log_at_trx_commit | 1   |
+--------------------------------+-------+

0:當(dāng)緩存中重做日志文件以每秒一次的頻率寫(xiě)入硬盤(pán)緩存，并且同時(shí)會(huì)更新到硬盤(pán)。

1：在每次事務(wù)提交的時(shí)候，將緩存中重做日志寫(xiě)到重做日志文件，同時(shí)寫(xiě)入硬盤(pán)，默認(rèn)是該行為。

2:事務(wù)提交的時(shí)候，寫(xiě)到緩存，但并不觸發(fā)文件系統(tǒng)到硬盤(pán)的同步操作，但此外每秒一次同步硬盤(pán)。

9、預(yù)讀機(jī)制

預(yù)讀機(jī)制主要利用了前文MySQL優(yōu)化:一 、緩存優(yōu)化所描述的原理。即局部性特征，空間局部性，和時(shí)間局部性，這里不再贅述。

1、InnoDB預(yù)讀機(jī)制

InnoDB采用預(yù)讀機(jī)制，將“未來(lái)即將訪(fǎng)問(wèn)的數(shù)據(jù)”包括索引加載到預(yù)讀緩存中，進(jìn)而提升數(shù)據(jù)的讀性能。InnoDB支持順序預(yù)讀（linear read ahead）與隨機(jī)預(yù)讀（random read ahead）兩種方式。

數(shù)據(jù)塊（page）是InnoDB硬盤(pán)管理的最小單位，一個(gè)區(qū)由64個(gè)連續(xù)的數(shù)據(jù)塊構(gòu)成，對(duì)于順序預(yù)讀而言，InnoDB首選將該數(shù)據(jù)所在數(shù)據(jù)塊置入InnoDB緩存池中，可以預(yù)測(cè)這些數(shù)據(jù)塊的后續(xù)塊很快就會(huì)被訪(fǎng)問(wèn)，于是這些數(shù)據(jù)塊以及前置的數(shù)據(jù)塊會(huì)被置入內(nèi)存中。根據(jù)innodb_read_ahead_threshold參數(shù)設(shè)定預(yù)讀前后多少個(gè)數(shù)據(jù)塊。

mysql> show variables like 'innodb_read_ahead%';
+-----------------------------+-------+
| Variable_name        | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56  |
+-----------------------------+-------+

2、索引緩存預(yù)加載

數(shù)據(jù)庫(kù)管理員可以使用MySQL命令 load index into cache 預(yù)加載MyISAM表索引

10、MyISAM表延遲插入

mysql> show variables like '%delayed%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| delayed_insert_limit    | 100  |
| delayed_insert_timeout   | 300  |
| delayed_queue_size     | 1000 |
| max_delayed_threads    | 20  |
| max_insert_delayed_threads | 20  |
+----------------------------+-------+

看到這個(gè)延遲插入的功能，想起項(xiàng)目里一個(gè)有點(diǎn)類(lèi)似的功能，啟發(fā)了自己的思路。

使用方法為：insert delyed into table values(*);

delyed_insert_limit

默認(rèn)值為100.當(dāng)向MySQL表延遲插入100行記錄后，檢查該表是否有select語(yǔ)句在等待執(zhí)行，如果有，暫停insert語(yǔ)句執(zhí)行。

delayed_insert_timeout

在超時(shí)范圍內(nèi)，如果delayed 隊(duì)列里沒(méi)有數(shù)據(jù)，延遲插入線(xiàn)程將關(guān)掉。

delayed_queue_size

延遲插入的隊(duì)列長(zhǎng)度,超出將阻塞，直到有足夠的空間。

max_delayed_threads

延遲插入的線(xiàn)程數(shù)。

MyISAM表的批量延遲插入

類(lèi)似 insert into table values(1),values(2),values(n)。MyISAM將進(jìn)行批量插入。先將插入的數(shù)據(jù)放入緩存。當(dāng)緩存被寫(xiě)滿(mǎn)或者提交完畢了，MySQL一次性的將緩存中的寫(xiě)入硬盤(pán)。通過(guò)批量插入可以大大縮減MySQL客戶(hù)機(jī)與服務(wù)機(jī)的連接語(yǔ)法分析等消耗，使得效率比分開(kāi)執(zhí)行單個(gè)insert語(yǔ)句快的多。

mysql> select @@global.bulk_insert_buffer_size/(1024*1024);
+----------------------------------------------+
| @@global.bulk_insert_buffer_size/(1024*1024) |
+----------------------------------------------+
|                    8.0000 |
+----------------------------------------------+

默認(rèn)批量插入的大小為8M。如果業(yè)務(wù)上有需要，可以設(shè)定的大一些，以提高批量插入的性能。

MyISAM表的索引延遲更新

索引可以加快數(shù)據(jù)檢索，但是對(duì)于更新來(lái)說(shuō)，不僅需要修改記錄，可能還需要修改索引，因此索引會(huì)導(dǎo)致數(shù)據(jù)更新操作變慢，如果將MySQL的delay_key_write參數(shù)設(shè)置為1（ON）,可以彌補(bǔ)這一缺陷。開(kāi)啟后更新操作修改數(shù)據(jù)的時(shí)候先將數(shù)據(jù)的更新提交到硬盤(pán)，索引的更新全部在索引緩存里完成。在關(guān)閉表的時(shí)候，一起更新到硬盤(pán)，這樣就可以使索引更新的更快。僅對(duì)MyISAM有效。

mysql> show variables like 'delay_key_write';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| delay_key_write | ON  |
+-----------------+-------+

InnoDB延遲更新

非聚簇索引的更新操作通常會(huì)帶來(lái)隨機(jī)I/O，降低InoDB的性能。當(dāng)更新（insert, delete ，update=insert+delete）非聚簇索引的數(shù)據(jù)時(shí)，會(huì)先檢查非聚簇索引頁(yè)是否位于InnoDB緩存池中，如果是直接更新，否則先將“信息修改”記錄在更新緩存中（change buffer）

這篇博客的內(nèi)容比較多，總結(jié)提煉下來(lái)以備以后查看。對(duì)整個(gè)MySQL的優(yōu)化先有個(gè)整體的框架，徐徐漸進(jìn)慢慢進(jìn)步。這些參數(shù)可以不用記憶，用到的時(shí)候到博客中查找或者百度即可。了解道，知道術(shù)，就可以完成優(yōu)化的過(guò)程。知道原理比記憶枯燥的原理要簡(jiǎn)單的多。對(duì)MySQL優(yōu)化感興趣的博友可以關(guān)注我的博客，以便看到后續(xù)的分享。

最新評(píng)論