Автор любит поговорить
Прошла еще одна напряженная неделя. Самое большое удовольствие на этой неделе — купить небольшой динамик и начать с удовольствием заниматься на фортепиано, когда я закончу работу. Программистам все еще нужно выращивать некоторые артистические бактерии. Ха-ха
На этой неделе я не думал о том, что написать, но в пятницу компания провела внутренний технический шеринг на тему «Объяснение индекса MySQL + лучшие практики». Я выбрал несколько важных и поделился с вами подробнее общие.Конечно, есть и многие моменты знаний об индексации, которые не освещены, и я не буду объяснять их подробно. Изначально я хотел перепечатать статью, чтобы поделиться с вами, но я не добавил оригинальную авторскую WeChat. Если вы заинтересованы, вы можете ответить «Уникальный индекс и обычный индекс». Я рекомендую вам оригинальный адрес.
Теперь вы можете мыть руки, открывать Naviact и практиковаться с менеджером Ваном, усвоение знаний +100%.
Напоминаем: Таблица выписок прилагается в конце статьи.
Индекс прелюдии
Менеджер Ван 🤓: Небо огромно, дикая природа огромна,показательЗапрос очень помогает.
Сяолинь прошептал bb: Менеджер выглядит дураком.
Менеджер Ван: Линь Будун, о чем ты говоришь?
Кобаяши: Нет-нет-нет, я сказал менеджер, хороший талант! Стихотворение написано, тут-тут, правда 👍 быдло разбивает стекло
Менеджер Ван: Считай себя ребенком, сегодня у тебя есть видение. Затем я проверю вас, можете ли вы суммировать преимущества и недостатки индексации?
Сяолинь: (Ган, проверьте меня еще раз) Менеджер, преимущества MySQL, описанные в книге «Высокопроизводительная MySQL», таковы:
- Уменьшает объем данных, которые необходимо сканировать запросу (ускоряет запрос)
- Сокращение операций сортировки на сервере и создание операций с временными таблицами (ускорение таких операций, как группировка и сортировка)
- Измените случайный ввод-вывод сервера на последовательный ввод-вывод (ускорьте запросы)
Что касается недостатков, тоИндекс также является данными и должен храниться, поэтому он займет дополнительное место для хранения. Во-вторых, одновременно с операциями вставки, обновления и удаления необходимо поддерживать индекс, поэтому это потребует дополнительных затрат времени.
Сяомей: Шаг за шагом, ты сегодня такой красивый
Кобаяши: 😎😎😎😎
Менеджер Ван: Сегодня Бу Дун прав, позвольте мне подытожить недостатки индекса.
- Индекс занимает место на диске или в памяти
- Замедляет операции вставки и обновления
Но на самом деле в пределах определенного диапазона данных (в случае, когда индексов не слишком много) стоимость построения индекса намного меньше, чем польза, которую он приносит, но нам все равно нужно предотвращать злоупотребление индексом.
Менеджер Ван: Лин Буденг, почему ты гордишься? Убирай меня со стола! Наш фокус сегодня не здесь, фокус объясняет лучшие практики для индексов MySQL в производстве. Возьмите ноутбук, откройте NaviaCT и получите движение.
Лучшие практики
Необходимые знания: ОБЪЯСНИТЬ
1. Правило самого левого префикса
EXPLAIN SELECT * FROM `employee_information` WHERE age = 23 AND position = "dev"
Для условного столбца WHERE он начинается с самого левого переднего столбца индекса и не пропускает столбцы в индексе.Если он не начинается с самого левого переднего столбца, индекс становится недействительным. Это значит, что если ты брат, то просто убей меня, да? должно бытьВедущий старший брат не может умереть, средний брат не может быть сломан
получено из принципа крайнего левого префиксаПроблема избыточности индекса: В соответствии с принципом крайнего левого индекса нам нетрудно прийти к выводу, что index(a,b) эквивалентен созданию двух индексов index(a), index(a,b). По аналогии index(a,b,c) эквивалентен созданию трех индексов index(a), index(a,b) и index(a,b,c).
Так почему бы не задать себе еще один вопрос? 🎯В чем проблема с избыточностью индекса? Как лучше всего установить несколько индексов для таблицы?(Вы можете обратиться к Ali Java Development Manual или книге «High Performance MySQL»)
2,Ничего не делать с индексным столбцом(вычисление, функция, (автоматическое или ручное) преобразование типов) приведет к сбою индекса и переходу к полному сканированию таблицы.
EXPLAIN SELECT * FROM `employee_information` WHERE LEFT(name,3) = 'bud'
Взяв этот SQL в качестве примера, мне нужно вынимать имена одно за другим, выполнять операции функций, а затем сравнивать их с ограниченными условиями (почками) одно за другим, поэтому нет возможности использовать индексы, только полное сканирование таблицы .
Так будет ли такая ситуация. Выполняла ли MySQL тайно операции над столбцами вашего индекса, когда вы об этом не знали? Привел к сбою нашего индекса.
Например,неявное преобразование?
3. Двигатель хранения не может использовать столбец справа от состояния диапазона в индексе (если столбец промежуточного индекса использует диапазон (>,
EXPLAIN SELECT * FROM `employee_information` WHERE name = 'budongli' AND age > 23 AND position = "dev"
См. type = range, на самом деле используется возрастной индекс (можно наблюдать key_len), только выражение этого индекса является диапазоном, в результате чего нет доступа к позиции индекса.
4. Попробуйте использовать покрывающий индекс (запрос, который обращается только к индексу (столбец индекса содержит столбец запроса)) и уменьшите количество операторов SELECT *.
EXPLAIN SELECT name FROM `employee_information` WHERE name = 'budongli'
Мы используем покрывающий индекс в соответствующей операции SELECT, что с точки зрения непрофессионалаЗапрошенный столбец покрыт индексом, при использовании покрывающего индекса скорость запросов будет очень быстрой, идеальное состояние для оптимизации SQl.
Следует отметить, что когда использование where и использование индекса появляются одновременно, использование where используется только для поиска данных из индекса.Если данные не используются для фильтрации, они используются для чтения, чтобы избежать чтения обратно в таблицу строка данных.
Если мы просто любим использовать SELECT * , мы закончили.
Extra сообщит вам, что столбец запроса не покрывается индексом, а затем отфильтруется для получения данных, требуемых условиями, где,
5. MySQL не может использовать индекс, если он не равен (!= или ), что приводит к полному сканированию таблицы, и равен нулю или равен нулю не будет проходить через индекс
EXPLAIN SELECT * FROM `employee_information` WHERE name != 'budongli'
Этот SQL 🤐, как и Shit, был написан, когда я был стажером.
Старайтесь как можно больше выносить такие суждения на бизнес-уровень, база сильно устает, и не давите на него. Защита базы данных начинается с вас и меня.
6. Подобно тому, как начинается с подстановочного знака ('$abc'), аннулирование индекса MySQL станет операцией полного сканирования таблицы.
Это похоже на поиск в словаре. Например, если вы хотите найти слова, первая буква которых в пиньине — ch, вы можете получить результаты, перелистывая страницы, начинающиеся с ch по порядку. В этом операторе SQL мы хотим найти Имя начинается с «budon», и для его поиска можно использовать порядок индекса, и его можно будет найти в ближайшее время.
EXPLAIN SELECT * FROM `employee_information` WHERE name like 'budon%'
Но если вы хотите проверить слово в словаре на наличие слова AN, вы можете полагаться только на страницу, чтобы закончить весь словарь, чтобы узнать, что нужно.В настоящее время он эквивалентен индексу, может принимать только полный сканирование таблицы.
EXPLAIN SELECT * FROM `employee_information` WHERE name like '%budon%'
7. Используйте или или в экономно
EXPLAIN SELECT * FROM `employee_information` WHERE name = "budongli" or name = "xiaomei"
Оптимизатор запросов MySQL — очень сложный компонент, использующий множество стратегий оптимизации для создания оптимального плана выполнения: При использовании or или in MySQL не обязательно использует индекс.Внутренний оптимизатор MySQL будет варьироваться в зависимости от количества страниц в каждой таблице или индексе, кардинальности индекса, длины индекса и строк данных и распределения. индекса., девяносто на девяносто и другие факторы, чтобы оценить, использовать ли индекс в целом. Существует множество причин, по которым MySQL выбирает неправильный план выполнения, например неточная статистика, не учитывающая стоимость операций, которые не находятся под ее контролем (пользовательские функции, хранимые процедуры).
В конце концов, то, что MySQL считает оптимальным, не является тем, что мы думаем (мы хотим, чтобы время выполнения было как можно короче, но значение MySQL выбирает, что считает, что стоимость мала, но малая стоимость не означает короткое время выполнения) и так далее.
Итак, внутренний оптимизатор MySQL иногда бывает идиотом и, скорее всего, выберет не тот. Конечно, если вы думаете, что при использовании фиксированного индекса этот оператор SQL будет выполняться быстро, не будьте вежливы.FORCE INDEXБитва с оптимизатором
проблемное время
Если поле в таблице может быть построено как с уникальным индексом, так и с обычным индексом, предполагая, что вы являетесь администратором баз данных компании, с точки зрения производительности, что бы вы выбрали? Что лежит в основе отбора?
(Рекомендуется, чтобы друзья взяли на себя инициативу подумать, подумать и попрактиковаться, конечно, официальный аккаунт также подготовил для вас более подробный обмен, вы можете ответить в официальном аккаунте "Уникальные и обычные индексы, которым я с вами поделюсь)
оператор создания таблицы
-- ----------------------------
-- Table structure for employee_information
-- ----------------------------
DROP TABLE IF EXISTS `employee_information`;
CREATE TABLE `employee_information` (
`id` int(11) NOT NULL,
`name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`age` int(11) NOT NULL,
`position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `hybrid_index`(`name`, `age`, `position`) USING BTREE,
INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of employee_information
-- ----------------------------
INSERT INTO `employee_information` VALUES (1, 'budongli', 23, 'dev', '2020-06-12 22:21:24');
INSERT INTO `employee_information` VALUES (2, 'xiaomei', 22, 'dev', '2020-05-06 22:22:10');
INSERT INTO `employee_information` VALUES (3, 'jingliwang', 30, 'manager', '2018-06-01 22:22:37');
SET FOREIGN_KEY_CHECKS = 1;
В этой статье используетсяmdniceнабор текста