MySQL 表與索引設計攻略

富途web開發團隊發表於2018-06-13

MySql索引

歡迎關注富途web開發團隊，php , 前端都缺。缺人從眾

首先，跟大家說句抱歉。很久沒有更新了。

上個月去了趟上海，參加了FDC2018前端千人峰會之後。就一直沒有來得及更新。

參會體驗分享點選這裡

感觸挺多的。有機會以後會多參加。

最近想著自己也學習一點好玩的東西吧。

原文連結

第一部分 MySQL概述

MySQL 是什麼

1970 年，Edgar Frank "Ted" Codd（關聯式資料庫之父）發表了題為"A Relational Model of Data for Large Shared Data Banks"（大型共享資料庫的關係資料模型）的論文，文中首次提出並證明了，可以使用關係模型來描述資料。

關係模型是指使用二維表的形式來表示實體和實體間的聯絡。MySQL 是基於這個理論而實現的許多關係型資料庫之一。

關係模型圖

MySQL 的歷史與現狀

1990 Michael Widenius 寫了 MySQL 的第一個版本。
1995 Michael Widenius 成立了 MySQL AB 公司。
2000 Michael Widenius 公佈了 MySQL 原始碼，採用 GPL 許可協議。MySQL 進入開源時代。
2008 Sun 收購了 MySQL AB 公司。MySQL 資料庫進入 Sun 時代。
2009 Oracle 收購 Sun 公司。MySQL 資料庫進入 Oracle 時代。Oracle 同時維護社群版的 MySQL，以及一個企業版本的 MySQL。
2009 Michael Widenius 從開源的 MySQL 分支上重新拉分支，創立 MariaDB。

MySQL 版本

MySQL 的社群版是開源免費的，企業版是閉源收費的。

下面是主要版本特性的對比圖。

騰訊雲 CDB 5.6 的版本是：5.6.28-cdb20160902-log。online ddl的時候，需要注意：給某個表增加列的時候還是會造成堵塞。

5.7版本的mysql 在效能和併發連線數上都有很大幅度的提升。

MySQL 與 MariaDB 的相容性和差異

隨著Oracle買下Sun，MySQL也落入了關係型資料庫王者之手。而早在2009年，考慮到Oracle的名聲以及其入手之後閉源的可能性，MySQL之父的Michael便先行一步，以他女兒Maria的名字開始了MySQL的另外一個衍生版本：MariaDB。

As MariaDB is a full replacement of MySQL, the MySQL manual at dev.mysql.com/doc is generally applicable.--來源

我們每月都會將社群版的 MySQL 基本程式碼編譯入 MariaDB，從而保證 MariaDB 與 Oracle 新增的任何補丁和更新的 MySQL 相相容。

MariaDB版本與Mysql版本相匹配——比如MariaDB 5.1，與MySQL 5.1使用相同的程式碼。由於更新和修復是針對MySQL原始碼樹的，這樣的話MariaDB可以採納這些補丁,指的是原有程式碼的補丁，不是各自的新特性（理論上，MariaDB每月都與MySQL原始碼合併）。--來源

Upgrading from MySQL to MariaDB

相容性和差異

MySQL 的邏輯架構

Mysql是由SQL介面，解析器，優化器，快取，儲存引擎組成的。

Connectors 呼叫方
Management Serveices & Utilities 系統管理和控制工具
Connection Pool 連線池
SQL Interface
Parser 解析器
Optimizer 優化器
Cache & Buffer 各種快取
Engine 儲存引擎

執行緒池是Mysql5.6的一個核心功能，對於伺服器應用而言，無論是web應用服務還是DB服務，高併發請求始終是一個繞不開的話題。當有大量請求併發訪問時，一定伴隨著資源的不斷建立和釋放，導致資源利用率低，降低了服務質量。執行緒池是一種通用的技術，通過預先建立一定數量的執行緒，當有請求達到時，執行緒池分配一個執行緒提供服務，請求結束後，該執行緒又去服務其他請求。通過這種方式，避免了執行緒和記憶體物件的頻繁建立和釋放，降低了服務端的併發度，減少了上下文切換和資源的競爭，提高資源利用效率。所有服務的執行緒池本質都是位了提高資源利用效率，並且實現方式也大體相同。本文主要說明Mysql執行緒池的實現原理。

MySQL 的物理檔案

日誌檔案會記錄mysql執行期間發生的變化。當mysql遭到意外的損壞時，可以通過日誌檔案進行資料恢復。

日誌記錄的資訊很多。比如：mysql連線狀況、SQL語句的執行情況和錯誤資訊等都會被記錄下來。

mysql的日誌檔案主要包含以下的幾種：

錯誤日誌
查詢日誌
慢查詢日誌
事務日誌
二進位制日誌

錯誤日誌主要記錄mysql伺服器相關的資料；

慢查詢日誌記錄一些執行時間較長的查詢query；

事務日誌是InnoDB儲存引擎特有的日誌；

二進位制日誌主要記錄修改資料或有可能引起資料改變的mysql語句；

mysql儲存資料檔案會根據各個儲存引擎不同而使用不同的儲存檔案。

SQL 執行過程

所有的我們想查詢或者修改，刪除資料，都是通過執行sql語句來完成的。mysql通過分析我們傳入的sql語句來進行相關操作。

sql語句傳入mysql後，會首選檢視快取中有木有匹配的資料，有的話直接返回資料，結束sql。如果沒有，則需要將當前sql語句傳入解析器進行語法解析。然後進行預處理檢查語法是否符合語義，最後優化器將其轉化為執行計劃，得到mysql最合適的查詢語句，最後交給查詢執行引擎。獲取我們最後想要的資料。

快取池、順序讀取與隨機讀取

快取池是儲存引擎實現的（與查詢快取是兩個不同層次的快取）。在 MySQL InnoDB 中，可以通過innodb_buffer_pool_size引數來定義快取池的大小。

快取池通過 LRU 策略進行維護。若資料庫中的資料可以完全存放於快取池中，則可以認為，此時資料庫的效能是最佳的了。除了同步或非同步的寫磁碟操作外，所有其他操作都可以在記憶體中完成。

下面是 18G 的資料，隨著快取池的變大，TPS 的變化情況。18G 資料，存到記憶體要比 18G 大一點，因為還有其他的開銷。

因為有了快取池，一些熱點的資料，就可以自動躺在快取池中了。

磁碟與硬碟的隨機讀寫和順序讀寫：順序讀取是指順序地讀取磁碟上的頁。隨機讀取是指訪問的頁不是連續的，需要磁碟的磁頭不停地移動。

======================第一部分完=====================

第二部分資料庫設計

正規化定義

第一正規化：屬性不可分割。資料表中的每一列（每個欄位）必須是不可拆分的最小單元，也就是確保每一列的原子性；
第二正規化：要有主鍵，要求其他欄位都依賴於主鍵。滿足1NF後，要求表中的所有列，都必須依賴於主鍵，而不能有任何一列與主鍵沒有關係，也就是說一個表只描述一件事情；
第三正規化：消除傳遞依賴（消除冗餘）。必須先滿足第二正規化（2NF），要求：表中的每一列只與主鍵直接相關而不是間接相關，（表中的每一列只能依賴於主鍵）
巴斯-科德正規化（BCNF）: 每個表中只有一個候選鍵
第四正規化: 消除表中的多值依賴。（當一個表中的非主屬性互相獨立時（3NF），這些非主屬性不應該有多值）

注意：通常我們到第三正規化就夠了，後面的都太嚴格了，不符合實際使用。按照領域模型方式來建資料庫，一般都能很好地滿足到第三正規化。

先按照正規化的規範來設計表。然後根據實際的查詢查詢需求，使用反正規化加速查詢。

欄位型別選擇

基本準則：

更小的，簡單地，夠用的，通常更好
一般情況下，應該儘量使用可以正確儲存資料的最小資料型別。
更小的資料型別通常更快，因為它們佔用更少的磁碟、記憶體和CPU快取，並在處理的時候需藥的CPU週期也更少。
當然，後期更換欄位型別是很耗時和痛苦的事情。所以，在一開始設計的時候，最好根據業務規模，在“更小原則”與“後期維護”間進行權衡。在夠用的情況下，選擇最小的。
根據欄位的屬性，選擇簡單地資料型別。如：年齡就應該用整型存，不要用字串存。時間就應該用內建的時間型別來存。IP就應該用int來存。（inet_aton('127.0.0.1') inet_ntoa(4294967295) MySQL都已經內建了轉化函式了）
儘量避免 NULL
儘量避免使用 set 和 enum 型別

整型

TINYINT: 8位
SMALLINT: 16位
MEDIUMINT: 24位
INT: 32位
BIGINT: 64位

範圍：-2^(n-1) ~ (2^(n-1))-1

使用 UNSIGNED,可以使正數的範圍提高正式的一倍+1，如-128~127 -> 0~255

UNSIGNED INT 40億
UNSIGNED BIGINT 18446744萬億

INT(11)，其中的11並沒有什麼作用。只是規定了一些MYSQL客戶端在顯示資料時的顯示格式而已。

題外話：上面的5種整數型別，只是規定了 MYSQL 怎麼在記憶體和磁碟中儲存資料。而，在整數計算時，MYSQL 一般將其全部轉成64位的 BIGINT 進行運算。

實數型別

小數字段推薦使用 decimal 型別，float 和 double 精度不夠，特別是涉及金錢的業務，必須使用 decimal。--騰訊雲 CDB for MySQL 使用規範指南

我們的實際業務中，更喜歡用整型來存（擴大 1000、10000等）

字串型別

5.7 後預設使用utf8mb4

CHAR 定長。括號中寫多少就固定開好了指定個數的儲存，只能存多少個字元，不夠則系統會預設補上些東西。
VARCHAR 變長。括號中填的是最大的字元個數。實際存資料的時候，不是開固定長度的空間。而是根據寫入的資料來開，但是不能超過最大的字元數。

注意，括號中定義的是字元數不是位元組數

CHAR 會去掉最右邊的空格（如果有的話）。而 VARCHAR 則會保留。

VARCHAR 和 CHAR 的括號中存的都是最大字元數。

存 'hello' 時，使用 VARCHAR(5) 比使用 VARCHAR(200) 要好。雖然二者佔用的空間是一樣的，但是還是 VARCHAR(5) 會好些，在後續的某些操作者更有效率。（更少原則）

儲存很大的資料:

BLOB系列（二進位制）：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB
TEXT系列（字串）：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT

附一個通用的實踐：整數字段的查詢效率是最好的。所以，如果某些欄位要查詢、排序、關聯等，使用整型的效率最好。

日期和時間型別

DATETIME

使用64位來儲存，時間跨度為1001年~9999年。這個型別沒有時區的概念，比如我在東八區存了個"2018-01-01 00:00:00"進去，然後在西八區取出來，取到的還是"2018-01-01 00:00:00"。那這就不正確了。

所以比較好的做法是，資料庫中使用DATETIME，然後存時間的時候一律用程式生成UTC時間（而不是local時區的時間）存進去，取出來的時候不管想顯示伺服器時間還是顯示使用者的時間都可以處理。

TIMESTAMP 使用32位來儲存，實際上存的是時間戳（所以範圍是1970~2038）

MYSQL提供了FROM_UNIXTIME在輸出時格式化顯示時間（同時也幫你把時區加上去了）。同時，MySQL也提供了CURRENT_TIMESTAMP來自動維護created_at

具體用 int 還是 TIMESTAMP 還是 DATETIME ，看下面文章，自己考慮下。

www.jianshu.com/p/edfdaacc3…

blog.csdn.net/ppvqq/artic…

標識列的資料型別選擇

標識列就是類似自增ID這種，用於標識某一行資料的一個關鍵值。因為這些標識列經常會用於關聯操作，或者和其他資料進行比較等，所以標識列的資料型別在選擇時一定要特別注意，以達到最佳的查詢效率。

下面是一些小技巧

一旦選定了型別，與其比較或關聯的相關列的資料型別最好和標識列完全一樣，包括unsigned這些屬性也最好一樣。
選用int等整形永遠是最好的選擇。
一定避免使用enum和set型別。
儘量不要使用字串型別。
儘量避免 NULL

一些技巧

使用快取表和彙總表

為了更快地讀，只能更慢地寫。有時提升效能最好的方法是在同一張表中儲存衍生的冗餘資料。然而，有時也需要建立一張完全獨立的彙總表或快取表（特別是為滿足檢索的需求時）

快取表：一般用於“冗餘資料”，可以從某個表中，花費一定的時間來生成。
彙總表：一般是 group 操作的結果（如：收藏數量表就是一個彙總表）。

彙總表、計數表，可能會遇到寫瓶頸。此時，可以使用“槽（slot）”，把每次新增的“+1”隨機地分配到某一行中，這樣就可以將每次寫都鎖一行，變成每次寫，會從 N 個槽中選一個來寫。儘量地避免了寫鎖等待。

其他的一些技巧

約束性與併發控制

唯一索引

利用資料庫的機制，來幫我們實現唯一性。可以通過組合欄位的唯一性，來達到唯 N 性等。

樂觀鎖

更新資料時，更新條件中帶上之前讀取的記錄的版本號（或重要欄位的值）。

YII 的樂觀鎖支援

increase 與 decrease

當前取到count=4，要加 1 後存會資料庫。count = count + 1 與 count = 5 的區別

使用槽（slot）來減少鎖資源等待

如果要對一行資料進行頻繁的修改，可能會出現對這行資料的寫鎖等待。此時，考慮下能否把改一行，從業務邏輯上變成改多行。把寫操作分配到多行，減少單行的鎖等待。

儲存原始資料而非結果

比如，要獲取使用者的剩餘抽獎次數。此時，資料庫中，存已經用了的次數和總共有多少次抽獎次數 要優於只存一個 剩餘抽獎次數。

===================第二部分完=======================

第三部分索引設計

這裡討論的是：MySQL 5.6 InnoDB BTREE 索引。只要把 MySQL InnoDB 中 BTREE 的樹結構理清了，就能自己推匯出許多索引的設計準則。

索引簡介

索引其實就是一種資料結構。（雜湊表、樹等等）不同型別的索引有著不同的資料結構和功能。

例如：語文課本，每篇文章都對應一個起始頁碼，所有的文章按照頁碼順序進行整理排版。此時，“文章按頁碼順序進行整理排版”就是索引的資料結構，書本的目錄就是索引檔案。

索引型別

Mysql 5.6 InnoDB 提供了兩個型別的索引（Index_type）

BTREE
FULLTEXT

（Mysql 5.6 InnoDB 不支援手動使用 hash index（InnoDB 內部支援自適應雜湊索引），也不支援 Geospatial index（5.7 後支援））

Clustered indexes 指聚集索引

可以使用 BTREE 索引，來實現

主鍵索引PRIMARY KEY
唯一索引UNIQUE KEY
普通索引KEY

（外來鍵FOREIGN KEY的實現，也有部分依賴於 BTREE索引，建外來鍵的時候，必須要求當前指定的列最少有個普通索引，不然的話，系統會自動幫你建一個。）

可以使用 FULLTEXT 索引，來實現

全文索引FULLTEXT KEY

單列索引與聯合索引

單列索引，只使用一列來建索引
聯合索引，使用多列來建索引


CREATE DATABASE `test_db` DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

use test_db;

CREATE TABLE  `table_b`(
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `name` VARCHAR(100) NOT NULL,
   `f_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY uk_name (`name`),
    KEY title (`title`),
    KEY title_name_fid (`title`,`name`,`f_id`),
    FULLTEXT KEY (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



SHOW INDEX FROM table_b;

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_b |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          0 | uk_name        |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title          |            1 | title       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            1 | title       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            2 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            3 | f_id        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | name           |            1 | name        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

複製程式碼

索引的作用

加速查詢速度
維護資料的約束性（完整性、一致性）

對於加速查詢，使用索引不一定是最好的選擇。小表就直接全表掃描，中到大表就建索引，超大表就分割槽分表。其實主要就要索引帶來的好處和維護索引的成本之間的權衡。

BTREE 索引

在 MySQL 5.6 InnoDB 中，我們平時建索引，只有 BTREE 這個選擇了。全文索引，一般我們的業務場景不會用到。

B+ 樹簡介

B+樹和二叉樹、平衡二叉樹一樣，都是經典的資料結構。B+樹由B樹和索引順序訪問方法（ISAM，是不是很熟悉？對，這也是MyISAM引擎最初參考的資料結構）演化而來，但是在實際使用過程中幾乎已經沒有使用B樹的情況了。

B+樹的定義十分複雜，因此只簡要地介紹B+樹：B+樹是為磁碟或其他直接存取輔助裝置而設計的一種平衡查詢樹，在B+樹中，所有記錄節點都是按鍵值的大小順序存放在同一層的葉節點中，各葉節點指標進行連線。

特點：

樹
查詢樹
平衡
資料都在葉子節點
節點可以包含多個資料
葉子節點間有指標相連
查詢、插入、刪除近似於 O(lgn)

相關：

區域性性原理
I/O 是很慢的

為什麼使用 B+樹這種資料結構來作為索引呢？

查詢樹，查詢效率接近 O(lgn)
平衡，每次查詢查詢的次數基本相等
索引資料在非葉子節點，可以把索引資料都 load 進記憶體，加快查詢
葉子節點點有指標相連，便於遍歷

線上 B+樹生成器

Mysql InnoDB 的邏輯儲存結構

劃分邏輯結構的目的是便於管理，就像學校要劃分年級、班級、小組一樣。

表空間（tablespace）
段（segment）
區（extent）
頁（page）
行（row）

頁是 B+樹中一個一個節點（葉子節點或非葉子節點）。
頁有分索引頁和資料頁。索引頁，其中存放的就是非葉子節點的資料，資料頁存放的就是葉子節點的資料。每個頁中，包含 1 個以上的行，行間通過指標按順序相連。我們在搜尋資料時，先定位到某個頁，再在頁內尋找想要的行。每頁預設 16KB。
一個區包括 64 個頁，區在申請磁碟時，是整塊整塊申請的，所以，一個區中的資料，在物理上是連續的。（64 個頁 × 一個頁 16kb = 一個區 1M）。一般記憶體都有能力把一個 B+樹索引中的所以非葉子節點全部 load 進記憶體中進行管理。所以，在記憶體中維護這個 B+樹的非葉子節點時，一般開銷都相對較小。但是，維護葉子節點的話，一般都要磁碟 io 了，因此整個葉子資料一般不能全部 load 進記憶體。
表空間就像是一個資料夾，段就是資料夾中的一個一個檔案。新建一個索引時，就會新建兩個段資料，一個只存索引（索引段），一個只存資料（資料段）。
表空間就是一個表的資料了。

Mysql InnoDB 的邏輯儲存結構有 3 個有意思的地方：

段，是為了區分非葉子資料和葉子資料。便於把整個非葉子資料 load 進記憶體。
區，是一個連續的磁碟空間。
頁，頁內也是一個連續的空間。

類比：

中心，劃分中心，是為了更好地管理有相同職能技能的員工。
業務線，業務線中包含好多小組，各個小組坐在附近，能便於交流。
小組，小組中包含員工，同一小組的員工坐在一起，能便於交流。

從邏輯結構，我們可以知道：

連續性
葉子節點與非葉子節點的分開管理

MySQL InnoDB BTREE 總覽

通過上面圖片，我們能推匯出下面這些結論

全值匹配
匹配最左字首
匹配列字首
匹配範圍值
精確匹配某一列並範圍匹配另外一列
第一個範圍查詢欄位後面的段索引字都只能用於過濾。

索引中的欄位可以有兩個作用：

確定索引片起始和終止位置
過濾，比較篩選

聚集索引與輔助索引

聚集索引

在 MySQL InnoDB 中，每行記錄，必有一個主鍵。官方推薦使用業務無關的整形無符號自增非空型別作為主鍵。如果沒有自定義主鍵，系統會根據以下規則來選取主鍵

看有沒有單個的非空的唯一索引，有的話，就用這個作為主鍵。有多個的話，就選擇建表語句中，符合條件的第一個欄位。
若無的話，就自己維護一個6個位元組的空間作為主鍵。詳情請見

擴充套件：為啥要自己定義自增非空int

6位元組。自己可能用不到那麼大
順序。減少頁分裂。

輔助索引

其他的索引就是輔助索引（唯一索引、普通索引等）。輔助索引中，葉子節點存的是記錄的主鍵值。通過主鍵值再去聚集索引查實際的值。

建議在業務要求的輔助索引欄位後面補上主鍵欄位？

這裡加和不加，從實驗上看不出區別。應該是優化器幫我們處理好了（優化器是很強大的，優化器的程式碼更新地很快，特性很多，要相信優化器）。

為什麼輔助索引的葉子節點不直接存錶行記錄實際的物理指標（頁號等），而是要去聚集索引那邊再查一次？

如果這樣的話，如果聚集索引中，頁的分裂，將會導致資料的物理結構發生變化。如果輔助索引存的還是物理資訊，那麼就還要去更新輔助索引中的資料，那就會產生許多的額外操作。而目前這種結構，我們要查兩次，但是這個負擔不是很嚴重，因為非葉子節點都在記憶體中，查起來很快的。

三星索引設計規範

如何為一條 SQL 語句設計索引。

覆蓋索引

無需訪問聚集索引，通過輔助索引就能完成需求。

要select的欄位都在索引中。
select count(*) 因為我們只要一個數字就好了，所以也是隻需要訪問輔助索引就好了，所以也算是覆蓋索引。

因為通常輔助索引都比聚集索引要小（輔助索引的葉子節點頁中，一個頁能包含更多的列記錄）。

如何優化count(*)

特別是帶條件的計數時，肯定要掃描的，沒有系統的統計資訊可以直接拿。所以，要不就使用覆蓋索引，如果還是慢的話，就用之前設計表時說的彙總表，設定多個槽防止鎖表。

我們說的全表掃描，就是指遍歷聚集索引的葉子節點

三星索引的定義:

第一顆星：儘量縮短將要被掃描的葉子節點範圍（起始位和終止位之間的間隔儘量小）。
第二顆星：避免把葉子節點的資料 load 進記憶體中的排序操作。使用 B+樹索引幫我們提前排好。
第三顆星：避免減少回聚集索引查詢，通過輔助索引就解決戰鬥。

設計步驟

通常，第三顆星我們都能達到，即，使用覆蓋索引來避免回聚集索引查詢的過程，可以減少很多回表的 I/O。

如果，查詢的 where 條件中都是等值查詢（或沒有排序的話），那麼我們能完成滿足 3 顆星的要求。

如果，查詢的 where 條件中存在範圍查詢，且有排序的需要，那麼我們就只要在（第一顆星 + 第三顆星）和（第二顆星 + 第三顆星）這兩者間選擇了。

候選A

（第一顆星 + 第三顆星）追求窄，掃描最少的索引片

取出對於等值條件的列，將這些列作為索引的前導列，任意順序皆可。（選擇性高的靠前會好點，便於其他查詢複用這個是索引）
將選擇性最好的範圍條件作為索引的下一列。
以正確的順序新增 order by列。忽略上面兩步已經新增過的列。
以任意順序將select語句中的其他列新增到索引中，已不易變列開始。

候選B

（第二顆星 + 第三顆星）追求不用排序

取出對於等值條件的列，將這些列作為索引的前導列，任意順序皆可。（選擇性高的靠前會好點，便於其他查詢複用這個是索引）
以正確的順序新增 order by列。忽略上面兩步已經新增過的列。
以任意順序將 select 語句中的其他列新增到索引中，以不易變列開始。

有排序且有範圍查詢時，才考慮選擇候選A 或候選B，其他情況，都能滿足三星索引。

排序指：order by

選擇候選A 還是候選B，就是判斷：load 進記憶體排序的成本大，還是一個一個從頭遍歷的篩選的成本大。這種沒有定性的答案，需要根據資料的特性以及要去取怎樣的資料決定。

select A,B from user where A > a order by B;

假設總共有 n 條記錄，滿足條件 A > a 的有 m 條，每條資料一次i/o

候選A ：n->m，然後 i/o m 條資料進入記憶體進行排序，耗時O(mlgm)，即，耗時為：m次i/o+ mlgm次比較
候選B ：i/o n 條資料，進入記憶體中比較，比較次數為1~n，即，耗時為：1~n次[比較+i/o時間]。（因為如果只需取 1 條資料，可以提前退出，所以 1~n）

假如 m 很大，大到接近 n 的話，那麼候選B 好。假如 m 很小，小到接近 1 的話，那麼候選A 好。

==================第三部分完====================

第四部分實踐測試

硬體配置

騰訊雲 CDB
5.6.28-cdb2016-log 20180122
高IO版，記憶體1000MB，硬碟25GB，1000次/秒

MySQL 主要配置資訊

關閉查詢快取

show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)
複製程式碼

每頁大小 16KB

show variables like '%innodb_page%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
複製程式碼

InnoDB 快取區大小 893M

show variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 936378368      |
+-------------------------------------+----------------+
複製程式碼

測試資料庫初始狀態

數量：500W
主鍵：int 型自增主鍵

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` tinyint(3) unsigned NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `email` varchar(255) NOT NULL,
  `address` varchar(350) NOT NULL,
  `company` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (1.81 sec)


select * from user limit 1 \G
*************************** 1. row ***************************
     id: 1
   name: Prof. Osborne Waelchi I
    sex: 0
    age: 60
  email: dach.angela@yahoo.com
address: 35712 Quigley Mountains North Alysonville, CO 53682-2718
company: McGlynn Ltd
   city: Port Maziebury
1 row in set (0.01 sec)
複製程式碼

實驗案例

索引相關

運算後的列，不能使用索引

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> explain select * from user where id+1=2;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.04 sec)

MySQL [test_db_for_index]> select * from user where id+1=2;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.04 sec)

MySQL [test_db_for_index]> select * from user where id=1;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

複製程式碼

列欄位的字首性與 IN 繞過技巧


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I';
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.11 sec)

MySQL [test_db_for_index]> explain select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1);
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | sex_name      | sex_name | 768     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1);
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)
複製程式碼

多列索引的部分使用

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I' and age=60;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.03 sec)

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I' and age=60;;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.03 sec)

ERROR: No query specified

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.03 sec)


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | name_age_email | name_age_email | 767     | const |    1 | Using index condition |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I' and age=60;;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

ERROR: No query specified

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I' and age=60;
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | name_age_email | name_age_email | 768     | const,const |    1 | Using index condition |
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
1 row in set (0.04 sec)

複製程式碼

單索引的部分使用

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%';
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
| id      | name                     | sex | age | email                    | address                                                   | company        | city             |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
|       1 | Prof. Osborne Waelchi I  |   0 |  60 | dach.angela@yahoo.com    | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd    | Port Maziebury   |
|  798465 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1167101 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 1660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3528809 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 4021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4521968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 5021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
11 rows in set (3.30 sec)



MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)

MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |   11 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%';
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
| id      | name                     | sex | age | email                    | address                                                   | company        | city             |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
|       1 | Prof. Osborne Waelchi I  |   0 |  60 | dach.angela@yahoo.com    | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd    | Port Maziebury   |
|  798465 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4521968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 5021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1167101 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 3528809 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
11 rows in set (0.04 sec)
複製程式碼

同欄位的等值 or，新版優化器會優化成 in，可以使用索引

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley');
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley');
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
| id | name                    | sex | age | email                  | address                                                   | company          | city           |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com  | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd      | Port Maziebury |
|  4 | Zaria Quigley           |   0 |  41 | ryan.anissa@cronin.com | 799 Barney Cove
Princessland, VA 34382                    | Farrell-Hartmann | DuBuqueport    |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
2 rows in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley';
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
| id | name                    | sex | age | email                  | address                                                   | company          | city           |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com  | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd      | Port Maziebury |
|  4 | Zaria Quigley           |   0 |  41 | ryan.anissa@cronin.com | 799 Barney Cove
Princessland, VA 34382                    | Farrell-Hartmann | DuBuqueport    |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
2 rows in set (0.01 sec)

複製程式碼

不同欄位的or的優化，使用兩個單列索引組合，使用 union 也是同樣效果，並不會優化。


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | email    |            1 | email       | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' or email='d';
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
| id | select_type | table | type        | possible_keys | key        | key_len | ref  | rows | Extra                                |
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
|  1 | SIMPLE      | user  | index_merge | name,email    | name,email | 767,767 | NULL |    2 | Using union(name,email); Using where |
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='1' or email='d';
Empty set (0.02 sec)


MySQL [test_db_for_index]> select * from user where name='1' union select * from user where email='d';
Empty set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' union select * from user where email='d';
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type  | table      | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
|  1 | PRIMARY      | user       | ref  | name          | name  | 767     | const |    1 | Using index condition |
|  2 | UNION        | user       | ref  | email         | email | 767     | const |    1 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL  | NULL    | NULL  | NULL | Using temporary       |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
3 rows in set (0.01 sec)
複製程式碼

儘量減少範圍條件。（>、<、!=、not in、between、not between），能轉成已知的，都轉已知的

## sex欄位只有 0 1 兩個取值
MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where sex!=1 and name='payton';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | sex_name      | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where sex!=1 and name='payton';
Empty set (3.18 sec)

MySQL [test_db_for_index]> desc select * from user where sex=0 and name='payton';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | sex_name      | sex_name | 768     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select * from user where sex=0 and name='payton';
Empty set (0.02 sec)

複製程式碼

聯合索引中，範圍搜尋欄位後面的欄位不能再決定索引片寬度，只能用於篩選。使用 IN 優化

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name |            1 | age         | A         |         136 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where age >= 10 and age <= 15 and name='payton';
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | user  | range | age_name      | age_name | 768     | NULL | 626654 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age >= 10 and age <= 15 and name='payton';
Empty set (0.09 sec)

MySQL [test_db_for_index]> desc select * from user where age in (10,11,12,13,14,15) and name='payton';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | age_name      | age_name | 768     | NULL |    6 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age in (10,11,12,13,14,15) and name='payton';
Empty set (0.02 sec)

複製程式碼

覆蓋索引更優

因為這裡的 InnoDB 的快取池太大了，有接近 900M 的記憶體。完全有能力把輔助索引的葉子和非葉子節點全部 load 進記憶體。同理，聚集索引也是。因為這裡沒有 io 的差距，所以差別就是一頁的記錄行資料多少了。所以這裡的差別不是很大。但是，如果不能完全把聚集索引非葉子節點全部進記憶體的話，這裡的差距會更大。

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (1.69 sec)


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex      |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | sex  | 1       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (0.67 sec)

複製程式碼

InnoDB 快取池的大小，對查詢的影響

這個實驗的環境和其他的不一樣。

快取池是儲存引擎實現的。在 MySQL InnoDB 中，可以通過innodb_buffer_pool_size引數來定義快取池的大小。

快取池通過LRU策略進行維護。若資料庫中的資料可以完全存放於快取池中，則可以認為，此時資料庫的效能是最佳的了。除了同步或非同步的寫磁碟操作外，所有其他操作都可以在記憶體中完成。

下面是18G的資料，隨著快取池的變大，TPS的變化情況。18G資料，存到記憶體要比18G大一點，因為還有其他的開銷。

因為有了快取池，一些熱點的資料，就可以自動躺在快取池中了，這樣，就快了。

磁碟與硬碟的隨機讀寫和順序讀寫

順序讀取是指順序地讀取磁碟上的頁。隨機讀取是指訪問的頁不是連續的，需要磁碟的磁頭不停地移動。

注意，這裡指的順序，指的是大塊內部是順序地，大塊與大塊間可以是不連續的。因為很難保證能申請到一塊幾十G的連續空間。

在 MySQL InnoDB 中，頁是通過區來進行管理的，每次申請儲存時，會申請一塊連續的區，其中包括64個頁。所以，可以保證這64個頁是連續的，但是區與區間就不保證連續了。

固態硬碟雖然物理結構和磁碟不一樣，但是也是準守上面的原則的。順序讀還是會比隨機讀快。

快取的作用，系統把random_digit索引的所有葉子節點都快取到記憶體中了

MariaDB [big_tables]> show index from custom;
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| custom |          0 | PRIMARY      |            1 | id           | A         |     1240315 |     NULL | NULL   |      | BTREE      |         |               |
| custom |          1 | email        |            1 | email        | A         |     1240315 |      255 | NULL   | YES  | BTREE      |         |               |
| custom |          1 | name         |            1 | name         | A         |     1240315 |      255 | NULL   | YES  | BTREE      |         |               |
| custom |          1 | random_digit |            1 | random_digit | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (1.39 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.25 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.22 sec)

MariaDB [big_tables]> desc select count(*) from custom;
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
| id   | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
|    1 | SIMPLE      | custom | index | NULL          | random_digit | 7       | NULL | 1240315 | Using index |
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)


複製程式碼

優化器是很厲害的


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+


MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | user  | range | age_name_email | age_name_email | 1       | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>580 order by name limit 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.02 sec)
複製程式碼

Cardinality 與欄位選擇性


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex      |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)


MySQL [test_db_for_index]> select count(distinct sex)/count(*) from user;
+------------------------------+
| count(distinct sex)/count(*) |
+------------------------------+
|                       0.0000 |  # 其實不是0，不夠很接近
+------------------------------+
1 row in set (1.88 sec)


MySQL [test_db_for_index]> select count(distinct name)/count(*) from user;
+-------------------------------+
| count(distinct name)/count(*) |
+-------------------------------+
|                        0.1592 |
+-------------------------------+
1 row in set (6.13 sec)

MySQL [test_db_for_index]> select count(distinct left(email,5))/count(*) from user;
+----------------------------------------+
| count(distinct left(email,5))/count(*) |
+----------------------------------------+
|                                 0.0049 |
+----------------------------------------+
1 row in set (4.12 sec)


MySQL [test_db_for_index]> select count(distinct left(email,15))/count(*) from user;
+-----------------------------------------+
| count(distinct left(email,15))/count(*) |
+-----------------------------------------+
|                                  0.1545 | # 這個最省空間。不過要注意，截斷後，就不能使用覆蓋索引了，必須要回聚集索引才能拿到當前列完整的內容
+-----------------------------------------+
1 row in set (5.74 sec)


MySQL [test_db_for_index]> select count(distinct email)/count(*) from user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
|                         0.1586 |
+--------------------------------+
1 row in set (5.66 sec)

複製程式碼

候選A 還是候選B

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

MySQL [test_db_for_index]> select age,name,email from user where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.00 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 100000,10;
+-----+--------------------+-------------------------------+
| age | name               | email                         |
+-----+--------------------+-------------------------------+
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
+-----+--------------------+-------------------------------+
10 rows in set (0.06 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 100000,10;
+-----+--------------------+-------------------------------+
| age | name               | email                         |
+-----+--------------------+-------------------------------+
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
+-----+--------------------+-------------------------------+
10 rows in set (18.65 sec)


--------------------------


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 89 order by name limit 1;
Empty set (1.61 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 89 order by name limit 1;
Empty set (0.01 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.00 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

## 選擇候選A 還是 候選B，我們自己想的過程，其次優化器已經幫我們都想好了。（如果同時存在候選A 和 候選B 的話）



MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+


MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | user  | range | age_name_email | age_name_email | 1       | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.02 sec)

複製程式碼

參考資料

書

部落格

測試資料集

mysql索引設計
2020-11-28
MySql索引
MySQL 索引優化全攻略
2015-06-15
MySql索引優化
MySQL 索引設計概要
2017-09-14
MySql索引
MySQL 的覆蓋索引與回表
2020-02-11
MySql索引
MySQL-索引優化全攻略
2018-12-07
MySql索引優化
mysql 索引設計原則
2024-04-12
MySql索引
MySQL 索引的設計原則
2024-04-04
MySql索引
MySQL效能優化之索引設計
2022-03-08
MySql優化索引
Mysql研磨之設計索引原則
2018-05-07
MySql索引
(7)mysql 索引的設計和使用
2017-01-18
MySql索引
MySQL大表設計
2023-11-21
MySql
MySQL資料庫表索引取樣統計
2024-09-02
MySql資料庫索引
MySQL管理表和索引
2018-03-13
MySql索引
「 MySQL高階篇」MySQL索引原理，設計原則
2022-02-04
MySql索引
Mysql索引結構與索引原理
2017-09-22
MySql索引
MySQL | 05 如何設計高效能的索引？
2018-12-13
MySql索引
阿里面試：MySQL如何設計索引更高效？
2021-01-11
阿里面試MySql索引
MySQL庫表設計小技巧
2020-05-08
MySql
ORACLE表統計資訊與列統計資訊、索引統計資訊
2018-03-25
Oracle索引
還傻傻分不清MySQL回表查詢與索引覆蓋？
2023-02-20
MySql索引
Oracle表與索引的分析及索引重建
2013-12-30
Oracle索引
阿里面試官：MySQL如何設計索引更高效？
2021-01-12
阿里面試MySql索引
MySQL-08.索引的建立和設計原則
2024-05-03
MySql索引
MySQL設計與優化
2018-09-06
MySql優化
Oracle表與索引的分析及索引重建(轉）
2012-05-24
Oracle索引
MySQL建庫建表索引規範
2020-10-27
MySql索引
MySQL索引失效行鎖變表鎖
2020-10-03
MySql索引
mysql對資料庫表建索引
2017-05-27
MySql資料庫索引
MySQL 覆蓋索引、回表查詢
2024-04-04
MySql索引
關於MySql資料庫設計表與查詢耗時分析
2016-03-17
MySql資料庫
程式設計師養生攻略
2014-06-27
程式設計師
淺談MySQL的B樹索引與索引優化
2018-03-26
MySql索引優化
MySQL索引與查詢優化
2019-04-13
MySql索引優化
Mysql索引的建立與刪除
2021-06-01
MySql索引
理解 MySQL（2）：索引與優化
2015-05-25
MySql索引優化
MySQL 高效能表設計規範
2017-07-27
MySql
表資料量影響MySQL索引選擇
2018-10-27
MySql索引
程式設計師面試之MySQL資料庫表的設計
2021-08-27
程式設計師面試MySql資料庫