喜悦国际村 - PHP高级编程 - 如何运用数据结构知识到PHP中？(页 1)

喜悦国际村 » PHP高级编程 » 如何运用数据结构知识到PHP中？

页: [1]

xieaotian

2007-3-9 05:54 AM

如何运用数据结构知识到PHP中？

如题所示：
如果使用数据结构知识到PHP，无疑是PHP程序速度效率的一大提高点。
但是需要解决两个问题：
1：如何将庞大的数据量从数据库中取出（这里只的是数据量太大了，如果取出会不会浪费时间和效率）
2：利用取出的结果，然后使用数据结构中的知识去编写算法以及编码。
第一个步骤，请问如何实现？会不会有问题。
注意：上题，如果没学过数据结构或PHP初学者的同志，请勿乱发言。

xhhy	2007-3-9 06:19 AM

第一个问题，数据库技巧是真理。

最近接手一个工作，在不修改数据库结构和输出接口的情况下，优化一个报表，数据库中目前有40W+的数据，预计一年内将达到200W以上。
经过一个星期努力，我成功把报表的计算时间从54秒降低到5秒。

这是最关键的SQL
[code]
SELECT
case_cause.id,
case_cause_btype.iname AS bTypeName,
case_cause_type.iname AS typeName,
case_cause.iname AS causeName,
(IFNULL(oldRcvCount, 0) + IFNULL(newRcvCount, 0)) AS allRcvCount,
(IFNULL(oldRcvMoney, 0) + IFNULL(newRcvMoney, 0)) AS allRcvMoney,
IFNULL(t2.oldRcvCount, 0) AS oldRcvCount,
IFNULL(t2.oldRcvMoney, 0) AS oldRcvMoney,
IFNULL(t1.newRcvCount, 0) AS newRcvCount,
IFNULL(t1.newRcvMoney, 0) AS newRcvMoney,
t3.forArrays,
IFNULL(t3.forCount, 0) AS forCount
FROM case_cause
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.cause_id) AS newRcvCount,
SUM(case_info.apply_money) AS newRcvMoney
FROM case_info
WHERE (
case_info.reg_date > '2007-02-09'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '2007-02-09' AND '2007-03-09'
)
)
GROUP BY case_info.cause_id
) AS t1 on (case_cause.id = t1.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.cause_id) AS oldRcvCount,
SUM(case_info.apply_money) AS oldRcvMoney
FROM case_info
WHERE (
case_info.reg_date < '2007-02-09'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '2007-02-09' AND '2007-03-09'
)
)
GROUP BY case_info.cause_id
) AS t2 on (case_cause.id = t2.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
GROUP_CONCAT( case_info.foreign_factor_id ORDER BY case_info.foreign_factor_id SEPARATOR "," ) AS forArrays,
COUNT(DISTINCT case_info.foreign_factor_id) AS forCount
FROM case_info
WHERE (
case_info.reg_date BETWEEN '2007-02-09' AND '2007-03-09'
OR (
case_info.reg_date < '2007-02-09'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '2007-02-09' AND '2007-03-09'
)
)
AND foreign_factor_id >1
)
GROUP BY case_info.cause_id
) AS t3 on (case_cause.id = t3.causeId)
LEFT OUTER JOIN case_cause_type ON ( case_cause.case_cause_type_id = case_cause_type.id )
LEFT OUTER JOIN case_cause_btype ON ( case_cause_type.case_cause_btype_id = case_cause_btype.id )
ORDER BY
case_cause_btype.id,
case_cause.id
[/code]

[[i] 本帖最后由 xhhy 于 2007-3-9 02:21 PM 编辑 [/i]]

seakingx

2007-3-10 03:48 AM

这个查询语句太劲了，学不来...

deng0685

2007-3-10 06:56 AM

靠不懂~

007007jing

2007-3-10 07:12 AM

这个查询语句你还真想的出
汗

xhhy	2007-3-13 03:42 AM

上面SQL还是精简的

在程序中做优化对运行速度的提升是有限的，当然，写得不好的程序除外。
真正想提高速度，还是要多注意数据库方面的技巧。

比如索引，很多人只知道索引是什么，却没有了解索引的机制。
这条SQL很长很长，但它的运行速度很快，从42W数据统计出一张报表大概是5秒。

其实还有更好的方法，但此项任务的前提条件是不能修改数据库结构，只好用这个笨办法了
[code]
SELECT
case_cause.id,
case_cause_btype.iname AS bTypeName,
case_cause_type.iname AS typeName,
case_cause.iname AS causeName,
(IFNULL(oldRcvCount, 0) + IFNULL(newRcvCount, 0)) AS allRcvCount,
(IFNULL(oldRcvMoney, 0) + IFNULL(newRcvMoney, 0)) AS allRcvMoney,
t2.oldRcvCount AS oldRcvCount,
t2.oldRcvMoney AS oldRcvMoney,
t1.newRcvCount AS newRcvCount,
t1.newRcvMoney AS newRcvMoney,
st1.srcCount AS src1Count,
st2.srcCount AS src2Count,
st3.srcCount AS src3Count,
st4.srcCount AS src4Count,
st5.srcCount AS src5Count,
st6.srcCount AS src6Count,
st7.srcCount AS src7Count,
st8.srcCount AS src8Count,
ft1.forCount AS for1Count,
ft2.forCount AS for2Count,
ft3.forCount AS for3Count,
ft4.forCount AS for4Count,
ft5.forCount AS for5Count,
ft6.forCount AS for6Count,
(IFNULL(ft1.forCount, 0) + IFNULL(ft2.forCount, 0) + IFNULL(ft3.forCount, 0) + IFNULL(ft4.forCount, 0) + IFNULL(ft5.forCount, 0) + IFNULL(ft6.forCount, 0)) AS allForCount
FROM case_cause
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.cause_id) AS newRcvCount,
SUM(case_info.apply_money) AS newRcvMoney
FROM case_info

WHERE (
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)

GROUP BY case_info.cause_id
) AS t1 on (case_cause.id = t1.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.cause_id) AS oldRcvCount,
SUM(case_info.apply_money) AS oldRcvMoney
FROM case_info

WHERE (
case_info.reg_date < '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)

GROUP BY case_info.cause_id
) AS t2 on (case_cause.id = t2.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.foreign_factor_id) AS forCount
FROM case_info

WHERE (
(
case_info.reg_date BETWEEN '1998-02-13' AND '2006-03-13'
OR (
case_info.reg_date < '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)
)
AND case_info.foreign_factor_id = 1
)

GROUP BY case_info.cause_id
) AS ft1 on (case_cause.id = ft1.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.foreign_factor_id) AS forCount
FROM case_info

WHERE (
(
case_info.reg_date BETWEEN '1998-02-13' AND '2006-03-13'
OR (
case_info.reg_date < '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)
)
AND case_info.foreign_factor_id = 2
)

GROUP BY case_info.cause_id
) AS ft2 on (case_cause.id = ft2.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.foreign_factor_id) AS forCount
FROM case_info

WHERE (
(
case_info.reg_date BETWEEN '1998-02-13' AND '2006-03-13'
OR (
case_info.reg_date < '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)
)
AND case_info.foreign_factor_id = 3
)

GROUP BY case_info.cause_id
) AS ft3 on (case_cause.id = ft3.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.foreign_factor_id) AS forCount
FROM case_info

WHERE (
(
case_info.reg_date BETWEEN '1998-02-13' AND '2006-03-13'
OR (
case_info.reg_date < '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)
)
AND case_info.foreign_factor_id = 4
)

GROUP BY case_info.cause_id
) AS ft4 on (case_cause.id = ft4.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.foreign_factor_id) AS forCount
FROM case_info

WHERE (
(
case_info.reg_date BETWEEN '1998-02-13' AND '2006-03-13'
OR (
case_info.reg_date < '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)
)
AND case_info.foreign_factor_id = 5
)

GROUP BY case_info.cause_id
) AS ft5 on (case_cause.id = ft5.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.foreign_factor_id) AS forCount
FROM case_info

WHERE (
(
case_info.reg_date BETWEEN '1998-02-13' AND '2006-03-13'
OR (
case_info.reg_date < '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
)
)
AND case_info.foreign_factor_id = 6
)

GROUP BY case_info.cause_id
) AS ft6 on (case_cause.id = ft6.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 1
)
)

GROUP BY case_info.cause_id
) AS st1 on (case_cause.id = st1.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 2
)
)

GROUP BY case_info.cause_id
) AS st2 on (case_cause.id = st2.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 3
)
)

GROUP BY case_info.cause_id
) AS st3 on (case_cause.id = st3.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 4
)
)

GROUP BY case_info.cause_id
) AS st4 on (case_cause.id = st4.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 5
)
)

GROUP BY case_info.cause_id
) AS st5 on (case_cause.id = st5.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 6
)
)

GROUP BY case_info.cause_id
) AS st6 on (case_cause.id = st6.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 7
)
)

GROUP BY case_info.cause_id
) AS st7 on (case_cause.id = st7.causeId)
LEFT OUTER JOIN (
SELECT
case_info.cause_id AS causeId,
COUNT(case_info.case_src_id) AS srcCount
FROM case_info

WHERE (
(
case_info.reg_date > '1998-02-13'
AND (
case_info.end_case_date IS NULL
OR case_info.end_case_date BETWEEN '1998-02-13' AND '2006-03-13'
)
AND case_info.case_src_id = 8
)
)

GROUP BY case_info.cause_id
) AS st8 on (case_cause.id = st8.causeId)
LEFT OUTER JOIN case_cause_type ON ( case_cause.case_cause_type_id = case_cause_type.id )
LEFT OUTER JOIN case_cause_btype ON ( case_cause_type.case_cause_btype_id = case_cause_btype.id )
ORDER BY
case_cause_btype.id,
case_cause.id
[/code]

菜鸟也要有理想

2007-3-14 10:43 PM

[quote]原帖由 [i]xhhy[/i] 于 2007-3-9 02:19 PM 发表
第一个问题，数据库技巧是真理。

最近接手一个工作，在不修改数据库结构和输出接口的情况下，优化一个报表，数据库中目前有40W+的数据，预计一年内将达到200W以上。
经过一个星期努力，我成功把报表的计算时间 ... [/quote]
你说得太对了
我前段时间也是1000W条记录的数据表
设计程序处理大数据搞了半死居然50多秒
后来记录slow_queries 具体方法看我BLOG
发现原来是少了一个索引。。
加上后
每个查询从5秒缩到0.00x秒
感觉PHP如果做数据处理
很大一部分时间都在等数据库
一定要优化好数据表的索引之类的

菜鸟也要有理想

2007-3-14 10:45 PM

xhhy的SQL好变态啊。。
那么长。。。。

yeyunan

2007-3-15 08:58 AM

谁能具体解释下索引的应用吖？

菜鸟也要有理想

2007-3-16 04:37 AM

[quote]原帖由 [i]yeyunan[/i] 于 2007-3-15 04:58 PM 发表
谁能具体解释下索引的应用吖？ [/quote]
百度搜索一下

php5	2007-3-16 08:05 AM

查过字典就大概了解一下数据库的查询了

danssion

2007-3-16 08:21 AM

这么多
left jion

牛啊

dreamblaze

2007-3-16 10:38 AM

强烈佩服下那么长的SQL

zzffhh

2007-3-20 03:08 PM

太复杂了，看不懂。

芽雨	2007-3-21 01:04 AM

偶是来拜写SQL那位兄弟的

查看完整版本: 如何运用数据结构知识到PHP中？