关于MYSQL效率的问题,100分诚恳赐教!!
三张表的查询:
SQL code
非常诚恳的想请教达人们能给出一些优化与修正的方案,提高查询效率。
SQL code
EXPLAIN SELECT f.fid,t.tid,t.subject FROM bbs_threads t INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid INNER JOIN bbs_threadtags tt ON t.tid=tt.tid AND tt.tagname='标签' ORDER BY t.dateline DESC LIMIT 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tt type: ref possible_keys: tagname,tid key: tagname key_len: 40 ref: const rows: 1366 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t type: eq_ref possible_keys: PRIMARY,displayorder,typeid key: PRIMARY key_len: 3 ref: bbs.tt.tid rows: 1 Extra: Using where *************************** 3. row *************************** id: 1 select_type: SIMPLE table: f type: eq_ref possible_keys: PRIMARY,forum key: PRIMARY key_len: 2 ref: bbs.t.fid rows: 1 Extra: Using where 3 rows in set (0.08 sec) EXPLAIN SELECT f.fid,t.tid,t.subject FROM bbs_threadtags tt,bbs_threads t,bbs_forums f WHERE f.fid=t.fid AND f.status=1 AND t.tid=tt.tid AND tt.tagname='标签' ORDER BY t.dateline DESC LIMIT 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tt type: ref possible_keys: tagname,tid key: tagname key_len: 40 ref: const rows: 1366 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t type: eq_ref possible_keys: PRIMARY,displayorder,typeid key: PRIMARY key_len: 3 ref: bbs.tt.tid rows: 1 Extra: Using where *************************** 3. row *************************** id: 1 select_type: SIMPLE table: f type: eq_ref possible_keys: PRIMARY,forum key: PRIMARY key_len: 2 ref: bbs.t.fid rows: 1 Extra: Using where 3 rows in set (0.03 sec) EXPLAIN之后,结果一样。这两个效率是一样的吗? 其中这三张表的索引结构分别如下: mysql> SHOW INDEX FROM bbs_threads \G *************************** 1. row *************************** Table: bbs_threads Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: tid Collation: A Cardinality: 2382067 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: bbs_threads Non_unique: 1 Key_name: digest Seq_in_index: 1 Column_name: digest Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: bbs_threads Non_unique: 1 Key_name: sortid Seq_in_index: 1 Column_name: sortid Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 4. row *************************** Table: bbs_threads Non_unique: 1 Key_name: displayorder Seq_in_index: 1 Column_name: fid Collation: A Cardinality: 346 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 5. row *************************** Table: bbs_threads Non_unique: 1 Key_name: displayorder Seq_in_index: 2 Column_name: displayorder Collation: A Cardinality: 346 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 6. row *************************** Table: bbs_threads Non_unique: 1 Key_name: displayorder Seq_in_index: 3 Column_name: lastpost Collation: A Cardinality: 297758 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 7. row *************************** Table: bbs_threads Non_unique: 1 Key_name: typeid Seq_in_index: 1 Column_name: fid Collation: A Cardinality: 346 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 8. row *************************** Table: bbs_threads Non_unique: 1 Key_name: typeid Seq_in_index: 2 Column_name: typeid Collation: A Cardinality: 346 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 9. row *************************** Table: bbs_threads Non_unique: 1 Key_name: typeid Seq_in_index: 3 Column_name: displayorder Collation: A Cardinality: 346 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 10. row *************************** Table: bbs_threads Non_unique: 1 Key_name: typeid Seq_in_index: 4 Column_name: lastpost Collation: A Cardinality: 297758 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 11. row *************************** Table: bbs_threads Non_unique: 1 Key_name: recommends Seq_in_index: 1 Column_name: recommends Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 12. row *************************** Table: bbs_threads Non_unique: 1 Key_name: heats Seq_in_index: 1 Column_name: heats Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 13. row *************************** Table: bbs_threads Non_unique: 1 Key_name: authorid Seq_in_index: 1 Column_name: authorid Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 14. row *************************** Table: bbs_threads Non_unique: 1 Key_name: bbs_threads_index1 Seq_in_index: 1 Column_name: dateline Collation: A Cardinality: 183235 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 14 rows in set (0.01 sec) mysql> SHOW INDEX FROM bbs_threadtags \G *************************** 1. row *************************** Table: bbs_threadtags Non_unique: 1 Key_name: tagname Seq_in_index: 1 Column_name: tagname Collation: A Cardinality: 12291 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: bbs_threadtags Non_unique: 1 Key_name: tid Seq_in_index: 1 Column_name: tid Collation: A Cardinality: 2382066 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 2 rows in set (0.00 sec) mysql> SHOW INDEX FROM bbs_forums\G *************************** 1. row *************************** Table: bbs_forums Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: fid Collation: A Cardinality: 387 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: bbs_forums Non_unique: 1 Key_name: forum Seq_in_index: 1 Column_name: status Collation: A Cardinality: 3 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: bbs_forums Non_unique: 1 Key_name: forum Seq_in_index: 2 Column_name: type Collation: A Cardinality: 3 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 4. row *************************** Table: bbs_forums Non_unique: 1 Key_name: forum Seq_in_index: 3 Column_name: displayorder Collation: A Cardinality: 96 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 5. row *************************** Table: bbs_forums Non_unique: 1 Key_name: fup Seq_in_index: 1 Column_name: fup Collation: A Cardinality: 38 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 5 rows in set (0.00 sec)
非常诚恳的想请教达人们能给出一些优化与修正的方案,提高查询效率。
作者: rczjp 发布时间: 2011-06-08
执行时间哪个少?
EXPLAIN SELECT f.fid,t.tid,t.subject FROM bbs_threads t INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid INNER JOIN bbs_threadtags tt ON
t.tid=tt.tid AND tt.tagname='标签' ORDER BY t.dateline DESC LIMIT 10 \G
TT是什么表?
你的两个SQL语句都是内连接,只是SQL标准不同写法不同,在索引情况一致的情况下,效率应该没有差别
OR 差别很小
EXPLAIN SELECT f.fid,t.tid,t.subject FROM bbs_threads t INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid INNER JOIN bbs_threadtags tt ON
t.tid=tt.tid AND tt.tagname='标签' ORDER BY t.dateline DESC LIMIT 10 \G
TT是什么表?
你的两个SQL语句都是内连接,只是SQL标准不同写法不同,在索引情况一致的情况下,效率应该没有差别
OR 差别很小
作者: WWWWA 发布时间: 2011-06-08