在重负载下检索mysql数据的正确方法

I have build a system using PHP-MySQL. This system is subject to a very heavy load, with thousands of selects,updates,inserts,deletes every minute.

I would like to optimize this system, to make it faster, and reduce load on the servers. I have already introduced memcache, but mysql data is still needed.

So my question is, which method would be the best in this case. Currently my queries would look like this:

$q = mysql_query($sql);
while(mysql_fetch_array($q)) {...

I have read that there is a little speed to gain by using mysql_fetch_assoc (?)

But perhaps there is an antirely different approach, when i start optimizing this system?

Thank you all - (Apologies for my limited english skills)

mysql_fetch_assoc vs mysql_fetch_array will duplicate less data thus use less memory. Since the data is presented associative and by index in the array, with that you will get some tiny optimization although will help if your dataset is big.

  • Try to use natural sort (AKA avoid SORT in query sentences) and LIMIT your result set if you can
  • Batch queries: instead run 100 inserts over the same table try to do a few of them small.
  • cache cache cache if you can: using redis or memcached.
  • if you generate pages that can be treated as static try to use HTTP headers to avoid browsers to request your site all the time
  • etc. etc.

I would recommend you to use the mysql keyword LIMIT to limit the result set.

Adding pagination to the mysql returning resultset will make your application lighter, the ui will load faster because of less rows to fetch and the mysql server will only receive the select queries when needed. Basically this is the syntax of how to use limit.

SELECT * FROM Person LIMIT X,Y

Where X is the total row count to be retrieved and Y the offset.

Example:

SELECT * FROM Person LIMIT 10, 0

This query will return the first ten rows of the table Person, and:

SELECT * FROM Person LIMIT 10, 10

Will display the next 10