mysql性能优化的一些建议

mysql性能优化的一些建议

1、EXPLAIN 你的 SELECT 查询
查看rows列可以让我们找到潜在的性能问题。

2、为关键字段添加索引，比如：where, order by, group by等后面的字段
注意：like '%abc%'; 是不会用到索引的
只有 like 'abc%' 才会用到索引

3、当只要一行数据时使用 LIMIT 1
MySQL数据库引擎会在找到一条数据后停止搜索

4、关于join
join是将两张表拼在一起形成一个临时大表的操作，然后在大表里查找记录的；因此，数据量大的话非常慢，记录表不要join。
而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。

5、避免 SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两*立的服务器的话，这还会增加网络传输的负载。
所以，你应该养成一个需要什么就取什么的好的习惯。

6、永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。

7、不要用VARCHAR 类型来当主键

8、使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。
如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

9、尽可能的使用 NOT NULL 定义字段
应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

10、固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

11、字段较多的表，使用垂直分割
比如用户表，经常用到的字段一个表，用户属性分到另一个表

12、DELETE 或 INSERT 语句会锁表
一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。
while (1) {
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
break;
}
usleep(50000);
}

13、关于索引
1、mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。
2、在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。
3、MySQL索引通常是被用于WHERE，ORDER BY，GROUP BY
4、ORDER BY的索引优化：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
5、WHERE + ORDER BY的索引优化：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
此sql的索引将被应用在[columnX]上，虽然[sort]有索引但是用不上，需要建立联合索引才能使order by使用索引
create index index_name(columnX,sort);
注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
6、WHERE + ORDER BY 多个字段
SELECT * FROM [table] WHERE uid=1 ORDER by x,y LIMIT 0,10;
create index index_name(uid,x,y); 注意顺序，index_name(uid,x,y)支持三种索引：uid, uid+x, uid+x+y