索引优化实战一

╰半橙微兮° 2022-09-12 02:53 172阅读 0赞

# 一 防止索引失效口诀 #

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16][]

# 二 表脚本 #

CREATE TABLE staffs(
      id INT PRIMARY KEY AUTO_INCREMENT,
      `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
      `age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
      `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
      `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
    )CHARSET utf8 COMMENT'员工记录表';
    INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
    INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
    INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
    ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)
    mysql> select * from staffs;
    +----+------+-----+---------+---------------------+
    | id | name | age | pos     | add_time            |
    +----+------+-----+---------+---------------------+
    |  1 | z3   |  22 | manager | 2021-09-19 09:15:14 |
    |  2 | July |  23 | dev     | 2021-09-19 09:15:14 |
    |  3 | 2000 |  23 | dev     | 2021-09-19 09:15:14 |
    +----+------+-----+---------+---------------------+
    3 rows in set (0.00 sec)

# 三 全值匹配我最爱和最佳左前缀法则 #

## 1 说明 ##

**a 全值匹配我最爱**

查询的字段按照顺序在索引中都可以匹配到。SQL 中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，给你自动地优化。

**b 最佳左前缀法则**

查询字段与索引字段顺序的不同会导致索引无法充分使用，甚至索引失效。

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

## 2 实战 ##

mysql> show index from staffs;
    +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | staffs |          0 | PRIMARY                 |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
    | staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    | staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    | staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    4 rows in set (0.00 sec)
    
    # 能使用上索引，索引使用了一个字段
    mysql> explain select * from staffs where name = 'July';
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 能使用上索引，索引使用了两个字段
    mysql> explain select * from staffs where name = 'July' and age=15;
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref         | rows | filtered | Extra |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 能使用上索引，索引使用了三个字段
    mysql> explain select * from staffs where name = 'July' and age=15 and pos='dev';
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 调换 age=15 和 pos='dev' 的顺序。SQL 中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，给你自动地优化。
    mysql> explain select * from staffs where name = 'July' and pos='dev' and age=15;
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    
    # 索引失效
    mysql> explain select * from staffs where age=15 and pos='dev';
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    # 索引失效：带头大哥（name）不能死，火车不能没有火车头（name）
    mysql> explain select * from staffs where pos='dev';
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    # 索引部分失效，只用到了 name：中间兄弟（age）不能断————不能跳过索引中的列
    mysql> explain select * from staffs where name = 'July' and pos='dev';
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |    33.33 | Using index condition |
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/643e6b07fc944429a8259719384cf74c.png

发表评论取消回复

表情：

评论列表（有 0 条评论，172人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引优化实战

mark：今天同事提到了索引的优化，自己感觉挺模糊，去网上找到一篇挺不错的文章，记录一下~ 原文：[http://www.mamicode.com/info-detail-2

我会带着你远行/ 2022年10月26日 14:15/ 0 赞/ 155 阅读

相关 MySQL索引优化实战一

DROP TABLE IF EXISTS `employees`; CREATE TABLE `employees`( `id` INT(11

痛定思痛。/ 2022年09月12日 06:54/ 0 赞/ 174 阅读

相关索引优化实战二

一不要在索引列上做任何计算不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。二实战等号左边

短命女/ 2022年09月12日 02:53/ 0 赞/ 158 阅读

相关索引优化实战一

一防止索引失效口诀 ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaX

╰半橙微兮°/ 2022年09月12日 02:53/ 0 赞/ 173 阅读

相关索引优化实战六

一字段的 is not null 和 is null is null 和 is not null 也无法使用索引二实战无法使用索引

迈不过友情╰/ 2022年08月28日 13:52/ 0 赞/ 159 阅读

相关索引优化实战三

一索引列上不能有范围查询存储引擎不能使用索引中范围条件右边的列。二实战全值匹配我最爱 mysql> explain select

川长思鸟来/ 2022年08月28日 13:49/ 0 赞/ 146 阅读

相关索引优化实战五

一点睛 mysql 在使用不等于（!= 或者 <>）的时候，无法使用索引，会导致索引失效，进行全表扫描。二实战使用 = 能用到索引 my

Love The Way You Lie/ 2022年08月28日 13:48/ 0 赞/ 153 阅读

相关索引优化实战七

一 like 的前后模糊匹配 like 以通配符开头（'%abc...'），mysql 索引失效会变成全表扫描的操作。二实战 % 作为 like 的

水深无声/ 2022年08月28日 13:46/ 0 赞/ 171 阅读

相关索引优化实战八

一点睛 1 字符串不加单引号索引失效。 2 少用 or，用它来连接时会使得索引失效。二实战——字符串不加单引号索引失效 mysql> select

亦凉/ 2022年08月28日 13:45/ 0 赞/ 141 阅读

相关 MySQL索引优化实战

在列上进行运算或使用函数会使索引失效，从而进行全表扫描。如下面例子在publish\_time，id列上分别加上索引，publish\_time为datetime类型，id为i

ゝ一世哀愁。/ 2022年01月13日 01:57/ 0 赞/ 249 阅读