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

MySQL中的系統(tǒng)數(shù)據(jù)庫

mysql數(shù)據(jù)庫：是mysql的核心數(shù)據(jù)庫，類似于sql server中的master庫，主要負(fù)責(zé)存儲數(shù)據(jù)庫的用戶、權(quán)限設(shè)置、關(guān)鍵字等mysql自己需要使用的控制和管理信息

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

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

服務(wù)器配置

mysqld選項(xiàng)，服務(wù)器系統(tǒng)變量和服務(wù)器狀態(tài)變量

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/

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

獲取mysqld的可用選項(xiàng)列表：

mysqld --help –verbose

mysqld --print-defaults 獲取默認(rèn)設(shè)置

服務(wù)器系統(tǒng)變量：分全局和會話兩種

服務(wù)器狀態(tài)變量：分全局和會話兩種

獲取運(yùn)行中的mysql進(jìn)程使用各服務(wù)器參數(shù)及其值

mysql> SHOW GLOBAL VARIABLES;

mysql> SHOW [SESSION] VARIABLES;

設(shè)置服務(wù)器系統(tǒng)變量三種方法：

在命令行中設(shè)置:

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

在配置文件my.cnf中設(shè)置：

skip_name_resolve=1;

在mysql客戶端使用SET命令：

mysql>SET GLOBAL sql_log_bin=0

服務(wù)器端設(shè)置

修改服務(wù)器變量的值：

mysql> help SET

修改全局變量：僅對修改后新創(chuàng)建的會話有效；對已經(jīng)建立的會話無效

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;

狀態(tài)變量（只讀）：用于保存mysqld運(yùn)行中的統(tǒng)計(jì)數(shù)據(jù)的變量，不可更改

mysql> SHOW GLOBAL STATUS;

mysql> SHOW [SESSION] STATUS;

服務(wù)器變量SQL_MODE

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

常見MODE:

NO_AUTO_CREATE_USER

禁止GRANT創(chuàng)建密碼為空的用戶

NO_AUTO_VALUE_ON_ZERO

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

NO_BACKSLASH_ESCAPES

反斜杠“\”作為普通字符而非轉(zhuǎn)義字符

PAD_CHAR_TO_FULL_LENGTH

啟用后，對于CHAR類型將不會截?cái)嗫斩磾?shù)據(jù)

PIPES_AS_CONCAT

將"||"視為連接操作符而非“或運(yùn)算符”

例：

set sql_mode='traditional';

show variables like 'sql_mode'可以看到traditional由很多項(xiàng)組成

MariaDB配置文件需要增加的選項(xiàng)

innodb_file_per_table

       每個表存成一個獨(dú)立的文件

log_bin=/data/mysqllog/mysql-bin

       開啟二進(jìn)制日志的兩個選項(xiàng)之一

       二進(jìn)制日志不要跟數(shù)據(jù)放在同一塊磁盤

       注意文件夾的所有者所屬組權(quán)限需是mysql

       預(yù)留足夠空間，通常二進(jìn)制日志比數(shù)據(jù)庫本身還要大

binlog_format=row

       二進(jìn)制日志基于行記錄

innodb_log_group_home_dir=/data/mysqllog/transaction_log/

       指定事務(wù)日志存放目錄，建議跟數(shù)據(jù)存放在不同磁盤中

skip_name_resolve=on

       禁止名字解析

query_cache_size=10M

       查詢緩存size調(diào)成不為0，否則查詢緩存不開啟

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

MySQL架構(gòu)

數(shù)據(jù)庫的數(shù)據(jù)塊大小是4K的整數(shù)倍，MySQL是16K

查詢的執(zhí)行路徑

查詢緩存

查詢緩存（ Query Cache ）原理：

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

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

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

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

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

哪些查詢可能不會被緩存

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

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

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

對系統(tǒng)數(shù)據(jù)庫的查詢：mysql、information_schema 查詢語句中使用SESSION級別變量或存儲過程中的局部變量

查詢語句中使用了LOCK IN SHARE MODE、FOR UPDATE的語句 查詢語句中類似SELECT …INTO 導(dǎo)出數(shù)據(jù)的語句

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

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

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

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

query_cache_limit：單個查詢結(jié)果能緩存的最大值，默認(rèn)為1M，

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

query_cache_size：查詢緩存總共可用的內(nèi)存空間；單位字節(jié)，必須是1024的整數(shù)倍，最小值40KB，低于此值有警報(bào)（默認(rèn)為0，緩存不啟動）

query_cache_wlock_invalidate：如果某表被其它的會話鎖定，是否仍然可以從查詢緩存中返回結(jié)果， 默認(rèn)值為OFF，表示可以在表被其它會話鎖定的場景中繼續(xù)從緩存返回?cái)?shù)據(jù)；ON則表示不允許

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

SELECT語句的緩存控制

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

SQL_NO_CACHE: 顯式查詢結(jié)果不予緩存

query_cache_type參數(shù)變量：

query_cache_type的值為OFF或0時，查詢緩存功能關(guān)閉

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

query_cache_type的值為DEMAND或2時，查詢緩存功能按需進(jìn)行，顯式指定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

優(yōu)化查詢緩存

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

SHOW GLOBAL STATUS LIKE 'Qcache%';

Qcache_free_blocks：處于空閑狀態(tài) Query Cache中內(nèi)存 Block 數(shù)

Qcache_total_blocks：Query Cache 中總的 Block，當(dāng)Qcache_free_blocks相對此值較大時，可能用內(nèi)存碎片，執(zhí)行FLUSH QUERY CACHE清理碎片

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)存容量不夠，需要刪除老的 Query Cache 以給新的 Cache 對象使用的次數(shù)

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

Qcache_queries_in_cache：在 Query Cache 中的 SQL 數(shù)量

命中率和內(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存儲引擎

InnoDB存儲引擎的緩沖池:

通常InnoDB存儲引擎緩沖池的命中不應(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ù)，每次讀取可能需要讀取多個頁

Innodb緩沖池命中率計(jì)算：

平均每次讀取的字節(jié)數(shù)：

索引

索引是特殊數(shù)據(jù)結(jié)構(gòu)：定義在查找時作為查找條件的字段

索引實(shí)現(xiàn)在存儲引擎

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

索引可以降低服務(wù)需要掃描的數(shù)據(jù)量，減少了IO次數(shù)

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

索引可以幫助將隨機(jī)I/O轉(zhuǎn)為順序I/O

缺點(diǎn)：

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

索引類型：

B+ TREE、HASH、R TREE

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

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

稠密索引、稀疏索引：是否索引了每一個數(shù)據(jù)項(xiàng)

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

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

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

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

聚簇索引相當(dāng)于書的目錄，非聚簇索引相當(dāng)于書的附錄

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

MyISAM不支持聚簇索引

B+TREE索引

B+tree索引

B+tree索引

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

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

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

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

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

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

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

只訪問索引的查詢

B-Tree索引的限制：

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

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

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

特別提示：

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

為優(yōu)化性能，可能需要針對相同的列但順序不同創(chuàng)建不同的索引來滿足不同類型的查詢需求

Hash索引

Hash索引：基于哈希表實(shí)現(xiàn)，只有精確匹配索引中的所有列的查詢才有效，索引自身只存儲索引列對應(yīng)的哈希值和數(shù)據(jù)指針，索引結(jié)構(gòu)緊湊，查詢性能好

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

適用場景：

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

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

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

不支持模糊匹配

不支持范圍查詢

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

其他索引

空間索引（R-Tree）：

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

全文索引(FULLTEXT)：

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

聚簇和非聚簇索引

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

冗余和重復(fù)索引：

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

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

重復(fù)索引：已經(jīng)有索引，再次建立索引

索引優(yōu)化策略：

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

左前綴索引：構(gòu)建指定索引字段的左側(cè)的字符數(shù)，要通過索引選擇性來評估

索引選擇性：不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值

多列索引：AND操作時更適合使用多列索引，而非為每個列創(chuàng)建單獨(dú)的索引

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

索引優(yōu)化建議

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

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

對于經(jīng)常在where子句使用的列，最好設(shè)置索引

對于有多個列where或者order by子句，應(yīng)該建立復(fù)合索引

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

盡量不要在列上進(jìn)行運(yùn)算（函數(shù)操作和表達(dá)式操作）

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

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

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

大部分情況連接效率遠(yuǎn)大于子查詢

多表連接時，盡量小表驅(qū)動大表，即小表 join 大表

在千萬級分頁時使用limit

對于經(jīng)常使用的查詢，可以開啟緩存

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

查看慢查詢?nèi)罩?，找出?zhí)行時間長的sql語句優(yōu)化

管理索引

創(chuàng)建索引：

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;

優(yōu)化表空間：

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

查看索引的使用

SET GLOBAL userstat=1;

SHOW INDEX_STATISTICS

EXPLAIN

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

EXPLAIN SELECT clause

獲取查詢執(zhí)行計(jì)劃信息，用來查看查詢優(yōu)化器如何執(zhí)行查詢

輸出信息說明：

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

id: 當(dāng)前查詢語句中，每個SELECT語句的編號

復(fù)雜類型的查詢有三種：

簡單子查詢

用于FROM中的子查詢

聯(lián)合查詢：UNION

注意：UNION查詢的分析結(jié)果會出現(xiàn)一個額外匿名臨時表

select_type：

簡單查詢?yōu)?/span>SIMPLE

復(fù)雜查詢：

SUBQUERY: 簡單子查詢

PRIMARY:最外面的SELECT

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

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

UNION RESULT: 匿名臨時表

table：SELECT語句關(guān)聯(lián)到的表

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

ALL: 全表掃描

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

range：有范圍限制的根據(jù)索引實(shí)現(xiàn)范圍掃描；掃描位置始于索引中的某一點(diǎn)，結(jié)束于另一點(diǎn)

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

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

const, system: 直接返回單個行

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

key: 查詢中使用到的索引

key_len: 在索引使用的字節(jié)數(shù)

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

rows：MySQL估計(jì)為找所有的目標(biāo)行而需要讀取的行數(shù)

Extra：額外信息

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

Using where：MySQL服務(wù)器將在存儲引擎檢索后，再進(jìn)行一次過濾

Using temporary：MySQL對結(jié)果排序時會使用臨時表

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