В последнее время возникла необходимость модифицировать существующую структуру хранения, что предполагает рассмотрение условий запросов и эффективности запросов.После прочтения нескольких статей, связанных с индексами и HBase, я вспомнил соответствующие знания в сочетании с требованиями проекта и рассказал о своем собственное понимание и обобщение.
Друзья, кто пропустил, могут просмотреть первые 3 статьи:
- Структура индекса и процесс размещения данных
- Процесс запроса и расширенный запрос
- Детали плана выполнения
В предыдущей статье была подробно описана команда объяснения. С помощью этой команды вы можете определить, на каком этапе возникает проблема с производительностью, а следующим шагом будет ее решение путем оптимизации индекса.
Часть контента взята из статей нескольких блоггеров, и, наконец, будет дана ссылка на статью, чтобы поблагодарить их за прекрасный анализ.
Общие методы оптимизации
Принцип крайнего левого префикса объединенного индекса
Составной индекс следует принципу "крайний левый префикс". В условии запроса поле перед составным индексом используется перед использованием индекса. Если поиск не начат по крайнему левому столбцу индекса, индекс не может использоваться.
Например, если для трех полей (a,b,c) установлен совместный индекс, это может ускорить запрос трех групп a|(a,b)|(a,b,c), но не b|(b, a) Этот порядок запроса.
Кроме того, при построении совместного индекса поле с наибольшей степенью дискриминации находится слева.
Не используйте функции и операции над столбцами
Не используйте функции для столбцов, это приведет к аннулированию индекса для выполнения полного сканирования таблицы.
Например, следующая инструкция SQL:
select * from artile where YEAR(create_time) <= '2018';
Даже если индекс установлен на дату, будет выполнено полное сканирование таблицы, а расчет может быть размещен на бизнес-уровне, что не только экономит ЦП базы данных, но и оптимизирует кэш запросов.
Отрицательный условный запрос не может использовать индекс
К отрицательным условиям относятся: !=, , не в, не существует, не нравится и т. д.
select * from artile where status != 1 and status != 2;
Вы можете использовать in для оптимизации:
select * from artile where status in (0,3)
Используйте покрывающий индекс
Так называемый покрывающий индекс относится к запрашиваемому столбцу, и данные можно получить из индекса вместо перехода к таблице данных через локатор строк, что может значительно повысить производительность.
Можно определить дополнительный столбец для включения в индекс, даже если столбец бесполезен для индекса.
Избегайте принуждения
Когда типы в левой и правой частях условия запроса не совпадают, происходит приведение, которое может привести к сбою индекса и выполнению полного сканирования таблицы.
Если поле телефона имеет тип varchar, следующий SQL не может попасть в индекс:
select * from user where phone=12345678901;
может быть оптимизирован для:
select * from user where phone='12345678901';
Столбцы диапазона могут быть проиндексированы
Условия диапазона: , >=, между и т. д.
Индексы можно использовать для столбцов диапазона, но столбцы после столбца диапазона нельзя индексировать. Индекс можно использовать не более чем для одного столбца диапазона. Если в условии запроса есть два столбца диапазона, индекс нельзя использовать для всех их.
Не рекомендуется строить индексы на полях с частыми обновлениями и низкой дискриминацией данных.
Обновления изменят B+-дерево, а индексация часто обновляемых полей сильно снизит производительность базы данных.
"Пол" — атрибут с небольшой степенью дискриминации. Строить индекс бессмысленно и нельзя эффективно фильтровать данные. Производительность аналогична полному сканированию таблицы.
Дискриминация может быть рассчитана с помощью count(distinct(имя столбца))/count(*), и индекс может быть установлен, когда он превышает 80%.
Столбцы индекса не могут быть нулевыми
Одностолбцовый индекс не хранит нулевые значения, а составной индекс не хранит все нулевые значения.Если столбцу разрешено быть нулевым, может быть получен неожиданный результирующий набор.
Избегайте использования или присоединения к условиям
Вы должны стараться избегать использования или объединения условий в предложении where, потому что это приведет к сбою индекса и выполнению полного сканирования таблицы.Хотя новая версия MySQL может попасть в индекс, оптимизация запросов потребляет больше ресурсов ЦП, чем в.
нечеткий запрос
Ведущие нечеткие запросы не могут использовать индексы, но не опережающие запросы могут.
Случай оптимизации
Используйте ленивую ассоциацию или подзапросы для оптимизации сценариев гиперпагинации.
Вместо того, чтобы пропустить строку смещения, MySQL извлекает строку смещения + N, а затем возвращается, чтобы отбросить первую строку смещения, возвращая N строк.
Когда смещение особенно велико, оно очень неэффективно, либо контролируя общее количество возвращаемых страниц, либо выполняя перезапись SQL для количества страниц, превышающего определенный порог.
Вы можете быстро найти сегмент идентификатора, который необходимо получить, а затем связать:
selecta.* from 表1 a,(select id from 表1 where 条件 limit 1000000 ,10 ) b where a.id=b.id
Если ясно, что возвращается только один результат, ограничение 1 может повысить эффективность.
Хотя он знает, что есть только один результат, база данных этого не знает, поэтому ей явно приказано активно остановить движение курсора.
Как построить индекс
where a=1 and b=1
where b=1
where b=1 order by time desc
Рекомендуется построить два индекса, а именно idx_ab(a,b) и idx_b_time(b,time)
Оптимизатор запросов MySQL автоматически изменит условный порядок предложения where для использования соответствующего индекса.Для первого приведенного выше SQL, если индекс равен idx_ba(b,a), этот индекс также можно использовать.
Сопоставление нескольких значений и сопоставление диапазонов
Если есть совместные индексы (empno, title, fromdate), могут ли следующие SQL использовать индексы, и если да, то сколько?
select * from employee.titles
where emp_no between '10001' and'10010'
and title='软件工程师'
and from_date between '2008-01-01'and '2018-01-01'
Можно использовать индексы, и все три столбца могут быть проиндексированы.Этот SQL, кажется, использует два запроса диапазона, но между действием на empno фактически эквивалентно in, что означает, что empno на самом деле является точным совпадением с несколькими значениями.
Будьте осторожны, чтобы различать сопоставление нескольких значений и сопоставление диапазона в MySQL, иначе это приведет к путанице в поведении MySQL.
Крайний левый принцип соответствия совместного индекса
Если установлен совместный индекс (a, b, c), может ли следующий оператор использовать индекс, и если да, то какие столбцы используются?
where a= 3 // 是，使用了a列
where a= 3 and b = 5 // 是，使用了ab列
where a = 3 and c = 4 and b = 5 // 是，使用了 a，b，c 列
where b= 3 // 否
where a= 3 and c = 4 // 是，使用了a列
where a = 3 and b > 10 and c = 7 // 是，使用了 a，b 列
where a = 3 and b like 'xx%' andc = 7 // 是，使用了 a，b 列
Создать индекс на основе дискриминации
Существует следующий оператор запроса для поиска обзоров указанного продукта, который был рассмотрен (статус = 1):
SELECT user_id,title,content FROM `comment`
WHERE status=1 AND product_id=1
LIMIT 0,5 ;
Можно установить совместный индекс, статус и product_id, но какой из них будет помещен слева, будет вычислять степень дискриминации:
SELECT COUNT(DISTINCT status)/COUNT(*) AS audit_rate,
COUNT(DISTINCT product_id)/COUNT(*) AS product_rate
FROM comment;
Как правило, степень дифференциации продуктов будет выше, и могут быть созданы следующие индексы:
CREATE INDEX idx_productID_Status ON comment(product_id,status)
Сортировать индекс поля
Просмотрите последние 20 записей входа пользователя, отсортированные по времени:
select * from login_history where uid = $uid order by create_time desc limit 20;
Устанавливается составной индекс uid+timeline, в структуру индекса вводится сортировка, и нагрузка на базу данных резко падает.
Справочная статья:
Добро пожаловать, чтобы отсканировать QR-код ниже, обратите внимание на мою личную общедоступную учетную запись WeChat и просмотрите другие статьи ~