关于mysql优化，临时表的问题？

Question

show global status like 'created%';
结果：
variable_name               value
Created_tmp_disk_tables     71731
Created_tmp_files           7
Created_tmp_tables          116312下面的是my.cnf中的几个参数配置：
key_buffer_size = 640M
tmp_table_size = 768M
max_heap_table_size = 640M下面的一句sql觉着不好，想做些改动，请各位给些建议：
select a.effectivetime,a.dozen_mount,a2.spe_desc,a.prod_id,a.pic_path,a.prod_ename,a.offline_dt,a.prod_unit,a.prod_url,
                                                b.seller_id,b.nickname,b.seller_score,
                                                min(c.seller_price) as min_seller_price,max(c.seller_price) as max_seller_price,min(c.prod_price_id) as prod_price_id,min(c.start_pur_mount) as start_pur_mount,min(c.discount_price) as min_discount_price,max(c.discount_price) as max_discount_price,d.normal
from t_ss_product_nomal a
left join t_ss_product_desc a2 on a.prod_id=a2.prod_id
left join t_ss_seller_info b on a.seller_id=b.seller_id
left join t_ss_prod_price c on a.prod_id=c.prod_id
 left join t_ss_prod_shiptype d on a.prod_id=d.prod_id and d.normal = 3
where a.prod_status='2' and a.prod_id in (4115927,3013279,2860916,2772148,2361593,4198851,4197321,4197320,2907172,2907167,2907165
,2797050,2797005,
2794147,2764516,2739483,2723411,2687011,2677738,2622294,2610112,2590534,2590008,2579213) 
group by a.prod_id；谢谢

Answer 1

1 max_heap_table_size太小导致内存临时表转磁盘临时表
2 有text或者blog字段导致不能用内存临时表而是用磁盘临时表

Answer 2

1 max_heap_table_size太小导致内存临时表转磁盘临时表
2 有text或者blog字段导致不能用内存临时表而是用磁盘临时表

Answer 3

show index from tabl1;
show index from tabl2;
...
explain select a.effectivetime,a.dozen_mount,a2.spe_desc,a.prod_id,a.pic_path,a.prod_ename,a.offline_dt,a.prod_unit,a.prod_url,
                                               b.seller_id,b.nickname,b.seller_score,
                                               min(c.seller_price) as min_seller_price,max(c.seller_price) as max_seller_price,min(c.prod_price_id) as prod_price_id,min(c.start_pur_mount) as start_pur_mount,min(c.discount_price) as min_discount_price,max(c.discount_price) as max_discount_price,d.normal
from t_ss_product_nomal a
left join t_ss_product_desc a2 on a.prod_id=a2.prod_id
left join t_ss_seller_info b on a.seller_id=b.seller_id
left join t_ss_prod_price c on a.prod_id=c.prod_id
  left join t_ss_prod_shiptype d on a.prod_id=d.prod_id and d.normal = 3
where a.prod_status='2' and a.prod_id in (4115927,3013279,2860916,2772148,2361593,4198851,4197321,4197320,2907172,2907167,2907165
,2797050,2797005,
2794147,2764516,2739483,2723411,2687011,2677738,2622294,2610112,2590534,2590008,2579213)  
group by a.prod_id；
的结果贴出来以供分析。

Answer 4

show index from t_ss_product_nomal 的结果:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
t_ss_product_nomal 0 PRIMARY 1 prod_id A 2368497 BTREE
t_ss_product_nomal 1 ind_t_ss_product_bak20090206_sellerid 1 seller_id A 4955 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_bak20090206_createdt 1 create_dt A 2368497 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_bak20090206_prodcatcode 1 prod_catcode A 2585 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_bak20090206_effectivetime 1 effectivetime A 2802 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_bak20090206_prodstatus 1 prod_status A 6 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_sellerid_prodstatus 1 seller_id A 4955 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_sellerid_prodstatus 2 prod_status A 5060 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_prodid_status 1 prod_id A 2368497 BTREE
t_ss_product_nomal 1 ind_t_ss_product_prodid_status 2 prod_status A 2368497 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_effectivetime_prodstatus 1 effectivetime A 2802 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_effectivetime_prodstatus 2 prod_status A 2909 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_sellerid_prodstatus_prodid 1 seller_id A 4955 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_sellerid_prodstatus_prodid 2 prod_status A 5060 YES BTREE
t_ss_product_nomal 1 ind_t_ss_product_sellerid_prodstatus_prodid 3 prod_id A 2368497 BTREE
t_ss_product_nomal 1 idx_product_nomal_sellerid 1 seller_id A 4955 YES BTREE
t_ss_product_nomal 1 idx_product_nomal_lastupdate 1 last_update_dt A 592124 YES BTREE show index from t_ss_product_desc 的结果：
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
t_ss_product_desc 0 PRIMARY 1 prod_id A 2380808 BTREE show index from t_ss_seller_info 的结果：
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
t_ss_seller_info 0 PRIMARY 1 seller_id A 12583 BTREE
t_ss_seller_info 1 ind_t_ss_seller_info_regdate 1 reg_date A 12583 YES BTREE
t_ss_seller_info 1 ind_t_ss_seller_info_selleremail 1 seller_email A 12583 YES BTREE
t_ss_seller_info 1 ind_t_ss_seller_info_nickname 1 nickname A 12583 YES BTREE
t_ss_seller_info 1 ind_t_ss_seller_info_recommendsellerid 1 recommend_seller_id A 12583 YES BTREE

Answer 5

show index from t_ss_prod_price 的结果：
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
t_ss_prod_price 0 PRIMARY 1 prod_price_id A 6850566 BTREE
t_ss_prod_price 1 ind_t_ss_prod_price_prodid 1 prod_id A 2283522 YES BTREE show index from t_ss_prod_shiptype 的结果：
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
t_ss_prod_shiptype 0 PRIMARY 1 prod_shptype_id A 4851476 BTREE
t_ss_prod_shiptype 1 ind_prodid 1 prod_id A 2425738 YES BTREE
t_ss_prod_shiptype 1 ind_normal 1 normal A 4 YES BTREE
t_ss_prod_shiptype 1 ind_shp_type 1 shp_type A 7 YES BTREE

Answer 6

explain sql的结果：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a range PRIMARY,ind_t_ss_product_bak20090206_prodstatus,ind_t_ss_product_prodid_status PRIMARY 4 24 Using where
1 SIMPLE a2 eq_ref PRIMARY PRIMARY 4 thsdb.a.prod_id 1
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 thsdb.a.seller_id 1
1 SIMPLE c ref ind_t_ss_prod_price_prodid ind_t_ss_prod_price_prodid 5 thsdb.a.prod_id 3
1 SIMPLE d ref ind_prodid,ind_normal ind_prodid 5 thsdb.a.prod_id 2