为了帮助 MySQL 更好地优化查询，请在表加载数据后使用 ANALYZE TABLE 或运行 myisamchk --analyze。这会更新每个索引部分的值，以指示具有相同值的平均行数。（对于唯一索引，这始终为 1。）MySQL 使用此信息在基于非常量表达式关联两个表时决定选择哪个索引。你可以使用 SHOW INDEX FROM tbl_name 检查表分析结果，并查看 Cardinality 值。myisamchk --description --verbose 显示索引分布信息。To help MySQL better optimize queries, use ANALYZE TABLE or run myisamchk --analyze on a table after it has been loaded with data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1.) MySQL uses this to decide which index to choose when you join two tables based on a nonconstant expression. You can check the result from the table analysis by using SHOW INDEX FROM tbl_name and examining the Cardinality value. myisamchk --description --verbose shows index distribution information.

要根据索引对索引和数据进行排序，请使用 myisamchk --sort-index --sort-records=1（假设您想按索引 1 排序）。如果您希望根据索引顺序读取所有行，并且该索引是唯一的，这是加快查询速度的好方法。第一次对大表进行这种排序可能需要很长时间。To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time.

尽量避免对频繁更新的 MyISAM 表执行复杂的 SELECT 查询，以避免因读写冲突导致的表锁定问题。Try to avoid complex SELECT queries on MyISAM tables that are updated frequently, to avoid problems with table locking that occur due to contention between readers and writers.

MyISAM 支持并发插入：如果表的数据文件中间没有空闲块，你可以在其他线程正在读取表的同时 INSERT 新行。如果能够做到这一点很重要，可以考虑以避免删除行的方式使用该表。另一种方法是在删除大量行之后运行 OPTIMIZE TABLE 来对表进行碎片整理。通过设置 concurrent_insert 变量可以改变此行为。你可以强制将新行追加到表尾（从而允许并发插入），即使在有删除行的表中也是如此。参见 第 10.11.3 节，“并发插入”。MyISAM supports concurrent inserts: If a table has no free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table. If it is important to be able to do this, consider using the table in ways that avoid deleting rows. Another possibility is to run OPTIMIZE TABLE to defragment the table after you have deleted a lot of rows from it. This behavior is altered by setting the concurrent_insert variable. You can force new rows to be appended (and therefore permit concurrent inserts), even in tables that have deleted rows. See Section 10.11.3, “Concurrent Inserts”.

对于经常变化的MyISAM表，尽量避免使用所有可变长度列（VARCHAR、BLOB 和 TEXT）。如果表中包含即使只有一个可变长度列，也会使用动态行格式。参见 第 18 章，替代存储引擎。For MyISAM tables that change frequently, try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column. See Chapter 18, Alternative Storage Engines.

通常，仅仅因为行变大而将表拆分成不同的表是没有用的。在访问一行时，最大的性能损失是寻找该行第一个字节所需的磁盘寻道时间。找到数据后，大多数现代磁盘能够足够快速地读取整行，以满足大多数应用的需求。唯一会显著受益于拆分表的情况是，如果它是使用动态行格式的MyISAM表，而你可以将其改为固定行大小，或者如果你经常需要扫描表但不需要大部分列。请参见第 18 章，替代存储引擎。It is normally not useful to split a table into different tables just because the rows become large. In accessing a row, the biggest performance hit is the disk seek needed to find the first byte of the row. After finding the data, most modern disks can read the entire row fast enough for most applications. The only cases where splitting up a table makes an appreciable difference is if it is a MyISAM table using dynamic row format that you can change to a fixed row size, or if you very often need to scan the table but do not need most of the columns. See Chapter 18, Alternative Storage Engines.

如果您通常按 ALTER TABLE ... ORDER BY expr1, expr2, ... 顺序检索行，请使用 expr1, expr2, ...。在对表进行大规模更改后使用此选项，可能能够获得更高的性能。Use ALTER TABLE ... ORDER BY expr1, expr2, ... if you usually retrieve rows in expr1, expr2, ... order. By using this option after extensive changes to the table, you may be able to get higher performance.

如果你经常需要基于大量行的信息计算诸如计数之类的结果，可能更适合引入一个新表并实时更新计数器。下面这种形式的更新速度非常快：If you often need to calculate results such as counts based on information from a lot of rows, it may be preferable to introduce a new table and update the counter in real time. An update of the following form is very fast:

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

当你使用如 MyISAM 这样的 MySQL 存储引擎（仅有表级锁，支持多个读者和单个写者）时，这非常重要。这也能在大多数数据库系统中提供更好的性能，因为在这种情况下，行级锁管理器的工作量较少。This is very important when you use MySQL storage engines such as MyISAM that has only table-level locking (multiple readers with single writers). This also gives better performance with most database systems, because the row locking manager in this case has less to do.

定期使用 OPTIMIZE TABLE 来避免动态格式 MyISAM 表的碎片化。参见 第 18.2.3 节，“MyISAM 表存储格式”。Use OPTIMIZE TABLE periodically to avoid fragmentation with dynamic-format MyISAM tables. See Section 18.2.3, “MyISAM Table Storage Formats”.

使用 DELAY_KEY_WRITE=1 表选项声明一个 MyISAM 表会使索引更新更快，因为它们在表关闭之前不会写入磁盘。缺点是，如果在这样一个表打开时服务器发生故障，你必须通过设置 myisam_recover_options 系统变量运行服务器，或者在重启服务器之前运行 myisamchk 来确保表的安全性。（然而，即使在这种情况下，使用 DELAY_KEY_WRITE 也不应丢失任何数据，因为键信息总能从数据行中生成。）Declaring a MyISAM table with the DELAY_KEY_WRITE=1 table option makes index updates faster because they are not flushed to disk until the table is closed. The downside is that if something kills the server while such a table is open, you must ensure that the table is okay by running the server with the myisam_recover_options system variable set, or by running myisamchk before restarting the server. (However, even in this case, you should not lose anything by using DELAY_KEY_WRITE, because the key information can always be generated from the data rows.)

在 MyISAM 索引中，字符串会自动进行前后空格压缩。详见 第 15.1.15 节，“CREATE INDEX 语句”。Strings are automatically prefix- and end-space compressed in MyISAM indexes. See Section 15.1.15, “CREATE INDEX Statement”.

您可以通过在应用程序中缓存查询或答案，然后一起执行多个插入或更新来提高性能。在此操作期间锁定表可以确保索引缓存仅在所有更新之后刷新一次。You can increase performance by caching queries or answers in your application and then executing many inserts or updates together. Locking the table during this operation ensures that the index cache is only flushed once after all updates.