MariaDB基礎(二)

? 介紹關于MariaDB的如下知識點：

??????? 1. 查詢緩存

??????? 2. 索引

??????? 3. EXPLAIN

??????? 1.查詢緩存：

??????????? 1）什么是緩存？

???????????????????? 緩存就是數據交換的緩沖區，即Cache，存放在內存中；

????????????? 2）查詢緩存的數據以何種形式存在？

???????????????????? 查詢緩存的數據以鍵值對(key/value)的形式存在;

??????????????????????? key??? ： 查詢語句的哈希值（哈希值可理解為數據的×××，用于驗證數據來源的真實性）

??????????????????????? value ： 查詢語句的查詢結果

????????????? 3）緩存命中的標準：

????????????????????? 將查詢語句的哈希值與數據的源哈希值做比較，相同則命中，反之則未命中；

　　　　 4）什么樣的查詢結果不可緩存？

????????????????????? 1. 要查詢的數據庫中包含敏感信息，如MySQL數據庫中的各系統表；

????????????????????? 2. 查詢語句中包含用戶自定義的函數或變量；

????????????????????? 3. 存儲函數；

????????????????????? 4. 對于臨時表發起的查詢請求；

????????????????????? 5. 包含列級別授權的查詢；

????????????????????? 6. 有著不確定值得MySQL內建函數；如：NOW(),CURRENT_DATE()等；

?????????????? 5) 與查詢緩存相關得服務器變量?

?????????MariaDB?[(none)]>?show?global?variables?like?'query_cache%';+------------------------------+---------+|?Variable_name????????|?Value??|+------------------------------+---------+|?query_cache_limit???????|?1048576||?query_cache_min_res_unit???|??4096?||?query_cache_size???????|?0????||?query_cache_strip_comments??|?OFF???||?query_cache_type???????|?ON???||?query_cache_wlock_invalidate|?OFF???|+------------------------------+---------+1.query_cache_limit:查詢緩存限制能夠緩存的最大結果的字節數上限(單語句結果集的上限)注意：在使用select查詢語句時，盡量避免避免“select?*?”盡可能使用where或having子句，是結果盡可能精確；??2.query_cache_min_res_unit：查詢緩存的最小分配單元功能：可有效避免內存碎片注意：較大的變量值可能會造成內存空間的浪費；較小的變量值可能減少內存空間浪費，但會導致頻繁的內存分配和回收操作，長期會造成內存碎片；3.query_cache_size：查詢緩存大小查詢緩存申請的在內存空間的空間大小，單位為字節，大小需是1024的整數倍；示例：MariaDB?[(none)]>?select?10*1024*1024;+--------------+|?10*1024*1024|+--------------+|?10485760???|+--------------+MariaDB?[(none)]>?set?@@global.query_cache_size=10485760;4.query_cache_strip_comments用于控制是否去掉SQL查詢語句中的注釋部分之后再作為key存入查詢緩存；默認值為“OFF”，如果啟用，插入查詢緩存的查詢語句是不帶有注釋信息的；5.query_cache_type：緩存功能開啟與否的開關ON：啟用；僅不緩存"SQL_NO_CACHE"參數的查詢結果；OFF：停用；DEMAND：按需緩存；默認不緩存，僅緩存"SQL_CACHE"參數的查詢結果；6.query_cache_wlock_invalidate：查詢緩存寫鎖無效開關功能：如果某會話對某表施加了寫鎖，是否可以從緩存中查詢并返回查詢結果；OFF：?可以ON：??不可以

??????????????? 6）與查詢緩存相關的服務器狀態參數

??????????MariaDB?[(none)]>?show?global?status?like?'Qcache%';+-------------------------+----------+|?Variable_name??????|?Value??|+-------------------------+----------+|?Qcache_free_blocks????|?1?????||?Qcache_free_memory????|?10468296|??|??Qcache_hits??????|?0?????||?Qcache_inserts?????|?0????||?Qcache_lowmem_prunes??|?0????||?Qcache_not_cached????|?0????||?Qcache_queries_in_cache|?0????||?Qcache_total_blocks???|?1????|+-------------------------+----------+??????1.Qcache_free_blocks:?查詢緩存空閑塊查看目前緩存中有多少個剩余空閑塊若該數值過大，說明內存碎片過多2.Qcache_free_memory:?查詢緩存空閑內存空間如果該值太小，且剩余塊太多，則內存碎片多；如果該值太小，且剩余塊不多，則說明分配查詢緩存的內存空間剛好或偏小；如果該值較大，分配的查詢緩存的內存空間太多，應進行相應調整；一旦調整了緩存大小，則其中存放的查詢結果會立即被清除；3.Qcache_hits:?查詢緩存命中數4.Qcache_inserts:表示未命中的而后經過處理將查詢結果添加至查詢緩存的查詢請求的數量；數值越大，則證明查詢緩存效果越不理想；可以通過規范書寫查詢請求的SQL語句減少此類查詢請求的數量；注意：如果查詢緩存中確實沒有對應查詢語句的查詢緩存，此數值的增加也是正常現象；5.Qcache_lowmem_prunes:查詢緩存低內存修剪該參數記錄了有多少條查詢請求是因為內存空間不足而基于LRU算法移出緩存的；如果該數值多大，則表示為查詢緩存分配的內存空間太小；6.Qcache_not_cached:查詢請求未被緩存取決于query_cache_type變量的設置的作用下，沒有被緩存的查詢請求的數量；7.Qcache_queries_in_cache:當前查詢緩存中緩存的查詢請求的結果的數量；8.Qcache_total_blocks:當前查詢緩存中總計分配了多少個block

???????????

????????? 示例：修改查詢緩存相關服務器變量，查詢數據后，查看服務器狀態參數變化：

???????????????????????????? 設置前狀態參數：

????????????????MariaDB?[(none)]>?show?global?status?like?'Qcache%';+-------------------------+-------+|?Variable_name??????|?Value|+-------------------------+-------+|?Qcache_free_blocks???|?0???||?Qcache_free_memory???|?0???||?Qcache_hits???????|?0???||?Qcache_inserts??????|?0???||?Qcache_lowmem_prunes???|?0???||?Qcache_not_cached????|?0???||?Qcache_queries_in_cache|?0???||?Qcache_total_blocks???|?0???|+-------------------------+-------+

???????

???????? 設置query_cache_size為10485760（該值需為1024的整數倍）

??????????????MariaDB?[(none)]>?select?10*1024*1024;+--------------+|?10*1024*1024|+--------------+|??10485760??|+--------------+MariaDB?[(none)]>?set?@@global.query_cache_size=10485760;MariaDB?[(none)]>?show?global?variables?like?'query_cache%';+------------------------------+----------+|?Variable_name????????|?Value??|+------------------------------+----------+|?query_cache_limit??????|?1048576?||?query_cache_min_res_unit???|?4096???||?query_cache_size??????????|??10485760||?query_cache_strip_comments??|?OFF????||?query_cache_type??????????|?ON????||?query_cache_wlock_invalidate|?OFF????|+------------------------------+----------+

? ? ? ? 查看此時狀態參數：開辟了一個空閑的塊，緩存剩余為10468296

??????????????MariaDB?[(none)]>?show?global?status?like?'Qcache%';+-------------------------+----------+|?Variable_name??????|?Value???|+-------------------------+----------+|?Qcache_free_blocks????|?1?????||?Qcache_free_memory???|??10468296||?Qcache_hits???????|?0?????||?Qcache_inserts?????|?0?????||?Qcache_lowmem_prunes???|?0?????||?Qcache_not_cached????|??0?????||?Qcache_queries_in_cache|?0?????||?Qcache_total_blocks???|?1?????|+-------------------------+----------+

??????? 使用名為hellodb的數據庫，用select語句查看庫中students表的信息，查看狀態參數變化

??????? 如下：緩存剩余空間變為10466128

????????????????? 因為第一次請求所以沒有命中緩存，即Qcache_hits=0；

????????????????? 因為有一個select請求，所以將該請求列入緩存，即Qcache_inserts=1；

????????????????? 未緩存為1，即Qcache_not_cached=1

????????????????? 請求數為1,即Qcache_queries_in_cache=1

???????????????? 分配塊數為4,即Qcache_total_blocks=4

???????????MariaDB?[(none)]>?use?hellodb;MariaDB?[hellodb]>?select?*?from?students;MariaDB?[hellodb]>?show?global?status?like?'Qcache%';+-------------------------+----------+|?Variable_name??????|?Value???|+-------------------------+----------+|?Qcache_free_blocks????|?1?????||?Qcache_free_memory???|?10466128||?Qcache_hits???????|?0?????||?Qcache_inserts??????|?1?????||?Qcache_lowmem_prunes??|?0????||?Qcache_not_cached????|?1?????||?Qcache_queries_in_cache|?1?????||?Qcache_total_blocks????|?4????|+-------------------------+----------+

????????? 再次請求查看：

????????? 緩存空間變小

????????? 命中數為2， 即Qcache_hits=2

????????? (因為之前相同的select請求被列入緩存，所以接下來兩次請求都命中)

??????????MariaDB?[hellodb]>?select?*?from?students;MariaDB?[hellodb]>?select?*?from?students;MariaDB?[hellodb]>?show?global?status?like?'Qcache%';+-------------------------+----------+|?Variable_name??????|?Value???|+-------------------------+----------+|?Qcache_free_blocks????|?1?????||?Qcache_free_memory????|?10466128||?Qcache_hits????????|?2?????||?Qcache_inserts??????|?1?????||?Qcache_lowmem_prunes???|?0?????||?Qcache_not_cached????|?1?????||?Qcache_queries_in_cache|??1?????||?Qcache_total_blocks???|?4?????|+-------------------------+----------+

??
????????????? 7) 如何優化緩存：

????????????????????? 1：盡可能的批量寫入（構建自定義過程，啟用事務），盡量減少多次的單寫入操作；
????????????????????? 2：緩存空間不宜設置過大，如果大量緩存同時失效，會使MySQL的執行引擎壓力過大，可能導致服務器假死；
????????????????????? 3.?? 必要時，必須使用SQL_CACHE和SQL_NO_CACHE等SELECT語句中的參數手動控制緩存存入與否；
????????????????????? 4.??? 對于密集型寫操作應用場景來說，禁用緩存功能可能提升服務器性能；

???????????

???????? 2. 索引

???????????? 1）什么是索引？

????????????????????? 指的是表中的數據子集；
????????????????????? 即：將表中的某個或某些字段中的數據提取出來，另存為一個用一個特定數據結構進行組織的數據；

?????????????? 2）索引的功能

????????????????????? 加速查詢操作

???????????? 3） 索引的類型

????????????????????? FULLTEXT，SPACIAL，B+ TREE，HASH

?????????????????????? B+ TREE索引:
????????????????????????? 順序存儲，所有的索引數據都放在葉節點上，并且每個葉節點都有順序訪問指針，
???????????????????????????? ? ? ? ? ? ? ? 以此指針指向相鄰的葉子節點，可提高區間數據的查詢效率；

????????????????????????? 最左前綴索引：適用于高效的范圍類數據查詢；

??????????????????? ? ??? 適用的場景：
?????????????????????????????? 全鍵值匹配：精確匹配某個值；
????????????????????????? ? ???? select .... where Name='zhangsan';
?????????????????????????????? 左前綴匹配：只精確到數據起始位置的一部分；
???????????????????????????????? select ...?? where Name like 'zhangsan%';
?????????????????????????????? 區間數據的連續數值匹配：通常用于BETWEEN...AND...環境中；
???????????????????????????????? select ...? where Age between 20 and 30;
?????????????????????????????? 區間數據的離散值匹配：通常用于IN列表環境或OR列表環境；也是精確匹配；
???????????????????????????????? select ...? where StuID in (1,2,4);
????????????????????????????? 精確匹配左列，范圍匹配右側其它列：
???????????????????????????????? select ...? where StuID? > 10 and Name like 'a%';
?????????????????????????????????????????????

? ? ? ? ? ? ? ? ? ? ? ???? 對于覆蓋索引的查詢請求；
? ? ? ? ? ? ? ? ? ? ? ?? ? 不適用的場景：
?????????????????????????????? 如果查詢條件不是精確從最左側列開始的，索引無效；
???????????????????????????????? 如：對StuID字段做了索引，select ... where Name like 'a%' and StuID >10;
?????????????????????????????? 如果索引了多列，若跳過了索引中的某列，則索引無效：
???????????????????????????????? 如：對StuID,Name,Age做索引，select ... where StuID>10 and Age>20;
???????????????????????? ? ??? 如果索引了多列，且在查詢語句中某個列做范圍匹配，則右側列不再使用索引優化查詢：

? ? ? ? ? ? ??

?????????????? ? ? ? HASH索引：基于HASH表實現的索引；
???????????????????????? 非常適用于值的精確匹配的查詢請求；
???????????????????????? 適用場景：
???????????????????????????? 只支持等值比較查詢：
???????????????????????????? 如：=,IN(),<=>(NULL safe equal)?? ;

???????????????????????? 不適用的場景：
???????????????????????? ? ? 所有的非精確值的比較查詢；

???????????????????????? 注意：
???????????????????????????? 1.在InnoDB存儲引擎中，創建索引時，只能顯式使用“BTREE”索引；
???????????????????????????? 2.索引中的數據來源于數據表，但數據結構與源數據可能有很大差異；

???????????? 4）索引的查詢優點：
??????????????????????? 1.? 減少需要掃描的數據總量，減少IO次數
??????????????????????? 2.? 避免對掃描的數據進行再次排序；
??????????????????????? 3.? 避免生成和使用臨時表；
??????????????????????? 4.? 將隨機IO轉換為順序IO；

???????????? 5） 定義索引的一般性規則：
?????????????????????? 1.選擇用于索引的數據類型；
???????????????????????????? 越小的數據類型越適于做索引；例如int和char
???????????????????????????? 越簡單的數據類型越適于做索引；例如 char 和varchar
???????????????????????????? 盡量避免該字段中出現“NULL”值；如果必須要使用空值，建議使用“0”或一個空的字符串
???????????????????????????? 或某個認可的特殊值來代替“NULL”值；?????????????????? ?
????????????????? ?
??????????????????????? 2.選擇主鍵的類型：
????????????????????????????? 優先選擇整型；
????????????????????????????? 整型數據可以更快速被處理，且可以使用AUTO_INCREMENT修飾符避免重復數據；
????????????????????????????? 盡量避免使用字符型；
????????????????????????????? 存儲字符型數據需要消耗更多空間，處理字符型數據需要消耗更多的CPU和內存資源，處理速度較慢；
????????????????????????????? 可能導致頁面分裂，隨機IO等問題；

? ? ? ? ? ? ? ? ? ? ?? ????????????????????????????????????????? ?????????????

???????? 3. EXPLAIN

???????????????? 查看幫助文檔：

??????????MariaDB?[hellodb]>?help?explain;

?????????????????? 格式：explain_type: EXTENDED| PARTITIONS Or: EXPLAIN tbl_name

?????????????????? 示例：

?????MariaDB?[hellodb]>?explain?select?*?from??students?where?StuID<30\G;***************************?1.?row?***************************id:?1select_type:?SIMPLEtable:?studentstype:?rangepossible_keys:?PRIMARYkey:?PRIMARYkey_len:?4ref:?NULLrows:?26Extra:?Using?where1.id：當前的查詢語句中，各個select語句的編號；2.select_type:查詢類型簡單查詢：SIMPLE復雜查詢：簡單的子查詢(用于where子句中的子查詢)：SUBQUERY用于FROM語句中的子查詢：DERIVED聯合查詢中的第一個查詢：PRIMARY；聯合查詢中其它的查詢：UNION聯合查詢時生成的臨時表查詢：UNION　RESULT3.table:?當前的查詢語句所針對的表；4.type:??關聯類型，或稱訪問類型，也可理解為MySQL是如何查詢表中的行；1）?ALL：全表掃描，MySQL將遍歷全表找到可以匹配的行；2）?index：全表掃描，與ALL不同的是index類型只是遍歷索引樹；3）?range：索引范圍掃描，對索引的掃描從某一個點開始，返回匹配值域的行；通常可以基于指定的索引，where子句中使用IN列表，BETWEEN...AND....或帶有“=”，“>","<"的查詢；4）?ref:?使用非唯一索引掃描或使用唯一索引的做左前綴掃描，返回匹配某個單獨值得行；5）?eq_ref:?類似ref,區別是使用唯一索引，對于每一個索引鍵值，表中都只有一條記錄匹配；無論是單表查詢還是多表查詢，都使用主鍵或唯一鍵索引作為關聯條件；6）?const,system:?當MySQL對查詢部分進行優化，并轉換為一個常量；使用const類型system類型是一個const類型得特例，當要查詢得表是一行時，使用system類型;7）?NULL?:?MySQL在優化過程中分解查詢語句，執行時不用訪問表或索引；5.possible_keys:?為了執行查詢語句，MySQL可能使用哪個索引在表中查找到記錄；如果查詢所涉及到的字段上，如果存在索引，則該索引被列出，但不一定被查詢使用；6.?key:??顯示MySQL數據庫在查詢過程中實際使用到的索引；如果查詢過程沒有用到任何索引，則此處顯示“NULL”；7.?key_len:?表示索引中可以被引用的最大字節數；可通過該列計算查詢中使用的索引的長度；注意：key_len顯示的值通常為索引字段的最大可能長度，并非實際使用長度，因此key_len是根據表定義時指定的字段長度計算得到的，并不是在表中通過通過檢索數據得到的；8.?ref:??在利用key字段所顯示的索引完成查詢操作時所引用的列或常量值；若果都沒有則顯示為NULL；??9.?rows:?表示MySQL根據表統計信息及索引選用的情況，估算的本次檢索所需要查找所有記錄的過程中需要讀取表的行數；10.Extra：額外信息，或稱擴展信息；Using?where：?表示MySQL服務器將在存儲引擎檢索后再次進行條件過濾；在許多的where條件里涉及到索引中的列并且當MySQL讀取該索引時就可被存儲引擎檢索；Using?index：??使用了覆蓋索引進行檢索；Using?temporary：在查詢過程中使用了臨時表存放查詢結果集；Using?filesort：?MySQL中無法利用索引完成的排序操作就稱為“文件排序”；Using?join?buffer：?強調了在獲取連接條件時沒有使用到索引，且需要連接緩沖區來存儲中間結果；如果出現了該值，需要根據查詢的具體情況適當的添加索引以提示查詢性能；Impossible?where：若該值出現，則意味著在查詢時沒有發現符合條件的行；Select?tables?optimized?away：?該值意味著僅通過使用索引，來進行查詢，但是優化器可能從聚合函數的結果中給出一個可行優化方案；11.filtered：從可選的行中再次過濾之后選擇出最終的查詢結果的比值；即從多少行中過濾選擇出多少行的比值；

????????

???????

?????????????????????

?????