MySQL数据库优化（四）-性能调优

　　7  其他优化点子

网管u家bitscn.net

　　本章节列出了一些改善查询处理速度的其他点子：

• 　　使用永久连接到数据库，避免连接的开销。如果需要初始化很多连接，而又不能用永久连接，那么可以修改变量 thread_cache_size 的值，详情请看\"7.5.2 Tuning Server Parameters\"。
• 　　总是检查查询是否利用了表中已有的索引。在MySQL中，可以用 EXPLAIN 语句来分析。详情请看\"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)\"。
• 　　尽量不要在经常需要更新的 MyISAM 表上用太过复杂的 SELECT 语句，这是为了避免在读和写之间争夺锁。
• 　　在 MyISAM 表中，如果没有正在删除记录，则可以在其他查询正在读取数据的同时插入记录。如果这种情况十分重要，那么就要尽量在表没有删除记录时才使用表。另一个可能的办法就是在删除一大堆记录之后执行 OPTIMIZE TABLE 语句。
• 　　如果总是需要按照 expr1, expr2, ... 的顺序取得记录，那么请使用 ALTER TABLE ... ORDER BY expr1, expr2, ... 修改表。通过这种方法扩充修改表之后，就可能获得更高的性能表现。
• 　　在一些情况下，让一个字段类型是 ``hashed`` ，它基于其他字段信息。如果这个字段比较短而且基本上都是唯一值的话，那么就可能会比在几个字段上使用一个大索引来得更快，很简单的就能使用这样的额外字段，如下：

• 　　如果 MyISAM 表经常大量修改，那么要尽量避免修改所有的变长字段(VARCHAR, BLOB，TEXT)。尽管表中只有一个变长字段，它也会采用动态记录格式的。详情请看\"15 MySQL Storage Engines and Table Types\"。
• 　　通常情况下，当数据表记录变 ``大`` 之后，将表拆分成几个不同的表并没有多大用处。访问一条记录是最大的性能点在于磁盘搜索时找到记录的第一个字节上。只要找到记录的位置后，现在的大部分磁盘对于大部分的应用程序来说都能很快的读取到记录。将 MyISAM 表拆分成多个唯一有关系的情况是，数据表中动态格式的字段(见上)就可以被修改成固定大小的记录，或者需要频繁的扫描表，但是却不需要读取出大部分的字段。详情请看\"15 MySQL Storage Engines and Table Types\"。
• 　　如果需要频繁的对一个表做基于很多字段信息的统计信息的话，那么可能新建一个表来存储这些实时更新的统计结果会更好。类似下面的更新就会非常快了：

　　UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

网管网www.bitscn.com

• 　　如果只需要表级锁(多个读/一个写)，那么采用 MyISAM 存储引擎就非常重要了，例如 MyISAM 和 ISAM 表。这在很多的数据库中也会有不错的性能表现，因为行级锁管理程序在这种情况下也基本上没什么用。
• 　　如果需要从很大的日志表中搜集统计信息的话，可以用摘要表来代替扫描整个日志表。维护摘要表比保持 ``实时`` 的统计信息来得更快。当事情发生变化时(比如商业决策)，重新建里摘要表比修改运营中的应用程序快多了。
• 　　如果可能，最好是分类报告 ``实时`` 还是 ``统计`` 的，报告所需要的数据只需要来自摘要表，摘要表的信息则是周期的从实时数据中产生。
• 　　应该认识到一个优点就是字段有默认值。当要插入的值和默认值不一致时才需要明确指定。这就省去了MySQL需要来提高插入速度这步了。
• 　　在一些情况下，将数据组装存储在 BLOB 类型字段中更方便。那么在应用程序中就需要增加额外的命令来组装和拆开 BLOB 字段中的值，不过这么做在一些时候就可以节省很多存储开销。这在数据无需遵从 记录-和-字段 格式的表结构是很实用。
• 　　通常地，应该保存所有的冗余数据(在数据库原理中叫做\"第三范式\")。然而，为了能取得更高的效率复制一些信息或者创建摘要表也是划算的。
• 　　存储过程或者 UDFs(用户定义函数) 的方式在执行一些任务时可能性能更高。尽管如此，当数据库不支持这些特性时，还是有其他的替代方法可以达到目的，即使它们有点慢。
• 　　可以从查询缓存或应答中取得结果，然后将很多次的插入及更新操作放在一起做。如果数据库支持表锁(如MySQL和ORACLE)，那么这就可以确保索引缓存在所有的更新操作之后只需要刷新一次。
• 　　当不需要直到数据什么时候写入表中时，可以用 INSERT DELAYED。这就会提高速度，因为多条记录同时在一起做一次磁盘写入操作。
• 　　当想让 SELECT 语句的优先级比插入操作还高时，用 INSERT LOW_PRIORITY。
• 　　用 SELECT HIGH_PRIORITY 来使检索记录跳过队列，也就是说即使有其他客户端正要写入数据，也会先让 SELECT 执行完。
• 　　在一条 INSERT 语句中采用多重记录插入格式(很多数据库都支持)。
• 　　用 LOAD DATA INFILE 来导入大量数据，这比 INSERT 快。
• 　　用 AUTO_INCREMENT 字段来生成唯一值。
• 　　定期执行 OPTIMIZE TABLE 防止使用动态记录格式的 MyISAM 表产生碎片。
• 　　采用 HEAP 表，它可能会提高速度。详情请看\"15.1.3 MyISAM Table Storage Formats\"。
• 　　正常的WEB服务器配置中，图片文件最好以文件方式存储，只在数据库中保存文件的索引信息。这么做的原因是，通常情况下WEB服务器对于文件的缓存总是做的比数据库来得好，因此使用文件存储会让系统更容易变得更快。
• 　　对于频繁访问的不是很重要的数据，可以保存在内存表中，例如对那些web客户端不能保存cookies时用于保存最后一次显示的标题等信息。
• 　　在不同表中值相同的字段应该将它们声明为一样的类型。在 MySQL 3.23 之前，不这么做的话在表连接时就会比较慢。让字段名尽可能简单，例如，在一个叫做 customer 的表中，用 name 来代替 customer_name 作为字段名。为了让字段名在其他数据库系统中也能移植，应该保持在18个字符长度以内。
• 　　如果需要真正的高速，建议看看各种数据库服务器支持的底层数据存储接口之间的区别。例如，通过直接访问MySQL的 MyISAM 存储引擎，会比通过其他的SQL接口快2-5倍。这要求数据必须和应用程序在同一个服务器上，并且它通常只被一个进程访问(因为外部文件锁确实慢)。只用一个进程就可以消除在MySQL服务器上引入底层的 MyISAM 指令引发的问题了(这容易获得更高性能，如果需要的话)。由于数据库接口设计的比较细心，就很容易支持这种优化方式了。
• 　　如果使用数字型数据的话，在很多情况下想要访问数据库(使用在线连接)的信息会比采用文本文件来得快。由于数字型信息相比文本文件在数据库中存储的更加紧凑，因此访问时只需要更少的磁盘搜索。而且在应用程序中也可以节省代码，因为无需解析文本文件以找到对应的行和字段。
• 　　数据库复制对一些操作会有性能上的益处。可以将客户端从多个复制服务器上取得数据，这就能将负载分摊了。为了避免备份数据时会让主服务器变慢，还可以将备份放在从服务器上。详情请看\"6 Replication in MySQL\"。
• 　　定义 MyISAM 表时增加选项 DELAY_KEY_WRITE=1，这样的话就会另索引更新更快，因为只有等到数据表关闭了才会刷新磁盘。不过缺点是可能会在数据表还打开时服务器被杀死，可以使用参数 --myisam-recover 来保证数据的安全，或者在数据库重启前运行 myisamchk 命令(尽管如此，在这种情况下，使用 DELAY_KEY_WRITE 的话也不会丢失任何东西，因为索引总是可以从数据中重新生成)。