服务器上的是5.5.24,本地是5.5.31(按理来说不会有太大差别的?!)coupon表和coupon_item表inner join连接
两个表都只有主键索引
EXPLAIN SELECT coupon_item.id, coupon_item.sn, coupon_item.password, coupon.distribute_type sn_distribute_type, coupon_item.distribute, coupon_item.distribute_time, coupon_item.status, coupon_item.channel, coupon.sn sn_coupon
FROM `coupon_item`
INNER JOIN coupon ON coupon.id = coupon_item.coupon_id
ORDER BY coupon_item.id DESC
LIMIT 0 , 505.5.24:
5.5.31:
在本地添加了coupon_item.coupon_id的索引后:
1.这两种差别是什么原因呢?
2.这种连接语句应该是很最常见的连接了。应该如何下手进行优化呢?
mysqlinner join
两个表都只有主键索引
EXPLAIN SELECT coupon_item.id, coupon_item.sn, coupon_item.password, coupon.distribute_type sn_distribute_type, coupon_item.distribute, coupon_item.distribute_time, coupon_item.status, coupon_item.channel, coupon.sn sn_coupon
FROM `coupon_item`
INNER JOIN coupon ON coupon.id = coupon_item.coupon_id
ORDER BY coupon_item.id DESC
LIMIT 0 , 505.5.24:
5.5.31:
在本地添加了coupon_item.coupon_id的索引后:
1.这两种差别是什么原因呢?
2.这种连接语句应该是很最常见的连接了。应该如何下手进行优化呢?
mysqlinner join
1.本地数据量是177万、服务器上是178万(本地是前几天从服务器上导的)
2.coupon_id索引是很有必要。但是本例中第一张截图却是理想的情况,不是吗?
及explain select ..直接贴文本不要贴图以便他人分析。
只有主键索引(coupon.id, coupon_item.id),所以就没有贴上来。服务器上的:
Table:coupon
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:id
Collation:A
Cardinality:95
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
Index_comment:Table:coupon_item
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:id
Collation:A
Cardinality:1787019
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
Index_comment:本地服务器:
Table:coupon
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:id
Collation:A
Cardinality:4
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
Index_comment:Table:coupon_item
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:id
Collation:A
Cardinality:1776538
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
Index_comment:
explain ...Server 2:
show index from ..
explain ...否则别人无法准确判断对应关系,另外不要加 /G
id: 1
select_type: SIMPLE
table: coupon
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 95
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: coupon_item
type: ref
possible_keys: i_couponID
key: i_couponID
key_len: 4
ref: weimeigou_online_0426.coupon.id
rows: 8790
Extra: