Статьи

Отладка SQL-запроса в MySQL

Март 20, 2019

Недавно я начал писать SQL-запрос для анализа и отладки рабочего кода. Но я был удивлен, увидев, что выполнение некоторых запросов занимает больше времени для достижения того же результата. Я провел исследование и обнаружил некоторые интересные вещи о том, как отлаживать SQL-запрос. У меня есть очень простая таблица, определение которой заключается в следующем. В тестовой среде эта таблица была заполнена более чем 1000 тысячами строк.

01
02
03
04
05
06
07
08
09
10
+-----------------------+--------------+------+-----+----------------+
| Field                 | Type         | Null | Key | Extra          |
+-----------------------+--------------+------+-----+----------------+
| id                    | bigint(20)   | NO   | PRI | auto_increment |
| dateCreated           | datetime     | NO   |     |                |
| dateModified          | datetime     | NO   |     |                |
| phoneNumber           | varchar(255) | YES  | MUL |                |
| version               | bigint(20)   | NO   |     |                |
| oldPhoneNumber        | varchar(255) | YES  |     |                |
+-----------------------+--------------+------+-----+----------------+

Я выполнил очень простой запрос, чтобы найти кортеж, содержащий 5107357058 в качестве номера телефона. Потребовалось почти 4 секунды, чтобы получить результат.

1
2
select * from Device where phoneNumber = 5107357058;
takes 4 sec.

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

1
2
select * from Device where phoneNumber = '5107357058';
takes almost no time.

После поиска в Google и чтения поста в stackoverflow я обнаружил EXPLAIN SQL clouse, который помогает в отладке запроса. Оператор EXPLAIN предоставляет информацию о плане выполнения для SELECT . Когда я использовал его для получения информации о двух запросах, я получил следующие результаты.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
mysql> EXPLAIN select * from Device where phoneNumber = 5107357058;
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys                         | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | Device    | ALL  | phoneNumber,idx_Device_phoneNumber    | NULL | NULL    | NULL | 6482116 | Using where |
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
 
mysql> EXPLAIN select * from Device where phoneNumber = '5107357058';
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys                         | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      |   Device  | ref  | phoneNumber,idx_Device_phoneNumber    | phoneNumber | 258     | const |    2 | Using where |
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

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

  • possible_keys : показывает, что индексы применяются к запросу
  • key : какой ключ использовать, чтобы найти запись. Значения NULL показывают, что ключ не использовался для запроса и поиска SQL линейно, что в итоге занимает много времени.
  • rows : SQL-запрос с меньшим количеством строк результатов, эффективен. Всегда следует пытаться улучшить запрос и избегать использования общего запроса. Производительность запроса очень очевидна при выполнении большого количества записей.
  • type : is «Тип соединения». Ref означает, что все строки с соответствующими значениями индекса считываются из таблицы; All означает полное сканирование таблицы.

Два выхода EXPLAIN ясно указывают на тонкие различия. В последнем запросе используется string правильным типом данных, в качестве ключа используется phoneNumber и проверяется только две строки. В то время как первый использует в запросе целое число, отличающееся типом данных, и, следовательно, SQL преобразуется в целочисленное значение в строковое значение и сравнивается с каждой записью, представленной в этой таблице. Это приводит к NULL качестве ключа и 6482116 в качестве вывода строки. Вы также можете видеть, что более поздним значением типа запроса является ref а предыдущим значением типа запроса является All , что ясно указывает на то, что первый является неправильным запросом.

Ссылка: Отладка SQL-запроса от нашего партнера по JCG Ракеша Кусата в блоге Code4Reference .

76
Share: