【优化】MySQL实在是太慢了，以下查询语句优化的地方在哪里？

[用户昵称不能为空]

我有一个1亿数据的表ratedat，存储用户交易记录，大小30GB。对于这个表我根本就无法直接查询。后多次测试发现限定WHERE id BETWEEN IN 查询不会死机。所以就想到了分区，1万记录一分区。之前在网上看到说根据id来分表，某一区间id一个表，我没有分表，如果真的分表那得分多少表啊、况且ratedat表的id跨越13亿，最小值几万最大值十几亿，所以分表根本就没有意义不可行。但由于表ratedat中的id是不连续的，所以另外用了一张表ratedat_page来记录分区。1万一个区间。然后我自己用PHP制作了一个自动搜寻分区然后保存到表ratedat_page的程序，可发现实在是太慢了。一两秒才能够完成一个分区。

表ratedat（存交易记录的表）

mysql> DESC ratedat;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| trade_id    | bigint(20)    | NO   | PRI | NULL    |       |
| trade_time  | int(10)       | NO   |     | NULL    |       |
| uid_buy     | bigint(20)    | NO   | MUL | NULL    |       |
| uid_sell    | bigint(20)    | NO   | MUL | NULL    |       |
| goods_title | varchar(120)  | NO   | MUL | NULL    |       |
| goods_price | decimal(10,2) | NO   |     | NULL    |       |
| rate_txt    | text          | NO   | MUL | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

表ratedat_page(存储分区的表)

mysql> DESC ratedat_page;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| page  | int(10)    | NO   | PRI | NULL    | auto_increment |
| id1   | bigint(20) | NO   |     | NULL    |                |
| id2   | bigint(20) | NO   |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

PHP程序执行的所有语句（测试每次执行平均花费至少1-2秒）：

程序的运行原理很简单，先找到这一分区开始id1,再找结束的id2。都找到后存储到表ratedat_page中。

以下是从第ratedat.trade_id>=45270589182168记录开始，对于id2，因为不连续，所以每次往下(大)扫描900000010000行，直到找到“第1万”记录为止。
找到后存入到ratedat_page表中。

 1.218750 s,q:9
Array
(
    [0] => SELECT MAX(trade_id) AS `idMax`,MIN(`trade_id`) AS `idMin` FROM `ratedat` LIMIT 0,1; 
    #为了统计ratedat中最大最小来鉴定是否已经执行完毕
    
    [1] => EXPLAIN SELECT 1 FROM `ratedat` LIMIT 0,1; 
    #其实原先是在[0]语句中的SELECT COUNT()结果发现总是死机，后来才用EXPLAIN 的rows

    [2] => SELECT MIN(trade_id) AS id1 FROM ratedat WHERE trade_id>=45270589172168 LIMIT 0,1;
    #选出分区的开始id,ratedat_page.id1

    [3] => SELECT trade_id AS id2 FROM ratedat WHERE trade_id>=45270589172168 AND trade_id<(45270589182168+90000001000) ORDER BY trade_id ASC LIMIT 9999, 1 ;
    #选出分区的结束，因为id是不连续，所以就会有多次搜寻。找的是第1000个，所以用的是LIMIT 9999,1

    [4] => SELECT trade_id AS id2 FROM ratedat WHERE trade_id>=45270589172168 AND trade_id<(45270589182168+900000010000) ORDER BY trade_id ASC LIMIT 9999, 1 
    #第[3]中没有搜索到最大的，因为他里面不够一个分区(1w记录)，所以就继续加大搜索，但是表ratedat的id是不连续的，又为了防止扫描过多死机，所以每次都是扫描增加 90000001000 背后多加一个0

    [5] => SELECT trade_id AS id2 FROM ratedat WHERE trade_id>=45270589172168 AND trade_id<(45270589182168+9000000100000) ORDER BY trade_id ASC LIMIT 9999, 1 ;
    #第[5]次终于搜索到了最大的
    
    [6] => INSERT INTO ratedat_page SET id1=45270672789725,id2=46703483905514 ;
    #第[6]则写入到ratedat_page表

    [7] => SELECT 46703483905514+1 AS idStartNext,46703483905514+10000+1 AS idEndNext LIMIT 0,1;
    #这一段纯粹是为了加减运算，因为PHP无法执行这么大的运算（有一个gmp扩展但是没有用）

    [8] => SELECT CEIL((46703483905515 - 48353669)*100/299922201925902) AS pctAll,
    CEIL(currpage/totalpage*100) AS pctCount,currpage,totalpage
    FROM (
        SELECT MAX(page) AS currpage,
        CEIL(10554777/10000) AS totalpage
        FROM ratedat_page
    ) s LIMIT 0,1; 
    #第[8]语句则也是为了执行加减运算，计算完成比例，剩余比例
    
)

[用户昵称不能为空]

引用 1 楼 ACMAIN_CHM 的回复:

是要遍历所有记录，还是查询某条记录？

其实我是为了执行这一段SQL（涉及另外一个表 rate_buyer）：

    INSERT INTO rate_buyer(uid,costcount,costsum,costavg,costmax,costmin)
    SELECT uid_buy AS uid,
        COUNT(trade_id) AS costcount,
        SUM(goods_price) AS costsum,
        ROUND(AVG(goods_price),2) AS costavg,
        MAX(goods_price) AS costmax,
        MIN(goods_price) AS costmin
    FROM ratedat  
    GROUP BY uid_buy

但是由于ratedat表有1亿多数据，太多了。所以才会用WHERE来限制每次存取的条数。 就变成了如下：

    SELECT uid_buy AS uid,
        COUNT(trade_id) AS costcount,
        SUM(goods_price) AS costsum,
        ROUND(AVG(goods_price),2) AS costavg,
        MAX(goods_price) AS costmax,
        MIN(goods_price) AS costmin
    FROM ratedat  WHERE trade_id BETWEEN $id1 AND $id2
    GROUP BY uid_buy

$id1 和 $id2 就来自于ratedat_page 中的记录。现在发现生成ratedat_page真的太慢了。 数据一多，以前经常用的老方法都失灵了一样。

[Rgsser]

分表吧， 这么大的表，你还要遍历全表会搞死人的（搞死服务器）。具体不知道你怎么用， 如果是查出来的数据要在前台直接展现的话，本身设计就不合理了。

[ACMAIN_CHM]

是要遍历所有记录，还是查询某条记录？

[用户昵称不能为空]

引用 1 楼 ACMAIN_CHM 的回复:

是要遍历所有记录，还是查询某条记录？

要遍历 表ratedat中的所有记录。但为了防止死机，对每次遍历数量我加了限制。

[用户昵称不能为空]

引用 1 楼 ACMAIN_CHM 的回复:

是要遍历所有记录，还是查询某条记录？

我对遍历开头和结尾作了限制。 比如第10个分区，是从$idStart开始扫描。

-- 得到这一区间的开始：id1
SELECT MIN(trade_id) AS id1 FROM tb WHERE trade_id>=$idStart 


-- 得到这一区间的结束：id2
SELECT trade_id AS id2 FROM tb WHERE trade_id>=$idStart AND trade_id<$idStart+$ScanRange LIMIT $PerPartition-1,1

[用户昵称不能为空]

引用 5 楼 u010416615 的回复:

分表吧， 这么大的表，你还要遍历全表会搞死人的（搞死服务器）。具体不知道你怎么用， 如果是查出来的数据要在前台直接展现的话，本身设计就不合理了。

怎么分法

[用户昵称不能为空]

引用 8 楼 lhs295988029 的回复:

我有3个建议可以参考: 1.分区。按照trade_time分区，每年（或者每月，主要看你业务是年还是月）的数据是一个分区，按照id有点太多了些。 2.把历史数据放到归档库中，估计热点数据也不会有很多，这样查询就会快很多！ 3.如果还是不行，新增汇总表之类的，把要查询的数据提前准备好，然后只对这个表查询，是不是好很多！

但查询不是按照历史来查询的，是查询所有记录。

[栗华山]

我有3个建议可以参考: 1.分区。按照trade_time分区，每年（或者每月，主要看你业务是年还是月）的数据是一个分区，按照id有点太多了些。 2.把历史数据放到归档库中，估计热点数据也不会有很多，这样查询就会快很多！ 3.如果还是不行，新增汇总表之类的，把要查询的数据提前准备好，然后只对这个表查询，是不是好很多！

[栗华山]

我想：uid_buy是有索引的，对吧？

[栗华山]

更正：按照trade_time是不行的，因为你的查询中根本就没有用到trade_time条件！！！

[栗华山]

看来必须要分区了！！！因为数据量太多，直接查，使用索引查都要扫描很多很多数据，从而产生很多随机IO。 innodb一个表最多1024个分区，但一般不要大于100个分区，分区太多也会影响效率的。 注意： 1.分区后，where条件中一定要有分区列过滤，尽量筛选分区。 2.各个分区可以放到不同的硬盘上，可以提高io效率。 按照id分区：HASH(ID DIV 1000000)，这将为100万数据建立一个分区。

[attilax]

应该分表..一个表... 1KW记录比较好...

[世宝宝]

你这不是正常操作，备份后在另一台机器跑吧。NoSQL对于这种大数据的操作可能性能会好些。