我该怎么做才能使mysql 100％达到最佳状态？

Question

Recently I've been doing quite a big project with php + mysql. And now I'm concerned about my mysql. What should I do to make my mysql as optimal as possible? Tell everything you know, I'll be really very grateful.

Second question, I use one mysql query per page load which takes information from mysql. It's quite a big query, because I take information from a few tables with a join. Maybe I should do something else?

Thank you.

Answer 1

Some top tips from MySQL Performance tips forge

Specific Query Performance:

1. Use EXPLAIN to profile the query execution plan
2. Use Slow Query Log (always have it on!)
3. Don't use DISTINCT when you have or could use GROUP BY Insert performance
4. Batch INSERT and REPLACE
5. Use LOAD DATA instead of INSERT
6. LIMIT m,n may not be as fast as it sounds
7. Don't use ORDER BY RAND() if you have > ~2K records
8. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
9. Avoid wildcards at the start of LIKE queries
10. Avoid correlated subqueries and in select and where clause (try to avoid in)

Scaling Performance Tips:

1. Use benchmarking
2. isolate workloads don't let administrative work interfere with customer performance. (ie backups)
3. Debugging sucks, testing rocks!
4. As your data grows, indexing may change (cardinality and selectivity change). Structuring may want to change. Make your schema as modular as your code. Make your code able to scale. Plan and embrace change, and get developers to do the same.

Network Performance Tips:

1. Minimize traffic by fetching only what you need. 1. Paging/chunked data retrieval to limit 2. Don't use SELECT * 3. Be wary of lots of small quick queries if a longer query can be more efficient
2. Use multi_query if appropriate to reduce round-trips
3. Use stored procedures to avoid bandwidth wastage

OS Performance Tips:

1. Use proper data partitions 1. For Cluster. Start thinking about Cluster before you need them
2. Keep the database host as clean as possible. Do you really need a windowing system on that server?
3. Utilize the strengths of the OS
4. pare down cron scripts
5. create a test environment