mysql - 如何提高 mysql 查询执行性能

Question

我有一个名为 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 优化器无法考虑子查询中这些日期字段的索引。

sqlfiddle

有什么办法可以缩短执行时间吗？

Answer 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。