索引优化实战二

短命女 2022-09-12 02:53 205阅读 0赞

#  一  不要在索引列上做任何计算 #

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

# 二 实战 #

# 等号左边无计算，索引生效
    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 left(name,4) = 'July';
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | 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 |   100.00 | Using where |
    +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    
    # 字符串加单引号，能查出结果
    mysql> select * from staffs where name='2000';
    +----+------+-----+-----+---------------------+
    | id | name | age | pos | add_time            |
    +----+------+-----+-----+---------------------+
    |  3 | 2000 |  23 | dev | 2021-09-19 09:15:14 |
    +----+------+-----+-----+---------------------+
    1 row in set (0.00 sec)
    
    # 字符串不加单引号，也能查出结果
    mysql> select * from staffs where name=2000;
    +----+------+-----+-----+---------------------+
    | id | name | age | pos | add_time            |
    +----+------+-----+-----+---------------------+
    |  3 | 2000 |  23 | dev | 2021-09-19 09:15:14 |
    +----+------+-----+-----+---------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # 字符串加单引号，索引生效
    mysql> explain select * from staffs where name='2000';
    +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
    | 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)
    
    # 字符串不加单引号，索引失效：字符串不加单引号，会在 name 列上做一次转换，也就是会自动进行一次类型转换
    mysql> explain select * from staffs where name=2000;
    +----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
    | id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    +----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
    1 row in set, 3 warnings (0.00 sec)

# 三 结论 #

等号左边无计算。