Программисты Али как раз такие, их ставит в тупик простой SQL-запрос

MySQL

(Пожалуйста, простите меня, возглавив группу на некоторое время, уделите несколько минут, чтобы увидеть и, возможно, помочь вам.) Недавняя работа столкнулась с «магической» проблемой, возможно, чтобы помочь всем, и поэтому возникла эта статья.

задний план

Недавно столкнулся на работе с "волшебной" проблемой, которая может быть полезна всем, поэтому и сформирована эта статья.

Проблема, вероятно, в том, что у меня есть две таблицы, TableA и TableB, в которых TableA имеет около миллиона строк (базовые бизнес-данные), а TableB имеет несколько строк (новые бизнес-сценарии, данные еще не расширены), семантическиTableA.columnA = TableB.columnA, вcolumnAИндекс строится на сервере, но запрос действительно медленный, в основном 5-6 секунд, что явно не соответствует ожиданиям.

Позвольте мне проиллюстрировать это на конкретном примере, имитирующем сценарий SQL-запроса.

Место действия

  • user_infoТаблица, для максимально простой сцены, я только MOCK данных трех столбцов.
mysql> desc user_info;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| uid   | varchar(64)  | NO   | MUL | NULL    |                |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
  • user_scoreстол, из которыхuidиuser_info.uidСемантически непротиворечивый:
mysql> desc user_info;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| uid   | varchar(64)  | NO   | MUL | NULL    |                |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
  • Данные следующие, которые являются очень распространенными сценариями.
mysql> select * from user_score limit 2;
+----+--------------------------------------+-------+
| id | uid                                  | score |
+----+--------------------------------------+-------+
|  5 | 111111111                            |   100 |
|  6 | 55116d58-be26-4eb7-8f7e-bd2d49fbb968 |   100 |
+----+--------------------------------------+-------+
2 rows in set (0.00 sec)

mysql> select * from user_info limit 2;
+----+--------------------------------------+-------------+
| id | uid                                  | name        |
+----+--------------------------------------+-------------+
|  1 | 111111111                            | tanglei     |
|  2 | 55116d58-be26-4eb7-8f7e-bd2d49fbb968 | hudsonemily |
+----+--------------------------------------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*) from user_score
    -> union
    -> select count(*) from user_info;
+----------+
| count(*) |
+----------+
|        4 |
|  3000003 |
+----------+
2 rows in set (1.39 sec)
  • Индексный случай:
mysql> show index from user_score;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_score |          0 | PRIMARY   |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| user_score |          1 | index_uid |            1 | uid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show index from user_info;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_info |          0 | PRIMARY   |            1 | id          | A         |     2989934 |     NULL | NULL   |      | BTREE      |         |               |
| user_info |          1 | index_uid |            1 | uid         | A         |     2989934 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
  • Запрос бизнес-сценариев: Известенuser_score.id, необходимо связать запрос, соответствующийuser_info(Не обращайте внимания на то, разумен ли этот конкретный бизнес-сценарий в первую очередь.) Тогда соответствующий SQL, естественно, выглядит следующим образом:
mysql> select * from user_score us
    -> inner join user_info ui on us.uid = ui.uid
    -> where us.id = 5;
+----+-----------+-------+---------+-----------+---------+
| id | uid       | score | id      | uid       | name    |
+----+-----------+-------+---------+-----------+---------+
|  5 | 111111111 |   100 |       1 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685399 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685400 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685401 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685402 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685403 | 111111111 | tanglei |
+----+-----------+-------+---------+-----------+---------+
6 rows in set (1.18 sec)

Просьба не обращать внимания на данные в нем, я просто сначала издевался над 100W, а потом импортировал его дважды, поэтому данные имеют некоторое дублирование.Данные 300W, финальный запрос также 1,18 секунды.Это должно быть быстрее.Старые правилаexplainВидишь, что происходит?

mysql> explain
    -> select * from user_score us
    -> inner join user_info ui on us.uid = ui.uid
    -> where us.id = 5;
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows    | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | us    | const | PRIMARY,index_uid | PRIMARY | 4       | const |       1 | NULL        |
|  1 | SIMPLE      | ui    | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)

Находитьuser_infoТаблица не использует индексы, а полная таблица сканирует почти 300Вт данных?Явление такое,почему?

Вы также можете подумать об этом, если вы столкнулись с таким сценарием, как вы должны расследовать это?

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

Например, изменить порядок таблицы соединений (ведущая таблица/ведомая таблица)

mysql> explain select * from user_info ui inner join user_score us on us.uid = ui.uid where us.id = 5;
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows    | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | us    | const | PRIMARY,index_uid | PRIMARY | 4       | const |       1 | NULL        |
|  1 | SIMPLE      | ui    | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)

Другой пример — использование подзапроса:

mysql> explain select * from user_info where uid in  (select uid from user_score where id = 5);
+----+-------------+------------+-------+-------------------+---------+---------+-------+---------+-------------+
| id | select_type | table      | type  | possible_keys     | key     | key_len | ref   | rows    | Extra       |
+----+-------------+------------+-------+-------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | user_score | const | PRIMARY,index_uid | PRIMARY | 4       | const |       1 | NULL        |
|  1 | SIMPLE      | user_info  | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 | Using where |
+----+-------------+------------+-------+-------------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)

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

mysql> select * from user_info where uid = '111111111';
+---------+-----------+---------+
| id      | uid       | name    |
+---------+-----------+---------+
|       1 | 111111111 | tanglei |
| 3685399 | 111111111 | tanglei |
| 3685400 | 111111111 | tanglei |
| 3685401 | 111111111 | tanglei |
| 3685402 | 111111111 | tanglei |
| 3685403 | 111111111 | tanglei |
+---------+-----------+---------+
6 rows in set (0.01 sec)

mysql> explain select * from user_info where uid = '111111111';
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra                 |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user_info | ref  | index_uid     | index_uid | 194     | const |    6 | Using index condition |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

проблема решена

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

mysql> show create table user_info;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(64) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3685404 DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table user_score;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                             |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_score | CREATE TABLE `user_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(64) NOT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Есть все основания подозревать, что индекс недействителен из-за несогласованных наборов символов. Таким образом, набор символов маленькой таблицы (реальная онлайн-среда не может работать случайным образом) модифицируется, чтобы соответствовать большой таблице, а затем тестируется.

mysql> select * from user_score us
    -> inner join user_info ui on us.uid = ui.uid
    -> where us.id = 5;
+----+-----------+-------+---------+-----------+---------+
| id | uid       | score | id      | uid       | name    |
+----+-----------+-------+---------+-----------+---------+
|  5 | 111111111 |   100 |       1 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685399 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685400 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685401 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685402 | 111111111 | tanglei |
|  5 | 111111111 |   100 | 3685403 | 111111111 | tanglei |
+----+-----------+-------+---------+-----------+---------+
6 rows in set (0.00 sec)

mysql> explain
    -> select * from user_score us
    -> inner join user_info ui on us.uid = ui.uid
    -> where us.id = 5;
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key       | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | us    | const | PRIMARY,index_uid | PRIMARY   | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | ui    | ref   | index_uid         | index_uid | 194     | const |    6 | NULL  |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
2 rows in set (0.00 sec)

Это действительно сработало.

Копайте первопричину

На самом деле, чтобы докопаться до сути, в различных воинских уставах/уставах MySQL в Интернете упоминается «Не участвовать в расчете столбцов индекса». В этом случае, если вы знаетеexplain extended + show warningsЕсли этот инструмент используется (я не знал об этом раньшеexplainможно добавить позжеextendedПараметр), у вас может быть «внезапное реализация» как можно скорее. (Последняя версия MySQL 8.0 не должна добавлять это ключевое слово).

Взгляните на эффект (Ах, я должен изменить набор символов обратно!!!)

mysql> explain extended select * from user_score us  inner join user_info ui on us.uid = ui.uid where us.id = 5;
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | us    | const | PRIMARY,index_uid | PRIMARY | 4       | const |       1 |   100.00 | NULL        |
|  1 | SIMPLE      | ui    | ALL   | NULL              | NULL    | NULL    | NULL  | 2989934 |   100.00 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '5' AS `id`,'111111111' AS `uid`,'100' AS `score`,`test`.`ui`.`id` AS `id`,`test`.`ui`.`uid` AS `uid`,`test`.`ui`.`name` AS `name` from `test`.`user_score` `us` join `test`.`user_info` `ui` where (('111111111' = convert(`test`.`ui`.`uid` using utf8mb4))) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

Что касается того, почему возникает эта проблема?В общем, именно из-за исторических причин оригинальные часы в старом бизнес-сценарии являются подделкой.utf8, новая бизнес-таблица принимает истиннуюutf8mb4.

  1. При рассмотрении новой таблицы не обращайте внимания на сравнение с набором символов исходной библиотеки.На самом деле обнаружено, что разные таблицы в библиотеке могут иметь разные наборы символов, и разные люди могут выбирать разные наборы символов в соответствии с личными предпочтениями при построении. , это видно,Насколько важна спецификация разработки.
  2. Хотя известно, что вычисляют столбец индекса не могут участвовать, но они одинаковы при этом сценарии,varchar(64)Преобразование типов по-прежнему происходит в конечном процессе запроса, поэтому необходимо приравнять несогласованные наборы символов полей к несогласованным типам полей.
  3. В этом случае используйтеfail-fastЕсли окажется, что идея несовместима, не лучше ли было бы просто запретить объединение? (Например,char v.s varcharнельзя присоединиться к одному).

оставить вопрос мысли

Можете ли вы объяснить следующую ситуацию? Почему результат запроса ведет себя непоследовательно? Обратите внимание на порядок выполнения SQL, рабочий процесс оптимизатора запросов иUsing join buffer (Block Nested Loop), Рекомендуется посмотреть большеОфициальное руководство по MySQLПогрузитесь в обоснование этого.

mysql> select * from user_info ui
    -> inner join user_score us on us.uid = ui.uid
    -> where us.uid = '111111111';
+---------+-----------+---------+----+-----------+-------+
| id      | uid       | name    | id | uid       | score |
+---------+-----------+---------+----+-----------+-------+
|       1 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685399 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685400 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685401 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685402 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685403 | 111111111 | tanglei |  5 | 111111111 |   100 |
+---------+-----------+---------+----+-----------+-------+
6 rows in set (1.14 sec)

mysql> select * from user_info ui
    -> inner join user_score us on us.uid = ui.uid
    -> where ui.uid = '111111111';
+---------+-----------+---------+----+-----------+-------+
| id      | uid       | name    | id | uid       | score |
+---------+-----------+---------+----+-----------+-------+
|       1 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685399 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685400 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685401 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685402 | 111111111 | tanglei |  5 | 111111111 |   100 |
| 3685403 | 111111111 | tanglei |  5 | 111111111 |   100 |
+---------+-----------+---------+----+-----------+-------+
6 rows in set (0.00 sec)

mysql> explain
    -> select * from user_info ui
    -> inner join user_score us on us.uid = ui.uid
    -> where us.uid = '111111111';
+----+-------------+-------+------+---------------+-----------+---------+-------+---------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows    | Extra                                              |
+----+-------------+-------+------+---------------+-----------+---------+-------+---------+----------------------------------------------------+
|  1 | SIMPLE      | us    | ref  | index_uid     | index_uid | 258     | const |       1 | Using index condition                              |
|  1 | SIMPLE      | ui    | ALL  | NULL          | NULL      | NULL    | NULL  | 2989934 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+-----------+---------+-------+---------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain
    -> select * from user_info ui
    -> inner join user_score us on us.uid = ui.uid
    -> where ui.uid = '111111111';
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | ui    | ref  | index_uid     | index_uid | 194     | const |    6 | Using index condition                              |
|  1 | SIMPLE      | us    | ALL  | index_uid     | NULL      | NULL    | NULL  |    4 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
2 rows in set (0.01 sec)

Примечание: Тестовый сценарий в этой статье основан на MySQL 5.6.Кроме того, кейс в этой статье просто иллюстрирует проблему.SQL в нем не стандартизирован (например, старайтесь не использовать select * и тому подобное) , пожалуйста, не имитируйте его (я не несу ответственности за его имитацию). На написание этой статьи, сборку БД, заполнение фиктивных данных и т. д. ушло много времени. Если вы найдете это полезным, я надеюсь, что вы можете помочь » просмотр» и «пересылка». Наконец, оставьте вопрос для обсуждения, добро пожаловать, чтобы оставить сообщение и высказать свое мнение.

Рекламировать

Сервис эластичных вычислений Alibaba Cloud ECS — один из самых важных продуктов облачных сервисов Alibaba Cloud. Служба эластичных вычислений — это простая и эффективная вычислительная служба с эластично масштабируемой вычислительной мощностью. Мы всегда стремились использовать и создавать новейшие передовые технологии в отрасли, позволяя большему количеству клиентов легко пользоваться преимуществами этих технологий, быстро создавать более стабильные и безопасные приложения в облаке, повышать эффективность эксплуатации и обслуживания, сокращать затраты на ИТ. затрат и позволяет клиентам больше сосредоточиться на инновациях собственного основного бизнеса. Эластичные вычисления переопределяют то, как люди используют вычислительные ресурсы, и этот новый способ продолжает влиять на экологический и экономический круг вычислительных ресурсов. Мы творим историю и искренне приглашаем вас присоединиться к нам.

В последнее время команда выпустила много HC, и мы искренне ищем студентов P6/P7/P8.Эта группа студентов в основном набирает студентов, занимающихся исследованиями и разработками (В этом JD), заинтересованные студенты могут отсканировать приведенный ниже QR-код, чтобы добавить меня в контакт.

Кроме того, в 2021 году также ведется набор в школу / вакансии стажера (Нажмите, чтобы узнать подробности), если вы заканчиваете обучение в период с 2020-11 по 2021-07 и вас интересует Alibaba, вы можете обратиться ко мне за помощью.

использованная литература

Категории