* MySQL EXPLAIN – оптимизиране на заявки
Публикувано на 06 април 2012 в раздел Бази от Данни.
Заявките от тип EXPLAIN се използват предимно от администраторите на бази от данни, за да видят т.нар. query execution plan (план за изпълнение на заявките). При слагането на префикс EXPLAIN преди дадена заявка тя не се изпълнява, а вместо това се извежда информация за това по какъв начин MySQL ще я изпълни. Преди версии 5.6 EXPLAIN може да се изпълнява само върху SELECT заявки. От версия 5.6 вече работят и за INSERT, UPDATE и DELETE.
Всяка заявка EXPLAIN връща като резултат таблица с 9 колони. Нека да разгледаме пълния им списък и възможните стойности, като дадем кратко описание:
- id – идентификационен номер на SELECT заявката. Вървят по ред започвайки от 1. Така може да разберете коя от вложените заявки се изпълнява първа, коя втора и т.н.
- select_type– Типа на SELECT заявката, който е един от следните:
- SIMPLE – проста SELECT заявка без вложени заявки или UNION.
- PRIMARY – най-външната (обрграждаща другите) заявка
- UNION – заявка, резултатът от която ще бъде обединен към друга
- DEPENDENT UNION – UNION заявка, която е вътрешна за друга заявка
- SUBQUERY – вложен SELECT
- DEPENDENT SUBQUERY – вложен SELECT, който е зависим от външни за него данни
- DERIVED – SELECT заявка във FROM условие
- table – име на таблицата
- type– тип на заявката
- CONST – указва, че таблицата има първичен или уникален ключ, който се използва в условие за сравнение. Това означава, че винаги ще има не повече от един ред в резултатната таблица от заявката, което впрочем прави такива заявки доста бързи – MySQL спира прелистването при намиране на съвпадение.
- SYSTEM – const заявка върху системна таблица
- EQ_REF – използва се UNIQUE и NOT NULL ключ. Това например може да се случи в JOIN заявка с класическа връзка 1:1. Това е най-бързият възможен JOIN, защото присъединяваната таблица винаги връща един ред и прелистването спира дотам.
- REF – използва се неуникален ключ без NULL стойности. Това например може да е връзка 1:M.
- REF_OR_NULL – използва се неуникален ключ, който може да има NULL стойности.
- INDEX_MERGE – специален случай, в който MySQL сам обединява индекси в един множествен. Обикновено се случва при наличието на условия OR в WHERE частта на заявката.
- UNIQUE_SUBQUERY – имаме вложен SELECT (извикан с оператор IN), който връща уникални стойности (без повторения).
- INDEX_SUBQUERY – същото като предишното, но вложения SELECT връща стойности с повторения.
- RANGE – използва се множество от стойности върху индекса. Например оператор BETWEEN, <, >, <=, >= и т.н.
- INDEX – указва, че ще бъде преровен ЦЕЛИЯТ index, т.е. ще се прелистят всичките му стойности.
- ALL – ще бъде направен FULL TABLE SCAN върху таблицата. Това е най-бавната възможна операция.
- possible_keys – възможните индекси, които MySQL може да използва за конкретната заявка.
- key – кой от възможните индекси е избран за конкретната заявка.
- key_len – колко байта от индекса ще се използват.
- rows – колко реда ще бъдат прелистени. Колкото по-малко, толкова по-добре.
- Extra– допълнителна информация.
- Distinct – сървъра ще направи оптимизация като спре да търси повече колони при наличие на първо съвпадение (т.е. дублиращите ще се пропуснат)
- Not exists – сървъра ще спре да търси повече съвпадения при първо такова в JOIN клауза.
- range checked for each record – сървърът не е намерил подходящ индекс, но въпреки това ще прави пълно обхождане на съществуващ такъв, вместо FULL TABLE SCAN.
- Using filesort – сървърът не използва индекс при сортирането.
- Using index – сървърът използва индекс при сортирането.
- Using temporary – ще се наложи създаване на временна таблица.
- Using where – в заявката е използвана WHERE клауза за лимитиране на изхода.
Преди да започнем с практически примери ще ни трябва база от данни с достатъчно голямо количество информация. Нека например вземем world-innodb, която е дадена като примерна на сайта на MySQL. Тя има следната структура:
CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America',
'Africa','Oceania','Antarctica',
'South America')
NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`)
REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
CREATE TABLE `countrylanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1`
FOREIGN KEY (`CountryCode`)
REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Нека разгледаме нашата първа елементарна заявка – искаме да изведем броят на държавите, които са с управление от тип ‘Republic’. Ето какво ще върне EXPLAIN на една такава заявка ако нямаме създадени никакви допълнителни индекси:
mysql> EXPLAIN SELECT COUNT(*)
-> FROM country
-> WHERE GovernmentForm = 'Republic';
+----+---------+------+---------------+------+---------+------+------+
| id | table | type | possible_keys | key | key_len | ref | rows |
+----+---------+------+---------------+------+---------+------+------+
| 1 | COUNTRY | ALL | NULL | NULL | NULL | NULL | 233 |
+----+---------+------+---------------+------+---------+------+------+
1 row in set (0.00 sec)
Първата важна колона, която трябва да разгледаме е „possible_keys“. Тя ни указва кои са индексите, които MySQL може да използва в заявката. В случая няма нито един индекс, който може да бъде използван. Съответно MySQL няма да използва никакъв индекс (стойността под key е NULL). Другата важна за нас колона е „rows“ – тя показва колко реда са били прегледани, преди да се достигне до желания резултат. В случая са били прелистени 233 реда.
Ако вие използвате SELECT заявка, която не използва Index, то много често това означава, че можете да подобрите бързодействието чрез създаване на такъв. Нека да видим:
ALTER TABLE country
ADD INDEX GovernmentFormIndex (GovernmentForm);
mysql> EXPLAIN SELECT COUNT(*)
-> FROM country
-> WHERE GovernmentForm = 'Republic';
+---------+------+---------------------+---------------------+---------+-------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+---------+------+---------------------+---------------------+---------+-------+------+
| COUNTRY | ref | GovernmentFormIndex | GovernmentFormIndex | 45 | const | 122 |
+---------+------+---------------------+---------------------+---------+-------+------+
Виждаме, че вече има възможен индекс, MySQL го е избрал и най-важното – прелистените редове са почти два пъти по-малко, отколкото преди – 122. Това в общия случай ще означава, че SQL заявката ще се изпълнява близо два пъти по-бързо.
Разбира се индексите силно зависят от данните, които се записват в тях и обемът от информация, който се използва. Ето ви друг пример – искаме да изведем броят на градовете, които имат население над 5000 души. Първата заявка ще изпълним без индекс, а втората с:
mysql> EXPLAIN SELECT COUNT(*)
-> FROM city
-> WHERE city.population > 5000;
+-------+------+---------------+------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+-------+------+---------------+------+---------+------+------+
| city | ALL | NULL | NULL | NULL | NULL | 4234 |
+-------+------+---------------+------+---------+------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE city
-> ADD INDEX population (population);
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT COUNT(*)
-> FROM city
-> WHERE city.population > 5000;
+-------+-------+---------------+------------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+-------+-------+---------------+------------+---------+------+------+
| city | range | population | population | 4 | NULL | 4048 |
+-------+-------+---------------+------------+---------+------+------+
1 row in set (0.00 sec)
Виждаме, че тук създадохме индекс, той се използва, но резултатът в не е значителен – имаме само малко намаление на броя прелистени редове. Това се получава така, понеже почти всички градове в базата от данни са с повече от 5000 души, т.е. ние използваме почти цялата таблица, което се свежда доста близко до обикновен full table scan. Нека променим условието и изведем броят на градовете с над 1 милион души:
mysql> ALTER TABLE city
-> DROP INDEX population;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT COUNT(*)
-> FROM city
-> WHERE city.population > 1000000;
+-------+------+---------------+------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+-------+------+---------------+------+---------+------+------+
| city | ALL | NULL | NULL | NULL | NULL | 4234 |
+-------+------+---------------+------+---------+------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE city
-> ADD INDEX population (population);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT COUNT(*)
-> FROM city
-> WHERE city.population > 1000000;
+-------+-------+---------------+------------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+-------+-------+---------------+------------+---------+------+------+
| city | range | population | population | 4 | NULL | 237 |
+-------+-------+---------------+------------+---------+------+------+
1 row in set (0.00 sec)
Тук вече виждаме огромна разлика от близо 18 пъти по-малко прелиствания! Или колкото по-ограничено е количеството на данни в резултатната таблица, толкова по-ефективен е индексът.
По-интересен е случаят, когато имаме логическа връзка между две колони. Нека изведем Европейските държави, които имат население над 40 милиона души:
mysql> EXPLAIN SELECT name
-> FROM country
-> WHERE population > 40000000
-> AND
-> Continent="Europe";
+---------+------+---------------+------+------+
| table | type | possible_keys | key | rows |
+---------+------+---------------+------+------+
| country | ALL | NULL | NULL | 233 |
+---------+------+---------------+------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE country
-> ADD INDEX populationIndex (population);
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE country
-> ADD INDEX continentIndex (Continent);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT name
-> FROM country
-> WHERE population > 40000000
-> AND
-> Continent="Europe";
+---------+------+--------------------------------+----------------+------+
| table | type | possible_keys | key | rows |
+---------+------+--------------------------------+----------------+------+
| country | ref | populationIndex,continentIndex | continentIndex | 46 |
+---------+------+--------------------------------+----------------+------+
1 row in set (0.00 sec)
Тук виждаме нещо интересно – има два възможни индекса, от които MySQL си е избрал един. Нека видим какъв ще бъде резултатът, ако използваме другия индекс (можем да принудим MySQL да го използва чрез командата USE INDEX след FROM):
mysql> EXPLAIN SELECT name
-> FROM country
-> USE INDEX(populationIndex)
-> WHERE population > 40000000
-> AND
-> Continent="Europe";
+---------+-------+-----------------+-----------------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+---------+-------+-----------------+-----------------+---------+------+------+
| country | range | populationIndex | populationIndex | 4 | NULL | 28 |
+---------+-------+-----------------+-----------------+---------+------+------+
1 row in set (0.00 sec)
Ясно се вижда, че в зависимост от заявката, използването на един или друг индекс ще доведе до различно бързодействие. Причината в случая от примера е, че индексът continentIndex „не е достатъчно уникален“, т.е. в колоната има доста повторения, докато при populationIndex повторенията са много малко.
Вече можем да си изведем първи два основни извода за оптимизиране на заявки:
- Колкото по-уникална (с различни стойности по редове) е една колона, толкова по-ефективни ще са индексите, които се правят по нея;
- При по-ограничен изход от заявките (т.е. извличат се по-малко редове от оригиналната в изходната таблица) се получава по-голяма разлика в бързодействието между използването на индекс и full table scan.
Обратно – ако трябва да изведем информация от всички редове в съществуваща таблица или по дадената колона имаме много повторения на стойности, то индексите започват да губят ефект.
Можем ли да използваме повече от един индекс върху една таблица? Отговорът за MySQL е отрицателен – не може. Това, което можем да направим обаче е да се възползваме от „множествени индекси“, т.е. индекси създадени по повече от една колона. Ето пример с предишната заявка:
mysql> ALTER TABLE country
-> ADD INDEX pop_cont_index (Continent, population);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT name
-> FROM country
-> USE INDEX(pop_cont_index)
-> WHERE population > 40000000
-> AND
-> Continent="Europe";
+---------+-------+----------------+----------------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+---------+-------+----------------+----------------+---------+------+------+
| country | range | pop_cont_index | pop_cont_index | 5 | NULL | 6 |
+---------+-------+----------------+----------------+---------+------+------+
1 row in set (0.00 sec)
Виждаме, че тук имаме гигантска разлика! Оригиналната заявка (без индекси) имаше нужда от 233 прелиствания, а с използването на правилния индекс ги сведохме до само 6, което е с близо 39 пъти по-високо бързодействие!
Тук е много важно да отбележим нещо – при създаването на множествени индекси е много важна подредбата на колоните. Ако например в предишната заявка ги „обърнем“ и сложим колоната population преди continent, то ще се получи следното:
mysql> ALTER TABLE country
-> DROP INDEX pop_cont_index;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE country
-> ADD INDEX pop_cont_index (population, Continent);
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT name
-> FROM country
-> USE INDEX(pop_cont_index)
-> WHERE population > 40000000
-> AND
-> Continent="Europe";
+---------+-------+----------------+----------------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows |
+---------+-------+----------------+----------------+---------+------+------+
| country | range | pop_cont_index | pop_cont_index | 4 | NULL | 28 |
+---------+-------+----------------+----------------+---------+------+------+
1 row in set (0.00 sec)
Виждаме, че тук изгубихме ефекта от добавянето на continent в индекса. Това е така, понеже стойностите по редове в колона population са доста различни (почти без повторения), т.е. на изолираната единична стойност няма какво допълнително да се оптимизира. Ето как можем да изкараме още една зависимост:
- При използване на множествен индекс в общия случай ще по-добре да слагаме първо колоната с повече повторения, а след нея по-уникалната такава.
Това „правило“ разбира се може лесно да получи впечатляващи изключения (главно тогава, когато се окаже, че въобще не е нужно да бъде използвана една от двете колони). Затова винаги проверявайте и правете тестове. Също така имайте предвид, че когато в условието WHERE на заявката има логическо OR ще е особено подходящо да използвате именно множествен индекс.
Нуждата от оптимизация на заявките идва особено важна когато ни се налага да използваме JOIN между две и повече таблици. Тук EXPLAIN ще ни дава информация за всяка една от таблиците използвани във FROM условието:
mysql> EXPLAIN SELECT country.Name as country, city.Name as city
-> FROM country JOIN city ON country.code = city.countryCode
-> WHERE country.Continent = "Europe";
+---------+---------------------------------------+----------------+--------------------+------+
| table | possible_keys | key | ref | rows |
+---------+---------------------------------------+----------------+--------------------+------+
| country | PRIMARY,continentIndex,pop_cont_index | continentIndex | const | 46 |
| city | CountryCode | CountryCode | world.country.Code | 7 |
+---------+---------------------------------------+----------------+--------------------+------+
2 rows in set (0.00 sec)
Виждаме, че тук има няколко възможни индекса. Индексите continentIndex и pop_cont_index за country ги създадохме ние в предишни заявки. Индексите PRIMARY за country и CountryCode за city са създадени автоматично при създаването на таблиците в MySQL. Последното е така, понеже едното е primary key, а другото е foreign key. За всички ключови колони по подразбиране се създават индекси. Тук при по-сложни WHERE условия също е добре да следим индексите, които се използват и при нужда да указваме кой от тях да бъде употряван или да създаваме допълнителни множествени.
Освен кой индекс да се използва има и някои други неща, които трябва да следим. Важна е и колона EXTRA от резултата на EXPLAIN заявката. Там ще виждате различни стойности, сред които „using where“, „using index“, „using filesort“, „using temporarily table“ и др. Ако там специално присъства „using filesort“ (може да се появи когато използвате ORDER BY), то това означава, че по време на сортирането не се използва индекс. Ето един пример:
mysql> EXPLAIN SELECT name
-> FROM city
-> ORDER BY name DESC;
+-------+------+---------------+------+---------+------+------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+----------------+
| city | ALL | NULL | NULL | NULL | NULL | 4234 | Using filesort |
+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
Използването на индекс тук ще разреши проблема:
ALTER TABLE city
ADD INDEX nameIndex (name);
mysql> EXPLAIN SELECT name
-> FROM city
-> ORDER BY name DESC;
+-------+-------+---------------+-----------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+-----------+---------+------+------+-------------+
| city | index | NULL | nameIndex | 35 | NULL | 4234 | Using index |
+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
Така можем да изведем още едно „правило“:
- Сортирането с индекс почти винаги е по-добро от такова без. При „лош индекс“ (колона с много повторения) бързината при двете сортирания може да се доближи. Но дори тогава ако изходните данни са с много голям обем, то е възможно системата да изпита недостиг на оперативна памет (големината на sort_buffer променливата в my.cnf дефинира максимален лимит) и от там нататък MySQL ще трябва да създаде временна (temporarily) таблица на хард диска. Достигането до такава ситуация винаги е „убиец на производителност“. С използването на индекси при сортирането рядко ще достигаме до такива моменти. Така, че ако видите „using index“ в колона „Extra“, то би трябвало всичко да е наред, докато „using filesort“ не.
И накрая, но не последно – създаването на излишни или лошо работещи индекси е вредно за производителността на системата. Причината е, че те също отнемат ресурси, за да бъдат поддържани. Поради тази причина можем да изведем последното (за статията) условие:
- Създавайте само индекси, които ще използвате често
Харесва ми!