[Mysql] Не хочет использовать индекс
От: rosencrantz  
Дата: 09.07.21 17:13
Оценка:
База Mysql 8.0.21.

Полная модельная БД — в конце поста. Есть вот такой запрос:

-- Q1:
explain analyze select *
from Enrollments E
left join Users U on U.orgId = E.orgId and U.id = E.userId
where E.orgId = 1
order by U.name;


-> Sort: U.`name`  (actual time=0.062..0.062 rows=2 loops=1)
    -> Stream results  (actual time=0.016..0.021 rows=2 loops=1)
        -> Nested loop left join  (cost=1.15 rows=2) (actual time=0.014..0.018 rows=2 loops=1)
            -> Index lookup on E using EnrollmentUser (orgId=1)  (cost=0.45 rows=2) (actual time=0.009..0.011 rows=2 loops=1)
            -> Single-row index lookup on U using PRIMARY (orgId=1, id=E.userId)  (cost=0.30 rows=1) (actual time=0.003..0.003 rows=1 loops=2)


Работает медленно (Дисклеймер: Всё, что написано в этом вопросе — это выжимка из настоящей проблемы в настоящей базе. Настоящая проблема обнаружена перформанс тестами — 10 параллельных юзеров делают по 100 запросов. Медленно это ~2000ms, быстро это ~200ms).

Если в where добавить "and U.orgId = E.orgId", работает быстро:

-- Q2:
explain analyze select *
from Enrollments E
left join Users U on U.orgId = E.orgId and U.id = E.userId
where E.orgId = 1 and U.orgId = E.orgId
order by U.name;


-> Nested loop inner join  (cost=1.15 rows=2) (actual time=0.020..0.026 rows=2 loops=1)
    -> Filter: (U.id is not null)  (cost=0.45 rows=2) (actual time=0.014..0.015 rows=2 loops=1)
        -> Index lookup on U using Name (orgId=1)  (cost=0.45 rows=2) (actual time=0.013..0.014 rows=2 loops=1)
    -> Index lookup on E using EnrollmentUser (orgId=1, userId=U.id)  (cost=0.30 rows=1) (actual time=0.002..0.004 rows=1 loops=2)


Запрос Q1 строится ORM и поменять его на Q2 я не могу. Вот эта подсказка "U.orgId = E.orgId" кажется очень глупой, но без неё, похоже, Mysql не понимает, что orgId у нас ограничен единственным значением на весь запрос — и для Users, и для Enrollments, и соответственно можно очень здорово воспользоваться индексом Name. Как бы так сделать Mysql более проницательным?

  Полная модельная БД, на которой можно воспроизвести поведение
create table Users(
    orgId int not null,
    id int not null,
    name varchar(32),
    primary key (orgId, id)
);

insert into Users(orgId, id, name) values
    (1, 1, 'user 1 1'),
    (1, 2, 'user 1 2'),
    (2, 1, 'user 2 1'),
    (2, 2, 'user 2 2'),
    (3, 1, 'user 3 1');

create index Name on Users(orgId, name);

create table Enrollments(
    orgId int not null,
    id int not null,
    userId int not null,
    primary key (orgId, id),
    foreign key EnrollmentUser (orgId, userId) references Users (orgId, id)
);

insert into Enrollments(orgId, id, userId) values
    (1, 1, 1),
    (1, 2, 2),
    (2, 1, 1),
    (2, 2, 2),
    (3, 1, 1);
Отредактировано 09.07.2021 17:20 rosencrantz . Предыдущая версия .
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.