操作系统 | 基础服务 | 网络技术 | IT技术 | WEB技术 | 数据库技术 | 高可用技术 | 安全技术 | 运维工具 | 脚本编写 | 分布式技术 | 大数据分析 | 搜索
首页 > 运维技术 > 数据库技术 > 正文

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.
主要问题是，由于这个过于简单，直接对程序员透明，我们经常会忽略它。有一些操作会造成querycache功能无法生效。
  1. // query cache does NOT work  
  2. $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");  
  3.   
  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

    解析SELECT查询语句

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.
使用EXPLAIN关键字能够让你看到MYSQL在执行你的查询时，内部的实际工作流程。这能够帮助你定位到你的查询或者表结构的瓶颈和其他问题。
The results of an EXPLAIN query will show you which indexes are being utilized, how the table is being scanned and sorted etc…
EXPLAIN的结果将展示给你哪些索引会被用到，表是怎么被扫描，排序等等。
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:
拿一个SELECT查询(一个使用join的复杂查询最好)，在语句前面加上EXPLAIN.你可以使用phpmyadmin。他将结果以表的形式返回给你。比如，我在某个字段上面缺失了索引，下面执行了一个join:
\
After adding the index to the group_id field:
在字段group_id上添加索引后：
\
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.
他将只扫描两个表的9行和16行，而不是所有的7883行。一个很好的窍门就是将rows字段的所有数字相乘，你的查询性能将会和这个数字成比例(注：对于这类联合查询适用)

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.
有些时候当你查询表时，你已经知道你只会拿到一行。你可能是查看一个唯一记录，或者你可能只是检查满足你的where条件
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?  
  2.   
  3. // what NOT to do:  
  4. $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");  
  5. if (mysql_num_rows($r) > 0) {  
  6.     // ...  
  7. }  
  8.   
  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

  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.
另外，这些做了JOIN操作的字段，需要是同一种类型。比如，如果你将一个DECIMAL字段和一个INT字段做了关联，MYSQL将至少不能用到一个索引。对于string类型，甚至字符编码需要时同样的类型。
  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 users.id = $user_id");  
  5.   
  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  

6. Do Not ORDER BY RAND()

   切忌ORDER BY RAND()

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.
如果你确实需要随机的返回行，我们有更为有效的方法。虽然它多了一些代码量，但是你能够根本上解决掉这个随着数据量增长而出现的瓶颈。问题就是，MYSQL将对于表中的所有行做RAND操作（耗cpu）然后才返回给你一行。
  1. // what NOT to do:  
  2. $r = mysql_query("S
上一页 1 2 3 4 下一页

相关热词搜索：Mysql

上一篇：阿里双十一核心数据库技术实践
下一篇：最后一页

延伸阅读：
分享到： 收藏
评论排行
频道总排行
频道本月排行

关于我们 | 联系方式 | 版权声明 | 招聘信息 | 友情链接
Powered by 5iOPS © 2012 沪ICP备12031567号