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