содержание

About MySQL
Why MySQL
MySQL Index
Как использовать индекс
Рекомендации по оптимизации запросов
Суммировать
Links
- Ресурсы для авторов
- использованная литература

About MySQL

MySQL (произносится /maɪ ˈsiːkwəl/"My Sequel") — это система управления реляционными базами данных с открытым исходным кодом, первоначально разработанная шведской компанией MySQL AB, а в настоящее время являющаяся продуктом Oracle.

После приобретения Oracle сообщество свободного программного обеспечения обеспокоено тем, будет ли Oracle продолжать поддерживать MySQL Community Edition (единственную бесплатную версию MySQL), поэтому основатель MySQL Майкл Видениус использует MySQL в качестве основы, учредив филиал проекта MariaDB. Некоторое программное обеспечение с открытым исходным кодом, которое использовало MySQL, изначально использовало MariaDB или другие базы данных.

Нельзя отрицать, что MySQL стала одной из самых популярных баз данных с открытым исходным кодом благодаря своей высокой производительности, низкой стоимости и хорошей надежности.По мере того, как MySQL продолжает развиваться, она постепенно используется на более крупных веб-сайтах и в приложениях и становится очень популярной. Буква «M» в портфолио программного обеспечения с открытым исходным кодом LAMP относится к MySQL.

Why MySQL

Среди многих систем с открытым исходным кодом и бесплатных реляционных баз данных MySQL имеет следующие выдающиеся преимущества:

беги быстро
легко использовать
Поддержка языка SQL
хорошая переносимость
Многофункциональный
бюджетный

Для скорости бега, согласно официальному представлению,MySQL 8.0более широко используется, чем предыдущая версияMySQL 5.7Есть двойное улучшение.

в своем официальномBenchmarks, производительность только для чтения превышает миллион раз в секунду:

Производительность чтения и записи близка к 250 000 раз в секунду:

MySQL Index

Why Index

Концептуально база данных представляет собой набор таблиц данных, которые представляют собой наборы строк данных и столбцов данных. когда вы выполняетеSELECT语句Когда вы запрашиваете некоторые строки данных из таблицы данных, вы получаете другой набор таблиц данных и строк данных.

Конечно, мы все хотим, чтобы эта новая коллекция была как можно короче и эффективнее, что и является запросом на оптимизацию.

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

Как работают индексы

Сначала создайте на своем MySQLt_user_action_logтаблицу для удобства следующей демонстрации.

CREATE DATABASE `ijiangtao_local_db_mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE ijiangtao_local_db_mysql;

DROP TABLE IF EXISTS t_user_action_log;

CREATE TABLE `t_user_action_log` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` VARCHAR(32) DEFAULT NULL COMMENT '用户名',
  `ip_address` VARCHAR(50) DEFAULT NULL COMMENT 'IP地址',
  `action` INT4 DEFAULT NULL COMMENT '操作：1-登录，2-登出，3-购物，4-退货，5-浏览',
  `create_time` TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.3', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.4', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 4, CURRENT_TIMESTAMP);

Если бы мы фильтровалиactionза2Для всех записей SQL выглядит следующим образом:

SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

Через анализатор запросовexplainПроанализируйте этот запрос:

EXPLAIN SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

Результаты анализа следующие:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		ALL					1	100.00	Using where

вtypeзаALLУказывает, что необходимо выполнить полное сканирование таблицы. Эта эффективность, несомненно, чрезвычайно медленная.

НижеactionИндекс добавления столбца:

ALTER TABLE t_user_action_log ADD INDEX (`action`);

Затем снова выполните анализ запроса, и результаты будут следующими:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		ref	action	action	5	const	1	100.00

Мы видим, что этот запрос использует индекс. перед индексациейExtraЗначение Использование Где, после добавления индексаExtraЗначение пустое.

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

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

как пользоваться

Вот как создавать, просматривать и удалять индексы с помощью SQL.

создать индекс

Три способа:

использоватьCREATE INDEXСоздать, синтаксис следующий:

CREATE INDEX indexName ON tableName (columnName(length));

Например, у нас естьip_addressЭтот столбец создает индекс длиной 16:

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

использоватьALTERСоздание оператора, синтаксис следующий:

ALTER TABLE tableName ADD INDEX indexName(columnName);

ALTERОператор для создания индекса уже приводился ранее в качестве примера. Ниже приведен пример установки длины индекса:

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));

SHOW INDEX FROM t_user_action_log;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t_user_action_log	1	ip_address_idx	1	ip_address	A	1	16		YES	BTREE

Создайте индекс при создании таблицы:

CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);

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

в состоянии пройтиshowИндекс просмотра заявления:

SHOW INDEX FROM t_user_action_log;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Null	Index_type
t_user_action_log	0	PRIMARY	1	id	A	199,368			BTREE
t_user_action_log	1	action	1	action	A	4		YES	BTREE
t_user_action_log	1	index_ip_addr	1	ip_address	A	1	16	YES	BTREE

падение индекса

использоватьALTERКоманды могут удалять индексы, например:

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

Как использовать индекс

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

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

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

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

Дополнительные индексы занимают больше места на диске

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

Не индексировать выходные столбцы

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

Например, следующий запрос:

select ip_address from t_user_action_log
where name='LiSi'
group by action
order by create_time;

Таким образом, можно рассматривать возможность увеличенияname action create_timeколонка вместоip_address.

Учитывайте размерное преимущество

НапримерactionЗначение столбца содержит: 1, 2, 3, 4, 5, тогда размерность столбца равна 5.

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

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

Например, значения столбца «гендер» — только мужские и женские, и на каждый тип результатов запроса приходится около 50%. Как правило, когда обработчик оптимизации запросов обнаруживает, что результат запроса превышает 30 % от полной таблицы, он пропускает индекс и выполняет полное сканирование таблицы напрямую.

индексация коротких значений

Индексирование коротких значений означает, что индекс занимает меньше места, снижает активность ввода-вывода и быстрее сравнивает индексы.

В частности, первичный ключ должен быть как можно короче.

Кроме того, InnoDB использует кластерный индекс (clustered index), то есть первичный ключ и строки данных хранятся вместе. Все индексы, отличные от первичного ключа, являются вторичными индексами, и эти вторичные индексы также сохраняют первичный ключ, так что после запроса индекса можно найти соответствующую строку данных в соответствии с первичным ключом. Если первичный ключ слишком длинный, пространство, занимаемое вторичным индексом, увеличится.

Например, индекс действия ниже хранит идентификатор соответствующей строки.

префикс строки индекса

Преимущества коротких индексов уже упоминались ранее.Иногда первые несколько символов строки могут однозначно идентифицировать эту запись.В настоящее время установка длины индекса является очень экономичным подходом.

Индекс настройки был предоставлен ранееlengthпример, здесь нет примера.

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

Синтаксис создания составного индекса следующий:

CREATE INDEX indexName ON tableName (column1 DESC, column2 DESC, column3 ASC);

Мы видим, что самый левый индекс column1 всегда действителен.

блокировка индекса

Для InnoDB индексы позволяют запросам блокировать меньше строк, что приводит к повышению производительности в параллельных ситуациях.

Далее демонстрируется взаимосвязь между блокировками запросов и индексами.

бывшего употребленияt_user_action_logВ настоящее время в таблице естьidПервичный ключ и вторичный индексaction.

Объем модификации следующего утвержденияidзначение1 2 3 4Строка, в которой находится блокировка запроса, будет заблокирована.idзначение1 2 3 4 5на линии.

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

Сначала создайте соединение с базой данных 1, откройте транзакцию и выполните оператор обновления.

set autocommit=0;

begin;

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

Затем откройте другое соединение 2 и выполните следующие операторы обновления соответственно.

-- 没有被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=6;
-- 被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=5;

ты найдешьid=5Строка данных заблокирована,id=6Строка данных может быть представлена нормально.

Соединение 1 фиксирует транзакцию, соединение 2id=1иid=5Строка данных может быть успешно обновлена.

-- 在连接1提交事务
commit;

Если индекс не используется

ip_addressЕсли индекса нет, вся таблица будет заблокирована.

После подключения открытой сделкиcommit;Раньше для всех обновлений таблицы соединением 2 требовалось ждать, пока соединение 1 освободит блокировку.

set autocommit=0;

begin;

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where ip_address='8.8.8.1';

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

Если индекс содержит все данные, удовлетворяющие запросу, он называется покрывающим индексом (покрывающие индексы).Покрывающий индекс является очень мощным и может значительно повысить производительность запроса.

Причинами высокой эффективности покрывающих индексов являются:

Индексы обычно меньше, чем записи, и покрывающие индексные запросы должны читать только индекс, а не записи.
Индексы хранятся последовательно по значению, что требует меньше операций ввода-вывода, чем произвольный доступ к записям.
Большинство механизмов обработки данных кэшируют индексы лучше, например, MyISAM кэширует только индексы.

ijiangtao_local_db_mysqlТаблицаactionСтолбцы содержат индексы. использоватьexplainПроанализируйте следующую инструкцию запроса для запроса с индексом (запрос с индексом), проанализируйте результатыExtraЗначениеUsing index, что указывает на то, что используется покрывающий индекс:

explain select `action` from ijiangtao_local_db_mysql.t_user_action_log;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		index		action	5		199,703	100.00	Using index

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

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

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

Не все механизмы хранения поддерживают кластерные индексы, в настоящее время InnoDB поддерживает.

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

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

Выберите правильный тип индекса

С точки зрения структуры данных типы индексов, поддерживаемые MySQL, включают индекс B-дерева, хэш-индекс и т. д.

Индекс B-дерева

Индексы B-дерева более эффективны для =, >, , !=, между запросами, операциями точного сравнения и операциями сравнения диапазонов.

Индекс B-дерева также является структурой индекса по умолчанию механизма хранения InnoDB.

Хэш-индекс

Хэш-индексы могут удовлетворять только =, и в запросах.

Эффективность поиска хэш-индекса очень высока, поиск индекса может быть расположен за один раз, в отличие от индекса B-дерева, который требует множественного доступа ввода-вывода от корневого узла к узлу ветви и, наконец, к узлу страницы, поэтому эффективность запросов индекса Hash намного выше, чем индекс B-дерева. Но хэш-индексы не могут использовать запросы диапазона.

Рекомендации по оптимизации запросов

Вот несколько предложений по оптимизации запросов.

Используйте объяснение для анализа операторов запроса

Было продемонстрировано, как использоватьexplainПосле того, как команда проанализирует оператор запроса, вот объяснение значения нескольких полей со ссылочным значением:

select_type

select_type представляет тип каждого предложения select в запросе и обычно имеет следующие значения:

ПРОСТО Простой SELECT, без UNION или подзапросов и т.д.
НАЧАЛЬНЫЙ Если запрос содержит какие-либо сложные подразделы, самый внешний выбор помечается как PRIMARY.
СОЮЗ Второй или последующий оператор SELECT в UNION.
ЗАВИСИМЫЙ СОЮЗ Второй или последующий оператор SELECT в UNION, в зависимости от внешнего запроса.
СОЮЗ РЕЗУЛЬТАТ результат СОЮЗА.
ПОДЗАПРОС Первый SELECT в подзапросе.
ЗАВИСИМЫЙ ПОДЗАПРОС Первый SELECT в подзапросе, в зависимости от внешнего запроса.
ПОЛУЧЕННЫЙ Подзапросы для предложений SELECT, FROM производных таблиц.
НЕКЭШИРУЕМЫЙ ПОДЗАПРОС Результаты подзапроса не могут кэшироваться, а первая строка внешней ссылки должна быть переоценена.

type

Тип указывает, как MySQL находит нужную строку в таблице, также известный как "тип доступа". Обычно используются следующие типы:

ВСЕ, индекс, диапазон, ссылка, eq_ref, константа, система, NULL.

Слева направо производительность варьируется от плохой до хорошей.

ВСЕ: Полное сканирование таблицы, MySQL просматривает всю таблицу, чтобы найти совпадающие строки.
показатель: Полное сканирование индекса, разница между индексом и ALL заключается в том, что тип индекса проходит только по дереву индекса.
диапазон: Получить только заданный диапазон строк, используя индекс для выбора строк.
ссылка: Указывает условие соответствия соединения для приведенной выше таблицы, то есть какие столбцы или константы используются для поиска значения в индексированном столбце.
eq_ref: Как и в случае со ссылкой, разница заключается в том, что используемый индекс является уникальным индексом. Для каждого значения ключа индекса соответствует только одна запись в таблице. Короче говоря, первичный ключ или уникальный ключ используется в качестве условия ассоциации в мультитаблице. связь.
константа: Эти типы доступа используются, когда MySQL оптимизирует некоторую часть запроса и преобразует его в константу. Если первичный ключ помещен в список where, MySQL может преобразовать запрос в константу.
НУЛЕВОЙ: MySQL декомпозирует оператор в процессе оптимизации, и ему даже не требуется доступ к таблице или индексу во время выполнения.Например, выбор минимального значения из столбца индекса может быть выполнен с помощью отдельного поиска индекса.

Key

Столбец ключа показывает ключ (индекс), который MySQL фактически решил использовать, если индекс не был выбран, ключ равен NULL.

possible_keys

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

ref

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

rows

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

объявить НЕ НУЛЕВЫМ

После того, как столбец данных объявлен как NOT NULL, нет необходимости судить, является ли он NULL при запросе.Поскольку оценка уменьшается, сложность может быть уменьшена, а скорость запроса может быть повышена.

Если вы хотите указать, что столбец данных пуст, вы можете вместо этого использовать 0 и т. д.

Рассмотрите возможность использования числовых типов вместо строк

MySQL обрабатывает числовые типы намного быстрее, чем строки, и числовые типы, как правило, более эффективны в пространстве.

Например, вместо «Мужской» и «Женский» можно использовать «0» и «1».

Рассмотрите возможность использования типа ENUM

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

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');

SELECT name, size FROM shirts WHERE size = 'medium';

Суммировать

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

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

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