MySQL的索引：

• 索引：定義在查找時作為查找條件的字段上，構(gòu)建出的一個獨特的數(shù)據(jù)結(jié)構(gòu)；
  • 基本法則：應(yīng)該構(gòu)建在經(jīng)常被用作查詢條件的字段上
  • 作用：加速查詢操作
  • 副作用：占用額外空間，降低寫操作性能
  • 索引實現(xiàn)在存儲引擎
    • 表中數(shù)據(jù)子集：把表中某個或某些字段的數(shù)據(jù)提取出來另存為一個特定數(shù)據(jù)結(jié)構(gòu)組織的數(shù)據(jù)
    • 某個字段或某些字段：WHERE子集中用到的字段
• 索引的類型：B+ TREE，HASH
  • B+ TREE：
    • 順序存儲：
      常見的數(shù)據(jù)存儲格式有兩種：一種是線性存儲也即順序的，第二種叫堆存儲，雜亂無章的存儲；
      B樹索引指定就是平衡樹索引，它存在的目的：是能夠加快數(shù)據(jù)訪問速度；如果沒有索引，一次數(shù)據(jù)查詢過程就要全表掃描實現(xiàn)；但有了B樹索引，首先查詢過程就變成了，先去查詢?nèi)頀呙韪?jié)點，注意是從索引的根節(jié)點進行開始，再找出二級節(jié)點，從根節(jié)點找出一個指針，而后找出下一節(jié)點即二級分支節(jié)點找出后，再找三級分支節(jié)點，最終找到葉子節(jié)點；而葉子節(jié)點則有可能是指向最終數(shù)據(jù)所存放的數(shù)據(jù)塊的指針，從而找到數(shù)據(jù)；
    • 每個葉子節(jié)點到根節(jié)點的距離相同：
      對于B樹索引，每一個分支節(jié)點都兩部分組成，一部分存儲的是自己的數(shù)據(jù)是什么、下一級節(jié)點的指針指向的位置，另一部分是一個指針，指向了同一級節(jié)點的下一個節(jié)點的位置；這些都是在內(nèi)存空間中存儲的內(nèi)容；
      所以在每一個層級上，它們都是獨立的由左而右按順序存儲完成以后，一個鏈表結(jié)構(gòu)的數(shù)據(jù)；
      根節(jié)點和二級、三級節(jié)點都是稀疏格式的索引，葉子節(jié)點是稠密格式的；在字段上構(gòu)建了索引，字段中的每一行的值，都有葉子節(jié)點，所以找到某一葉子節(jié)點以后，這個葉子節(jié)點就對應(yīng)行實際存儲的位置，從而找到數(shù)據(jù)的；
    • 左前綴索引，適合于范圍類型的數(shù)據(jù)查詢
  • 適用于B+ TREE 索引的查詢類型：全鍵值、鍵值范圍或鍵前綴查找
    • 全值匹配：精確查找某個值；WHERE COLUMN = 'value';
    • 匹配最左前綴：只精確匹配起頭部分；WHERE COLUMN LIKE 'PREFIX%';
    • 匹配范圍值： 精確酦醅某一列，范圍匹配另外一列；
    • 只用訪問索引的查詢叫做覆蓋索引；index(name) SELECT Name FROM students WHERE Name LIKE 'L%';
  • 不適用B+ TREE索引：
    • 如果查詢條件不是最左側(cè)列開始，索引無效；
    • 不能跳過索引中的某列；
    • 如果查詢中的某列是為范圍查詢，那么其右側(cè)的列都無法再使用索引優(yōu)化查詢
  • Hash索引：基于哈希表實現(xiàn)，特別使用與值得精確匹配查詢；
    • 適用場景：只支持等值比較查詢
    • 不適用場景：所有非精確值查詢；MySQL僅對memory存儲引擎支持顯示的hash索引；
• 索引有點：
  • 降低需要掃描的數(shù)據(jù)量，減少IO次數(shù)
  • 可以幫助避免排序操作，避免使用臨時表
  • 幫助將隨機IO轉(zhuǎn)為順序IO
• 高性能索引策略：
  • (1) 在WHERE 中獨立使用列，盡量避免其參與運算；
  • (2) 左前綴索引：索引構(gòu)建與字段的最左側(cè)的多少個字符，要通過索引選擇性來評估;
    • 索引選擇性：不重復(fù)的索引和數(shù)據(jù)表的記錄總數(shù)的比值；
  • (3)多列索引：AND 連接的多個查詢條件更適合使用多列索引，而非多個單建索引；
  • (4) 選擇合適的索引列次序，選擇性最高的放左側(cè)；

EXPLAIN來分析索引有效性

用法：EXPLAIN [explain_type] SELECT select_options

    explain_type:
        EXTEBDED | PARTITIONS

示例：MariaDB [hellodb]> EXPLAIN SELECT * FROM students\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: students
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 25
            Extra: 
    1 row in set (0.00 sec)

• id：當前查詢語句中，第幾個SELECT語句的編號
  復(fù)雜的查詢的類型主要三種：
  • 簡單子查詢
  • 用于FROM中的子查詢
  • 聯(lián)合查詢
  • 注意： 聯(lián)合查詢的分析結(jié)果會出現(xiàn)一個額外的匿名臨時表
• select_type：查詢類型
  • 簡單查詢：SIMPLE
  • 復(fù)雜查詢：
    • 簡單子查詢：SUBQUERY
    • 用于FROM中的子查詢：DERIVED
    • 聯(lián)合查詢中的第一個查詢：PRIMARY
    • 聯(lián)合查詢中的第一個查詢之后的其它查詢：UNION
    • 聯(lián)合查詢生成的臨時表：UNION RESULT
• table：查詢針對的表
• type：關(guān)系類型，或稱為訪問類型，即MySQL如何與查詢表中的行
  • ALL：權(quán)標掃描
  • index：根據(jù)索引的順序進行的權(quán)標掃描；但同時如果Extra列出現(xiàn)了“Using index”表示使用了覆蓋索引
  • range：有范圍限制地根據(jù)索引實現(xiàn)范圍掃描；掃描位置始于索引中的某一項，結(jié)束于另一項；
  • ref：根據(jù)索引返回的表中匹配到某單個值的所有行（匹配給定值的行不止一個）；
  • eq_ref：根據(jù)索引返回的表中匹配到某單個值的單一行，僅返回一個行，但需要與某個額外的參考值比較，而不是常數(shù)；
  • const，system：與某個常數(shù)比較，且只返回一行；
• possiable_keys：查詢中可能會用到的索引；
• key：查詢中使用的索引；
• key_len：查詢中用到的索引長度；
• ref：在利用key字段所顯示的索引完成查詢操作時所引用的列或常量值；
• rows：MySQL估計出的為找到所有的目標項而需要讀取的行數(shù)；
• Extra：額外信息
  • Using index：使用了覆蓋索引進行的查詢；
  • Using where：拿到數(shù)據(jù)后還要再次進行過濾；
  • Using temporary：使用了臨時表以完成查詢；
  • Using filesort：對結(jié)果使用了一個外部索引排序；

注意：判斷索引是否有效：
type類型中，all最差-->index-->range-->ref-->eq_ref-->const,system最好
一般保持在range上，避免冗余索引，移除無用索引；

示例：
    MariaDB [hellodb]> CREATE INDEX age ON students(Age);
    Query OK, 25 rows affected (0.02 sec)
    Records: 25  Duplicates: 0  Warnings: 0
    
    MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE Age>100;
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    | id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    |    1 | SIMPLE      | students | range | age           | age  | 1       | NULL |    1 | Using index condition |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    1 row in set (0.00 sec)
：
        MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE Age=100;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
|    1 | SIMPLE      | students | ref  | age           | age  | 1       | const |    1 |       |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE StuID=3;
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

查詢緩存

mysql把整個查詢緩存維持在內(nèi)存中，內(nèi)存空間的頻繁的創(chuàng)建和回收，會導(dǎo)致內(nèi)存碎片，類似于memcached一樣，必須有個高效的內(nèi)存分配回收算法，以盡可能降低內(nèi)存碎片；
查詢緩存是完全存儲在內(nèi)存中的，對整個內(nèi)存空間的分配回收等，也會額外的產(chǎn)生系統(tǒng)資源消耗，此外，為了能夠使用內(nèi)存空間當做緩存，mysql服務(wù)器的查詢緩存，還必須把一些內(nèi)存空間拿來做緩存空間的元數(shù)據(jù)，例如整個空間中哪些分配了、哪些沒分配等等，內(nèi)置需要維護一個追蹤表以始終得知哪些空間仍在在使用，哪些空間沒被使用，一旦要有可能產(chǎn)生碎片時，怎么能降低碎片的產(chǎn)生等等；
不是所有場景中緩存查詢都能提高性能的；緩存和失效都會帶來額外開銷，所以只有當帶來的收益大于開銷時，才使用查詢緩存；
可通過緩存命中率來判斷緩存是否足夠有效；而命中率除了查詢語句的命中率之外，還有結(jié)果集的命中率；雖然查詢語句10個里，只有2個命中，但是這2個里結(jié)果集都很大處理起來都很慢，這個命中仍然認為收益是較大的；所以，也不能簡單的以為語句命中率少就一定是命中率低；
內(nèi)存空間不足，數(shù)據(jù)修改等都會造成緩存失效，如果配置了足夠的緩存空間，而且對應(yīng)的參數(shù)設(shè)置合理，緩存失效就只應(yīng)該是數(shù)據(jù)修改導(dǎo)致的；所以要配置好查詢緩存，避免緩存失效是因為空間過少或產(chǎn)生過多空間碎片導(dǎo)致緩存失效；

• 緩存：k/v;
  任何緩存都是鍵值存放的，而緩存鍵放在哈希表中，當再次查詢是，沒一個查詢語句會被同樣做哈希計算，并查找表中是否有與之對應(yīng)的條目，如果有把對應(yīng)的鍵所指向的值直接返回給客戶端，否則就表示緩存未命中

  • key：查詢語句的hash值
  • value：查詢語句的執(zhí)行結(jié)果

• 如何判斷緩存是否命中：

  • 通過查詢語句的哈希值判斷；

    • 哈希值考慮的因素：查詢本身、要查詢數(shù)據(jù)庫、客戶端使用的協(xié)議版本、...

    例如：SELECT Name FROM students WHERE StuID=3;
    select Name From students where StuID=3;兩個查詢語句的hash值是不一樣的

• 哪些查詢可能不會被緩存：

  • 查詢語句中包含UDF（UDF:用戶自定義函數(shù)：user define function）
  • 存儲函數(shù)
  • 用戶自定義變量
  • 臨時表
  • mysql系統(tǒng)表或者是包含列級別權(quán)限的查詢
  • 有著不確定結(jié)果值得函數(shù)（now()）

• 查詢緩存相關(guān)的服務(wù)器變量：>SHOW GLOBAL VARIABLES LIKE '%query%';

  • query_cache_min_res_unit：查詢緩存中內(nèi)存塊的最小分配單位；緩存過小的查詢結(jié)果集會浪費內(nèi)存空間；單位字節(jié)

    • 較小的值會減少空間浪費，但會導(dǎo)致更頻繁地內(nèi)存分配及回收操作；
    • 較大值的會帶來空間浪費；

  • query_cache_limit：mysql能夠緩存的最大查詢結(jié)果；單語句結(jié)果集大小上限）； 有著較大結(jié)果集的語句，顯式使用SQL_NO_CACHE，以避免先緩存再移出；

  • query_cache_size：查詢緩存空間的總共可用的大?。粏挝皇纸?，必須是1024的整數(shù)倍

  • query_cache_type：緩存功能啟用與否；

    • ON：啟用
    • OFF：禁用
    • DEMAND：表示只有在查詢時明確寫明SQL_CACHE才緩存，其他都不緩存

  • query_cache_wlock_invalidate：如果某表被其它的連接鎖定，是否仍然可以從緩存中返回查詢結(jié)果；默認為OFF,表示可以；ON則表示不可以；

    MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%query%';
    +------------------------------+--------------------+
    | Variable_name                | Value              |
    +------------------------------+--------------------+
    | expensive_subquery_limit     | 100                |
    | ft_query_expansion_limit     | 20                 |
    | have_query_cache             | YES                |
    | long_query_time              | 10.000000          |
    | query_alloc_block_size       | 8192               |
    | query_cache_limit            | 1048576            |
    | query_cache_min_res_unit     | 4096               |
    | query_cache_size             | 4096000            |
    | query_cache_strip_comments   | OFF                |
    | query_cache_type             | ON                 |
    | query_cache_wlock_invalidate | OFF                |
    | query_prealloc_size          | 8192               |
    | slow_query_log               | OFF                |
    | slow_query_log_file          | localhost-slow.log |
    +------------------------------+--------------------+
    14 rows in set (0.00 sec)
    

• 優(yōu)化緩存

  
  
  mysql優(yōu)化緩存查詢.png

判斷是否因為大部分查詢都是不可緩存的導(dǎo)致不能命中；如果是這個原因?qū)е碌模瑒t判斷是否是因為query_cache_limit（查詢結(jié)果最大上限）設(shè)置不夠大導(dǎo)致不能緩存，如果不能緩存的原因是因為查詢緩存的結(jié)果集超出了緩存所設(shè)定的上限導(dǎo)致，則增加query_cache_limit的值即可；如果不是因為query_cache_limit設(shè)定的值不夠大導(dǎo)致的，則查詢不能被緩存；

如果大部分查詢結(jié)果都是可緩存的但是沒有被緩存（緩存命中率不能被接受），判斷是否發(fā)生了很多驗證工作（驗證緩存是否有效），如果是則判斷緩存是否碎片化了，如果是碎片化了則要降低query_cache_min_res_unit參數(shù)值以減少碎片或使用FLUSH QUERY CACHE命令整理緩存；
如果緩存不是因為碎片過多導(dǎo)致的，則判斷是否因為緩存空間過低而發(fā)生修正，就是緩存空間小，導(dǎo)致緩存被頻繁清空，所導(dǎo)致緩存不能命中，如果是這個原因則增加query_cache_size參數(shù)值即可；
如果不是因為緩存空間太小導(dǎo)致緩存無法命中，則判斷是否頻繁更新語句，如果是表示為負載并不適合緩存則關(guān)閉緩存；如果不是頻繁更新語句導(dǎo)致緩存失效，則可能是配置錯誤；

各種原因都不是，沒有碎片、又不是不可緩存的、又沒有發(fā)生驗證工作、又沒有被碎片化、sql語句又沒有頻繁更新則可認為是其它配置錯誤導(dǎo)致；

如果不是發(fā)送很多驗證工作，則判斷緩存是否啟動，如果緩存啟動，則從沒見過該查詢；如果緩存沒啟動則啟動緩存即可；

• 查詢相關(guān)的狀態(tài)變量：SHOW GLOBAL STATUS LIKE 'Qcache%';
  • Qcache_free_blocks：空閑內(nèi)存塊，緩存失效了還沒被回收，可被下一次存儲緩存查詢結(jié)果繼續(xù)使用；free_blocks這是內(nèi)存空間被分隔成block以后仍然處于空閑的塊；
    注意：真正查詢緩存所能夠得到的內(nèi)存空間未必是連續(xù)的內(nèi)存空間；
  • Qcache_free_memory：空閑的內(nèi)存空間；
  • Qcache_hits：緩存命中
  • Qcache_inserts：向緩存中插入查詢結(jié)果的次數(shù)，就是把可緩存查詢語句的結(jié)果被放入緩存的次數(shù)；
  • Qcache_lowmem_prunes： 表示有多少次因為查詢緩存空間太少，而不得不利用LRU算法清理緩存空間的次數(shù)；此值過大通常表示由query_cache_size太小導(dǎo)致的；
  • Qcache_not_cached：可緩存卻沒能被緩存的結(jié)果；
  • Qcache_queries_in_cache： 當前查詢緩沖空間中被緩存下來查詢語句的個數(shù)；例如16M的查詢緩存空間能緩存1000個查詢結(jié)果個數(shù)；
  • Qcache_total_blocks：整個查詢緩存一共有多少個內(nèi)存塊；注意：個個內(nèi)存塊不是分割以后能夠緩存內(nèi)存查詢結(jié)果的內(nèi)存塊，而指的是內(nèi)存區(qū)段；

注意：query_cache_size調(diào)整的話，整個緩存空間必須重新進行分配，所以整個緩存都會被失效的；因此，最好不要輕易調(diào)整此值；
如果要使用memcached來緩存數(shù)據(jù)，mysql的緩存就顯得不那么重要了；可自行決定繼續(xù)開啟還是關(guān)閉；

• 緩存命中率的評估：Qcache_hits/(Qcache_hists+Com_select)
  • Com_select：記錄的并不是所有的查詢語句，僅記錄了由mysql執(zhí)行的查詢語句，如果從緩存命中了此值是不會增加的；