MySql 优化

数据库的操作向来是应用的瓶颈，关注数据库的优化不仅是DBA的事情，程序员也应关注。

 

1. 使用查询缓存

使用查询缓存可减少查询次数，但注意查询时不能直接在sql中使用MySql的函数如now(),curdate()。

// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

 

2. 选用适用的数据库引擎

如果数据量少且查询多用MyISAM，如果数据量大且更新多、需要事务支持和故障恢复，用InnoDB。

 

3. 使用explain分析sql慢查询语句

在复杂的查询语句前+explain，来查看sql查询语句如何进一步优化。

EXPLAIN SELECT * FROM city

分析结果的含义：
1）table：表名；
2）type：连接的类型，(ALL/Range/Ref)。其中ref是最理想的；
3）possible_keys：查询可以利用的索引名；
4）key：实际使用的索引；
5）key_len：索引中被使用部分的长度（字节）；
6）ref：显示列名字或者"const"（不明白什么意思）；
7）rows：显示MySQL认为在找到正确结果之前必须扫描的行数；
8）extra：MySQL的建议；

 

4. 当只要一行数据时使用LIMIT 1

当知道要查询的数据只有一行时，比如根据ID查询产品，根据名字查询人

// 没有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
    // ...
}
 
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

 

5. 为经常查询的字段建立索引

比如在产品表中经常查询product_name字段，就给这个字段建立一个索引

select (*) from product where product_name like 'a_%'

 

6. 在join表的时候使用相同类型的列，并且在列上建索引

在join查询的语句中，两个join的列的数据类型应该一致，并且建索引以提高查询速度

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

 

7. 避免select *

从数据库中读出越多的数据，查询越慢，并且如果应用服务器和数据库服务器是两台服务器的话，增加网络数据传输的负载。

所以，应该需要什么就查询什么，按需查询。

// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
 
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

 

8. 永远为每张表设置一个ID

为每张表设置一个ID当主键，而且最好类型是INT型。

推荐使用unsigned，设置为自动增加aotu_increment.

如果使用varchar类型来当主键会造成性能下降。

 

9. 使用enum而不是varchar

enum是非常快和紧凑的，实际上保存的是tinyint，外表上显示为字符串。

像“性别”、“状态”等，这些字段的取值是有限且固定的，应用使用enum。

 

10. 从 procedure analyse()取得建议

 

11. 尽可能的使用not null

尽量将列定义为not null，这样可使数据的出来更快，所需的空间更少，而且在查询时，MySQL不需要检查是否存在特例，即null值，从而优化查询；

 

12. 使用preparedStatement 预编译语句

 

13. 字段为固定长度的表会更快

a. 使用char替代varchar，固定长度的数据处理比变长的快些；

给每个字段设置固定的长度，将被认为是static和fixed-length的，这样能提高查询性能，因为MySql能搜寻得快一些。定长的表更容易被缓存，缺点是浪费一些的空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

 

14. 垂直切割表

是把数据库的表一张变成数张，可以降低表的复杂度和字段的数目，从而达到优化的目的。

 

15. varchar类型上不要建索引

varchar列上建索引影响查询性能，当需要大量查询varchar列时，可以建全文索引(full text)在varchar列上，但表的类型需要是MyIsAM。

 

16. 使用procedure analyse()优化表的数据类型

SELECT * FROM city PROCEDURE ANALYSE(16,256)

 

17、使用tinyint(1)代表boolean值

因为boolean值在数据库中会自动转换为tinyint(1)类型。

 

18、调整硬件
1）在机器上装更多的内存；
2）增加更快的硬盘以减少I/O等待时间；
寻道时间是决定性能的主要因素，逐字地移动磁头是最慢的，一旦磁头定位，从磁道读则很快；
3）在不同的物理硬盘设备上重新分配磁盘活动；
如果可能，应将最繁忙的数据库存放在不同的物理设备上，这跟使用同一物理设备的不同分区是不同的，因为它们将争用相同的物理资源（磁头）。

 

19.更新SQL语句中，使用索引，会提高更新速度

 

20. 使用 show processlist

查看正在执行的sql情况

 

21. 尽量使用数值型字段

若只有数值信息的字段，不要设计成字符类型，因为字符类型引擎会逐个比较字符，数值则只比较一次。

 

22. 使用count(*) 而不是count(1)或count(column name)

count(*)是整个结果集有多少记录，count(column name)是整个结果集中有多少column不为空的记录。