Оптимизация явно не является коньком MySQL сервера. Цель отправной статьи объяснить разработчикам, какой-никакие плотно не сидятся с базами данных и иногда не разумеют, по какой-никакой первопричине запрос, какой-никакой хорошо отрабатывает в другых СУБД, в MySQL богопротивно остаст, как оптимизируется конструкция between в MySQL.
MySQL применяет rule based оптимизатор. Зачатки cost based оптимизации в нем конечно ищутся, хотя не в соответствующей мерке, в какой их желалось бы видеть. По исходной первопричине достаточно часто мощь получаемых после применения фильтров множеств вычисляются ложно. Это приводит к оплошностям оптимизатора и выбору неверного плана выполнения. При ежели полученные between оптимизации невозможно преобразить приоткрытым указанием: индексов для исполнения запроса и распорядка соединения таблиц.
Для начала оглядим баг: Чтобы изучить суть бага сформируем опытный набор этих в габарите 125 миллионов записей.
drop table if exists pivot;
drop table if exists big_table;
drop table if exists attributes;

create table pivot
(
row_number int(4) unsigned auto_increment,
primary key pk_pivot (row_number)
)
engine = innodb;

insert into pivot(row_number)
select null
from information_schema.global_status g1, information_schema.global_status g2
limit 500;

create table attributes(attr_id int(10) unsigned auto_increment,
attribute_name varchar(32) not null,
start_date datetime,
end_date datetime,
constraint pk_attributes primary key(attr_id)
)
engine = innodb;

create table big_table(btbl_id int(10) unsigned auto_increment,
attr_attr_id int(10) unsigned,
record_date datetime,
record_value varchar(128) not null,
constraint pk_big_table primary key(btbl_id)
)
engine = innodb;

insert into attributes(attribute_name, start_date, end_date)
select row_number, str_to_date("20000101", "%Y%m%d", str_to_date("20000201", "%Y%m%d" from pivot;

insert into big_table(attr_attr_id, record_date, record_value)
select p1.row_number,
date_add(str_to_date("20000101", "%Y%m%d", interval p2.row_number + p3.row_number day),
p2.row_number * тыщи + p3.row_number
from pivot p1,
pivot p2,
pivot p3;

create index idx_big_table_attr_date on big_table(attr_attr_id, record_date);
Сетка attributes в самом деле представляется справочником, для
big_table а также наиболее подключает 2 колонки ограничивающие интервал посунут одним месяцем.
Для другого attr_id в большой таблице держится 250 000 записей. Отведим прознать в какой мере записей держится в
big_table с учетом подат данных таблицей attributes для аттрибута один.
select attr_attr_id,
max(record_date),
min(record_date),
max(record_value),
count(1)
from big_table
where attr_attr_id = 1 and record_date between str_to_date("20000101", "%Y%m%d" and str_to_date("20000201", "%Y%m%d"
group by attr_attr_id;
Покупаем распорядка 500 записей (время выполнения запроса ничтожно малюсенько и скопило 00.050 моменты). Мудро пропустить, что поскольку отправные распределены одинаково для совершенно абсолютно всех значений аттрибутов, то при соединении с таблицей
attributes заместо приоткрытого указания бинд шатких время запроса должно повысится незначительно, и скопить не более 20 5 сек.. Что литера проверим:
select b.attr_attr_id,
max(b.record_date),
min(b.record_date),
max(b.record_value),
count(1)
from attributes a
join
big_table b
on b.attr_attr_id = a.attr_id and b.record_date between a.start_date and a.end_date
group by b.attr_attr_id;
Время выполнения: более 15 мин. (на данной балле я побил выполнение запроса). По какой причине же так получется? Все как оказалось MySQL не удерживает динамическое ранжирование о нежели нам и извещает бага
изготовленная еще в далеком 2004 году. Посмотрим сан исполнения:
Сан очевидно обнаруживает, что идет цельное определение таблицы в 125 миллионов записей. Изумительное мнение. Исправить ситуацию не вызволяет и
straight_join для подмены порядка джойна буква force index для открытого указания применять индекс. Все дело в том что в оптимальном случае мы покупаем чин вида:
Какой-никакой сообщает нам о этом
сетка big_table станет сканироваться по нужному индексу, однако индекс будет задействован неполностью, т. буква. из него будет использоваться только начальная колонка
. В каких-то, дегенерированных альтернативах, мы сможем добиться нужного нам плана и цельного применения индекса, впрочем, по причине непостоянства оптимизатора и неосуществимости использования отправного решения (не буду приводить здесь его код, оно не сидится в 90% ситуации) во всех 100% ситуации, нужен иной подход.
Данный поход поделает хорошее предложение нам сам MySQL. Станем явно направлять
bind шаткие. Конечно для ряда задач это не всегда отменно, так как приключается, что целое распознавание пройдет быстрее чем индексное, но явно не в нашем случае когда необходимо выбить пятьсот записей из 250000. Для решения задачи нам спрашивается произведение следующей процедуры.
attr_id, start_date, end_date
(10) unsigned,
max_record_date datetime,
min_record_date datetime,
(128),
v_attr_id, v_start_date, v_end_date;
temp_big_table_results(attr_attr_id,
&nbs p; &n bsp; max_record_date,
&nbs p; &n bsp; min_record_date,
&nbs p; &n bsp; max_record_value,
attr_attr_id,
(record_date) max_record_date,
(record_date) min_record_date,
(record_value) max_record_value,
attr_attr_id,
max_record_date,
min_record_date,
max_record_value,
* This source code was highlighted with .
Т.буква. мы в начале показываем курсор по пятистам записям таблицы аттрибутов, и для другой строки данной таблицы поделаем запрос из
big_table . Поглядим на результат:
* This source code was highlighted with .
Время выполнения: 005.017
на мой взгляд результат гораздо лучше. Не примерно, но но но зато трудится.
Теперь же же вероятно оглядеть рекуррентный пример, когда поиск лится не в таблице «транзакций», а против в таблице фактов.
Теперь посмотрим на творение и т.буква.
Сначала досоздадим пробный набор данных из двадцать пять миллионов строк.
drop table if exists big_range_table;

create table big_range_table(rtbl_id int(10) unsigned auto_increment,
value_from int(10) unsigned,
value_to int(10) unsigned,
range_value varchar(128),
constraint pk_big_range_table primary key(rtbl_id)
)
engine = innodb;

insert into big_range_table(value_from, value_to, range_value)
select @row_number := @row_number + 1, @row_number + 1, p1.row_number + p2.row_number + p3.row_number
from (select *
from pivot
where row_number <= 100) p1,
pivot p2,
pivot p3,
(select @row_number := 0) counter;

create index idx_big_range_table_from_to
on big_range_table(value_from, value_to);

create index idx_big_range_table_from
on big_range_table(value_from);
И с ходу отведим исполнить запрос, какой удачно оптимизируется абсолютно абсолютно всеми СУБД за огромным исключением MySQL:
select range_value
from big_range_table
where 10000000.5-ого >= value_from and 10000000.пятого < value_to;
Время выполнения: 00:22.412
. Вообще не варианты с учетом данного, что мы то видим, что сходственной запрос должен вернуть одну прекрасную строку. И чем выше качество лучшей вами шаткой — тем вот вот больше записей будет просканировано, время работы запроса растёт экпоненциально.
Время выполнения: 000.350
. Отлично. Хотя отправное заключение владеет ряд несовершенства, в частности, вы не можете лить join с другыми таблицами. Т.е. данный запрос может быть очень запросто. Для таланты джойнов прибегнемся стандартным решением RTree индексами (если конечно ваш справочник не нуждается в транзакциях или даже вы обеспечиваете его целостность триггерами, так как этот тип индексов по былому работает лишь для MyISAM). Для тех кто не в курсе, что такое геометральные объекты в MySQL приведу иллюстрацию этого, что как обычно поделают в таких случаях:
Отрекомендуем плоскость. На оси абсцисс встанут разыскивается наши резона для поиска. Ордината ашей точки схожа нулю, так как в отправном буквальном случае, для упрощения, станем подыскивать
between только для 1-го ньюанса. Если критериев больше необходимо употреблять многомерные объекты. В добром качестве границ прямоугольника a и b как обычно применяют 1 и -1 соответственно. Поэтому смысла из нашего справочника станут покрывать луч выходящей из 0. Так же они будут обрезаны большим большим множеством заштрихованных прямоугольников. Если искра принадлежит отправному прямоугольнику, то значит идентификатор исходного прямоугольника дает нам желаемый идентификатор записи в таблице. Кинем преображение:
alter table big_range_table engine = myisam, add column polygon_value polygon not null;

update big_range_table
set polygon_value =
geomfromwkb(polygon(linestring( /* подвигаемся против часовой стрелке, важничаем 4 точки и обратно к начальной прикрываем полигон */
point(value_from, -1), /* нижняя левая */
point(value_to, -1), /* нижняя изнаночная */
point(value_to, 1), /* верхняя левая */
point(value_from, 1), /* верхняя левая */
point(value_from, -1) /* к первоначальной точке */
)));
Для тех, кто рискнул делать исходную операцию разом со наименьшей — мониторим время заканчивания
update .
select (select @first_value := variable_value from information_schema.global_status where variable_name = 'HANDLER_UPDATE') updated,
sleep(10) lets_sleep,
(select @second_value := variable_value from information_schema.global_status where variable_name = 'HANDLER_UPDATE') updated_in_a_ten_second,
@second_value - @first_value myisam_updated_records,
25000000 / (@second_value - @first_value) / 6 estimate_for_update_in_minutes,
(select 25000000 / (@second_value - @first_value) / 6 - time / шестьдесят from information_schema.processlist where info like 'update big_range_table%') estimate_time_left_in_minutes;
Если вы дошли ранее шага — советую его не делать, так как при неверных функциях БД творение данного индекса сможет затянуться на неделю.
Что буква теперь же же возможно подискутировать скорость работы. Для начала поглядим на быстрота выполнения исходного варианта запроса и «геометрального» немного увеличивая свойство
limit от 10 до 100.
select *
from (select row_number * 5000 row_number from pivot order by row_number limit 10) p, big_range_table
where mbrcontains(polygon_value, pointfromwkb(point(row_number, 0))) and row_number < value_to;

select *
from (select row_number * 5000 row_number from pivot order by row_number limit 10) p, big_range_table
where value_from <= row_number and row_number < value_to;
По изнаночную избежаю — время, снизу — значение limit. Как от сюда следует из рисунка время between (васильковый) вырастает показательно в зависимости от этого в начале мы или же ближе наконец-то, так как для любого следующего значения бинд неустойчивой нам нужно проверять все больше и больше строчек. «Геометральное» же решение (алый) на таких маленьких значениях элементарно константа.
Отведим поспорить order by limit 1 и geometry для больших значений. Для этого прибегнемся упражнениями, для творения равновесных мерило и проведем рандомную сборку.
mbrcontains(polygon_value, pointfromwkb(point(v_random, 0)))
* This source code was highlighted with .
На графике мы видим итог последовательного роста количества запусков процедур от 10000 до 90000 и кол-во сек. затраченно на соотвествующие операции. Как следовательно «геометральное» решение (алое) в 2 раза быстрее нежели решения с использованием
order by limit 1 (милое) а помимо этого исходное решение можно использовать в стандартном SQL.
Разъяснение данной темы, я провел особенно по причине того, что на небольших габаритах данных исходные косяки не видны, впрочем когда БД вырастает, и на ней задумывает быть более 10 пользователей, деградация производительности поделается элементарно ужасной, а указанные типы запросов можно встретить абсолютно в любой смазочной БД.
Удачных вам оптимизаций. Если данная статья будет интересна в следующий раз произнесу про баги, какие не только не мешают быть, но и напротив — увеличивают производительность запросов при неизменном их использовании.
Литера.Ы.
MySQL version 5.5.11
все условия проводились после перезагрузки MySQL для исключения попадания в КЭШ результатов.
функции MySQL далеко не стандартные, но величина буферов innodb не превышает число Mb, размеры буфферов MyISAM (за исключнием момента творения индекса) не превосходят 100Mb.
big_range_table.ibd 1740M
big_table.ibd 5520M — в отсутствие индексов
big_table.ibd 8268M — с индексами
т.е. попадание объектов в кэш БД до начала запроса абсолютно исключено.