Есть чувства, есть галантерейные товары, поиск в WeChat【Третий принц Ао Бин] Обратите внимание на этого другого программиста.
эта статьяGitHub github.com/JavaFamilyВключено, и есть полные тестовые площадки, материалы и мой цикл статей для интервью с производителями первой линии.
предисловие
Теперь, когда серия баз данных была обновлена, я думаю, что у всех есть общее представление обо всех концепциях Когда я читал комментарии на этой неделе, я нашел вопрос от пользователя сети, который показался мне очень интересным: Как Шуай Бинг разрабатывает индекс ? Как вы все разработали индекс? Как проектировать эффективнее?
Когда я думаю об индексе, я написал много.Не имеет смысла, чтобы читатель не знал об этом, но когда я оглядываюсь назад и читаю, это правда, что я написал концепцию индексации, ее преимущества и недостатки. , и я не упомянул, как его оформить.Потом в этой статье это случилось снова.
В этой статье по-прежнему будет много повторяющихся концепций, написанных ранее, но она также предназначена для того, чтобы каждый мог лучше понять принципы нескольких структур индексов в MySQL.
текст
Мы знаем, что индекс представляет собой древовидную структуру, основанную на связанном списке, который может быстро извлекать данные.В настоящее время почти все базы данных СУБД реализуют функции индекса, такие как индекс B+Tree MySQL, индекс BTree MongoDB и т. д.
В процессе развития бизнеса от того, эффективен ли дизайн индекса, зависит эффективность выполнения соответствующего SQL интерфейса. Эффективный индекс может сократить время отклика интерфейса и одновременно снизить стоимость. Наша реалистичная цель это: дизайн индекса -> уменьшить время отклика интерфейса -> уменьшить конфигурацию сервера -> снизить стоимость и, наконец, реализовать стоимость,Потому что босс больше всего беспокоится о стоимости.
Сегодня я расскажу вам об индексах в MySQL и о том, как проектировать индексы.Только с помощью индексов мы можем улучшить RT интерфейса и улучшить физический осмотр пользователя.
Индексы в MySQL
Движок InnoDB в MySQL использует структуру B+Tree для хранения индексов, что позволяет минимизировать количество дисковых операций ввода-вывода при запросе данных.В то же время высота дерева напрямую влияет на производительность запроса.Как правило, высота дерева дерево поддерживается на уровне 3-4 слоев.
B+Tree состоит из трех частей: root root, branch branch и Leaf leaf.Корень и ветвь не хранят данные, а хранят только адреса указателей.Все данные хранятся в Leaf Node.В то же время Leaf Nodes связаны двусвязным списком, структура которого следующая:
Как видно из вышеизложенного, каждый конечный узел состоит из трех частей, а именно указателя-предшественника p_prev, данных данных и указателя-последователя p_next, причем данные данных упорядочены, по умолчанию это восходящий ASC, значение ключа распределено по правая сторона B+дерева всегда больше, чем левая, и расстояние от корня до каждого листа одинаково, то есть количество операций ввода-вывода, необходимых для доступа к любому конечному узлу, одинаково, то есть высота уровень дерева индексов + 1 операция ввода-вывода.
Мы можем думать об индексе в MySQL как о небольшой таблице, которая занимает место на диске.Процесс создания индекса на самом деле является процессом сортировки по столбцу индекса.Сначала сортируйте по размеру_буфера_сортировки.Файл отсортирован, и наиболее важно то, что операции сортировки (различные, группировать, упорядочивать) можно избежать с помощью индексации.
кластеризованный индекс
Таблица в MySQL — это IOT (Index Organization Table), данные хранятся в порядке идентификатора первичного ключа (логически непрерывный, физически прерывистый), а идентификатор первичного ключа — это кластеризованный индекс (clustered index), в котором хранится весь строка данных, если указанный первичный ключ не отображается, MySQL объединит все столбцы для создания row_id в качестве первичного ключа, например таблицы users(id, user_id, user_name, phone, primary key(id)), id кластеризованный индекс, в котором хранятся id, user_id, user_name, телефонные данные всей строки.
Вторичный индекс
Вспомогательные индексы также называются вторичными индексами. Помимо хранения столбцов индекса, индекс также хранит идентификатор первичного ключа. Для индекса idx_user_name(user_name) для user_name он фактически эквивалентен idx_user_name(user_name, id), и MySQL автоматически Идентификатор первичного ключа добавляется в конец индекса. Те, кто знаком с базой данных Oracle, знают, что в дополнение к столбцу индекса индекс также хранит row_id (представляющий физическое расположение данных, состоящий из четырех частей: номер объекта + номер файла данных + номер блока данных + номер строки данных), мы также можем отобразить добавление идентификатора первичного ключа при создании вторичного индекса.
-- 创建user_name列上的索引
mysql> create index idx_user_name on users(user_name);
-- 显示添加主键id创建索引
mysql> create index idx_user_name_id on users(user_name,id);
-- 对比两个索引的统计数据
mysql> select a.space as tbl_spaceid, a.table_id, a.name as table_name, row_format, space_type, b.index_id , b.name as index_name, n_fields, page_no, b.type as index_type from information_schema.INNODB_TABLES a left join information_schema.INNODB_INDEXES b on a.table_id =b.table_id where a.name = 'test/users';
+-------------+----------+------------+------------+------------+----------+------------------+----------+------
| tbl_spaceid | table_id | table_name | row_format | space_type | index_id | index_name | n_fields | page_no | index_type |
+-------------+----------+------------+------------+------------+----------+------------------+----------+------
| 518 | 1586 | test/users | Dynamic | Single | 1254 | PRIMARY | 9 | 4 | 3 |
| 518 | 1586 | test/users | Dynamic | Single | 4003 | idx_user_name | 2 | 5 | 0 |
| 518 | 1586 | test/users | Dynamic | Single | 4004 | idx_user_name_id | 2 | 45 | 0 |
mysql> select index_name, last_update, stat_name, stat_value, stat_description from mysql.innodb_index_stats where index_name in ('idx_user_name','idx_user_name_id');
+------------------+---------------------+--------------+------------+-----------------------------------+
| index_name | last_update | stat_name | stat_value | stat_description |
+------------------+---------------------+--------------+------------+-----------------------------------+
| idx_user_name | 2021-01-02 17:14:48 | n_leaf_pages | 1358 | Number of leaf pages in the index |
| idx_user_name | 2021-01-02 17:14:48 | size | 1572 | Number of pages in the index |
| idx_user_name_id | 2021-01-02 17:14:48 | n_leaf_pages | 1358 | Number of leaf pages in the index |
| idx_user_name_id | 2021-01-02 17:14:48 | size | 1572 | Number of pages in the index |
Сравнивая результаты двух индексов, n_fields представляет количество столбцов в индексе, n_leaf_pages представляет количество конечных страниц в индексе, а размер представляет общее количество страниц в индексе.Из сравнения данных видно, что вторичный индекс действительно содержит первичный ключ.id, также показывает, что два индекса абсолютно одинаковы.
| Index_name | n_fields | n_leaf_pages | size |
|---|---|---|---|
| idx_user_name | 2 | 1358 | 1572 |
| idx_user_name_id | 2 | 1358 | 1572 |
индексная задняя таблица
Вышеприведенное доказывает, что вспомогательный индекс содержит идентификатор первичного ключа. Если данные фильтруются через столбец вспомогательного индекса, может потребоваться вернуть таблицу. Например, бизнесу необходимо запросить информацию о пользователях пользовательской таблицы через имя пользователя user_name и соответствующий SQL бизнес-интерфейса:
select user_id, user_name, phone from users where user_name = 'Laaa';
Мы знаем, что для индекса idx_user_name на самом деле это небольшая таблица idx_user_name(user_name, id). Если вы запрашиваете только столбцы в индексе, вам нужно только просмотреть индекс, чтобы получить необходимые данные, и вам не нужно чтобы вернуться к таблице.Следующее выражение SQL:
SQL 1: select id, user_name from users where user_name = 'Laaa';
SQL 2: select id from users where user_name = 'Laaa';
mysql> explain select id, name from users where name = 'Laaa';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| 1 | SIMPLE | users | NULL | ref | idx_user_name | idx_user_name | 82 | const | 1 | 100.00 | Using index |
mysql> explain select id from users where name = 'Laaa';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| 1 | SIMPLE | users | NULL | ref | idx_user_name | idx_user_name | 82 | const | 1 | 100.00 | Using index |
Extra= в плане выполнения SQL 1 и SQL 2Using indexУказывает, что используется сканирование покрывающего индекса, нет необходимости возвращаться к таблице, а затем смотреть на приведенный выше бизнес-SQL:
select user_id, user_name, phone from users where user_name = 'Laaa';
Вы можете видеть, что столбцы user_id и phone за select не входят в индекс idx_user_name, поэтому вам необходимо выполнить поиск в таблице по идентификатору первичного ключа.MySQL обрабатывается в следующие два этапа:
Section 1:select **id** from users where user_name = 'Laaa' //id = 100101
Section 2: select user_id, user_name, phone from users where id = 100101;
будетSection 2Операция называется возвратом к таблице, то есть поиском данных в исходной таблице по id первичного ключа во вспомогательном индексе.
индекс высоты
Индекс MySQL представляет собой структуру B+tree.Даже если в таблице сотни миллионов данных, высота индекса не будет очень большой.Обычно поддерживается около 3-4 слоев.Позвольте мне рассчитать высоту индекс idx_name и узнать информацию об индексе из приведенного выше.
$hexdump -s 81984 -n 10 /usr/local/var/mysql/test/users.ibd
0014040 00 02 00 00 00 00 00 00 0f a3
001404a
PAGE_LEVEL индекса равен 00, то есть высота индекса idx_user_name равна 1, а 0f a3 представляет номер индекса.В десятичном виде это 4003, что является index_id.
Метод сканирования данных
полное сканирование таблицы
Просканируйте все B+Tree слева направо, чтобы получить данные, и просканируйте все данные таблицы Накладные расходы ввода-вывода велики, скорость низкая, а блокировка серьезная, что влияет на параллелизм MySQL.
Для бизнес-сценариев OLAP необходимо сканировать и возвращать большой объем данных, в этом случае эффективность последовательного ввода-вывода при полном сканировании таблицы выше.
сканирование индекса
Вообще говоря, индекс меньше, чем таблица, объем сканируемых данных невелик, потребление операций ввода-вывода невелико, скорость выполнения блочная, почти нет блокировок и т. д., что может улучшить параллелизм MySQL.
Для систем OLTP всегда приятно надеяться, что все SQL попадут в соответствующие индексы.
Основное отличие заключается в размере отсканированных данных и операции ввода-вывода. Полное сканирование таблицы — это последовательный ввод-вывод, а сканирование индекса — случайный ввод-вывод. MySQL оптимизировал это и добавил функцию буфера изменений для повышения производительности ввода-вывода.
Случай оптимизации индекса
Оптимизация запросов страницы
Бизнес должен запрашивать записи транзакций в соответствии с диапазоном времени.Исходный SQL интерфейса выглядит следующим образом:
select * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;
В таблице trade_info есть индекс idx_status_create_time(status,create_time).Судя по приведенному выше анализу, он эквивалентен индексу **(status,create_time,id)**.Для типичного лимита подкачки m, n, чем больше вы поворачиваете страница, тем более Медленная, то есть чем больше m, тем медленнее она будет, потому что все больше и больше данных необходимо сканировать, чтобы найти позицию m, что приводит к относительно большим накладным расходам ввода-вывода.Здесь вы можете использовать сканирование покрытия вспомогательного индекса для оптимизации, сначала получите идентификатор, и этот шаг является сканированием перезаписи индекса, нет необходимости возвращаться к таблице, а затем связать с исходной таблицей trade_info через идентификатор, переписанный SQL выглядит следующим образом:
select * from trade_info a ,
(select id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b //这一步走的是索引覆盖扫描，不需要回表
where a.id = b.id;
Многие студенты знают только, что эффективно писать таким образом, но они могут не знать, почему их нужно переписывать таким образом.Понимание особенностей индексации особенно важно для написания высококачественного SQL.
Разделяй и властвуй всегда хорошо
В маркетинговой системе есть пакет купонов с истекшим сроком действия, которые необходимо аннулировать.Основной SQL выглядит следующим образом:
-- 需要更新的数据量500w
update coupons set status = 1 where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';
Обновление данных 500 Вт в Oracle происходит очень быстро, потому что для его выполнения может использоваться несколько ядер ЦП, но MySQL требует внимания.Один SQL может быть обработан только одним ядром ЦП.Если SQL сложный или выполняется медленно, он будет заблокирован. Следующий запрос SQL вызвал резкое увеличение количества активных подключений, ЦП MySQL на 100%, соответствующего тайм-аута интерфейса, и в то же время для архитектуры репликации master-slave, а также было выполнено разделение операций чтения и записи, на обновление данных 500w ушло 5 минут, а на выполнение на мастере ушло 5 минут., бинлог тоже нужно выполнять 5 минут после его передачи на слейв, то есть слейв задерживается на 5 минут. В течение этого периода это приведет к грязным бизнес-данным, таким как повторные заказы.
Идея оптимизации: сначала получите минимальный идентификатор и максимальный идентификатор в условии where, а затем обновите пакетами, каждый пакет из 1000, что может не только быстро завершить обновление, но и гарантировать отсутствие задержки в репликации master-slave.
Оптимизация выглядит следующим образом:
- Сначала получите минимальный идентификатор и максимальный идентификатор в диапазоне данных, который необходимо обновить (в таблице нет физического удаления, поэтому идентификатор непрерывен)
mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
| 1 | SIMPLE | users | NULL | range | idx_status_create_time | idx_status_create_time | 6 | NULL | 180300 | 100.00 | Using where; Using index |
Extra=Using where; При использовании индекса используется индекс idx_status_create_time, и необходимые данные можно найти в индексе, поэтому нет необходимости возвращать таблицу для запроса данных.
- Циклическое обновление выполняется каждые 1000 коммитов, основной код выглядит следующим образом:
current_id = min_id;
for current_id < max_id do
update coupons set status = 1 where id >=current_id and id <= current_id + 1000; //通过主键id更新1000条很快
commit;
current_id += 1000;
done
Эти два случая говорят нам о том, что для полного использования функции, заключающейся в том, что вторичный индекс содержит идентификатор первичного ключа, эффективно сначала получить идентификатор первичного ключа через индекс и выполнить сканирование покрывающего индекса, не возвращая таблицу, а затем использовать идентификатор для деассоциации операции.В то же время, согласно характеристикам MySQL, использование идеи «разделяй и властвуй» может не только эффективно завершить операцию, но и избежать путаницы бизнес-данных, вызванной задержкой репликация master-slave.
Дизайн индекса MySQL
После того, как вы ознакомитесь с функциями индексов, вы сможете разрабатывать высококачественные индексы в процессе развития бизнеса, чтобы сократить время отклика интерфейсов.
индекс префикса
Для таблиц InnoDB, использующих формат REDUNDANT или COMPACT, длина префикса ключа индекса ограничена 767 байтами. Это ограничение может быть достигнуто, если индекс префикса столбца столбца TEXT или VARCHAR превышает 191 символ, предполагая набор символов utf8mb4, до 4 байтов на символ.
Вы можете включить или отключить ограничение на длину префикса индекса, установив параметр innodb_large_prefix, то есть, если он установлен в OFF, хотя индекс может быть создан успешно, будет предупреждение, в основном потому, что размер индекса будет очень большой, и эффективность большого количества операций ввода-вывода, даже если оптимизатор MySQL попадет в этот показатель, и эффективность будет не очень высокой.
-- 设置innodb_large_prefix=OFF禁用索引前缀限制，虽然可以创建成功，但是有警告。
mysql> create index idx_nickname on users(nickname); // `nickname` varchar(255)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
На ранней стадии развития бизнеса, чтобы быстро реализовывать функции, определения длины некоторых полей таблицы данных являются относительно свободными. интерфейсы, которые необходимо запрашивать по псевдониму.После того, как система работает в течение определенного периода времени, максимальная длина псевдонима для запроса таблицы пользователей составляет 30. В это время можно создать индекс префикса, чтобы уменьшить длину индекса и улучшить представление.
-- `nickname` varchar(128) DEFAULT NULL定义的执行计划
mysql> explain select * from users where nickname = 'Laaa';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+--------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+--------
| 1 | SIMPLE | users | NULL | ref | idx_nickname | idx_nickname | 515 | const | 1 | 100.00 | NULL |
key_len=515, так как таблица и столбец имеют набор символов utf8mb4, каждый символ занимает 4 байта, тип данных переменной длины +2 байта, допускается NULL дополнительно +1 байт, то есть 128 x 4 + 2 + 1 = 515 байт. Создайте индекс префикса. Длина префикса не может быть максимальным значением столбца данных текущей таблицы. Это должна быть длина части с наивысшей степенью дискриминации. Как правило, она может достигать более 90%. Например , хранилище полей электронной почты аналогично этому значению.xxxx@yyy.com, максимальная длина индекса префикса может быть максимальной длиной части xxxx.
-- 创建前缀索引，前缀长度为30
mysql> create index idx_nickname_part on users(nickname(30));
-- 查看执行计划
mysql> explain select * from users where nickname = 'Laaa';
+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-
| 1 | SIMPLE | users | NULL | ref | idx_nickname_part,idx_nickname | idx_nickname_part | 123 | const | 1 | 100.00 | Using where |
Видно, что оптимизатор выбрал префиксный индекс, а длина индекса равна 123, то есть 30 х 4 + 2 + 1 = 123 байта, что меньше четверти исходного размера.
Префиксная индексация уменьшает размер индекса, но не устраняет сортировку.
mysql> explain select gender,count(*) from users where nickname like 'User100%' group by nickname limit 10;
+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----
| 1 | SIMPLE | users | NULL | range | idx_nickname_part,idx_nickname | idx_nickname | 515 | NULL | 899 | 100.00 | Using index condition |
--可以看到Extra= Using index condition表示使用了索引，但是需要回表查询数据，没有发生排序操作。
mysql> explain select gender,count(*) from users where nickname like 'User100%' group by nickname limit 10;
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------
| 1 | SIMPLE | users | NULL | range | idx_nickname_part | idx_nickname_part | 123 | NULL | 899 | 100.00 | Using where; Using temporary |
--可以看到Extra= Using where; Using temporaryn表示在使用了索引的情况下，需要回表去查询所需的数据，同时发生了排序操作。
составной индекс
Если индекс с одним столбцом не может хорошо фильтровать данные, вы можете объединить другие поля в условии where, чтобы создать составной индекс для лучшей фильтрации данных и сокращения количества сканирований ввода-вывода.Например, бизнесу необходимо запрашивать записи транзакций по периоду времени, со следующим SQL:
select * from trade_info where status = 1 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';
На основе опыта разработки студентами предыдущих составных индексов:Столбец с хорошей мультиселективностью уникальных значений используется в качестве ведущего столбца составного индекса., так что эффективно создавать составной индекс idx_create_time_status, потому что create_time — это значение в секунду, есть много уникальных значений, и селективность очень хорошая, а статус имеет только 6 дискретных значений, поэтому я думаю, что создать не проблема этим способом,Но этот опыт подходит только для фильтрации по условию равенства, а не для фильтрации по условию диапазона.Например, idx_user_id_status(user_id, status) не представляет проблемы, но он не подходит для составных индексов, содержащих диапазон create_time. Давайте посмотрим на разницу между этими двумя разными порядками индексов, а именно idx_status_create_time и idx_create_time_status.
-- 分别创建两种不同的复合索引
mysql> create index idx_status_create_time on trade_info(status, create_time);
mysql> create index idx_create_time_status on trade_info(create_time,status);
-- 查看SQL的执行计划
mysql> explain select * from users where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';
+----+-------------+-------+------------+-------+-----------------------------------------------+---------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------------------------------+---------------
| 1 | SIMPLE | trade_info | NULL | range | idx_status_create_time,idx_create_time_status | idx_status_create_time | 6 | NULL | 98518 | 100.00 | Using index condition |
Как видно из плана выполнения, есть два вида составных индексов в разном порядке.Оптимизатор MySQL выбирает индекс idx_status_create_time, так почему бы не выбрать idx_create_time_status, мы используем Optimizer_trace для отслеживания выбора оптимизатора.
-- 开启optimizer_trace跟踪
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
-- 执行SQL语句
mysql> select * from trade_info where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';
-- 查看跟踪结果
mysql>SELECT trace FROM information_schema.OPTIMIZER_TRACE\G;
Сравните статистику следующих двух индексов следующим образом:
| составной индекс | Type | Rows | Участвовать в фильтрации столбцов индекса | Chosen | Cause |
|---|---|---|---|---|---|
| idx_status_create_time | Index Range Scan | 98518 | status AND create_time | True | бюджетный |
| idx_create_time_status | Index Range Scan | 98518 | create_time | False | Высокая стоимость |
Оптимизатор MySQL основан на стоимости. COST в основном включает IO_COST и CPU_COST. CBO MySQL (оптимизатор на основе затрат) всегда выбирает наименьшую стоимость в качестве окончательного плана выполнения для выполнения. Из приведенного выше анализа CBO выбирает составной индекс это idx_status_create_time, потому что и статус, и create_time в индексе могут участвовать в фильтрации данных, а стоимость низкая, а idx_create_time_status имеет фильтрацию данных только по параметру create_time, а статус игнорируется, на самом деле CBO упрощает его до одноколоночного индекса idx_create_time , который является выборочным. Нет составного индекса idx_status_create_time.
Принципы составления составного индекса
- Поместите столбцы запроса диапазона в конец составного индекса, например idx_status_create_time.
- Чем выше частота фильтрации столбцов, тем лучше избирательность.Он должен использоваться как ведущий столбец составного индекса, который подходит для поиска по равным значениям, например idx_user_id_status.
Эти два принципа не противоречат друг другу, а дополняют друг друга.
пропустить индекс
При нормальных обстоятельствах, если таблица users имеет составной индекс idx_status_create_time, все мы знаем, что оптимизатор MySQL не использует индекс для запросов только с create_time, поэтому необходимо создать одностолбцовый индекс idx_create_time. Учащиеся, которые использовали Oracle, знают, что сканирование с пропуском индекса возможно.В MySQL 8.0 также реализовано сканирование с пропуском индекса, аналогичное Oracle.Вы также можете увидеть skip_scan=on в опции оптимизатора.
| optimizer_switch |use_invisible_indexes=off,skip_scan=on,hash_join=on |
Это подходит для случая, когда ведущий столбец составного индекса имеет мало уникальных значений, а замыкающий столбец имеет много уникальных значений.Если уникальное значение ведущего столбца увеличивается, MySQL CBO не будет выбирать сканирование с пропуском индекса, которое зависит от разделения данных столбца индекса.
mysql> explain select id, user_id，status, phone from users where create_time >='2021-01-02 23:01:00' and create_time <= '2021-01-03 23:01:00';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----
| 1 | SIMPLE | users | NULL | range | idx_status_create_time | idx_status_create_time | NULL | NULL | 15636 | 11.11 | Using where; Using index for skip scan|
Функцию сканирования с пропуском индекса также можно отключить с помощью optimizer_switch='skip_scan=off'.
Суммировать
В этом посте представлены индексы в MySQL, включая кластеризованные индексы и вспомогательные индексы. Вспомогательные индексы содержат идентификатор первичного ключа для операций возврата таблицы. В то же время использование сканирования индексов может лучше оптимизировать SQL.
В то же время он также знакомит с тем, как лучше проектировать индексы MySQL, включая префиксные индексы, порядок составных индексов и сканирование с переходом индекса, представленное в MySQL 8.0.Все мы знаем, что индексы могут ускорить поиск данных, уменьшить накладные расходы ввода-вывода, и занимают место на диске.Это метод оптимизации, который использует пространство в обмен на время.В то же время операция обновления приведет к частому слиянию и разбиению индекса, что влияет на производительность индекса.В реальном развитии бизнеса это очень важно разработать соответствующий индекс в соответствии с бизнес-сценарием.Сегодня мы поговорим об этом.Так много, я надеюсь помочь всем.
Я Ао Бин, чем больше вы знаете, тем больше вы не знаете, спасибо за три компании, увидимся в следующем выпуске.
Я Ао Бин,Чем больше вы знаете, тем больше вы не знаете, спасибо за ваши таланты:как,собиратьиКомментарий, увидимся в следующий раз!
Статья постоянно обновляется, вы можете искать в WeChat "Третий принц Ао Бин"Прочтите это в первый раз, ответьте [материал] Подготовленные мной материалы интервью и шаблоны резюме крупных заводов первой линии, эта статьяGitHub github.com/JavaFamilyОн был включен, и есть полные тестовые сайты для интервью с крупными заводами.Добро пожаловать в Star.