关于MYSQL效率的问题，100分诚恳赐教！！

Question

三张表的查询：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)非常诚恳的想请教达人们能给出一些优化与修正的方案，提高查询效率。

Answer 1

执行时间哪个少？
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 \GTT是什么表？
你的两个SQL语句都是内连接，只是SQL标准不同写法不同，在索引情况一致的情况下，效率应该没有差别
OR 差别很小

Answer 2

恩，第一个问题效率应该一样，测试的时候感觉差不多重点是三张表的联表查询，应该怎么对这三张表做索引，复合索引？效率最高呢。其中最关键的是bbs_threadtags 这张表，由于数据量有1000多万了，所以查询的时候感觉有点慢了它的表结构是
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| tagname | char(20)         | NO   | MUL | NULL    |       |
| tid     | int(10) unsigned | NO   | MUL | NULL    |       |
+---------+------------------+------+-----+---------+-------+网上我搜索过一些文章是说，做复合索引提高效率，请问这三张表，我应该怎么做复合索引。
bbs_threads   主题表
bbs_forums    版块表
bbs_threadtags   主题对应的标签表

Answer 3

另外我想请问下，AND之间的顺序有没有关系？应该怎么写？

Answer 4

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
是这个SQL语句
bbs_forum：fid、status
bbs_threads：fid、tid、dateline
bbs_threadtags：tid、tagname另外我想请问下，AND之间的顺序有没有关系？应该怎么写？
MYSQL会自动优化

Answer 5

bbs_forum：fid、status
bbs_threads：fid、tid、dateline
bbs_threadtags：tid、tagname你好，列出的字段的这三张表都单独做了索引。
请问需要做复合索引来提高效率吗？

Answer 6

bbs_threadtags： tid、tagname 不做索引，速度非常慢
做复合索引也是复合索引的结果是这样：
*************************** 1. row ***************************
       Table: bbs_threadtags
  Non_unique: 1
    Key_name: tid_tagname
Seq_in_index: 1
 Column_name: tid
   Collation: A
 Cardinality: 2382066
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: bbs_threadtags
  Non_unique: 1
    Key_name: tid_tagname
Seq_in_index: 2
 Column_name: tagname
   Collation: A
 Cardinality: 11910330
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
2 rows in set (0.06 sec)我不明白的是，这种复合索引是不是在两个字段上面都有了索引？单独建立索引的效率上来了，我删除两个单独的索引，做复合索引速度慢？

Answer 7

嗯，你好因为不带\G，输出的格式不清晰主要是想优化这条SQL，三张表查询一般是需要做哪些索引，复合索引。。
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

Answer 8

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 ;sql语句的分析如下：
+----+-------------+-------+--------+-----------------------------+---------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys               | key     | key_len | ref               | rows | Extra|+
----+-------------+-------+--------+-----------------------------+---------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | tt    | ref    | tid_tagname,tid,tagname     | tagname | 40      | const             | 1291 | Using where; Using temporary; Using filesort|
|  1 | SIMPLE      | t     | eq_ref | PRIMARY,displayorder,typeid | PRIMARY | 3      | bbs.tt.tid |    1 | Using where|
|  1 | SIMPLE      | f     | eq_ref | PRIMARY,forum               | PRIMARY | 2      | bbs.t.fid  |    1 | Using where|
+----+-------------+-------+--------+-----------------------------+---------+---------+-------------------+------+----------------------------------------------+
3 rows in set (0.05 sec)
请指教

Answer 9

三张表的结构如下：mysql> DESCRIBE bbs_threads;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra  |
+-----------------+-----------------------+------+-----+---------+----------------+
| tid             | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| fid             | smallint(6) unsigned  | NO   | MUL | 0       |  |
| iconid          | smallint(6) unsigned  | NO   |     | 0       |  |
| typeid          | smallint(6) unsigned  | NO   |     | 0       |  |
| sortid          | smallint(6) unsigned  | NO   | MUL | 0       |  |
| readperm        | tinyint(3) unsigned   | NO   |     | 0       |  |
| price           | smallint(6)           | NO   |     | 0       |  |
| author          | char(15)              | NO   |     |         |  |
| authorid        | mediumint(8) unsigned | NO   | MUL | 0       |  |
| subject         | char(80)              | NO   |     |         |  |
| dateline        | int(10) unsigned      | NO   | MUL | 0       |  |
| lastpost        | int(10) unsigned      | NO   |     | 0       |  |
| lastposter      | char(15)              | NO   |     |         |  |
| views           | int(10) unsigned      | NO   |     | 0       |  |
| replies         | mediumint(8) unsigned | NO   |     | 0       |  |
| displayorder    | tinyint(1)            | NO   |     | 0       |  |
| highlight       | tinyint(1)            | NO   |     | 0       |  |
| digest          | tinyint(1)            | NO   | MUL | 0       |  |
| rate            | tinyint(1)            | NO   |     | 0       |  |
| special         | tinyint(1)            | NO   |     | 0       |  |
| attachment      | tinyint(1)            | NO   |     | 0       |  |
| moderated       | tinyint(1)            | NO   |     | 0       |  |
| closed          | mediumint(8) unsigned | NO   |     | 0       |  |
| itemid          | mediumint(8) unsigned | NO   |     | 0       |  |
| supe_pushstatus | tinyint(1)            | NO   |     | 0       |  |
| recommends      | smallint(6)           | NO   | MUL | NULL    |  |
| recommend_add   | smallint(6)           | NO   |     | NULL    |  |
| recommend_sub   | smallint(6)           | NO   |     | NULL    |  |
| heats           | int(10) unsigned      | NO   | MUL | 0       |  |
| status          | smallint(6) unsigned  | NO   |     | 0       |  |
+-----------------+-----------------------+------+-----+---------+----------------+mysql> DESCRIBE bbs_forums;
+------------------+-----------------------------+------+-----+---------+----------------+
| Field            | Type                        | Null | Key | Default | Extra         |
+------------------+-----------------------------+------+-----+---------+----------------+
| fid              | smallint(6) unsigned        | NO   | PRI | NULL    | auto_increment |
| fup              | smallint(6) unsigned        | NO   | MUL | 0       |         |
| type             | enum('group','forum','sub') | NO   |     | forum   |         |
| name             | char(50)                    | NO   |     |         |         |
| status           | tinyint(1)                  | NO   | MUL | 0       |         |
| displayorder     | smallint(6)                 | NO   |     | 0       |         |
| styleid          | smallint(6) unsigned        | NO   |     | 0       |         |
| threads          | mediumint(8) unsigned       | NO   |     | 0       |         |
| posts            | mediumint(8) unsigned       | NO   |     | 0       |         |
| todayposts       | mediumint(8) unsigned       | NO   |     | 0       |         |
| lastpost         | char(110)                   | NO   |     |         |         |
| allowsmilies     | tinyint(1)                  | NO   |     | 0       |         |
| allowhtml        | tinyint(1)                  | NO   |     | 0       |         |
| allowbbcode      | tinyint(1)                  | NO   |     | 0       |         |
| allowimgcode     | tinyint(1)                  | NO   |     | 0       |         |
| allowmediacode   | tinyint(1)                  | NO   |     | 0       |         |
| allowanonymous   | tinyint(1)                  | NO   |     | 0       |         |
| allowshare       | tinyint(1)                  | NO   |     | 0       |         |
| allowpostspecial | smallint(6) unsigned        | NO   |     | 0       |         |
| allowspecialonly | tinyint(1) unsigned         | NO   |     | 0       |         |
| alloweditrules   | tinyint(1)                  | NO   |     | 0       |         |
| allowfeed        | tinyint(1)                  | NO   |     | 1       |         |
| recyclebin       | tinyint(1)                  | NO   |     | 0       |         |
| modnewposts      | tinyint(1)                  | NO   |     | 0       |         |
| jammer           | tinyint(1)                  | NO   |     | 0       |         |
| disablewater | tinyint(1)                  | NO   |     | 0       |         |
| inheritedmod     | tinyint(1)                  | NO   |     | 0       |         |
| autoclose        | smallint(6)                 | NO   |     | 0       |         |
| forumcolumns     | tinyint(3) unsigned         | NO   |     | 0       |         |
| threadcaches     | tinyint(1)                  | NO   |     | 0       |         |
| alloweditpost    | tinyint(1) unsigned         | NO   |     | 1       |         |
| simple           | tinyint(1) unsigned         | NO   |     | NULL    |         |
| modworks         | tinyint(1) unsigned         | NO   |     | NULL    |         |
| allowtag         | tinyint(1)                  | NO   |     | 1       |         |
| allowglobalstick | tinyint(1)                  | NO   |     | 1       |         |
| allocate         | tinyint(1)                  | NO   |     | 0       |         |
+------------------+-----------------------------+------+-----+---------+----------------+mysql> DESCRIBE bbs_threadtags;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| tagname | char(20)         | NO   | MUL | NULL    |       |
| tid     | int(10) unsigned | NO   | MUL | NULL    |       |
+---------+------------------+------+-----+---------+-------+

Answer 10

以否再把三张表的 show index from bbs_threads; 分别贴出以供分析。

Answer 11

mysql> SHOW INDEX FROM bbs_threads;
+--------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name                  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |
+--------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| bbs_threads |          0 | PRIMARY                   |            1 | tid          | A         |     2382067 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | digest                    |            1 | digest       | A         |           1 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | sortid                    |            1 | sortid       | A         |           1 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | displayorder              |            1 | fid          | A         |         346 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | displayorder              |            2 | displayorder | A         |         346 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | displayorder              |            3 | lastpost     | A         |      297758 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | typeid                    |            1 | fid          | A         |         346 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | typeid                    |            2 | typeid       | A         |         346 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | typeid                    |            3 | displayorder | A         |         346 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | typeid                    |            4 | lastpost     | A         |      297758 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | recommends                |            1 | recommends   | A         |           1 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | heats                     |            1 | heats        | A         |           1 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | authorid                  |            1 | authorid     | A         |           1 |     NULL | NULL   |      | BTREE      |        |
| bbs_threads |          1 | bbs_threads_index1        |            1 | dateline     | A         |      183235 |     NULL | NULL   |      | BTREE      |        |
+--------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> SHOW INDEX FROM bbs_threadtags;
+-----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                 | Non_unique | Key_name    | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| bbs_threadtags        |          1 | tid_tagname |            1 | tid| A         |     2382066 |     NULL | NULL   |      | BTREE      |         |
| bbs_threadtags        |          1 | tid_tagname |            2 | tagname| A         |    11910330 |     NULL | NULL   |      | BTREE      |         |
| bbs_threadtags        |          1 | tid         |            1 | tid| A         |     2382066 |     NULL | NULL   |      | BTREE      |         |
| bbs_threadtags        |          1 | tagname     |            1 | tagname| A         |       12291 |     NULL | NULL   |      | BTREE      |         |
+-----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+mysql> SHOW INDEX FROM bbs_forums;
+-------------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| bbs_forums        |          0 | PRIMARY  |            1 | fid          | A      |         387 |     NULL | NULL   |      | BTREE      |         |
| bbs_forums        |          1 | forum    |            1 | status       | A      |           3 |     NULL | NULL   |      | BTREE      |         |
| bbs_forums        |          1 | forum    |            2 | type         | A      |           3 |     NULL | NULL   |      | BTREE      |         |
| bbs_forums        |          1 | forum    |            3 | displayorder | A      |          96 |     NULL | NULL   |      | BTREE      |         |
| bbs_forums        |          1 | fup      |            1 | fup          | A      |          38 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+三张表的结构以及它们的索引情况
查询的效率希望可以提高，请赐教！！

Answer 12

先去掉ORDER BY ，看看速度如何

Answer 13

快个1秒左右，我分别查询几条SQL，不带ORDER BY的时间在2-3秒之间，带了ORDER BY就是3-4秒之间
在命令行里面的时间，如果再考虑到网速的情况，那就比较慢了，所以我想在查询的时候最好能够在1秒之内查询完毕。

Answer 14

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 
速度如何

Answer 15

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='标签' 
速度如何

Answer 16

3秒左右+----+-------------+-------+--------+-----------------------------+---------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys               | key     | key_len | ref               | rows | Extra|+
----+-------------+-------+--------+-----------------------------+---------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | tt    | ref    | tid_tagname,tid,tagname     | tagname | 40      | const             | 1291 | Using where; Using temporary; Using filesort|
|  1 | SIMPLE      | t     | eq_ref | PRIMARY,displayorder,typeid | PRIMARY | 3      | bbs.tt.tid |    1 | Using where|
|  1 | SIMPLE      | f     | eq_ref | PRIMARY,forum               | PRIMARY | 2      | bbs.t.fid  |    1 | Using where|
+----+-------------+-------+--------+-----------------------------+---------+---------+-------------------+------+----------------------------------------------+
3 rows in set (0.05 sec)
主要是rows: 影响的行数，过千的情况，查询就需要3秒以上了。
PS:刚才也测试了那个bbs_forum：fid、status复合索引，时间还是在3秒左右，也是取决于那个tagname查询出来的行数，只有几十几百行的情况，基本上就OK了，1秒钟之内
怎么优化那个 tagname ？

Answer 17

bbs_threadtags：ti、tagname复合索引吗？
SELECT f.fid,t.tid,t.subject FROM bbs_threadtags tt INNER JOIN bbs_threads t ON t.tid=tt.tid
INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid    
where tt.tagname='标签'

Answer 18

恩，创建了复合索引mysql> SHOW INDEX FROM bbs_threadtags;
+-----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                 | Non_unique | Key_name    | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| bbs_threadtags        |          1 | tid_tagname |            1 | tid| A         |     2382066 |     NULL | NULL   |      | BTREE      |         |
| bbs_threadtags        |          1 | tid_tagname |            2 | tagname| A         |    11910330 |     NULL | NULL   |      | BTREE      |         |
| bbs_threadtags        |          1 | tid         |            1 | tid| A         |     2382066 |     NULL | NULL   |      | BTREE      |         |
| bbs_threadtags        |          1 | tagname     |            1 | tagname| A         |       12291 |     NULL | NULL   |      | BTREE      |         |
+-----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
就是让我感到有点郁闷的是，tagname做了索引以及tid_tagname复合索引，为什么在有tagname查询的情况，速度却不理想呢？

Answer 19

SELECT * FROM bbs_threadtags WHERE tagname='标签' LIMIT 15;当然单独查询的速度很快，在0.1秒之内。为什么联合查询的时候却慢了？当联合查询，我把tagname这个条件不要的话，也是非常快的。这是什么原因呢？

Answer 20

另外比较一下这个速度。select STRAIGHT_JOIN t.tid,t.subject 
form bbs_threads t,bbs_forums f
where f.fid=t.fid 
and f.status=1
and exists (select 1  from bbs_threadtags where tid=t.tid AND tagname='标签')
order by t.dateline DESC LIMIT 10 ;

Answer 21

bbs_threadtags表有几个索引，在tid、tagname上有单独的索引？

Answer 22

恩，测试你的，一直卡在那？退出再测试也是这样。其中form的小错误 from 呵呵恩，有单独索引，开始我想有了复合索引应该不需要单独索引，就删除了单独索引，这样一来的测试结果是10来秒了，太夸张了
所以主题标签表我是有tid tagname的单独索引以及tid_tagname的复合索引

Answer 23

去掉单独的索引，建立tagname、tid复合索引试试

Answer 24

索引上已经没有问题了，该有的索引都已经有了。测试一下 select STRAIGHT_JOIN t.tid,t.subject 
form bbs_threads t,bbs_forums f
where f.fid=t.fid 
and f.status=1
order by t.dateline DESC LIMIT 10 ;这个，另外看一下EXPLAIN select STRAIGHT_JOIN t.tid,t.subject 
form bbs_threads t,bbs_forums f
where f.fid=t.fid 
and f.status=1
order by t.dateline DESC LIMIT 10 ;

Answer 25

速度很快 0.2秒左右
+----+-------------+-------+--------+--------------------------+---------------------------+---------+------------------+---------+-------------+
| id | select_type | table | type   | possible_keys            | key           | key_len | ref              | rows    | Extra       |
+----+-------------+-------+--------+--------------------------+---------------------------+---------+------------------+---------+-------------+
|  1 | SIMPLE      | t     | index  | displayorder,typeid      | bbs_threads_index1 | 4       | NULL             | 2382067 |             |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY,forum,fid_status | PRIMARY           | 2       | tatazxcity.t.fid |       1 | Using where |
+----+-------------+-------+--------+--------------------------+---------------------------+---------+------------------+---------+-------------+

Answer 26

explain select STRAIGHT_JOIN t.tid,t.subject 
form bbs_threads t,bbs_forums f
where f.fid=t.fid 
and f.status=1
and exists (select 1  from bbs_threadtags where tid=t.tid AND tagname='标签')
order by t.dateline DESC LIMIT 10 ;的结果是什么？

Answer 27

或者再试一下这个的速度。select STRAIGHT_JOIN t.tid,t.subject 
form bbs_threads t,bbs_forums f,(select tid  from bbs_threadtags where  tagname='标签') tt
where f.fid=t.fid 
and f.status=1
and t.tid =tt.tid
order by t.dateline DESC LIMIT 10 ;

Answer 28

一直卡在那里，所以我就Ctrl+C给退出来了  再次测试又卡在那
时间更久了  20来秒

Answer 29

去掉单独的索引，建立tagname、tid复合索引
运行
SELECT * FROM bbs_threadtags WHERE tagname='标签' LIMIT 15;
速度如何

Answer 30

你好，删除单独索引之后，查询基本上要1分钟了#20 #23楼我觉得是主要问题，其他的条件加不加影响不会很大。ORDER BY的话就是1秒的优化。所以我想在加了ORDER BY的情况下，能达到1秒半查询OK也满意了

Answer 31

建立tagname、tid复合索引先用两个表测试：SELECT t.tid,t.subject FROM bbs_threadtags tt INNER JOIN bbs_threads t ON t.tid=tt.tid
where tt.tagname='标签'

Answer 32

SELECT t.tid,t.subject FROM bbs_threadtags tt INNER JOIN bbs_threads t ON t.tid=tt.tid
INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid
where tt.tagname='标签'
速度如何

Answer 33

SELECT f.fid,t.tid,t.subject FROM bbs_threadtags tt INNER JOIN bbs_threads t ON t.tid=tt.tid
INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid   
where tt.tagname='标签'呵呵，看来你没有仔细看看别人的回复哦，21楼就有了，大表放在第1个

Answer 34

呵呵，每个回复我都会去认真实践下
问题还是围绕在bbs_threadtags 表 tt.tagname='标签' 查询超过1千的数据，基本上时间就要2-3秒以上了

Answer 35

另外我把这个threadtags分成了2张表测试了下，速度差不多。

Answer 36

两个表的时候速度是1秒以内三个表的情况在tagname查询的主题数是1千以内的话，速度也控制在1秒以内大于1千的数据就需要2-3秒以上了