php - 对具有数百万条记录的 2 个表进行连接速度更快

Question

我有两张 table

security_stat => 拥有 400 万条记录

security_trade => 拥有1000万条记录

我已经成功运行了这个查询，但是我如何优化这个查询才能在10秒内至少查询100,000条记录(可能吗？)..目前它非常非常慢。

SELECT `sec_stat_sec_name`, `sec_stat_date`, `sec_stat_market`, `sec_trade_close`, `sec_stat_date` 
FROM security_stat` LEFT JOIN `security_trade` 
ON `security_trade`.`sec_trade_sec_name` = `security_stat`.`sec_stat_sec_name` 
    and `security_trade`.`sec_trade_date` = `security_stat`.`sec_stat_date` 
limit 100,000

我在 sec_trade_sec_name、sec_stat_sec_name、sec_trade_date 和 sec_stat_date 上有 INDEX

我尝试使用 WHERE sec_stat_date >= 2005-01-01 限制结果，但这没有多大帮助。 (我的记录范围为1975年至2014年)

编辑

security_stat架构

CREATE TABLE `security_stat` (
  `sec_stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sec_stat_date` date NOT NULL,
  `sec_stat_sec_name` varchar(255) NOT NULL,
  `sec_stat_sec_id` int(11) NOT NULL,
  `sec_stat_market` varchar(255) NOT NULL,
  `sec_stat_industry` int(11) NOT NULL,
  `sec_stat_sector` int(11) NOT NULL,
  `sec_stat_subsector` int(11) NOT NULL,
  `sec_stat_sec_type` varchar(1) NOT NULL,
  `sec_stat_status` varchar(2) NOT NULL,
  `sec_stat_benefit` varchar(2) NOT NULL,
  `sec_stat_listed_share` bigint(20) NOT NULL,
  `sec_stat_earn_p_share` decimal(12,5) NOT NULL,
  `sec_stat_value` decimal(9,2) NOT NULL,
  `sec_stat_p_of_earn` int(11) NOT NULL,
  `sec_stat_as_date` date NOT NULL,
  `sec_stat_div_p_share` decimal(16,12) NOT NULL,
  `sec_stat_p_of_div` int(11) NOT NULL,
  `sec_stat_end_date_div` date NOT NULL,
  `sec_stat_pe` decimal(8,2) NOT NULL,
  `sec_stat_pbv` decimal(8,2) NOT NULL,
  `sec_stat_div_yield` decimal(8,2) NOT NULL,
  `sec_stat_par_value` decimal(16,5) NOT NULL,
  `sec_stat_market_cap` decimal(20,2) NOT NULL,
  `sec_stat_turn_ratio` decimal(8,2) NOT NULL,
  `sec_stat_npg_flag` varchar(1) NOT NULL,
  `sec_stat_acc_div` decimal(16,12) NOT NULL,
  `sec_stat_acc_no_of_pay` int(11) NOT NULL,
  `sec_stat_div_pay_ratio` decimal(6,2) NOT NULL,
  `sec_stat_earn_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sec_stat_ev` decimal(20,2) DEFAULT NULL,
  `sec_stat_ev_revenue` decimal(20,2) DEFAULT NULL,
  `sec_stat_ev_ebit` decimal(20,2) DEFAULT NULL,
  `sec_stat_ev_ebitda` decimal(20,2) DEFAULT NULL,
  `sec_stat_earning_yield` decimal(10,5) DEFAULT NULL,
  `sec_stat_ps_ratio` decimal(10,5) DEFAULT NULL,
  PRIMARY KEY (`sec_stat_id`),
  UNIQUE KEY `sec_stat_date_name_id_cap` (`sec_stat_date`,`sec_stat_market`,`sec_stat_sec_id`,`sec_stat_sector`),
  KEY `sec_stat_date` (`sec_stat_date`),
  KEY `sec_stat_sec_name` (`sec_stat_sec_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3598612 ;

security_trade架构

CREATE TABLE `security_trade` (
  `sec_trade_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sec_trade_date` date NOT NULL,
  `sec_trade_sec_name` varchar(20) NOT NULL,
  `sec_trade_sec_id` int(11) NOT NULL,
  `sec_trade_market` varchar(1) NOT NULL,
  `sec_trade_trading_method` varchar(1) NOT NULL,
  `sec_trade_trade_report` varchar(1) NOT NULL,
  `sec_trade_prior_date` date NOT NULL,
  `sec_trade_prior` decimal(8,2) NOT NULL,
  `sec_trade_open` decimal(8,2) NOT NULL,
  `sec_trade_high` decimal(8,2) NOT NULL,
  `sec_trade_low` decimal(8,2) NOT NULL,
  `sec_trade_close` decimal(8,2) NOT NULL,
  `sec_trade_last_bid` decimal(8,2) NOT NULL,
  `sec_trade_last_offer` decimal(8,2) NOT NULL,
  `sec_trade_transaction` int(11) NOT NULL,
  `sec_trade_volume` bigint(20) NOT NULL,
  `sec_trade_value` decimal(20,2) NOT NULL,
  `sec_trade_avg_price` decimal(8,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`sec_trade_id`),
  UNIQUE KEY `sec_trade_close` (`sec_trade_date`,`sec_trade_sec_name`,`sec_trade_market`,`sec_trade_trade_report`,`sec_trade_trading_method`),
  KEY `security_trade_sec_trade_sec_name_index` (`sec_trade_sec_name`),
  KEY `security_trade_sec_trade_date_index` (`sec_trade_date`),
  KEY `security_trade_sec_trade_prior_date_index` (`sec_trade_prior_date`),
  KEY `security_trade_sec_trade_close_index` (`sec_trade_close`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10019817 ;

我的最终查询实际上会有更多

WHERE sec_stat_earning_yield IS NULL
ORDER BY updated_at ASC

但是因为当我将这两个语句添加到 LIMIT 为 1,000 条记录的查询中时，它会使我的查询变得更慢(可能是因为我在这两列上没有索引？)

提前致谢

Answer 1

将以下内容作为您的实际查询:

SELECT `sec_stat_sec_name`, `sec_stat_date`, `sec_stat_market`, `sec_trade_close`, `sec_stat_date` 
FROM `security_stat` LEFT JOIN `security_trade` 
ON `security_trade`.`sec_trade_sec_name` = `security_stat`.`sec_stat_sec_name` 
    and `security_trade`.`sec_trade_date` = `security_stat`.`sec_stat_date` 
WHERE sec_stat_earning_yield IS NULL
ORDER BY updated_at ASC
limit 100,000

您可以通过两种方式过滤 security_stat 表:
1.仅当 sec_stat_earning_yield IS NULL
2. 按 updated_at 排序的前 100k 条记录

注意:我假设您的意思是security_stat.updated_at，但您没有说清楚。

为了尽可能便宜地添加一个涵盖这两个字段的索引(sec_stat_earning_yield，updated_at)。

注意:添加变化很大的索引，尤其是当索引内的记录顺序发生变化时，可能会使 INSERT 变慢。 您将需要平衡 INSERT 性能和 SELECT 性能。

然后您加入交易表，因此您希望查找尽可能快，这可以通过该表上覆盖 (sec_trade_sec_name, sec_trade_date, sec_trade_close) 的索引来实现。
- 索引中的前两个字段使查找更简单
- 索引中的最后一个字段意味着 DBMS 可以避免查找表

完成后，您还可以查看 EXPLAIN 计划，尽管相对复杂，但它将为您提供关键信息，帮助您了解优化目标的最佳位置。