mysql之高效能索引

一灰灰發表於2018-03-22

MySql索引

mysql之高效能索引

當db的量達到一定數量級之後，每次進行全表掃描效率就會很低，因此一個常見的方案是建立一些必要的索引作為優化手段，那麼問題就來了：

那麼什麼是索引呢？
索引的實現原理是怎樣的？
我們通常說的聚集索引，非聚集索引的區別是什麼？
如何建立和使用索引呢？

I. 索引介紹

MySQL官方對索引的定義為：索引是幫助MySQL高效獲取資料的資料結構。簡而言之,索引是資料結構

1. 幾種樹的結構

a. B+樹

單來說就是一種為磁碟或者其他儲存裝置而設計的一種平衡二叉樹,在B+tree中所有記錄都按照key的大小存放在葉子結點上，各葉子結點直接用指標連線

b. 二叉樹

二叉樹的規則是父節點大於左孩子節點，小於右孩子節點

c. 平衡二叉樹

首先是一個二叉樹，但是要求任意一個節點的左右孩子節點的高度差不大於1

d. B樹

首先是一個平衡二叉樹，但是又要求每個葉子節點到根節點的距離相等

那麼B樹和B+樹的區別是什麼呢？

B+樹的葉子節點可以包含一個指標，指向另一個葉子節點
B+樹鍵值的拷貝存在非葉子節點；鍵值+記錄儲存在葉子節點

2. InnoDB引擎之B+樹

mysql的InnnoDB引擎採用的B+樹，只有葉子節點儲存對應的資料列，有以下好處

葉子結點通常包含較多的記錄，具有較高的扇出性(可理解為每個節點對應的下層節點較多)，因此樹的高度較低(3~4)，而樹的高度也決定了磁碟IO的次數，從而影響了資料庫的效能。一般情況下，IO次數與樹的高度是一致的
對於組合索引，B+tree索引是按照索引列名(從左到右的順序)進行順序排序的，因此可以將隨機IO轉換為順序IO提升IO效率;並且可以支援order by \group等排序需求;適合範圍查詢

3. hash索引

hash索引，相比較於B樹而言，不需要從根節點到葉子節點的遍歷，可以一次定位到位置，查詢效率更高，但缺點也很明顯

僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢
- 因為是通過hash值進行計算，所以只能精確查詢，hash值是沒什麼規律的，不能保證順序和原來一致，所以範圍查詢不行
無法進行排序
- 原因同上
不支援部分索引
- hash值的計算，是根據完整的幾個索引列計算，如果少了其中一個乃至幾個，這個hash值就沒法計算了
hash碰撞

4. 聚集索引與非聚集索引

a. 聚集索引

InnoDB的資料檔案本身就是索引檔案，B+Tree的葉子節點上的data就是資料本身，key為主鍵，非葉子節點存放<key,address>，address就是下一層的地址

聚簇索引的結構圖:

b. 非聚集索引

非聚簇索引，葉子節點上的data是主鍵(即聚簇索引的主鍵，所以聚簇索引的key，不能過長)。為什麼存放的主鍵，而不是記錄所在地址呢，理由相當簡單，因為記錄所在地址並不能保證一定不會變，但主鍵可以保證

非聚簇索引結構圖：

從非聚集索引的結構上，可以看出這種場景下的定位流程：

先通過非聚集索引，定位到對應的葉子節點，找到對應的主鍵
根據上面找到的主鍵，在聚集索引中，定位到對應的葉子節點（獲取資料）

5. 索引的優點

避免全表掃描（當走不到索引時，就只能一個一個的去匹配；如果走索引，則可以根據B樹來定位）
使用索引可以幫助伺服器避免排序或者臨時表（葉子節點上的指標，可以有效的支援範圍查詢；此外葉子節點本身就是根據key進行排序的）
索引將隨機IO變成順序IO

6. 適用範圍

索引並不是適用於任何情況。對於中型、大型表適用。對於小型表全表掃描更高效。而對於特大型表，考慮”分割槽”技術

II. 索引的使用原則

一般我們在建立表的時候，需要指定primary key, 這樣就可以確定聚集索引了，那麼如何新增非聚集索引呢？

1. 索引的幾個語法

建立索引

-- 建立索引
create index `idx_img` on newuser(`img`);

-- 檢視
show create table newuser\G;
複製程式碼

輸出

show create table newuser\G
*************************** 1. row ***************************
       Table: newuser
Create Table: CREATE TABLE `newuser` (
  `userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '使用者id',
  `username` varchar(30) DEFAULT '' COMMENT '使用者登入名',
  `nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '使用者暱稱',
  `password` varchar(50) DEFAULT '' COMMENT '使用者登入密碼 & 密文根式',
  `address` text COMMENT '使用者地址',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '使用者郵箱',
  `phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '使用者手機號',
  `img` varchar(100) DEFAULT '' COMMENT '使用者頭像',
  `extra` text,
  `isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  PRIMARY KEY (`userId`),
  KEY `idx_username` (`username`),
  KEY `idx_nickname` (`nickname`),
  KEY `idx_email` (`email`),
  KEY `idx_phone` (`phone`),
  KEY `idx_img` (`img`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
複製程式碼

另一種常見的新增索引方式

alter table newuser add index `idx_extra_img`(`isDeleted`, `img`);

-- 檢視索引
show index from newuser;
複製程式碼

輸出結果

+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newuser |          0 | PRIMARY       |            1 | userId      | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_username  |            1 | username    | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| newuser |          1 | idx_nickname  |            1 | nickname    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_email     |            1 | email       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_phone     |            1 | phone       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_img       |            1 | img         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| newuser |          1 | idx_extra_img |            1 | isDeleted   | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_extra_img |            2 | img         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
複製程式碼

刪除索引

drop index `idx_extra_img` on newuser;
drop index `idx_img` on newuser;

-- 檢視索引
show index from newuser;
複製程式碼

輸出

show index from newuser;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newuser |          0 | PRIMARY      |            1 | userId      | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_username |            1 | username    | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| newuser |          1 | idx_nickname |            1 | nickname    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_email    |            1 | email       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_phone    |            1 | phone       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
複製程式碼

強制走索引的一種方式

語法： select * from table force index(索引) where xxx

explain select * from newuser force index(PRIMARY) where userId not in (3, 2, 5);
-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
-- | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | range | PRIMARY       | PRIMARY | 8       | NULL |    4 | Using where |
-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+


explain select * from newuser where userId not in (3, 2, 5);
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
複製程式碼

2. 索引使用規則

當一個表內有多個索引時，如何判斷自己的sql是否走到了索引，走的是哪個索引呢？

可以通過 explain 關鍵字來進行輔助判斷，當然在實際寫sql時，我們也有必要了解下索引匹配的規則，避免設定了一些冗餘的索引，或者寫出一些走不到索引的sql

測試的表結構如下

*************************** 1. row ***************************
       Table: newuser
Create Table: CREATE TABLE `newuser` (
  `userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '使用者id',
  `username` varchar(30) DEFAULT '' COMMENT '使用者登入名',
  `nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '使用者暱稱',
  `password` varchar(50) DEFAULT '' COMMENT '使用者登入密碼 & 密文根式',
  `address` text COMMENT '使用者地址',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '使用者郵箱',
  `phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '使用者手機號',
  `img` varchar(100) DEFAULT '' COMMENT '使用者頭像',
  `extra` text,
  `isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  PRIMARY KEY (`userId`),
  KEY `idx_username` (`username`),
  KEY `idx_nickname_email_phone` (`nickname`,`email`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
複製程式碼

a. 最左字首匹配原則

這個主要是針對多列非聚簇索引而言，比如有下面這個索引idx_nickname_email_phone(nickname, email, phone), nickname 定義在email的前面，那麼下面這幾個語句對應的情況是

-- 走索引
explain select * from newuser where nickname='小灰灰' and email='greywolf@xxx.com';

-- 1. 匹配nickname，可以走索引
explain select * from newuser where nickname='小灰灰';

-- 輸出:
-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
-- | id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
-- |  1 | SIMPLE      | newuser | ref  | idx_nickname_email | idx_nickname_email | 92      | const |    1 | Using index condition |
-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+


-- 2. 雖然匹配了email, 但是不滿足最左匹配，不走索引
explain select * from newuser where email='greywolf@xxx.com';

-- 輸出
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
複製程式碼

b. 無法跳過某個列使用後續索引列

即對索引idx_nickname_email_phone(nickname, email, phone), 如果你的sql中，只有 nickname 和 phone, 那麼phone走不到索引，因為不能跳過中間的email走索引

c. 範圍查詢後的列無法使用索引

如 >, <, between, like這種就是範圍查詢，下面的sql中，email 和phone都無法走到索引，因為nickname使用了範圍查詢

select * from newuser where nickname like '小灰%' and email='greywolf@xxx.com' and phone=15971112301 limit 10;
複製程式碼

d. 列作為函式引數或表示式的一部分

-- 走不到索引
explain select * from newuser where userId+1=2 limit 1;


-- 輸出
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
複製程式碼

3. 索引缺點

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。
建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不太嚴重，但如果你在一個大表上建立了多種組合索引，索引檔案的會膨脹很快。

4. 注意事項

索引不會包含有NULL值的列
使用短索引
索引列排序
- MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引
like語句操作
- 一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引
不要在列上進行運算
- select * from users where YEAR(adddate)<2007;
儘量不使用NOT IN和<>操作

5. sql使用策略

a. 使用一個sql代替多個sql

通常建議是使用一個sql來替代多個sql的查詢

當然若sql執行效率很低，或者出現delete等導致鎖表的操作時，也可以採用多個sql，避免阻塞其他sql

b. 分解關聯查詢

將關聯join儘量放在應用中來做，儘量執行小而簡單的的sql

分解後的sql簡單，利於使用mysql快取
執行分解後的sql，減少鎖競爭
更好的擴充套件性和維護性（sql簡單）
關聯sql使用的是內嵌迴圈演算法nestloop，而應用中可以使用hashmap等結構處理資料，效率更高

c. count

count(*) 統計的是行數
count(列名) 統計的是列不為null的數量

d. limit

limit offset, size; 分頁查詢，會查詢出 offset + size 條資料，獲取最後的size條資料

如 limit 1000, 20 則會查詢出滿足條件的1020條資料，然後將最後的20個返回，所以儘量避免大翻頁查詢

e. union

需要將where、order by、limit 這些限制放入到每個子查詢，才能重分提升效率。另外如非必須，儘量使用Union all，因為union會給每個子查詢的臨時表加入distinct，對每個臨時表做唯一性檢查，效率較差。

6. mysql使用查詢

a. 檢視索引

-- 單位為GB
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';
複製程式碼

b. 檢視錶空間

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size'   
FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName'; 
複製程式碼

c. 檢視資料庫中所有表的資訊

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',   
    table_rows AS 'Number of Rows',   
    CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',   
    CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,   
    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'  
FROM information_schema.TABLES   
WHERE table_schema LIKE 'databaseName';
複製程式碼

IV. 其他

參考

深入理解Mysql——高效能索引與高效能SQL

個人部落格：一灰灰Blog

基於hexo + github pages搭建的個人部落格，記錄所有學習和工作中的博文，歡迎大家前去逛逛

宣告

盡信書則不如，已上內容，純屬一家之言，因本人能力一般，見識有限，如發現bug或者有更好的建議，隨時歡迎批評指正

微博地址: 小灰灰Blog
QQ：一灰灰/3302797840

掃描關注

MySQL索引效能分析
2021-01-21
MySql索引
MYSQL索引及高效能索引策略
2020-09-11
MySql索引
Mysql-高效能索引
2018-10-12
MySql索引
MySQL 索引效能分析 show profiles
2024-04-04
MySql索引
高效能MySQL-索引
2021-05-15
MySql索引
MySQL 索引的型別——《高效能MySQL》
2020-11-23
MySql索引型別
MySQL 效能優化——B+Tree 索引
2019-03-16
MySql優化索引
「MySQL」高效能索引優化策略
2019-03-02
MySql索引優化
高效能MySQL實戰（二）：索引
2023-09-28
MySql索引
MySQL效能優化之索引設計
2022-03-08
MySql優化索引
【MySQL】三、效能優化之覆蓋索引
2018-10-11
MySql優化索引
Mysql效能優化：如何給字串加索引？
2020-04-02
MySql優化字串索引
mysql覆蓋索引高效能的探究
2021-09-11
MySql索引
MySQL | 05 如何設計高效能的索引？
2018-12-13
MySql索引
效能調優-Mysql索引資料結構詳解與索引優化
2021-11-04
MySql索引資料結構優化
MySQL-效能優化-索引和查詢優化
2018-06-03
MySql優化索引
mysql索引之字首索引
2019-08-20
MySql索引
ElasticSearch 索引 VS MySQL 索引
2020-10-09
Elasticsearch索引MySql
MySQL效能優化 - 別再只會說加索引了
2022-05-02
MySql優化索引
DRBD物理同步方式實現MySQL之高可用
2021-04-25
MySql
mysql索引
2024-05-11
MySql索引
[Mysql]索引
2024-07-05
MySql索引
MySQL 索引
2024-12-05
MySql索引
MySQL — 索引
2022-03-22
MySql索引
單執行緒Redis效能為何如此之高？
2019-09-25
執行緒Redis
MySQL索引系列：全文索引
2020-11-07
MySql索引
MySQL 字串索引和字首索引
2024-11-06
MySql字串索引
MySQL全面瓦解25：構建高效能索引（案例分析篇）
2021-03-09
MySql索引
MongoDB索引，效能分析
2018-09-18
MongoDB索引
高效能索引
2018-07-01
索引
MySQL 索引初探
2018-11-09
MySql索引
Mysql索引使用
2024-01-12
MySql索引
MySQL索引原理
2019-07-25
MySql索引
MySQL 二索引
2020-11-02
MySql索引
MySQL 索引 +explain
2019-05-17
MySql索引AI
MySQL的索引
2024-10-09
MySql索引
MySQL InnoDB 索引
2021-05-24
MySql索引
mysql組合索引，abc索引命中
2020-09-23
MySql索引

mysql之高效能索引

mysql之高效能索引

I. 索引介紹

1. 幾種樹的結構

a. B+樹

b. 二叉樹

c. 平衡二叉樹

d. B樹

2. InnoDB引擎之B+樹

3. hash索引

4. 聚集索引與非聚集索引

a. 聚集索引

b. 非聚集索引

5. 索引的優點

6. 適用範圍

II. 索引的使用原則

1. 索引的幾個語法

2. 索引使用規則

a. 最左字首匹配原則

b. 無法跳過某個列使用後續索引列

c. 範圍查詢後的列無法使用索引

d. 列作為函式引數或表示式的一部分

3. 索引缺點

4. 注意事項

5. sql使用策略

a. 使用一個sql代替多個sql

b. 分解關聯查詢

c. count

d. limit

e. union

6. mysql使用查詢

a. 檢視索引

b. 檢視錶空間

c. 檢視資料庫中所有表的資訊

IV. 其他

參考

個人部落格： 一灰灰Blog

宣告

掃描關注

相關文章

個人部落格：一灰灰Blog