本节列出一些杂项技巧改善查询执行速度:
·使用持续连接(persistent connections )来连数据库以避免连接开销。如果你没法
使用持续连接而你又有很多的数据库连接,你可以改变thread_cache_size 这个变量的值。
·经常检查你所有的查询是否都使用了你建立的表里的索引。在MySQL里,你可以
用EXPLAIN 语句。
·避免对经常更新的MyISAM表进行复杂的SELECT查询,这样能够避免在读者和写者争夺时产生锁表的问题。
·使用没有删行的MyISAM表,你可以在表尾插入行而同时另一个查询可以从表里读,如果这对你很重要的话,你可以考虑用避免删除行的方法使用表,另一个可能性是在你删除了很多行以后执行OPTIMIZE TABLE
·如果你经常按expr1, expr2, ...的顺序检索表,那就使用
ALTER TABLE ... ORDER BY expr1, expr2, ... ,在对表进行了大量改变之后
使用这个选项,能够得到性能提升。
·在某些情况下,插入一列基于其他列信息的``hashed'' 列会比较有意义,如果这
列比较短并且相当唯一,它会比在其他很多列上作大的索引要快的多。在MySQL里,使用这个额外列是非常简单的:
SELECT * FROM tbl_name
WHERE hash_col=MD5(CONCAT(col1,col2))
AND col1='constant' AND col2='constant';
·对于改变较多的MyISAM 表,你应该避免使用所有的变长列(VARCHAR, BLOB,
和 TEXT). 即使只包含有一个变长列这个表也会使用动态记录格式
·仅仅因为一行太“大”了就把一个表分成不同的表一般来说是没用的。要访问一行,最大的性能点是磁盘寻道找到那一行的第一个字节,当找到数据以后,大部分流行的磁盘都可以很快的读出整行数据,真正有必要分开一个表的情况是:你使用的是可以变长的动态记录格式的MyISAM表,或者是你经常需要搜索表,但是这个表里大部分列都不需要。
·如果你经常需要计算结果,比如对很多行进行counts。可能最好是建一个新表,
用来实时更新行数。
UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
当你使用只有一个表级锁的MySQL数据引擎比如MyISAM 和ISAM时,这个就很重要,这个对很多数据库都有好的性能,因为行锁定管理就可以少作很多事情。
·如果你需要从大的日志表里收集状态,使用summary tables 而不要用整个日志表,维护summaries要比实时计算状态快多了。当记录发生变化时从日志里生成新的
summary tables 是非常快的。
·如果可能的话,你应该把报告分类为``live'' 和 ``statistical'',statistical
reports需要的数据只从summary tables 里产生,而summary tables 是周期性的
从实时的数据产生。
·利用列具有默认值的事实,当你需要插入的数据确实和默认值不同时,才真正使用insert。这可以减少MySQL需要作的分析以加快插入速度。
·在有些情况下,把数据存储成BLOB列是比较合适的,这时候,你必须在程序里增加一些额外的代码来打包和打开BLOB值里的信息。但是这可能会在一些阶段保存很多访问,如果你有不遵从行列表结构的数据话,这是实用的。
·正常情况下,你应该保证所有数据没有冗余(在数据库理论里叫做
"third normal form")。不过,不好害怕为了加快速度而作的复制信息和生成概要表
·存储过程和UDFs (user-defined functions)对一些任务来说都是增加性能的
好方法,不过如果你使用的数据库系统不支持这些能力,你应该总是能有别的方法
来完成同样的任务的,即使这种替代方法比较慢。
·你可以在你的程序里缓存一些查询或者结果然后同时执行多条insert和update,
如果你的数据库支持锁表,(像MySQL和Oracle),这能保证在所有更新之后才
把索引的缓存写入磁盘。
·如果你不需要知道你的数据什么时候被写入的,你可以使用INSERT DELAYED ,这会提高速度,因为这样可以在一次写盘时写入多条记录。
·如果你想让SELECT语句的优先级高于insert,使用INSERT LOW_PRIORITY
·使用SELECT HIGH_PRIORITY ,这样的话,即使已经有另一个客户端在等待写入,也会优先完成SELECT查询。
·使用多行INSERT语句来一次存储多行数据(很多数据库服务都支持)
·使用LOAD DATA INFILE 来导入大量数据,这比INSERT语句快很多
·使用AUTO_INCREMENT 来生成唯一列
·间或使用OPTIMIZE TABLE 来避免用动态表格式的MyISAM表时数据崩溃。
·为了高速度可以使用HEAP表,
·当使用普通的WEB server 配置时,图片应该保存为文件,而只在数据库里存放
他们的引用。这样作的主要原因是一般的WEB Server在缓存文件方面比缓存数据库
目录要快得多。所以如果用文件,你的系统很容易变快。
·把经常访问的非关键性的表放在内存表里,比如为那些在浏览器里没有启用
cookie的用户保存的最后访问的标题信息。
·在不同表里存放同样数据的列应该使用同样的数据类型声明。否则在MySQL 3.23之前你的join会很慢。列名尽量简单些,例如:在customer表里,用name作列名,而不要用customer_name,并且使你的列名可以移植到其他数据库上,使用少于18个字符的列名。
·如果你真的需要高速度,你应该看一下不同数据库服务支持的底层接口,比如,
直接访问MySQL的MyISAM表存储引擎要比你通过SQL接口快两到五倍。要作到这点,数据得和程序放在同一台机子上,而且一般同时只能有一个进程访问(因为外部文件锁很慢的),也有人可以通过引入MySQL的底层MyISAM命令来解决这个问题。(这可能是得到更好性能的一个简单方法),通过小心设计数据库接口,很容易支持这种优化
·如果使用数值数据,在多数情况下从数据库访问信息(使用活动的连接)要比从
文本文件访问要快,数据库里的信息要比文本文件存储得更紧密,所以访问他们会
需要比较少得磁盘读写,你可以省去你程序里的代码,因为你不需要分析你的文件
来得到行列分界。
·Replication(复制)对某些操作可以提供性能改善,你可以在Replication server
中分配客户检索以分担负载。要避免在数据备份时拖慢master,你可以在slave
服务器上作备份。
·声明MyISAM 表的时候使用DELAY_KEY_WRITE=1 选项能够使索引更新地比较快,因为只有当表关掉地时候才写盘。缺点是如果当表打开时服务器进程被杀掉,
你应该在启动服务时带上--myisam-recover参数以保证数据完好,或者在重启服务
之前运行myisamchk(然而,即使是这种情况,你用DELAY_KEY_WRITE应该不会丢任何东西,因为关键信息总能从数据行里生成)