Mysql performance

Написание этой статьи навеяно вот этой трилогией: один, два, три. Захотелось добавить свои 0.02$, по использованию трюков и особенностей.
Чтоб мне не запутаться в мыслях, в первом своём посте, возмём примеры запросов из одного open-source продукта. Используемые «хитрости» работают также на PostgreSQL, Oracle, SQLite, DB2 и не являются MySQL направленными, хотя оптимизация в первую очередь нацелена на MySQL InnoDB:

SELECT h.hostid,hg.groupid,h.name
FROM hosts h,hosts_groups hg
WHERE (hg.groupid IN ('4'))
  AND hg.hostid=h.hostid
  AND hg.groupid BETWEEN 000000000000000 AND 099999999999999
  AND h.status IN (0,1)
  AND EXISTS (
    SELECT hh.hostid
    FROM hosts hh, hosts_groups hgg, rights r, users_groups ug
    WHERE hh.hostid=h.hostid
      AND hh.hostid=hgg.hostid
      AND r.id=hgg.groupid
      AND r.groupid=ug.usrgrpid
      AND ug.userid=3
      AND r.permission>=3
      AND NOT EXISTS(
        SELECT hggg.groupid
        FROM hosts_groups hggg, rights rr, users_groups gg
        WHERE hggg.hostid=hgg.hostid
          AND rr.id=hggg.groupid
          AND rr.groupid=gg.usrgrpid
          AND gg.userid=3
          AND rr.permission<3
    ))
ORDER BY h.name ASC
LIMIT 1001

* This source code was highlighted with Source Code Highlighter.

Ситуация: есть авторизированный пользователь, пользователь принадлежит к группе пользователей. Есть хосты принадлежащие группе хостов.
Суть: вытащить хосты к которым пользователь имеет доступ. Доступ даётся группам пользователей на группы хостов.

Первое на что хочу обратить внимание это на условие EXISTS. Очень редко вижу использование этой структуры. EXISTS — это подзапрос, который проверят наличие строк в подзапросе. Такая конструкция запроса позволяет манипулировать используемыми в запросе индексами (как в основном запросе, так и в подзапросе) вне зависимости от общего запроса, к тому же, в случае успеха, подзапрос останавливается на первой найденной строке, удовлетворяющей запросу. Манипулировать ключами часто приходится когда в запросе используется сортировка. Поскольку MySQL не может использовать разные ключи для поиска и сортировки.

Если запрос происходит по одной таблице, то иногда можно использовать такие хитрости:

CREATE TABLE events (
    eventid         bigint unsigned              NOT NULL,
    source          integer     DEFAULT '0'        NOT NULL,
    object          integer     DEFAULT '0'        NOT NULL,
    objectid         bigint unsigned DEFAULT '0'        NOT NULL,
    clock          integer     DEFAULT '0'        NOT NULL,
    value          integer     DEFAULT '0'        NOT NULL,
    acknowledged       integer     DEFAULT '0'        NOT NULL,
    ns            integer     DEFAULT '0'        NOT NULL,
    value_changed      integer     DEFAULT '0'        NOT NULL,
    PRIMARY KEY (eventid)
) ENGINE=InnoDB;
CREATE INDEX events_1 ON events (object,objectid,eventid);
CREATE INDEX events_2 ON events (clock,eventid);

* This source code was highlighted with Source Code Highlighter.

EXPLAIN
SELECT eventid,clock,value
FROM events
WHERE objectid=17131
  AND object=0
  AND clock>=1325635327
ORDER BY clock DESC, eventid DESC;

* This source code was highlighted with Source Code Highlighter.

+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | events | ref | events_1,events_2 | events_1 | 12 | const,const | 113056 | Using where; Using filesort |
+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+

Например MySQL посчитал, что использовать в WHERE выгоднее индекс «events_1», это и понятно, ведь в WHERE задействованы два поля ключа, но он не учёл, что результат содержит 100к строк и их надо сортировать.
В данном случае, изменив в запросе критерий первого поля выбранного MySQL индекса:

EXPLAIN
SELECT eventid,clock,value
FROM events
WHERE objectid=17131
  AND object+0=0
  AND clock>=1325635327
ORDER BY clock DESC, eventid DESC;

* This source code was highlighted with Source Code Highlighter.

+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | events | range | events_2 | events_2 | 4 | NULL | 113056 | Using where |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+

MySQL не может использовать индексы на модифицированных значениях полей, в данном случае применив арифметическое присваивание мы заставляем использовать индекс events_2, который подходит как для выборки данных так и для сортировки, что видно из EXPLAIN.

Обращаю внимание, что MySQL не умеет сортировать по индексу, если используемые поля сортируются в разном порядке:

EXPLAIN
SELECT eventid,clock,value
FROM events
WHERE objectid=17131
  AND object+0=0
  AND clock>=1325635327
ORDER BY clock ASC, eventid DESC;

* This source code was highlighted with Source Code Highlighter.

+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | events | range | events_2 | events_2 | 4 | NULL | 113056 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+

Так же, индекс не используется, если сортировка происходит по полям стоящим в другом порядке нежели в индексе. Вообще, MySQL хранит индексы как B-дерево, по-этому использовать поля стоящие в середине или конце индекса не получится.

Если невозможно использовать один индекс для выборки и сортировки, то случалось так, что MySQL не правильно делал оценку и выбирал индексы для сортировки. Это случается при прохождении некого количества записей в таблице, когда становится выгодней, чтоб MySQL использовать индекс для выборки, а не для сортировки или на оборот. Такие моменты предусмотреть сложно, и выявляются проверяя одни и те же запросы на разном количестве записей.

Вернёмся к первому запросу. На мой взгляд лучше сортировать уже средствами исполняемого скрипта, разумеется если выборка в пределах тысяч строк.
Во-первых, в этом случае MySQL не приходится делать полную выборку из таблицы, а останавливается по достижению LIMIT;
Во-вторых, редко когда действительно нужно одновременно выводить больше информации, если это только не отчёты;
В-третьих, страницы с большим количеством информации будут медленными, даже если это просто выпадающий список с 1000+ вариантами, это уже не юзабельно;
В-четвёртых, в MySQL нет натуральной сортировки;

Но в реальных примерах это редко возможно, т.к. приходится давать пользователям возможность сортировки, да и без указания сортировки MySQL не гарантирует одинаковую выборку. Как вариант можно в индекс ставить первым полем, то по которому чаще происходит сортировка, тем самым MySQL будет использовать один индекс и для поиска и для сортировки.

COUNT, многие делают второй запрос для постраничного вывода, но тот же гугле, хоть и говорит, что найдено миллионы+ совпадений, реально вам выдаст, примерно, первую тысячу и всё. Причём на последней странице сообщит, что на самом деле найдено меньше. Так вот, выбрав 1001 строку мы просто говорим пользователю, что найдено 1000+ совпадений и нет нужды выбирать на данном этапе больше. Кода пользователь запросит больше, тогда и будем выбирать шаг за шагом по 1ой страничке. Проверяя на 1 строку больше чем надо.

Индексы. По мере запросов к таблицам, индексы этих таблиц кешируются в памяти и там остаются пока не закончится память и тогда их «просят» выйти. Так вот, если у вас гигабайты информации, то индексы будут занимать ± 40% процентов места, зависит от количества тех самых индексов. К примеру у Вас скромный сервер с 16гб оперативки выделенными под MySQL. При запросе в таблицу с индексом весом в 10гб+, вся память выделенная под MySQL будет высвобождена и заполнена этим индексом и все предыдущие закешированные индексы скинутся в небытие. Таким образом, делая один тяжёлый запрос вы можете убить весь перформанс сервера. Что делать? Вариантов много, но не сказал бы, что они просты и сходятся к хранению больших >10 миллионов+ таблиц на отдельных хранителях, например BigTable, NoSQL или даже NoSQL для MySQL и т.д.

На этом, пока всё. Буду рад выслушать ваше решения и советы по выше сказанному.