MySQL之變量、查詢緩存和索引

MySQL中的系統數據庫

mysql數據庫：是mysql的核心數據庫，類似于sql server中的master庫，主要負責存儲數據庫的用戶、權限設置、關鍵字等mysql自己需要使用的控制和管理信息

PERFORMANCE_SCHEMA:MySQL 5.5開始新增的數據庫，主要用于收集數據庫服務器性能參數,庫里表的存儲引擎均為PERFORMANCE_SCHEMA，用戶不能創建存儲引擎為PERFORMANCE_SCHEMA的表

information_schema數據庫:MySQL 5.0之后產生的，一個虛擬數據庫，物理上并不存在。information_schema數據庫類似與“數據字典”，提供了訪問數據庫元數據的方式，即數據的數據。比如數據庫名或表名，列類型，訪問權限（更加細化的訪問方式）

服務器配置

mysqld選項，服務器系統變量和服務器狀態變量

https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html

https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

注意：其中有些參數支持運行時修改，會立即生效；有些參數不支持，且只能通過修改配置文件，并重啟服務器程序生效；有些參數作用域是全局的，且不可改變；有些可以為每個用戶提供單獨（會話）的設置

獲取mysqld的可用選項列表：

mysqld --help –verbose

mysqld --print-defaults 獲取默認設置

服務器系統變量：分全局和會話兩種

服務器狀態變量：分全局和會話兩種

獲取運行中的mysql進程使用各服務器參數及其值

mysql> SHOW GLOBAL VARIABLES;

mysql> SHOW [SESSION] VARIABLES;

設置服務器系統變量三種方法：

在命令行中設置:

shell> ./mysqld_safe –-skip-name-resolve=1; #跳過DNS反向解析，加快連接速度

在配置文件my.cnf中設置：

skip_name_resolve=1;

在mysql客戶端使用SET命令：

mysql>SET GLOBAL sql_log_bin=0

服務器端設置

修改服務器變量的值：

mysql> help SET

修改全局變量：僅對修改后新創建的會話有效；對已經建立的會話無效

mysql> SET GLOBAL system_var_name=value;

mysql> SET @@global.system_var_name=value;

修改會話變量：

mysql> SET [SESSION] system_var_name=value;

mysql> SET @@[session.]system_var_name=value;

狀態變量（只讀）：用于保存mysqld運行中的統計數據的變量，不可更改

mysql> SHOW GLOBAL STATUS;

mysql> SHOW [SESSION] STATUS;

服務器變量SQL_MODE

SQL_MODE：對其設置可以完成一些約束檢查的工作,可分別進行全局的設置或當前會話的設置，參看：https://mariadb.com/kb/en/library/sql-mode/

常見MODE:

NO_AUTO_CREATE_USER

禁止GRANT創建密碼為空的用戶

NO_AUTO_VALUE_ON_ZERO

在自增長的列中插入0或NULL將不會是下一個自增長值

NO_BACKSLASH_ESCAPES

反斜杠“\”作為普通字符而非轉義字符

PAD_CHAR_TO_FULL_LENGTH

啟用后，對于CHAR類型將不會截斷空洞數據

PIPES_AS_CONCAT

將"||"視為連接操作符而非“或運算符”

例：

set sql_mode='traditional';

show variables like 'sql_mode'可以看到traditional由很多項組成

MariaDB配置文件需要增加的選項

innodb_file_per_table

       每個表存成一個獨立的文件

log_bin=/data/mysqllog/mysql-bin

       開啟二進制日志的兩個選項之一

       二進制日志不要跟數據放在同一塊磁盤

       注意文件夾的所有者所屬組權限需是mysql

       預留足夠空間，通常二進制日志比數據庫本身還要大

binlog_format=row

       二進制日志基于行記錄

innodb_log_group_home_dir=/data/mysqllog/transaction_log/

       指定事務日志存放目錄，建議跟數據存放在不同磁盤中

skip_name_resolve=on

       禁止名字解析

query_cache_size=10M

       查詢緩存size調成不為0，否則查詢緩存不開啟

頻繁使用的字段可以加索引

MySQL架構

數據庫的數據塊大小是4K的整數倍，MySQL是16K

查詢的執行路徑

查詢緩存

查詢緩存（ Query Cache ）原理：

緩存SELECT操作或預處理查詢的結果集和SQL語句，當有新的SELECT語句或預處理查詢語句請求，先去查詢緩存，判斷是否存在可用的記錄集，判斷標準：與緩存的SQL語句，是否完全一樣，區分大小寫（因為使用的是hash值）

優缺點

不需要對SQL語句做任何解析和執行，當然語法解析必須通過在先，直接從Query Cache中獲得查詢結果，提高查詢性能

查詢緩存的判斷規則，不夠智能，也即提高了查詢緩存的使用門檻，降低其效率；

查詢緩存的使用，會增加檢查和清理Query Cache中記錄集的開銷

哪些查詢可能不會被緩存

查詢語句中加了SQL_NO_CACHE參數（例如查詢幾百萬的大表的時候，實際是沒法存入緩存的，這時候加SQL_NO_CACHE反而能夠提高性能）

查詢語句中含有獲得值的函數，包含自定義函數，如：NOW()

CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等

對系統數據庫的查詢：mysql、information_schema 查詢語句中使用SESSION級別變量或存儲過程中的局部變量

查詢語句中使用了LOCK IN SHARE MODE、FOR UPDATE的語句 查詢語句中類似SELECT …INTO 導出數據的語句

對臨時表的查詢操作；存在警告信息的查詢語句；不涉及任何表或視圖的查詢語句；某用戶只有列級別權限的查詢語句

事務隔離級別為Serializable時，所有查詢語句都不能緩存

查詢緩存相關的服務器變量

query_cache_min_res_unit: 查詢緩存中內存塊的最小分配單位，默認4k，較小值會減少浪費，但會導致更頻繁的內存分配操作，較大值會帶來浪費，會導致碎片過多，內存不足

query_cache_limit：單個查詢結果能緩存的最大值，默認為1M，

對于查詢結果過大而無法緩存的語句，建議使用SQL_NO_CACHE

query_cache_size：查詢緩存總共可用的內存空間；單位字節，必須是1024的整數倍，最小值40KB，低于此值有警報（默認為0，緩存不啟動）

query_cache_wlock_invalidate：如果某表被其它的會話鎖定，是否仍然可以從查詢緩存中返回結果， 默認值為OFF，表示可以在表被其它會話鎖定的場景中繼續從緩存返回數據；ON則表示不允許

query_cache_type: 是否開啟緩存功能，取值為ON, OFF, DEMAND

SELECT語句的緩存控制

SQL_CACHE: 顯式指定存儲查詢結果于緩存之中

SQL_NO_CACHE: 顯式查詢結果不予緩存

query_cache_type參數變量：

query_cache_type的值為OFF或0時，查詢緩存功能關閉

query_cache_type的值為ON或1時，查詢緩存功能打開，SELECT的結果符合緩存條件即會緩存，否則，不予緩存，顯式指定SQL_NO_CACHE，不予緩存，此為默認值

query_cache_type的值為DEMAND或2時，查詢緩存功能按需進行，顯式指定SQL_CACHE的SELECT語句才會緩存；其它均不予緩存

參看：

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

優化查詢緩存

查詢緩存相關的狀態變量

SHOW GLOBAL STATUS LIKE 'Qcache%';

Qcache_free_blocks：處于空閑狀態 Query Cache中內存 Block 數

Qcache_total_blocks：Query Cache 中總的 Block，當Qcache_free_blocks相對此值較大時，可能用內存碎片，執行FLUSH QUERY CACHE清理碎片

Qcache_free_memory：處于空閑狀態的 Query Cache 內存總量

Qcache_hits：Query Cache 命中次數

Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，即沒有命中的次數

Qcache_lowmem_prunes：當 Query Cache 內存容量不夠，需要刪除老的 Query Cache 以給新的 Cache 對象使用的次數

Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL 以及由于 query_cache_type 設置的不會被 Cache 的 SQL語句

Qcache_queries_in_cache：在 Query Cache 中的 SQL 數量

命中率和內存使用率估算

查詢緩存中內存塊的最小分配單位query_cache_min_res_unit ：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

查詢緩存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%

查詢緩存內存使用率：(query_cache_size – qcache_free_memory) / query_cache_size * 100%

InnoDB存儲引擎

InnoDB存儲引擎的緩沖池:

通常InnoDB存儲引擎緩沖池的命中不應該小于99%

查看相關狀態變量：

show global status like 'innodb%read%'\G

Innodb_buffer_pool_reads: 表示從物理磁盤讀取頁的次數

Innodb_buffer_pool_read_ahead: 預讀的次數

Innodb_buffer_pool_read_ahead_evicted: 預讀頁，但是沒有讀取就從緩沖池中被替換的頁數量，一般用來判斷預讀的效率

Innodb_buffer_pool_read_requests: 從緩沖池中讀取頁次數

Innodb_data_read: 總共讀入的字節數

Innodb_data_reads: 發起讀取請求的次數，每次讀取可能需要讀取多個頁

Innodb緩沖池命中率計算：

平均每次讀取的字節數：

索引

索引是特殊數據結構：定義在查找時作為查找條件的字段

索引實現在存儲引擎

優點：

索引可以降低服務需要掃描的數據量，減少了IO次數

索引可以幫助服務器避免排序和使用臨時表

索引可以幫助將隨機I/O轉為順序I/O

缺點：

占用額外空間，影響插入速度（因要同時改索引）

索引類型：

B+ TREE、HASH、R TREE

聚簇（集）索引、非聚簇索引：數據和索引存儲順序是否一致

主鍵索引、二級（輔助）索引

稠密索引、稀疏索引：是否索引了每一個數據項

簡單索引、組合索引（分別指在1個和多個字段上建立索引）

左前綴索引：取前面的字符做索引

覆蓋索引：從索引中即可取出要查詢的數據，性能高

聚簇和非聚簇索引，主鍵和二級索引

聚簇索引相當于書的目錄，非聚簇索引相當于書的附錄

一般主鍵索引是和數據在一起的，二級索引存放的是指向主鍵的指針，跟數據不在一起，一般不是主鍵的索引

MyISAM不支持聚簇索引

B+TREE索引

B+tree索引

B+tree索引

B+ Tree索引：順序存儲，每一個葉子節點到根結點的距離是相同的；左前綴索引，適合查詢范圍類的數據

可以使用B-Tree索引的查詢類型：

全值匹配：精確所有索引列，如：姓qin，名jianyuan，年齡27

匹配最左前綴：即只使用索引的第一列，如：姓qin

匹配列前綴：只匹配一列值開頭部分，如：姓以q開頭的

匹配范圍值：如：姓qin和姓wang之間

精確匹配某一列并范圍匹配另一列：如：姓qin，名以j開頭的

只訪問索引的查詢

B-Tree索引的限制：

如果不從最左列開始，則無法使用索引：如：查找名為xiaochun，或姓為g結尾

不能跳過索引中的列：如：查找姓wang，年齡30的，只能使用索引第一列

如果查詢中某個列是為范圍查詢，那么其右側的列都無法再使用索引：如：姓wang,名x%,年齡30，只能利用姓和名上面的索引

特別提示：

索引列的順序和查詢語句的寫法應相匹配，才能更好的利用索引

為優化性能，可能需要針對相同的列但順序不同創建不同的索引來滿足不同類型的查詢需求

Hash索引

Hash索引：基于哈希表實現，只有精確匹配索引中的所有列的查詢才有效，索引自身只存儲索引列對應的哈希值和數據指針，索引結構緊湊，查詢性能好

只有Memory存儲引擎支持顯式hash索引

適用場景：

只支持等值比較查詢，包括=, IN(), <=>

不適合使用hash索引的場景：

不適用于順序查詢：索引存儲順序的不是值的順序

不支持模糊匹配

不支持范圍查詢

不支持部分索引列匹配查找：如A，B列索引，只查詢A列索引無效

其他索引

空間索引（R-Tree）：

MyISAM支持空間索引，可以使用任意維度組合查詢，使用特有的函數訪問，常用于做地理數據存儲，使用不多

全文索引(FULLTEXT)：

在文本中查找關鍵詞，而不是直接比較索引中的值，類似搜索引擎

聚簇和非聚簇索引

聚簇和非聚簇索引，主鍵和二級索引

冗余和重復索引：

冗余索引：（A），（A，B）

此為不好的索引使用策略，建議擴展索引，而非冗余

重復索引：已經有索引，再次建立索引

索引優化策略：

獨立地使用列：盡量避免其參與運算，獨立的列指索引列不能是表達式的一部分，也不能是函數的參數，在where條件中，始終將索引列單獨放在比較符號的一側

左前綴索引：構建指定索引字段的左側的字符數，要通過索引選擇性來評估

索引選擇性：不重復的索引值和數據表的記錄總數的比值

多列索引：AND操作時更適合使用多列索引，而非為每個列創建單獨的索引

選擇合適的索引列順序：無排序和分組時，將選擇性最高放左側

索引優化建議

只要列中含有NULL值，就最好不要在此例設置索引，復合索引如果有NULL值，此列在使用時也不會使用索引

盡量使用短索引，如果可以，應該制定一個前綴長度

對于經常在where子句使用的列，最好設置索引

對于有多個列where或者order by子句，應該建立復合索引

對于like語句，以%或者‘-’開頭的不會使用索引，以%結尾會使用索引

盡量不要在列上進行運算（函數操作和表達式操作）

盡量不要使用not in和<>操作

SQL語句性能優化

查詢時，能不要*就不用*，盡量寫全字段名

大部分情況連接效率遠大于子查詢

多表連接時，盡量小表驅動大表，即小表 join 大表

在千萬級分頁時使用limit

對于經常使用的查詢，可以開啟緩存

多使用explain和profile分析查詢語句

查看慢查詢日志，找出執行時間長的sql語句優化

管理索引

創建索引：

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name,...);

help CREATE INDEX

刪除索引：

DROP INDEX index_name ON tbl_name;

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

優化表空間：

OPTIMIZE TABLE tb_name（表發生大變化的時候建議做一下整理）

查看索引的使用

SET GLOBAL userstat=1;

SHOW INDEX_STATISTICS

EXPLAIN

通過EXPLAIN來分析索引的有效性：

EXPLAIN SELECT clause

獲取查詢執行計劃信息，用來查看查詢優化器如何執行查詢

輸出信息說明：

參考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

id: 當前查詢語句中，每個SELECT語句的編號

復雜類型的查詢有三種：

簡單子查詢

用于FROM中的子查詢

聯合查詢：UNION

注意：UNION查詢的分析結果會出現一個額外匿名臨時表

select_type：

簡單查詢為SIMPLE

復雜查詢：

SUBQUERY: 簡單子查詢

PRIMARY:最外面的SELECT

DERIVED: 用于FROM中的子查詢（較新版本中這項要用的話）

UNION：UNION語句的第一個之后的SELECT語句

UNION RESULT: 匿名臨時表

table：SELECT語句關聯到的表

type：關聯類型或訪問類型，即MySQL決定的如何去查詢表中的行的方式，以下順序，性能從低到高

ALL: 全表掃描

index：根據索引的次序進行全表掃描；如果在Extra列出現“Using index”表示了使用覆蓋索引，而非全表掃描

range：有范圍限制的根據索引實現范圍掃描；掃描位置始于索引中的某一點，結束于另一點

ref: 根據索引返回表中匹配某單個值的所有行

eq_ref：僅返回一個行，但與需要額外與某個參考值做比較

const, system: 直接返回單個行

possible_keys：查詢可能會用到的索引

key: 查詢中使用到的索引

key_len: 在索引使用的字節數

ref: 在利用key字段所表示的索引完成查詢時所用的列或某常量值

rows：MySQL估計為找所有的目標行而需要讀取的行數

Extra：額外信息

Using index：MySQL將會使用覆蓋索引，以避免訪問表

Using where：MySQL服務器將在存儲引擎檢索后，再進行一次過濾

Using temporary：MySQL對結果排序時會使用臨時表

Using filesort：對結果使用一個外部索引排序