【MYSQL】Mysql长时间SQL优化一例（force index）

SELECT SUBSTR(eff_time,1,10),ns_name,COUNT(*) FROM HEHEDA WHERE isActive = 1 AND stype <> 'report' AND usefull = 1 AND eff_time > '2014-01-01' AND itype =1 AND ns_name IN ('凤凰网', '新浪财经', '和讯网', '金融界', '腾讯网', '中国经济网', '财经网', '中证网', '中国证券网', '中金在线', '经济观察网') GROUP BY SUBSTR(eff_time,1,10),ns_name ORDER BY ns_name,SUBSTR(eff_time,1,10) DESC; show index from HEHEDA; Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1 ID A 31246724 BTREE 1 news_url A 31246724 150 YES BTREE 2 stype A 31246724 YES BTREE 1 group_id A 31246724 BTREE 1 news_id A 31246724 BTREE 2 has_summary A 31246724 YES BTREE 3 stype A 31246724 YES BTREE 1 insert_time A 31246724 YES BTREE 1 news_fetch_time A 31246724 YES BTREE 1 update_time A 31246724 YES BTREE 1 news_publish_time A 31246724 YES BTREE 2 news_id A 31246724 BTREE 1 ns_name A 54247 YES BTREE 2 insert_time A 31246724 YES BTREE 1 ns_name A 78707 YES BTREE 2 news_title A 31246724 255 YES BTREE 3 news_publish_time A 31246724 YES BTREE 1 itype A 3188 YES BTREE 1 eff_time A 15623362 YES BTREE 1 ns_name A 86079 YES BTREE 2 itype A 72666 YES BTREE 1 itype A 1200 YES BTREE 2 stype A 2402 YES BTREE 3 ns_name A 164456 YES BTREE --修改SQL SELECT SUBSTR(eff_time, 1, 10), ns_name, COUNT(*) FROM HEHEDA FORCE INDEX (HEHEDA_ix_10) WHERE itype = 1 AND stype <> 'report' AND ns_name IN ( '凤凰网', '新浪财经', '和讯网', '金融界', '腾讯网', '中国经济网', '财经网', '中证网', '中国证券网', '中金在线', '经济观察网' ) AND isActive = 1 AND usefull = 1 AND eff_time > '2014-01-01' GROUP BY SUBSTR(eff_time, 1, 10), ns_name ORDER BY ns_name, SUBSTR(eff_time, 1, 10) DESC;