使用慢速日志查询的PHp Mysql OPtimization

Question

嗨，我还在研究mysql的性能，目前我开始在mysql服务器上检查-log-slow-query命令。

它返回给我下一个结果：

/usr/sbin/mysqld，版本: 5.0.81- Community -log (MySQL社区版)。开头是：

Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
110211  0:28:16       1 Connect     promls_sysdba@localhost on 
              1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       select id , fullname from users where company_id = 7
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       select id , name from groups where company_id = 7 and context = "agency"
              1 Query       SELECT FOUND_ROWS() AS total_results
110211  0:28:26       1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       SELECT  SQL_CALC_FOUND_ROWS property_id, property_type,property_name, bathrooms, bedrooms, for_sale, for_rent, for_vacational, units, 
                            city,estate,country, rent_price, sale_price, min_price,mid_price, hig_price, max_sale,min_sale, min_rent, max_rent, min_vrental,max_vrental,
                            negotiation,property_status,
                            category_es category, image, dev_logo, inventory,company_id,broker_id FROM property_listing  where property_status in('active','active-rented','active-sold')   group by parent   order by min_price asc, mid_price asc, hig_price asc, rent_price asc, sale_price asc  limit 10, 10
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       select st.id estate_id,st.description estate from active_locations group by estate_id
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       select city_id , city  from active_locations where estate_id =272 group by city_id
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       select f.id, f.name_es as name from families f inner join category_types ct on ct.category = f.id where type='category' group by f.id
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       select t.id, t.name_es as name from property_types t inner join category_types ct on ct.property_type = t.id where  ct.category =12
              1 Query       SELECT FOUND_ROWS() AS total_results
110211  0:28:56       2 Connect     root@localhost on 
              2 Query       select id_area , description from areas where parent_area = 2
              2 Quit       
110211  0:28:58       1 Quit       
110211  0:29:01       3 Connect     promls_sysdba@localhost on 
              3 Query       SET NAMES utf8
              3 Init DB     promls_box
              3 Query       select id , fullname from users where company_id = 7
              3 Query       SELECT FOUND_ROWS() AS total_results
              3 Query       select id , name from groups where company_id = 7 and context = "agency"
              3 Query       SELECT FOUND_ROWS() AS total_results
110211  0:29:43       4 Connect     viva_webjun@localhost on 
              4 Init DB     viva_blogen
              4 Query       SET NAMES utf8
              4 Query       select name, val from txp_prefs where prefs_id=1 AND user_name=''
              4 Query       select name, data from txp_lang where lang='en-gb' AND ( event='public' OR event='common')
              4 Query       select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order
              4 Query       select page, css from txp_section where name = 'default' limit 1
              4 Query       select unix_timestamp(val) from txp_prefs where `name` = 'lastmod' limit 1
              4 Query       select name from txp_section where in_rss != '1'
              4 Query       select *, unix_timestamp(Posted) as uPosted, unix_timestamp(LastMod) as uLastMod, unix_timestamp(Expires) as uExpires, ID as thisid from textpattern where Status = 4 and Section != 'about'  and Posted < now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') order by Posted desc limit 5
              4 Query       select RealName from txp_users where `name` = 'webjunkie01' limit 1
              4 Query       select unix_timestamp(Posted) from textpattern where Posted <= now() and Status >= 4 order by Posted desc limit 1
              4 Quit       
110211  0:29:44       5 Connect     viva_webjun@localhost on 
              5 Init DB     viva_blogen
              5 Query       SET NAMES utf8
              5 Query       select name, val from txp_prefs where prefs_id=1 AND user_name=''
              5 Query       select name, data from txp_lang where lang='en-gb' AND ( event='public' OR event='common')
              5 Query       select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order
              5 Query       select page, css from txp_section where name = 'default' limit 1
              5 Query       select unix_timestamp(val) from txp_prefs where `name` = 'lastmod' limit 1
              5 Query       select name from txp_section where in_rss != '1'
              5 Query       select *, unix_timestamp(Posted) as uPosted, unix_timestamp(LastMod) as uLastMod, unix_timestamp(Expires) as uExpires, ID as thisid from textpattern where Status = 4 and Section != 'about'  and Posted < now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') order by Posted desc limit 5
              5 Query       select RealName from txp_users where `name` = 'webjunkie01' limit 1
              5 Query       select unix_timestamp(Posted) from textpattern where Posted <= now() and Status >= 4 order by Posted desc limit 1
              5 Quit       
110211  0:30:02       6 Connect     root@localhost on 
              6 Query       show processlist
              6 Quit       
110211  0:32:28       3 Quit       
              7 Connect     promls_sysdba@localhost on 
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select id , fullname from users where company_id = 7
              7 Query       SELECT FOUND_ROWS() AS total_results
              7 Query       select id , name from groups where company_id = 7 and context = "agency"
              7 Query       SELECT FOUND_ROWS() AS total_results
110211  0:33:02       7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select * from message_listing where (company_id = 7 or agency_id =7 ) and ( broker_id =2 ) and readed !=1 limit 0,5
              7 Query       SELECT FOUND_ROWS() AS total_results
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select * from message_listing  where message_type = 'networking' and (category_message ='all' or (category_message='city' and area_id = 588  ) or (category_message='estate' and area_id = 272 ) or (category_message='agency' and company_id = 7 ))  limit 0,5
              7 Query       SELECT FOUND_ROWS() AS total_results
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select SQL_CALC_FOUND_ROWS id , name, contact, email_contact, phone_contact, address, phone,
            fax, email, website, creation_date, last_modification, area_id,  area, status , logo, type, owner_id, users,
              creator, creator_id
    from companies_listing where  status = 'active' and type in( 'sponsor' )
              7 Query       SELECT FOUND_ROWS() AS total_results
110211  0:34:20       7 Quit       
110211  0:34:44       8 Connect     promls_sysdba@localhost on 
              8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       select id , fullname from users where company_id = 7
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       select id , name from groups where company_id = 7 and context = "agency"
              8 Query       SELECT FOUND_ROWS() AS total_results
110211  0:35:01       9 Connect     root@localhost on 
              9 Query       show processlist
              9 Quit       
110211  0:35:16       8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       SELECT  SQL_CALC_FOUND_ROWS property_id, property_type,property_name, bathrooms, bedrooms, for_sale, for_rent, for_vacational, units, 
                            city,estate,country, rent_price, sale_price, min_price,mid_price, hig_price, max_sale,min_sale, min_rent, max_rent, min_vrental,max_vrental,
                            negotiation,property_status,
                            category_es category, image, dev_logo, inventory,company_id,broker_id FROM property_listing  where property_status in('active','active-rented','active-sold')   group by parent   order by min_price asc, mid_price asc, hig_price asc, rent_price asc, sale_price asc  limit 0, 10
110211  0:35:17       8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       select st.id estate_id,st.description estate from active_locations group by estate_id
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       select city_id , city  from active_locations where estate_id =272 group by city_id
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       select f.id, f.name_es as name from families f inner join category_types ct on ct.category = f.id where type='category' group by f.id
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       select t.id, t.name_es as name from property_types t inner join category_types ct on ct.property_type = t.id where  ct.category =12
              8 Query       SELECT FOUND_ROWS() AS total_results
110211  0:35:43      10 Connect     promls_sysdba@localhost on 
             10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       select id , fullname from users where company_id = 7
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       select id , name from groups where company_id = 7 and context = "agency"
             10 Query       SELECT FOUND_ROWS() AS total_results
              8 Quit       
110211  0:36:08      10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       SELECT  SQL_CALC_FOUND_ROWS property_id, property_type,property_name, bathrooms, bedrooms, for_sale, for_rent, for_vacational, units, 
                            city,estate,country, rent_price, sale_price, min_price,mid_price, hig_price, max_sale,min_sale, min_rent, max_rent, min_vrental,max_vrental,
                            negotiation,property_status,
                            category_es category, image, dev_logo, inventory,company_id,broker_id FROM property_listing  where property_status in('active','active-rented','active-sold')   group by parent   order by min_price asc, mid_price asc, hig_price asc, rent_price asc, sale_price asc  limit 0, 10
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       select st.id estate_id,st.description estate from active_locations group by estate_id
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       select city_id , city  from active_locations where estate_id =272 group by city_id
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       select f.id, f.name_es as name from families f inner join category_types ct on ct.category = f.id where type='category' group by f.id
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       select t.id, t.name_es as name from property_types t inner join category_types ct on ct.property_type = t.id where  ct.category =12
             10 Query       SELECT FOUND_ROWS() AS total_results
110211  0:36:21      10 Quit       
110211  0:36:22      11 Connect     promls_sysdba@localhost on 
             11 Query       SET NAMES utf8
             11 Init DB     promls_box
             11 Query       select id , fullname from users where company_id = 7
             11 Query       SELECT FOUND_ROWS() AS total_results
             11 Query       select id , name from groups where company_id = 7 and context = "agency"
             11 Query       SELECT FOUND_ROWS() AS total_results

从php到mysql的连接看起来花了很多时间，我怎么解决这个问题呢？

Answer 1

我发现您的日志更令人困惑而不是有帮助；这些查询看起来都不是特别复杂，但是话又说回来，我不知道您的表结构是什么样子的，您的服务器负载有多大，或者表中有多少条记录。如果查询本身实际上运行得非常慢，那么您可能需要检查表是否被正确索引。如果你用谷歌搜索mysql索引，你可以找到大量的信息，如果你真的想要了解它，可以看看这本电子书：http://sql-performance-explained.com/

我还注意到有一些查询同时使用ORDER BY和LIMIT语句。在这种情况下，您应该查看这两个命令在一起的关系，以及如何优化这些类型的查询。有关更多信息，请查看此站点：

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

但如果不是查询，也许正如您所说的那样，MySQL在创建到数据库的连接时遇到了困难。如果您有很多用户，可能是因为您的可用连接太少，您需要增加MySQL允许的并发连接数。请看这里，了解如何做到这一点：

http://rackerhacker.com/2007/01/24/increase-mysql-connection-limit/

另一方面，如果PHP应用程序需要很长时间才能连接到数据库，则可能需要考虑在PHP应用程序中使用持久连接。

http://php.net/manual/en/features.persistent-connections.php

这可以减少PHP连接到数据库所花费的时间。或者，如果您已经在使用持久连接，那么可能是您的PHP代码中存在一个错误，它创建了一个连接，启动了一个事务，但随后保留了它，并独占了该连接，从而没有为其他用户留下可用的连接。确保在PHP应用程序与数据库交互时使用正确的错误处理。

同样，这些只是我根据您所展示的内容做出的一些最好的猜测。我希望它能让你朝着正确的方向前进。

Answer 2

我继承了一个可怕的PHP后端，它有错误，使其查询数据库数百次(高达600为网站索引页)。客户不会花钱做必要的后端重写，但我想加快速度。

查询都很小，在一个小数据库上，但后端为每个查询连接并选择一个数据库。

我写了下面的快速代码来最小化这部分流量：

function DBConnectOnce($dbhost, $dbname, $dbuser, $dbpasswd) {
    $GLOBALS['dbrecord_Debug'] = ($dbDebug == 'true'); // @TODO better way to handle this within the class?
    if(isset($GLOBALS['DBRECORDCONFIGSTATUS'])&&$GLOBALS['DBRECORDCONFIGSTATUS']==$dbuser.$dbname.$dbhost){//check to see if the connectionon the proper type has been made.
        $dblink=unserialize($GLOBALS['DBRECORDCONNECTION']);
    } else {
        $GLOBALS['DBRECORDCONFIGSTATUS']=$dbuser.$dbname.$dbhost;
        $GLOBALS['DBRECORDCONNECTION']=serialize($dblink = mysql_connect($dbhost, $dbuser, $dbpasswd));

        if($dblink === false) {
            trigger_error("DBConnectOnce: Unable to connect to database server - ".mysql_error(), E_USER_ERROR);
        }

        if(!mysql_select_db($dbname)) {
            trigger_error("DBConnectOnce: Unable to select database - $dbname at $dbhost", E_USER_ERROR);
        }
    }
return $dblink;

}

它使用全局变量来检查您已经建立的数据库连接的类型，如果它与您需要建立的数据库连接的类型相匹配，则使用它。