"Не знать важности индексов только тогда, когда в библиотеке больше данных, и не знать важности соответствующих индексов, когда в библиотеке больше данных. Зная советы из этой статьи, вы сможете создать эффективные и подходящие индексы и избежать написания медленного SQL."
1. Старайтесь не использовать выражения при использовании столбцов индекса для запроса и поместите расчет на бизнес-уровень, а не на уровень базы данных.
Как показано на рисунке ниже, результаты двух SQL-запросов одинаковы, но планы выполнения двух SQL-запросов различны.Эффективность индекса в типе намного меньше, чем у выражения act_id+4 в const где условие, влияющее на выполнение плана.Что касается значения типа, см.объяснить подробное объяснение
2. Попробуйте использовать запрос первичного ключа вместо других индексов. Запрос первичного ключа не будет отображаться в запросе таблицы.
Все наши таблицы в основном имеют первичный ключ, поэтому при нормальной разработке мы можем использовать индекс, если можем использовать индекс, и мы можем использовать индекс первичного ключа, если можем.
3. Используйте индекс префикса
Во многих случаях наши индексы на самом деле являются строками, и появление длинных строк неизбежно, что приведет к тому, что индекс займет слишком много места и снизит его эффективность. Особенно для длинных столбцов, таких как blob, text, varchar. В настоящее время метод обработки заключается в том, чтобы не использовать полное значение поля в качестве индекса, а брать только его первую половину (селективность выбранного префиксного индекса близка ко всему столбцу). Это может значительно сократить пространство индекса, тем самым повысив эффективность, но недостатком является снижение избирательности индекса.
Избирательность индекса: отношение уникальных значений индекса к общему количеству записей в таблице данных (#T) в диапазоне от 1/#T до 1. Чем выше селективность индекса, тем выше эффективность запросов, поскольку данные очень избирательны, и можно отфильтровать больше строк. Селективность уникального индекса равна 1, и его производительность тоже лучшая.
Например, в поле почтового ящика таблицы сотрудников компании суффикс почтового ящика компании совпадает сxxxx@qq.com, На самом деле, использование почтового ящика в качестве индекса допустимо для части xxxx, потому что @qq.com это то же самое, он не имеет смысла для индекса, очевидно, что в качестве индекса используется только xxxx, и его избирательность такая же, как у целое значение, но xxxx используется в качестве индекса, очевидно, уменьшит пространство индекса.
Ниже у нас есть таблица сотрудников в качестве примера (см. Конец статьи о структуре таблицы и данных)
В качестве примера возьмем индексирование поля электронной почты:
Адрес электронной почты этих данных на самом деле является номером мобильного телефона.+@qq.comНапример, на самом деле первые 11 цифр после этого совпадают. Я использую следующий sql, чтобы увидеть избирательность этих данных (взять первые 10, 11, 12 соответственно) битового расчета.
-- 当是11个前缀的时候选择性是1，在增加字段长度，选择性也不会变化select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11, count(distinctleft(email,12))/count(*) as e12 from employee;
Из приведенного выше рисунка видно, что селективность топ-10, топ-11 и топ-12 составляет 0,14, 1,0 и 1,0 соответственно.На 11-й позиции индекс селективности равен наивысшему 1, поэтому нет нужно использовать их все как индексы, а индекс увеличивается.пробел.
-- 创建前缀索引
alter table employee add key(email(11));
Мы также можем использовать count для вычисления частоты для подсчета (чем меньше количество вхождений, тем ниже частота повторения и выше избирательность).
-- 查找前缀出现的频率
select count(*) as cnt,left(email,11) as pref from employee group by pref order by cnt desc limit 10;
4. Используйте индексное сканирование для сортировки
У нас часто есть требования к сортировке. Мы используем порядок, но порядок влияет на производительность. Он сортирует, загружая данные в память. Если объем данных не может быть сохранен в памяти, он может быть обработан только несколько раз. Однако сам индекс упорядочен, и сортировку проще выполнить непосредственно через индекс.
Сканирование самого индекса выполняется быстро, потому что ему нужно только перейти от одной записи индекса к следующей записи, но если индекс не может покрыть все столбцы, требуемые запросом, вам придется возвращаться к запросу таблицы каждый раз, когда запись индекса сканируется соответствующая строка, это в основном случайный ввод-вывод. Таким образом, чтение данных в индексном порядке обычно выполняется медленнее, чем последовательное полное сканирование таблицы.
MySQL может использовать один и тот же индекс для сортировки и поиска строк. Рассмотрите возможность создания такого индекса, если это возможно.
Только когда порядок столбцов индекса и предложение order by точно такие же, а направление сортировки (обратное или прямое) всех столбцов одинаково, mysql может использовать индекс для сортировки результатов.. Если запросу необходимо связать несколько таблиц, сортировку по индексу можно использовать только в том случае, если все поля предложения order by относятся к первой таблице. Порядок по запросу также должен удовлетворять крайнему левому префиксу составного индекса, иначе сортировка по индексу не может быть использована.
На самом деле есть два основных момента, на которые стоит обратить внимание при разработке:
- Поля в условии where и поля в порядке могут быть составными индексами и удовлетворять самому левому префиксу.
- Порядок полей в порядке следования должен быть последовательным, и не может быть desc или asc.
5. Можно использовать Union all, in или index, но рекомендуется использовать in
Как и выше, union all будет выполняться дважды, а in и или только один раз. При этом видно, что план выполнения or и in одинаков, \
Но мы смотрим на время их выполнения. Используйте, как показано нижеset profiling=1Вы можете увидеть подробное время, использоватьshow profilesПроверьте конкретное время. На следующем рисунке показано, что время or равно 0,00612000, а время in равно 0,00022800. Разрыв по-прежнему очень велик (данные тестовой таблицы содержат всего 200 строк).
union all: Запрос разделен на два этапа.На самом деле, есть еще и union.Рекомендуется использовать union all, когда в обычной разработке необходимо использовать union, т.к. в объединении есть дополнительный шаг отдельной дедупликации. Так что попробуйте использовать union all.
6. Столбцы диапазона можно индексировать
Условия диапазона: >,>=,
Столбцы диапазона можно индексировать, но столбцы, следующие за столбцом диапазона, индексировать нельзя (индексы используются не более чем для одного столбца диапазона).
Например, составной индекс age+name, если условие запросаwhere age>18 and name="纪"Следующее имя является неиспользуемым индексом.
Однажды меня спросили в интервью, могу ли я перейти к определенному индексу. Я не обратил на это внимания и не ответил на него успешно. В этот раз я провел эксперимент сам. Заключение, пожалуйста, смотрите в конце статья.
7. Принуждение выполнит полное сканирование таблицы
Я определил в таблице сотрудниковmobileПоля имеют тип varchar и индексируются, я запрашиваю числа и строки соответственно.
Посмотрите на результат: два типа разные, и для индексации используются только строки.
Если тип значения условия несовместим с типом, определенным в таблице, mysql принудительно преобразует тип, но в результате индекс не будет использоваться.При разработке индекса нам нужно ввести соответствующее значение типа в соответствии с типом, определенным нами.
8. Степень различения данных невысока, и не рекомендуется создавать индекс для часто обновляемых полей.
- Обновления столбцов индекса изменят B+-дерево, а частые обновления значительно снизят производительность базы данных.
- Подобно полу (только мужчина и женщина или неизвестно), данные не могут быть эффективно отфильтрованы.
- Как правило, индекс может быть установлен, когда степень различения выше 80%, а степень различения может использовать count(distinct(имя столбца))/count(*)
9. Столбец для создания индекса не может быть нулевым, и вы можете получить неожиданные результаты.
То есть старайтесь не ставить индексируемое поле пустым, могут быть какие-то непредвиденные проблемы, но маловероятно, что в реальной работе оно не пустое, так что относитесь к нему по фактическому делу, и старайтесь избегать такой ситуации .
10. Когда требуется объединение таблиц, лучше не превышать трех таблиц.
Объединение таблиц на самом деле представляет собой вложенное циклическое сопоставление нескольких таблиц, что влияет на производительность, а типы данных полей, которые необходимо объединить, должны быть согласованы для повышения эффективности запросов. Позже напишем специальную статью о принципе объединения таблиц.
11. Используйте лимит как можно чаще, когда вы можете его использовать.
Роль ограничения заключается не только в использовании подкачки, но и в ограничении вывода.
limit фактически просматривает данные запроса один за другим, если для добавления требуется только один фрагмент данныхlimit 1limit, то указатель индекса остановится после того, как найдет данные, соответствующие условиям, и не будет продолжать оценку вниз, а вернется напрямую. Если нет предела, он будет продолжать судить.
Но если пейджинг занимает 5 из 10 000 записейlimit 10000,10005Нужно быть осторожным, он пройдет 10 000 и вынесет 5, что очень неэффективно. Совет: Если первичный ключ является последовательным, вы можете получить данные с разбивкой на страницы непосредственно через первичный ключ.
12. Индекс одной таблицы должен контролироваться в пределах 5, насколько это возможно.
Создание/обслуживание индексов также требует больших затрат и занимает место. Индексов не так много, как возможно, и индексы следует использовать разумно.
13. Количество полей в одном составном индексе не должно превышать 5
Чем больше полей, тем больше индекс и тем больше места для хранения он занимает.
Чем больше индексов, тем лучше, и нет необходимости проектировать все индексы при построении таблицы. Преждевременная оптимизация не будет эффективным индексом. Необходимо понимать бизнес и делать статистические компромиссы в соответствии с соответствующим бизнесом. sql, прежде чем идти. Создайте соответствующие индексы, чтобы рассмотрение было более всесторонним, а установленные индексы были более эффективными и действенными.
Выше приведены небольшие детали соответствующей оптимизации индекса, я надеюсь помочь вам написать swish sql.
Пополнить
К вопросу о том, равно ли переходить на индекс
в заключении:Пойдет только первичный ключ, не пойдет ни уникальный ключ, ни обычный индекс.
Я создал уникальный индекс в таблице сотрудниковemployee_numи совместный индексemployee_num+name, результатом является реализация следующего рисунка.
структура таблицы сотрудников
CREATE TABLE `employee` (
`employee_id` bigint(20) NOT NULL AUTO_INCREMENT, `employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工编码',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',
`email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件',
`mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '移动电话',
`gender` tinyint(1) NOT NULL COMMENT '性别, 0: 男 1: 女',
PRIMARY KEY (`employee_id`) USING BTREE,
INDEX `email`(`email`(11)) USING BTREE,
INDEX `employee_u1`(`employee_num`, `name`) USING BTREE,
UNIQUE INDEX `employee_u2`(`employee_num`) USING BTREE,
INDEX `employee_u3`(`mobile`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;
Данные о работнике следующие:
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (10, '001', '员工A', '15500000001@qq.com', '15500000001', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (11, '002', '员工B', '15500000002@qq.com', '15500000002', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (12, '003', '员工C', '15500000003@qq.com', '15500000003', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (13, '004', '员工D', '15500000004@qq.com', '15500000004', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (14, '005', '员工E', '15500000005@qq.com', '15500000005', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (15, '006', '员工F', '15500000006@qq.com', '15500000006', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (16, '007', '员工G', '15500000007@qq.com', '15500000007', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (17, '008', '员工H', '15500000008@qq.com', '15500000008', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (18, '009', '员工I', '15500000009@qq.com', '15500000009', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (19, '010', '员工J', '15500000010@qq.com', '15500000010', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (20, '011', '员工K', '15500000011@qq.com', '15500000011', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (21, '012', '员工L', '15500000012@qq.com', '15500000012', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (22, '013', '员工M', '15500000013@qq.com', '15500000013', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (23, '014', '员工N', '15500000014@qq.com', '15500000014', 1);