Прежде чем писать статью, спасибоТехнология ФейюДокументы, предоставленные г-ном Лу. .
Объявить: следующие схемы оптимизации основаны на "Mysql-index-Тип BTree" из
1. ОБЪЯСНИТЕ
Чтобы оптимизировать MySQL, мы должны правильно его использовать.EXPLAINПросмотрите план выполнения SQL.
Возьмем простой пример, пометим (1, 2, 3, 4, 5) данные, на которых мы хотим сосредоточиться.
- столбец типа, тип соединения. Хороший оператор SQL должен как минимум достигать уровня диапазона. Предотвратить появление всех уровней
- ключевой столбец, используемое имя индекса. Если индекс не выбран, значение равно NULL. принудительная индексация
- столбец key_len, длина индекса
- Столбец rows, количество строк для сканирования. Это значение является оценочным
- дополнительная колонка, подробное описание. Обратите внимание, что распространенными недружественными значениями являются: Использование сортировки файлов, Использование временных
2. Значение, содержащееся в IN в операторе SQL, не должно быть слишком большим.
MySQL сделал соответствующие оптимизации для IN, то есть все константы в IN хранятся в массиве, и массив сортируется. Однако, если значение велико, потребление также относительно велико. Другой пример: выберите идентификатор из t, где число в (1,2,3) Для непрерывных значений, если вы можете использовать между, не используйте в; или вместо этого используйте соединение.
3. Оператор SELECT должен указывать имя поля
SELECT * увеличивает много ненужного потребления (процессор, ввод-вывод, память, пропускная способность сети); увеличивает возможность использования покрывающих индексов; при изменении структуры таблицы предыдущее прерывание также необходимо обновить. Поэтому требуется подключить имя поля сразу после выбора.
В-четвертых, когда нужен только один фрагмент данных, используйте ограничение 1.
Это делается для того, чтобы столбец типа в EXPLAIN достигал константного типа.
5. Если поле сортировки не использует индекс, постарайтесь сортировать как можно меньше
6. Если в ограничениях нет индексов в других полях, используйте или как можно меньше
Если одно из полей по обеим сторонам или не является полем индекса, а другие условия не являются полями индекса, это приведет к тому, что запрос не будет проходить через индекс. Многократное использование union all или union (при необходимости) вместо «или» даст лучшие результаты.
7. Попробуйте использовать union all вместо union
Основное различие между объединением и объединением всех заключается в том, что первому необходимо объединить набор результатов, а затем выполнить уникальную операцию фильтрации, которая включает в себя сортировку, увеличение количества операций ЦП, увеличение потребления ресурсов и задержки.Конечно, предварительным условием для объединения всех является то, что два набора результатов не содержат повторяющихся данных.
Восемь, не используйте ORDER BY RAND ()
select id from `dynamic` order by rand() limit 1000;Приведенный выше оператор sql может быть оптимизирован как
select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nid limit 1000;9. Различать в и существует, не в и не существует
select * from 表A where id in (select id from 表B)Приведенный выше оператор sql эквивалентен
select * from 表A where exists(select * from 表B where 表B.id=表A.id)Различение в и существует в основном приводит к изменению порядка управления (это ключ к изменениям производительности). Если он существует, то внешняя таблица является управляющей таблицей, к которой обращаются в первую очередь. Если это IN, то подзапрос выполняется первым. такIN подходит для случая, когда внешний вид большой, а внутренний маленький; EXISTS подходит для случая, когда внешний вид маленький, а внутренний большой.
Что касается не в и не существует, рекомендуется использовать не существует не только из-за проблем с эффективностью, но и не может иметь логических проблем.Как эффективно написать оператор SQL, который заменяет не существует?
исходный оператор sql
select colname … from A表 where a.id not in (select b.id from B表)Эффективный оператор SQL
select colname … from A表 Left join B表 on where a.id = b.id where b.id is nullПолученный набор результатов показан на следующем рисунке, данные в таблице A отсутствуют в таблице B.
10. Используйте разумный метод пейджинга для повышения эффективности пейджинга
select id,name from product limit 866613, 20При использовании приведенного выше оператора sql для разбиения по страницам некоторые люди могут обнаружить, что с увеличением объема табличных данных прямое использование запроса на разбиение по страницам будет становиться все медленнее и медленнее.
Метод оптимизации следующий: можно взять id максимального количества строк предыдущей страницы, а затем ограничить начальную точку следующей страницы по максимальному id. Например, в этом столбце самый большой идентификатор предыдущей страницы — 866612. sql можно записать следующим образом:
select id,name from product where id> 866612 limit 20Одиннадцать, сегментированный запрос
На некоторых страницах выбора пользователя диапазон времени, выбранный некоторыми пользователями, может быть слишком большим, что приводит к медленному запросу. Основная причина — слишком много строк для сканирования. В настоящее время вы можете выполнять запросы по программам, сегментам, циклам и объединять результаты для отображения.
Как показано в следующем операторе sql, сегментированные запросы можно использовать, когда количество отсканированных строк превышает один миллион.
12. Избегайте нулевых значений для полей в предложении where
Оценка null заставит движок отказаться от использования индекса и выполнить полное сканирование таблицы.
13. Не рекомендуется использовать нечеткий запрос с префиксом %
Например, LIKE "%name" или LIKE "%name%", запрос такого типа приведет к сбою индекса и выполнению полного сканирования таблицы. Но можно использовать LIKE "name%".
Как запросить %name%?
Как показано на рисунке ниже, несмотря на то, что к секретному полю добавлен индекс, он не используется в результате объяснения.
Итак, как решить эту проблему, ответ:Использовать полнотекстовое индексирование
Выберите id,fnum,fdst из dynamic_201606, где user_name, например «%zhangsan%», часто используется в наших запросах. Для такого оператора обычные индексы не могут удовлетворить требованиям запроса. К счастью, в MySQL есть полнотекстовые индексы, которые нам в помощь.
Синтаксис SQL для создания полнотекстового индекса:
ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);Оператор sql для использования полнотекстового индекса:
select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);Примечание. Прежде чем создавать полнотекстовый индекс, обратитесь к своему администратору баз данных, чтобы узнать, можно ли его создать. При этом необходимо обратить внимание на отличие способа написания оператора запроса от обычного индекса.
14. Избегайте операций с выражениями над полями в предложениях where
Например
select user_id,user_project from user_base where age*2=36;Над полем посередине выполняется арифметическая операция, из-за чего движок отказывается от использования индекса, рекомендуется изменить его на
select user_id,user_project from user_base where age=36/2;15. Избегайте неявных преобразований типов
Преобразование типа, которое происходит, когда тип поля столбца в предложении where не соответствует типу входящего параметра, рекомендуется сначала определить тип параметра в предложении where.
16. Для совместных индексов соблюдайте правило крайнего левого префикса.
Например, индекс содержит поля идентификатор, имя и школа.Вы можете использовать поле идентификатора напрямую или можете использовать порядок идентификатора и имени, но ни одного имени; школа может использовать этот индекс. Поэтому при создании объединенного индекса необходимо обращать внимание на порядок полей индекса, а часто используемые поля запроса размещаются вверху.
17. При необходимости вы можете использовать принудительный индекс, чтобы заставить запрос пройти через индекс.
有的时候MySQL优化器采取它认为合适的索引来检索sql语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。18. Обратите внимание на оператор запроса диапазона
Для объединенного индекса, если есть запрос диапазона, например между, >,
Девятнадцать, об оптимизации JOIN
- LEFT JOIN Таблица является управляющей таблицей
- INNER JOIN MySQL автоматически найдет таблицу с меньшим количеством данных для управления таблицей
- Таблица RIGHT JOIN B — управляющая таблица
Примечание. В MySQL нет полного соединения, которое можно решить следующими способами.
select * from A left join B on B.name = A.name
where B.name is null
union all
select * from B;
Попробуйте использовать внутреннее соединение и избегайте левого соединения
Таблицы, участвующие в совместном запросе, представляют собой как минимум две таблицы, которые обычно различаются по размеру. Если методом соединения является внутреннее соединение, MySQL автоматически выберет маленькую таблицу в качестве управляющей таблицы при отсутствии других условий фильтрации, но левое соединение следует принципу левого управления правым при выборе управляющей таблицы, то есть имя таблицы слева от левого соединения для таблицы дисков.
Эффективно используйте индексы
Поле индекса управляемой таблицы используется в качестве поля предела включения.
Используйте маленькие таблицы для управления большими таблицами
Из схемы интуитивно видно, что если уменьшить таблицу накопителей, можно уменьшить количество циклов во вложенном цикле, чтобы уменьшить общий объем операций ввода-вывода и количество операций ЦП.
Разумное использование STRAIGHT_JOIN
Внутреннее соединение выбирается mysql для управления таблицей, но в некоторых особых случаях необходимо выбрать другую таблицу в качестве управляющей таблицы, например, когда есть «Использование сортировки файлов» и «Использование временных», таких как группировка и порядок. STRAIGHT_JOIN для принудительного порядка соединения, имя таблицы слева от STRAIGHT_JOIN — это управляющая таблица, а справа — управляемая таблица.Предпосылкой для использования STRAIGHT_JOIN является то, что запрос является внутренним соединением, то есть внутренним соединением. STRAIGHT_JOIN не рекомендуется для других ссылок, иначе результат запроса может быть неточным.
Этот метод иногда может сократить время в 3 раза.
Здесь перечислены только приведенные выше схемы оптимизации.Конечно, есть и другие способы оптимизации.Вы можете изучить и попробовать.Спасибо за внимание. .