Эта статья была опубликована сообществом cloud+community
В статье "Анализ запросов MySQL" описывается метод использования медленных запросов MySQL и команды объяснения для обнаружения узких мест в производительности MySQL. После обнаружения оператора SQL с узким местом в производительности необходимо оптимизировать неэффективный оператор SQL. В этой статье в основном обсуждается принцип индекса MySQL и обычно используемая оптимизация запросов sql.
Простой сравнительный тест
В предыдущем случае таблица c2c_zwdb.t_file_count имеет только один самоувеличивающийся идентификатор, а SQL-выполнение неиндексированного поля FFileName выглядит следующим образом:
На изображении выше type=all, key=null, rows=33777. SQL не использует индексы и является очень неэффективным полным сканированием таблицы. Если добавить комбинированный запрос и некоторые другие ограничения, база данных будет сильно потреблять память, и это повлияет на выполнение клиентских программ.
Затем добавьте индекс в поле FFileName:
alter table c2c_zwdb.t_file_count add index index_title(FFileName);
Выполните приведенный выше оператор запроса еще раз, сравнение очевидно:
На этом рисунке type=ref, key=index_title, rows=1. SQL использует индекс index_title и является постоянным сканированием, только одна строка сканируется в соответствии с индексом.
По сравнению с неиндексированным случаем, после добавления индекса сравнение эффективности запроса очень очевидно.
индексы MySQL
Из приведенного выше сравнительного теста видно, что ключом к быстрому поиску является индексирование. Создание индексов MySQL очень важно для эффективной работы MySQL. Для небольшого объема данных влияние отсутствия подходящего индекса не очень велико, но когда объем данных увеличивается, производительность резко падает. Если индексируется несколько столбцов (комбинированный индекс), порядок столбцов очень важен, и MySQL может выполнять эффективный поиск только по крайнему левому префиксу индекса.
Ниже описаны несколько распространенных типов индексов MySQL.
Индекс делится на индекс с одним столбцом и составной индекс. Одностолбцовый индекс, то есть индекс содержит только один столбец, таблица может иметь несколько одностолбцовых индексов, но это не составной индекс. Составной индекс, то есть один индекс содержит несколько столбцов.
1. Тип индекса MySQL
(1) Индекс первичного ключа PRIMARY KEY
Это специальный уникальный индекс, который не допускает пустых значений. Как правило, индекс первичного ключа создается одновременно с созданием таблицы.
Конечно, вы также можете использовать команду ALTER. Помните: у таблицы может быть только один первичный ключ.
(2) Уникальный индекс UNIQUE
Уникальные значения столбца индекса должны быть уникальными, но допускаются нулевые значения. В случае составного индекса комбинация значений столбцов должна быть уникальной. Его можно указать при создании таблицы или изменить структуру таблицы, например:
ALTER TABLE table_name ADD UNIQUE (column)
(3) Обыкновенный индекс ИНДЕКС
Это самый базовый индекс, он не имеет ограничений. Его можно указать при создании таблицы или изменить структуру таблицы, например:
ALTER TABLE table_name ADD INDEX index_name (column)
(4) Комбинированный индекс ИНДЕКС
Составной индекс, то есть один индекс содержит несколько столбцов. Его можно указать при создании таблицы или изменить структуру таблицы, например:
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)
(5) Полнотекстовый индекс FULLTEXT
Полнотекстовое индексирование (также называемое полнотекстовым поиском) является ключевой технологией, используемой в настоящее время поисковыми системами. Он может использовать технологию сегментации слов и другие алгоритмы для интеллектуального анализа частоты и важности ключевых слов в тексте, а затем интеллектуально отфильтровывать нужные результаты поиска в соответствии с определенными правилами алгоритма.
Его можно указать при создании таблицы или изменить структуру таблицы, например:
ALTER TABLE table_name ADD FULLTEXT (column)
2. Структура и принцип индекса
B+Tree обычно используется в качестве индекса в mysql, но реализация различается в зависимости от кластеризованного индекса и некластеризованного индекса.В этой статье этот момент обсуждаться не будет.
Введение дерева b+
Изображение дерева b+ ниже можно увидеть во многих местах.Причина, по которой это изображение также выбрано здесь, заключается в том, что я думаю, что это изображение может очень хорошо объяснить процесс поиска в индексе.
Как показано выше, это дерево b+. Светло-синий блок мы называем дисковым блоком, вы можете видеть, что каждый дисковый блок содержит несколько элементов данных (показаны темно-синим) и указатели (показаны желтым), например, дисковый блок 1 содержит элементы данных 17 и 35, содержит указатели P1 , P2, P3, P1 представляет дисковые блоки меньше 17, P2 представляет дисковые блоки от 17 до 35, а P3 представляет дисковые блоки больше 35.
Реальные данные существуют в листовых узлах, а именно 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Неконечные узлы не хранят реальные данные, а хранят только элементы данных, определяющие направление поиска.Например, 17 и 35 фактически не существуют в таблице данных.
Найти процесс
На приведенном выше рисунке, если вы хотите найти элемент данных 29, то сначала с диска в память будет загружен дисковый блок 1. В это время происходит IO. В памяти используется двоичный поиск для определения что 29 находится между 17 и 35, а блок диска 1 заблокирован.Указатель P2, время памяти незначительно, потому что оно очень короткое (по сравнению с вводом-выводом диска), а блок диска 3 загружается с диска в память через дисковый адрес указателя P2 дискового блока 1, и происходит второй IO, 29 в 26 и между 30, указатель P2 дискового блока 3 блокируется, и дисковый блок 8 загружается в память через указатель, и происходит третий ввод-вывод. Реальная ситуация такова, что трехслойное дерево b+ может представлять миллионы данных. Если для поиска миллионов данных требуется только три операции ввода-вывода, повышение производительности будет огромным. Если нет индекса, каждый элемент данных будет иметь операцию ввода-вывода. , тогда в общей сложности требуются миллионы операций ввода-вывода, очевидно, что стоимость очень и очень высока.
природа
(1) Поле индекса должно быть как можно меньше.
Из-за процесса поиска дерева b+ выше или из-за того, что реальные данные существуют в листовых узлах, количество операций ввода-вывода зависит от высоты h числа b+.
Предполагая, что объем данных текущей таблицы данных равен N, а количество элементов данных в каждом блоке диска равно m, высота дерева h=㏒(m+1)N, когда объем данных N постоянен, чем больше m есть, чем выше h, тем меньше;
И m = размер дискового блока / размер элемента данных, размер дискового блока - это размер страницы данных, который является фиксированным; если место, занимаемое элементом данных, меньше, число m элементов данных больше, а высота дерева меньше на h. Поэтому каждый элемент данных, то есть поле индекса, должен быть как можно меньше, например, int занимает 4 байта, что вдвое меньше, чем bigint 8 байт.
(2) Самый левый совпадающий признак индекса.
Когда элемент данных дерева b+ представляет собой составную структуру данных, например (имя, возраст, пол), число b+ строит дерево поиска в порядке слева направо, например, когда (Чжан Сан, 20 лет, Ж) Когда данные получены, дерево b+ сначала сравнивает имя, чтобы определить следующее направление поиска.Если имя совпадает, то по очереди сравнивает возраст и пол и, наконец, получает извлеченные данные; но когда нет имени такие как (20,F) Когда приходят данные, дерево b+ не знает, какой узел проверять следующим, потому что имя является первым фактором сравнения при построении дерева поиска, и его нужно искать по имени, чтобы узнать куда запросить дальше. Например, когда извлекаются такие данные, как (Чжан Сан, F), дерево b+ может использовать имя для указания направления поиска, но следующее поле age отсутствует, поэтому только данные, имя которых равно Zhang San, могут быть только найдено, а затем сопоставляется пол — это данные F. Это очень важное свойство, то есть крайний левый совпадающий признак индекса.
Несколько принципов построения индекса
(1) Принцип соответствия самого левого префикса
Для многоколоночных индексов всегда начинайте с первого поля индекса, а затем продолжайте, не пропуская середину. Например, если создается многостолбцовый индекс (имя, возраст, пол), сначала будет сопоставлено поле имени, затем будет сопоставлено поле возраста, а затем будет сопоставлено поле пола, и середина не может быть пропущена. . MySQL всегда будет сопоставляться справа, пока не встретит запрос диапазона (>,
Как правило, при создании многостолбцового индекса наиболее часто используемый столбец в предложении where помещается в крайнее левое положение.
Взгляните на пример дополнения, которое соответствует принципу соответствия крайнего левого префикса, и на пример, который соответствует этому принципу.
Пример: таблица c2c_db.t_credit_detail имеет индекс (Flistid,Fbank_listid)
Операторы SQL, которые не соответствуют принципу сопоставления крайнего левого префикса:
select * from t_credit_detail where Fbank_listid='201108010000199'\G
SQL напрямую использует второе поле индекса Fbank_listid и пропускает первое поле индекса Flistid, что не соответствует принципу сопоставления крайнего левого префикса. Используйте команду объяснения для просмотра плана выполнения инструкции sql, как показано ниже:
Как видно из рисунка выше, sql не использует индексы и является неэффективным полным сканированием таблицы.
Операторы SQL, соответствующие принципу сопоставления крайнего левого префикса:
select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199'\G
SQL сначала использует первое поле индекса Flistid, а затем использует второе поле индекса Fbank_listid, не пропуская середину, в соответствии с принципом соответствия крайнего левого префикса. Используйте команду объяснения для просмотра плана выполнения инструкции sql, как показано ниже:
Как видно из рисунка выше, sql использует индекс и сканирует только одну строку.
Для сравнения видно, что оператор SQL, который соответствует принципу сопоставления крайнего левого префикса, более эффективен, чем оператор SQL, который не соответствует этому принципу, от полного сканирования таблицы до постоянного сканирования.
(2) Попробуйте выбрать столбец с высокой степенью дискриминации в качестве индекса.
Например, мы выберем номер студента в качестве индекса, но не выберем пол в качестве индекса.
(3) = и in может быть не в порядке
Например, a = 1 и b = 2 и c = 3, индекс (a,b,c) может быть установлен в любом порядке, и оптимизатор запросов mysql поможет вам оптимизировать его в форме, которую индекс может распознать.
(4) Столбец индекса не может участвовать в расчете, сохраняя столбец «чистым».
Например: Flistid+1>'2000000608201108010831508721'. Причина очень проста: если в расчете участвует колонка индекса, индекс будет вычисляться один раз, а затем каждый раз сравниваться, очевидно, что стоимость слишком высока.
(5) Максимально расширьте индекс, не создавайте новый индекс.
Например, в таблице уже есть индекс a, и теперь вам нужно добавить индекс (a, b), тогда вам нужно только изменить исходный индекс.
Недостаточный индекс
Хотя индексы могут повысить эффективность запросов, они также имеют свои недостатки.
Дополнительные расходы на индексацию:
(1) Пространство: индекс должен заниматься пространством;
(2) Время: требуется время для запроса индекса;
(3) Обслуживание: индекс необходимо поддерживать (при изменении данных);
Когда индексация не рекомендуется:
(1) Таблицы с небольшим объемом данных
(2) Места мало
Общая сводка по оптимизации
Существует много утверждений по оптимизации, и есть много вещей, на которые следует обратить внимание.Обобщите следующие пункты для обычной ситуации:
1. Индекс есть, но не используется (не рекомендуется)
(1) Когда параметр Like начинается с подстановочного знака
Старайтесь избегать того, чтобы параметр Like начинался с подстановочного знака, иначе ядро базы данных откажется от использования индекса и выполнит полное сканирование таблицы.
Операторы SQL, начинающиеся с подстановочных знаков, например: выберите * из t_credit_detail, где Flistid как '%0'\G
Это полное сканирование таблицы, индекс не используется, и это не рекомендуется.
Операторы SQL, которые не начинаются с подстановочных знаков, например: select * from t_credit_detail, где Flistid вроде '2%'\G
Очевидно, что при этом используется индекс, который представляет собой поиск по диапазону, что намного эффективнее, чем оператор SQL, начинающийся с подстановочных знаков.
(2) Когда условие where не соответствует принципу крайнего левого префикса
Примеры даны в содержании принципа соответствия крайнего левого префикса.
(3) Используйте! = или оператор
Старайтесь избегать его использования! = или , в противном случае механизм базы данных откажется от использования индекса и выполнит полное сканирование таблицы. Использование > или
select * from t_credit_detail where Flistid != '2000000608201108010831508721'\G
(4) Столбец индекса участвует в расчете
Следует максимально избегать операций с выражениями над полями в предложениях where, что заставит движок отказаться от использования индексов и выполнить полное сканирование таблицы.
select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'\G
(5) Судействуя нулевое значение поля
Старайтесь избегать определения нулевого значения поля в предложении where, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы, например: Неэффективно: выберите * из t_credit_detail, где Flistid равно null ;
Вы можете установить значение по умолчанию 0 для Flistid, чтобы убедиться, что в столбце Flistid в таблице нет нулевого значения, а затем выполнить запрос следующим образом: Эффективно: выберите * из t_credit_detail, где Flistid = 0;
(6) Используйте или для подключения условий
Вы должны стараться избегать использования или подключения условий в предложении where, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы, например: 10000200001';
Приведенный выше запрос или можно заменить запросом следующего вида: Эффективный: выберитеfrom t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001';
2. Избегайте выбора *
В процессе синтаксического анализа '*' будет преобразовано во все имена столбцов по очереди.Эта работа выполняется путем запроса словаря данных, что означает, что это займет больше времени.
Поэтому вы должны выработать в себе полезную привычку брать все, что вам нужно.
3. Оптимизация заказа по оператору
Любые неиндексированные элементы или вычисляемые выражения в инструкции Order by замедляют выполнение запроса.
Метод: 1. Перепишите порядок по операторам, чтобы использовать индекс;
2.为所使用的列建立另外一个索引
3.绝对避免在order by子句中使用表达式。
4. Оптимизация оператора GROUP BY
Повысьте эффективность операторов GROUP BY, отфильтровав нежелательные записи перед GROUP BY.
Неэффективно:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
ИМЕЕТ РАБОТУ = 'ПРЕЗИДЕНТ'
ИЛИ РАБОТА = 'МЕНЕДЖЕР'
Эффективный:
SELECT JOB , AVG(SAL)
FROM EMP
ГДЕ РАБОТА = 'ПРЕЗИДЕНТ'
ИЛИ РАБОТА = 'МЕНЕДЖЕР'
GROUP by JOB
5. Использование существует вместо в
Часто хорошим выбором является замена in на exists: select num from a where num in(select num from b) Замените следующим утверждением: select num from a where exists(select 1 from b where num=a.num)
6. Используйте varchar/nvarchar вместо char/nchar
Максимально используйте varchar/nvarchar вместо char/nchar, потому что, во-первых, место для хранения полей переменной длины мало, что может сэкономить место для хранения, а во-вторых, для запросов эффективность поиска в относительно небольшом поле явно выше.
7. Если вы можете использовать DISTINCT, вам не нужна GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
Можно изменить на:
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
8. Не используйте UNION, если вы можете использовать UNION ALL
UNION ALL не выполняет функцию SELECT DISTINCT, что уменьшит количество ненужных ресурсов.
9. Используйте большое количество примеров при объединении таблицы и ее индексации.
Если приложение имеет много запросов JOIN, вы должны убедиться, что поля соединения в двух таблицах проиндексированы. Таким образом, MySQL запустит для вас механизм оптимизации оператора Join SQL.
Более того, эти поля, используемые для соединения, должны быть одного типа. Например: если вы соедините поле DECIMAL с полем INT, MySQL не сможет использовать их индексы. Для этих типов STRING вам также необходимо иметь тот же набор символов. (Наборы символов двух таблиц могут отличаться)
Эта статья была разрешена автором для публикации сообщества Tencent Cloud + на различных каналах.
Чтобы узнать больше о свежих технических сухих товарах, вы можете подписаться на наше сообщество Tencent Cloud Technology — официальный аккаунт сообщества Yunjia и аккаунт учреждения Zhihu.