Принцип крайнего левого префикса (согласно официальной документации)

MySQL

предисловие

Я всегда хотел знать, что такое принцип самого левого префикса, и тогда я нашел много документов в Интернете.Каждый документ дляПринцип крайнего левого префикса (самого левого совпадения)Объяснения расплывчатые.После прочтения люди в замешательстве.Единственный вывод,что принцип крайнего левого префикса может быть связан с совместным индексом.

В этом случае можно только сдать официальную документацию.

Официальный документ обучения

мы ищемleftmost prefixключевое слово найдено8.3.6 Multiple-Column Indexesдокументации, в которой есть абзац, в котором говорится:

MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Раздел 8.3.5, «Индексы столбцов»).

Давайте переведем это.

MYSQL может создавать федеративные индексы (то есть индексы с несколькими столбцами). Индекс может содержать до 16 столбцов. Для некоторых типов данных можно индексировать префикс столбца.

Итак, как мы понимаем предложение «Для некоторых типов данных вы можете индексировать префикс столбца»?

давайте посмотрим еще раз8.3.5 Column IndexesВведение в префиксы индекса в:

With col_name(N) syntax in an index specification for a string column, you can create an index that uses only the first N characters of the column. Indexing only a prefix of column values in this way can make the index file much smaller. When you index a BLOB or TEXT column, you must specify a prefix length for the index. For example:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

То есть при создании индекса по строковому столбцу мы можем использоватьcol_name(N)синтаксис для создания индекса с использованием только первых N символов столбца. Индексные файлы, созданные таким образом, будут намного меньше. дляBLOBилиTEXTКогда столбец индексируется, для индекса должна быть указана длина префикса.

Это введение в индекс префикса, давайте вернемся и посмотрим на самый левый индекс префикса.

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Совместный индекс MYSQL можно использовать для запросов, включающих все столбцы индекса или только первый столбец, первые два столбца, первые три столбца и т. д. Объединенный индекс может ускорить выполнение многих различных типов запросов к одной и той же таблице, если вы укажете столбцы в правильном порядке в определении индекса.

Пример

Давайте посмотрим на пример крайнего левого префикса, приведенного в официальной документации.

Сначала у нас есть следующая таблица:

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

nameвключенlast_nameа такжеfirst_nameСовместный индекс столбца. Этот индекс можно использовать дляlast_nameа такжеfirst_nameУказанный диапазон запросов также можно использовать только для указанногоlast_nameЗапрос столбца, так как этот столбец является индексомкрайний левый префикс, поэтому индексnameМожет использоваться для следующих операторов запроса:

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

Тем не менее, индексnameНельзя использовать для следующих запросов:

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

Если таблица имеет индекс объединения, оптимизатор использует крайний левый префикс любого индекса для поиска столбца. Например, если вы создаете индекс объединения с тремя столбцами(col1, col2, col3), ваш индекс вступает в силу(col1), (col1, col2), and (col1, col2, col3).

развитие знаний

Зачем использовать федеративный индекс

  • уменьшить накладные расходы. построить совместный индекс(col1,col2,col3), что фактически эквивалентно построению(col1),(col1,col2),(col1,col2,col3)три индекса. Каждый дополнительный индекс увеличивает нагрузку на операции записи и дисковое пространство. Для таблиц с большим объемом данных использование объединенного индекса значительно сократит накладные расходы!
  • индекс покрытия. совместный указатель(col1,col2,col3), если у вас есть следующий SQL:select col1,col2,col3 from test where col1=1 and col2=2;. Затем MySQL может получать данные напрямую, обходя индекс, не возвращаясь к таблице, что сокращает количество случайных операций ввода-вывода. Сокращение операций ввода-вывода, особенно произвольного ввода-вывода, на самом деле является основной стратегией оптимизации администратора баз данных. Поэтому в реальных практических приложениях покрывающий индекс является одним из основных методов оптимизации для повышения производительности.
  • эффективный. Чем больше столбцов индекса, тем меньше данных отфильтровывается индексом. Таблица с фрагментами данных объемом 1000 Вт имеет следующий SQL:select from table where col1=1 and col2=2 and col3=3, предполагая, что каждое условие может отфильтровать 10% данных, если есть только индекс с одним значением, то индекс может отфильтровать 1000W10%=100w фрагментов данных, а затем вернуться к таблице, чтобы найти col2= 2 и col3 из фрагментов данных размером 100 Вт. = 3 данных, затем отсортируйте и разбейте на страницы; если это совместный индекс, отфильтруйте 1000 Вт * 10% * 10% * 10% = 1 Вт через индекс, и повышение эффективности может быть воображаемый!

перейти проблема с индексом

В примере, приведенном в официальной документации выше,SELECT * FROM test WHERE first_name='John';Этот SQL нельзя использоватьnameIndex, на самом деле это утверждение не очень точное, давайте разберемся с ним подробнее.

Давайте взглянем на процесс выполнения следующих двух операторов SQL.

mysql> explain select * from test where last_name='Jones';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | name          | name | 90      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from test where first_name='Jones';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | name          | name | 180     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Из приведенного выше мы видим, что эти два SQL исчезли.nameindex, значит, не только тот индекс, который соответствует принципу крайнего левого префикса, может использовать объединенный индекс?

На самом деле это не так, мы можем внимательно наблюдать за результатами выполнения двух приведенных выше SQL-запросов. мы найдем ихtypeне то же самое, одноref,одинindex.

мы можем просмотретьmysql объяснить подробное объяснение, соответствующая часть которого касаетсяrefа такжеindexвведение.

8.8.2 EXPLAIN Output FormatоrefВведение такое:

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

То есть: будут прочитаны только совпадающие строки, когда столбец запроса является крайним левым префиксом индекса или будет использоваться обычный индекс (не индекс первичного ключа или уникальный индекс).ref. То есть будут опрошены только несколько строк.

оrefВведение такое:

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

  • If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

  • A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

MySQL can use this join type when the query uses only columns that are part of a single index.

indexтип соединения сallТо же самое, за исключением того, что сканируется дерево индексов. Есть в основном два пути:

  • Дерево индексов сканируется только в том случае, если запрошенный индекс охватывает индекс и данные, которые необходимо запросить из таблицы, встречаются. при этих обстоятельствах,Extraуказан какUsing index.Сканирование индекса будет быстрее, чем ALL, потому что дерево индекса обычно меньше, чем вся таблица..
  • Выполняя полное сканирование таблицы при чтении индекса, чтобы найти строки данных в порядке индекса,Using indexне появится вExtraСписок.

MYSQL использует этот тип, когда запрашиваемый столбец является частью индекса.

Из приведенного выше сравнения мы видим, чтоrefбудет сканировать только несколько строк, аindexБудет просканирован весь индекс.Хотя используется один и тот же индекс, эффективность выполнения разная.

Давайте сравним, когда толькоid,first_name,last_nameполе, и у нас есть совместный индексname, мы выполняемselect * from test where first_name='刘';Все наши данные будут получены из индекса, поэтому нет необходимости сканировать всю таблицу, нужно сканировать только дерево индекса. Итак, типindex.

Когда мы вставляем другое поле, напримерdescription, дерева индексов в настоящее время нетdescriptionполе, мы не можем получить полный результат, просматривая дерево индексов, тип в это время должен бытьall.

Давайте проверим:

mysql> alter table test add column `description` char(30) NOT NULL;


mysql> explain select * from test where first_name='刘';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Суммировать

Выше мы изучили введение документов, связанных с крайним левым префиксом в официальные документы, и можем сделать следующие выводы:

  • крайний левый префикс: То есть при создании индекса объединения любой крайний левый префикс индекса может использоваться для запросов. Например, когда у вас есть совместный индекс (col1, col2, col3), все префиксы этого индекса(col1),(col1, col2),(col1, col2, col3), все запросы, содержащие эти столбцы, будут выполняться с использованием этого индекса.
  • Объединенный индекс может содержать до 16 столбцов.
  • BLOBа такжеTEXTСколько первых цифр должно быть выполнено, чтобы создать индекс

Справочная документация

Категории