Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 20
> Таким образом если у вас в таблице primary key (ID), key (A,B,C), то в реальности у вас второй ключ не (A,B,C), а (A,B,C,ID).
Тогда уж (ID, A, B, C).
И на мой взгляд как-то многовато воды в статье. Хотелось бы больше примеров на реальных данных и с DDL таблиц.
Тогда уж (ID, A, B, C).
И на мой взгляд как-то многовато воды в статье. Хотелось бы больше примеров на реальных данных и с DDL таблиц.
Не знаю, уместно ли спорить — но вот тут же на хабре есть подробная статья об этом
Фактически, вторичный ключ вида KEY `key` (a,b,c) будет иметь структуру KEY `key` (a,b,c,clustered_index).
С уважением,
PAV.
Фактически, вторичный ключ вида KEY `key` (a,b,c) будет иметь структуру KEY `key` (a,b,c,clustered_index).
С уважением,
PAV.
>Да, MySQL используют только B-tree индексы,
dev.mysql.com/doc/refman/5.5/en/create-index.html
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
dev.mysql.com/doc/refman/5.5/en/create-index.html
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
Я крайне извиняюсь что не выделил специальным шрифтом фразу
> Рассматривается движок MySQL innodb/percona — в дальнейшем просто MySQL.
Сейчас выделю.
По той же ссылке видим:
Storage Engine Permissible Index Types
MyISAM — BTREE
InnoDB — BTREE
MEMORY/HEAP — HASH, BTREE
NDB — HASH, BTREE (see note in text)
> Рассматривается движок MySQL innodb/percona — в дальнейшем просто MySQL.
Сейчас выделю.
По той же ссылке видим:
Storage Engine Permissible Index Types
MyISAM — BTREE
InnoDB — BTREE
MEMORY/HEAP — HASH, BTREE
NDB — HASH, BTREE (see note in text)
14.2.10.4. Adaptive Hash Indexes
If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit from building a hash index, it does so automatically.
If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit from building a hash index, it does so automatically.
Адаптивный — это не то. Хотя бы потому, что мы никак на него влиять не можем.
Впрочем, никто не мешает прозрачно эмулировать хэш-индекс дополнительным полем и парой триггеров.
Впрочем, никто не мешает прозрачно эмулировать хэш-индекс дополнительным полем и парой триггеров.
Похоже у нас завязалась настоящая дискуссия.
Я хотел бы попросить вас, уважаемый shagguboy, дочитывать приводимые ссылки до конца.
А далее там же написано что адаптивный хеш индекс строится только в памяти, только поверх существующего B-tree индекса, и только в тех случаях когда оптимайзер найдет это целесообразным. То есть в случае множества запросов с type=const/ew_ref
К сожалению такие случаи не попадают под изначальные условия статьи.
Но чтобы закончить спор я переформулирую фразу.
С уважением,
PAV.
Я хотел бы попросить вас, уважаемый shagguboy, дочитывать приводимые ссылки до конца.
А далее там же написано что адаптивный хеш индекс строится только в памяти, только поверх существующего B-tree индекса, и только в тех случаях когда оптимайзер найдет это целесообразным. То есть в случае множества запросов с type=const/ew_ref
К сожалению такие случаи не попадают под изначальные условия статьи.
Но чтобы закончить спор я переформулирую фразу.
С уважением,
PAV.
> MySQL создает только кластерные индексы.
вы перманентно путаете MYSQL и storage engine
вы перманентно путаете MYSQL и storage engine
Кстати, что касается задания. В задаче не хватает условий. Например, какой тип у нас имеет поле D, текст это, чар или варчар? Какие значения принимает.
В своё время был удивлён, когда узнал, что like работает достаточно быстро при выборках типа like 'any%'.
В своё время был удивлён, когда узнал, что чар на порядок быстрее варчара при поиске, т.к. выделяется определённое количество места в памяти под значение.
В настоящее время был удивлён, когда узнал, что уже не надо оптимизировать OR, что mysql это делает сама )
В своё время был удивлён, когда узнал, что like работает достаточно быстро при выборках типа like 'any%'.
В своё время был удивлён, когда узнал, что чар на порядок быстрее варчара при поиске, т.к. выделяется определённое количество места в памяти под значение.
В настоящее время был удивлён, когда узнал, что уже не надо оптимизировать OR, что mysql это делает сама )
> какой тип у нас имеет поле D, текст это, чар или варчар?
Так как сортировки по D в примере нет, то все три толстых строковых типа ведут себя одинаково.
> Какие значения принимает.
Безусловно ограничений на количество значений нет. На мой взгляд самоочевидно то, что что при наличии ограничений на количество значений в строковом типе в таблицах с миллионами записей, использование char/varchar/text не эффективно. Для таких случаев следует использовать статический (ENUM) или динамический (отдельная таблица) классификатор. И то и другое сводят толстое поле к тонкому и в таблице и в индексе.
> В своё время был удивлён, когда узнал, что like работает достаточно быстро при выборках типа like 'any%'.
Только при наличии b-tree индекса.
> В своё время был удивлён, когда узнал, что чар на порядок быстрее варчара при поиске, т.к. выделяется определённое количество места в памяти под значение.
Только при отсутствии индекса, и при условии что в таблице нет полей с непостоянной длиной (varchar/text/etc).
> В настоящее время был удивлён, когда узнал, что уже не надо оптимизировать OR, что mysql это делает сама )
Не все так радужно. Для простых условий — делает. А если OR внутри сложной комбинации скобок — то может ошибаться.
Похоже уже пора рисовать десять заповедей оптимизатора (My)SQL.
Вот и мои пять копеек. В статью не включил, потому что объяснить такую черную магию не могу.
Если у вас гарантированный полный скан покрывающего индекса для запроса без order by, и у таблицы первичный ключ — автоинкрементное поле, то добавление этого поля в начало покрывающего индекса даст 15-20% улучшения скорости. (Да, в реальности это означает что автоинкрементное поле дважды присутствует в индексе — в начале и в конце).
Так как сортировки по D в примере нет, то все три толстых строковых типа ведут себя одинаково.
> Какие значения принимает.
Безусловно ограничений на количество значений нет. На мой взгляд самоочевидно то, что что при наличии ограничений на количество значений в строковом типе в таблицах с миллионами записей, использование char/varchar/text не эффективно. Для таких случаев следует использовать статический (ENUM) или динамический (отдельная таблица) классификатор. И то и другое сводят толстое поле к тонкому и в таблице и в индексе.
> В своё время был удивлён, когда узнал, что like работает достаточно быстро при выборках типа like 'any%'.
Только при наличии b-tree индекса.
> В своё время был удивлён, когда узнал, что чар на порядок быстрее варчара при поиске, т.к. выделяется определённое количество места в памяти под значение.
Только при отсутствии индекса, и при условии что в таблице нет полей с непостоянной длиной (varchar/text/etc).
> В настоящее время был удивлён, когда узнал, что уже не надо оптимизировать OR, что mysql это делает сама )
Не все так радужно. Для простых условий — делает. А если OR внутри сложной комбинации скобок — то может ошибаться.
Похоже уже пора рисовать десять заповедей оптимизатора (My)SQL.
Вот и мои пять копеек. В статью не включил, потому что объяснить такую черную магию не могу.
Если у вас гарантированный полный скан покрывающего индекса для запроса без order by, и у таблицы первичный ключ — автоинкрементное поле, то добавление этого поля в начало покрывающего индекса даст 15-20% улучшения скорости. (Да, в реальности это означает что автоинкрементное поле дважды присутствует в индексе — в начале и в конце).
Поясните, что из описанного специфично именно для перконы и не работает для обычных innodb/myisam?
Думается, оборот «обычных innodb/myisam» не совсем корректен. Эти два движка имеют столько различий, что с равным успехом можно было бы говорить об «обычных Oracle / MS SQL Server».
Percona XtraDB (Innodb-plugin) просто оптимизированный форк от InnoDB. Чего-то специфичного в оптимизации на уровне SQL запросов, отличающего его от InnoDB, мне не известно. С удовольствием узнаю что-то новое.
Относительно оптимизации запросов для MyISAM видимо нужна отдельная статья. Но я не большой специалист по MyISAM.
С уважением,
PAV.
Percona XtraDB (Innodb-plugin) просто оптимизированный форк от InnoDB. Чего-то специфичного в оптимизации на уровне SQL запросов, отличающего его от InnoDB, мне не известно. С удовольствием узнаю что-то новое.
Относительно оптимизации запросов для MyISAM видимо нужна отдельная статья. Но я не большой специалист по MyISAM.
С уважением,
PAV.
Мне показался несколько странным и удивительным приведенный Вами порядок
>> Во-первых, смотрим на клоз order by
>>Далее клоз where.
Сразу оговорюсь я не знаком с МySQL (но стремлюсь быть в курсе), я эскалирую свои знания из других систем.
Если бы речь шла об оракле, я бы настаивал на следующем порядке:
В первую очередь смотрим на where. Причем только на строгие равентсва (=,in).
Во вторую очередь смотрим на ордер бай и отбор по диапазонам. Здесь все очень не однозначно, зависит от состава данных. Для поиска по диапазону мне удавалось использовать только один предикат, по этой причине тут надо выбрать наиболее селективный и оценить, что нам будет дешевле — отсортировать набор, полученный с отбором по всем предикатам, или же сканирование индекса по предикатам выбранным в первую очередь, но без сортировки.
В третью очередь, соответственно, ставим то, что не во поставили во вторую
В четвертую очередь, в самый хвост ставим селект лист и отбор по неравенству.
Если мы будем поступать так, и звезды к нам будут благосклонны, вместо фулскана индекса, мы можем отделаться поиском по диапазону, что может статься существенно дешевле, даже если мы при том не избавимся от сортировки.
В MySQL как-то все совсем иначе?
>> Во-первых, смотрим на клоз order by
>>Далее клоз where.
Сразу оговорюсь я не знаком с МySQL (но стремлюсь быть в курсе), я эскалирую свои знания из других систем.
Если бы речь шла об оракле, я бы настаивал на следующем порядке:
В первую очередь смотрим на where. Причем только на строгие равентсва (=,in).
Во вторую очередь смотрим на ордер бай и отбор по диапазонам. Здесь все очень не однозначно, зависит от состава данных. Для поиска по диапазону мне удавалось использовать только один предикат, по этой причине тут надо выбрать наиболее селективный и оценить, что нам будет дешевле — отсортировать набор, полученный с отбором по всем предикатам, или же сканирование индекса по предикатам выбранным в первую очередь, но без сортировки.
В третью очередь, соответственно, ставим то, что не во поставили во вторую
В четвертую очередь, в самый хвост ставим селект лист и отбор по неравенству.
Если мы будем поступать так, и звезды к нам будут благосклонны, вместо фулскана индекса, мы можем отделаться поиском по диапазону, что может статься существенно дешевле, даже если мы при том не избавимся от сортировки.
В MySQL как-то все совсем иначе?
Домашка:
1)
Но тут, право же, оба индекса сделаны покрываеющими только лишь от того, что тема про покрывающие индексы. Нужны ли тут покрывающие — всамделе большой вопрос. Мне так думается, в большинстве случаев достаточно было бы индексов по А,B и C,D
2)
тут собсна то же саоме
В какой — верхний или нижний запрос поставить предикат b is null — не зна, зависит от того, где нулы в индексе mysql хранит — в верху или внизу
c покрытием то же самое, мне думается можно было бы обойтись индексами по B и C,D,B
1)
select A,B,C,D from tableName where A=1 and B=2 or C=3 and D like 'test%';
create index indexAB on table tableName (A,B,C,D);
create index indexCD on table tableName (C,D,A,B);
select A,B,C,D from tableName where A=1 and B=2
union all
select A,B,C,D from tableName where C=3 and D like 'test%' and (not (A=1 and B=2) or A is null or B is null)
)
Но тут, право же, оба индекса сделаны покрываеющими только лишь от того, что тема про покрывающие индексы. Нужны ли тут покрывающие — всамделе большой вопрос. Мне так думается, в большинстве случаев достаточно было бы индексов по А,B и C,D
2)
select A,C,D from tableName where B=3 or C=3 and D ='test' order by B;
тут собсна то же саоме
create index (B,A,C,D);
create index (C,D,B,A);
select A,C,D from tableName where (B<3 /*or B is null*/)and C=3 and D ='test' order by B
union all
select A,C,D from tableName where B=3
union all
select A,C,D from tableName where (B>3 /*or B is null*/)and C=3 and D ='test' order by B
В какой — верхний или нижний запрос поставить предикат b is null — не зна, зависит от того, где нулы в индексе mysql хранит — в верху или внизу
c покрытием то же самое, мне думается можно было бы обойтись индексами по B и C,D,B
Браво, ztxn. Замечательное приведение к type=range. Добавил вам плюсик.
Но все же суть статьи была не в этом. Попробую пояснить.
Для большого числа видов запросов, определяемых пользователями, и в большинстве своем сложных и несводимых к type=range, для больших и широких таблиц, в свое время было выяснено, что одни и те же запросы (с точностью до автоматического перевода) вполне нормально работают на PostgreSQL/MS SQL Server (порт на Oracle сейчас готовится, и пока что показывает себя вполне пристойно), но начинают крайне медленно выполняться и сильно нагружать систему на MySQL, при наличии одних и тех же индексов.
Стоит отметить, что изначально система писалась именно под MySQL, и порты на другие базы данных зачастую не так оптимальны в силу необходимости перевода «нестандартных возможностей» MySQL.
С другой стороны, в защиту MySQL стоит сказать что остальная работа системы (читай обычные запросы) на MySQL выполняются либо так же быстро, либо гораздо быстрее чем на PostgreSQL/MS SQL Server.
В свете выявленной проблемы, и отсутствии возможности отказаться от MySQL, был разработан алгоритм, работающий только для MySQL, выявляющий покрывающие запрос индексы, информирующий оптимизатор MySQL об их наличии, или администратора системы об их отсутствии. Именно этот реально работающий, и решающий такую проблему с MySQL, алгоритм и был описан в статье.
Почему только покрывающий индекс, а не перевод условия в ДНФ, накопление статистики по данным столбцов, разбиение порядка и так далее, по списку того что было продемонстрировано выше, и многое другое? Другими словами почему не написать оптимизатор оптимизатора MySQL? Ответ, я думаю, очевиден.
Таким образом, видимо стоит добавить в задание, условие «допустим запросы не сводимы к type=range»
Относительно сортировки. Если покрывающий индекс не начинается со столбцов используемых в клозе order by, то нам грозит using filesort, который приводит к самым серьезным нагрузкам системы. Поэтому это условие первое.
Но все же суть статьи была не в этом. Попробую пояснить.
Для большого числа видов запросов, определяемых пользователями, и в большинстве своем сложных и несводимых к type=range, для больших и широких таблиц, в свое время было выяснено, что одни и те же запросы (с точностью до автоматического перевода) вполне нормально работают на PostgreSQL/MS SQL Server (порт на Oracle сейчас готовится, и пока что показывает себя вполне пристойно), но начинают крайне медленно выполняться и сильно нагружать систему на MySQL, при наличии одних и тех же индексов.
Стоит отметить, что изначально система писалась именно под MySQL, и порты на другие базы данных зачастую не так оптимальны в силу необходимости перевода «нестандартных возможностей» MySQL.
С другой стороны, в защиту MySQL стоит сказать что остальная работа системы (читай обычные запросы) на MySQL выполняются либо так же быстро, либо гораздо быстрее чем на PostgreSQL/MS SQL Server.
В свете выявленной проблемы, и отсутствии возможности отказаться от MySQL, был разработан алгоритм, работающий только для MySQL, выявляющий покрывающие запрос индексы, информирующий оптимизатор MySQL об их наличии, или администратора системы об их отсутствии. Именно этот реально работающий, и решающий такую проблему с MySQL, алгоритм и был описан в статье.
Почему только покрывающий индекс, а не перевод условия в ДНФ, накопление статистики по данным столбцов, разбиение порядка и так далее, по списку того что было продемонстрировано выше, и многое другое? Другими словами почему не написать оптимизатор оптимизатора MySQL? Ответ, я думаю, очевиден.
Таким образом, видимо стоит добавить в задание, условие «допустим запросы не сводимы к type=range»
Относительно сортировки. Если покрывающий индекс не начинается со столбцов используемых в клозе order by, то нам грозит using filesort, который приводит к самым серьезным нагрузкам системы. Поэтому это условие первое.
Не иначе. Это в статье все иначе, плюс куча ошибок, не говоря уж про «лукапы» и «клозы».
> Один тонкий момент, про который иногда забываешь — MySQL создает только кластерные индексы.
Что-то у Вас с терминологией. Почитайте какие индексы кластерные, а какие дополнительные (или вторичные) здесь: dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html
> Следует указать на разницу в кешировании запросов в разных базах. Если PostgreSQL/Oracle кешируют планы запросов (как бы prepare for some timeout), то MySQL просто кеширует СТРОКУ запроса (включая значение параметров) и сохраняет результат запроса.
Это Вы зачем-то поведение query cache объясняете. Каким оно тут боком — непонятно.
Что-то у Вас с терминологией. Почитайте какие индексы кластерные, а какие дополнительные (или вторичные) здесь: dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html
> Следует указать на разницу в кешировании запросов в разных базах. Если PostgreSQL/Oracle кешируют планы запросов (как бы prepare for some timeout), то MySQL просто кеширует СТРОКУ запроса (включая значение параметров) и сохраняет результат запроса.
Это Вы зачем-то поведение query cache объясняете. Каким оно тут боком — непонятно.
Согласен, неудачная фраза. Думаю всех устроит оборот "… создает только таблицы с кластерным индексом"
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Оптимизация сложных запросов MySQL