Индекс высокой производительности mysql

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

• Так что же такое индекс?
• Как работает индекс?
• В чем разница между тем, что мы обычно называем кластерным индексом, и некластеризованным индексом?
• Как создавать и использовать индексы?

I. Введение в Индекс

Официальное определение индекса в MySQL состоит в том, что индекс — это структура данных, которая помогает MySQL эффективно получать данные. Короче говоря, индекс — это структура данных.

1. Несколько древовидных структур

а) B+ дерево

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

б. Двоичное дерево

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

C. Сбалансированное бинарное дерево

Первое — это бинарное дерево, но разница в высоте между левым и правым дочерними узлами любого узла должна быть не более 1.

г. B-дерево

Первое — это сбалансированное бинарное дерево, но оно требует одинакового расстояния от каждого конечного узла до корневого узла.

Так в чем же разница между деревом B и деревом B+?

• Листовой узел дерева B+ может содержать указатель на другой листовой узел.
• Копия значения ключа дерева B+ существует в нелистовом узле; значение ключа + запись хранится в листовом узле.

2. B+ дерево движка InnoDB

Дерево B+, используемое движком InnnoDB для mysql, только листовые узлы хранят соответствующие столбцы данных, имеет следующие преимущества.

• Листовые узлы обычно содержат больше записей и имеют высокую разветвленность (можно понять, что каждый узел соответствует большему количеству узлов более низкого уровня), поэтому высота дерева мала (3~4), а высота дерева также определяет количество затронутых дисковых операций ввода-вывода, что влияет на производительность базы данных. В целом количество операций ввода-вывода соответствует высоте дерева.
• Для комбинированного индекса индекс B+tree сортируется в соответствии с именем столбца индекса (слева направо), поэтому случайный ввод-вывод может быть преобразован в последовательный ввод-вывод для повышения эффективности ввода-вывода; и он может поддерживать требования сортировки, такие как порядок по \ группа; Подходит для запросов диапазона

3. хэш-индекс

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

• Могут быть удовлетворены только запросы "=","IN" и "", а запросы диапазона не могут использоваться.
  • Поскольку вычисление выполняется по хеш-значению, его можно только точно запросить.Хеш-значение не имеет регулярности, и нельзя гарантировать, что порядок будет таким же, как исходный, поэтому запрос диапазона использовать нельзя.
• Не могу отсортировать
  • Причина та же, что и выше
• Частичные индексы не поддерживаются
  • Расчет хеш-значения основан на нескольких полных столбцах индекса. Если один или несколько из них отсутствуют, хэш-значение не может быть вычислено.
• хэш-коллизия

4. Кластеризованные и некластеризованные индексы

а. Кластерный индекс

Файл данных самой InnoDB является индексным файлом, данные на листовом узле B+Tree — это сами данные, ключ — это первичный ключ, нелистовые узлы хранят , а адрес — это адрес следующего слоя

Структурная схема кластеризованного индекса:

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

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

Схема структуры некластеризованного индекса:

Из структуры некластеризованного индекса мы можем увидеть процесс позиционирования в этом сценарии:

• Сначала найдите соответствующий конечный узел через некластеризованный индекс и найдите соответствующий первичный ключ.
• В соответствии с первичным ключом, найденным выше, в кластеризованном индексе найдите соответствующий конечный узел (получите данные).

5. Преимущества индексации

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

6. Область применения

Индексы подходят не для каждой ситуации. Подходит для средних и больших столов. Более эффективное сканирование полных таблиц для небольших таблиц. А для очень больших таблиц рассмотрите методы «разделения».

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

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

1. Несколько синтаксисов индекса

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

-- 创建索引
create index `idx_img` on newuser(`img`);

-- 查看
show create table newuser\G;

вывод

show create table newuser\G
*************************** 1. row ***************************
       Table: newuser
Create Table: CREATE TABLE `newuser` (
  `userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(30) DEFAULT '' COMMENT '用户登录名',
  `nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称',
  `password` varchar(50) DEFAULT '' COMMENT '用户登录密码 & 密文根式',
  `address` text COMMENT '用户地址',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '用户邮箱',
  `phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户手机号',
  `img` varchar(100) DEFAULT '' COMMENT '用户头像',
  `extra` text,
  `isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  PRIMARY KEY (`userId`),
  KEY `idx_username` (`username`),
  KEY `idx_nickname` (`nickname`),
  KEY `idx_email` (`email`),
  KEY `idx_phone` (`phone`),
  KEY `idx_img` (`img`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Еще один распространенный способ добавления индексов

alter table newuser add index `idx_extra_img`(`isDeleted`, `img`);

-- 查看索引
show index from newuser;

выходной результат

+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newuser |          0 | PRIMARY       |            1 | userId      | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_username  |            1 | username    | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| newuser |          1 | idx_nickname  |            1 | nickname    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_email     |            1 | email       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_phone     |            1 | phone       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_img       |            1 | img         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| newuser |          1 | idx_extra_img |            1 | isDeleted   | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_extra_img |            2 | img         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

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

drop index `idx_extra_img` on newuser;
drop index `idx_img` on newuser;

-- 查看索引
show index from newuser;

вывод

show index from newuser;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newuser |          0 | PRIMARY      |            1 | userId      | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_username |            1 | username    | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| newuser |          1 | idx_nickname |            1 | nickname    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_email    |            1 | email       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| newuser |          1 | idx_phone    |            1 | phone       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Способ заставить индекс

грамматика:select * from table force index(索引) where xxx

explain select * from newuser force index(PRIMARY) where userId not in (3, 2, 5);
-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
-- | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | range | PRIMARY       | PRIMARY | 8       | NULL |    4 | Using where |
-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+


explain select * from newuser where userId not in (3, 2, 5);
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

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

Когда в таблице есть несколько индексов, как вы определяете, достиг ли ваш sql индекс и какой это индекс?

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

Структура таблицы теста следующая

*************************** 1. row ***************************
       Table: newuser
Create Table: CREATE TABLE `newuser` (
  `userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(30) DEFAULT '' COMMENT '用户登录名',
  `nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称',
  `password` varchar(50) DEFAULT '' COMMENT '用户登录密码 & 密文根式',
  `address` text COMMENT '用户地址',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '用户邮箱',
  `phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户手机号',
  `img` varchar(100) DEFAULT '' COMMENT '用户头像',
  `extra` text,
  `isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  PRIMARY KEY (`userId`),
  KEY `idx_username` (`username`),
  KEY `idx_nickname_email_phone` (`nickname`,`email`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

а. Принцип сопоставления крайнего левого префикса

Это в основном для многостолбцовых некластеризованных индексов, таких как следующий индексidx_nickname_email_phone(nickname, email, phone), псевдоним определяется перед адресом электронной почты, поэтому следующие операторы соответствуют

-- 走索引
explain select * from newuser where nickname='小灰灰' and email='greywolf@xxx.com';

-- 1. 匹配nickname，可以走索引
explain select * from newuser where nickname='小灰灰';

-- 输出:
-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
-- | id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
-- |  1 | SIMPLE      | newuser | ref  | idx_nickname_email | idx_nickname_email | 92      | const |    1 | Using index condition |
-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+


-- 2. 虽然匹配了email, 但是不满足最左匹配，不走索引
explain select * from newuser where email='greywolf@xxx.com';

-- 输出
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

б. Нельзя пропустить столбец и использовать последующие столбцы индекса

показательidx_nickname_email_phone(nickname, email, phone), Если в вашем sql только ник и телефон, то телефон не может перейти в индекс, потому что вы не можете пропустить среднее письмо, чтобы перейти в индекс

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

Такие как >,

select * from newuser where nickname like '小灰%' and email='greywolf@xxx.com' and phone=15971112301 limit 10;

г. Столбцы как аргументы функции или как часть выражений

-- 走不到索引
explain select * from newuser where userId+1=2 limit 1;


-- 输出
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | newuser | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

3. Недостатки индексации

• Хотя индекс значительно повышает скорость выполнения запросов, он снижает скорость обновления таблицы, например операций INSERT, UPDATE и DELETE таблицы. Потому что при обновлении таблицы MySQL не только сохраняет данные, но и сохраняет индексный файл.
• Индексирование индексного файла, который занимает место на диске. В целом эта проблема не является серьезной, но если вы создадите несколько составных индексов для большой таблицы, файл индекса очень быстро разбухнет.

4. Меры предосторожности

• Индекс не будет содержать столбцов со значениями NULL
• использовать короткий индекс
• Порядок столбцов индекса
  • Запросы MySQL используют только один индекс, поэтому, если индекс уже используется в предложении where, столбцы в порядке не будут использовать индекс. Поэтому не используйте операцию сортировки, когда сортировка базы данных по умолчанию может удовлетворить требованиям; старайтесь не включать сортировку нескольких столбцов, при необходимости лучше всего создавать составные индексы для этих столбцов
• подобная операторная операция
  • В общем, использование подобной операции не рекомендуется, если она должна быть использована, то как ее использовать, также является проблемой. как "%aaa%" не будет использовать индекс, в то время как как "aaa%" будет использовать индекс
• Не работайте со столбцами
  • select * from users where YEAR(adddate)<2007;
• Старайтесь не использовать операции NOT IN и

5. стратегия использования sql

а. Используйте один sql вместо нескольких sql

Обычно рекомендуется использовать один sql вместо нескольких sql-запросов.

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

б. Разложение связанных запросов

Поместите соединение ассоциации как можно больше в приложение и попробуйте выполнить небольшой и простой sql

• Разложенный sql прост, что способствует использованию кеша mysql.
• Выполните разложенный sql, чтобы уменьшить конкуренцию блокировок.
• Лучшая масштабируемость и ремонтопригодность (sql simple)
• Связанный sql использует алгоритм вложенного цикла, и приложение может использовать такие структуры, как хэш-карта, для обработки данных, что более эффективно.

c. count

• count(*) подсчитывает количество строк
• count(имя столбца) подсчитывает количество столбцов, которые не являются нулевыми

d. limit

• ограничить смещение, размер; запрос на подкачку будет запрашивать смещение + размер фрагментов данных и получать фрагменты данных последнего размера

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

e. union

Ограничения «Где», «Упорядочить по» и «Ограничение» необходимо ввести в каждый подзапрос, чтобы повысить эффективность за счет повторной сегментации. Кроме того, если нет необходимости, максимально используйте Union all, потому что union добавит различное во временную таблицу каждого подзапроса и проверит уникальность каждой временной таблицы, что неэффективно.

6. MySQL-запрос

а. Просмотр указателя

-- 单位为GB
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';

б. Просмотрите табличное пространство

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size'   
FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName'; 

в) просмотреть информацию обо всех таблицах в базе данных

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',   
    table_rows AS 'Number of Rows',   
    CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',   
    CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,   
    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'  
FROM information_schema.TABLES   
WHERE table_schema LIKE 'databaseName';

IV. Другое

Ссылаться на

• Глубокое понимание Mysql - индекс высокой производительности и высокопроизводительный SQL

личный блог:Серый блог

Личный блог, основанный на страницах hexo+github, записывающий все посты блога в учебе и работе, добро пожаловать в гости

утверждение

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

• Адрес вейбо:Блог Маленького Серого
• QQ: серо-серый / 3302797840

Сканировать внимание