MySQL 5.1参考手册 :: 7. 优化(4)

7.2.13.2. 紧凑索引扫描

紧凑式索引扫描可以为索引扫描或一个范围索引扫描，取决于查询条件。

如果不满足松散索引扫描条件，GROUP BY查询仍然可以不用创建临时表。如果WHERE子句中有范围条件，该方法只读取满足这些条件的关键字。否则，进行索引扫描。该方法读取由WHERE子句定义的每个范围的所有关键字，或没有范围条件式扫描整个索引，我们将它定义为紧凑式索引扫描。请注意对于紧凑式索引扫描，只有找到了满足范围条件的所有关键字后才进行组合操作。

要想让该方法工作，对于引用GROUP BY关键字元素的前面、中间关键字元素的查询中的所有列，有一个常量等式条件即足够了。等式条件中的常量填充了搜索关键字中的“差距”，可以形成完整的索引前缀。这些索引前缀可以用于索引查找。如果需要排序GROUP BY结果，并且能够形成索引前缀的搜索关键字，MySQL还可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

上述的第一种方法不适合下面的查询，但第2种索引访问方法可以工作(假定我们已经提及了表t1的索引idx)：

·         GROUP BY中有一个差距，但已经由条件c2 = 'a'覆盖。

     SELECT c1，c2，c3 FROM t1 WHERE c2 = 'a' GROUP BY c1，c3;

·         GROUP BY不以关键字的第1个元素开始，但是有一个条件提供该元素的常量：

     SELECT c1，c2，c3 FROM t1 WHERE c1 = 'a' GROUP BY c2，c3;

7.2.14. MySQL如何优化LIMIT

在一些情况中，当你使用LIMIT row_count而不使用HAVING时，MySQL将以不同方式处理查询。

·         如果你用LIMIT只选择一些行，当MySQL选择做完整的表扫描时，它将在一些情况下使用索引。

·         如果你使用LIMIT row_count与ORDER BY，MySQL一旦找到了排序结果的第一个row_count行，将结束排序而不是排序整个表。如果使用索引，将很快。如果必须进行文件排序（filesort），必须选择所有匹配查询没有LIMIT子句的行，并且在确定已经找到第1个row_count行前，必须对它们的大部分进行排序。在任何一种情况下，一旦找到了行，则不需要再排序结果的其它部分，并且MySQL不再进行排序。

·         当结合LIMIT row_count和DISTINCT时，MySQL一旦找到row_count个唯一的行，它将停止。

·         在一些情况下，GROUP BY能通过顺序读取键(或在键上做排序)来解决，然后计算摘要直到关键字的值改变。在这种情况下，LIMIT row_count将不计算任何不必要的GROUP BY值。

·         只要MySQL已经发送了需要的行数到客户，它将放弃查询，除非你正使用SQL_CALC_FOUND_ROWS。

·         LIMIT 0将总是快速返回一个空集合。这对检查查询的有效性是有用的。当使用MySQL API时，它也可以用来得到结果列的列类型。(该技巧在MySQL Monitor中不工作，只显示Empty set；应使用SHOW COLUMNS或DESCRIBE）。

·         当服务器使用临时表来进行查询时，使用LIMIT row_count子句来计算需要多少空间。

7.2.15. 如何避免表扫描

EXPLAIN的输出显示了当MySQL使用表扫描来解决查询时使用的所有类型列。这通常在如下条件下发生：

·         表很小，扫描表比查找关键字速度快。这对于少于10行并且行较短的表比较普遍。

·         在ON或WHERE子句中没有适用的索引列的约束。

·         正用常量值比较索引列，并且MySQL已经计算到(基于索引树)常数覆盖了表的很大部分并且表扫描将会比较快。参见7.2.4节，“MySQL怎样优化WHERE子句”。

·         你正通过另一个列使用一个低的集的势的关键字(许多行匹配关键字)。在这种情况下，MySQL假设通过使用关键字它可能会进行许多关键字查找，表扫描将会更快。

对于小表，表扫描通常合适。对于大表，尝试下面的技巧以避免优化器错选了表扫描：

·         使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。参见13.5.2.1节，“ANALYZE TABLE语法”。

·         对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。参见13.2.7节，“SELECT语法”。

·                SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

·                    WHERE t1.col_name=t2.col_name；

·         用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。参见5.3.3节，“服务器系统变量”。

7.2.16. INSERT语句的速度

插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：

• 连接：(3)
• 发送查询给服务器：(2)
• 分析查询：(2)
• 插入记录：（1x记录大小）
• 插入索引：（1x索引）
• 关闭：(1)

这不考虑打开表的初始开销，每个并发运行的查询打开。

表的大小以logN (B树)的速度减慢索引的插入。

加快插入的一些方法：

·         如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，可以调节bulk_insert_buffer_size变量，使数据插入更快。参见5.3.3节，“服务器系统变量”。

·         如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度。参见13.2.4节，“INSERT语法”。

·         用MyISAM，如果在表中没有删除的行，能在SELECT语句正在运行的同时插入行。

·         当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见13.2.5节，“LOAD DATA INFILE语法”。

·         当表有很多索引时，有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程：

1. 有选择地用CREATE TABLE创建表。
2. 执行FLUSH TABLES语句或命令mysqladmin flush-tables。
3. 使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。
4. 用LOAD DATA INFILE把数据插入到表中，因为不更新任何索引，因此很快。
5. 如果只想在以后读取表，使用myisampack压缩它。参见15.1.3.3节，“压缩表特性”。
6. 用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树，并且它更快，因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
7. 执行FLUSH TABLES语句或mysqladmin flush-tables命令。

请注意如果插入一个空MyISAM表，LOAD DATA INFILE也可以执行前面的优化；主要不同处是可以让myisamchk为创建索引分配更多的临时内存，比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。

也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq /path/to/db/tbl_name，使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name。使用这种方式，还可以跳过FLUSH TABLES。

·         锁定表可以加速用多个语句执行的INSERT操作：

• LOCK TABLES a WRITE;
• INSERT INTO a VALUES (1,23),(2,34),(4,33);
• INSERT INTO a VALUES (8,26),(6,29);
• UNLOCK TABLES;

这样性能会提高，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行，就不需要锁定。

对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。

锁定也将降低多连接测试的整体时间，尽管因为它们等候锁定最大等待时间将上升。例如：

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

如果不使用锁定，2、3和4将在1和5前完成。如果使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。

INSERT、UPDATE和DELETE操作在MySQL中是很快的，通过为在一行中多于大约5次连续不断地插入或更新的操作加锁，可以获得更好的整体性能。如果在一行中进行多次插入，可以执行LOCK TABLES，随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。

INSERT装载数据比LOAD DATA INFILE要慢得多，即使是使用上述的策略。

·         为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通过增加key_buffer_size系统变量来扩大 键高速缓冲区。参见7.5.2节，“调节服务器参数”。

 

7.2.17. UPDATE语句的速度

更新查询的优化同SELECT查询一样，需要额外的写开销。写速度依赖于更新的数据大小和更新的索引的数量。没有更改的索引不被更新。

使更改更快的另一个方法是推迟更改然后在一行内进行多次更新。如果锁定表，同时做多个更新比一次做一个快得多。

请注意对使用动态记录格式的MyISAM表，更新一个较长总长的记录可能会切分记录。如果经常这样该，偶尔使用OPTIMIZE TABLE很重要。参见13.5.2.5节，“OPTIMIZE TABLE语法”。

7.2.18. DELETE语句的速度

删除一个记录的时间与索引数量确切成正比。为了更快速地删除记录，可以增加键高速缓冲的大小。参见7.5.2节，“调节服务器参数”。

如果想要删除一个表的所有行，使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name。参见13.2.9节，“TRUNCATE语法”。

7.2.19. 其它优化技巧

该节列出了提高查询速度的各种技巧：

·         使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接，可能要更改thread_cache_size变量的值。参见7.5.2节，“调节服务器参数”。

·         总是检查所有查询确实使用已经在表中创建了的索引。在MySQL中，可以用EXPLAIN命令做到。参见7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。

·         尝试避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题。

·         对于没有删除的行的MyISAM表，可以在另一个查询正从表中读取的同时在末尾插入行。如果这很重要，应考虑按照避免删除行的方式使用表。另一个可能性是在删除大量行后运行OPTIMIZE TABLE。参见15.1节，“MyISAM存储引擎”。

·         要修复任何ARCHIVE表可以发生的压缩问题，可以执行OPTIMIZE TABLE。参见15.8节，“ARCHIVE存储引擎”。

·         如果你主要按expr1，expr2，...顺序检索行，使用ALTER TABLE ... ORDER BY expr1, expr2, ...。对表大量更改后使用该选项，可以获得更好的性能。

·         在一些情况下，使得基于来自其它表的列的信息引入一个“ 哈希”的列有意义。如果该列较短并且有合理的唯一值，它可以比在许多列上的一个大索引快些。在MySQL中，很容易使用这个额外列：

·                SELECT * FROM tbl_name

·                     WHERE hash_col=MD5(CONCAT(col1,col2))

·                     AND col1='constant' AND col2='constant';

·         对于频繁更改的MyISAM表，应试图避免所有变长列(VARCHAR、BLOB和TEXT)。如果表包括单一的变长列则使用动态记录格式。参见第15章：存储引擎和表类型。

·         只是因为行太大，将一张表分割为不同的表一般没有什么用处。为了访问行，最大的性能冲击是磁盘搜索以找到行的第一个字节。在找到数据后，大多数新型磁盘对大多数应用程序来说足够快，能读入整个行。确实有必要分割的唯一情形是如果它是使用动态记录格式使之变为固定的记录大小的MyISAM表(见上述)，或如果你需要很频繁地扫描表而不需要大多数列。参见第15章：存储引擎和表类型。

·         如果你需要很经常地计算结果，例如基于来自很多行的信息的计数，引入一个新表并实时更新计数器可能更好一些。下面形式的更新会更快一些：

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

当你使用象MyISAM那样的只有表级锁定的MySQL存储引擎(多重读/单个写)时，这确实很重要。这也给大多数数据库较好的性能，因为行锁定管理器在这种情况下有较少的事情要做。

·         如果你需要从大的记录文件表中收集统计信息，使用总结性的表而不是扫描整个表。维护总结应该比尝试做“实时”统计要快些。当有变化时从日志重新生成新的总结表比改变运行的应用(取决于业务决策)要快得多。

• 如果可能，应该将报告分类为“实时”或“统计”，这里统计报告所需的数据仅仅基于从实际数据定期产生的总结表中产生。
• 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。
• 在一些情况下，包装并存储数据到一个BLOB列中是很方便的。在这种情况下，必须在你的应用中增加额外的代码来打包/解包信息，但是这种方法可以在某些阶段节省很多访问。当有不符合行和列表结构的数据时，这很实用。
• 在一般情况下，应该尝试以非冗余方式(查看数据库理论中的第三正则形式)保存数据，但是为了获得更快的速度，可以复制信息或创建总结表。
• 存储过程或UDF(用户定义函数)可能是获得更好性能的一个好方法，详细信息参见第20章：存储程序和函数和27.2节，“为MySQL添加新函数”。
• 总是能通过在应用程序中缓存查询/答案并尝试同时执行很多插入/更新来获得一些好处。如果数据库支持锁定表(象MySQL和Oracle)，这应该有助于确保索引缓存只在所有更新后刷新一次。还可以利用MySQL的查询缓存来获得类似的结果；参见5.13节，“MySQL查询高速缓冲”。
• 当不需要知道何时写入数据时，使用INSERT DELAYED。这样可以加快处理，因为很多记录可以通过一次磁盘写入被写入。
• 当你想要让选择显得更重要时，使用INSERT /*! LOW_PRIORITY */。
• 使用INSERT LOW_PRIORITY来取得插入队列的检索，也就是即使有另一个客户等待写入也要执行SELECT。
• 使用多行INSERT语句通过一个SQL命令来存储很多行(许多SQL服务器支持它，包括MySQL)。
• 使用LOAD DATA INFILE装载较大数量的数据。这比使用INSERT要快得多。
• 使用AUTO_INCREMENT列构成唯一值。
• 当MyISAM使用动态表格式时，偶尔使用OPTIMIZE TABLE可以避免碎片。参见15.1.3节，“MyISAM表的存储格式”。
• 可能时使用MEMORY表以得到更快的速度。参见15.4节，“MEMORY (HEAP)存储引擎”。
• 在Web服务器中，图象和其它二进制资产应该作为文件存储。也就是仅在数据库中存储的本文件的引用而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多，因此使用文件一般要快得多。
• 对经常访问的不重要数据(如为没有在Web 浏览器中启用cookie的用户最后显示的标语的相关信息)使用内存表。在许多Web应用程序环境中也可以使用用户会话来处理可变状态数据。
• 在不同表中具有相同信息的列应该被声明为相同的并有相同的名字。尝试使名字简单化。例如，在customer表中使用name而不是customer_name。为了使名字能移植到其它SQL服务器，应该使名字短于18个字符。
• 如果确实需要很高的速度，应该研究一下不同SQL服务器支持的数据存储的低层接口！例如直接访问MySQL MyISAM存储引擎，比起使用SQL接口，速度可以提高2-5倍。为了能实现，数据必须与应用程序在同一台服务器上，并且通常只应该被一个进程访问(因为外部文件锁定确实很慢)。通过在MySQL服务器中引进低层MyISAM命令能消除以上问题(如果需要，这可能是获得更好性能的一个简单的方法)。通过精心设计数据库接口，应该能相当容易地支持这类优化。
• 如果正使用数字数据，在许多情况下，从一个数据库访问信息(使用实时连接)比访问一个文本文件快些。这是因为数据库中的信息比文本文件更紧凑，因此这将涉及更少的磁盘访问。还可以在应用程序中节省代码，因为不须分析文本文件来找出行和列的边界。

·         通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份时主服务器变慢，可以使用一个从服务器来备份。参见第6章：MySQL中的复制。

·         用DELAY_KEY_WRITE=1选项声明MyISAM表可以使索引更新更快，因为在表关闭之前它们不刷新到硬盘上。不利之处是当表打开时如果杀掉服务器，应确保用--myisam-recover选项运行服务器保证没有问题，或者在重启服务器之前运行myisamchk。(然而，即使在这种情况下，应通过使用DELAY_KEY_WRITE保证不丢失数据，因为关键字信息总是可以从数据行产生）。

 

7.3. 锁定事宜

7.3.1. 锁定方法

7.3.2. 表锁定事宜

7.3.1. 锁定方法

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。

在许多情况下，可以根据培训猜测应用程序使用哪类锁定类型最好，但一般很难说出某个给出的锁类型就比另一个好。一切取决于应用程序，应用程序的不同部分可能需要不同的锁类型。

为了确定是否想要使用行级锁定的存储引擎，应看看应用程序做什么并且混合使用什么样的选择和更新语句。例如，大多数Web应用程序执行许多选择，而很少进行删除，只对关键字的值进行更新，并且只插入少量具体的表。基本MySQL MyISAM设置已经调节得很好。

在MySQL中对于使用表级锁定的存储引擎，表锁定时不会死锁的。这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

对WRITE，MySQL使用的表锁定方法原理如下：

• 如果在表上没有锁，在它上面放一个写锁。
• 否则，把锁定请求放在写锁定队列中。

对READ，MySQL使用的锁定方法原理如下：

• 如果在表上没有写锁定，把一个读锁定放在它上面。
• 否则，把锁请求放在读锁定队列中。

当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。

这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> SHOW STATUS LIKE 'Table%';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+

如果INSERT语句不冲突，可以自由为MyISAM表混合并行的INSERT和SELECT语句而不需要锁定。也就是说，你可以在其它客户正读取MyISAM表的时候插入行。如果数据文件中间不包含空闲块，不会发生冲突，因为在这种情况下，记录总是插入在数据文件的尾部。(从表的中部删除或更新的行可能导致空洞）。如果有空洞，当所有空洞填入新的数据时，并行的插入能够重新自动启用。

如果不能同时插入，为了在一个表中进行多次INSERT和SELECT操作，可以在临时表中插入行并且立即用临时表中的记录更新真正的表。

这可用下列代码做到：

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;

mysql> INSERT INTO real_table SELECT * FROM insert_table;

mysql> TRUNCATE TABLE insert_table;

mysql> UNLOCK TABLES;

InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定和BDB获得页锁定，而不是在事务启动时获得。

行级锁定的优点：

·         当在许多线程中访问不同的行时只存在少量锁定冲突。

·         回滚时只有少量的更改。

·         可以长时间锁定单一的行。

行级锁定的缺点：

·         比页级或表级锁定占用更多的内存。

·         当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

·         如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。

·         用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

在以下情况下，表锁定优先于页级或行级锁定：

·         表的大部分语句用于读取。

·         对严格的关键字进行读取和更新，你可以更新或删除可以用单一的读取的关键字来提取的一行：

·                UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

·                DELETE FROM tbl_name WHERE unique_key_col=key_value;

·         SELECT 结合并行的INSERT语句，并且只有很少的UPDATE或DELETE语句。

·         在整个表上有许多扫描或GROUP BY操作，没有任何写操作。

不同于行级或页级锁定的选项：

·         版本(例如，为并行的插入在MySQL中使用的技术)，其中可以一个写操作，同时有许多读取操作。这说明数据库或表支持数据依赖的不同视图，取决于访问何时开始。其它共同的术语是“时间跟踪”、“写复制”或者“按需复制”。

·         按需复制在许多情况下优先于页级或行级锁定。然而，在最坏的情况下，它可能比使用常规锁定使用更多的内存。

·         除了行级锁定外，你可以使用应用程序级锁定，例如在MySQL中使用GET_LOCK()和RELEASE_LOCK()。这些是建议性锁定，它们只能在运行良好的应用程序中工作。