MySQL 性能优化学习笔记
MySQL 优化参数 （my.ini文件中修改）
1. max_connections
最大连接数，默认为100。连接数越大，占用内存越多，因为MySQL会为每个连接提供缓冲区。
// 最大连接数
show variables like 'max_connections'
// 最大响应连接数
show status like 'max_used_connections'
理想状况：max_used_connections / max_connections = 85%
2. back_log
如果当前连接数达到了max_connections，新来的请求将会被存放在堆栈中，等待资源。
该堆栈的容量即为 back_log，若新来的请求超过了back_log，将不会被授予连接资源。
3. interactive_timeout
一个交互连接在被服务器关闭前等待的秒数。
默认值为28800，可修改为7200。
4. key_buffer_size
索引缓冲区大小，决定了索引处理的速度，即空间换时间。
只针对 MyISAM 表起作用。
默认值为8M，可优化为256M。
// 索引缓冲区大小
show variables like 'key_buffer_size'
// 有多少个索引读取请求
show global status like 'key_read_request'
// 有多少个索引读取请求没有在内存缓冲区中找到，需要去磁盘中找
show global status like 'key_reads'
理想状况：未命中缓冲区的概率key_reads / key_read_request 在1%比较好。
5. query_cache_size
查询缓冲区大小，对于同样的select查询语句，将直接从缓冲区中读取。
默认为32M。
6. table_cache
表缓冲区大小。
7. tmp_table_size
临时表大小，group by操作会用到。
8. 其他的一些buffer设置
- record_buffer_size
- read_rnd_buffer_size
- sort_buffer_size
- join_buffer_size
MySQL 性能优化实践
1. 使用查询缓存
当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中。
这样，后续的相同的查询就不用操作表而直接访问缓存结果了。
// 查询缓存不开启
$r = mysql_query("SELECT * FROM student WHERE signup_date = CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT * FROM student WHERE signup_date = '$today'");
像 CURDATE(), NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存。
因为这些函数的返回值是不定的。
2. 当只要一行数据时使用 LIMIT 1
MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
// 没有效率的：
$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) {
// ...
}
3. 一些操作：
- 使用Explain来分析Select查询语句
EXPLAIN SELECT * FROM products WHERE products_id = '123'
-
如果要清空表，不要使用 delete，使用 truncate table users
-
如果要导入大量数据，使用 load data infile
-
经常清理碎片：optimize table users
4. 索引
关于索引，参见另一篇文章 MySQL 索引学习笔记
5. 存储引擎
关于存储引擎，参见另一篇文章 MySQL 存储引擎学习笔记
6. 静态 (固定长度) 数据表
关于静态 (固定长度) 数据表，参见另一篇文章 MySQL 静态 (固定长度) 数据表 特性
7. 避免 SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。
8. 永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
就算 users 表有一个 Unique 字段，比如身份证号码，你也别让它成为主键。因为使用 VARCHAR 类型来当主键会使用得性能下降。
9. 使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。
如果你有一个字段，比如“性别”，“国家”，“民族”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。
- Compact data storage in situations where a column has a limited set of possible values. The strings you specify as input values are automatically encoded as numbers. 字符串自动转换为数字
- Readable queries and output. The numbers are translated back to the corresponding strings in query results.
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
每一个枚举值都有一个索引 Index，从 1 开始。空的枚举值的索引为 0。
10. 尽可能的使用 NOT NULL
除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。
不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。
NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.
11. 数据表的分割
关于数据表的分割，参见另一篇文章 数据表的分割 学习笔记
12. 拆分大的 DELETE 或 INSERT 语句
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
while (1) {
//每次只做1000条
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 没得可删了，退出！
break;
}
// 每次都要休息一会儿
usleep(50000);
}
13. 无缓冲的查询
正常的情况下，当你在当你在你的脚本中执行一个SQL语句的时候，你的程序会停在那里直到这个SQL语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。
关于这个事情，在PHP的文档中有一个非常不错的说明： mysql_unbuffered_query() 函数：
“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”
上面那句话翻译过来是说，mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。
