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...
同的值，mysql只需要解析一次。
Also latest versions of MySQL transmits prepared statements in a native binary form, which are more efficient and can also help reduce network delays.
最新的版本的MYSQL使用native binary form传送prepare声明，更为高效，也节省了带宽。
There was a time when many programmers used to avoid prepared statements on purpose, for a single important reason. They were not being cached by the MySQL query cache. But since sometime around version 5.1, query caching is supported too.
曾经有一段时间很多程序员习惯于不使用prepare声明，他们是为了一个重要的原因而故意这么做。因为这个不会给MYSQLquery cache使用到。但是从５．１版本之后这个问题被解决了。
To use prepared statements in PHP you check out the mysqli extension or use a database abstraction layer like PDO.
- // create a prepared statement
- if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
- // bind parameters
- $stmt->bind_param("s", $state);
- // execute
- $stmt->execute();
- // bind result variables
- $stmt->bind_result($username);
- // fetch value
- $stmt->fetch();
- printf("%s is from %s\n", $username, $state);
- $stmt->close();
- }
13. Unbuffered Queries
不缓存的查询
Normally when you perform a query from a script, it will wait for the execution of that query to finish before it can continue. You can change that by using unbuffered queries.
正常情况下，当你的脚本执行一个查询时，它需要等到查询全部执行完才能继续下去。你可以使用不缓存查询来跳过它。
There is a great explanation in the PHP docs for the mysql_unbuffered_query() function:
“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”
However, it comes with certain limitations. You have to either read all the rows or call mysql_free_result()before you can perform another query. Also you are not allowed to use mysql_num_rows() ormysql_data_seek() on the result set.
然而，这个有一定的限制。你必须读取所有的行，或者在执行其它查询之前执行mysql_free_result()。你也不能够在结果集使用mysql_num_rows()或者mysql_data_seek()。
14. Store IP Addresses as UNSIGNED INT
用UNSIGNED INT存储IP地址
Many programmers will create a VARCHAR(15) field without realizing they can actually store IP addresses as integer values. With an INT you go down to only 4 bytes of space, and have a fixed size field instead.
很多程序员会使用VARCHAR(15)来存储IP，而没有意识到可以使用interger. 使用INT你可以节省４个字节的空间，而且字段大小也固定。
You have to make sure your column is an UNSIGNED INT, because IP Addresses use the whole range of a 32 bit unsigned integer.
你必须确保你的字段是UNSIGNED INT, 因为IP地址使用整个３２位的unsigned integer.
In your queries you can use the INET_ATON() to convert and IP to an integer, and INET_NTOA() for vice versa. There are also similar functions in PHP called ip2long() and long2ip().
在你的查询中你可以使用INET_ATON()来转换IP到整型，反之用INET_NTOA()。 PHP也有相似的函数叫ip2long()和long2ip().
- $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
15. Fixed-length (Static) Tables are Faster
定长(静态)表更快
When every single column in a table is “fixed-length”, the table is also considered “static” or “fixed-length”. Examples of column types that are NOT fixed-length are: VARCHAR, TEXT, BLOB. If you include even just 1 of these types of columns, the table ceases to be fixed-length and has to be handled differently by the MySQL engine.
数据表中每一列都是“定长”时，这个表也称之为“静态”或“定长”表。非定义的列类型有：VARCHAR，TEXT与BLOB。若数据表包括有这3类中任一列类型，数据表即非定长表并被MYSQL引擎另行处理。
数据表中每一列都是“定长”时，这个表也称之为“静态”或“定长”表。非定义的列类型有：VARCHAR，TEXT与BLOB。若数据表包括有这3类中任一列类型，数据表即非定长表并被MYSQL引擎另行处理。
Fixed-length tables can improve performance because it is faster for MySQL engine to seek through the records. When it wants to read a specific row in a table, it can quickly calculate the position of it. If the row size is not fixed, every time it needs to do a seek, it has to consult the primary key index.
定长表性能表现优异原因在于MYSQL引擎查找记录更快。当查表中特定行时，引擎可快带计算它的位置，反之若行记录大小不定，每次引擎都得使用关键字索引进行查询。
定长表性能表现优异原因在于MYSQL引擎查找记录更快。当查表中特定行时，引擎可快带计算它的位置，反之若行记录大小不定，每次引擎都得使用关键字索引进行查询。
They are also easier to cache and easier to reconstruct after a crash. But they also can take more space. For instance, if you convert a VARCHAR(20) field to a CHAR(20) field, it will always take 20 bytes of space regardless of what is it in.
By using “Vertical Partitioning” techniques, you can separate the variable-length columns to a separate table. Which brings us to:
而且定长表更加容易缓存与崩溃后重建，当然它会更消耗一些空间。比如，若要转换VARCHAR(20)字段到CHAR(20)，无论字段包含多大内容，它将总是占用20个字节的空间。通过使用“垂直分区”技术，可以分离变长的字段列到另外一个数据表中，这就引出：
而且定长表更加容易缓存与崩溃后重建，当然它会更消耗一些空间。比如，若要转换VARCHAR(20)字段到CHAR(20)，无论字段包含多大内容，它将总是占用20个字节的空间。通过使用“垂直分区”技术，可以分离变长的字段列到另外一个数据表中，这就引出：
16. Vertical Partitioning
垂直分区
Vertical Partitioning is the act of splitting your table structure in a vertical manner for optimization reasons.
垂直分区是一种以垂直方式分割数据表结构的优化方法。
垂直分区是一种以垂直方式分割数据表结构的优化方法。
Example 1: You might have a users table that contains home addresses, that do not get read often. You can choose to split your table and store the address info on a separate table. This way your main users table will shrink in size. As you know, smaller tables perform faster.
例1：有一包含家庭住址字段的用户表，但该字段很少会读到。就可以分割用户表，将家庭住址信息分离放到另外一张表上。这种操作方式就可以将主用户缩小，小表操作也更快。
例1：有一包含家庭住址字段的用户表，但该字段很少会读到。就可以分割用户表，将家庭住址信息分离放到另外一张表上。这种操作方式就可以将主用户缩小，小表操作也更快。
Example 2: You have a “last_login” field in your table. It updates every time a user logs in to the website. But every update on a table causes the query cache for that table to be flushed. You can put that field into another table to keep updates to your users table to a minimum.
例2：一家包括“最后登录”字段的用户表，用户每登录网站都会要更新，而每次对表的更新都会导致这个表的查询缓存要刷新。这种情况就可以将该字段将到另外一张表中，从而将用户表的更新减少到最小。
例2：一家包括“最后登录”字段的用户表，用户每登录网站都会要更新，而每次对表的更新都会导致这个表的查询缓存要刷新。这种情况就可以将该字段将到另外一张表中，从而将用户表的更新减少到最小。
But you also need to make sure you don’t constantly need to join these 2 tables after the partitioning or you might actually suffer performance decline.
要注意是的，确保不会经常要JOIN这两张垂直分割的表，这种JOIN操作的性能严重下降。
要注意是的，确保不会经常要JOIN这两张垂直分割的表，这种JOIN操作的性能严重下降。
17. Split the Big DELETE or INSERT Queries
分割大的删除或插入请求
If you need to perform a big DELETE or INSERT query on a live website, you need to be careful not to disturb the web traffic. When a big query like that is performed, it can lock your tables and bring your web application to a halt.
若要对在线网站进行大的删除或插入数据表操作请求，需要小心影响网站流量。这类操作请求是锁表的，可能导致网站应用挂起。
Apache runs many parallel processes/threads. Therefore it works most efficiently when scripts finish executing as soon as possible, so the servers do not experience too many open connections and processes at once that consume resources, especially the memory.
Apache运行多个并行进程/线程，脚本尽快执行完成使之可运行的更有效率。服务器无法忍受同时消耗资源的过多连接与进程，尤其是内存。
Apache runs many parallel processes/threads. Therefore it works most efficiently when scripts finish executing as soon as possible, so the servers do not experience too many open connections and processes at once that consume resources, especially the memory.
Apache运行多个并行进程/线程，脚本尽快执行完成使之可运行的更有效率。服务器无法忍受同时消耗资源的过多连接与进程，尤其是内存。
If you end up locking your tables for any extended period of time (like 30 seconds or more), on a high traffic web site, you will cause a process and query pileup, which might take a long time to clear or even crash your web server.
若高流量网站锁表超过一定的时限（如30秒或以上），将导致进程请求堆积，需要很长时间缓解或导致服务器崩溃。
若高流量网站锁表超过一定的时限（如30秒或以上），将导致进程请求堆积，需要很长时间缓解或导致服务器崩溃。
If you have some kind of maintenance script that needs to delete large numbers of rows, just use the LIMIT clause to do it in smaller batches to avoid this congestion.
若有某类维护脚本需要删除表中很多行时，仅需使用LIMIT语句小批量来做，就可以避免这种冲突。
若有某类维护脚本需要删除表中很多行时，仅需使用LIMIT语句小批量来做，就可以避免这种冲突。
- while (1) {
- mysql_query("DELETE FR
相关热词搜索：Mysql
上一篇：阿里双十一核心数据库技术实践
下一篇：分布式MYSQL部署的可行方案
分享到：
收藏
收藏
评论排行
- ·Windows（Win7)下用Xming...(92)
- ·使用jmx client监控activemq(20)
- ·Hive查询OOM分析(14)
- ·复杂网络架构导致的诡异...(8)
- ·使用 OpenStack 实现云...(7)
- ·影响Java EE性能的十大问题(6)
- ·云计算平台管理的三大利...(6)
- ·Mysql数据库复制延时分析(5)
- ·OpenStack Nova开发与测...(4)
- ·LTPP一键安装包1.2 发布(4)
- ·Linux下系统或服务排障的...(4)
- ·PHP发布5.4.4 和 5.3.1...(4)
- ·RSYSLOG搭建集中日志管理服务(4)
- ·转换程序源码的编码格式[...(3)
- ·Linux 的木马程式 Wirenet 出现(3)
- ·Nginx 发布1.2.1稳定版...(3)
- ·zend framework文件读取漏洞分析(3)
- ·Percona Playback 0.3 development release(3)
- ·运维业务与CMDB集成关系一例(3)
- ·应该知道的Linux技巧(3)
频道总排行
