Mysql 开发最佳实践
2014-11-15 14:28:51   来源:我爱运维网   评论:0 点击:

1.Optimize Your Queries For the Query Cache 使用query cache来优化查询Most MySQL servers have query caching enabled...

1. Optimize Your Queries For the Query Cache

    使用query cache来优化查询

Most MySQL servers have query caching enabled. It’s one of the most effective methods of improving performance, that is quietly handled by the database engine. When the same query is executed multiple times, the result is fetched from the cache, which is quite fast.
大多数mysql服务器都开启了query caching。这是提高性能的最直接有效的方法,由数据库引擎直接实现。当同一个查询被多次执行时,结果集可以直接从缓存中获取,非常快速。
The main problem is, it is so easy and hidden from the programmer, most of us tend to ignore it. Some things we do can actually prevent the query cache from performing its task.
  1. // query cache does NOT work  
  2. $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");  
  4. // query cache works!  
  5. $today = date("Y-m-d");  
  6. $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");  
The reason query cache does not work in the first line is the usage of the CURDATE() function. This applies to all non-deterministic functions like NOW() and RAND() etc… Since the return result of the function can change, MySQL decides to disable query caching for that query. All we needed to do is to add an extra line of PHP before the query to prevent this from happening.
第一行的query cache无法工作的原因时使用了CURDATE()函数。这个同样适用于所有的不确定性函数比如NOW()和RAND()等。。。由于返回的结果可能会变化,MYSQL决定对于查询不使用query cache。解决这个问题的办法就是在PHP中加入额外的一行。

2. EXPLAIN Your SELECT Queries


Using the EXPLAIN keyword can give you insight on what MySQL is doing to execute your query. This can help you spot the bottlenecks and other problems with your query or table structures.
The results of an EXPLAIN query will show you which indexes are being utilized, how the table is being scanned and sorted etc…
Take a SELECT query (preferably a complex one, with joins), and add the keyword EXPLAIN in front of it. You can just use phpmyadmin for this. It will show you the results in a nice table. For example, let’s say I forgot to add an index to a column, which I perform joins on:
After adding the index to the group_id field:
Now instead of scanning 7883 rows, it will only scan 9 and 16 rows from the 2 tables. A good rule of thumb is to multiply all numbers under the “rows” column, and your query performance will be somewhat proportional to the resulting number.

3. LIMIT 1 When Getting a Unique Row

  查询一行时用limit 1

Sometimes when you are querying your tables, you already know you are looking for just one row. You might be fetching a unique record, or you might just be just checking the existence of any number of records that satisfy your WHERE clause.
In such cases, adding LIMIT 1 to your query can increase performance. This way the database engine will stop scanning for records after it finds just 1, instead of going thru the whole table or index.
这种情况下,加上limit 1将提高你的效率。数据库引擎在找到一条记录后就停止继续扫描,而不是扫完整张表或者索引。
  1. // do I have any users from Alabama?  
  3. // what NOT to do:  
  4. $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");  
  5. if (mysql_num_rows($r) > 0) {  
  6.     // ...  
  7. }  
  9. // much better:  
  10. $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");  
  11. if (mysql_num_rows($r) > 0) {  
  12.     // ...  
  13. }  

4. Index the Search Fields


Indexes are not just for the primary keys or the unique keys. If there are any columns in your table that you will search by, you should almost always index them.
As you can see, this rule also applies on a partial string search like “last_name LIKE ‘a%’”. When searching from the beginning of the string, MySQL is able to utilize the index on that column.
可以看到,这个规则也适用于partial string搜索比如"last_name LIKE ‘a%"。当从string的开头开始搜索时,MYSQL能够使用到这个字段上面的索引。
You should also understand which kinds of searches can not use the regular indexes. For instance, when searching for a word (e.g. “WHERE post_content LIKE ‘%apple%’”), you will not see a benefit from a normal index. You will be better off using mysql fulltext search or building your own indexing solution.
你也应该能够理解哪种类型的搜索不能够用到索引。比如,当检索一个字(比如, “WHERE post_content LIKE ‘%apple%’),正常的索引将不能够帮助到你。你最好使用MYSQL全文检索或者建立自己的索引解决方案。

5. Index and Use Same Column Types for Joins


If your application contains many JOIN queries, you need to make sure that the columns you join by are indexed on both tables. This affects how MySQL internally optimizes the join operation.
如果你的程序包含了多个JOIN 查询,你需要确认你join的字段在两个表中都被索引。这个会影响到MYSQL如何内部优化join操作。
Also, the columns that are joined, need to be the same type. For instance, if you join a DECIMAL column, to an INT column from another table, MySQL will be unable to use at least one of the indexes. Even the character encodings need to be the same type for string type columns.
  1. // looking for companies in my state  
  2. $r = mysql_query("SELECT company_name FROM users 
  3.     LEFT JOIN companies ON (users.state = companies.state) 
  4.     WHERE = $user_id");  
  6. // both state columns should be indexed  
  7. // and they both should be the same type and character encoding  
  8. // or MySQL might do full table scans  



This is one of those tricks that sound cool at first, and many rookie programmers fall for this trap. You may not realize what kind of terrible bottleneck you can create once you start using this in your queries.
If you really need random rows out of your results, there are much better ways of doing it. Granted it takes additional code, but you will prevent a bottleneck that gets exponentially worse as your data grows. The problem is, MySQL will have to perform RAND() operation (which takes processing power) for every single row in the table before sorting it and giving you just 1 row.
  1. // what NOT to do:  
  2. $r = mysql_query("S



分享到: 收藏