#mysql #join #subquery #query-performance #jalali-calendar
#mysql #Присоединиться #подзапрос #запрос-производительность #jalali-календарь
Вопрос:
У меня есть основная таблица с именем prog, как показано ниже:
CREATE TABLE `prog` (
`prog_id` int(11) NOT NULL AUTO_INCREMENT,
`prog_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`prog_edit_date` varchar(16) COLLATE utf8_persian_ci DEFAULT NULL,
`prog_name` text COLLATE utf8_persian_ci NOT NULL,
`prog_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`prog_id`),
KEY `prog_insert_date` (`prog_sabt_date`),
KEY `prog_edit_date` (`prog_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO prog VALUES
(1,'1395-01-01 11:00','1395-01-01 12:00','prog A', 'prog A description'),
(2,'1395-01-02 11:00','1395-01-02 12:00','prog B', 'prog B description'),
(3,'1395-01-03 11:00','1395-01-03 12:00','prog C', 'prog C description');
Из-за необходимости использования календаря Jalali в моем приложении я решил использовать varchar(16) для столбцов даты и сохранить их в этом формате : '1395-07-20 12:43'
.
У меня также есть три одинаковые таблицы, которые содержат несколько строк, соответствующих каждой prog_id
. Они зачисляются, пополняются и оплачиваются, как показано ниже:
CREATE TABLE `credit` (
`credit_id` int(11) NOT NULL AUTO_INCREMENT,
`credit_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`credit_edit_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`credit_prog` int(11) NOT NULL,
`credit_amount` bigint(20) NOT NULL,
`credit_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`credit_id`),
KEY `credit_prog` (`credit_prog`),
KEY `credit_insert_date` (`credit_insert_date`),
KEY `credit_edit_date` (`credit_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO credit VALUES
(1,'1395-02-01 11:00','1395-02-01 12:00',1, 100000, 'sample description'),
(2,'1395-02-02 11:00','1395-02-02 12:00',1, 200000, 'sample description'),
(3,'1395-02-03 11:00','1395-02-03 12:00',2, 300000, 'sample description'),
(4,'1395-02-04 11:00','1395-02-04 12:00',2, 400000, 'sample description'),
(5,'1395-02-05 11:00','1395-02-05 12:00',3, 500000, 'sample description'),
(6,'1395-02-06 11:00','1395-02-06 12:00',3, 600000, 'sample description');
CREATE TABLE `fund` (
`fund_id` int(11) NOT NULL AUTO_INCREMENT,
`fund_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`fund_edit_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`fund_prog` int(11) NOT NULL,
`fund_amount` bigint(20) NOT NULL,
`fund_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`fund_id`),
KEY `fund_prog` (`fund_prog`),
KEY `fund_insert_date` (`fund_insert_date`),
KEY `fund_edit_date` (`fund_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO fund VALUES
(1,'1395-03-01 11:00','1395-03-01 12:00',1, 10000, 'sample description'),
(2,'1395-03-02 11:00','1395-03-02 12:00',1, 20000, 'sample description'),
(3,'1395-03-03 11:00','1395-03-03 12:00',2, 30000, 'sample description'),
(4,'1395-03-04 11:00','1395-03-04 12:00',2, 40000, 'sample description'),
(5,'1395-03-05 11:00','1395-03-05 12:00',3, 50000, 'sample description'),
(6,'1395-03-06 11:00','1395-03-06 12:00',3, 60000, 'sample description');
CREATE TABLE `pay` (
`pay_id` int(11) NOT NULL AUTO_INCREMENT,
`pay_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`pay_edit_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`pay_prog` int(11) NOT NULL,
`pay_amount` bigint(20) NOT NULL,
`pay_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`pay_id`),
KEY `pay_prog` (`pay_prog`),
KEY `pay_insert_date` (`pay_insert_date`),
KEY `pay_edit_date` (`pay_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO pay VALUES
(1,'1395-04-01 11:00','1395-04-01 12:00',1, 1000, 'sample description'),
(2,'1395-04-02 11:00','1395-04-02 12:00',1, 2000, 'sample description'),
(3,'1395-04-03 11:00','1395-04-03 12:00',2, 3000, 'sample description'),
(4,'1395-04-04 11:00','1395-04-04 12:00',2, 4000, 'sample description'),
(5,'1395-04-05 11:00','1395-04-05 12:00',3, 5000, 'sample description'),
(6,'1395-04-06 11:00','1395-04-06 12:00',3, 6000, 'sample description');
Теперь я хочу, чтобы каждая строка программы содержала соответствующую сумму кредита, фонда и оплаты, а также дату последнего редактирования, которая должна быть построена из всех таблиц. Мой запрос:
SELECT
prog_id,
GREATEST(IFNULL(credit_edit_date,''),IFNULL(fund_edit_date,''),IFNULL(pay_edit_date,'')) last_edit_date,
prog_name,
credit_amount,
fund_amount,
pay_amount,
prog_desc
FROM prog
LEFT JOIN (
SELECT
credit_prog,
sum(credit_amount) as credit_amount,
max(credit_edit_date) as credit_edit_date
FROM credit GROUP BY credit_prog
) as credit ON credit_prog=prog_id
LEFT JOIN (
SELECT
fund_prog,
sum(fund_amount) as fund_amount,
max(fund_edit_date) as fund_edit_date
FROM fund GROUP BY fund_prog
) as fund ON fund_prog=prog_id
LEFT JOIN (
SELECT
pay_prog,
sum(pay_amount) as pay_amount,
max(pay_edit_date) as pay_edit_date
FROM pay GROUP BY pay_prog
) as pay ON pay_prog=prog_id
ORDER BY (SELECT last_edit_date) DESC
Я получаю истинный результат, но в реальных случаях с несколькими строками в таблицах требуется слишком много времени для ответа. Я думаю, что основной проблемой является last_edit_date
столбец и потому, что оптимизатор MYSQL не может учитывать индекс в этих полях даты в подзапросах.
Есть идеи по увеличению времени выполнения?
Комментарии:
1. Если бы это был я, я думаю, я бы сохранил дату ansi и обработал преобразование в другом месте.
2. @Strawberry Вы уверены, что изменение типов столбцов даты может значительно сократить время? Я думаю, что основной проблемой является индексация в подзапросах
3. Дублирование dba.stackexchange.com/questions/152456 /…
Ответ №1:
Если ваши подзапросы используют индексы, отличные от первичного ключа (а они, вероятно, используют), это фактически замедлит ваш запрос, потому что это заставит MySQL вернуться назад и принудительно ввести вашу таблицу вместо того, чтобы один раз читать всю таблицу в память и делать все остальное там.
Попробуйте добавить следующие индексы:
alter table credit add index
idx_credit_p_a_ed(credit_prog, credit_amount, credit_edit_date);
alter table pay add index
idx_pay_p_a_ed(pay_prog, pay_amount, pay_edit_date);
alter table fund add index
idx_fund_p_a_ed(fund_prog, fund_amount, fund_edit_date);
Если вы не используете MySQL 5.7 (или если он не принимает эти индексы по какой-либо другой причине), вам, возможно, придется заставить его использовать их, поэтому добавьте, например
... FROM credit force index (idx_credit_p_a_ed) GROUP BY credit_prog ...
к подзапросам.
Вы можете получить некоторую дополнительную скорость, если измените столбцы даты на тип данных 1 байт на символ, например, использовать latin1_swedish_ci
вместо utf8_persian_ci
, поскольку вы просто храните там даты. Это уменьшит размеры вашего индекса и таблицы и, следовательно, скорость (вероятно, значительно).
Кроме того, измените ORDER BY (SELECT last_edit_date) DESC
на ORDER BY last_edit_date DESC
.
Комментарии:
1. Спасибо, я создал индексы и использовал force index, но, похоже, существенных изменений не произошло
2. @MohammadHasanBakhtiarifar Можете ли вы опубликовать
explain
для своего запроса (просто добавьтеexplain
непосредственно передselect
)?3. Но вы также удалили
SELECT
изORDER BY
? Это предотвращает использование любого индекса.