- PVSM.RU - https://www.pvsm.ru -
Когда вы выполняете какой-нибудь запрос, оптимизатор запросов MySQL пытается придумать оптимальный план выполнения этого запроса. Вы можете посмотреть этот самый план используя запрос с ключевым словом EXPLAIN. EXPLAIN – это один из самых мощных инструментов, предоставленных в ваше распоряжение для понимания MySQL-запросов и их оптимизации, но печальным фактом является то, что многие разработчики редко его используют. В данной статье вы узнаете о том, какие данные предлагает EXPLAIN на выходе и ознакомитесь с примером того, как использовать его для оптимизации запросов.
Использовать оператор EXPLAIN просто. Его необходимо добавлять в запросы перед оператором SELECT. Давайте проанализируем вывод, чтобы познакомиться с информацией возвращаемой командой.
********************** 1. row **********************
id: 1
select_type: SIMPLE
table: categories
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra:
1 row in set (0.00 sec)
Вывод может не выглядеть точь-в-точь так, тем не менее, в нем будут содержаться те же 10 столбцов. Что же это за возвращаемые столбцы?
После EXPLAIN в запросе вы можете использовать ключевое слово EXTENDED и MySQL покажет вам дополнительную информацию о том, как выполняется запрос. Чтобы увидеть эту информацию, вам нужно сразу после запроса с EXTENDED выполнить запрос SHOW WARNINGS. Наиболее полезно смотреть эту информацию о запросе, который выполнялся после каких-либо изменений сделанных оптимизатором запросов.
EXPLAIN EXTENDED SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
********************** 1. row **********************
id: 1
select_type: SIMPLE
table: Country
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
filtered: 100.00
Extra:
********************** 2. row **********************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
SHOW WARNINGS
********************** 1. row **********************
Level: Note
Code: 1003
Message: select `World`.`City`.`Name` AS `Name` from `World`.`City` join `World`.`Country` where ((`World`.`City`.`CountryCode` = 'IND'))
1 row in set (0.00 sec)
Теперь давайте посмотрим на то, как мы может оптимизировать не очень шустрый запрос, анализируя вывод команды EXPLAIN. Несомненно, что в действующих рабочих приложениях существует ряд таблиц со многими связями между ними, но иногда сложно предвидеть наиболее оптимальный способ написания запроса.
Я создал тестовую базу данных для приложения электронной торговли, которая не имеет никаких индексов или первичных ключей, и продемонстрирую влияние такого не очень хорошего способа создания таблиц при помощи “страшных” запросов. Дамп это таблицы вы можете скачать здесь — github.com/phpmasterdotcom/UsingExplainToWriteBetterMySQLQueries [2]
EXPLAIN SELECT * FROM
orderdetails d
INNER JOIN orders o ON d.orderNumber = o.orderNumber
INNER JOIN products p ON p.productCode = d.productCode
INNER JOIN productlines l ON p.productLine = l.productLine
INNER JOIN customers c on c.customerNumber = o.customerNumber
WHERE o.orderNumber = 10101
Если вы посмотрите на результат (на него вам придется посмотреть только в примере ниже, по ссылке выше лежит дамп с уже добавленными ключами), то увидите все симптомы плохого запроса. Но даже если я напишу запрос получше, результат будет тем же самым, пока я не добавлю индексов. Указанный тип соединения ALL (худший), что означает, что MySQL не смог определить ни одного ключа, который бы мог использоваться при соединении. Отсюда следует и то, что possible_keys и key имеют значение NULL. Самым важным является то, что поле rows показывает, что MySQL сканирует все записи каждой таблицы для запроса. Это означает, что она просканирует 7 × 110 × 122 × 326 × 2996 = 91,750,822,240 записей, чтобы найти подходящие четыре (уберите из запроса EXPLAIN, проверьте сами). Это очень нехорошо и количество этих записей будет экспоненциально увеличиваться по мере роста базы данных.
Теперь давайте добавим очевидные индексы, такие, как первичный ключ для каждой таблицы, и выполним запрос еще раз. Взяв это за основное правило, в качестве кандидатов для добавления ключей вы можете использовать те столбцы которые используются в JOIN’ах, т.к. MySQL всегда сканирует их для нахождения соответствующих записей.
ALTER TABLE customers
ADD PRIMARY KEY (customerNumber);
ALTER TABLE employees
ADD PRIMARY KEY (employeeNumber);
ALTER TABLE offices
ADD PRIMARY KEY (officeCode);
ALTER TABLE orderdetails
ADD PRIMARY KEY (orderNumber, productCode);
ALTER TABLE orders
ADD PRIMARY KEY (orderNumber),
ADD KEY (customerNumber);
ALTER TABLE payments
ADD PRIMARY KEY (customerNumber, checkNumber);
ALTER TABLE productlines
ADD PRIMARY KEY (productLine);
ALTER TABLE products
ADD PRIMARY KEY (productCode),
ADD KEY (buyPrice),
ADD KEY (productLine);
ALTER TABLE productvariants
ADD PRIMARY KEY (variantId),
ADD KEY (buyPrice),
ADD KEY (productCode);
Давайте выполним наш прежний запрос после добавления индексов. Вы увидите это:
********************** 1. row **********************
id: 1
select_type: SIMPLE
table: o
type: const
possible_keys: PRIMARY,customerNumber
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
********************** 2. row **********************
id: 1
select_type: SIMPLE
table: c
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
********************** 3. row **********************
id: 1
select_type: SIMPLE
table: d
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 4
Extra:
********************** 4. row **********************
id: 1
select_type: SIMPLE
table: p
type: eq_ref
possible_keys: PRIMARY,productLine
key: PRIMARY
key_len: 17
ref: classicmodels.d.productCode
rows: 1
Extra:
********************** 5. row **********************
id: 1
select_type: SIMPLE
table: l
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: classicmodels.p.productLine
rows: 1
Extra:
5 rows in set (0.00 sec)
После добавления индексов, число считанных записей упало до 1 × 1 × 4 × 1 × 1 = 4 Для каждой записи order_number = 10101 в таблице orderdetails – это значит, что MySQL смогла найти соответствующие записи во всех других таблицах с использованием индексов и не стала прибегать к полному сканированию таблицы.
В первом выводе вы можете что использован тип соединения – “const”, который является самым быстрым типом соединения для таблиц с более, чем одной записью. MySQL смогла использовать PRIMARY KEY как индекс. В поле “ref” отображается “const”, что есть ни что иное, как значение 10101, указанное в запросе после ключевого слова WHERE.
Смотрим на еще один запрос. В нем мы выбираем объединение двух таблиц, products и productvariants, каждая объединена с productline. productvariants, которая состоит из разных вариантов продуктов с полем productCode – ссылкой на их цены.
EXPLAIN SELECT * FROM (
SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM
products p
INNER JOIN productlines l ON p.productLine = l.productLine
UNION
SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM productvariants v
INNER JOIN products p ON p.productCode = v.productCode
INNER JOIN productlines l ON p.productLine = l.productLine
) products
WHERE status = 'Active' AND lineStatus = 'Active' AND buyPrice BETWEEN 30 AND 50
********************** 1. row **********************
id: 1
select_type: PRIMARY
table: type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 219
Extra: Using where
********************** 2. row **********************
id: 2
select_type: DERIVED
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 110
Extra:
********************** 3. row **********************
id: 2
select_type: DERIVED
table: l
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: classicmodels.p.productLine
rows: 1
Extra:
********************** 4. row **********************
id: 3
select_type: UNION
table: v
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 109
Extra:
********************** 5. row **********************
id: 3
select_type: UNION
table: p
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 17
ref: classicmodels.v.productCode
rows: 1
Extra:
********************** 6. row **********************
id: 3
select_type: UNION
table: l
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: classicmodels.p.productLine
rows: 1
Extra:
********************** 7. row **********************
id: NULL
select_type: UNION RESULT
table: <union2,3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
7 rows in set (0.01 sec)
Вы можете заметить ряд проблем в этом запросе. Он сканирует все записи в products и productvarians. Т.к. в этих таблицах нет индексов для столбцов productLine и buyPrice, в полях possible_keys и key отображаются значения NULL. Статус таблиц products и productlines проверяется после UNION’а, поэтому перемещение их внутри UNION’а уменьшит число записей. Добавим индексы.
CREATE INDEX idx_buyPrice ON products(buyPrice);
CREATE INDEX idx_buyPrice ON productvariants(buyPrice);
CREATE INDEX idx_productCode ON productvariants(productCode);
CREATE INDEX idx_productLine ON products(productLine);
EXPLAIN SELECT * FROM (
SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status as lineStatus FROM products p
INNER JOIN productlines AS l ON (p.productLine = l.productLine AND p.status = 'Active' AND l.status = 'Active')
WHERE buyPrice BETWEEN 30 AND 50
UNION
SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status FROM productvariants v
INNER JOIN products p ON (p.productCode = v.productCode AND p.status = 'Active')
INNER JOIN productlines l ON (p.productLine = l.productLine AND l.status = 'Active')
WHERE
v.buyPrice BETWEEN 30 AND 50
) product
********************** 1. row **********************
id: 1
select_type: PRIMARY
table: type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra:
********************** 2. row **********************
id: 2
select_type: DERIVED
table: p
type: range
possible_keys: idx_buyPrice,idx_productLine
key: idx_buyPrice
key_len: 8
ref: NULL
rows: 23
Extra: Using where
********************** 3. row **********************
id: 2
select_type: DERIVED
table: l
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: classicmodels.p.productLine
rows: 1
Extra: Using where
********************** 4. row **********************
id: 3
select_type: UNION
table: v
type: range
possible_keys: idx_buyPrice,idx_productCode
key: idx_buyPrice
key_len: 9
ref: NULL
rows: 1
Extra: Using where
********************** 5. row **********************
id: 3
select_type: UNION
table: p
type: eq_ref
possible_keys: PRIMARY,idx_productLine
key: PRIMARY
key_len: 17
ref: classicmodels.v.productCode
rows: 1
Extra: Using where
********************** 6. row **********************
id: 3
select_type: UNION
table: l
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: classicmodels.p.productLine
rows: 1
Extra: Using where
********************** 7. row **********************
id: NULL
select_type: UNION RESULT
table: <union2,3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
7 rows in set (0.01 sec)
Как вы видите, в результате количество сканированных строк уменьшилось с 2,625,810 (219 × 110 × 109) до 276 (12 × 23), что является отличным приобретением в производительности. Если вы выполните этот же запрос без предыдущих перестановок в запросе сразу после добавления индексов, вы не увидите такого уменьшения просканированных строк. MySQL не способна использовать индексы, когда в производном результате используется WHERE. После помещения этих условий внутри UNION становится возможных использование индексов. Это значит, что добавления индексов не всегда достаточно. MySQL не сможет их использовать до тех пор, пока вы не будете писать подходящие запросы. (http://www.php.su/mysql/manual/?page=MySQL_indexes – доп. информация).
В статье рассмотрено ключевое слово EXPLAIN, информация на выводе и примеры того, как вы можете использовать вывод команды для улучшения запросов. В реальном мире данная команда может быть более полезна, чем в рассмотренных сценариях. Почти всегда вы будете соединять ряд таблиц вместе, используя сложные конструкции с WHERE. При этом, просто добавленные индексы к таблицам не всегда приведут к нужному результату. В таком случае нужно пересмотреть ваши запросы.
Автор: hbuser
Источник [3]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/mysql/54018
Ссылки в тексте:
[1] dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information: http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information
[2] github.com/phpmasterdotcom/UsingExplainToWriteBetterMySQLQueries: https://github.com/phpmasterdotcom/UsingExplainToWriteBetterMySQLQueries
[3] Источник: http://habrahabr.ru/post/211022/
Нажмите здесь для печати.