Как оптимизировать подсчет и порядок по запросу в миллионах строк

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

Я должен присоединиться к 4 таблицам и получить записи. Когда я запускаю простой запрос, для завершения требуется только миллисекунда, но, пытаясь подсчитать или заказать, оставив таблицу соединений, она застряла неограниченное время.

См. Нижеприведенные случаи.

Конфигурация сервера БД:

CPU Number of virtual cores: 4
Memory(RAM): 16 GiB
Network Performance: High

Строки в каждой таблице:

tbl_customers -  #Rows: 20 million.
tbl_customers_address -  #Row 25 million.
tbl_shop_setting - #Rows 50k
aio_customer_tracking - #Rows 5k

Таблицы Схема:

CREATE TABLE 'tbl_customers' (
    'id' BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    'shopify_customer_id' BIGINT(20) UNSIGNED NOT NULL,
    'shop_id' BIGINT(20) UNSIGNED NOT NULL,
    'email' VARCHAR(225) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    'accepts_marketing' TINYINT(1) NULL DEFAULT NULL,
    'first_name' VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    'last_name' VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    'last_order_id' BIGINT(20) NULL DEFAULT NULL,
    'total_spent' DECIMAL(12,2) NULL DEFAULT NULL,
    'phone' VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    'verified_email' TINYINT(4) NULL DEFAULT NULL,
    'updated_at' DATETIME NULL DEFAULT NULL,
    'created_at' DATETIME NULL DEFAULT NULL,
    'date_updated' DATETIME NULL DEFAULT NULL,
    'date_created' DATETIME NULL DEFAULT NULL,
    PRIMARY KEY ('id'),
    UNIQUE INDEX 'shopify_customer_id_unique' ('shopify_customer_id'),
    INDEX 'email' ('email'),
    INDEX 'shopify_customer_id' ('shopify_customer_id'),
    INDEX 'shop_id' ('shop_id')
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;


CREATE TABLE 'tbl_customers_address' (
    'id' BIGINT(20) NOT NULL AUTO_INCREMENT,
    'customer_id' BIGINT(20) NULL DEFAULT NULL,
    'shopify_address_id' BIGINT(20) NULL DEFAULT NULL,
    'shopify_customer_id' BIGINT(20) NULL DEFAULT NULL,
    'first_name' VARCHAR(50) NULL DEFAULT NULL,
    'last_name' VARCHAR(50) NULL DEFAULT NULL,
    'company' VARCHAR(50) NULL DEFAULT NULL,
    'address1' VARCHAR(250) NULL DEFAULT NULL,
    'address2' VARCHAR(250) NULL DEFAULT NULL,
    'city' VARCHAR(50) NULL DEFAULT NULL,
    'province' VARCHAR(50) NULL DEFAULT NULL,
    'country' VARCHAR(50) NULL DEFAULT NULL,
    'zip' VARCHAR(15) NULL DEFAULT NULL,
    'phone' VARCHAR(20) NULL DEFAULT NULL,
    'name' VARCHAR(50) NULL DEFAULT NULL,
    'province_code' VARCHAR(5) NULL DEFAULT NULL,
    'country_code' VARCHAR(5) NULL DEFAULT NULL,
    'country_name' VARCHAR(50) NULL DEFAULT NULL,
    'longitude' VARCHAR(250) NULL DEFAULT NULL,
    'latitude' VARCHAR(250) NULL DEFAULT NULL,
    'default' TINYINT(1) NULL DEFAULT NULL,
    'is_geo_fetched' TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id'),
    INDEX 'customer_id' ('customer_id'),
    INDEX 'shopify_address_id' ('shopify_address_id'),
    INDEX 'shopify_customer_id' ('shopify_customer_id')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CREATE TABLE 'tbl_shop_setting' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,   
    'shop_name' VARCHAR(300) NOT NULL COLLATE 'latin1_swedish_ci',
     PRIMARY KEY ('id'),
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;


CREATE TABLE 'aio_customer_tracking' (
    'id' BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    'shopify_customer_id' BIGINT(20) UNSIGNED NOT NULL,
    'email' VARCHAR(255) NULL DEFAULT NULL,
    'shop_id' BIGINT(20) UNSIGNED NOT NULL,
    'domain' VARCHAR(255) NULL DEFAULT NULL,
    'web_session_count' INT(11) NOT NULL,
    'last_seen_date' DATETIME NULL DEFAULT NULL,
    'last_contact_date' DATETIME NULL DEFAULT NULL,
    'last_email_open' DATETIME NULL DEFAULT NULL,
    'created_date' DATETIME NOT NULL,
    'is_geo_fetched' TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id'),
    INDEX 'shopify_customer_id' ('shopify_customer_id'),
    INDEX 'email' ('email'),
    INDEX 'shopify_customer_id_shop_id' ('shopify_customer_id', 'shop_id'),
    INDEX 'last_seen_date' ('last_seen_date')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

Служебные дела, выполняемые и не выполняющиеся:

1. Running:  Below query fetch the records by joining all the 4 tables, It takes only 0.300 ms.

SELECT 'c'.first_name,'c'.last_name,'c'.email, 't'.'last_seen_date', 't'.'last_contact_date', 'ssh'.'shop_name', ca.'company', ca.'address1', ca.'address2', ca.'city', ca.'province', ca.'country', ca.'zip', ca.'province_code', ca.'country_code'
FROM 'tbl_customers' AS 'c'
JOIN 'tbl_shop_setting' AS 'ssh' ON c.shop_id = ssh.id 
LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id
LEFT JOIN 'tbl_customers_address' as ca ON (c.shopify_customer_id = ca.shopify_customer_id AND ca.default = 1)
GROUP BY c.shopify_customer_id
LIMIT 20

2. Not running: Simply when try to get the count of these row stuk the query, I waited 10 min but still running.

SELECT 
     COUNT(DISTINCT c.shopify_customer_id)   -- what makes #2 different
FROM 'tbl_customers' AS 'c'
JOIN 'tbl_shop_setting' AS 'ssh' ON c.shop_id = ssh.id 
LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id
LEFT JOIN 'tbl_customers_address' as ca ON (c.shopify_customer_id = ca.shopify_customer_id AND ca.default = 1)
GROUP BY c.shopify_customer_id
LIMIT 20


3. Not running: In the #1 query we simply put the 1 Order by clause and it get stuck, I waited 10 min but still running. I study query optimization some article and tried by indexing, Right Join etc.. but still not working.

SELECT 'c'.first_name,'c'.last_name,'c'.email, 't'.'last_seen_date', 't'.'last_contact_date', 'ssh'.'shop_name', ca.'company', ca.'address1', ca.'address2', ca.'city', ca.'province', ca.'country', ca.'zip', ca.'province_code', ca.'country_code'
FROM 'tbl_customers' AS 'c'
JOIN 'tbl_shop_setting' AS 'ssh' ON c.shop_id = ssh.id 
LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id
LEFT JOIN 'tbl_customers_address' as ca ON (c.shopify_customer_id = ca.shopify_customer_id AND ca.default = 1)
GROUP BY c.shopify_customer_id
  ORDER BY 't'.'last_seen_date'    -- what makes #3 different
LIMIT 20

ОБЪЯСНЕНИЕ ВОПРОСА № 1:

ОБЪЯСНЕНИЕ ВОПРОСА № 2:

РАЗВИВАЙТЕ ВОПРОС № 3:

Любые предложения по оптимизации запроса, структуры таблицы приветствуются.

ЧТО Я ПЫТАЮСЬ ДЕЛАТЬ:

Таблица tbl_customers содержит информацию о клиенте, таблица tbl_customer_address содержит адреса клиентов (у одного клиента может быть несколько адресов), а таблица aio_customer_tracking содержит aio_customer_tracking записи клиента last_seen_date - дата посещения.

Теперь просто хочу получить и подсчитать клиентов с их адресом и информацией о посещении. Кроме того, я могу заказать любой столбец из этих трех таблиц. В моем примере я заказываю last_seen_date (порядок по умолчанию). Надеюсь, это объяснение поможет понять, что я пытаюсь сделать.

Ответы

Ответ 1

В запросе # 1, но не в двух других, оптимизатор может использовать

UNIQUE INDEX 'shopify_customer_id_unique' ('shopify_customer_id')

сократить короткий запрос

GROUP BY c.shopify_customer_id
LIMIT 20

Это связано с тем, что он может остановиться после 20 пунктов индекса. Запрос не является сверхбыстрым из-за производной таблицы (подзапрос t), которая занимает около 51 тыс. Строк.

Запрос № 2 может быть медленным просто потому, что Оптимизатор не заметил и удалил избыточный DISTINCT. Вместо этого он может думать, что он не может остановиться после 20.

Запрос № 3 должен пройти полностью через таблицу c чтобы получить каждую группу shopify_customer_id. Это связано с тем, что ORDER BY предотвращает короткое замыкание, чтобы перейти к LIMIT 20.

Столбцы в GROUP BY должны включать все неагрегатные столбцы в SELECT за исключением тех, которые уникально определены группой по столбцам. Поскольку вы сказали, что может быть несколько адресов для одного shopify_customer_id, то выборка ca.address1 не подходит в связи с GROUP BY shopify_customer_id. Аналогично, подзапрос кажется неправильным по отношению к last_seen_date, last_contact_date.

В aio_customer_tracking это изменение (к "покрывающему" индексу) может немного помочь:

INDEX ('shopify_customer_id')

INDEX ('shopify_customer_id', 'last_seen_date', 'last_contact_date')

Препарирование цели

Теперь просто хочу... подсчитать клиентов

Чтобы подсчитать клиентов, сделайте это, но не пытайтесь объединить его с "выборкой":

SELECT COUNT(*) FROM tbl_customers;

Теперь, просто я хочу, чтобы получить... клиентов...

tbl_customers - #Rows: 20 миллионов.

Конечно, вы не хотите получать 20 миллионов строк! Я не хочу думать о том, как это сделать. Просьба уточнить. И я не соглашусь разбивать страницы на несколько строк. Возможно, есть WHERE? Предложение WHERE является (как правило) самой важной частью оптимизации!

Теперь просто хочу получить... клиентов, с их одним адресом и информацией о посещении.

Предполагая, что WHERE фильтрует до "нескольких" клиентов, тогда JOINing к другой таблице, чтобы получить "любой" адрес и "любую" информацию о посещении, может быть проблематичным и/или неэффективным. Требовать "первого" или "последнего" вместо "любого" не будет легче, но может быть более значимым.

Могу ли я предложить, чтобы ваш пользовательский интерфейс сначала нашел несколько клиентов, а затем, если пользователь хочет, перейдите на другую страницу со всеми адресами и всеми посещениями. Или можно посещать сотни или более?

Кроме того, я могу заказать любой столбец из этих трех таблиц. В моем примере я заказываю last_seen_date (порядок по умолчанию).

Давайте сосредоточимся на оптимизации WHERE, затем last_seen_date на конец любого индекса.

Ответ 2

shopify_customer_id уникален в таблице tbl_customers, затем в 2-м запросе, почему вы используете отдельную shopify_customer_id и группируете ее в столбце shopify_customer_id?

Пожалуйста, избавитесь от этого.

Ответ 3

У вас слишком много индексов, и это может быть настоящим убийцей производительности, когда дело доходит до вставки, обновления и удаления, а также изредка для выбора в зависимости от настроек оптимизации.

Также удалите оператор GROUP BY.

Более того, я могу сказать о правильном использовании кластеризованных или некластеризованных индексов, GROUP BY, ORDER BY, WHERE и представлений для оптимизации запросов. Тем не менее, я думаю, что если вы удалите некоторые индексы, ваши запросы будут ускоряться. (Возможно, также переработайте свои запросы, чтобы следовать строгим стандартам SQL и быть немного более логичными, но это выходит за рамки этого вопроса.)

Еще одна вещь - что вы делаете с результатами запроса? Это где-то хранится где-то и доступно для поиска, используется для расчетов, используется для автоматических отчетов, отображения через подключение к веб-базе данных и т.д.? Это имеет значение, потому что если вам просто нужен отчет/резервное копирование или экспорт в плоский файл, тогда есть способы более эффективные способы получения этих данных. Множество различных вариантов в зависимости от того, что вы делаете.

Ответ 4

Запрос 2 содержит логическую ошибку, как указано другими: count(distinct(c.shopify_customer_id)) возвращает одно значение, поэтому ваша группа только усложняет запрос (это может действительно сделать MySQL-группировку shopize_customer_id сначала, а затем выполнить count(distinct(shopify_customer_id )) что может быть причиной как-то длительного времени выполнения

Порядок по Query 3 не может быть оптимизирован, поскольку вы присоединяетесь к подзапрос, который не может быть проиндексирован. Время, затрачиваемое на это, - это просто время, необходимое системе для заказа набора результатов.

Решение вашей проблемы будет состоять в следующем:

измените индекс shopify_customer_id (shopify_customer_id) таблицы tbl_customers_address на shopify_customer_id (shopify_customer_id, по default), чтобы оптимизировать следующий запрос
создать таблицу с результатом запроса 1 (результат), но без

LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id.
изменить таблицу результатов и добавить столбец для last_seen_date и индексов для last_seen_date и shopify_customer_id
создайте таблицу для результата этого запроса (last_Date):

SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id

Обновите таблицу результатов со значениями из таблицы last_Date

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

Весь процесс должен пройти меньше времени, чем выполнение запроса 2 или запроса 3