淺談慢SQL優(yōu)化之索引的作用
慢SQL與索引的關(guān)系
慢SQL優(yōu)化原則
數(shù)據(jù)庫也是應(yīng)用，MySQL 作為一種磁盤數(shù)據(jù)庫，屬于典型的 IO 密集型應(yīng)用，并且隨機 IO 比順序 IO 更昂貴。
真實的慢 SQL 往往會伴隨著大量的行掃描、臨時文件排序，直接影響就是磁盤 IO 升高、CPU 使用率升高，正常 SQL 也變?yōu)榱寺?SQL，對于應(yīng)用來說就是大面積執(zhí)行超時。
線上很多事故都與慢 SQL 有關(guān)，因此慢 SQL 治理已成為 DBA 與業(yè)務(wù)研發(fā)的共識。
慢SQL的優(yōu)化原則為：減少數(shù)據(jù)訪問量與減少計算操作
減少訪問量：
- 創(chuàng)建合適的索引
- 減少不必要訪問的列
- 使用覆蓋索引
- 語句改寫
- 數(shù)據(jù)結(jié)轉(zhuǎn)
減少計算操作：
- 排序列加入索引
- 適當(dāng)?shù)牧腥哂?/li>
- SQL 拆分
- 計算功能拆分
可以將慢 SQL 優(yōu)化的方法分為三類：
- 查詢優(yōu)化
- 索引優(yōu)化
- 庫表結(jié)構(gòu)優(yōu)化
其中索引是數(shù)據(jù)庫中用來提升性能的最常用工具。
可是，為什么索引可以加快查詢，索引一定可以加快查詢嗎？
索引的作用
要回答這個問題，可以對比沒有索引與有索引時查詢操作的性能差異。
在此之前，首先介紹下查詢操作的處理流程。
查詢操作可以分為以下兩步：
- 定位到記錄所在的頁
- 從所在的頁中定位到具體的記錄
其中從頁中定位記錄的方法依賴每個頁面中創(chuàng)建的Page Directory（頁目錄），因此關(guān)鍵在于如何定位頁。
數(shù)據(jù)保存在磁盤上，數(shù)據(jù)處理發(fā)生在內(nèi)存中，數(shù)據(jù)頁是磁盤與內(nèi)存之間交互的基本單位，也是 MySQL 管理存儲空間的基本單位，大小默認為 16KB。
因此通常一次最少從磁盤中讀取 16KB 的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的 16KB 內(nèi)容刷新到磁盤中。
要理解索引的作用，需要首先明確沒有索引時如何定位頁。
沒有索引時，由于每個頁中的數(shù)據(jù)沒有規(guī)律，因此無法快速定位記錄所在的頁，只能從第一個頁沿雙向鏈表向后遍歷，也就是說需要遍歷所有數(shù)據(jù)頁依次判斷是否滿足查詢條件。
簡單來說，沒有索引時每次查詢都是全表掃描。
因此索引需要解決的主要問題就是實現(xiàn)每個數(shù)據(jù)頁中數(shù)據(jù)有規(guī)律，具體是保證下一個數(shù)據(jù)頁中用戶記錄的索引列值必須大于上一個頁中用戶記錄的索引列值。
索引是存儲引擎用于快速查找的一種排序的數(shù)據(jù)結(jié)構(gòu)。
有索引時，優(yōu)化器首先基于成本自動選擇最優(yōu)的執(zhí)行計劃，然后基于索引的有序性可以通過掃描更少的數(shù)據(jù)頁定位到滿足條件的數(shù)據(jù)。
具體原因與索引的數(shù)據(jù)結(jié)構(gòu)有關(guān)，下面基于索引的數(shù)據(jù)結(jié)構(gòu)介紹常見的索引生效與索引失效的場景。
索引
索引的數(shù)據(jù)結(jié)構(gòu)
索引是一種以空間換時間思想的具體實現(xiàn)，用于加速查詢。
MySQL 中由存儲引擎層實現(xiàn)索引，InnoDB 存儲引擎中基于 B+ 樹實現(xiàn)，因此每個索引都是一棵 B+ 樹。
索引用于組織頁，頁用于組織行記錄。在介紹索引的結(jié)構(gòu)之前首先介紹頁的結(jié)構(gòu)，如下圖所示。
其中：
- 每個數(shù)據(jù)頁中的記錄會按照主鍵值從小到大的順序組成一個單向鏈表，依賴行記錄的
Page Header中next_record屬性實現(xiàn)，其中保存下一條記錄相對于本條記錄的地址偏移量； - 數(shù)據(jù)頁之間組成一個雙向鏈表，依賴數(shù)據(jù)頁的
File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實現(xiàn)，其中保存本頁的上一個和下一個頁的頁號。
多個頁通過樹進行組織，其中保存用戶數(shù)據(jù)與目錄項。目錄項中保存頁的用戶記錄中主鍵的最小值與頁號，從而保證下一個數(shù)據(jù)頁中用戶記錄的主鍵值大于上一個頁中用戶記錄的主鍵值。
其中：
- 用戶數(shù)據(jù)保存在葉子節(jié)點，目錄項保存在非葉子節(jié)點，每個節(jié)點中可能保存多個頁；
- 最上面的節(jié)點稱為根節(jié)點，根節(jié)點的地址保存在內(nèi)存的數(shù)據(jù)字典中；
- B+ 樹的深度一般控制在 3 層以內(nèi)，因此定位到單條記錄不超過 3 次 IO。
因此，頁面和記錄是排好序的，就可以通過二分法來快速定位查找。
有索引時，查詢操作變成了什么樣呢？
- 從 B+ 樹的根節(jié)點出發(fā)，一層一層向下搜索目錄項，由于上層節(jié)點保存的都是下層節(jié)點的最小值，因此可以快速定位到數(shù)據(jù)可能所在的頁；
- 如果數(shù)據(jù)頁在緩存池中，直接從內(nèi)存中獲取，否則從磁盤加載到內(nèi)存中；
- 數(shù)據(jù)頁內(nèi)部二分查找定位滿足條件的記錄行。
索引保存的數(shù)據(jù)
索引中保存的數(shù)據(jù)與索引的類型有關(guān)。
索引可以分為兩種類型：
- 聚簇索引，主鍵索引。葉子節(jié)點中保存主鍵值+對應(yīng)的完整行記錄，目錄項中保存主鍵最小值+頁號。InnoDB 屬于索引組織表，每張表都有聚簇索引，因此表必須有主鍵，表中行的物理順序與索引的邏輯順序相同；
- 非聚簇索引，二級索引，在非主鍵的其他列上建的索引。葉子節(jié)點中保存索引列的值+對應(yīng)的主鍵值，目錄項中保存索引列最小值+對應(yīng)的主鍵值+頁號。
引性能相關(guān)的概念：
| 概念 | explain.extra |
|---|---|
| 覆蓋索引 | Using index |
| 回表 | Using where |
| 索引下推 | Using index condition |
- 覆蓋索引，當(dāng)二級索引中包含要查詢的所有字段時，這個索引稱為覆蓋索引；
- 回表，當(dāng)二級索引中不包含要查詢的所有字段時，就需要先通過二級索引查出主鍵索引，再通過主鍵索引查詢二級索引中沒有的其他列的數(shù)據(jù)，這個過程叫做回表；
- 索引下推，用于優(yōu)化使用二級索引從表中檢索行的實現(xiàn)。條件過濾可以下推到存儲引擎層進行，先由索引元組（index tuple）根據(jù)查詢條件進行過濾，滿足條件的前提下才回表，否則跳過，相當(dāng)于延遲加載數(shù)據(jù)行，因此 ICP 可以降低回表次數(shù)與 IO 次數(shù)。
正常情況下看不到二級索引中隱藏的主鍵，但實際上，如下所示查看鎖信息，顯示LOCK_DATA: '17118168721', 2，其中 2 就是二級索引中保存的主鍵值。
? ? ? ? ? ? ? ENGINE:INNODB ? ? ? ENGINE_LOCK_ID:140123070938328:14:7:4:140122972537552 ENGINE_TRANSACTION_ID:2032566 ? ? ? ? ? THREAD_ID:157 ? ? ? ? ? ? EVENT_ID:44 ? ? ? OBJECT_SCHEMA: test_zk ? ? ? ? OBJECT_NAME: t_lock_test ? ? ? PARTITION_NAME:NULL ? SUBPARTITION_NAME:NULL ? ? ? ? ? INDEX_NAME: idx_uk_mobile OBJECT_INSTANCE_BEGIN:140122972537552 ? ? ? ? ? LOCK_TYPE: RECORD ? ? ? ? ? LOCK_MODE: X,REC_NOT_GAP ? ? ? ? LOCK_STATUS: GRANTED ? ? ? ? ? LOCK_DATA:'17118168721',2
如下所示，工作中多次遇到研發(fā)創(chuàng)建二級索引時顯式指定主鍵，實際上是不需要的，二級索引末尾自動保存主鍵。
alter table payable_unsettled add index idx_seller_no_recno_id(seller_no, receipt_no,id) using BTREE;
因此二級索引+主鍵與聯(lián)合索引的相同點是依次排序，不同點是索引中保存的數(shù)據(jù)不同。
索引生效的場景
等值查詢
線上環(huán)境多次遇到表沒有創(chuàng)建二級索引，只有主鍵索引。
SQL
select sys_no,area_no,area_name,dc_no,dc_name,wh_no,wh_name, business_type,business_no,item_code,item_grade, item_result,item_remark,status, yn,ts,create_time,create_pin,update_time,update_pin from evaluate_result where yn =1 ?and wh_no ='611-887-2' ?and business_no ='QNSYKF23020900000018' ?and create_pin ='13940137489' orderby update_time desc; ? # 執(zhí)行用時 5 rows in set(7.311125 sec)
執(zhí)行計劃，顯示全表掃描
表結(jié)構(gòu)，顯示查詢字段無索引
mysql>show create table evaluate_result \G ***************************1.row*************************** ? ? ? Table: evaluate_result CreateTable:CREATE TABLE `evaluate_result`( ?`sys_no` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '內(nèi)部主鍵', ?`wh_no` varchar(32) NOT NULL DEFAULT '' COMMENT'倉庫編碼', ?`business_no` varchar(20) NOT NULL DEFAULT '' COMMENT'調(diào)研業(yè)務(wù)主鍵', ? ... ? PRIMARY KEY(`sys_no`) )ENGINE=InnoDB AUTO_INCREMENT=2007412 DEFAULT CHARSET=utf8 COMMENT='評價結(jié)果表' 1rowinset(0.00 sec)
優(yōu)化方法：創(chuàng)建索引
alter table evaluate_result add index idx_wh_bus_no(wh_no,business_no);
執(zhí)行計劃
***************************1.row*************************** ? ? ? ? ? id:1 select_type: SIMPLE ? ? ? ?table: evaluate_result ? partitions:NULL ? ? ? ? type: ref possible_keys: idx_wh_bus_no ? ? ? ? ?key: idx_wh_bus_no ? ? key_len:160 ? ? ? ? ref: const,const ? ? ? ? rows:5 ? ? filtered:1.00 ? ? ? Extra:Using index condition;Using where;Using filesort 1rowinset,1 warning (0.00 sec) ? # 執(zhí)行用時 5 rows in set(0.01 sec)
其中：
- key_len: 160，表明聯(lián)合索引的兩個字段都用到了，(32+20) * 3+2 * 2 = 160。
等值查詢索引生效的原因是相同值的數(shù)據(jù)組成單向鏈表，因此定位到滿足條件的 5 行數(shù)據(jù)需要掃描的行數(shù)從 1377442 行降低到 5 行。
范圍查詢
SQL
select id from board_chute where ?status=1 ?and create_time <= date_sub(now(),interval 24 hour);
執(zhí)行計劃，顯示全表掃描
***************************1.row*************************** ? ? ? ? ? id:1 select_type: SIMPLE ? ? ? ?table: board_chute ? partitions:NULL ? ? ? ? type:ALL possible_keys: idx_create_time ? ? ? ? ?key:NULL ? ? key_len:NULL ? ? ? ? ref:NULL ? ? ? ? rows:407632 ? ? filtered:5.00 ? ? ? Extra:Using where 1rowinset,1 warning (0.00 sec)
查詢字段有索引，但是索引失效
?KEY`idx_create_time`(`create_time`),
status 字段的區(qū)分度
mysql> select status,count(*) from board_chute group by status; +--------+----------+ | status | count(*) | +--------+----------+ | 0 | 407317 | | 1 | 4309 | +--------+----------+ 2 rows in set (0.17 sec)
因此范圍查詢索引失效的原因是查看數(shù)據(jù)量大并且需要回表。
優(yōu)化方法：創(chuàng)建聯(lián)合索引實現(xiàn)覆蓋索引
alter table board_chute add index idx_status_create_time(status, create_time);
執(zhí)行計劃，顯示 Using index 表明用到了覆蓋索引，不需要回表。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: board_chute
partitions: NULL
type: range
possible_keys: idx_create_time,idx_status_create_time
key: idx_status_create_time
key_len: 8
ref: NULL
rows: 203816
filtered: 10.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)范圍查詢索引生效的原因是葉子節(jié)點中除了保存索引，還保存指向下個節(jié)點的指針，因此遍歷葉子節(jié)點就可以獲得范圍值。
因此建議使用 between and 代替 in，如select * from T where k in (1,2,3,4,5);對應(yīng) 5 次樹的搜索，而select * from T where k between 1 and 5;對應(yīng) 1 次樹的搜索。
假設(shè)索引基于哈希表實現(xiàn)，可以通過散列函數(shù)將 key 值轉(zhuǎn)換成一個固定的地址，如果發(fā)生哈希碰撞就在這個位置拉出一個鏈表。因此哈希表的優(yōu)點是插入操作的速度快，根據(jù) key 直接往后追加即可。但由于散列函數(shù)的離散特性，經(jīng)過散列函數(shù)處理后的 key 將失去原有的順序，所以哈希表無法滿足范圍查詢，只適合等值查詢。
注意上述索引生效的場景并非絕對成立，需要回表的記錄越多，優(yōu)化器越傾向于使用全表掃描，反之傾向于使用二級索引 + 回表的方式。
回表查詢成本高有兩點原因：
- 需要使用到兩個 B+ 樹索引，一個二級索引，一個聚簇索引；
- 訪問二級索引使用順序 I/O，訪問聚簇索引使用隨機 I/O。
因此有兩條建議：
- 建議為區(qū)分度高的字段創(chuàng)建索引，并且將區(qū)分度高的字段優(yōu)先放在聯(lián)合索引前面；
- 建議優(yōu)先使用覆蓋索引，必須要回表時也需要控制回表的記錄數(shù)，從而降低索引失效的風(fēng)險。
索引失效的場景
違反最左匹配原則
SQL
select ?count(*) from sort_cross_detail where yn =1 ?and org_id =3 ?and site_type =16 ?and site_code ='121671';
執(zhí)行計劃，顯示全表掃描
盡管當(dāng)前有聯(lián)合索引 idx_site_type(SUB_TYPE, THIRD_TYPE, SITE_TYPE )，但由于查詢條件中不包括 SUB_TYPE 字段，因此違反最左匹配原則，導(dǎo)致索引失效。
當(dāng)前查詢條件的多個字段區(qū)分度由高到低為 site_code、org_id、site_type。
優(yōu)化方法：site_code 字段區(qū)分度很高，創(chuàng)建單列索引。
alter table sort_cross_detail add index `idx_site_code` (`SITE_CODE`);
執(zhí)行計劃
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sort_cross_detail
partitions: NULL
type: ref
possible_keys: idx_site_code
key: idx_site_code
key_len: 99
ref: const
rows: 1336
filtered: 0.10
Extra: Using where
1 row in set, 1 warning (0.00 sec)其中：
- 使用聯(lián)合索引過程中可以通過執(zhí)行計劃中的 key_len 字段評估具體 SQL 使用到了聯(lián)合索引中的幾個字段；
- 聯(lián)合索引中頁面和記錄首先按照聯(lián)合索引前面的列排序，如果該列值相同，再按照聯(lián)合索引后邊的列排序。
違反最左匹配原則導(dǎo)致索引失效的原因是只有當(dāng)索引前面的列相同時，后面的列才有序。
下面結(jié)合 innodb_ruby 工具解析 InnoDB 數(shù)據(jù)文件查看記錄保存的順序驗證聯(lián)合索引中索引前面的列不同時，后面的列可能無序。
準備測試數(shù)據(jù)。
mysql> show create table t_index \G
*************************** 1. row ***************************
Table: t_index
Create Table: CREATE TABLE `t_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT '0',
`name` varchar(10) DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_age_name` (`age`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into t_index(age, name) values(8, "Tom"),(8, "David"), (10, "Andy");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_index;
+----+------+-------+
| id | age | name |
+----+------+-------+
| 2 | 8 | David |
| 1 | 8 | Tom |
| 3 | 10 | Andy |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> explain select * from t_index \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_index
partitions: NULL
type: index
possible_keys: NULL
key: idx_age_name
key_len: 38
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)其中：
- insert 時，三條記錄按照 name 字段逆序；
- select 時，三條記錄按照聯(lián)合索引排序，并不是按照主鍵排序。
分別查看索引以及索引中保存的數(shù)據(jù)
[root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index space-indexes id ? ? ? ? name ? ? ? ? ? ? ? ? ? ? ? ? ? root ? ? ? fseg ? ? ? fseg_id ? ? used ? ? ? allocated ? fill_factor 218 ? ? ? ? PRIMARY ? ? ? ? ? ? ? ? ? ? ? ? 3 ? ? ? ? ? internal ? ?1 ? ? ? ? ? 1 ? ? ? ? ? 1 ? ? ? ? ? 100.00% ? ? 218 ? ? ? ? PRIMARY ? ? ? ? ? ? ? ? ? ? ? ? 3 ? ? ? ? ? leaf ? ? ? ?2 ? ? ? ? ? 0 ? ? ? ? ? 0 ? ? ? ? ? 0.00% ? ? ? 219 ? ? ? ? idx_age_name ? ? ? ? ? ? ? ? ? ?4 ? ? ? ? ? internal ? ?3 ? ? ? ? ? 1 ? ? ? ? ? 1 ? ? ? ? ? 100.00% ? ? 219 ? ? ? ? idx_age_name ? ? ? ? ? ? ? ? ? ?4 ? ? ? ? ? leaf ? ? ? ?4 ? ? ? ? ? 0 ? ? ? ? ? 0 ? ? ? ? ? 0.00% ? ? ? [root@exps-test3 data]# [root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index -p 3 page-records Record 127: (id=1) → (age=8, name="Tom") Record 158: (id=2) → (age=8, name="David") Record 191: (id=3) → (age=10, name="Andy") [root@exps-test3 data]# [root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index -p 4 page-records Record 145: (age=8, name="David") → (id=2) Record 127: (age=8, name="Tom") → (id=1) Record 165: (age=10, name="Andy") → (id=3)
其中：
- 主鍵與二級索引的根節(jié)點頁號分別是 3 與 4；
- 查看聚簇索引中保存的記錄，按照主鍵排序；
- 查看二級索引中保存的記錄，聯(lián)合索引中當(dāng) age 相同時，name 有序，age 不同時，name 無序。
上面提到，數(shù)據(jù)字典中保存表的根節(jié)點的地址，具體是 INFORMATION_SCHEMA.INNODB_SYS_INDEXES 系統(tǒng)表的 space 與 page_no 字段，分別保存表空間 ID 與根節(jié)點頁號。
mysql> SELECT tables.name, indexs.space, indexs.name, indexs.page_no FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES as tables inner join INFORMATION_SCHEMA.INNODB_SYS_INDEXES as indexs on tables.table_id = indexs.table_id WHERE tables.NAME = 'test_zk/t_index'; +-----------------+-------+--------------+---------+ | name | space | name | page_no | +-----------------+-------+--------------+---------+ | test_zk/t_index | 106 | PRIMARY | 3 | | test_zk/t_index | 106 | idx_age_name | 4 | +-----------------+-------+--------------+---------+ 2 rows in set (0.00 sec)
其中 test_zk/t_index 表有兩個索引，對應(yīng)的根節(jié)點頁號分別等于 3 與 4，與上面數(shù)據(jù)文件解析的結(jié)果一致。
order by limit
SQL，不建議使用 select *
select
*
from
waybill_order_added_value_report_detail goodsInfo
WHERE
goodsInfo.is_delete = 0
AND goodsInfo.org_no = '418'
AND goodsInfo.distribute_no = '636'
AND (
goodsInfo.company_code = 'EBU4418046542406'
OR goodsInfo.company_name = 'EBU4418046542406'
)
AND goodsInfo.network_type = 1
AND goodsInfo.should_operator_time >= concat('2022-05-01', ' 00:00:00')
AND goodsInfo.should_operator_time <= concat('2022-05-31', ' 23:59:59')
AND goodsInfo.uniform_status = 0
ORDER BY
goodsInfo.id DESC
LIMIT
0, 20 \G
# 執(zhí)行用時
2 rows in set (1 min 9.71 sec)執(zhí)行計劃，主鍵全索引掃描，聯(lián)合索引失效
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goodsInfo
type: index
possible_keys: idx_org_dc_operator_time,idx_operator_time_network
key: PRIMARY
key_len: 8
ref: NULL
rows: 16156
Extra: Using where
1 row in set (0.00 sec)表結(jié)構(gòu)與查詢條件
# 查詢條件
WHERE
goodsInfo.org_no = '418'
AND goodsInfo.distribute_no = '636'
AND goodsInfo.should_operator_time >= concat('2022-05-01', ' 00:00:00')
AND goodsInfo.should_operator_time <= concat('2022-05-31', ' 23:59:59')
# 索引
KEY `idx_org_dc_operator_time` (`org_no`,`distribute_no`,`should_operator_time`),
KEY `idx_operator_time_network` (`should_operator_time`,`network_type`)將 limit 20 修改為 limit 30，SQL 如下所示。
select
*
from
waybill_order_added_value_report_detail goodsInfo
WHERE
...
ORDER BY
goodsInfo.id DESC
LIMIT
0, 30 \G
# 執(zhí)行用時
2 rows in set (0.06 sec)執(zhí)行計劃顯示當(dāng)改為 limit 30 時，聯(lián)合索引生效。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goodsInfo
type: range
possible_keys: idx_org_dc_operator_time,idx_operator_time_network
key: idx_org_dc_operator_time
key_len: 132
ref: NULL
rows: 19024
Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)可見，索引的選擇與 limit n 的 n 值也有關(guān)系。
從現(xiàn)象上看，當(dāng) limit n 的 n 值變大時，SQL的執(zhí)行反倒有可能變快了。
實際上，這是 MySQL 低版本中的 bug #97001，優(yōu)化器認為排序是個昂貴的操作，因此在執(zhí)行 order by id limit 這條 SQL 時，為了避免排序，并且認為當(dāng) limit n 的 n 很小時，全表掃描可以很快執(zhí)行完，因此選擇使用全表掃描，以避免額外的排序。
針對 MySQL 中 order by limit 或 group by limit 優(yōu)化器選擇錯誤索引的場景，常見的優(yōu)化方法有四種：
- 強制索引，通過 hint 固化執(zhí)行計劃，比如可以通過 force index 指定使用的索引，但是當(dāng)條件發(fā)生變化時有可能失效，因此生產(chǎn)環(huán)境中不建議使用；
- prefer_ordering_index，5.7.33 中已修復(fù)該 bug，因此建議新申請時使用 5.7.33 及以上版本，存量低版本建議升級，建議優(yōu)先使用該方法；
- 聯(lián)合索引，建議在合適的字段加聯(lián)合索引， 增強可選索引的區(qū)分度，讓優(yōu)化器認為這種方式優(yōu)于有序索引；
- order by (id+0)，通過 trick 的方式欺騙優(yōu)化器，由于 id 上進行了加法這種耗時操作，使優(yōu)化器認為此時基于全表掃描的會更耗性能，因此選擇基于成本選擇的索引。
優(yōu)化方法：order by (id+0)
select ... ORDER BY goodsInfo.id+0 DESC LIMIT 0, 20\G
執(zhí)行計劃
select ... ORDER BY goodsInfo.id+0 DESC LIMIT 0, 20\G
order by limit 導(dǎo)致索引失效的原因是當(dāng)查詢字段與排序字段不同時，如果使用查詢字段的索引，排序字段將無序。優(yōu)化器認為排序操作昂貴，因此優(yōu)先使用排序字段的索引。
隱式轉(zhuǎn)換
字段類型不一致或字符集不一致時自動隱式轉(zhuǎn)換將導(dǎo)致索引失效。
SQL
SELECT id FROM base_operating_report WHERE yn = 1 and ec_code = 42 order by inbound_time desc;
執(zhí)行計劃，顯示索引失效全表掃描
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: base_operating_report
partitions: NULL
type: ALL
possible_keys: idx_ecCode_transferCode
key: NULL
key_len: NULL
ref: NULL
rows: 36524
filtered: 1.00
Extra: Using where; Using filesort
1 row in set, 3 warnings (0.00 sec)查看警告信息，顯示隱式轉(zhuǎn)換導(dǎo)致索引失效
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: base_operating_report
partitions: NULL
type: ALL
possible_keys: idx_ecCode_transferCode
key: NULL
key_len: NULL
ref: NULL
rows: 36524
filtered: 1.00
Extra: Using where; Using filesort
1 row in set, 3 warnings (0.00 sec)表結(jié)構(gòu)
# 索引信息 KEY `idx_ecCode_transferCode` (`ec_code`, `transfer_code`) # 字段類型 `ec_code` varchar(64) DEFAULT NULL COMMENT '倉庫編碼'
優(yōu)化方法：將參數(shù)中的數(shù)值類型轉(zhuǎn)換成字符串
SELECT id FROM base_operating_report WHERE yn = 1 and ec_code = '42' order by inbound_time desc;
執(zhí)行計劃，顯示索引生效。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: base_operating_report
partitions: NULL
type: ref
possible_keys: idx_ecCode_transferCode
key: idx_ecCode_transferCode
key_len: 195
ref: const
rows: 443
filtered: 10.00
Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)隱式轉(zhuǎn)換導(dǎo)致索引失效的原因是字段上有函數(shù)，而函數(shù)并不一定是單調(diào)函數(shù)，因此會破壞索引本身的有序性。
IN
SQL
select IFNULL(count(DISTINCT (awi.id)), 0) from tc_attorney_waybill_info awi where awi.is_delete = 0 and awi.cur_transit_center_code in ( '2008' , '2052' , '2053' , '2054' , '2055' , '2056' , '2057' , '2058' , '2059' , '2061' , '2064' , '2069' , '2079' , '2084' , '2085' , '2094' , '2171' , '2201' , '2202' , '2207' , '2216' , '2258' , '2292' , '2301' , '2311' , '2324' , '2332' , '2334' , '2336' , '2349' , '2354' , '2355' , '2359' , '2367' , '2369' , '2373' , '2381' , '2385' ) and awi.send_time >= '2022-10-20 00:00:00' and awi.send_time <= '2022-11-18 23:59:59' and awi.split_send_package_times > 0 and awi.first_split_type = 1; # 執(zhí)行用時 1 rows in set (1.309 sec)
執(zhí)行計劃，顯示索引失效全表掃描
trace，顯示當(dāng)前的索引中全表掃描的成本最低，因此索引失效。
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_transit_code_waybill_code",
"ranges": [
"2008 <= cur_transit_center_code <= 2008",
"2052 <= cur_transit_center_code <= 2052",
"2053 <= cur_transit_center_code <= 2053",
"2054 <= cur_transit_center_code <= 2054",
"2055 <= cur_transit_center_code <= 2055",
"2056 <= cur_transit_center_code <= 2056",
"2057 <= cur_transit_center_code <= 2057",
"2058 <= cur_transit_center_code <= 2058",
"2059 <= cur_transit_center_code <= 2059",
"2061 <= cur_transit_center_code <= 2061",
"2064 <= cur_transit_center_code <= 2064",
"2069 <= cur_transit_center_code <= 2069",
"2079 <= cur_transit_center_code <= 2079",
"2084 <= cur_transit_center_code <= 2084",
"2085 <= cur_transit_center_code <= 2085",
"2094 <= cur_transit_center_code <= 2094",
"2171 <= cur_transit_center_code <= 2171",
"2201 <= cur_transit_center_code <= 2201",
"2202 <= cur_transit_center_code <= 2202",
"2207 <= cur_transit_center_code <= 2207",
"2216 <= cur_transit_center_code <= 2216",
"2258 <= cur_transit_center_code <= 2258",
"2292 <= cur_transit_center_code <= 2292",
"2301 <= cur_transit_center_code <= 2301",
"2311 <= cur_transit_center_code <= 2311",
"2324 <= cur_transit_center_code <= 2324",
"2332 <= cur_transit_center_code <= 2332",
"2334 <= cur_transit_center_code <= 2334",
"2336 <= cur_transit_center_code <= 2336",
"2349 <= cur_transit_center_code <= 2349",
"2354 <= cur_transit_center_code <= 2354",
"2355 <= cur_transit_center_code <= 2355",
"2359 <= cur_transit_center_code <= 2359",
"2367 <= cur_transit_center_code <= 2367",
"2369 <= cur_transit_center_code <= 2369",
"2373 <= cur_transit_center_code <= 2373",
"2381 <= cur_transit_center_code <= 2381",
"2385 <= cur_transit_center_code <= 2385"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1328061,
"cost": 1.59e6,
"chosen": false,
"cause": "cost"
},
{
"index": "idx_outstoretime",
"ranges": [
"0x99ae280000 <= send_time <= 0x99ae657efb"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1597295,
"cost": 1.92e6,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`tc_attorney_waybill_info` `awi`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 3194590,
"access_type": "scan",
"resulting_rows": 3.19e6,
"cost": 677027,
"chosen": true
}
]
},優(yōu)化方法：
- 創(chuàng)建一個更多查詢字段的聯(lián)合索引，減少回表次數(shù)；
- 縮小查詢的時間范圍，因為查詢的數(shù)據(jù)量比較大，而且用到的字段比較多，導(dǎo)致回表成本高。
IN 導(dǎo)致索引失效的原因是符合條件的數(shù)據(jù)量過大導(dǎo)致回表成本高于全表掃描。
分組字段無索引
提數(shù)，創(chuàng)建唯一鍵之前分組查詢是否有重復(fù)數(shù)據(jù)。
SQL
select operate_id, waybill_code, private_call_id from tos_resource.courier_call_out_record_0 group by operate_id, waybill_code, private_call_id having count(*) > 1;
執(zhí)行計劃，顯示全表掃描
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: courier_call_out_record_0
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1470107
filtered: 100.00
Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.01 sec)分組字段無聯(lián)合索引，有兩個索引覆蓋三個分組字段，因此索引無法使用。
KEY `courier_call_out_record_0_operate_id_IDX` (`operate_id`,`waybill_code`), KEY `courier_call_out_recourd_0_waybill_code` (`waybill_code`)
優(yōu)化方法：給分組字段創(chuàng)建聯(lián)合索引
alter table courier_call_out_record_0 add index `courier_call_out_record_0_composite_key` (`operate_id`,`waybill_code`,`private_call_id`);
執(zhí)行計劃
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: courier_call_out_record_0
partitions: NULL
type: index
possible_keys: courier_call_out_record_0_composite_key
key: courier_call_out_record_0_composite_key
key_len: 907
ref: NULL
rows: 2230391
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)分組字段無聯(lián)合索引導(dǎo)致全表掃描的原因是分組時需要先排序，因此只有當(dāng)分組字段在同一個索引中時才可以保證有序。
索引的優(yōu)缺點
索引的代價
通過以上分析可以發(fā)現(xiàn)索引不是萬能的，實際上有時候索引甚至?xí)懈弊饔谩?/p>
創(chuàng)建索引的代價可以分為兩類：
- 空間代價，索引需要占用磁盤空間，并且刪除索引并不會立即釋放空間，因此無法通過刪除索引的方式降低磁盤使用率；
- 時間代價，有的時候會發(fā)現(xiàn)創(chuàng)建索引后導(dǎo)致寫入變慢，原因是每次數(shù)據(jù)寫入后還需要對該記錄按照索引排序。因此經(jīng)常更新的列不建議創(chuàng)建索引。
可見，索引的優(yōu)點是可以加快查詢速度，缺點是占用內(nèi)存與磁盤空間，同時減慢了插入與更新操作的速度。
因此，B+ Tree 適用于讀多寫少的業(yè)務(wù)場景，相對應(yīng)的 LSM-Tree 適用于寫多讀少的業(yè)務(wù)場景，原因是每次數(shù)據(jù)寫入對應(yīng)一條日志追加寫入磁盤文件，用順序 IO 代替了隨機 IO。
索引使用的建議
關(guān)于索引的使用有以下幾點建議：
- 建議給區(qū)分度高的字段創(chuàng)建索引；
- 建議刪除冗余索引，否則優(yōu)化器可能使用 index_merge 導(dǎo)致選擇到錯誤的索引；
- 不建議使用強制索引，比如當(dāng)數(shù)據(jù)量或統(tǒng)計信息發(fā)生變化時，強制索引不一定最優(yōu)。
下面測試下與索引相關(guān)的兩個操作：
- 如果 SQL 中強制指定已刪除的索引，SQL 執(zhí)行會報錯嗎？
- 如果刪除字段，索引也會自動刪除嗎？
準備數(shù)據(jù)
mysql> create table t_index_drop like t_index;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t_index_drop \G
*************************** 1. row ***************************
Table: t_index_drop
Create Table: CREATE TABLE `t_index_drop` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT '0',
`name` varchar(10) DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_age_name` (`age`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)如果 SQL 中強制指定已刪除的索引，SQL 將直接報錯，生產(chǎn)環(huán)境中遇到過相關(guān)案例。
mysql> alter table t_index_drop drop index idx_age_name; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t_index_drop force index(idx_age_name); ERROR 1176 (42000): Key 'idx_age_name' doesn't exist in table 't_index_drop'
如果刪除字段，索引也會自動刪除。
mysql> alter table t_index_drop add index idx_age(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_index_drop \G
*************************** 1. row ***************************
Table: t_index_drop
Create Table: CREATE TABLE `t_index_drop` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT '0',
`name` varchar(10) DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_age_name` (`age`,`name`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table t_index_drop drop column age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_index_drop \G
*************************** 1. row ***************************
Table: t_index_drop
Create Table: CREATE TABLE `t_index_drop` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_age_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)基于優(yōu)化器選擇索引有可能選錯索引導(dǎo)致性能下降，而使用強制索引可能導(dǎo)致 SQL 執(zhí)行直接報錯。
結(jié)論
慢SQL的優(yōu)化原則是減少數(shù)據(jù)訪問量與減少計算操作，其中索引是數(shù)據(jù)庫中用來提升性能的最常用工具。
索引是一種用于快速查找的一種排序的數(shù)據(jù)結(jié)構(gòu)，基于以空間換時間的思想實現(xiàn)。
MySQL 中由存儲引擎層實現(xiàn)索引，InnoDB 存儲引擎中基于 B+ 樹實現(xiàn)，因此每個索引都是一棵 B+ 樹。
索引用于組織頁，頁用于組織行記錄。
其中：
- 每個數(shù)據(jù)頁中的記錄會按照主鍵值從小到大的順序組成一個單向鏈表，依賴行記錄的
Page Header中next_record屬性實現(xiàn)，其中保存下一條記錄相對于本條記錄的地址偏移量； - 數(shù)據(jù)頁之間組成一個雙向鏈表，依賴數(shù)據(jù)頁的
File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實現(xiàn)，其中保存本頁的上一個和下一個頁的頁號。
查詢操作可以分為以下兩步：
- 定位到記錄所在的頁
- 從所在的頁中定位到具體的記錄
對比沒有索引與有索引時查詢操作的性能差異：
- 沒有索引時每次查詢都是全表掃描；
- 有索引時從 B+ 樹的根節(jié)點出發(fā)，一層一層向下搜索目錄項，由于上層節(jié)點保存的都是下層節(jié)點的最小值，因此可以快速定位到數(shù)據(jù)可能所在的頁。
關(guān)于索引失效的場景總結(jié)以下兩點：
- 索引的本質(zhì)是有序的數(shù)據(jù)結(jié)構(gòu)，因此破壞索引有序性的操作都有可能導(dǎo)致索引失效或部分生效；
- 回表成本較高，因此優(yōu)先使用覆蓋索引，必須要回表時也需要控制回表的記錄數(shù)，從而降低索引失效的風(fēng)險。
以上就是淺談慢SQL優(yōu)化之索引的作用的詳細內(nèi)容，更多關(guān)于慢SQL索引作用的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
Mysql GROUP BY查詢每組某值最大的一條數(shù)據(jù)
這篇文章主要介紹了Mysql GROUP BY查詢每組某值最大的一條數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
由于這幾天在寫mysql存儲過程且發(fā)現(xiàn)程序體積越來越龐大，于是嘗試使用mysql的函數(shù)2011-04-04
這篇文章主要介紹了給Mysql添加遠程訪問權(quán)限的方法的相關(guān)資料,需要的朋友可以參考下2015-03-03
這篇文章主要為大家介紹了MySQL的字段默認null對唯一索引的影響詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-09-09
win10下mysql 5.7.23 winx64安裝配置方法圖文教程
這篇文章主要為大家詳細介紹了win10下mysql 5.7.23 winx64安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-09-09
這篇文章主要介紹了MySQL安全輸入密碼的一些操作,示例基于Linux操作系統(tǒng),需要的朋友可以參考下2015-07-07
CentOS下安裝mysql時忘記設(shè)置root密碼致無法登錄的解決方法
最近在給公司的內(nèi)網(wǎng)開發(fā)用服務(wù)器裝系統(tǒng)，然后裝mysql居然就花了一天，原因是因為本人在CentOS下安裝萬mysql后,無法通過root進入,因為安裝的時候,并沒有設(shè)置root密碼而導(dǎo)致無法登錄，通過查找了資料終于解決了，現(xiàn)在想方法分享給大家，有需要的朋友們可以參考借鑒。2016-11-11
這篇文章主要給大家介紹了關(guān)于MySQL命令行操作時的編碼問題，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04
這篇文章主要介紹了mysql中的limit 1 for update的鎖類型,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
本文主要介紹了MySQL主從同步的幾種實現(xiàn)方式,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2025-02-02