mysql懒加载数据_Mysql 开发最佳实践

"DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");

if(mysql_affected_rows() == 0) {

// done deleting

break;

}

// you can even pause a bit

usleep(50000);

}

18. Smaller Columns Are Faster

更小数据列更快

With database engines, disk is perhaps the most significant bottleneck. Keeping things smaller and more compact is usually helpful in terms of performance, to reduce the amount of disk transfer.

对于数据库引擎，磁盘或许是最大的瓶颈。将数据变小或更精简会减少磁盘传输量，对性能非常有帮助。

MySQL docs have a list of Storage Requirements for all data types.

MYSQL文档对于所有的数据类型有一个存储要求清单。

If a table is expected to have very few rows, there is no reason to make the primary key an INT, instead of MEDIUMINT, SMALLINT or even in some cases TINYINT. If you do not need the time component, use DATE instead of DATETIME.

若数据表中的记录很少，没必要使用INT关键字，而应该使用MEDIUMINT，SMALLINT甚至TINYINT。若不需要精确时间，应该使用DATE而不是DATETIME。

Just make sure you leave reasonable room to grow or you might end up like Slashdot.

务必留有合理的空间增长，否则可能出现诸如Slashdot情况。

19. Choose the Right Storage Engine

使用恰当的存储引擎

The two main storage engines in MySQL are MyISAM and InnoDB. Each have their own pros and cons.

MYSQL的MyISAM与InnoDB两种数据存储引擎，各有优劣势。

MyISAM is good for read-heavy applications, but it doesn't scale very well when there are a lot of writes. Even if you are updating one field of one row, the whole table gets locked, and no other process can even read from it until that query is finished.

MyISAM适合高频读的应用，但它在大量写时扩展性不好。即使仅更新一个字段或者一行记录，整表都会锁住，在操作完成前没有进程可以读取它。

MyISAM is very fast at calculating SELECT COUNT(*) types of queries.

MyISAM在select count(*)这类查询时非常快。

InnoDB tends to be a more complicated storage engine and can be slower than MyISAM for most small applications. But it supports row-based locking, which scales better. It also supports some more advanced features such as transactions.

而InnoDB是更复杂的存储引擎，对于多数小应用而言会比MyISAM表现慢。但它支持行级锁，扩展性更好。它也支持更多诸如transaction之类先进特性。

20. Use an Object Relational Mapper使用对象关系映射器(ORM)

By using an ORM (Object Relational Mapper), you can gain certain performance benefits. Everything an ORM can do, can be coded manually too. But this can mean too much extra work and require a high level of expertise.

使用ORM，可以获得某些更好的收益。ORM可以做的情形，手动代码都可以做到，但这需要更高水平及更多的工作。

ORM's are great for "Lazy Loading". It means that they can fetch values only as they are needed. But you need to be careful with them or you can end up creating to many mini-queries that can reduce performance.

ORM对于“懒加载”的非常棒的，它意味着可以仅传送所需的值。但使用时要小心，不要创建过多的小查询从而降低性能。

ORM's can also batch your queries into transactions, which operate much faster than sending individual queries to the database.

ORM可以进行交易查询的批处理，这比单条数据库查询操作要快得多。

Currently my favorite ORM for PHP isDoctrine. I wrote an article on how toinstall Doctrine with CodeIgniter.

当前我喜爱PHP语言的ORM是Doctrine，关于Doctrine可以参考这篇文章：install Doctrine with CodeIgniter.

21. Be Careful with Persistent Connections

小心使用持续连接

Persistent Connections are meant to reduce the overhead of recreating connections to MySQL. When a persistent connection is created, it will stay open even after the script finishes running. Since Apache reuses it's child processes, next time the process runs for a new script, it will reuse the same MySQL connection.

持续连接意味着减少重连MySQL的消耗。持续连接创建后，即使在查询结整后仍会保持连接。由于Apache重用子进程，下次进程进行新的查询，就可重用这个连接。

It sounds great in theory. But from my personal experience (and many others), this features turns out to be not worth the trouble. You can have serious problems with connection limits, memory issues and so on.

理论上非常棒，但个人经验而言，这个功能有点言过其实，有可能出现连接不足，内存泄露等严重问题。

Apache runs extremely parallel, and creates many child processes. This is the main reason that persistent connections do not work very well in this environment. Before you consider using the mysql_pconnect() function, consult your system admin.

Apache 并行运行并创建多个子进程，这也是持续连接不能很好工作的主要原因。在考虑使用mysql_pconnect()前，请咨询系统管理员。