php - 使用cakephp从mysql数据库获取大量数据

Question

我有 7 个类似的表，分别名为“device_1_updates”、“device_2_updates”、“device_3_updates”...“device_7_updates”。

我正在使用UNION来面对这7个表中的数据，但是它花费了太多时间..因为这7个表中的每一个数据都太重了。

请建议一些加快速度的方法

我的sql查询是:

SELECT 
  * 
FROM
  (SELECT 
    * 
  FROM
    device_1_updates 
  UNION
  SELECT 
    * 
  FROM
    device_1_updates 
  UNION
  SELECT 
    * 
  FROM
    device_2_updates 
  UNION
  SELECT 
    * 
  FROM
    device_3_updates 
  UNION
  SELECT 
    * 
  FROM
    device_4_updates 
  UNION
  SELECT 
    * 
  FROM
    device_5_updates 
  UNION
  SELECT 
    * 
  FROM
    device_6_updates 
  UNION
  SELECT 
    * 
  FROM
    device_7_updates) AS device_data 
WHERE device_time > '2014-04-05 01:55:08' 
  AND device_time < '2014-04-11 11:55:08' 
  AND imei = 357804045965906 
ORDER BY device_time ASC 
LIMIT 0, 100

对应的响应是:

"response": [{
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:57:52",
            "lat": "",
            "lang": "",
            "event": "^6"
        }
    }, {
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:58:02",
            "lat": "",
            "lang": "",
            "event": "^4"
        }
    }, {
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:58:14",
            "lat": "",
            "lang": "",
            "event": "^8"
        }
    }, {
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:58:19",
            "lat": "",
            "lang": "",
            "event": "^1"
        }
    }
]

Answer 1

我建议你要一张 table device_updates使用附加列作为类型​​，其中您可以有 1-7 个数字，但现在您需要为 device_time ,imei 上的所有 7 个表添加索引列然后使用 UNION ALL所以它不会删除重复项，使用 union MySQL 会将合并的日期集排序到临时表中，一旦数据集排序，它将删除重复项，以优化它推送 ORDER BY , LIMIT和WHERE每个子查询内的条件

(SELECT * FROM  device_1_updates WHERE device_time > '2014-04-05 01:55:08' 
  AND device_time < '2014-04-11 11:55:08' 
  AND imei = 357804045965906 
ORDER BY device_time ASC 
LIMIT 0, 100 )
UNION ALL
(query 2 with where limit and order by)
UNION ALL
(query 3 with where limit and order by)
.
.
.
.
ORDER BY device_time ASC LIMIT 0, 100 /*at the end of query add order by and limit*/

确保为 where 子句中的列添加索引，我想说的是，如果列太多，只选择需要的列，使用 select * 也会降低速度

For reference