MySQL的20+最佳实践

MySQL的20+最佳实践

 

1. 优化查询的查询缓存

 

      大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。

 

MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。

 

 

2. explain你的选择查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

 

EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

 

在一个select查询（有连接的复杂查询）的前面加上explain，就会显示相关的信息

EXPLAIN
SELECT c.id, cl.question, cl.lang, cl.answer FROM cs_faq c
LEFT JOIN cs_faq_lang cl ON cl.cs_faq_id = c.id
WHERE c.id < 10;

 

 

3. 获取唯一一行时用LIMIT 1 

确定了只有一条数据后，使用LIMIT 1的好处是，当扫描到数据后就停止扫描，不会再遍历后面的表和索引。

 

 

4. 适当加索引

一般要加索引的情况如下：

1）where后面的条件

2）搜索字段，如like ‘aa%’，就可以加索引，但是like ‘%aa%’，就没有效果。可以改用全文索引。

3）要在重复性低的字段加索引

 

 

5. 使用索引时，用到字段连接，要相同数据类型

如果使用一个decimal字段，连接另一个int字段，MySQL就无法使用这些字段的索引来索引合并后的字段。

// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");
 
// 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。

 

 

6.不要用order by RAND()

 

7. 尽量不用select * ，查询更少的字段会更快

 

8. 所有表都要有一个id主键，并且一般是用int 并且AUTO_INCREMENT

 

9. 相比varchar，优先使用enum

enum所占用空间更少，如果像status列，存放的是状态字符串，“active”，“inactive”，“pending”等，这类用enum会更省空间

 

10. 使用procedure analyse()，获取表字段的建议

SELECT * FROM faq_lang procedure analyse();

 

 前面的列都是表信息，最后一列就是建议。不过这只是建议，我们还是要根据实际情况选择是否修改表字段。

 

 

11. 如果可以的话，尽量使用 not null

1）除非有非常重要的理由使用null，否则将列设置未not null

2）要清楚空字符串和null的区别

3）null需要额外的空间，会增加比较语句的复杂度

 

12. 预处理语句

使用PrepareStatement，预编译的方式，可以防止SQL注入的问题。

 

13. 无缓冲查询

 

14. 使用unsigned int 存储IP地址

一般我们会用varchar(15)存IP地址，如果用unsigned int，占用空间会更小

 

15. 固定长度（静态）的表会更快

不固定长度列的类型包括：varchar、text、blob，如果用固定长度，检索的时候会更快，但是会浪费一部分空间。

 

16. 垂直分表

例如：一张用户表，包含家庭住址，而这个不是常用的数据。这样就可以将地址拆分出来，保存到另一个表中，用户表越小查询就越快。

 

17. 拆分大型delete和insert语句

 这种大型SQL语句会锁表，并使得Web应用程序停止。这样对导致其他用户无法使用，为了避免这种情况，可以执行一部分SQL后sleep()一段时间。

 

18. 越小的列，越少的列，越快

 

19. 正确的选择存储引擎

1）MyISAM，表锁，适合大量的查询

2）InnoDB，行锁，适合跟多的更新数据，支持事务，支持崩溃回滚

 

20. 使用对象关系映射器，ORM

1）方便数据迁移

2）可以将多个查询批处理到事务中，比向数据库发起单个查询更快

 

21. 小心持久链接

1）减少重新连接到MySQL的成本

2）但可能会遇到连接数限制问题，内存问题

 

 

参考：

http://coolshell.cn/articles/1846.html