MySQL架構(gòu)

mysql架構(gòu)圖

查詢緩存

查詢緩存（Query Cache）原理：

緩存SELECT操作或預(yù)處理查詢的結(jié)果集和SQL語句，當(dāng)有新的SELECT語句或預(yù)處理查詢語句請求，先去查詢緩存，判斷是否存在可用的記錄集，判斷標(biāo)準(zhǔn)：與緩存的SQL語句，是否完全一樣，區(qū)分大小寫

優(yōu)缺點(diǎn)：

不需要對SQL語句做任何解析和執(zhí)行，當(dāng)然語法解析必須通過在先，直接從Query Cache中獲得查詢結(jié)果，提高查詢性能
查詢緩存的判斷規(guī)則，不夠智能，也即提高了查詢緩存的使用門欄，降低其效率查詢緩存的使用，會(huì)增加檢查和清理Query Cache中記錄集的開銷

哪些查詢可能不會(huì)被緩存

• 查詢語句中加了SQL_NO_CACHE參數(shù)
• 查詢語句中含有獲得值的函數(shù)，包含自定義函數(shù)，如：NOW()、CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
• 對系統(tǒng)數(shù)據(jù)庫的查詢：mysql、information_schema查詢語句中使用SESSION級別變量或存儲(chǔ)過程中的局部變量
• 查詢語句中使用了LOCK IN SHARE MODE、FOR UPDATE的語句，查詢語句中類似SELECT ... INTO導(dǎo)出數(shù)據(jù)的語句
• 對臨時(shí)表的查詢操作；存在警告信息的查詢語句；不涉及任何表或視圖的查詢語句；某用戶只有列級別權(quán)限的查詢語句
• 事務(wù)隔離級別為Serializable時(shí)，所有查詢語句都不能緩存

查詢緩存相關(guān)的服務(wù)器變量

• query_cache_min_res_unit：查詢緩存中內(nèi)存塊的最小分配單位，默認(rèn)4k，較小值會(huì)減少浪費(fèi)，但會(huì)導(dǎo)致更頻繁的內(nèi)存分配操作，較大值會(huì)帶來浪費(fèi)，會(huì)導(dǎo)致碎片過多，內(nèi)存不足
• query_cache_limit：單個(gè)查詢結(jié)果能緩存的最大值，默認(rèn)為1M，對于查詢結(jié)果過大而無法緩存的語句，建議使用SQL_NO_CACHE
• query_cache_wlock_invalidate：如果某表被其它的會(huì)話鎖定，是否仍然可以從查詢緩存中返回結(jié)果，默認(rèn)值為OFF，表示可以在表被其它的會(huì)話鎖定的場景中繼續(xù)從緩存返回?cái)?shù)據(jù)；ON則表示不允許
• query_cache_type：是否開啟緩存功能，取值為ON，OFF，DEMAND

查詢緩存

• SELECT語句的緩存控制
  SQL_CACHE：顯示指定存儲(chǔ)查詢結(jié)果于緩存之中
  SQL_NO_CACHE：顯示查詢結(jié)果不予緩存
• query_cache_type參數(shù)變量：
  query_cache_type的值為OFF或0時(shí)，查詢緩存功能關(guān)閉
  query_cache_type的值為ON或1時(shí)，查詢緩存功能打開，SELECT的結(jié)果符合緩存條件即會(huì)緩存，否則，不予緩存，顯示指定SQL_NO_CACHE，不予緩存，此為默認(rèn)值
  query_cache_type的值為DEMAND或2時(shí)，查詢緩存功能按需進(jìn)行，顯示指定SQL_CACHE的SELECT語句才會(huì)緩存；其它均不予緩存
• 參看：https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
  https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

查詢緩存相關(guān)的狀態(tài)變量：

SHOW GLOBAL STATUS LIKE 'Qcache%';

• Qcache_free_blocks：處于空閑狀態(tài)Query Cache中內(nèi)存Block數(shù)
• Qcache_free_memory：處于空閑狀態(tài)的Query Cache內(nèi)存總量
• Qcache_hits：Query Cache命中次數(shù)
• Qcache_inserts：向Query Cache中插入新的Query Cache的次數(shù)，即沒有命中的次數(shù)
• Qcache_lowmem_prunes：當(dāng)Query Cache 內(nèi)存容量不夠，需要?jiǎng)h除老的Query Cache以給新的Cache對象使用的次數(shù)
• Qcache_not_cached：沒有被Cache的SQL數(shù)，包括無法被Cache的SQL以及由于query_cache_type設(shè)置的不會(huì)被Cache的SQL語句
• Qcache_queries_in_cache：在Query Cache中的SQL數(shù)量
• Qcache_total_blocks：Query Cache中總的Block

命中率和內(nèi)存使用率估算

• 查詢緩存中內(nèi)存塊的最小分配單位query_cache_min_res_unit：
  (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
• 查詢緩存命中率：
  Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
• 查詢緩存內(nèi)存使用率：
  (query_cache_size - qcache_free_memory) / query_cache_size * 100%

InnoDB存儲(chǔ)引擎的緩沖池：

通常InnoDB存儲(chǔ)引擎緩存池的命中不應(yīng)該小于99%

查看相關(guān)狀態(tài)變量：

SHOW GLOBAL STATUS LIKE 'innodb%read%'\G;

• Innodb_buffer_pool_reads：表示從物理磁盤讀取頁的次數(shù)
• Innodb_buffer_pool_read_ahead：預(yù)讀的次數(shù)
• Innodb_buffer_pool_read_ahead_evicted：預(yù)讀頁，但是沒有讀緩沖池中被替換的頁數(shù)量，一般用來判斷預(yù)讀的效率
• Innodb_buffer_pool_read_requests：從緩存池中讀取頁次數(shù)
• Innodb_data_read：總共讀入的字節(jié)數(shù)
• Innodb_data_reads：發(fā)起讀取請求的次數(shù)，每次讀取可能需要讀取多個(gè)頁

SQL語句性能優(yōu)化

• 查詢時(shí)，能不用*就不用*，盡量寫全字段名
• 大部分情況連接效率遠(yuǎn)大于子查詢
• 多表連接時(shí)，盡量小表驅(qū)動(dòng)大表，即小表 join 大表
• 在千萬級分頁時(shí)使用 limit
• 對于經(jīng)常使用的查詢，可以開啟緩存
• 多使用 explain 和 profile 分析查詢語句
• 查看慢查詢?nèi)罩?，找出?zhí)行時(shí)間長的 sql 語句優(yōu)化