使用慢日志查询的 PHP Mysql 优化

Question

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

它返回下一个结果:

/usr/sbin/mysqld，版本:5.0.81-community-log(MySQL 社区版 (GPL))。开始于:

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

我发现您的日志比帮助更令人困惑；这些查询看起来都不是特别复杂，但话又说回来，我不知道你的表结构是什么样的，你有多少服务器负载，或者你的表中有多少记录。如果查询本身实际上运行得非常慢，您可能需要检查您的表是否已正确索引。如果你用 google 搜索 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 应用程序中使用持久连接。

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

这些可以减少 PHP 连接数据库的时间。或者，如果您已经在使用持久连接，则可能是您的 PHP 代码在创建连接、启动事务但随后保持连接并占用连接时出现错误，没有留下可用连接其他用户。当您的 PHP 应用程序与数据库交互时，请确保您使用了正确的错误处理。

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