Monty说MySQL的优化（三）

十一、维护

　　如果可能，偶尔运行一下OPTIMIZE table，这对大量更新的变长行非常重要。

　　偶尔用myisamchk -a更新一下表中的键码分布统计。记住在做之前关掉MySQL。

　　如果有碎片文件，可能值得将所有文件复制到另一个磁盘上，清除原来的磁盘并拷回文件。

　　如果遇到问题，用myisamchk或CHECK table检查表。

　　用mysqladmin -i10 precesslist extended-status监控MySQL的状态。

　　用MySQL GUI客户程序，你可以在不同的窗口内监控进程列表和状态。

　　使用mysqladmin debug获得有关锁定和性能的信息。

　　十二、优化SQL

　　扬SQL之长，其它事情交由应用去做。使用SQL服务器来做：

　　找出基于WHERE子句的行。

　　JOIN表

　　GROUP BY

　　ORDER BY

　　DISTINCT

　　不要使用SQL来做：

　　检验数据(如日期)

　　成为一只计算器

　　技巧：

　　明智地使用键码。

　　键码适合搜索，但不适合索引列的插入/更新。

　　保持数据为数据库第三范式，但不要担心冗余信息或这如果你需要更快的速度，创建总结表。

　　在大表上不做GROUP BY，相反创建大表的总结表并查询它。

　　UPDATE table set count=count+1 where key_column=constant非常快。

　　对于大表，或许最好偶尔生成总结表而不是一直保持总结表。

　　充分利用INSERT的默认值。

　　十三、不同SQL服务器的速度差别（以秒计）

　　通过键码读取2000000行： NT Linux

　　mysql 367 249

　　mysql_odbc 464 　

　　db2_odbc 1206 　

　　informix_odbc 121126 　

　　ms-sql_odbc 1634 　

　　oracle_odbc 20800 　

　　solid_odbc 877 　

　　sybase_odbc 17614 　

　　插入350768行： NT Linux

　　mysql 381 206

　　mysql_odbc 619 　

　　db2_odbc 3460 　

　　informix_odbc 2692 　

　　ms-sql_odbc 4012 　

　　oracle_odbc 11291 　

　　solid_odbc 1801 　

　　sybase_odbc 4802 　

　　在上述测试中，MySQL配置8M高速缓存运行，其他数据库以默认安装运行。

　　十四、重要的MySQL启动选项

　　back_log 如果需要大量新连接，修改它。

　　thread_cache_size 如果需要大量新连接，修改它。

　　key_buffer_size 索引页池，可以设成很大。

　　bdb_cache_size BDB表使用的记录和键吗高速缓存。

　　table_cache 如果有很多的表和并发连接，修改它。

　　delay_key_write 如果需要缓存所有键码写入，设置它。

　　log_slow_queries 找出需花大量时间的查询。

　　max_heap_table_size 用于GROUP BY

　　sort_buffer 用于ORDER BY和GROUP BY

　　myisam_sort_buffer_size 用于REPAIR TABLE

　　join_buffer_size 在进行无键吗的联结时使用。

　　十五、优化表

　　MySQL拥有一套丰富的类型。你应该对每一列尝试使用最有效的类型。

　　ANALYSE过程可以帮助你找到表的最优类型：SELECT * FROM table_name PROCEDURE ANALYSE()。

　　对于不保存NULL值的列使用NOT NULL，这对你想索引的列尤其重要。

　　将ISAM类型的表改为MyISAM。

　　如果可能，用固定的表格式创建表。

　　不要索引你不想用的东西。

　　利用MySQL能按一个索引的前缀进行查询的事实。如果你有索引INDEX(a,b)，你不需要在a上的索引。

　　不在长CHAR/VARCHAR列上创建索引，而只索引列的一个前缀以节省存储空间。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))

　　对每个表使用最有效的表格式。

　　在不同表中保存相同信息的列应该有同样的定义并具有相同的列名