[TOC] 有了索引，我們根據索引為條件進行數據查詢速度就非常快 ① 索引本身有”算法”支持，可以快速定位我們要找到的關鍵字(字段) ② 索引字段與物理地址有直接對應，幫助我們快速定位要找到的信息 一個數據表的全部字段都可以設置索引 # 類型 四種類型： ① 主鍵 primary key auto_increment必須給主鍵索引設置 信息內容要求不能為null，唯一 ② 唯一 unique index 信息內容不能重復 ③ 普通 index 沒有具體要求 ④ 全文 fulltext index myisam數據表可以設置該索引 復合索引：索引關聯的字段是多個組成的，該索引就是復合索引。  # 添加索引  創建一個復合索引： 索引沒有名稱，默認把第一個字段取出來當做名稱使用。  # 刪除索引 alter table 表名 drop primary key; //刪除主鍵索引 注意：該主鍵字段如果存在auto_increment屬性，需要先刪除之 alter table 表名 modify 主鍵 int not null comment ‘主鍵’; 去除數據表主鍵字段的auto_increment屬性：  # 執行計劃 針對查詢語句設置執行計劃，當前數據庫只有查詢語句支持執行計劃。 每個select查詢sql語句執行之前，需要把該語句需要用到的各方面資源都計劃好 例如：cpu資源、內存資源、索引支持、涉及到的數據量等資源 查詢sql語句真實執行之前所有的資源計劃就是執行計劃。 我們討論的執行計劃，就是看看一個查詢sql語句是否可以使用上索引。 具體操作： explain 查詢sql語句\G; 1條 sql語句在沒有執行之前，可以看一下執行計劃。  # 索引覆蓋 給ename和job設置一個復合索引  索引覆蓋：我們查詢的全部字段(ename,job)已經在索引里邊存在，就直接獲取即可 不用到數據表中再獲取了。因此成為”索引覆蓋” 該查詢速度非常快，效率高，該索引也稱為”黃金索引” 索引本身需要消耗資源的(空間資源、升級維護困難)：// .MYI 文件 # 索引原則 ## 使用ENUM而不是字符串 ENUM保存的是TINYINT，別在枚舉中搞一些“中國”“北京”“技術部”這樣的字符串，字符串空間又大，效率又低。 ## 負向條件查詢不能使用索引 ~~~ select * from order where status!=0 and stauts!=1 ~~~ not in/not exists 都不是好習慣 可以優化為 in 查詢： ~~~ select * from order where status in(2,3) ~~~ ## 數據區分度不大的字段不宜使用索引 ~~~ select * from user where sex=1 ~~~ 原因：性別只有男，女，每次過濾掉的數據很少，不宜使用索引。 經驗上，能過濾 80% 數據時就可以使用索引。對于訂單狀態，如果狀態值很少，不宜使用索引，如果狀態值很多，能夠過濾大量數據，則應該建立索引。 ## 字段獨立原則 select * from emp where empno=1325467; //empno條件字段獨立 select * from emp where empno+2=1325467; //empno條件字段不獨立 只有獨立的條件字段才可以使用索引 獨立的條件字段可以使用索引, 不獨立的條件字段不給使用索引 ## 左原則 模糊查詢，like % _ %：關聯多個模糊內容 _: 關聯一個模糊內容 select * from 表名 like “beijing%”; //使用索引 select * from 表名 like “beijing_”; //索引索引 查詢條件信息在左邊出現，就給使用索引 XXX% YYY_ 使用索引 %AAA% _ABC_ %UUU 不使用索引 沒有使用索引(中間條件查詢)：  ## 復合索引 ename復合索引 內部有兩個字段（ename，job） ① ename(前者字段)作為查詢條件可以使用復合索引 ② job(后者字段)作為查詢條件不能使用復合索引 復合索引的第一個字段可以使用索引：  ## or原則 OR左右的關聯條件必須都具備索引 才可以使用索引：  ## 前綴索引 設計索引的字段，不使用全部內容，而只使用該字段前邊一部分內容。 如果字段的前邊N位的信息已經可以足夠標識當前記錄信息，就可以把前邊N位信息設置為索引內容，好處：索引占據的物理空間小、運行速度就非常快。 具體實現： ① 操作 alter table 表名 add index (字段(位數)) ② 前邊到底取得多少位，才是記錄的唯一標識 總記錄數目/前n位記錄數目 = 比值; ~~~ select count(*) from 表名; ~~~ mysql字符串截取：substring(字段,開始位置1開始，長度)  ## 設計原則 字段內容需要足夠花樣,性別字段不適合做索引 ## 全文索引 ### 什么是全文索引 ① 其他索引是把字段的內容作為一個整體進行索引設計 ② 全文索引，是把內容中的一些“單詞”拆分出來作為索引字段使用。 類似我們有一篇作文，把作文中的一些關鍵字給獲取出來當成是索引內容  全文索引可以幫助我們解決：模糊查詢不能使用索引的問題 ### 具體操作 Mysql5.5 Myisam存儲引擎 支持全文索引 Mysql5.6 Myisam和Innodb存儲引擎 都支持全文索引 目前中文不支持全文索引。  ### 注意 ① 字段類型必須為varchar/char/text類型 ② mysql 5.6.4之前只有Myisam支持，5.6.4之后則Myisam和innodb都支持。 ③ mysql中的全文索引目前只支持英文(不支持中文)，如果需要支持中文可以使用sphinx ④ 生產活動中mysql的全文索引不常使用，可以通過sphinx代替 ⑤ mysql全文索引會自作聰明，對關鍵字的收錄有自己的考慮。 例如生活常用單詞、頻繁使用單詞都不給創建索引(比如 for when where run等等),會導致有些查詢會失敗查不到 # 前綴索引 通過字段前n位創建的索引就稱為“前綴索引”。 如果一個字段內容的前邊的n位信息已經足夠標識當前的字段內容，就可以把字段的前n位獲得出來并創建索引，該索引占據空間更小、運行速度更快 制作前綴索引語法： ~~~ alter table 表名 add key (字段(前n位位數)) ~~~ 現在思考： 到底前幾位可以唯一標識字段的內容？ 獲取制作前綴索引的n的信息： ① 去除字段重復內容并計算總數目 ② 取字段的前(n)1、2、3.....位不重復的信息并計算總數目，n從1開始不斷累加，直到總數目 與 ①計算的總數目相等，此時n就是我們設計前綴索引的數字n信息 mysql中截取字段的前n位信息，使用函數left(字段,長度) 例如截取前5位信息： left(字段,5) ## 制作前綴索引 計算全部字段不重復記錄的總條數 ~~~ select count(distinct epassword) from emp; 結果 : 1218365 ~~~ ~~~ 計算前n位不重復記錄的總條數，n從1開始累加 select count(distinct left(epassword,9)) from emp; 結果:1215663 select count(distinct left(epassword,10)) from emp; 結果:1218355 select count(distinct left(epassword,11)) from emp; 結果:1218365 ~~~ 可以看到epassword的前11位信息，可以唯一標識字段內容 創建前綴索引： alter table emp add key (epassword(11)) # 如何選擇合適的列建立索引  看離散度  # 建索引注意 不要建立重復索引  --- 冗余索引  因為innodb表中每個索引他都會在后面加上主鍵索引,再加上就冗余了 --- 查找重復及冗余索引  --- 刪除不用索引  # 索引設計 ## 磁盤IO 一個數據庫必須保證其中存儲的所有數據都是可以隨時讀寫的，同時因為 MySQL 中所有的數據其實都是以文件的形式存儲在磁盤上的，而從磁盤上隨機訪問對應的數據非常耗時，所以數據庫程序和操作系統提供了緩沖池和內存以提高數據的訪問速度。  除此之外，我們還需要知道數據庫對數據的讀取并不是以行為單位進行的，無論是讀取一行還是多行，都會將該行或者多行所在的頁全部加載進來，然后再讀取對應的數據記錄；也就是說，讀取所耗費的時間與行數無關，只與頁數有關  在 MySQL 中，頁的大小一般為 16KB，不過也可能是 8KB、32KB 或者其他值，這跟 MySQL 的存儲引擎對數據的存儲方式有很大的關系，文中不會展開介紹，**不過索引或行記錄是否在緩存池中極大的影響了訪問索引或者數據的成本。** ### 隨機讀取 數據庫等待一個頁從磁盤讀取到緩存池的所需要的成本巨大的，無論我們是想要讀取一個頁面上的多條數據還是一條數據，都需要消耗約 10ms 左右的時間：  10ms 的時間在計算領域其實是一個非常巨大的成本，假設我們使用腳本向裝了 SSD 的磁盤上順序寫入字節，那么在 10ms 內可以寫入大概 3MB 左右的內容，但是數據庫程序在 10ms 之內只能將一頁的數據加載到數據庫緩沖池中，從這里可以看出隨機讀取的代價是巨大的  這 10ms 的一次隨機讀取是按照每秒 50 次的讀取計算得到的，其中等待時間為 3ms、磁盤的實際繁忙時間約為 6ms，最終數據頁從磁盤傳輸到緩沖池的時間為 1ms 左右，在對查詢進行估算時并不需要準確的知道隨機讀取的時間，只需要知道估算出的 10ms 就可以了。 ### 內存讀取 如果在數據庫的緩存池中沒有找到對應的數據頁，那么會去內存中尋找對應的頁面：  當對應的頁面存在于內存時，數據庫程序就會使用內存中的頁，這能夠將數據的讀取時間降低一個數量級，將 10ms 降低到 1ms；MySQL 在執行讀操作時，會先從數據庫的緩沖區中讀取，如果不存在與緩沖區中就會嘗試從內存中加載頁面，如果前面的兩個步驟都失敗了，最后就只能執行隨機 IO 從磁盤中獲取對應的數據頁。 ### 順序讀取 從磁盤讀取數據并不是都要付出很大的代價，當數據庫管理程序一次性從磁盤中順序讀取大量的數據時，讀取的速度會異常的快，大概在 40MB/s 左右。  如果一個頁面的大小為 4KB，那么 1s 的時間就可以讀取 10000 個頁，讀取一個頁面所花費的平均時間就是 0.1ms，相比隨機讀取的 10ms 已經降低了兩個數量級，甚至比內存中讀取數據還要快。  數據頁面的順序讀取有兩個非常重要的優勢： 1. 同時讀取多個界面意味著總時間的消耗會大幅度減少，磁盤的吞吐量可以達到 40MB/s； 2. 數據庫管理程序會對一些即將使用的界面進行預讀，以減少查詢請求的等待和響應時間； ### 小結 數據庫查詢操作的時間大都消耗在從磁盤或者內存中讀取數據的過程，由于隨機 IO 的代價巨大，如何在一次數據庫查詢中減少隨機 IO 的次數往往能夠大幅度的降低查詢所耗費的時間提高磁盤的吞吐量。 ## 查詢過程 在上一節中，文章從數據頁加載的角度介紹了磁盤 IO 對 MySQL 查詢的影響，而在這一節中將介紹 MySQL 查詢的執行過程中以及數據庫中的數據的特征對最終查詢性能的影響。 ### 索引片（Index Slices） 索引片其實就是 SQL 查詢在執行過程中掃描的一個索引片段，在這個范圍中的索引將被順序掃描，根據索引片包含的列數不同，數據庫索引設計與優化 書中對將索引分為寬索引和窄索引：  主鍵列 id 在所有的 MySQL 索引中都是一定會存在的 對于查詢` SELECT id, username, age FROM users WHERE username="draven" `來說，(id, username) 就是一個窄索引，因為該索引沒有包含存在于 SQL 查詢中的 age 列，而 (id, username, age) 就是該查詢的一個寬索引了，它包含這個查詢中所需要的全部數據列 寬索引能夠避免二次的隨機 IO，而窄索引就需要在對索引進行順序讀取之后再根據主鍵 id 從主鍵索引中查找對應的數據：  對于窄索引，每一個在索引中匹配到的記錄行最終都需要執行另外的隨機讀取從聚集索引中獲得剩余的數據，如果結果集非常大，那么就會導致隨機讀取的次數過多進而影響性能。 ### 過濾因子 從上一小節對索引片的介紹，我們可以看到影響 SQL 查詢的除了查詢本身還與數據庫表中的數據特征有關，如果使用的是窄索引那么對表的隨機訪問就不可避免，在這時如何讓索引片變『薄』就是我們需要做的了。 一個 SQL 查詢掃描的索引片大小其實是由過濾因子決定的，也就是滿足查詢條件的記錄行數所占的比例：  對于 users 表來說，sex=”male” 就不是一個好的過濾因子，它會選擇整張表中一半的數據，所以在一般情況下我們最好不要使用 sex 列作為整個索引的第一列；而 name=”draven” 的使用就可以得到一個比較好的過濾因子了，它的使用能過濾整個數據表中 99.9% 的數據；當然我們也可以將這三個過濾進行組合，創建一個新的索引 (name, age, sex) 并同時使用這三列作為過濾條件：  當三個過濾條件都是等值謂詞時，幾個索引列的順序其實是無所謂的，索引列的順序不會影響同一個 SQL 語句對索引的選擇，也就是索引 (name, age, sex) 和 (age, sex, name) 對于上圖中的條件來說是完全一樣的，這兩個索引在執行查詢時都有著完全相同的效果 組合條件的過濾因子就可以達到十萬分之 6 了，如果整張表中有 10w 行數據，也只需要在掃描薄索引片后進行 6 次隨機讀取，這種直接使用乘積來計算組合條件的過濾因子其實有一個比較重要的問題：列與列之間不應該有太強的相關性，如果不同的列之間有相關性，那么得到的結果就會比直接乘積得出的結果大一些，比如：所在的城市和郵政編碼就有非常強的相關性，兩者的過濾因子直接相乘其實與實際的過濾因子會有很大的偏差，不過這在多數情況下都不是太大的問題。 對于一張表中的同一個列，不同的值也會有不同的過濾因子，這也就造成了同一列的不同值最終的查詢性能也會有很大差別：  當我們評估一個索引是否合適時，需要考慮極端情況下查詢語句的性能，比如 0% 或者 50% 等；最差的輸入往往意味著最差的性能，在平均情況下表現良好的 SQL 語句在極端的輸入下可能就完全無法正常工作，這也是在設計索引時需要注意的問題。 總而言之，需要掃描的索引片的大小對查詢性能的影響至關重要，而掃描的索引記錄的數量，就是總行數與組合條件的過濾因子的乘積，索引片的大小最終也決定了從表中讀取數據所需要的時間。 ### 匹配列與過濾列 假設在 users 表中有 name、age 和 (name, sex, age) 三個輔助索引；當 WHERE 條件中存在類似 age = 21 或者 name = “draven” 這種等值謂詞時，它們都會成為匹配列（Matching Column）用于選擇索引樹中的數據行，但是當我們使用以下查詢時： ~~~ SELECT * FROM users WHERE name = "draven" AND sex = "male" AND age > 20; ~~~ 雖然我們有 (name, sex, age) 索引包含了上述查詢條件中的全部列，但是在這里只有 name 和 sex 兩列才是匹配列，MySQL 在執行上述查詢時，會選擇 name 和 sex 作為匹配列，掃描所有滿足條件的數據行，然后將 age 當做過濾列（Filtering Column）  過濾列雖然不能夠減少索引片的大小，但是能夠減少從表中隨機讀取數據的次數，所以在索引中也扮演著非常重要的角色 ## 索引的設計 相信文章前面的內容已經為索引的設計提供了充足的理論基礎和知識，從總體來看如何減少隨機讀取的次數是設計索引時需要重視的最重要的問題，在這一節中，我們將介紹 數據庫索引設計與優化 一書中歸納出的設計最佳索引的方法 ### 三星索引 三星索引是對于一個查詢語句可能的最好索引，如果一個查詢語句的索引是三星索引，那么它只需要進行一次磁盤的隨機讀及一個窄索引片的順序掃描就可以得到全部的結果集；因此其查詢的響應時間比普通的索引會少幾個數量級；根據書中對三星索引的定義，我們可以理解為主鍵索引對于 WHERE id = 1 就是一個特殊的三星索引，我們只需要對主鍵索引樹進行一次索引訪問并且順序讀取一條數據記錄查詢就結束了  為了滿足三星索引中的三顆星，我們分別需要做以下幾件事情： 1. 第一顆星需要取出所有等值謂詞中的列，作為索引開頭的最開始的列（任意順序）； 2. 第二顆星需要將 ORDER BY 列加入索引中； 3. 第三顆星需要將查詢語句剩余的列全部加入到索引中 三星索引的概念和星級的給定來源于 數據庫索引設計與優化 書中第四章三星索引一節 如果對于一個查詢語句我們依照上述的三個條件進行設計，那么就可以得到該查詢的三星索引，這三顆星中的最后一顆星往往都是最容易獲得的，滿足第三顆星的索引也就是上面提到的寬索引，能夠避免大量的隨機 IO，如果我們遵循這個順序為一個 SQL 查詢設計索引那么我們就可以得到一個完美的索引了；這三顆星的獲得其實也沒有表面上這么簡單，每一顆星都有自己的意義：  1. 第一顆星不只是將等值謂詞的列加入索引，它的作用是減少索引片的大小以減少需要掃描的數據行； 2. 第二顆星用于避免排序，減少磁盤 IO 和內存的使用； 3. 第三顆星用于避免每一個索引對應的數據行都需要進行一次隨機 IO 從聚集索引中讀取剩余的數據； 在實際場景中，問題往往沒有這么簡單，我們雖然可以總能夠通過寬索引避免大量的隨機訪問，但是在一些復雜的查詢中我們無法同時獲得第一顆星和第二顆星。 ~~~ SELECT id, name, age FROM users WHERE age BETWEEN 18 AND 21 AND city = "Beijing" ORDER BY name; ~~~ 在上述查詢中，我們總可以通過增加索引中的列以獲得第三顆星，但是如果我們想要獲得第一顆星就需要最小化索引片的大小，這時索引的前綴必須為 (city, age)，在這時再想獲得第三顆星就不可能了，哪怕在 age 的后面添加索引列 name，也會因為 name 在范圍索引列 age 后面必須進行一次排序操作，最終得到的索引就是 (city, age, name, id)：  如果我們需要在內存中避免排序的話，就需要交換 age 和 name 的位置了，在這時就可以得到索引 (city, name, age, id)，當一個 SQL 查詢中**同時擁有范圍謂詞和 ORDER BY 時**，無論如何我們都是沒有辦法獲得一個三星索引的，我們能夠做的就是在這兩者之間做出選擇，是犧牲第一顆星還是第二顆星。 總而言之，在設計單表的索引時，首先把查詢中所有的**等值謂詞全部取出**以任意順序放在索引最前面，在這時，如果索引中同時存在范圍索引和 ORDER BY 就需要權衡利弊了，希望最小化掃描的索引片厚度時，應該將**過濾因子最小的范圍索引列**加入索引，如果希望避免排序就選擇 **ORDER BY 中的全部列**，在這之后就只需要將查詢中**剩余的全部列**加入索引了，通過這種固定的方法和邏輯就可以最快地獲得一個查詢語句的二星或者三星索引了。 ## 總結 在單表上對索引進行設計其實還是非常容易的，只需要遵循固定的套路就能設計出一個理想的三星索引，在這里強烈推薦 數據庫索引設計與優化 https://www.amazon.cn/圖書/dp/B00ZH27RH0 這本書籍，其中包含了大量與索引設計與優化的相關內容；