Преимущества индексации
- 1. Ускорить поиск данных, что является основной причиной создания индексов;
- 2. Создавая уникальный индекс, можно гарантировать уникальность каждой строки данных в таблице базы данных;
- 3. Ускорить соединение между столом и таблицей;
- 4. При использовании операторов группировки и сортировки для выборки данных можно значительно сократить время группировки и сортировки в запросах.
Недостатки индексации
- 1. Для создания и обслуживания индексов требуется время, и это время увеличивается с увеличением объема данных.
- 2. Индекс должен занимать физическое пространство. В дополнение к таблице данных, занимающей пространство данных, каждый индекс также занимает определенное количество физического пространства. Если необходимо установить кластеризованный индекс, требуемое пространство будет больше.
- 3. При добавлении, удалении и изменении данных в таблице индекс также должен вестись динамически и синхронно, что снижает скорость добавления, удаления и изменения данных.
Поэтому, если в одной таблице слишком мало данных, индекс будет влиять на скорость, данные, которые очень часто обновляются, не подходят для индексации.
Принципы построения индекса
В зависимости от возможностей базы данных существует три типа индексов, которые можно создать в конструкторе баз данных.
- Уникальный индекс: уникальный индекс — это тот, который не позволяет любым двум строкам в нем иметь одинаковое значение индекса.
- Индекс первичного ключа: первичный ключ определения таблицы автоматически создает индекс первичного ключа, который представляет собой особый тип уникального индекса. Этот индекс требует, чтобы каждое значение в первичном ключе было уникальным. Когда в запросе используется индекс первичного ключа, он обеспечивает быстрый доступ к данным.
- Кластеризованный индекс: физический порядок строк в таблице совпадает с логическим (индексным) порядком значений ключа. Таблица может содержать только один кластеризованный индекс
Конечной целью выбора индекса является ускорение выполнения запроса. Приведенные ниже принципы являются самыми основными, но вы не должны придерживаться этих принципов, вы должны анализировать и судить в соответствии с реальной ситуацией приложения и выбирать наиболее подходящий метод индексации.
1. Принцип сопоставления с крайним левым индексом
- Индекс может быть простым, состоящим из одного столбца (a), или сложным, состоящим из нескольких столбцов (a, b, c, d), то есть объединенным индексом.
- Если это совместный индекс, ключ также состоит из нескольких столбцов. В то же время индекс может использоваться только для определения того, существует ли ключ (равный), и не может быть сопоставлен в дальнейшем при обнаружении запросов диапазона (>,
пример:
Если есть индекс (a, b, c, d) и условия запроса a = 1 и b = 2 и c > 3 и d = 4, a, b и c будут последовательно выполняться в каждом узле, но d нельзя поразить. (очень просто: индексные попадания могут быть только случаями равенства, а не совпадениями диапазона)
Понимание самого левого принципа сопоставления очень полезно для разработки индексов и написания эффективных операторов SQL.
2. Создавайте индексы для полей, которые часто требуют операций сортировки и группировки.
Поля, которые часто требуют ORDER BY, GROUP BY, DISTINCT и других операций, операции сортировки будут тратить много времени. Если вы построите на нем индекс, вы сможете эффективно избежать операций сортировки.
Группировка полей или поля сортировки должны создавать индекс
3. Создавайте индексы для полей, которые часто используются в качестве условий запроса.
Если поле часто используется в качестве условия запроса, скорость запроса этого поля повлияет на скорость запроса всей таблицы. Следовательно, индексация таких полей может повысить скорость запроса всей таблицы.
Поля, которые часто используются в предложениях Where, должны быть проиндексированы
4. Ограничьте количество индексов
Количество индексов не больше, тем лучше. Для каждого индекса требуется место на диске, и чем больше индексов, тем больше требуется места на диске. Рефакторинг и обновление индексов обременительны при изменении таблицы. Чем больше индексов, тем больше времени уходит на обновление таблицы.
5. Попробуйте выбрать столбец с высокой степенью дискриминации в качестве индекса
Попробуйте выбрать столбец с высокой степенью дискриминации в качестве индекса. Формула степени дискриминации: количество (уникальный столбец) / количество (*), что указывает на долю полей, которые не повторяются. 1, и некоторый статус и гендерные поля могут иметь степень дискриминации 0 перед большими данными, поэтому некоторые люди могут спросить, есть ли какая-либо ценность опыта для этого соотношения? Различные сценарии использования, это значение также трудно определить.Как правило, поля, которые необходимо объединить, должны быть выше 0,1, то есть сканируется в среднем 10 записей.
6. Индексные столбцы не могут участвовать в вычислениях
Столбцы индекса не могут участвовать в вычислениях, сохраняйте столбцы «чистыми», например, изunixtime(createвремя) = ‘2019-12-02’, индекс использовать нельзя.Причина очень проста.Все значения полей в таблице данных хранятся в дереве b+, но при поиске нужно применять все элементы к функции для сравнения, очевидно, стоимость слишком велика. Таким образом, оператор должен быть написан как createtime = unixметка времени('2014-05-29');
То есть столбец индекса не может нести функции, иначе индекс будет недействителен.
7. Расширенный индекс
Попробуйте расширить индекс, не создавайте новый индекс. Например, в таблице уже есть индекс a, и теперь вам нужно добавить индекс (a, b), тогда вам нужно только изменить исходный индекс
8. Меры предосторожности для условных диапазонов, таких как
В аналогичном нечетком запросе правильный нечеткий запрос (abc%) будет использовать индекс, а (%abc) и (%abc%) откажутся от индекса и будут использовать полное сканирование таблицы.
9. Старайтесь использовать индексы с небольшим объемом данных
Если значение индекса очень длинное, это повлияет на скорость запроса. Например, полнотекстовый поиск в поле типа CHAR(100) занимает больше времени, чем в поле типа CHAR(10).
10. Попробуйте использовать префиксы для индексации
Если значение индексируемого поля очень длинное, лучше использовать префикс значения для индексации. Например, для полей типа ТЕКСТ и БЛОГ полнотекстовый поиск — пустая трата времени. Это может повысить скорость поиска, если извлекаются только первые несколько символов поля.
11. Удалите индексы, которые больше не используются или используются редко.
После значительного обновления данных в таблице или изменения способа использования данных некоторые исходные индексы могут больше не понадобиться. Администраторы баз данных должны регулярно выявлять эти индексы и удалять их, чтобы уменьшить влияние индексов на операции обновления.
12.= и может выйти из строя.
Например, a = 1 и b = 2 и c = 3, индекс (a,b,c) может быть установлен в любом порядке, и оптимизатор запросов mysql поможет вам оптимизировать его в форме, которую индекс может распознать.
13. Совместный запрос
При операциях с несколькими таблицами, таких как запрос на объединение и подзапрос, связанные поля должны быть проиндексированы.