sql - выберите медленный из двух таблиц

Question

sql - выберите медленный из двух таблиц

0

у меня есть две таблицы в моей базе данных mariadb, A и B:

мой запрос очень медленный 3 мин 18.028 сек мин с индексом.

SQL запрос:

select A.Cid, A.time, A.step, B.cpu 
from A
join B on B.CID2 = A.CID group by A.CID;

Первая таблица A:

+------+---------------------+--------------------+-----------------+
| CID  | time                | step               | time_in_seconde |
+------+---------------------+--------------------+-----------------+
|    3 | 2017-07-27 06:35:52 | gege               |          13.229 |
|    4 | 2017-07-27 06:36:56 | titi               |          12.823 |
|    5 | 2017-07-27 06:55:04 | fefe               |          12.667 |
344039 rows in set (1.239 sec)

вторая таблица B (:

+------+---------------------+-----------------+
| CID2 | time                | cpu             |
+------+---------------------+-----------------+
|    3 | 2017-07-27 06:35:52 |          0.01   |
|    4 | 2017-07-27 06:36:56 |          0.05   |
|    5 | 2017-07-27 06:55:04 |          0.03   |
51639 rows in set (0.181 sec)

результат запроса:

+------+---------------------+--------------------+-----------------+-----------------+
| CID  | time                | step               | time_in_seconde | cpu             |
+------+---------------------+--------------------+-----------------+-----------------+
|    3 | 2017-07-27 06:35:52 | gege               |          13.229 |          0.01   |
|    4 | 2017-07-27 06:36:56 | titi               |          12.823 |          0.05   |
|    5 | 2017-07-27 06:55:04 | fefe               |          12.667 |          0.03   |

405 рядов в наборе (3 мин 18,028 сек)

Конфигурация mariadb:

+--------------------------------------------------------+----------------------+
| Variable_name                                          | Value                |
+--------------------------------------------------------+----------------------+
| aria_block_size                                        | 8192                 |
| aria_log_file_size                                     | 1073741824           |
| aria_max_sort_file_size                                | 9223372036853727232  |
| aria_pagecache_buffer_size                             | 134217728            |
| aria_pagecache_file_hash_size                          | 512                  |
| aria_sort_buffer_size                                  | 268434432            |
| binlog_cache_size                                      | 32768                |
| binlog_file_cache_size                                 | 16384                |
| binlog_stmt_cache_size                                 | 32768                |
| bulk_insert_buffer_size                                | 8388608              |
| delayed_queue_size                                     | 1000                 |
| histogram_size                                         | 0                    |
| host_cache_size                                        | 279                  |
| innodb_buffer_pool_chunk_size                          | 134217728            |
| innodb_buffer_pool_size                                | 536870912            |
| innodb_change_buffer_max_size                          | 25                   |
| innodb_ft_cache_size                                   | 8000000              |
| innodb_ft_max_token_size                               | 84                   |
| innodb_ft_min_token_size                               | 3                    |
| innodb_ft_total_cache_size                             | 640000000            |
| innodb_log_buffer_size                                 | 16777216             |
| innodb_log_file_size                                   | 50331648             |
| innodb_log_write_ahead_size                            | 8192                 |
| innodb_max_undo_log_size                               | 10485760             |
| innodb_online_alter_log_max_size                       | 134217728            |
| innodb_page_size                                       | 16384                |
| innodb_purge_batch_size                                | 300                  |
| innodb_sort_buffer_size                                | 1048576              |
| innodb_sync_array_size                                 | 1                    |
| join_buffer_size                                       | 134217728            |
| key_buffer_size                                        | 134217728            |
| key_cache_block_size                                   | 1024                 |
| key_cache_file_hash_size                               | 512                  |
| large_page_size                                        | 0                    |
| log_tc_size                                            | 24576                |
| max_binlog_cache_size                                  | 18446744073709547520 |
| max_binlog_size                                        | 1073741824           |
| max_binlog_stmt_cache_size                             | 18446744073709547520 |
| max_heap_table_size                                    | 16777216             |
| max_join_size                                          | 18446744073709551615 |
| max_long_data_size                                     | 16777216             |
| max_relay_log_size                                     | 1073741824           |
| metadata_locks_cache_size                              | 1024                 |
| mrr_buffer_size                                        | 262144               |
| myisam_block_size                                      | 1024                 |
| myisam_data_pointer_size                               | 6                    |
| myisam_max_sort_file_size                              | 9223372036853727232  |
| myisam_mmap_size                                       | 18446744073709551615 |
| myisam_sort_buffer_size                                | 134216704            |
| performance_schema_accounts_size                       | -1                   |
| performance_schema_digests_size                        | -1                   |
| performance_schema_events_stages_history_long_size     | -1                   |
| performance_schema_events_stages_history_size          | -1                   |
| performance_schema_events_statements_history_long_size | -1                   |
| performance_schema_events_statements_history_size      | -1                   |
| performance_schema_events_waits_history_long_size      | -1                   |
| performance_schema_events_waits_history_size           | -1                   |
| performance_schema_hosts_size                          | -1                   |
| performance_schema_session_connect_attrs_size          | -1                   |
| performance_schema_setup_actors_size                   | 100                  |
| performance_schema_setup_objects_size                  | 100                  |
| performance_schema_users_size                          | -1                   |
| preload_buffer_size                                    | 32768                |
| profiling_history_size                                 | 15                   |
| query_alloc_block_size                                 | 16384                |
| query_cache_size                                       | 0                    |
| query_prealloc_size                                    | 24576                |
| range_alloc_block_size                                 | 4096                 |
| read_buffer_size                                       | 131072               |
| read_rnd_buffer_size                                   | 262144               |
| rowid_merge_buff_size                                  | 8388608              |
| sort_buffer_size                                       | 2097152              |
| thread_cache_size                                      | 4                    |
| thread_pool_size                                       | 2                    |
| tmp_disk_table_size                                    | 18446744073709551615 |
| tmp_memory_table_size                                  | 16777216             |
| tmp_table_size                                         | 16777216             |
| transaction_alloc_block_size                           | 8192                 |
| transaction_prealloc_size                              | 4096                 |
| wsrep_max_ws_size                                      | 2147483647           |
+--------------------------------------------------------+----------------------+

мой конфиг:

[root/] # free -m всего использованных свободных общих буфера/кэша. Mem: 3788 1177 143 10 2467 2326 Своп: 1535 284 1251

процессор: Intel Celeron_4x0 (Conroe/Merom Class Core 2)

у меня вопрос как увеличить скорость моего запроса?

dfg ert 30 янв. 2019, в 16:22

Источник

0

Сколько у вас рядов?
DDS 30 янв. 2019, в 14:47
0

Этот вопрос был бы лучше, если бы он был размещен на dba.stackexchange?
DDS 30 янв. 2019, в 14:50
0

Нам также понадобится соответствующая схема.
Vatev 30 янв. 2019, в 14:58
0

Начните с использования JOIN чуть более 20-го века
RiggsFolly 30 янв. 2019, в 14:59
0

@ Ватев, как я могу получить схему?
dfg ert 30 янв. 2019, в 15:00
0

Если в SELECT используется более одной таблицы, добавьте в каждый столбец псевдоним таблицы.
The Impaler 30 янв. 2019, в 15:01
0

@RiggSFolly Я использовал объединение, но у меня есть много строк, у меня есть 344039 для таблицы A и 51639 для таблицы B
dfg ert 30 янв. 2019, в 15:01
2

@dfgert «схема» означает таблицы (типы столбцов и индексы). Используйте SHOW CREATE TABLE my_table .
Vatev 30 янв. 2019, в 15:02
0

from A,B не использует JOIN
RiggsFolly 30 янв. 2019, в 15:02
0

Почему вы group by CID как мне кажется, что отношения один к одному
RiggsFolly 30 янв. 2019, в 15:03
0

Каковы первичные ключи , внешние ключи , уникальные ограничения и существующие индексы в этих таблицах?
The Impaler 30 янв. 2019, в 15:05
0

Каково текущее состояние вашего компьютера? Если процессор на 100% занят другими вещами или ваша память постоянно меняется, вы можете получить плохую производительность.
Conffusion 30 янв. 2019, в 15:06
0

моя память на 100% и процессор на 100%
dfg ert 05 фев. 2019, в 12:37

Показать ещё 11 комментариев

Теги:

mysql

sql

mariadb

3 ответа

Ещё вопросы

Этот вопрос был бы лучше, если бы он был размещен на dba.stackexchange?
Нам также понадобится соответствующая схема.
Начните с использования JOIN чуть более 20-го века
@ Ватев, как я могу получить схему?
Если в SELECT используется более одной таблицы, добавьте в каждый столбец псевдоним таблицы.
@RiggSFolly Я использовал объединение, но у меня есть много строк, у меня есть 344039 для таблицы A и 51639 для таблицы B
@dfgert «схема» означает таблицы (типы столбцов и индексы). Используйте SHOW CREATE TABLE my_table .
Почему вы group by CID как мне кажется, что отношения один к одному
Каковы первичные ключи , внешние ключи , уникальные ограничения и существующие индексы в этих таблицах?
Каково текущее состояние вашего компьютера? Если процессор на 100% занят другими вещами или ваша память постоянно меняется, вы можете получить плохую производительность.
моя память на 100% и процессор на 100%

Luuk · Answer 1 · 2019-01-30T13-58-00.000Z

Почему вы используете 'group by'?

Попробуй это:

select Cid, A.time, step, cpu from A,B where B.CID2=A.CID

Gordon Linoff · Answer 2 · 2019-01-30T13-08-00.000Z

У вас есть "голые" столбцы в group by - и это приведет к ошибкам практически в любой базе данных.

Следующее может быть быстрее:

select A.Cid, A.time, A.step,
       (select B.cpu 
        from B 
        where B.CID2 = A.CID
        limit 1
       ) as cpu
from A;

Для производительности вы хотите индекс на B(CID2, cpu).

Обратите внимание, что это на самом деле делает LEFT JOIN а не INNER JOIN. Если это важное различие (т.е. Вам действительно нужны только строки, совпадающие между двумя таблицами), добавьте:

having cpu is not null

Если A.Cid был первичным ключом таблицы, да. Или, по крайней мере, если бы он имел уникальное ограничение. В противном случае это не эквивалентно.

scaisEdge · Answer 3 · 2019-01-30T13-06-00.000Z

Вы можете попробовать оптимизировать JOIN

        select distinct Cid
                , time
                , step
                , cpu 
        from A
        INNER JOIN B ON B.CID2=A.CID

добавив правильный индекс для вас, так что убедитесь, что вы указали в таблице б

create index  idx1  on B(CID2)

и на столе добавление составного избыточного индекса

    create index  idx2  on A(CID,time, step, cpu )

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

запрос очень медленный, у вас есть идея?, может быть, это конфиг mariadb, я тестировал с mysql (та же проблема) медленный запрос
вы создали индексы как предложено ??