数据库检索优化

Question

我现在有个关于数据库优化的问题。
一个主数据表(几万行），以及和这个主数据表关联的一些Master表。
实现的要求很简单，就是在这个几万行的数据按照日期（已设定Index）排序，1页显示10条主数据和关联的Master值结果简单的数据检索竟然执行了20秒
调查了Explain Plan，越发糊涂了。
1）如果直接出Explain Plan
第一行，竟然不是我的主表的ALL的全表扫描，而是某一个Master表（1千行）的扫描，之后是主表的ref，之后是其他Master的eq_ref
2）设定FORCE INDEX
如果不join一个只有4行的Master表，确实按照我的想法，先做主表的Index扫描，其他的Master都用eq_ref的方式快速Join。
执行速度不到一秒。可是只要加上那个4行小表，这个小表总是用ALL(Extra:Using where; Using join buffer)的方式Fetch,同时我那个Force Index也失效了，变成了首先做主表的ALL扫描当然我也能理解通常情形下，用一个小表作为主关联的可行性，或者一个极小表的buffer全检索的方便。但是在我这个例子里面，我的主表其实是limit过的，配合Index即使主表全检索，也是非常快的。不知道如何设定Mysql的优化器。另外，这个SQL文如果去掉Oder by，即使如上所说的那种Plan,依然是很快的。就更加感觉是好多偶然交织的感觉。

Answer 1

贴表结构、索引、主键情况、SQL语句

Answer 2

对于去掉Oder By，速度很快的原因。看来是因为，即使ALL扫描，但是没有（Extra:Using temporary; Using filesort）,而是
（Extra:Using where）。而前者正是速度慢的直接原因。还请高手指点，如何优化

Answer 3

关于索引的优化，建议你可以参考一下这几个系列的贴子http://topic.csdn.net/u/20090520/16/a96a2e90-a935-4460-837e-e52b4557c519.html?35200
http://topic.csdn.net/u/20090526/17/639d78ec-e299-40d0-9c8e-8d5b21229405.html?8356
http://topic.csdn.net/u/20090604/15/b4120d6f-fd95-4a60-be4b-fbd5accbea73.html?40433


Answer 4

优化需要比较多的信息建议你读完上述贴子后，可以贴出如下信息。1. show table status ;
2. show index from yourTable;
3. explain select .....

Answer 5

select *
    from
        ExchangeData exchangeda0_ force index(dataindex),
        PersonalCustomerInfo personalcu1_ ,
        ShopInfo shopinfo2_, 
        PointBaseInfo pointbasei3_,
        CardTypeInfo cardtypein4_ 
   where
exchangeda0_.customer_manageID=personalcu1_.customer_manageID 
          and exchangeda0_.use_shopID=shopinfo2_.shopID 
          and exchangeda0_.pointID=pointbasei3_.pointID
          and exchangeda0_.card_typeID=cardtypein4_.card_typeID
    order by
        exchangeda0_.exchange_time asc 
limit 2ExchangeData就是主要表，CardTypeInfo就是4条数据超小表[Index的设定]：
Table * Non_unique * Key_name * Seq_in_index * Column_name *
ExchangeData 0 PRIMARY 1 exchangeID
ExchangeData 1 R_48 1 customer_manageID
ExchangeData 1 R_49 1 use_shopID
ExchangeData 1 R_50 1 pointID
ExchangeData 1 R_X1 1 issue_shopID
ExchangeData 1 dataIndex 1 exchange_time
ExchangeData 1 R_X2 1 card_typeID

Answer 6

贴EXPLAIN　你的SQL语句结果
优化问题需要耐心，反复测试。

Answer 7

1 SIMPLE exchangeda0_ ALL 144198 Using temporary; Using filesort
1 SIMPLE cardtypein4_ ALL PRIMARY 5 Using where; Using join buffer
1 SIMPLE personalcu1_ eq_ref PRIMARY PRIMARY 54 cam.exchangeda0_.customer_manageID 1
1 SIMPLE pointbasei3_ eq_ref PRIMARY PRIMARY 54 cam.exchangeda0_.pointID 1
1 SIMPLE shopinfo2_ eq_ref PRIMARY PRIMARY 9 cam.exchangeda0_.use_shopID 1 这个是用了Foce Index但是实效的Plan,就是那个 cardtypein4作怪1 SIMPLE exchangeda0_ index dataIndex 8 2
1 SIMPLE personalcu1_ eq_ref PRIMARY PRIMARY 54 cam.exchangeda0_.customer_manageID 1
1 SIMPLE pointbasei3_ eq_ref PRIMARY PRIMARY 54 cam.exchangeda0_.pointID 1
1 SIMPLE shopinfo2_ eq_ref PRIMARY PRIMARY 9 cam.exchangeda0_.use_shopID 1
这个就是非常快的那种，唯一不同就是去掉了Join cardtypein4我用的是最新的Mysql，没有做任何优化器的自定义

Answer 8

1 SIMPLE shopinfo2_ ALL PRIMARY 25 Using temporary; Using filesort
1 SIMPLE exchangeda0_ ref R_48,R_49,R_50,R_X2 R_49 10 cam.shopinfo2_.shopID 2403 Using where
1 SIMPLE cardtypein4_ ALL PRIMARY 5 Using where; Using join buffer
1 SIMPLE pointbasei3_ eq_ref PRIMARY PRIMARY 54 cam.exchangeda0_.pointID 1
1 SIMPLE personalcu1_ eq_ref PRIMARY PRIMARY 54 cam.exchangeda0_.customer_manageID 1
补充：如果FoceIndex不用，就是最初的那种。
可以看到，exchangeda0跑到第二行了，而shopinfo2在第一行。
我还测试过，如果把exchangeda0的数据删到各位数，即使不用FoceIndex，也会第一步去扫描exchangeda
所以让我确定这个优化器，有优化规则是根据各表的数据量的

Answer 9

楼主能不能按下面格式贴你的结果啊。需要以下信息
show index from ExchangeData;
show index from PersonalCustomerInfo;
show index from ShopInfo;
show index from PointBaseInfo;
show index from CardTypeInfo;explain select *
  from
    ExchangeData exchangeda0_ force index(dataindex),
    PersonalCustomerInfo personalcu1_ ,
    ShopInfo shopinfo2_, 
    PointBaseInfo pointbasei3_,
    CardTypeInfo cardtypein4_ 
 where
exchangeda0_.customer_manageID=personalcu1_.customer_manageID 
    and exchangeda0_.use_shopID=shopinfo2_.shopID 
    and exchangeda0_.pointID=pointbasei3_.pointID
    and exchangeda0_.card_typeID=cardtypein4_.card_typeID
  order by
    exchangeda0_.exchange_time asc 
limit 2;
[code=BatchFile]mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |      100000 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)mysql> explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 100000 |       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)mysql>[/code]

Answer 10

虽然DB搞了几年了，Mysql是第一次。所以现在用的是Toad for Mysql，索性截图咯

Answer 11

这个图可太费眼神来分析了。你能否直接用mysql工具连上去，就象sqlplus 一样，然后贴文本出来。

Answer 12

换个大点的。看看是不是清楚点。各个表的Index

Answer 13

不是大小，一般我会这些信息贴入到EXCEL，然后对各字段进行分析查找，并做上标记。当然这样用图可以，不过就是靠眼睛来找罢了，然后手工在纸上做标记，不过显然效率不高。