可扩展性代码问题 - PHP与MySQL [英] Scalability Code question - PHP vs MySQL

问题描述

我很难搞清楚这两个选项中哪一个最好是
。这是用PHP处理我的数据的问题，而不是MySQL。

通常这不是一件容易的事情，但我在这里有几个小天使和

会喜欢任何和所有意见在这里。我打算将它缩短为

并尽可能简单...


这适用于流量非常高的电子商务网站和选择

可能不是基于速度，而是更具可扩展性。我需要这个才能持续下去。所以这里是我的测试代码..你可能不知道所有这些功能，但我认为他们非常直接：


/ / 2方式这样做... 1个查询或更多？

$ start = microtime（true）;

$ productSql =" SELECT * FROM $ searchTemp $ productWhere $排序英寸; //

searchTemp是一个非规范化数据的大表

$ searchResults = $ my-> returnTableAssoc（$ productSql，

$ selectFromSlave） ; //这只返回一个多维数组

结果


//这是一个多维数组的array_unique，并将

基本上就像group_by productid

$ products = remove_dups（$ searchResults，''productid''）;

//获取所需的其他数据栏

$ brands = array（）;

$ cats = array（）;

$ colors = array（）;

$ years = array（）;

$ bootWidth = array（）;

$ flex = array（）;

foreach（$ searchResults as $ sr）

{

$ brands [] = $ sr [''制造商''];

$ cats [] = $ sr [ ''categoryid''];

$ colors [] = $ sr [''colorcode''];

$ years [] = $ sr [''modelYear' '];

$ bootWidth [] = $ sr [''bootWidth''];

$ flex [] = $ sr [''flexRating'']; < br $>
}

$ brands = array_unique（$ brands）;

$ cats = array_unique（$ cats）;

$ colors = array_uni que（$ colors）;

$ years = array_unique（$ years）;

$ bootWidth = array_unique（$ bootWidth）;

$ flex = array_unique（$ flex）;

$ end = microtime（true）;

echo"first in in 。 （$ end - $ start）。 "秒< br>" ;;


//再试一次 - 只做一堆查询让mysql做所有的

工作

$ productSql =" SELECT * FROM $ searchTemp $ productWhere GROUP BY

productid $ sort";

$ products = $ my-> returnTableAssoc（$ productSql，$ selectFromSlave）;

$ productSql =" SELECT distinct manufacturer FROM $ searchTemp

$ productWhere" ;;

$ brands = $ my - > returnArray（$ productSql，$ selectFromSlave）;

$ productSql =" SELECT distinct categoryid FROM $ searchTemp

$ productWhere" ;;

$ cats = $ my-> returnArray（$ productSql，$ selectFromSlave）;

$ productSql =" SELECT distinct colorcode FROM $ searchTemp

$ productWhere" ;;

$ colors = $ my-> returnArray（$ productSql，$ selectFromSlave）;

$ productSql =" SELECT distinct modelYear FROM $ searchTemp

$ productWhere" ;;

$ years = $ my-> return数组（$ productSql，$ selectFromSlave）;

$ productSql =" SELECT distinct bootWidth FROM $ searchTemp

$ productWhere" ;;

$ bootWidth = $ my-> returnArray（$ productSql，$ selectFromSlave）;

$ productSql =" SELECT distinct flexRating FROM $ searchTemp

$ productWhere" ;;

$ flex = $ my-> returnArray（$ productSql，$ selectFromSlave）;

$ end = microtime（true）;

echo"第二次在 。 （$ end - $ start）。 "秒< br>" ;;

因此，在我的开发服务器上，＃1在.9秒内运行，而＃2在

中运行3.7秒。然而，在我的实时制作环境中，有2个/ b $ b $ web服务器和2个数据库服务器，它们的运行时间约为1.1秒

。它基本上是一个平局。


要记住的另一件事是我选择的任何一个选项，我会用/或
使用memcache来加快速度还有。


所以，简而言之，两者都以相同的速度运行，但哪一个更可靠？
可扩展？


谢谢。

I''m having a tough time figuring out which of these two options are
best. This is a matter of processing my data in PHP, vs MySQL.
Usually that''s a no brainer, but I have a couple gotchyas here and
would love any and all opinions here. I''m going to make this as short
and simple as I can...

This is for an e-commerce site with very high traffic, and the choice
will probably not be based on speed, but which is more scalable. I
need this to last. So here''s my test code.. you may not know all
these functions, but I think they''re very straight forward:

// 2 ways of doing this.. 1 query or more?
$start = microtime(true);
$productSql = "SELECT * FROM $searchTemp $productWhere $sort"; //
searchTemp is a large table of denormalized data
$searchResults = $my->returnTableAssoc($productSql,
$selectFromSlave); // this just returns a multidimensional array of
the results

// this is an array_unique for a multidimensional array and will
essentially be like group_by productid
$products = remove_dups($searchResults, ''productid'');
// get the other columns of data needed
$brands = array();
$cats = array();
$colors = array();
$years = array();
$bootWidth = array();
$flex = array();
foreach($searchResults as $sr)
{
$brands[] = $sr[''manufacturer''];
$cats[] = $sr[''categoryid''];
$colors[] = $sr[''colorcode''];
$years[] = $sr[''modelYear''];
$bootWidth[] = $sr[''bootWidth''];
$flex[] = $sr[''flexRating''];
}
$brands = array_unique($brands);
$cats = array_unique($cats);
$colors = array_unique($colors);
$years = array_unique($years);
$bootWidth = array_unique($bootWidth);
$flex = array_unique($flex);
$end = microtime(true);
echo "Did first in " . ($end - $start) . " seconds <br>";

// try again - just do a bunch of queries and let mysql do all the
work
$productSql = "SELECT * FROM $searchTemp $productWhere GROUP BY
productid $sort";
$products = $my->returnTableAssoc($productSql, $selectFromSlave);
$productSql = "SELECT distinct manufacturer FROM $searchTemp
$productWhere";
$brands = $my->returnArray($productSql, $selectFromSlave);
$productSql = "SELECT distinct categoryid FROM $searchTemp
$productWhere";
$cats = $my->returnArray($productSql, $selectFromSlave);
$productSql = "SELECT distinct colorcode FROM $searchTemp
$productWhere";
$colors = $my->returnArray($productSql, $selectFromSlave);
$productSql = "SELECT distinct modelYear FROM $searchTemp
$productWhere";
$years = $my->returnArray($productSql, $selectFromSlave);
$productSql = "SELECT distinct bootWidth FROM $searchTemp
$productWhere";
$bootWidth = $my->returnArray($productSql, $selectFromSlave);
$productSql = "SELECT distinct flexRating FROM $searchTemp
$productWhere";
$flex = $my->returnArray($productSql, $selectFromSlave);
$end = microtime(true);
echo "Did second in " . ($end - $start) . " seconds <br>";
So, on my development server, #1 runs in .9 seconds, and #2 runs in
3.7 seconds. However in my live production environment with 2
webservers and 2 database servers, they run at approx 1.1 seconds
each. It''s essentially a tie.

Another thing to keep in mind is whichever option I choose, I''ll be
using memcache to speed things along also.

So, in short, both run at the same speed, but which one is more
scalable?

Thanks.

推荐答案

start = microtime（true）;

start = microtime(true);

productSql =" SELECT * FROM

productSql = "SELECT * FROM

searchTemp

searchTemp

这篇关于可扩展性代码问题 - PHP与MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！