@davidb. , , Это зависит от характера данных. Только ты можешь ответить на это.

у оптимизировать этот запрос,

select  location_id, dept_id,
        round(sum(sales),0), sum(qty),
        count(distinct tran_id),
        now()
    from  tran_sales
    where  tran_date <= '2016-12-24'
    group by  location_id, dept_id;

в настоящее время этот запрос выполняется в среднем около 98 секунд (запрос занял 97,4096 секунд) в Windows 10, 64-разрядной ОС, 16 ГБ ОЗУ.

это таблица деталей для вашей справки.

    CREATE TABLE tran_sales (
    tran_date date NOT NULL,
    location_id int(11) NOT NULL,
    dept_id int(11) NOT NULL,
    item_id varchar(25) NOT NULL,
    tran_id int(11) NOT NULL,
    sales float DEFAULT NULL,
    qty int(11) DEFAULT NULL,
    update_datetime datetime NOT NULL,
    PRIMARY KEY (tran_date,location_id,dept_id,item_id,tran_id),
    KEY tran_date (tran_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

количество записей в таблице tran_sales:13,5 миллионов.

Примечание: Даже я пытался без и с этим индексомKEY tran_date (tran_date) , и среднее время занимает 98 секунд с и безKEY tran_date (tran_date)

Пожалуйста, предложите, как ускорить результаты, изменив запрос или изменив некоторые настройки по умолчанию my.ini, если это поможет. Благодарю.

Обновить минимальная дата в таблице: 2016-07-01, а максимальная дата в таблице: 2017-07-25

 Bùi Đức Khánh26 дек. 2017 г., 11:56
Вы можете удалить tran_date из первичного ключа? Если нет, измените положение вашего первичного ключа как(location_id,dept_id, tran_date,item_id,tran_id), Ваше предложение where опережает ключ tran_date, но первичный ключ не помогает сгруппировать предложение
 davidb26 дек. 2017 г., 13:29
@ KhánhBùiĐức, я изменил положение первичного ключа, как ты сказал. но это не помогло. когда я использовалwhere tran_date='2016-12-24' объяснить, я вижу ключtran_date используется. но когда я использовалwhere tran_date<='2016-12-24' ключ ноль в объяснении.
 davidb26 дек. 2017 г., 11:12
@ KhánhBùiĐức, это была опечатка, я ее отредактировал.
 davidb26 дек. 2017 г., 12:08
@ KhánhBùiĐức, спасибо, запрос использует первичный ключ, когда я включил объяснение. я изменю позицию tran_date и проверим, использует ли запрос ключ tran_date. скоро сообщит вам результат. Благодарю.
 Bùi Đức Khánh26 дек. 2017 г., 11:07
Почему вы должныnow() в группе Клод?Now() функция может привести к вашемуgroup by ничего не значит, потому что, кажется, ничего не группирует

Ответы на вопрос(2)

Для этого запроса:

select location_id, dept_id,
       round(sum(sales), 0), sum(qty), count(distinct tran_id),
       now()
from tran_sales
where tran_date <= '2016-12-24'
group by location_id, dept_id;

Там не так много, вы можете сделать. Одной попыткой будет индекс покрытия:(tran_date, location_id, dept_id, sales, qty)Но я не думаю, что это сильно поможет.

 Gordon Linoff26 дек. 2017 г., 14:12
@davidb. , , Разбиение может немного помочь, но, вероятно, не сильно. Запросы агрегации трудно оптимизировать в MySQL. Вы можете использоватьCOUNT(*) вместоCOUNT(DISTINCT)?
 Gordon Linoff27 дек. 2017 г., 03:13
@davidb. , , Это зависит от характера данных. Только ты можешь ответить на это.
 davidb26 дек. 2017 г., 14:55
когда я пыталсяcount(*) запрос занял менее 3 секунд. количество транзакций для каждого отдела в месте будет различаться по количеству (*). не могли бы вы посоветовать, если есть возможность использоватьcount (*) чтобы соответствовать результату согласноcount (distinct)
 davidb26 дек. 2017 г., 13:59
после добавления такого индекса покрытия (tran_date, location_id, dept_id, sales, qty) я получил результат за 82 секунды. помогает ли разбиение?
 davidb26 дек. 2017 г., 13:30
спасибо, я буду применять этот индекс покрытия(tran_date, location_id, dept_id, sales, qty) и попробуй сейчас.
Решение Вопроса

Индекс покрытия: это только немного меньше, чем таблица, поэтому он немного быстрее.KEY(tran_date) -- отходы; лучше использовать ПК, который начинается сtran_date.PARTITIONing - Нет. Это, вероятно, будет медленнее.Удалениеtran_date (или иначе переставить ПК) - это будет больно. Фильтрация (WHERE) включенtran_date; обычно лучше иметь этопервый.Итак, почему былоCOUNT(*) быстрый? Ну, начнем с рассмотренияEXPLAIN, Это покажет, что он использовалKEY(tran_date) вместо сканирования стола. Меньше данных для сканирования, следовательно, быстрее.

Реальная проблема в том, что у вас есть миллионы строк для сканирования,требуется время, чтобы дотронуться до миллионов строк.

Как ускорить это? Создать и поддерживатьТаблица результатов , Затем запросите эту таблицу (с тысячами строк) вместо исходной таблицы (миллионы строк). Общее количествоSUM(counts); общая суммаSUM(sums); среднее значениеSUM(sums)/SUM(counts), и т.д.

 davidb28 дек. 2017 г., 14:35
пожалуйста, предложите, так как у меня уже есть ПЕРВИЧНЫЙ КЛЮЧ, как это (tran_date, location_id, dept_id, item_id, tran_id), мне все еще нужен дополнительный индекс покрытия KEY (tran_date, location_id, dept_id, item_id, tran_id)?
 Rick James28 дек. 2017 г., 16:41
@davidb - определенно не добавляйте избыточный индекс. Давайте посмотрим типичныйSELECTsТолько тогда я могу посоветоватьPRIMARY KEY и другие показатели.
 davidb28 дек. 2017 г., 14:30
спасибо за советы, я буду создавать и поддерживать сводные таблицы.

Ваш ответ на вопрос