目錄
- 系列教程
- 一、MySQL的架構(gòu)
- 二、查詢緩存（Query Cache）
- 哪些查詢可能不會(huì)被緩存：
- 查詢緩存相關(guān)的服務(wù)器變量：
- 查詢緩存相關(guān)的狀態(tài)變量：
- 三、索引
- 1、索引類型：
- 2、高性能索引策略：
- 3、索引的優(yōu)化建議
- 4、索引的創(chuàng)建與刪除
- 四、EXPLAIN命令
- 五、SQL語(yǔ)句性能優(yōu)化
系列教程
MySQL系列之開(kāi)篇 MySQL關(guān)系型數(shù)據(jù)庫(kù)基礎(chǔ)概念
MySQL系列之一 MariaDB-server安裝
MySQL系列之二 多實(shí)例配置
MySQL系列之三 基礎(chǔ)篇
MySQL系列之四 SQL語(yǔ)法
MySQL系列之五 視圖、存儲(chǔ)函數(shù)、存儲(chǔ)過(guò)程、觸發(fā)器
MySQL系列之六 用戶與授權(quán)
MySQL系列之七 MySQL存儲(chǔ)引擎
MySQL系列之八 MySQL服務(wù)器變量
MySQL系列之十 MySQL事務(wù)隔離實(shí)現(xiàn)并發(fā)控制
MySQL系列之十一 日志記錄
MySQL系列之十二 備份與恢復(fù)
MySQL系列之十三 MySQL的復(fù)制
MySQL系列之十四 MySQL的高可用實(shí)現(xiàn)
MySQL系列之十五 MySQL常用配置和性能壓力測(cè)試
一、MySQL的架構(gòu)
- 連接器
- 連接池，安全認(rèn)證、線程池、連接限制、檢查內(nèi)存、緩存
- SQL接口 DML、DDL
- SQL解析器，對(duì)SQL語(yǔ)句的權(quán)限檢查、解析為二進(jìn)制程序
- 優(yōu)化器，優(yōu)化訪問(wèn)路徑
- 緩存cache,buffer
- 存儲(chǔ)引擎 innodb
- 文件系統(tǒng)
- 日志
二、查詢緩存（Query Cache）
-
SQL語(yǔ)句
-
查詢緩存
-
解析器
-
解析樹(shù)
-
預(yù)處理
-
查找最好的查詢路徑
-
查詢優(yōu)化SQL語(yǔ)句
-
執(zhí)行計(jì)劃
-
API調(diào)用存儲(chǔ)引擎
-
調(diào)用數(shù)據(jù)，返回結(jié)果
緩存SELECT操作或預(yù)處理查詢的結(jié)果集和SQL語(yǔ)句，當(dāng)有新的SELECT語(yǔ)句或預(yù)處理查詢語(yǔ)句請(qǐng)求，先去查詢緩存，判斷是否存在可用的記錄集，判斷標(biāo)準(zhǔn)：與緩存的SQL語(yǔ)句，是否完全一樣，區(qū)分大小寫。
不需要對(duì)SQL語(yǔ)句做任何解析和執(zhí)行，當(dāng)然語(yǔ)法解析必須通過(guò)在先，直接從Query Cache中獲得查詢結(jié)果，提高查詢性能
查詢緩存的判斷規(guī)則，不夠智能，也即提高了查詢緩存的使用門檻，降低其效率；查詢緩存的使用，會(huì)增加檢查和清理Query Cache中記錄集的開(kāi)銷
哪些查詢可能不會(huì)被緩存：
- 查詢語(yǔ)句中加了SQL_NO_CACHE參數(shù)；
- 查詢語(yǔ)句中含有獲得值的函數(shù)，包含自定義函數(shù)，如：NOW()、CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等；
- 對(duì)系統(tǒng)數(shù)據(jù)庫(kù)的查詢：mysql、information_schema 查詢語(yǔ)句中使用SESSION級(jí)別變量或存儲(chǔ)過(guò)程中的局部變量；
- 查詢語(yǔ)句中使用了LOCK IN SHARE MODE、FOR UPDATE的語(yǔ)句，查詢語(yǔ)句中類似SELECT …INTO 導(dǎo)出數(shù)據(jù)的語(yǔ)句；
- 對(duì)臨時(shí)表的查詢操作；存在警告信息的查詢語(yǔ)句；不涉及任何表或視圖的查詢語(yǔ)句；某用戶只有列級(jí)別權(quán)限的查詢語(yǔ)句；
- 事務(wù)隔離級(jí)別為Serializable時(shí)，所有查詢語(yǔ)句都不能緩存。
查詢緩存相關(guān)的服務(wù)器變量：
- query_cache_min_res_unit: 查詢緩存中內(nèi)存塊的最小分配單位，默認(rèn)4k，較小值會(huì)減少浪費(fèi)，但會(huì)導(dǎo)致更頻繁的內(nèi)存分配操作，較大值會(huì)帶來(lái)浪費(fèi)，會(huì)導(dǎo)致碎片過(guò)多，內(nèi)存不足；
- query_cache_limit：?jiǎn)蝹€(gè)查詢結(jié)果能緩存的最大值，默認(rèn)為1M，對(duì)于查詢結(jié)果過(guò)大而無(wú)法緩存的語(yǔ)句，建議使用SQL_NO_CACHE；
- query_cache_size：查詢緩存總共可用的內(nèi)存空間；單位字節(jié)，必須是1024的整數(shù)倍，最小值40KB，低于此值有警報(bào)；
- query_cache_wlock_invalidate：如果某表被其它的會(huì)話鎖定，是否仍然可以從查詢緩存中返回結(jié)果，默認(rèn)值為OFF，表示可以在表被其它會(huì)話鎖定的場(chǎng)景中繼續(xù)從緩存返回?cái)?shù)據(jù)；ON則表示不允許；
- query_cache_type: 是否開(kāi)啟緩存功能，取值為ON, OFF, DEMAND，默認(rèn)值為ON
- 值為OFF或0時(shí)，查詢緩存功能關(guān)閉；
- 值為ON或1時(shí)，查詢緩存功能打開(kāi)，SELECT的結(jié)果符合緩存條件即會(huì)緩存，否則，不予緩存，顯式指定SQL_NO_CACHE，不予緩存；
- 值為DEMAND或2時(shí)，查詢緩存功能按需進(jìn)行，顯式指定SQL_CACHE的SELECT語(yǔ)句才會(huì)緩存；其它均不予緩存。
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
優(yōu)化查詢緩存：
查詢緩存相關(guān)的狀態(tài)變量：
- 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ù)，即沒(méi)有命中的次數(shù)；
- Qcache_lowmem_prunes：當(dāng) Query Cache 內(nèi)存容量不夠，需要?jiǎng)h除老的Query Cache 以給新的 Cache 對(duì)象使用的次數(shù)；
- Qcache_not_cached：沒(méi)有被 Cache 的 SQL 數(shù)，包括無(wú)法被 Cache 的 SQL 以及由于 query_cache_type 設(shè)置的不會(huì)被 Cache 的 SQL語(yǔ)句；
- Qcache_queries_in_cache：在 Query Cache 中的 SQL 數(shù)量；
- Qcache_total_blocks：Query Cache 中總的 Block。
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33536824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
命中率和內(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%
三、索引
索引是特殊數(shù)據(jù)結(jié)構(gòu)：定義在查找時(shí)作為查找條件的字段，索引實(shí)現(xiàn)在存儲(chǔ)引擎。
索引可以降低服務(wù)需要掃描的數(shù)據(jù)量，減少了IO次數(shù)
索引可以幫助服務(wù)器避免排序和使用臨時(shí)表
索引可以幫助將隨機(jī)I/O轉(zhuǎn)為順序I/O
但是占用額外空間，影響插入速度
1、索引類型：
2、高性能索引策略：
- 獨(dú)立使用列，盡量避免其參與運(yùn)算
- 使用左前綴索引：索引構(gòu)建于字段的左側(cè)的多少字符要通過(guò)索引選擇性來(lái)評(píng)估；索引選擇性：不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值
- 多列索引：AND操作時(shí)更適合使用多列索引，而非為每個(gè)列創(chuàng)建單獨(dú)的索引
- 選擇合適的索引列次序：無(wú)排序和分組時(shí)，將選擇性最高放左側(cè)
3、索引的優(yōu)化建議
- 只要列中含有NULL值，就最好不要在此例設(shè)置索引，復(fù)合索引如果有NULL值，此列在使用時(shí)也不會(huì)使用索引
- 盡量使用短索引，如果可以，應(yīng)該制定一個(gè)前綴長(zhǎng)度
- 對(duì)于經(jīng)常在where子句使用的列，最好設(shè)置索引
- 對(duì)于有多個(gè)列where或者order by子句，應(yīng)該建立復(fù)合索引
- 對(duì)于like語(yǔ)句，以%或者‘-'開(kāi)頭的不會(huì)使用索引，以%結(jié)尾會(huì)使用索引
- 盡量不要在列上進(jìn)行運(yùn)算（函數(shù)操作和表達(dá)式操作）
- 盡量不要使用not in和>操作
- 多表連接時(shí)，盡量小表驅(qū)動(dòng)大表，即小表 join 大表
- 在千萬(wàn)級(jí)分頁(yè)時(shí)使用limit
- 對(duì)于經(jīng)常使用的查詢，可以開(kāi)啟緩存
- 大部分情況連接效率遠(yuǎn)大于子查詢
4、索引的創(chuàng)建與刪除
創(chuàng)建索引
CREATE INDEX index_name ON tbl_name (index_col_name,...);
MariaDB [hellodb]> CREATE INDEX index_name ON students(name); #創(chuàng)建簡(jiǎn)單索引
MariaDB [hellodb]> CREATE INDEX index_name_age ON students(name,age); #創(chuàng)建復(fù)合索引
查看索引
SHOW INDEXES FROM [db_name.]tbl_name;
MariaDB [hellodb]> SHOW INDEX FROM students\G
刪除索引
DROP INDEX index_name ON tbl_name;
MariaDB [hellodb]> DROP INDEX index_name ON students;
優(yōu)化表空間
MariaDB [hellodb]> OPTIMIZE TABLE students;
查看索引使用的情況
啟用記錄索引使用情況：SET GLOBAL userstat=1;
查看索引使用情況：SHOW INDEX_STATISTICS;
我們可以統(tǒng)計(jì)不經(jīng)常使用的索引從而進(jìn)行優(yōu)化
四、EXPLAIN命令
通過(guò)EXPLAIN來(lái)分析索引的有效性：EXPLAIN SELECT clause，獲取查詢執(zhí)行計(jì)劃信息，用來(lái)查看查詢優(yōu)化器如何執(zhí)行查詢
MariaDB [hellodb]> EXPLAIN SELECT name FROM students WHERE name = 'Lin Daiyu'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ref
possible_keys: index_name_age
key: index_name_age
key_len: 152
ref: const
rows: 1
Extra: Using where; Using index
- id：當(dāng)前查詢語(yǔ)句中，每個(gè)SELECT語(yǔ)句的編號(hào)；復(fù)雜類型的查詢有三種：簡(jiǎn)單子查詢、用于FROM子句中的子查詢、聯(lián)合查詢（UNION，注意：UNION查詢的分析結(jié)果會(huì)出現(xiàn)一個(gè)額外匿名臨時(shí)表）
- select_type：
- SIMPLE ：簡(jiǎn)單查詢
- SUBQUERY: 簡(jiǎn)單子查詢
- PRIMARY:最外面的SELECT
- DERIVED: 用于FROM中的子查詢
- UNION：UNION語(yǔ)句的第一個(gè)之后的SELECT語(yǔ)句
- UNION RESULT: 匿名臨時(shí)表
- table：SELECT語(yǔ)句關(guān)聯(lián)到的表
- type：關(guān)聯(lián)類型或訪問(wèn)類型，即MySQL決定的如何去查詢表中的行的方式，以下順序，性能從低到高
- ALL: 全表掃描
- index：根據(jù)索引的次序進(jìn)行全表掃描；如果在Extra列出現(xiàn)“Using index”表示了使用覆蓋索引，而非全表掃描
- range：有范圍限制的根據(jù)索引實(shí)現(xiàn)范圍掃描；掃描位置始于索引中的某一點(diǎn)，結(jié)束于另一點(diǎn)
- ref: 根據(jù)索引返回表中匹配某單個(gè)值的所有行
- eq_ref：僅返回一個(gè)行，但與需要額外與某個(gè)參考值做比較
- const, system: 直接返回單個(gè)行
- possible_keys：查詢可能會(huì)用到的索引
- key: 查詢中使用到的索引
- key_len: 在索引使用的字節(jié)數(shù)
- ref: 在利用key字段所表示的索引完成查詢時(shí)所用的列或某常量值
- rows：MySQL估計(jì)為找所有的目標(biāo)行而需要讀取的行數(shù)
- Extra：額外信息
- Using index：MySQL將會(huì)使用覆蓋索引，以避免訪問(wèn)表
- Using where：MySQL服務(wù)器將在存儲(chǔ)引擎檢索后，再進(jìn)行一次過(guò)濾
- Using temporary：MySQL對(duì)結(jié)果排序時(shí)會(huì)使用臨時(shí)表
- Using filesort：對(duì)結(jié)果使用一個(gè)外部索引排序
五、SQL語(yǔ)句性能優(yōu)化
- 查詢時(shí)，能不要*就不用*，盡量寫全字段名
- 大部分情況連接效率遠(yuǎn)大于子查詢
- 多表連接時(shí)，盡量小表驅(qū)動(dòng)大表，即小表 join 大表
- 在千萬(wàn)級(jí)分頁(yè)時(shí)使用limit
- 對(duì)于經(jīng)常使用的查詢，可以開(kāi)啟緩存
- 多使用explain和profile分析查詢語(yǔ)句
- 查看慢查詢?nèi)罩荆页鰣?zhí)行時(shí)間長(zhǎng)的sql語(yǔ)句優(yōu)化
到此這篇關(guān)于MySQL系列之九 mysql查詢緩存及索引的文章就介紹到這了,更多相關(guān)mysql查詢緩存及索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySql 緩存查詢?cè)砼c緩存監(jiān)控和索引監(jiān)控介紹
- 淺談mysql增加索引不生效的幾種情況
- mysql聯(lián)合索引的使用規(guī)則
- MySQL 使用索引掃描進(jìn)行排序
- MySQL索引是啥?不懂就問(wèn)
