mysql的索引问题

mysql的索引问题

我使用的是innoDB，因此仅讨论innoDB的索引，其他引擎不一定适用

一、发现的问题

在学mysql索引的时候，一直记得一个原则是：带头大哥不能死，中间兄弟不能断。但是在排查问题的过程中，发现一个sql对索引的使用并未按此原则, 情况如下，因此决定探讨下原理。

-- user表索引
create index idx_index_1
    on user (level_id, is_del, vip_time);

-- 触发索引的sql
select id from user where is_del = 0 and   vip_time > '2015-01-01';
select count(*) from user where is_del = 0 and   vip_time > '2015-01-01';

-- 未触发索引的sql
select * from user where is_del = 0 and   vip_time > '2015-01-01';
select count(user_name) from user where is_del = 0 and   vip_time > '2015-01-01';

二、情况收集及分析

1.表

-- 建表及索引
create table test_user
(
    id         bigint auto_increment
        primary key,
    name       varchar(32)  default ''                    not null,
    avatar     varchar(200) default ''                    not null,
    cell_phone varchar(32)  default ''                    not null,
    city_id    bigint       default 0                     not null,
    sex        tinyint                                    null,
    is_del     tinyint                                    null comment '1-删除 0-未删除',
    created    datetime     default '2022-01-01 00:00:00' not null,
    updated    datetime     default CURRENT_TIMESTAMP     not null
)
    comment '测试索引' charset = utf8mb4;


create index idx_name_city_id_sex
    on test_user (name, city_id, sex);

-- 存储过程，批量插入数据
create
    definer = root@`%` procedure insert_info()
begin
    declare i int;
    set i = 1;
    while i < 10000 do

        insert into test_user(`name`,avatar,cell_phone,city_id, sex, is_del, created) value (concat('张', + cast(i as char(10))),
                                                                                           'https://pics2.baidu.com/feed/8718367adab44aed1a151a265319ed08a08bfbac.jpeg?token=d670ad0df6fe65b18371505e89a15036',
                                                                                          concat('1111111111', + cast(i as char(10))), i % 4, i % 2, 0, now());

        set i = i + 1;
        end while;
end;

2. 各sql情况

-- 带头大哥不能死，中间兄弟不能断
// 大哥在，触发
select * from test_user where name = '张3';
// 大哥三弟在，触发
select * from test_user where name = '张3' and sex = 1;
// 二哥三弟在，未触发
select * from test_user where city_id = 1 and sex = 1; 
// 大哥在，三弟用来排序，仅大哥触发了索引，但是会有using filesort
/*
-> Sort: test_user.sex  (cost=0.35 rows=1) (actual time=1.000..1.000 rows=1 loops=1)
    -> Index lookup on test_user using idx_name_city_id_sex (name='张3')  (actual time=0.034..0.036 rows=1 loops=1)
*/
select * from test_user where name = '张3' order by sex；

-- count(*)的情况
-- 这里不讨论count(1)或count(列）的情况，因为count(1)和count(*)基本无效率问题，而count(列）会忽略null值

// 大哥二哥三弟都在（只要大哥在就行），触发 using index
select count(*) from test_user where name='张3' and city_id = 1 and sex = 1;
// 二哥三弟在，触发 using where；using index
select count(*) from test_user where city_id = 1 and sex = 1;
// 三弟和其他非索引条件，未触发
select count(*) from test_user where sex = 1 and cell_phone = '11111111113';
// 大哥和其他非索引条件，触发索引 using where
select count(*) from test_user where name = '张3' and cell_phone = '11111111113';
// 触发了索引idx_name_city_id_sex using index
select count(*) from test_user;

3. 分析

带头大哥不能死，中间兄弟不能断。这个原则只是最佳左前缀法则，并不一定适用于所有sql，mysql会自行选用更优的方式来查询。

如前面的select * 的，二次索引查到数据后，还有做回表的操作，如果mysql认为使用索引后效率不如全表查询，就会采用全表查询。

再来说说count(*)的问题，多列索引是个完整的BTree索引，有最左原则，在搜索时，会先从根节点出发。而city_id = 1 and sex = 1这条语句，mysql判定使用索引比全表查询要快，因此会默认使用索引查询，但是extra为using where；using index，即有回表的操作。而在遵循最左原则的情况下（它上面那条），就仅有using index，不会有回表的操作。但是查询条件中一个为组合索引的列（非首列），一个为非索引列时，mysql判定通过索引去查，远远不如全表查询，因此会采用全表查询的方式。

在使用索引的过程中，也需要考虑下，extra中是直接触发索引，还是还触发了回表，在sql优化时需要着重关注。以下是extra中的值含义：

值	含义
using filesort	在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序
using temporary	排序未走索引、使用union、子查询连接查询等原因，创建了一个内部临时表
using index	使用覆盖索引
useing where	将在存储引擎检索行后再进行过滤，即需要mysql server再进行一次筛选
using index condition	使用二级索引不够，还需要回表，但是在回表之前会过滤此二级索引能过滤的条件