MySQL - 索引优化案例实操

迷南。 2023-03-04 09:05 19阅读 0赞

### 文章目录 ###

*  生猛干货
 *  DB Version
 *  Table
 *  Case 1 : 联合索引第一个字段用范围不一定会走索引
 *   *  优化一 强制走索引 force index(idx\_name\_age\_position)
     *  优化二 覆盖索引优化
 *  Case 2 : in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
 *  Case 3 : like KK% 一般情况都会走索引
 *   *  特殊例子
 *  搞定MySQL

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]

--------------------

# 生猛干货 #

[带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试][MySQL]

--------------------

# DB Version #

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

默认隔离级别 RR 可重复读

--------------------

# Table #

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=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

重点

1.  主键id PRIMARY KEY (`id`)
2.  联合索引 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

我们向表里写入10万来条数据

![在这里插入图片描述][20200802225700259.png]

--------------------

# Case 1 : 联合索引第一个字段用范围不一定会走索引 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]

当然了，也不是所有的情况都不走索引， MySQL会基于Cost选择一个合适的 ，如果没有走索引，可能mysql内部可能觉得第一个字段就用范围，结果集应该很大，**回表效率不高**，还不如就全表扫描

如果没有走索引想要去优化的话怎么办呢？

--------------------

## 优化一 强制走索引 force index(idx\_name\_age\_position) ##

![在这里插入图片描述][20200802230723458.png]

--------------------

## 优化二 覆盖索引优化 ##

![在这里插入图片描述][2020080223105374.png]

name , age , position 是联合索引，在索引树上，同时索引树上的叶子节点还会关联一个主键id , 如果查询 \* 的话，还要根据id去主键索引上去查找其他字段，需要回表， 如果仅查询二级索引树`idx_name_age_position`上的字段，那就无需回表操作了，效率自然高一些。

--------------------

# Case 2 : in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]

再搞个小表 ，和 employees 一模一样哈，连索引也得一样，插入3条数据 。

CREATE TABLE `employees_2` (
      `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=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
    
    INSERT INTO employees_2(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
    INSERT INTO employees_2(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
    INSERT INTO employees_2(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

![在这里插入图片描述][20200802233552908.png]

**为什么呢？ 就几条数据的话， 结合B+树的结构， MySQL认为从根节点开始向下找，还不如直接从叶子节点从头开始扫描快呢**

--------------------

# Case 3 : like KK% 一般情况都会走索引 #

结合索引树 ， like KK% 可以理解为就是按照 = KK 查询

原因： 索引下推 [MySQL -索引下推 Index Condition Pushdown 初探][MySQL -_ Index Condition Pushdown]

--------------------

## 特殊例子 ##

一般情况 ，但也不绝对。看下面这个例子

假设你这个表 的name字段 是以Artisan开头的，从Artisan1 到Artisan100000

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 3]

再去like的话 ，mysql会基于cost，自主选择 ，比如如下走了全表扫描。

![在这里插入图片描述][20200802234559534.png]

--------------------

# 搞定MySQL #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 4]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]: /images/20230208/cc0a8b73e91a4316bec4912027db6048.png
[MySQL]: https://marketing.csdn.net/poster/3766
[20200802225700259.png]: /images/20230208/a7f4051cef0740159baa10aa4b6be756.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]: /images/20230208/2f203f18a9874640a9d30f146ad47958.png
[20200802230723458.png]: /images/20230208/a2577da4c05e485d94850847a11e8ef4.png
[2020080223105374.png]: /images/20230208/e0ae6846b3d3490f92c169ae095b2c3d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]: /images/20230208/47dd4d52f1324d97a956e209b55f9be9.png
[20200802233552908.png]: /images/20230208/21dd51bf1fc545d3881607277b7d04e9.png
[MySQL -_ Index Condition Pushdown]: https://artisan.blog.csdn.net/article/details/107754192
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 3]: /images/20230208/6ad57ffb86784c3c807c5c18033260a9.png
[20200802234559534.png]: /images/20230208/c3b34a6e84ef497aaac435eb743047c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 4]: /images/20230208/dcc6560aa258402591790f45762a111d.png