目? 錄

一、最左前綴原則

1.完全使用索引?

2.部分使用索引

3.不使用索引

4.效率折損

（1）使用范圍查找

（2）索引斷開

?二、索引失效場景

1.?索引列參與運算

2.索引列模糊查詢以“%”開始

3.索引列是字符串類型，查詢省略單引號

4.查詢條件包含“or”，其中有未添加索引的字段?

5.查詢符合條件的記錄在表中占比較大

三、指定索引

?四、覆蓋索引

1.說明

2.實例

五、前綴索引

六、單列索引與復合索引的選擇

七、創建索引的原則

---

一、最左前綴原則

# 初始化
drop table if exists t_customer;
create table t_customer(id int primary key auto_increment,name varchar(10),age int,gender char(2),email varchar(20)
);
insert into t_customer(name, age, gender, email) values('劉林', 21, '女', '2238953721@999.com'),('王剛', 23, '男', '1477123899@999.com'),('趙輝', 19, '男', '3287654466@999.com'),('何鈺', 20, '女', '7981112520@999.com'),('周洋', 27, '男', '6287553412@999.com');create index index_tcustomer_nag on t_customer(name, age, gender);show index from t_customer;

? ? ? ? ?若要索引生效，必須遵循最左前綴原則。即上述為 t_customer 創建了name，age，gender 聯合索引，添加順序如此。則在進行查詢時，如果 where 條件中沒有 name 字段參與，則復合索引失效。

? ? ? ??條件中必須要有最左側字段參與，這樣復合索引才會生效。最具有唯一性的字段應該放在最左側。

---

1.完全使用索引?

explain select * from t_customer where name = '何鈺' and age = 20 and gender = '女';

---

2.部分使用索引

explain select * from t_customer where name = '何鈺' and age = 20;explain select * from t_customer where name = '何鈺';explain select * from t_customer where name = '何鈺' and gender = '女';

---

3.不使用索引

explain select * from t_customer where age = 20 and gender = '女';

---

4.效率折損

（1）使用范圍查找

? ? ? ? 使用了范圍查找，若范圍條件不添加等號，則范圍條件右側列不會使用索引。

? ? ? ? 如下實例，從【key_len】字段可以看出：第一條【gender】字段沒有使用索引。而第二條完全使用了索引。

explain select * from t_customer where name = '何鈺' and age > 20 and gender = '女';explain select * from t_customer where name = '何鈺' and age >= 20 and gender = '女';

---

（2）索引斷開

? ? ? ? 條件中使用了索引最左側字段，但是沒有使用索引中的全部字段且間斷使用，會使間斷的字段不使用索引。

? ? ? ? 如下方第一條，條件中沒有使用【age】字段，而導致間斷，所以【gender】字段沒有使用索引。而第二條完全使用了索引。

---

?二、索引失效場景

# 初始化
drop table if exists t_emp;
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,age char(2)
);
insert into t_emp(name, sal, age) values('劉強', 6000, 37),('川建國', 2000, 53),('郭珊珊', 9000, 27);create index index_temp_name on t_emp(name);
create index index_temp_sal on t_emp(sal);
create index index_temp_age on t_emp(age);show index from t_emp;

---

1.?索引列參與運算

explain select * from t_emp where sal * 10 > 50000;

---

2.索引列模糊查詢以“%”開始

explain select * from t_emp where name like '%珊珊';

---

3.索引列是字符串類型，查詢省略單引號

explain select * from t_mep where name = 郭珊珊;

---

4.查詢條件包含“or”，其中有未添加索引的字段?

-- 查看執行計劃
explain select * from t_emp where age = '53' or sal = 2000;-- 刪除sal索引
alter table t_emp drop index index_temp_sal;-- 查看執行計劃
explain select * from t_emp where age = '53' or sal = 2000;

---

5.查詢符合條件的記錄在表中占比較大

# 新插入幾條數據
insert into t_emp(name, sal, age) values('王琳', 1800, 20),('張昂', 3000, 23),('李冬雪', 4000, 33),('王子安', 6500, 47),('陸佳佳', 7000, 28),('王明', 1000, 26),('邱鈺紅', 2500, 31),('黃燦燦', 10000, 38);# 創建sal索引
create index index_temp_sal on t_emp(sal);# 查詢計劃
explain select * from t_emp where sal > 1500;explain select * from t_emp where sal > 8000;

# 執行計劃
explain select * from t_emp where age is null;# 將age字段全部更新為null
update t_emp set age = null;# 執行計劃
explain select * from t_emp where age is null;

# 執行計劃
explain select * from t_emp where age is not null;# 將age字段全部更新為not null
update t_emp set age = 23;# 執行計劃
explain select * from t_emp where age is not null;

---

三、指定索引

1. 當一個字段上既有單列索引，也有復合索引，可以通過下述 SQL 語句指定索引：
   1. use index(索引名)：建議使用該索引。MySQL 會根據實際效率考慮是否使用；
   2. ignore index(索引名)：忽略該索引；
   3. force?index(索引名)：強制使用該索引。

# 查看索引
show index from t_emp;# 為 t_emp 添加一個復合索引
create index index_temp_nsa on t_emp(name, sal, age);# 查看索引
show index from t_emp;# 執行計劃
explain select * from t_emp where name = '郭珊珊';
explain select * from t_emp use index(index_temp_nsa) where name = '郭珊珊';
explain select * from t_emp ignore index(index_temp_name) where name = '郭珊珊';
explain select * from t_emp force index(index_temp_nsa) where name = '郭珊珊';

---

?四、覆蓋索引

1.說明

? ? ? ? select 后的字段，盡可能是索引所覆蓋的字段，如此可以避免“回表”。

? ? ? ? 盡量避免使用【select * 】，因為其容易導致“回表”操作。

---

2.實例

? ? ? ? t_user 表字段有：?id，name，password，realname，birth，email。表中數據有600萬條，請針對下述 SQL 給出優化方案。

select id, name, realname from t_user where name = '郭珊珊';

? ? ? ? ?建議給 name 和 realname 兩個字段添加聯合索引，減少回表操作，大大提升效率。

---

五、前綴索引

? ? ? ? 若一個字段類型是 varchar 或 text，直接對其創建索引會使索引體積較大。

? ? ? ? 那么，可以將字符串前幾個字符截取下來當作索引，這種索引被稱為前綴索引。

# 為t_emp表的name字段前兩個字符創建索引
create index index_temp_subname on t_emp(name(2));# 截取字符數計算公式，其值越接近于1，越具有唯一性
select count(distinct substring(字段名, 1, 前幾個字符)) / count(*) from 表名;select count(distinct substring(name, 1, 2)) / count(*) from t_emp;

---

六、單列索引與復合索引的選擇

? ? ? ? 當查詢語句有多個條件，建議將這些列創建為復合索引，因為創建單列索引容易造成“回表”操作。

---

七、創建索引的原則

1. 表中數據量龐大，通常超過百萬；
2. 經常出現在 where、order by、group by 后邊的字段建議添加索引；
3. 創建索引的字段具有較強的唯一性；
4. 字段存儲文本，內容較大，一定要創建前綴索引；
5. 盡量使用復合索引，避免回表查詢；
6. 若一個字段中的數據不會為 null，建議建表時添加 not null 約束。如此優化器知道使用哪個索引列更有效；
7. 不要創建太多的索引，因為對數據進行增刪改時，索引需要重新排序；
8. 如果較少查詢，頻繁增刪改，不建議添加索引。