MySQL 性能优化的 21 个最佳实践( 三 ) _MySQL

Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL 注入式”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。当我们使用一些framework 或是 ORM 的时候，这样的问题会好一些。
在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些 Prepared Statements 定义一些参数，而 MySQL 只会解析一次。
虽然最新版本的 MySQL 在传输 Prepared Statements 是使用二进制形势，所以这会使得网络传输非常有效率。
当然，也有一些情况下，我们需要避免使用 Prepared Statements，因为其不支持查询缓存。但据说版本 5.1 后支持了。
在 PHP 中要使用 prepared statements，你可以查看其使用手册：mysqli
扩展或是使用数据库抽象层，如： PDO.

文章插图

13.无缓冲的查询
正常的情况下，当你在当你在你的脚本中执行一个 SQL 语句的时候，你的程序会停在那里直到没这个 SQL 语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。
mysql_unbuffered_query() 发送一个 SQL 语句到 MySQL 而并不像mysql_query()一样去自动 fethch 和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。
然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且，mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以，是否使用无缓冲的查询你需要仔细考虑。
14.把 IP 地址存成 UNSIGNED INT
很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的 IP 而不是整形的 IP 。如果你用整形来存放，只需要 4 个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的 WHERE 条件：IP between ip1 and ip2 。
我们必需要使用 UNSIGNED INT，因为 IP 地址会使用整个 32 位的无符号整形。
而你的查询，你可以使用 INET_ATON() 来把一个字符串 IP 转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串 IP 。在 PHP 中，也有这样的函数 ip2long() 和 long2ip() 。

文章插图

15.固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length” 。例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB 。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为 MySQL 搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。
使用“垂直分割”技术(见下一条)，你可以分割你的表成为两个一个是定长的，一个则是不定长的。
16.垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。(以前，在银行做过项目，见过一张表有 100 多个字段，很恐怖)
示例一：在 Users 表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。
示例二：你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户 ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。