Mysql最佳实践
本文总结自：
- http://www.databasejournal.com/features/mysql/article.php/3918631/Top-10-MySQL-Best-Practices.htm
- http://code.tutsplus.com/tutorials/top-20-mysql-best-practices--net-7855
- http://aadant.com/blog/wp-content/uploads/2013/09/50-Tips-for-Boosting-MySQL-Performance-CON2655.pdf
实践：
- 针对Query Cache优化查询语句。开启MySQL Query Cache是最有效提升性能的方式。如：
// 不会使用cache，因为类似CURDATE, NOW(), RAND()等不确定的函数，MySQL会禁用Query Cache
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 会使用cache
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");- 通过EXPLAIN关键字观察MySQL如何执行查询语句，如：
- 当明确查询的结果为一条时使用LIMIT 1语句，如：
- 尽可能对查询字段进行索引(INDEX)。这也是提升查询性能最简单直接的方式。
- 使用索引字段进行表JOIN操作。
- 不要使用Order BY RAND():
// what NOT to do:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// much better:
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");- 避免使用SELECT *，减轻磁盘操作及网络传输。
- 总是有一个id字段。通常每个表都有一个id字段：PRIMARY KEY，AUTO_INCREMENT，UNSIGNED。
- 使用ENUM代替VARCHAR。对于一些字段只有几个特定值，可以选用ENUM类型。
- 听取PROCEDURE ANALYSE()的建议。通过该语句，可以根据查询结果作出一些分析，比如，若表中的id(INT)值比较小，就会建议使用MEDIUMINT，若某VARCHAR字段的值只有几种，则会建议使用ENUM。
- 能不用就不用NULL。NULL也会占用额外的空间，MySQL中这样描述NULL：
- 使用Prepared Statement。这不仅利于性能，而且还有安全(防止SQL注入)。虽然你会对Prepared Statement语句传入多个不同的值，但是MySQL只会解析一次SQL语句。
- 使用UNSIGNED INT存储IP地址，而不是VARCHAR或CHAR。前者只用4个字节，而且你可以通过INET_ATON()和INET_NTOA()来转换。
- 固定长度的表更快。只有表中没有包含任何VARCHAR, TEXT, BLOB类型的字段，表才是固定的。固定长度的表有利于MySQL更快地查询记录。
- 垂直分区。如将用户表及其用户信息表分开，将一些不变和易变的字段分开，有利于性能提升。
- 拆分大批量的DELETE和INSERT语句。大批量的添加删除操作会影响查询性能，可以进行拆分成小批量的操作，如：
while (1) {
mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
if (mysql_affected_rows() == 0) {
// done deleting
break;
}
// you can even pause a bit
usleep(50000);
}- 尽可能使用更小的数据类型。更小的列将使用更少的磁盘，这对数据库的性能提升也是很重要的。
- 选择正确的存储引擎。对应业务需求，选择最适合的存储引擎，如，InnoDB和MyISAM。
- 调整OS limits参数十分重要：
- 选择使用其他的malloc库。一些malloc库针对多核环境作了优化：
- 设置CPU亲和力(affinity)。通过taskset命令设置CPU亲和力：
- 选择正确的文件系统：
- Mount选项：
- 选择最好的I/O调度算法：
- 多个磁盘上负载均衡：
- 当单线程瓶颈，且对于低并发系统时，关掉Query Cache:
可通过以下语句查看：
SELECT
EVENT_NAME AS nm,
COUNT_STAR AS cnt,
sum_timer_wait,
CONCAT(ROUND(sum_timer_wait / 10000000000000, 2), ' s') AS sec
FROM
performance_schema.events_stages_summary_global_by_event_name
WHERE
COUNT_STAR > 0
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT
20;- 使用线程池。
- 配置表缓存：
- 线程缓存：
- 减少每个线程的内存占用：
- 设置sync_binlog=1:
- 设置表引擎为InnoDB：
- 使用较大的缓冲池innodb_buffer_pool_size:
- 减少缓冲池竞争:
- 使用更大的redo log，有利于写性能：
- 调整I/O能力：
- 配置InnoDB日志刷新：
- 开启innodb_file_per_table:
- 配置线程并发性：
- 降低事务隔离性：
- 合理设计表：
- 降低rows_examined:
- 降低rows_sent：
- 降低锁操作：
- 分析slow query log：
- 使用performance_schema，使用ps_helper：
- 调优备份线程：
- 避免磁盘上的临时表：
- 在应用中缓存数据：
- 避免长时间运行的事务：
- 关闭空闲的连接：
- 关掉prepared_statements：
- 配置Connector/J：
- 使用MySQL Enterprice Monitor进行监控。
- 备份数据库，使用MySQL Enterprice Backup进行备份：
- 优化表和数据文件：
不吝指正。
