Оптимизация MySQL по индексам



Для оптимизации скорости работы SQL-запросов в лучше:

  • Создавать комбинированный индекс по колонкам, которые вы выбираете при помощи AND. Не забывайте при этом про «левую сторону». Выборка по (col2) или (col2, col3) не будет использовать комбинированный индекс (col1, col2, col3), т.к. он начинается с col1.
  • Создавать одиночные индексы на каждую колонку, выбираемую через OR (в этом случае будет задействован поиск по пересечению индексов, что медленней комбинированного индекса, который тут использоваться не может, но быстрее, чем без индексов вообще)
  • Если с помощью индексов не удается отсеить порядка 80% записей, то MySQL считает лучшим полное сканирование таблицы.
  • При JOIN очень важная последовательность выборки. Если вы джоините таблицу со значительно меньшим кол-вом записей, то (хотя, вероятно,это поймет и сам планировщик MySQL) логичней было бы выбрать записи в ней, а уже потом по выбранным записям подключить основную, более огромную таблицу. Если иначе, то сначала выбирается огромное кол-во данных из большой таблицы, а уже потом к ним подключается маленькая выборка по второй.
  • Всегда стоит использовать наиболее уникальные индексы (характеризумые мощностью данных, т.е. их уникальностью). Командой SHOW KEYS FROM table можно увидеть индексы, уникальность характеризуется колонкой Cardinality.
  • Почти всегда используется B-tree индекс, который очень хорош тогда, когда мощность данных большая. Можно также использовать пространственный индекс R-tree (для определенного типа данных) и хэш-индекс для таблиц типа Memory (хэш индекс хорош, когда используется точное сравнение и несколько хуже, когда используется больше меньше, потому что данные хранятся в виде значения хэш-функции и в этом же виде сравниваются).
  • B-tree индекс не будет использоваться в операторе LIKE, если его значение начинается со спецсимвола % или содержит другое поле.
Share Button
  • Роман

    Добрый день!
    Подскажите, пожалуйста.
    А если у меня есть комбинированный индекс col1,col3,col5, то когда я делаю выборку, важно ли соблюдать последовательность WHERE параметров? То есть если ли различие между:
    … WHERE col1 = 3 AND col3 = 4 AND col5 = 7 …
    и
    … WHERE col3 = 4 AND col5 = 7 AND col1 = 3 …

    И еще вопрос, если у меня есть запросы, в которых не участвуют некоторые столбцы, например:
    … WHERE AND col5 = 7 AND col1 = 3 …
    То мне надо делать два индекса? С тремя колонками и двумя? Как тогда это повлияет на производительность при вставке данных?

    И еще вопрос, как поведет себя MySQL, если у него будет B-Tree индекс по col3 и HASH индекс по col5. То как отработает этот запрос?
    … WHERE AND col5 = 7 AND col1 = 3 …

    Заранее спасибо за ответ!

    • MipH

      1. Можно соблюдать любую последовательность. Важно, чтобы самое наличие элементами в выборке следовало правилу слева направо, т.е. выборка WHERE col3 = 4 AND col5 = 7 не будет (не должна) использовать копмозитный индекс.

      2. Решите для себя, какие выборки вы делаете. Если они строго определены, то сделайте под них 2 составных индекса. Если же точно не известно, то лучше сделать отдельные индексы по каждому полю. Дальше планировшик СУБД сам решит, что и как ему использовать. Но перегружать таблицу индексами не стоит, особенно таблицу с часто изменяемым набором данных.

      3. Вы можете просто посмотреть план построения запроса, любые визуальные средства по работе с БД (равно как и консольные http://dev.mysql.com/doc/refman/4.1/en/explain.html) позволяют это сделать. Это наиболее точный ответ. Ну и вообще целесообразность применения хэш-индекса надо смотреть в контексте задачи.