我有两个表
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
我尝试用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
，但因为当我添加这两个语句到查询的限制1000条记录，它使我的查询更慢(可能是因为我没有索引这两列?)
Thanks in Advance
以以下内容作为实际查询:
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
注意:我假设你是指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计划，尽管相对复杂，它将为你提供关键信息，以了解优化的最佳目标位置。
首先，尝试创建索引来匹配连接:
security_trade (sec_trade_sec_name, sec_trade_date)
security_stat (sec_stat_sec_name, sec_stat_date)
或
security_stat (sec_stat_earning_yield, sec_stat_sec_name, sec_stat_date)
并且正如上面的评论所指出的，您的"Limit"子句可能会导致结果集没有明确定义。