Стратегия проектирования таблиц и индексов MySQL

Добро пожаловать в команду веб-разработчиков Futu, отсутствуют как php, так и внешний интерфейс.несоответствие

Прежде всего, я хочу извиниться перед всеми. Давно нет обновлений.

В прошлом месяце я был в Шанхае и принял участие в саммите FDC2018 Front-end Thousand People Summit. Ни разу не удалось обновить.

Участие в обмене опытомкликните сюда

Много чувств. В будущем будет больше возможностей для участия.

Я думал о том, чтобы узнать что-нибудь интересное для себя.

Оригинальная ссылка

Часть 1 Обзор MySQL

что такое MySQL

В 1970 г.Edgar Frank "Ted" Codd(Отец реляционных баз данных) опубликовал пост под названием«Реляционная модель данных для больших общих банков данных»Бумага, впервые предложенная и доказанная в статье, может быть использована с использованиемреляционная модельдля описания данных.

Реляционная модель относится к использованиюдвумерная таблицадля представления отношений между сущностями и сущностями. MySQL — одна из многих реляционных баз данных, реализованных на основе этой теории.

Диаграмма реляционной модели

История MySQL и текущая ситуация

1990 Michael WideniusНаписал первую версию MySQL.
1995 Майкл Видениус основал MySQL AB.
2000 Майкл Видениус выпустил исходный код MySQL под лицензией GPL. MySQL вступила в эру открытого исходного кода.
2008 Sun приобретает MySQL AB. База данных MySQL вступила в эру Sun.
2009 Oracle приобретает корпорацию Sun. База данных MySQL вступила в эру Oracle. Oracle поддерживает как версию MySQL для сообщества, так и версию MySQL для предприятий.
2009 Майкл Видениус отделился от ветки MySQL с открытым исходным кодом и основалMariaDB.

версия MySQL

MySQLИздание сообществадаоткрытый источникБесплатная, корпоративная версия с закрытым исходным кодом платная.

Ниже приведена сравнительная таблица основных функций версии.

Версия Tencent Cloud CDB 5.6:5.6.28-cdb20160902-log.online ddlКогда вам нужно обратить внимание: при добавлении столбцов в таблицу это все равно вызовет блокировку.

В версии 5.7 mysql значительно улучшена производительность и количество одновременных подключений.

Совместимость и различия MySQL и MariaDB

После того, как Oracle купила Sun, MySQL также попала в руки короля реляционных баз данных. Еще в 2009 году, учитывая репутацию Oracle и возможность закрытого исходного кода после ее запуска, Майкл, отец MySQL, взял на себя инициативу и запустил еще одну производную версию MySQL от имени своей дочери Марии: MariaDB.

As MariaDB is a full replacement of MySQL, the MySQL manual at dev.mysql.com/doc is generally applicable.--источник

Мы ежемесячно компилируем общедоступную версию базового кода MySQL в MariaDB, гарантируя, что MariaDB совместима со всеми исправлениями и обновлениями MySQL, добавленными Oracle.

Версия MariaDB соответствует версии Mysql — например, MariaDB 5.1, в которой используется тот же код, что и в MySQL 5.1. Поскольку в исходное дерево MySQL вносятся обновления и исправления, MariaDB затем может принять эти исправления, ссылаясь на исправления исходного кода, а не на соответствующие новые функции (теоретически MariaDB объединяется с исходным кодом MySQL каждый месяц).--источник

Upgrading from MySQL to MariaDB

Совместимость и различия

Логическая архитектура MySQL

Mysql состоит из интерфейса SQL, парсера, оптимизатора, кеша, механизма хранения.

Connectorsабонент
Management Serveices & UtilitiesСредства управления и контроля системы
Connection Poolпул соединений
SQL Interface
Parserпарсер
Optimizerоптимизатор
Cache & Bufferразличные тайники
Engineмеханизм хранения

Пул потоков является основной функцией Mysql 5.6.Для серверных приложений, будь то служба веб-приложений или служба БД, большое количество одновременных запросов всегда является темой, которой нельзя избежать. Когда имеется большое количество одновременных запросов на доступ, это должно сопровождаться непрерывным созданием и высвобождением ресурсов, что приводит к низкому использованию ресурсов и снижению качества обслуживания. Пул потоков — это общая технология. Создавая заранее определенное количество потоков, когда поступает запрос, пул потоков выделяет поток для предоставления услуг. После завершения запроса поток переходит к обслуживанию других запросов. Таким образом, можно избежать частого создания и освобождения потоков и объектов памяти, уменьшить параллелизм сервера, уменьшить переключение контекста и конкуренцию ресурсов, а также повысить эффективность использования ресурсов. Суть пула потоков всех сервисов заключается в повышении эффективности использования ресурсов, а методы реализации в основном одинаковы. В этой статье в основном объясняется принцип реализации пула потоков Mysql.

Физические файлы MySQL

Файл журнала записывает изменения, происходящие во время работы mysql. При случайном повреждении mysql восстановление данных можно выполнить через файлы журналов.

В журнал записывается много информации. Например: статус соединения mysql, выполнение оператора SQL и сообщения об ошибках будут записаны.

Файлы журнала MySQL в основном включают следующее:

журнал ошибок
журнал запросов
журнал медленных запросов
Журнал транзакций
двоичный журнал

Журнал ошибок в основном записывает данные, относящиеся к серверу mysql;

Журнал медленных запросов записывает некоторые запросы, выполнение которых занимает много времени;

Журнал транзакций — это журнал, специфичный для механизма хранения InnoDB;

Двоичный журнал в основном записывает операторы mysql, которые изменяют данные или могут вызвать изменения данных;

Файлы данных хранилища MySQL будут использовать разные файлы хранилища в соответствии с разными механизмами хранения.

Процесс выполнения SQL

Все, что мы хотим запросить, изменить или удалить данные, делается путем выполнения операторов SQL. MySQL выполняет связанные операции, анализируя операторы SQL, которые мы передаем.

После того, как оператор sql будет передан в mysql, он сначала проверит, есть ли соответствующие данные в кеше, и, если есть, вернет данные напрямую и завершит выполнение sql. Если нет, вам нужно передать текущую инструкцию sql в синтаксический анализатор для синтаксического анализа. Затем выполните предварительную обработку, чтобы проверить, соответствует ли синтаксис семантике, и, наконец, оптимизатор преобразует его в план выполнения, чтобы получить наиболее подходящий оператор запроса MySQL и, наконец, передать его механизму выполнения запросов. Получите последние данные, которые нам нужны.

Буферные пулы, последовательное чтение и случайное чтение

Кэш-пул реализуется механизмом хранения (а кеш запросов — это два разных уровня кеша). В MySQL InnoDB это можно сделать с помощьюinnodb_buffer_pool_sizeпараметр, определяющий размер буферного пула.

Пропуски буферного пулаLRUстратегия сохраняется. Если данные в базе данных могут быть полностью сохранены в пуле кеша, можно считать, что производительность базы данных является лучшей в это время. За исключением синхронной или асинхронной записи на диск, все остальные операции могут выполняться в памяти.

Ниже приведены данные 18G, так как буферный пул становится больше, изменение TPS. Данные 18G, хранящиеся в памяти, немного больше, чем 18G, потому что есть другие накладные расходы.

Благодаря пулу буферов некоторые горячие данные могут автоматически помещаться в пул буферов.

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

=======================Часть 1 Конец ======================

Часть II Проектирование базы данных

Определение парадигмы

Первая нормальная форма: Свойства неделимы. Каждый столбец (каждое поле) в таблице данных должен быть наименьшей единицей, которую нельзя разделить, то есть обеспечить атомарность каждого столбца;
Вторая нормальная форма: существует первичный ключ, и другие поля должны зависеть от первичного ключа. После выполнения 1НФ требуется, чтобы все столбцы в таблице зависели от первичного ключа, и не могло быть ни одного столбца, не имеющего отношения к первичному ключу, то есть таблица описывает только одну вещь;
Третья нормальная форма: устранить транзитивные зависимости (устранить избыточность). Сначала должна быть удовлетворена вторая нормальная форма (2NF), требующая: каждый столбец в таблице связан только напрямую с первичным ключом, а не косвенно (каждый столбец в таблице может зависеть только от первичного ключа)
Нормальная форма Bath-Corder (BCNF): в каждой таблице есть только один ключ-кандидат.
Четвертая нормальная форма: устранение многозначных зависимостей в таблицах. (Когда неосновные атрибуты в таблице независимы друг от друга (3NF), эти неосновные атрибуты не должны иметь несколько значений)

Примечание: Обычно нам достаточно третьей нормальной формы, последние слишком строгие, не соответствующие практическому использованию. Построение базы данных в соответствии с методом модели предметной области, как правило, хорошо удовлетворяет третьей парадигме.

Таблица сначала разрабатывается в соответствии со спецификацией парадигмы. Затем, в соответствии с фактическими требованиями к запросу, используйте антипарадигму для ускорения запроса.

выбор типа поля

Основные рекомендации:

меньше, просто, достаточно, обычно лучше
В общем, должна быть возможность использоватьХраните данные правильноизминимальный тип данных.
Меньшие типы данных, как правило, быстрее, потому что они занимают меньше места на диске, памяти и кэш-памяти ЦП и требуют меньше циклов ЦП для обработки.
Конечно, изменение типа поля позже требует много времени и усилий. Поэтому в начале проектирования лучше всего найти компромисс между «малым принципом» и «поздним обслуживанием» в зависимости от масштаба бизнеса.В случае достаточного выбирают наименьшее.
В зависимости от свойств поля выберите простой тип данных. Например, возраст следует хранить как целое число, а не как строку. Время должно храниться с использованием встроенного типа времени. IP должен храниться в int. (inet_aton('127.0.0.1') inet_ntoa(4294967295)MySQL имеет встроенные функции преобразования)
старайтесь избегать NULL
Старайтесь избегать использования типов set и enum

Целое число

TINYINT: 8 бит
SMALLINT: 16 бит
MEDIUMINT: 24 бита
INT: 32 бита
BIGINT: 64 бит

Диапазон: -2^(n-1) ~ (2^(n-1))-1

использоватьUNSIGNED, что может формально удвоить диапазон положительных чисел+1,как-128~127 -> 0~255

UNSIGNED INT4 миллиарда
UNSIGNED BIGINT18446744 триллиона

INT(11), где 11 не действует. Он просто указывает формат отображения некоторых клиентов MYSQL при отображении данных.

Отступление: указанные выше пять целочисленных типов просто указывают, как MYSQL хранит данные в памяти и на диске. Однако при вычислении целых чисел MYSQL обычно преобразует их все в 64-битные BIGINT для работы.

реальный тип

В поле Decimal рекомендуется использовать десятичный тип, float и double точности недостаточно, особенно для бизнеса, связанного с деньгами, необходимо использовать десятичный тип. --Руководство по использованию Tencent Cloud CDB для MySQL

В нашем реальном бизнесе мы предпочитаем использовать целые числа для хранения (расширение 1000, 10000 и т. д.)

Тип строки

Используется по умолчанию после 5.7utf8mb4

CHAR фиксированной длины. Количество символов записанных в скобках фиксировано, и открывается указанное количество хранилищ.Только сколько символов можно хранить, если не хватает, то система что-то добавит по умолчанию.
VARCHAR становится длиннее. В скобках указано максимальное количество символов. При фактическом хранении данных это не пространство фиксированной длины. Вместо этого он открывается в соответствии с записанными данными, но не может превышать максимальное количество символов.

Обратите внимание, что скобки определяютколичество символовне количество байт

CHAR удаляет крайний правый пробел (если он есть). Тогда как VARCHAR останется.

И VARCHAR, и CHAR содержат максимальное количество символов в круглых скобках.

При сохранении приветствия лучше использовать VARCHAR(5), чем VARCHAR(200). Хотя пространство, занимаемое этими двумя операциями, одинаково, VARCHAR(5) все же лучше и более эффективен для некоторых последующих операторов. (меньше принципа)

Храните очень большие данные:

Серия BLOB (двоичная): TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB
Серия TEXT (строка): TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT

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

тип даты и времени

DATETIME

использовать64 битдля хранения, промежуток времени1001~9999. У этого типа нет понятия часового пояса. Например, я сохранил «2018-01-01 00:00:00» в районе Дунба, а затем удалил его в районе Сиба, и у меня все еще есть «2018-01-01 00». : 00:00". Тогда это неправильно.

Так что лучше использовать DATETIME в базе данных, а затем использовать программу для генерации времени UTC (вместо времени в местном часовом поясе) и хранить его в базе данных.Когда вы его вынете, вы можете отобразить сервер время или время пользователя.

TIMESTAMP использовать32 битдля хранения метки времени (так что диапазон1970~2038)

MYSQL предоставляетFROM_UNIXTIMEФорматирует отображаемое время на выходе (а также добавляет для вас часовой пояс). В то же время MySQL также предоставляетCURRENT_TIMESTAMPавтоматически поддерживать created_at

В частности, используйте int или TIMESTAMP или DATETIME , см. статью ниже и подумайте сами.

woohoo.brief.com/afraid/Ed FDA ACC3…

blog.CSDN.net/pat PV QQ/art IC…

Выбор типа данных для столбцов идентификации

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

Вот несколько советов

Как только тип выбран, тип данных соответствующего столбца, который нужно сравнить или с которым связать, предпочтительно точно такой же, как и столбец идентификаторов, включаяunsignedЭти свойства также предпочтительно являются одинаковыми.
выберитеintПерепланировка всегда лучший вариант.
следует избегать использованияenum和set类型.
Старайтесь не использовать строковые типы.
старайтесь избегать NULL

некоторые трюки

Использование кэш-таблиц и сводных таблиц

Чтобы читать быстрее, только пишите медленнее. Иногда лучший способ повысить производительность — хранить производные избыточные данные в одной таблице. Однако иногда необходимо создать совершенно отдельную сводную или кеш-таблицу (особенно для целей поиска).

Кэш-таблица: обычно используется для «избыточных данных», которые могут быть сгенерированы из определенной таблицы за определенное время.
Сводная таблица: обычно это результат групповой операции (например, таблица номеров коллекций является сводной таблицей).

Сводные таблицы и таблицы подсчета могут столкнуться с узкими местами при записи. В настоящее время вы можете использовать «слот» для случайного назначения каждого нового «+1» определенной строке, чтобы вы могли заблокировать одну строку для каждой записи и изменить от N до N для каждой записи.Выберите один из слотов написать. По возможности избегайте ожидания блокировки записи.

некоторые другие трюки

Ограничения и контроль параллелизма

уникальный индекс

Используйте механизм базы данных, чтобы помочь нам добиться уникальности. Уникальность N может быть достигнута путем объединения уникальности полей.

оптимистическая блокировка

При обновлении данных в условие обновления включается номер версии (или значение важного поля) ранее прочитанной записи.

Поддержка оптимистичной блокировки для YII

увеличиваться и уменьшаться

в настоящее время извлеченоcount=4, чтобы добавить 1 для сохранения базы данных.count = count + 1иcount = 5разница

Используйте слоты, чтобы сократить время ожидания ресурсов блокировки

Если строка данных должна часто изменяться, может быть ожидание блокировки записи для этой строки данных. На этом этапе подумайте, можете ли вы изменить одну строку с бизнес-логики на изменение нескольких строк. Распределите операции записи на несколько строк, сократив время ожидания блокировки для одной строки.

Храните необработанные данные вместо результатов

Например, чтобы получить оставшееся время лотереи пользователя. На данный момент в базе данных已经用了的次数和总共有多少次抽奖次数лучше, чем иметь только один剩余抽奖次数.

====================Часть II Конец =======================

Часть III Дизайн индекса

Обсуждается здесь: Индексы MySQL 5.6 InnoDB BTREE. Пока древовидная структура BTREE в MySQL InnoDB проясняется, многие рекомендации по проектированию индексов могут быть получены самостоятельно.

Введение в индексирование

Индекс на самом деле является структурой данных. (Хеш-таблицы, деревья и т. д.) Разные типы индексов имеют разные структуры данных и функции.

Например, в китайском учебнике каждой статье соответствует номер начальной страницы, и все статьи расположены и набраны в порядке номеров страниц. В настоящее время «статьи организованы и типизированы в порядке страниц» — это структура данных указателя, а каталог книги — это файл указателя.

тип индекса

Mysql 5.6 InnoDB предоставляет два типа индексов (Index_type)

BTREE
FULLTEXT

(Mysql 5.6 InnoDB не поддерживает ручное использование хеш-индекса (внутренняя поддержка InnoDB).Адаптивный хэш-индекс) и не поддерживает геопространственный индекс (поддерживается с версии 5.7))

Кластерные индексы относятся к кластерным индексам.

Вы можете использовать индекс BTREE для достижения

индекс первичного ключаPRIMARY KEY
уникальный индексUNIQUE KEY
нормальный индексKEY

(иностранный ключFOREIGN KEYреализация также частично зависит отBTREEИндекс, при построении внешнего ключа вы должны требовать хотя бы общий индекс для текущего указанного столбца, в противном случае система автоматически создаст его для вас. )

Вы можете использовать индекс FULLTEXT для достижения

полный текстовый указательFULLTEXT KEY

Одноколоночный индекс и совместный индекс

Одностолбцовый индекс, используйте только один столбец для построения индекса
Совместный индекс с использованием нескольких столбцов для построения индекса


CREATE DATABASE `test_db` DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

use test_db;

CREATE TABLE  `table_b`(
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `name` VARCHAR(100) NOT NULL,
   `f_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY uk_name (`name`),
    KEY title (`title`),
    KEY title_name_fid (`title`,`name`,`f_id`),
    FULLTEXT KEY (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



SHOW INDEX FROM table_b;

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_b |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          0 | uk_name        |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title          |            1 | title       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            1 | title       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            2 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            3 | f_id        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | name           |            1 | name        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Роль индекса

Ускорить скорость запроса
Поддерживать привязку данных (целостность, непротиворечивость)

Для ускорения запросов использование индекса не обязательно лучший вариант. Полное сканирование таблицы выполняется для небольших таблиц, индексы строятся для средних и больших таблиц, а секционирование выполняется для очень больших таблиц. На самом деле, это в основном компромисс между преимуществами индекса и затратами на поддержание индекса.

BTREE-индекс

В MySQL 5.6 InnoDB мы обычно строим индексы и выбираем только BTREE. Полнотекстовое индексирование обычно не используется в наших бизнес-сценариях.

Введение в деревья B+

Подобно бинарным деревьям и сбалансированным бинарным деревьям, деревья B+ представляют собой классические структуры данных. Дерево B+ эволюционировало из дерева B и метода последовательного доступа к индексу (ISAM, вы знакомы с ним? Да, это также структура данных, на которую первоначально ссылался движок MyISAM), но дерево B почти никогда не используется в реальной жизни. использовать.

Определение дерева B+ очень сложное, поэтому дается только краткое введение в дерево B+: Дерево B+ — это сбалансированное дерево поиска, предназначенное для дисков или других вспомогательных устройств прямого доступа.В дереве B+ все узлы записи имеют размер ключа value Порядок хранится в конечных узлах того же слоя, и указатели каждого конечного узла связаны.

Функции:

Дерево
дерево поиска
остаток средств
Данные находятся в листовых узлах
Узлы могут содержать несколько данных
Между листовыми узлами есть указатели
Найти, вставить, удалить примерно O(lgn)

Связанный:

принцип локальности
Ввод/вывод медленный

Зачем использовать древовидную структуру данных B+ в качестве индекса?

Дерево поиска, эффективность поиска близка к O(lgn)
Сбалансированный, количество поисковых запросов на запрос в основном равно
Если данные индекса находятся в нелистовом узле, данные индекса можно загрузить в память, чтобы ускорить запрос.
Листовые узлы соединены указателями для легкого обхода.

Онлайн-генератор дерева B+

Логическая структура хранения Mysql InnoDB

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

табличное пространство
сегмент
степень
страница
ряд

Страница — это узел (конечный узел или нелистовой узел) в дереве B+.
Страницы имеют субиндексные страницы и страницы данных. Страница индекса хранит данные неконечных узлов, а страница данных хранит данные конечных узлов. Каждая страница содержит более одной строки, и строки последовательно соединены указателями. Когда мы ищем данные, мы сначала находим определенную страницу, а затем находим нужную строку на странице. По умолчанию 16 КБ на страницу.
Область включает 64 страницы.При применении области к диску она применяется в целом блоке.Поэтому данные в области физически непрерывны. (64 страницы × 16 КБ на страницу = 1 МБ на экстент). Общая память имеет возможность загружать все неконечные узлы в индексе дерева B+ в память для управления. Следовательно, при поддержании в памяти нелистовых узлов этого дерева B+ общие накладные расходы относительно невелики. Однако для обслуживания листовых узлов обычно требуется дисковый ввод-вывод, поэтому все листовые данные обычно не могут быть загружены в память.
Табличное пространство похоже на папку, а сегмент — это файл в папке. При создании нового индекса будут созданы два новых сегмента данных, один для хранения только индекса (сегмент индекса), а другой для хранения только данных (сегмент данных).
Табличное пространство — это данные таблицы.

В логической структуре хранения Mysql InnoDB есть три интересных места:

Сегмент предназначен для того, чтобы различать неконечные данные и конечные данные. Удобно загружать все нелистовые данные в память.
Область — это непрерывное дисковое пространство.
Страница также является непрерывным пространством внутри страницы.

аналогия:

Центры, разделенные центры, должны лучше управлять сотрудниками с одинаковыми функциональными навыками.
Направление бизнеса, есть много групп по бизнесу, и каждая группа сидит рядом, чтобы облегчить общение.
Группы, в которые входят сотрудники и сотрудники одной группы, сидят вместе, чтобы облегчить общение.

Из логической структуры мы можем узнать:

преемственность
Раздельное управление листовыми узлами и нелистовыми узлами

Обзор MySQL InnoDB BTREE

Из приведенной выше картины мы можем сделать следующие выводы

полное соответствие стоимости
соответствует крайнему левому префиксу
соответствие префиксу столбца
значение диапазона соответствия
Совпадение ровно с одним столбцом, а диапазон совпадет с другим столбцом
Слова индекса сегмента после первого поля запроса диапазона могут использоваться только для фильтрации.

Поля в индексе могут служить двум целям:

Определить начальную и конечную позицию индексного среза
фильтровать, сравнивать фильтр

Кластерные и вторичные индексы

кластеризованный индекс

В MySQL InnoDB каждая строка записей должна иметь первичный ключ. официальныйРекомендуется использовать независимый от бизнеса целочисленный беззнаковый самоинкрементный ненулевой тип.как первичный ключ. Если пользовательский первичный ключ отсутствует, система выберет первичный ключ в соответствии со следующими правилами.

Посмотрите, есть ли единственный ненулевой уникальный индекс, и если да, используйте его в качестве первичного ключа. Если их несколько, выберите первое поле, которое соответствует условиям в операторе построения таблицы.
Если нет, поддерживайте 6-байтовое пространство в качестве первичного ключа самостоятельно.Подробнее см.

Расширение: почему вы сами определяете самоинкрементный ненулевой int?

6 байт. Я не мог бы использовать так много
приказ. Уменьшите количество разбиений страниц.

Вторичный индекс

Другие индексы являются вспомогательными индексами (уникальные индексы, нормальные индексы и т. д.). Во вспомогательном индексе конечный узел хранит значение первичного ключа записи. Перейдите к кластеризованному индексу, чтобы проверить фактическое значение через значение первичного ключа.

Здесь нет никакой разницы между добавлением и не добавлением. За нас этим должен заниматься оптимизатор (оптимизатор очень мощный, код оптимизатора обновляется быстро, а фич много, оптимизатору надо доверять).

Почему конечный узел вспомогательного индекса не хранит непосредственно фактический физический указатель (номер страницы и т. д.) записи строки таблицы, а обращается к кластеризованному индексу для его повторной проверки?

В этом случае, если страница разделена в кластеризованном индексе, изменится физическая структура данных. Если вспомогательный индекс по-прежнему хранит физическую информацию, то необходимо обновить данные во вспомогательном индексе, что повлечет за собой множество дополнительных операций. В настоящее время нам приходится дважды проверять эту структуру, но нагрузка не очень серьезная, потому что все нелистовые узлы находятся в памяти, а проверка выполняется очень быстро.

Спецификация дизайна индекса Samsung

Как разработать индекс для оператора SQL.

индекс покрытия

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

Все поля, которые нужно выбрать, находятся в индексе.
select count(*) Поскольку нам нужно только число, нам нужно получить доступ только к вспомогательному индексу, поэтому он также является покрывающим индексом.

Поскольку вспомогательный индекс обычно меньше кластеризованного индекса (на странице конечного узла вспомогательного индекса страница может содержать больше записей столбцов).

Как оптимизироватьcount(*)

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

Полное сканирование таблицы, о котором мы говорим, относится к обходу конечных узлов кластеризованного индекса.

Определение индекса Samsung:

Первая звездочка: Минимизируйте диапазон листовых узлов, которые будут сканироваться (интервал между начальным и конечным битами должен быть как можно меньше).
Вторая звезда: избегайте операций сортировки, которые загружают данные из конечных узлов в память. Используйте индекс дерева B+, чтобы помочь нам сортировать дальше.
Третья звезда: избегайте сокращения запросов обратно к кластеризованному индексу и разрешайте битву с помощью вспомогательного индекса.

Этапы проектирования

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

Если все условия запроса являются эквивалентными (или без сортировки), то мы можем выполнить требование с 3 звездами.

Если в условии запроса where есть запрос диапазона, и есть необходимость в сортировке, то нам нужно выбрать только между (первая звездочка + третья звездочка) и (вторая звездочка + третья звездочка).

Кандидат А

(1-я звезда + 3-я звезда) Выберите самый узкий индексный лист, который наименее сканируется

Выньте столбцы для условия равенства и используйте эти столбцы в качестве ведущих столбцов индекса в любом порядке. (Лучше иметь высокую избирательность на переднем плане, чтобы можно было повторно использовать другие запросы. Это индекс)
Сделайте наиболее избирательное условие диапазона следующим столбцом индекса.
добавить в правильном порядкеorder byСписок. Игнорируйте столбцы, которые были добавлены на двух предыдущих шагах.
Добавьте другие столбцы в операторе select в индекс в любом порядке, начиная с неизменяемых столбцов.

Кандидат Б

(2-я звезда + 3-я звезда) Преследование не нужно сортировать

Выньте столбцы для условия равенства и используйте эти столбцы в качестве ведущих столбцов индекса в любом порядке. (Лучше иметь высокую избирательность на переднем плане, чтобы можно было повторно использовать другие запросы. Это индекс)
добавить в правильном порядкеorder byСписок. Игнорируйте столбцы, которые были добавлены на двух предыдущих шагах.
Добавьте другие столбцы в операторе select в индекс в любом порядке, начиная с энергонезависимых столбцов.

С сортировкой и запросом диапазона, прежде чем рассматривать выбор кандидата А или кандидата Б, в остальных случаях может быть удовлетворен индекс Самсунга.

Сортировка означает:order by

Выбор кандидата А или кандидата Б - это судить: высока стоимость загрузки в память сортировки, или высока стоимость обхода по одному с самого начала. Этот некачественный ответ необходимо определить в соответствии с характеристиками данных и тем, какие данные необходимо получить.

select A,B from user where A > a order by B;

Предположим, всегоnзаписи, отвечающие условиямA > aдаmбар, один раз для каждых данныхi/o

Кандидат А:n->m, Потомi/o mДанные поступают в память для сортировки, что занимает много времениO(mlgm), то есть затраченное время равно:mвторосортныйi/o+ mlgmсравнение
Кандидат Б:i/o nДанные заносятся в память для сравнения, и количество сравнений1~n, то есть затраченное время равно:1~nраз [сравнить +i/oвремя]. (Потому что, если вам нужно взять только 1 часть данных, вы можете выйти раньше, поэтому1~n)

Если m велико, близко к n, то кандидат B хорош. Если m мало, близко к 1, то кандидат А хорош.

===================Часть III Конец =====================

ЧАСТЬ 4 ПРАКТИЧЕСКОЕ ТЕСТИРОВАНИЕ

Конфигурация оборудования

Облачная база данных Tencent
5.6.28-cdb2016-log 20180122
Версия High IO, память 1000 МБ, жесткий диск 25 ГБ, 1000 раз/сек.

Основная информация о конфигурации MySQL

Отключить кеширование запросов

show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)

16 КБ на страницу

show variables like '%innodb_page%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

Кэш InnoDBРазмер 893M

show variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 936378368      |
+-------------------------------------+----------------+

Исходное состояние тестовой базы данных

Количество: 500 Вт
Первичный ключ: автоматически увеличивающийся первичный ключ int

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` tinyint(3) unsigned NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `email` varchar(255) NOT NULL,
  `address` varchar(350) NOT NULL,
  `company` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (1.81 sec)


select * from user limit 1 \G
*************************** 1. row ***************************
     id: 1
   name: Prof. Osborne Waelchi I
    sex: 0
    age: 60
  email: dach.angela@yahoo.com
address: 35712 Quigley Mountains North Alysonville, CO 53682-2718
company: McGlynn Ltd
   city: Port Maziebury
1 row in set (0.01 sec)

Экспериментальный случай

Корреляция индекса

После операции индекс нельзя использовать

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> explain select * from user where id+1=2;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.04 sec)

MySQL [test_db_for_index]> select * from user where id+1=2;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.04 sec)

MySQL [test_db_for_index]> select * from user where id=1;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

Префикс поля столбца и трюк обхода IN


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I';
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.11 sec)

MySQL [test_db_for_index]> explain select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1);
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | sex_name      | sex_name | 768     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1);
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

Частичное использование многоколоночных индексов

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I' and age=60;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.03 sec)

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I' and age=60;;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.03 sec)

ERROR: No query specified

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.03 sec)


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | name_age_email | name_age_email | 767     | const |    1 | Using index condition |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I' and age=60;;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

ERROR: No query specified

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I' and age=60;
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | name_age_email | name_age_email | 768     | const,const |    1 | Using index condition |
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
1 row in set (0.04 sec)

Частичное использование одного индекса

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%';
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
| id      | name                     | sex | age | email                    | address                                                   | company        | city             |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
|       1 | Prof. Osborne Waelchi I  |   0 |  60 | dach.angela@yahoo.com    | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd    | Port Maziebury   |
|  798465 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1167101 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 1660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3528809 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 4021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4521968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 5021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
11 rows in set (3.30 sec)



MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)

MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |   11 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%';
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
| id      | name                     | sex | age | email                    | address                                                   | company        | city             |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
|       1 | Prof. Osborne Waelchi I  |   0 |  60 | dach.angela@yahoo.com    | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd    | Port Maziebury   |
|  798465 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4521968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 5021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1167101 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 3528809 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
11 rows in set (0.04 sec)

Эквивалент или того же поля, новая версия оптимизатора будет оптимизировать, вы можете использовать индекс

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley');
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley');
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
| id | name                    | sex | age | email                  | address                                                   | company          | city           |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com  | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd      | Port Maziebury |
|  4 | Zaria Quigley           |   0 |  41 | ryan.anissa@cronin.com | 799 Barney Cove
Princessland, VA 34382                    | Farrell-Hartmann | DuBuqueport    |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
2 rows in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley';
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
| id | name                    | sex | age | email                  | address                                                   | company          | city           |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com  | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd      | Port Maziebury |
|  4 | Zaria Quigley           |   0 |  41 | ryan.anissa@cronin.com | 799 Barney Cove
Princessland, VA 34382                    | Farrell-Hartmann | DuBuqueport    |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
2 rows in set (0.01 sec)

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


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | email    |            1 | email       | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' or email='d';
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
| id | select_type | table | type        | possible_keys | key        | key_len | ref  | rows | Extra                                |
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
|  1 | SIMPLE      | user  | index_merge | name,email    | name,email | 767,767 | NULL |    2 | Using union(name,email); Using where |
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='1' or email='d';
Empty set (0.02 sec)


MySQL [test_db_for_index]> select * from user where name='1' union select * from user where email='d';
Empty set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' union select * from user where email='d';
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type  | table      | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
|  1 | PRIMARY      | user       | ref  | name          | name  | 767     | const |    1 | Using index condition |
|  2 | UNION        | user       | ref  | email         | email | 767     | const |    1 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL  | NULL    | NULL  | NULL | Using temporary       |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
3 rows in set (0.01 sec)

Минимизируйте условия диапазона. (>,

## sex字段只有 0 1 两个取值
MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where sex!=1 and name='payton';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | sex_name      | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where sex!=1 and name='payton';
Empty set (3.18 sec)

MySQL [test_db_for_index]> desc select * from user where sex=0 and name='payton';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | sex_name      | sex_name | 768     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select * from user where sex=0 and name='payton';
Empty set (0.02 sec)

В объединенном индексе поля после поля поиска диапазона больше не могут определять ширину среза индекса и могут использоваться только для фильтрации. Использовать оптимизацию IN

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name |            1 | age         | A         |         136 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where age >= 10 and age <= 15 and name='payton';
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | user  | range | age_name      | age_name | 768     | NULL | 626654 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age >= 10 and age <= 15 and name='payton';
Empty set (0.09 sec)

MySQL [test_db_for_index]> desc select * from user where age in (10,11,12,13,14,15) and name='payton';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | age_name      | age_name | 768     | NULL |    6 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age in (10,11,12,13,14,15) and name='payton';
Empty set (0.02 sec)

Индекс покрытия лучше

Из-за пула буферов InnoDB здесь также имеется почти 900 МБ памяти. Полностью способен индексировать нелистовые узлы и оставляет всю загрузку в памяти. Точно так же и кластеризованный индекс. Поскольку между io нет разрыва, разница заключается в количестве строк на странице данных. Так что тут разница не велика. Однако, если не совсем положить в память кластеризованный индекс всех нелистовых узлов, то разрыв здесь будет еще больше.

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (1.69 sec)


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex      |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | sex  | 1       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (0.67 sec)

Размер кэш-пула InnoDB, влияние на запросы

Среда для этого эксперимента отличается от других.

Кэш-пул реализуется подсистемой хранения. В MySQL InnoDB это можно сделать с помощьюinnodb_buffer_pool_sizeпараметр, определяющий размер буферного пула.

Из-за пула буферов некоторые горячие данные могут автоматически помещаться в пул буферов, что ускоряет работу.

Произвольное чтение и запись на диск и жесткий диск и последовательное чтение и запись

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

Обратите внимание, что порядок здесь относится к порядку внутри большого блока, а большой блок и большой блок могут быть прерывистыми. Потому что трудно гарантировать, что можно претендовать на непрерывное пространство в десятки гигабайт.

В MySQL InnoDB страницы управляются через области.Каждый раз, когда вы подаете заявку на хранение, вы будете подаваться на непрерывную область, включающую 64 страницы. Таким образом, можно гарантировать непрерывность 64 страниц, но непрерывность площади и интервала не гарантируется.

Хотя физическая структура твердотельных накопителей отличается от структуры дисков, они также придерживаются вышеуказанных принципов. Последовательное чтение будет по-прежнему быстрее, чем случайное чтение.

Роль кэширования, система кэширует все конечные узлы, индексированные random_digit, в память.

MariaDB [big_tables]> show index from custom;
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| custom |          0 | PRIMARY      |            1 | id           | A         |     1240315 |     NULL | NULL   |      | BTREE      |         |               |
| custom |          1 | email        |            1 | email        | A         |     1240315 |      255 | NULL   | YES  | BTREE      |         |               |
| custom |          1 | name         |            1 | name         | A         |     1240315 |      255 | NULL   | YES  | BTREE      |         |               |
| custom |          1 | random_digit |            1 | random_digit | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (1.39 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.25 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.22 sec)

MariaDB [big_tables]> desc select count(*) from custom;
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
| id   | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
|    1 | SIMPLE      | custom | index | NULL          | random_digit | 7       | NULL | 1240315 | Using index |
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Оптимизаторы отличные


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+


MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | user  | range | age_name_email | age_name_email | 1       | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>580 order by name limit 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.02 sec)

Кардинальность и избирательность поля


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex      |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)


MySQL [test_db_for_index]> select count(distinct sex)/count(*) from user;
+------------------------------+
| count(distinct sex)/count(*) |
+------------------------------+
|                       0.0000 |  # 其实不是0，不够很接近
+------------------------------+
1 row in set (1.88 sec)


MySQL [test_db_for_index]> select count(distinct name)/count(*) from user;
+-------------------------------+
| count(distinct name)/count(*) |
+-------------------------------+
|                        0.1592 |
+-------------------------------+
1 row in set (6.13 sec)

MySQL [test_db_for_index]> select count(distinct left(email,5))/count(*) from user;
+----------------------------------------+
| count(distinct left(email,5))/count(*) |
+----------------------------------------+
|                                 0.0049 |
+----------------------------------------+
1 row in set (4.12 sec)


MySQL [test_db_for_index]> select count(distinct left(email,15))/count(*) from user;
+-----------------------------------------+
| count(distinct left(email,15))/count(*) |
+-----------------------------------------+
|                                  0.1545 | # 这个最省空间。不过要注意，截断后，就不能使用覆盖索引了，必须要回聚集索引才能拿到当前列完整的内容
+-----------------------------------------+
1 row in set (5.74 sec)


MySQL [test_db_for_index]> select count(distinct email)/count(*) from user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
|                         0.1586 |
+--------------------------------+
1 row in set (5.66 sec)

Кандидат А или Кандидат Б

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

MySQL [test_db_for_index]> select age,name,email from user where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.00 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 100000,10;
+-----+--------------------+-------------------------------+
| age | name               | email                         |
+-----+--------------------+-------------------------------+
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
+-----+--------------------+-------------------------------+
10 rows in set (0.06 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 100000,10;
+-----+--------------------+-------------------------------+
| age | name               | email                         |
+-----+--------------------+-------------------------------+
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
+-----+--------------------+-------------------------------+
10 rows in set (18.65 sec)


--------------------------


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 89 order by name limit 1;
Empty set (1.61 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 89 order by name limit 1;
Empty set (0.01 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.00 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

## 选择候选A 还是 候选B，我们自己想的过程，其次优化器已经帮我们都想好了。（如果同时存在候选A 和 候选B 的话）



MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+


MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | user  | range | age_name_email | age_name_email | 1       | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.02 sec)

использованная литература

dev.mysql.com/doc/
Справочное руководство по MySQL 5.6
Документация по внутреннему механизму реализации MySQL