文章目錄

• 生猛干貨
• Pre
• Table Demo
• DB Version
• Case
• • 全值匹配
  • 最左前綴
  • 禁止索引列上做任何操作（計算、函數、（自動or手動）類型轉換）
  • 存儲引擎不能使用索引中范圍條件右邊的列
  • 盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少 select * 語句
  • mysql在使用不等于（！=或者<>）的時候無法使用索引會導致全表掃描
  • is null,is not null 一般情況下也無法使用索引
  • like以通配符開頭（'$abc...'）mysql索引失效會變成全表掃描操作
  • • like 的優化
  • 字符串不加單引號索引失效
  • 少用or或in
  • 范圍查詢優化
• 索引總結
• 搞定MySQL

生猛干貨

帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試

---

Pre

MySQL - Explain深度剖析

Table Demo

CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',PRIMARY KEY (`id`),KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

---

DB Version

mysql> select version(); +------------+ | version() | +------------+ | 5.7.29-log | +------------+ 1 row in set (0.00 sec)mysql>

---

Case

KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

聯合索引

全值匹配

mysql> explain select * from employees where name = 'LiLei'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)mysql>

算算這個ke_len

key_len ： 顯示了mysql在索引里使用的字節數，通過這個值可以算出具體使用了索引中的哪些列。

【字符串】

• char(n)：n字節長度
• varchar(n)：如果是utf-8，則長度 3n + 2 字節，加的2字節用來存儲字符串長度

【數值類型】

• tinyint：1字節
• smallint：2字節
• int：4字節
• bigint：8字節

【時間類型】

• date：3字節
• timestamp：4字節
• datetime：8字節

如果字段允許為 NULL，需要1字節記錄是否為 NULL

索引最大長度是768字節，當字符串過長時，mysql會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引

name varchar(24) —> 3 * 24 + 2 = 74 , 用了聯合索引中的name .

---

mysql> explain select * from employees where name = 'LiLei' and age= 22; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 78 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)

key_len 變成了 78 ？

第二個是int , int 占 4個字節 ， 74 + 4 = 78 ，這個SQL用了聯合索引中的 name + age

---

mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)mysql>

key_len = 74 + 4 + 72 = 140

---

那我們跳過age 呢 ？

mysql> explain select * from employees where name = 'LiLei' and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 33.33 | Using index condition | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql>

用了聯合所以中的 name

---

最左前綴

如果索引了多列，要遵守最左前綴法則 , 指的是查詢從索引的最左前列開始并且不跳過索引中的列。

mysql> explain select * from employees where name = 'LiLei' and age= 22; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 78 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)mysql>

符合最左前綴。

---

不符合 最左前綴

user where : 使用 where 語句來處理結果，并且查詢的列未被索引覆蓋

---

不符合 最左前綴

user where : 使用 where 語句來處理結果，并且查詢的列未被索引覆蓋

---

禁止索引列上做任何操作（計算、函數、（自動or手動）類型轉換）

會導致索引失效而轉向全表掃描

mysql> explain select * from employees where name = 'LiLei'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)mysql> mysql> explain select * from employees where left(name,2) = 'LiLei'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)mysql>

結合索引那個B+Tree , 特征 排好序

left 函數，MYSQL并沒有做優化 ，left(name,2) 在那棵B+Tree上并沒有，肯定不會走索引。

看看函數的操作

加個索引

alter table employees add index idx_hire_time(hire_time) using btree;

查看目前的索引

mysql> show index from employees ; +-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name_age_position | 1 | name | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name_age_position | 2 | age | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name_age_position | 3 | position | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_hire_time | 1 | hire_time | A | 1 | NULL | NULL | | BTREE | | | +-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec)mysql>

在索引列上使用函數

mysql> explain select * from employees where date(hire_time)='2018-09-30'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)mysql>

變幻一下

mysql> explain select * from employees where hire_time>='2018-09-30 00:00:00' and hire_time<='2018-09-30 23:59:59'; +----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_hire_time | idx_hire_time | 4 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql>

好了 ，實驗完畢

移除索引

alter table employees drop index idx_hire_time;

---

存儲引擎不能使用索引中范圍條件右邊的列

比對一下

mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)mysql>

key_len = 140 （74 + 4 + 78） 全部走了 idx_name_age_position (name,age,position)

mysql> explain select * from employees where name = 'LiLei' and age> 22 and position = 'manager'; +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 78 | NULL | 1 | 33.33 | Using index condition | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql>

key_len = 78 （74 + 4 ） 走了 idx_name_age_position (name,age,position) 中的 name 和 age

為什么呢？

腦海中找到那個B+Tree

name 是相同的， 所以第二列 age 肯定是有序的， 而age這里取的是大于， age是大于， 第三列沒辦法保證有序。 如果age是等于，那可以，第三列有序。 上面這個圖不是很合適，不要被誤導了，放上去僅僅是為了讓讀者對B+樹有個輪廓。

---

盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少 select * 語句

mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)mysql> explain select name , age from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | Using index | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)mysql>

看到第二個的 Extra ： Using Index 使用了覆蓋索引

---

mysql在使用不等于（！=或者<>）的時候無法使用索引會導致全表掃描

mysql> mysql> explain select * from employees where name != 'LiLei' ; +----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | 66.67 | Using where | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

---

is null,is not null 一般情況下也無法使用索引

mysql> explain select * from employees where name is null ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec)mysql>

null 值在樹中會放到一起和其他節點搞個雙向指針

---

like以通配符開頭（’$abc…’）mysql索引失效會變成全表掃描操作

mysql> explain select * from employees where name like '%Lei'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

%在前

還是要回想那個索引B+Tree , % 在前面 意味著前面可能還有其他的字符串， 那在樹中的有序性沒法保證啊

mysql> explain select * from employees where name like 'Lei%'; +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql>

繼續回想那個索引B+Tree , % 不在前面 意味著%前面的字符串固定， 那在樹中的就是有序的，當然可以走索引

key_len = 74 ,可以推導出 走了 聯合索引中的name

---

like 的優化

【問題：解決like’%字符串%'索引不被使用的方法？】

A： 使用覆蓋索引，查詢字段必須是建立覆蓋索引字段

mysql> explain select * from employees where name like '%Lei'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)mysql> mysql> mysql> explain select name ,age position from employees where name like '%Lei'; +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | employees | NULL | index | NULL | idx_name_age_position | 140 | NULL | 3 | 33.33 | Using where; Using index | +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)mysql>

不敢說好太多， index 總比 all 好吧 。

B： 如果不能使用覆蓋索引則可能需要借助搜索引擎 ，Es等

---

字符串不加單引號索引失效

---

少用or或in

用它查詢時，mysql不一定使用索引，mysql內部優化器會根據檢索比例、表大小等多個因素整體評 估是否使用索引，詳見范圍查詢優化

---

范圍查詢優化

增加索引

alter table employees add index idx_age(age) using BTREE; mysql> explain select * from employees where age>=1 and age<=2000; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | idx_age | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)mysql>

沒走索引原因：mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。比如這個例子，可能是由于單次數據量查詢過大導致優化器最終選擇不走索引

優化方法： 可以將大的范圍拆分成多個小范圍

mysql> explain select * from employees where age>=1 and age<=10; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql> explain select * from employees where age>=11 and age<=20; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql>

還原索引

alter table employees drop index idx_age ;

索引總結

like KK%相當于=常量，%KK和%KK% 相當于范圍

---

搞定MySQL

總結

以上是生活随笔為你收集整理的MySQL - 践行索引优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。