СУБД. Лекция 5

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

Индексы используются для:

Быстрого поиска записей по условию WHERE;
Для объединения таблиц с посредством JOIN. Необходимо использовать одинаковые типы сравниваемых полей.bo Если для сравнения необходимо произвести преобразование типов, то индексы использоваться не будут;
Для выбора наименьшего количества совпадений. Если есть множественный индекс, то использоваться будет тот индекс, который находит самое маленький число строк;
Поиска MAX и MIN значений для ключевых полей;
Для сортировки и группировки таблиц (……ORDER BY и GROUP BY);
Для извлечения данных не из таблицы с данными, а из индексного файла. Это возможно только в некоторых случаях, например, когда все извлекаемые поля проиндексированы.

Индексирование B-tree

Индексирование B-tree (пример)

CREATE TABLE people (
	last_name VARCHAR(50) NOT NULL,
	first_name VARCHAR(50) NOT NULL,
	dob DATE NOT NULL,
	gender ENUM(m, f) NOT NULL,
	KEY (last_name, first_name, dob)
);

Индексирование B-tree (особенности)

Можно:

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

Нельзя:

Поиск без использования левой части ключа;
Нельзя пропускать столбцы;
Оптимизация после поиска в диапазоне.

Индексирование ХЕШ-индексы

CREATE TABLE testhash (
	fname VARCHAR(50) NOT NULL,
	lname VARCHAR(50) NOT NULL,
	KEY USING HASH (fname)
) ENGINE=MEMORY;

fname	lname
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkachenko

Индексирование ХЕШ-индексы


f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

Ячейка	Значение
2323	Указатель на строку 1
2458	Указатель на строку 4
7437	Указатель на строку 2
8784	Указатель на строку 3

SELECT lname FROM testhash WHERE fname = 'Peter';

Индексирование B-tree (особенности)

MySQL не может использовать данные в индексе, чтобы избежать чтения строк.
MySQL не может использовать хеш-индексы для сортировки, поскольку строки в нем не хранятся в отсортированном порядке.
Хеш-индексы не поддерживают поиск по частичному ключу, так как хеш-коды вычисляются для всего индексируемого значения.
Хеш-индексы поддерживают только сравнения на равенство, использующие операторы =, IN() и <=>.
Доступ к данным в хеш-индексе очень быстр, если нет большого количества коллизий.
Некоторые операции обслуживания индекса могут оказаться медленными, если количество коллизий велико.

Индексирование. Специфика MySQL

MySQL для регистронезависимого сравнения использует COLLATIONS (например: utf8_general_ci):
http://collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html
В MySQL нет разницы между UNIQUE INDEX и UNIQUE CONSTRAINT.
MySQL автоматически создаёт индексы для FOREIGN KEY, но некоторые СУБД этого не делают (например: PostgreSQL).

Профилирование

К каким данным MySQL обращается чаще всего?
Какие типы запросов MySQL выполняет чаще всего?
В каких состояниях преимущественно находятся потоки (threads) MySQL?
Какие подсистемы MySQL чаще всего использует для выполнения запросов?
Какие виды обращения к данным встречаются наиболее часто?
Сколько различных видов действий, например просмотра индексов, выполняет MySQL?

Протоколирование запросов

Общий журнал

general_log_file = <имя_файла>

Журнал медленных запросов

slow_query_log_file = <имя_файла>
slow_query_log = 1
long_query_time = 2.5
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_throttle_queries_not_using_indexes = 60


# Time: 121018 9:47:00
# User@Host: root[root] @ localhost []
# Query_time: 0.000652 Lock_time: 0.000109 Rows_sent: 50
# Rows_examined: 3268
SELECT ...

Протоколирование запросов

Таблица могла быть заблокирована, поэтому запрос был вынужден ждать. Величина Lock_time показывает, как долго запрос ждал освобождения блокировки.
Данные или индексы могли к тому моменту еще отсутствовать в кэше. Это обычный случай, когда сервер MySQL только запущен или не настроен должным образом.
Мог идти ночной процесс резервного копирования, из-за чего все операции дискового ввода/вывода замедлялись.
Сервер мог обрабатывать в тот момент другие запросы, поэтому данный выполнялся медленнее.

Протоколирование запросов

Долго выполняющиеся запросы

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

Запросы, больше всего нагружающие сервер

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

Новые запросы

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

Протоколирование запросов

SHOW STATUS

Bytes_received и Bytes_sent: Количество байтов, соответственно полученных и отправленных сервером.
Com_*: Команды, которые сервер выполняет.
reated_*: Временные таблицы и файлы, созданные во время выполнения запроса.
Handler_*: Операции подсистемы хранения.
Select_*: Различные типы планов выполнения операции соединения.
Sort_*: Разнообразная информация о сортировке.

Протоколирование запросов

SET profiling = 1;

SELECT SQL_NO_CACHE "movie"."mID", COUNT(*)
FROM "movie"
	INNER JOIN "rating" USING ("mID")
GROUP BY "movie"."mID"
ORDER BY COUNT(*) DESC;

SHOW PROFILES\G
******************** 1. row  *********************
Query_ID: 1
Duration: 0.02596900
Query: SELECT …

EXPLAIN

EXPLAIN ничего не говорит о том, как влияют на запрос триггеры, хранимые и пользовательские (UDF) функции.
Она не работает с хранимыми процедурами, хотя можно разложить процедуру на отдельные запросы и вызвать EXPLAIN для каждого из них.
Она ничего не говорит об оптимизациях, которые MySQL производит уже на этапе выполнения запроса.
Часть отображаемой статистической информации – всего лишь оценка, иногда очень неточная.
Она не показывает все, что можно было бы сообщить о плане выполнения запроса.
Она не делает различий между некоторыми операциями, называя их одинаково.

JOIN-стратегии

MERGE JOIN: Соединение двух отсортированных последовательностей.
Работает быстро и за один проход обоих списков.
HASH JOIN: Меньшее отношение помещается в хэш-таблицу. Затем для каждой строки из большей таблицы выполняется поиск значений, соответствующих условию соединения.
Соединение только по условию эквивалентности.
NEASTED LOOP: Соединение вложенными циклами.

EXPLAIN (графическое представление)

Столбцы EXPLAIN: id

EXPLAIN SELECT (SELECT 1 FROM sakila.actor LIMIT 1) FROM sakila.film;

Id	select_type	table
1	PRIMARY	film
2	SUBQUERY	actor

EXPLAIN SELECT film_id
FROM (SELECT MAX(film_id) as film_id FROM sakila.film) AS der;

Id	select_type	table
1	PRIMARY	<derived2>
2	DERIVED

EXPLAIN SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;

Id	select_type	table
1	PRIMARY
2	UNION
3	UNION

Столбцы EXPLAIN: select_type

PRIMARY: Самый внешний запрос.
SUBQUERY: Запрос SELECT, который содержится в подзапросе, находящемся во фразе SELECT (иными словами, не во фразе FROM).
DERIVED: Значение DERIVED означает, что данный запрос SELECT является подзапросом во фразе FROM.
UNION: Второй и последующий запросы SELECT, входящие в объединение UNION, помечаются признаком UNION.
UNION RESULT: Запрос SELECT, применяемый для выборки результатов из временной таблицы, созданной в ходе выполнения UNION.

Столбцы EXPLAIN: table

EXPLAIN SELECT film.film_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING (film_id)
	INNER JOIN sakila.actor USING (actor_id);

Id	select_type	table
1	SIMPLE	actor
2	SIMPLE	film_actor
3	SIMPLE	film

Столбцы EXPLAIN: table

EXPLAIN SELECT
	actor_id,
	(SELECT 1 FROM sakila.film_actor
	          WHERE film_actor.actor_id = der_1.actor_id LIMIT 1)
FROM (
	SELECT actor_id
	FROM sakila.actor LIMIT 5
) AS der_1
UNION ALL
SELECT film_id,
(SELECT @var1 FROM sakila.rental LIMIT 1)
FROM (
	SELECT film_id,
	(SELECT 1 FROM sakila.store LIMIT 1)
	FROM sakila.film LIMIT 5
) AS der_2;

Столбцы EXPLAIN: table

Id	select_type	table
1	PRIMARY	<derived3>
3	DERIVED	actor
2	DEPENDENT SUBQUERY	film_actor
4	UNION	<derived6>
6	DERIVED	film
7	SUBQUERY	store
5	UNCACHEABLE SUBQUERY	rental

Столбцы EXPLAIN: table

Столбцы EXPLAIN: type

ALL: Этот подход обычно называют сканированием таблицы.
index: То же, что сканирование таблицы, только MySQL просматривает ее в порядке, задаваемом индексом, а не в порядке следования строк.
range: Просмотр диапазона – это ограниченная форма сканирования индекса. Просмотр начинается в определенной точке индекса и возвращает строки в некотором диапазоне значений.
ref: Это доступ по индексу (иногда он называется поиском по индексу (index lookup)), в результате которого возвращаются строки, соответствующие единственному заданному значению.
eq_ref: Это поиск по индексу в случае, когда MySQL точно знает, что будет возвращено не более одного значения.
const, system: Эти типы доступа MySQL применяет, когда в процессе оптимизации какую-то часть запроса можно преобразовать в константу.
NULL: Этот метод означает, что MySQL сумела разрешить запрос на фазе оптимизации, так что в ходе выполнения вообще не потребуется обращаться к таблице или индексу.

Столбцы EXPLAIN: possible_keys, key

EXPLAIN SELECT actor_id, film_id FROM sakila.film_actor\G

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: film_actor
type: index
possible_keys: NULL
key: idx_fk_film_id
key_len: 2
ref: NULL
rows: 5143
Extra: Using index

Столбцы EXPLAIN: possible_keys, key

EXPLAIN SELECT actor_id, film_id
FROM sakila.film_actor WHERE actor_id=4; 

...| type | possible_keys | key     | key_len |...
...| ref  | PRIMARY       | PRIMARY | 2       |...


CREATE TABLE t (
    a char(3) NOT NULL,
    b int(11) NOT NULL,
    c char(1) NOT NULL,
    PRIMARY KEY (a,b,c)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
EXPLAIN SELECT a FROM t WHERE a = 'sak' AND b = 112;


... |type | possible_keys | key     | key_len |...
... | ref | PRIMARY       | PRIMARY | 13      |...

Столбцы EXPLAIN: ref

EXPLAIN SELECT STRAIGHT_JOIN f.film_id
FROM sakila.film AS f
    INNER JOIN sakila.film_actor AS fa
        ON f.film_id=fa.film_id AND fa.actor_id = 1
    INNER JOIN sakila.actor AS a USING(actor_id);

...| table |...| key                | key_len | ref                    |...
...| a     |...| PRIMARY            | 2       | const                  |...
...| f     |...| idx_fk_language_id | 1       | NULL                   |...
...| fa    |...| PRIMARY            | 4       | const,sakila.f.film_id |...

Столбцы EXPLAIN: rows

EXPLAIN SELECT f.film_id
FROM sakila.film AS f
    INNER JOIN sakila.film_actor AS fa USING(film_id)
    INNER JOIN sakila.actor AS a USING(actor_id);

...| rows |...
...|  200 |...
...|   13 |...
...|    1 |...

Столбцы EXPLAIN: extra

Using index: Означает, что MySQL воспользуется покрывающим индексом, чтобы избежать доступа к самой таблице.
Using where: Означает, что сервер произведет дополнительную фильтрацию строк, отобранных подсистемой хранения.
Using temporary: Означает, что MySQL будет применять временную таблицу для сортировки результатов запроса.
Using filesort: Означает, что MySQL прибегнет к обычной сортировке для упорядочения результатов, а не станет читать строки из таблицы в порядке, задаваемом индексом.
range checked for each record: Означает, что подходящего индекса не нашлось, поэтому сервер будет заново искать индекс при обработке каждой строки в операции соединения.

Индексирование: изоляция столбца

SELECT ... FROM sakila.actor
WHERE actor_id + 1 = 5;

SELECT ...
WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;


SELECT ...
WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);


SELECT ...
WHERE date_col >= DATE_SUB(2008-01-17, INTERVAL 10 DAY);

Индексирование: кластерные индексы

Индексирование: кластерные индексы (+)

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

Индексирование: кластерные индексы (-)

Если данные помещаются в памяти, то порядок доступа к ним не имеет значения, и тогда кластерные индексы не принесут большой пользы.
Если вы загружаете большое количество данных в другом порядке, то по окончании загрузки имеет смысл реорганизовать таблицу с помощью команды OPTIMIZE TABLE.
Обновление столбцов кластерного индекса обходится дорого, поскольку InnoDB вынуждена перемещать каждую обновленную строку в новое место.
Для таблиц с кластерным индексом вставка новых строк или обновление первичного ключа, требующее перемещения строки, может приводить к расщеплению страницы.
Полное сканирование кластерных таблиц может оказаться более медленным, особенно если строки упакованы менее плотно или хранятся непоследовательно из-за расщепления страниц.
Вторичные (некластерные) индексы могут оказаться больше, чем вы ожидаете, поскольку в листовых узлах хранятся значения столбцов, составляющих первичный ключ.
Для доступа к данным по вторичному индексу требуется просмотр двух индексов вместо одного.

Размещение данных в MyISAM

CREATE TABLE layout_test (
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),
	KEY(col2)
);

Размещение данных в MyISAM

CREATE TABLE layout_test (
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),
	KEY(col2)
);

Размещение данных в InnoDB

CREATE TABLE layout_test (
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),
	KEY(col2)
);

Размещение данных в InnoDB

CREATE TABLE layout_test (
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),
	KEY(col2)
);

Размещение данных

CREATE TABLE layout_test (
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),
	KEY(col2)
);

INSERT: Сурогатный ключ

INSERT: Натуральный ключ

Покрывающие индексы

EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index

Покрывающие индексы

EXPLAIN SELECT actor_id, last_name
FROM sakila.actor WHERE last_name = 'HOPPER'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index

Покрывающие индексы

CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

EXPLAIN SELECT rental_id FROM sakila.rental
WHERE rental_date = '2005-05-25'
ORDER BY inventory_id, customer_id\G

*************************** 1. row ***************************
         type: ref
possible_keys: rental_date
          key: rental_date
         rows: 1
        Extra: Using where; Using index

Покрывающие индексы

CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

EXPLAIN SELECT rental_id FROM sakila.rental
WHERE rental_date = '2005-05-25'
ORDER BY inventory_id DESC\G

*************************** 1. row ***************************
         type: ref
possible_keys: rental_date
          key: rental_date
         rows: 1
        Extra: Using where; Using index

Покрывающие индексы

CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

EXPLAIN SELECT rental_id FROM sakila.rental
WHERE rental_date = '2005-05-25'
ORDER BY inventory_id DESC, customer_id ASC\G

*************************** 1. row ***************************
         type: ref
possible_keys: rental_date
          key: rental_date
         rows: 1
        Extra: Using where; Using index; Using filesort

Покрывающие индексы

CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

EXPLAIN SELECT rental_id FROM sakila.rental
WHERE rental_date = '2005-05-25'
ORDER BY inventory_id, staff_id\G

*************************** 1. row ***************************
         type: ref
possible_keys: rental_date
          key: rental_date
         rows: 1
        Extra: Using index condition; Using filesort

Покрывающие индексы

CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

EXPLAIN SELECT rental_id FROM sakila.rental
WHERE rental_date = '2005-05-25'
ORDER BY rental_date, inventory_id\G

*************************** 1. row ***************************
         type: ref
possible_keys: rental_date
          key: rental_date
         rows: 1
        Extra: Using where; Using index

Покрывающие индексы

CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

EXPLAIN SELECT rental_id FROM sakila.rental
WHERE rental_date = '2005-05-25'
ORDER BY customer_id\G

*************************** 1. row ***************************
         type: ref
possible_keys: rental_date
          key: rental_date
         rows: 1
        Extra: Using where; Using index; Using filesort

Покрывающие индексы

CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

EXPLAIN SELECT rental_id FROM sakila.rental
WHERE rental_date = '2005-05-25' AND inventory_id IN (1, 2)
ORDER BY customer_id\G

*************************** 1. row ***************************
         type: range
possible_keys: rental_date,idx_fk_inventory_id
          key: rental_date
         rows: 2
        Extra: Using where; Using index; Using filesort