Php，Mysql - 在我的案例中如何处理大数据

I have two table with big data, Example in http://www.sqlfiddle.com/#!2/ee61b/3

我有两个大数据表,例如http://www.sqlfiddle.com/#!2/ee61b/3

Table 1 like

表1喜欢

CREATE TABLE `t1`(
  `id` int(4) ,
  `name` varchar(40),
  `signal` int(4)
)
;

INSERT INTO `t1`
(`id`, `name`, `signal`)
VALUES
    (1, 'a', 1),
    (2, 'b', 1),
    (3, 'c', 0),
    (4, 'd', 0)
;

And table 2 like

和表2一样

CREATE TABLE `t2`(
  `uid` int(4),
  `type` int(4),
  `usignal` int(4)
)
;

INSERT INTO `t2`
(`uid`, `type`, `usignal`)
VALUES
    (3, 1, 1),
    (1, 2, 1),
    (4, 1, 0),
    (2, 2, 0)
;

Now i try to get all record have conditional like
1. All record in table 1 has signal = 1
2. OR(+) all record in table 1 has id in (SELECT uid FROM t2 WHERE t2.type = 1 AND t2.usignal = 1)

现在我尝试让所有记录都有条件像1.表1中的所有记录都有signal = 1 2. OR(+)表1中的所有记录都有id(SELECT uid FROM t2 WHERE t2.type = 1 AND t2.usignal = 1)

I do that like

我这样做

SELECT * FROM `t1` AS g1
WHERE g1.signal = 1
UNION 
SELECT * FROM  `t1` AS g1
WHERE g1.id
IN (
    SELECT uid
    FROM  `t2` AS g2
    WHERE g2.usignal = 1 AND g2.type = 1
)

But I must do some conditional on that (mybe join,...) and i do that like

但我必须做一些条件(mybe join,...),我就是这样做的

SELECT * FROM `t1` AS g1
WHERE g1.signal = 1
UNION 
SELECT * FROM  `t1` AS g1
WHERE g1.id
IN (
    SELECT uid
    FROM  `t2` AS g2
    WHERE g2.usignal = 1 AND g2.type = 1
)

/*dynamic other join query here */
and id>=1 /*dynamic conditional query here*/
order by id desc limit 0,20

That working well if data is small, But my data is bigger and that fail while execute

如果数据很小,那么效果很好,但是我的数据更大,而且在执行时失败

How can i do that thanks

我该怎么做呢谢谢

Edit:
What about my sql? Is that make execute slowly and make it timeout?

编辑:我的sql怎么样?这会使缓慢执行并使其超时吗?

Edit02:
I using phpmyadmin and click index to t1(id, signal) and t2(type, unsignal)
And i add id primary key fo both table example in http://www.sqlfiddle.com/#!2/d88a9/1
But in real my tables have about 10 column. And i using left join but that's still timeout :(

Edit02:我使用phpmyadmin并点击索引到t1(id,signal)和t2(类型,无信号)我在http://www.sqlfiddle.com/#!2/d88a9/1中添加了两个表示例的id主键但实际上我的表格大约有10列。我使用左连接,但仍然超时:(

6 个解决方案

#1

Try to get rid of your sub query and use join , i have also removed the union from the query and for this i have enclosed the conditions in braces to meet your union needs

尝试摆脱你的子查询并使用join,我也从查询中删除了联合,为此我已将条件括在括号中以满足您的联合需求

SELECT g1.* FROM `t1` AS g1
left join `t2` AS g2 on (g1.id=g2.uid)
WHERE (
(g2.usignal = 1 AND g2.type = 1 )
 OR g1.signal = 1      
  )
order by id desc limit 0,20

Also make sure you have proper index on your columns

还要确保列上有适当的索引

See this fiddle

看到这个小提琴

#2

What error do you get so that we can answer your question .. you can set the maximum execution time from php ini like this :ini_set('max_execution_time', 300); And your task will work for 300 seconds instead of 30 .

您得到了什么错误,以便我们可以回答您的问题..您可以设置php ini的最大执行时间,如下所示:ini_set('max_execution_time',300);而你的任务将工作300秒而不是30秒。

#3

Have you tried joining instead of that nested select (which won't be fast)?

您是否尝试过加入而不是嵌套选择(这不会很快)?

SELECT g1.* FROM `t1` AS g1
LEFT JOIN `t2` AS g2 ON g2.uid = g1.id 
WHERE g1.signal = 1
 OR (g2.usignal = 1 AND g2.type = 1)

/*dynamic other join query here */

and id>=1 /*dynamic conditional query here*/
order by id desc limit 0,20

#4

In your SQL-Fiddle http://www.sqlfiddle.com/#!2/ee61b/3 there is an option down below in the green navigation bar: "+ view execution plan". You can also get to this information by adding "EXPLAIN" to the front of your query.

在您的SQL-Fiddle http://www.sqlfiddle.com/#!2/ee61b/3中,绿色导航栏中有一个选项:“+ view execution plan”。您还可以通过在查询前添加“EXPLAIN”来获取此信息。

In you case the result looks something like this:

在你的情况下,结果看起来像这样:

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra       |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
|    1 | PRIMARY      | g1          | ALL    | NULL          | NULL         | NULL    | NULL |    4 | Using where |
|    2 | UNION        | g1          | ALL    | NULL          | NULL         | NULL    | NULL |    4 |             |
|    2 | UNION        | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |             |
|    3 | MATERIALIZED | g2          | ALL    | NULL          | NULL         | NULL    | NULL |    4 | Using where |
| NULL | UNION RESULT | <union1,2>  | ALL    | NULL          | NULL         | NULL    | NULL | NULL |             |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+

Look at the column "possible_keys": you have NULL everywhere. That is bad, it means that the database has to search sequentially through your data to find something.

查看“possible_keys”列:到处都是NULL。这很糟糕,这意味着数据库必须按顺序搜索您的数据才能找到一些东西。

If you add the right primary keys and indexes to your database, then every search will be done using a clever data structure like a tree, and will be much faster, especially for large date:

如果将正确的主键和索引添加到数据库中,那么每次搜索都将使用像树这样的聪明数据结构来完成,并且会更快,特别是对于大日期:

+------+--------------+------------+--------+-----------------+---------+---------+-------------+------+-------------+
| id   | select_type  | table      | type   | possible_keys   | key     | key_len | ref         | rows | Extra       |
+------+--------------+------------+--------+-----------------+---------+---------+-------------+------+-------------+
|    1 | PRIMARY      | g1         | ref    | signal          | signal  | 5       | const       |    2 |             |
|    2 | UNION        | g2         | ref    | PRIMARY,usignal | usignal | 5       | const       |    2 | Using where |
|    2 | UNION        | g1         | eq_ref | PRIMARY         | PRIMARY | 4       | test.g2.uid |    1 |             |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL            | NULL    | NULL    | NULL        | NULL |             |
+------+--------------+------------+--------+-----------------+---------+---------+-------------+------+-------------+

So: read up on Primary Keys and Indexes and apply this knowledge to your example.

所以:阅读主键和索引并将这些知识应用到您的示例中。

#5

Do you have any indices? Make sure that you created the proper ones.

你有指数吗?确保您创建了正确的。

For t1 it seems that you need an index on (id, signal) tuple. For t2 you need an index on (usignal, type) tuple.

对于t1,似乎需要一个索引(id,signal)元组。对于t2,您需要(usignal,type)元组的索引。

This is how you can add them if you already don't have (according to the create tables you don't):

这是你可以添加它们的方法(如果你没有创建表):

create index 'id_signal_index' on t1 (`id`, `signal`);
create index 'id_usignal_index' on t2 (`usignal`, `type`);

It is usually a good practice to have primary key for id with auto increment in a table like t1.

在t1这样的表中使用id的主键和自动增量通常是一个好习惯。

Also convert the inner query into left join query, which is explained in other answers.

还将内部查询转换为左连接查询,这在其他答案中进行了解释。

#6

Index t1 on signal, and t2 on (usignal,utype).

信号上的索引t1和(usignal,utype)上的t2。

Define your primary keys as well.

同时定义主键。