Mysql 索引优化（一）

刺骨的言语ヽ痛彻心扉 2022-11-03 15:28 104阅读 0赞

**目录**

一.创建表

二.插入数据

三.创建索引

四.索引总结

### 一.创建表 ###

CREATE TABLE `staffs` (
      `id` int(11) NOT NULL,
      `name` varchar(10) DEFAULT NULL COMMENT '姓名',
      `age` int(11) NOT NULL COMMENT '年龄',
      `pos` varchar(20) DEFAULT NULL COMMENT '职位',
      `add_time` timestamp NULL DEFAULT NULL COMMENT '入职时间',
      PRIMARY KEY (`id`),
      KEY `idx_n_a_p` (`name`,`age`,`pos`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工记录表';

### 二.插入数据 ###

INSERT INTO `explain`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('1', 'z3', '22', 'manager', '2021-02-28 22:08:14');
    INSERT INTO `explain`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('2', 'July', '23', 'dev', '2021-02-28 22:08:41');
    INSERT INTO `explain`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('3', '2000', '23', 'dev', '2021-02-28 22:08:56');

### 三.创建索引 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70][]

## 四.索引总结 ##

EXPLAIN SELECT * FROM staffs WHERE `name`='July' AND age ='23' AND pos='dev';

### 一 ###

**EXPLAIN SELECT \* FROM staffs WHERE \`name\`='July'**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1][]

**EXPLAIN SELECT \* FROM staffs WHERE \`name\`='July' AND age ='23'**

![20210228224116178.png][]

**EXPLAIN SELECT \* FROM staffs WHERE \`name\`='July' AND age ='23' AND pos='dev';**

![20210228224232652.png][]

三个SQL的精度越来越高. 花费的代价也是递增的 如图**key\_len**长度递增. 在精度一致的情况下**key\_len**越短越好.

结论：

**全值匹配我最爱**（最好查询字段和索引字段全部匹配）

### 二 ###

**EXPLAIN SELECT \* FROM staffs WHERE   age ='23' AND pos='dev';**

![20210228224644346.png][]

**EXPLAIN SELECT \* FROM staffs WHERE  pos='dev';**

![20210228224847581.png][]

**EXPLAIN SELECT \* FROM staffs WHERE    pos='dev' AND age ='23' ;**

** **![20210228224927700.png][]

索引失效.全表扫描.创建的索引没用使用到.

**EXPLAIN SELECT \* FROM staffs WHERE  name='July' AND pos ='dev' ;**

![2021022823013636.png][]

这个key\_len的长度和跟之前name的一致。所以索引只用到了一部分，到name后面没有用到。

结论：

**最佳左前缀法则——如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列.(中间字段不能跳过）**

### **三** ###

**EXPLAIN SELECT \* FROM staffs WHERE  LEFT(name,4)='July';**

![2021022823112966.png][]

总结：

**不能在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描**

### **四** ###

**SELECT \* FROM staffs WHERE name ='July' AND age > 20 AND pos = 'dev'  **

![20210228231704580.png][]

**EXPLAIN  SELECT \* FROM staffs WHERE name ='July' AND age > 20 AND pos = 'dev'  **

![20210228231804154.png][]

总结：

**存储引擎不能使用索引中范围条件右边的列（范围之后全失效，上篇Mysql EXPLAIN优化有介绍)**

### **五** ###

**EXPLAIN  SELECT \* FROM staffs WHERE name ='July' AND age = 23 AND pos = 'dev'  **

![20210228232152999.png][]

**EXPLAIN  SELECT name,age,pos FROM staffs WHERE name ='July' AND age = 23 AND pos = 'dev'  **

![2021022823222978.png][]

**EXPLAIN  SELECT name,age,pos FROM staffs WHERE name ='July' AND age >= 23 AND pos = 'dev'**

![2021022823234943.png][]

总结：

**尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select \***

### **六** ###

**EXPLAIN  SELECT \* FROM staffs WHERE name ='July' **

![20210228232814941.png][]

**EXPLAIN SELECT \* FROM staffs WHERE name !='July' **

![20210228232757277.png][]

**EXPLAIN SELECT \* FROM staffs WHERE name <> 'July' **

![20210228232859365.png][]

**PS: 不要因为创建了索引一定要用上，根据业务需求，若无法避免索引失效，且不影响性能情况下可以使用。能实现需求的情况下再进行优化。**

总结：

**mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描**

### **七** ###

![20210228233259499.png][]

**PS:若name 字段没设置不为空，则走索引。我上面创建表sql有个小坑。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2][]

![20210228233440458.png][]

**EXPLAIN SELECT \* FROM staffs WHERE name is not null**

![2021022823351191.png][]

总结：

**is null ,is not null也无法使用索引**

### **八** ###

**EXPLAIN  SELECT \* FROM staffs WHERE name LIKE '%J%'**

![20210228233648793.png][]

**EXPLAIN SELECT \* FROM staffs WHERE name LIKE '%J'**

![20210228233716359.png][]

**EXPLAIN SELECT \* FROM staffs WHERE name LIKE 'J%'**

![20210228233746640.png][]

**%加右边走索引，左边不走 业务需求两边必须都加%XX%用覆盖索引来实现 具体实现过程 后期写篇博客来分析解决like "%字符串%'时索引不被使用的方法。**

总结：

**like以通配符开头("%abc...")mysql索引失效会变成全表扫描的操作**

### **九** ###

**EXPLAIN SELECT \* FROM staffs WHERE name = 3;**

** **![20210228234852278.png][]

**EXPLAIN SELECT \* FROM staffs WHERE name = '3';**

![20210228234920904.png][]

总结：

**字符串不加单引号索引失效**

### **十** ###

**EXPLAIN SELECT \* FROM staffs WHERE name = '3' or name ='July';**

![20210228235312636.png][]

总结：

**少用or,用它来连接时会索引失效**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 3][]

![20210301104222118.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70]: /images/20221023/4f12106f84994aecbcc391371f8dee3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1]: /images/20221023/e4a25e0a3bb6484ebcdb6ad7691d638b.png
[20210228224116178.png]: /images/20221023/11d748c965314f3aa5a86ec04fa5dab3.png
[20210228224232652.png]: /images/20221023/5f97e64f73e0476da7eda01e9b9bc770.png
[20210228224644346.png]: /images/20221023/d16b9fbbf250468d9a20cce72c04bc4c.png
[20210228224847581.png]: /images/20221023/e5afc64022e1452bbef9f4c7b1d1c3da.png
[20210228224927700.png]: /images/20221023/0651a801b7ab42beb753e127742fa6e9.png
[2021022823013636.png]: /images/20221023/cbcfef02893c44d1a89d97c42b776a62.png
[2021022823112966.png]: /images/20221023/06d950cdfd2f4cfcb0f21412166eabf4.png
[20210228231704580.png]: /images/20221023/0d055b31de37489ca1c95d12fe60a4d6.png
[20210228231804154.png]: /images/20221023/ef58228ea7684b19825460758b5e2cb7.png
[20210228232152999.png]: /images/20221023/21bc386167e647aea98cd3078345596f.png
[2021022823222978.png]: /images/20221023/e78062c7aca14a2e9e88402da94433ce.png
[2021022823234943.png]: /images/20221023/ca54e67e7f564b25b3ac73563a957de4.png
[20210228232814941.png]: /images/20221023/2920e73adb8343e4b45a9a6504a13e0a.png
[20210228232757277.png]: /images/20221023/5b8b59b279624501b70e8bb612df516b.png
[20210228232859365.png]: /images/20221023/ecd2cbfbcdd54447aa04a186ba432c11.png
[20210228233259499.png]: /images/20221023/ad413ff34c5644b8a0aa9328c47e5e56.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2]: /images/20221023/d4c6ba31a8294b0c9c8cdf57b4528deb.png
[20210228233440458.png]: /images/20221023/857307f102bc423b884fe74a164d77e6.png
[2021022823351191.png]: /images/20221023/a5193c294ae14373a24c9e21a5aff43b.png
[20210228233648793.png]: /images/20221023/ae18b241eeec487cb986524672a4f394.png
[20210228233716359.png]: /images/20221023/bb3f77dfad5243a28749202578513a12.png
[20210228233746640.png]: /images/20221023/9160dacc624a4e438ffa57f2a7a5cef1.png
[20210228234852278.png]: /images/20221023/82c369633c7c482bb9668bf030ca5b48.png
[20210228234920904.png]: /images/20221023/abf4fca0f7b6486f88b4815d40f13de9.png
[20210228235312636.png]: /images/20221023/27e3aab3f8c042eabf94daa49a1468c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 3]: /images/20221023/cad212405bc44ceba8d4b3684f563db3.png
[20210301104222118.png]: /images/20221023/6cd54bb6387f419ab5e34056c3db480d.png