【讨论中】求高手诊断：70万条记录，select 要一个多小时，哪里除了问题

zhn158

tblcell 1万多个记录， tbl110共有700000条记录，使用select 计算各种指标，比如：

1. select start_date, lac_ci, 公式1, 公式2, 公式3, ......, 公式60 from tbl110  join tblcell using (LAC_CI) where region1='TD';

复制代码

其中tblcell中符合 region='TD'，约有5千个记录，这条语句运行下来要1个多小时，无法忍受

求高手诊断


以下两个表格格式：

tblcell

1. +-----------+----------------------+------+-----+---------+-------+
   
2. | Field     | Type                 | Null | Key | Default | Extra |
   
3. +-----------+----------------------+------+-----+---------+-------+
   
4. | LAC_CI    | varchar(11)          | NO   | PRI |         |       |
   
5. | CellName  | varchar(50)          | YES  |     | NULL    |       |
   
6. | LAC       | smallint(5) unsigned | YES  |     | NULL    |       |
   
7. | CI        | smallint(5) unsigned | YES  |     | NULL    |       |
   
8. | Longitude | float                | YES  |     | NULL    |       |
   
9. | Latitude  | float                | YES  |     | NULL    |       |
   
10. | Dir       | smallint(6)          | YES  |     | NULL    |       |
    
11. | Region    | varchar(50)          | YES  | MUL | NULL    |       |
    
12. | Region1   | varchar(50)          | YES  | MUL | NULL    |       |
    
13. | Region2   | varchar(50)          | YES  | MUL | NULL    |       |
    
14. | CellType  | varchar(10)          | YES  |     | NULL    |       |
    
15. | nbrTRX    | tinyint(3) unsigned  | YES  |     | NULL    |       |
    
16. | Band      | varchar(5)           | YES  |     | NULL    |       |
    
17. | BSC       | varchar(15)          | YES  |     | NULL    |       |
    
18. | OMC       | varchar(10)          | YES  |     | NULL    |       |
    
19. | BCCH      | smallint(6)          | YES  |     | NULL    |       |
    
20. +-----------+----------------------+------+-----+---------+-------+

复制代码

tbl110

[/code]tbl110

1. +------------+----------------------+------+-----+---------------------+-------+
   
2. | Field      | Type                 | Null | Key | Default             | Extra |
   
3. +------------+----------------------+------+-----+---------------------+-------+
   
4. | Start_Date | datetime             | NO   | PRI | 0000-00-00 00:00:00 |       |
   
5. | LAC_CI     | varchar(11)          | NO   | PRI |                     |       |
   
6. | CELL_CI    | smallint(5) unsigned | YES  | MUL | NULL                |       |
   
7. | CELL_LAC   | smallint(5) unsigned | YES  | MUL | NULL                |       |
   
8. | MC01       | int(11)              | YES  |     | NULL                |       |
   
9. | MC02       | int(11)              | YES  |     | NULL                |       |
   
10. | MC02A      | int(11)              | YES  |     | NULL                |       |
    
11. ....................................................................(省略了200多个字段)............................................................
    
12. 
    
13. +------------+----------------------+------+-----+---------------------+-------+

复制代码

cenalulu

最好是能够贴下语句的explain信息。
还有show indexes from tbl110

action08

show indexes学习一下

zhn158

回复 2# cenalulu

1. mysql> explain select MC01 from tbl110 join tblcell using(lac_ci) where region1='TD';
   
2. +----+-------------+---------+------+-----------------+--------+---------+----------------------------+------+-------------+
   
3. | id | select_type | table   | type | possible_keys   | key    | key_len | ref                        | rows | Extra       |
   
4. +----+-------------+---------+------+-----------------+--------+---------+----------------------------+------+-------------+
   
5. |  1 | SIMPLE      | tblcell | ALL  | PRIMARY,region1 | NULL   | NULL    | NULL                       | 5169 | Using where |
   
6. |  1 | SIMPLE      | tbl110  | ref  | LAC_CI          | LAC_CI | 24      | lyg_alc_gsm.tblcell.LAC_CI |   24 |             |
   
7. +----+-------------+---------+------+-----------------+--------+---------+----------------------------+------+-------------+
   

复制代码

1. mysql> explain select start_date,lac_ci,MC01 from tbl110 join tblcell using(lac_ci) where region1='TD';
   
2. +----+-------------+---------+------+-----------------+--------+---------+----------------------------+------+-------------+
   
3. | id | select_type | table   | type | possible_keys   | key    | key_len | ref                        | rows | Extra       |
   
4. +----+-------------+---------+------+-----------------+--------+---------+----------------------------+------+-------------+
   
5. |  1 | SIMPLE      | tblcell | ALL  | PRIMARY,region1 | NULL   | NULL    | NULL                       | 5169 | Using where |
   
6. |  1 | SIMPLE      | tbl110  | ref  | LAC_CI          | LAC_CI | 24      | lyg_alc_gsm.tblcell.LAC_CI |   24 |             |
   
7. +----+-------------+---------+------+-----------------+--------+---------+----------------------------+------+-------------+
   

复制代码

谢谢

horizonhyg

1. 初步诊断：
   
2. 1、你的Region1是不走索引的，看着像是联合索引，但是顺序错误，不走索引。
   
3. 2、tbl110的LAC_CI也没有索引
   
4. 3、tbl110包含太多 region1='TD'的LAC_CI值。

复制代码

以上延迟，只是猜测

龙雪刚

有点迷糊。

zhn158

看晕了，谢谢各位，我先慢慢消化

zhn158

horizonhyg 发表于 2012-08-17 14:04
初步诊断：
1、你的Region1是不走索引的，看着像是联合索引，但是顺序错误，不走索引。
2、tbl110的LAC_C ...

没有一个看明白的，还请多多指教：

1、顺序错是啥意思，我是用 create index regoin1 on tblcell (region1) 创建的索引
2、我在创建tbl110的时候，用了 Primary key （start_date,lac_ci), 这个不会创建LAC_CI的索引吗？是不是还要另外加一个 key （lac_ci),
3、在tbl110里面，每行数据是代表每个LAC_CI一个小时的原始数据，共三天，所以每个LAC_CI对应72个记录

cenalulu

回复 8# zhn158


    因为他的三句结论都是错的。
1. Cell表上，是否走region的索引对效率影响并不大，因为cell一共就10000行，'TD'值占到5000行，全表扫描和索引扫描的代价相似，不是问题的关键。
2. 从执行计划来看，join时 tbl110 也用到了索引（索引名字叫LAC_CI具体信息楼主需要show index看下）。
3. 关键的原因是楼主的主键顺序是先start_date在前，LAC_CI在后。也就意味着物理文件的分布是 按照时间排序，然后按照LAC_CI 排序。
然而在join时，确实通过LAC_CI的顺序去获取的。所以导致整个join产生了大量的随机IO造成效率低下。

个人建议，最好的办法是改变tbl110上的主键顺序 把primary key 改为 (LAC_CI, start_date) 。

zhn158

把2个table 的show index贴一下

1. mysql> show indexes from tbl110;
   
2. +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   
3. | Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_Comment |
   
4. +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   
5. | tbl110 |          0 | PRIMARY    |            1 | Start_Date  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
   
6. | tbl110 |          0 | PRIMARY    |            2 | LAC_CI      | A         |      700596 |     NULL | NULL   |      | BTREE      |         |               |
   
7. | tbl110 |          1 | Start_Date |            1 | Start_Date  | A         |         151 |     NULL | NULL   |      | BTREE      |         |               |
   
8. | tbl110 |          1 | LAC_CI     |            1 | LAC_CI      | A         |       29191 |     NULL | NULL   |      | BTREE      |         |               |
   
9. | tbl110 |          1 | CELL_CI    |            1 | CELL_CI     | A         |       29191 |     NULL | NULL   | YES  | BTREE      |         |               |
   
10. | tbl110 |          1 | CELL_LAC   |            1 | CELL_LAC    | A         |         113 |     NULL | NULL   | YES  | BTREE      |         |               |
    
11. +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    
12. 6 rows in set (0.17 sec)

复制代码

1. mysql> show indexes from tblcell;
   
2. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   
3. | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_Comment |
   
4. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   
5. | tblcell |          0 | PRIMARY  |            1 | LAC_CI      | A         |        5169 |     NULL | NULL   |      | BTREE      |         |               |
   
6. | tblcell |          1 | region   |            1 | Region      | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
   
7. | tblcell |          1 | region1  |            1 | Region1     | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
   
8. | tblcell |          1 | region2  |            1 | Region2     | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
   
9. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   
10. 4 rows in set (2.80 sec)

复制代码