时间：2021-07-01 10:21:17 帮助过：6人阅读
7.2.9 MySQL 如何优化 ORDER BY 在一些情况下，MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。 尽管 ORDER BY 不是和索引的顺序准确匹配，索引还是可以被用到，只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子
ORDER BY在一些情况下，MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。
尽管 ORDER BY 不是和索引的顺序准确匹配，索引还是可以被用到，只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了。下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分：
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
在另一些情况下，MySQL无法使用索引来满足 ORDER BY，尽管它会使用索引来找到记录来匹配 WHERE 子句。这些情况如下：
ORDER BY ：
SELECT * FROM t1 ORDER BY key1, key2;
ORDER BY：
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
ASC 和 DESC：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
ORDER BY 的不是同一个：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
ORDER BY 中的字段都不全是来自第一个非常数的表中（也就是说，在 EXPLAIN 分析的结果中的第一个表的连接类型不是 const）。
ORDER BY 和 GROUP BY 表达式。
HASH 和 HEAP 表就是这样。通过执行 EXPLAIN SELECT ... ORDER BY，就知道MySQL是否在查询中使用了索引。如果 Extra 字段的值是 Using filesort，则说明MySQL无法使用索引。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。
当必须对结果进行排序时，MySQL 4.1 以前它使用了以下 filesort 算法：
WHERE 分句的记录都会被略过。
sort_buffer_size 的值而定。
MERGEBUFF（7）个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。
MERGEBUFF2 (15)。
read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。这个逼近算法的一个问题是，数据库读取了2次记录：一次是估算 WHERE 分句时，第二次是排序时。尽管第一次都成功读取记录了（例如，做了一次全表扫描），第二次是随机的读取（索引键已经排好序了，但是记录并没有）。
在MySQL 4.1 及更新版本中，filesort 优化算法用于记录中不只包括索引键值和记录的位置，还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的 filesort 算法做法大致如下：
WHERE 分句的记录。
使用改进后的 filesort 算法相比原来的，‘元组’比‘对’需要占用更长的空间，它们很少正好适合放在排序缓冲中（缓冲的大小是由 sort_buffer_size 的值决定的）。因此，这就可能需要有更多的I/O操作，导致改进的算法更慢。为了避免使之变慢，这种优化方法只用于排序‘元组’中额外的字段的大小总和超过系统变量 max_length_for_sort_data 的情况（这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载）。
想要提高 ORDER BY 的速度，首先要看MySQL能否使用索引而非额外的排序过程。如果不能使用索引，可以试着遵循以下策略：
sort_buffer_size 的值。
read_rnd_buffer_size 的值。
tmpdir，让它指向一个有很多剩余空间的专用文件系统。如果使用MySQL 4.1或更新，这个选项允许有多个路径用循环的格式。各个路径之间在 Unix 上用冒号（':'）分隔开来，在 Windows，NetWare以及OS/2 上用分号（';'）。可以利用这个特性将负载平均分摊给几个目录。注意：这些路径必须是分布在不同物理磁盘上的目录，而非在同一个物理磁盘上的不同目录。GROUP BY col1, col2, ... 查询做排序，跟 ORDER BY col1, col2, ... 查询一样。如果显式地包含一个有同样字段列表的 ORDER BY 分句，MySQL优化它的时候并不会损失速度，因为排序总是会发生。如果一个查询中包括 GROUP BY，但是想要避免对结果排序的开销，可以通过使用 ORDER BY NULL 来取消排序。例如：
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
LIMITLIMIT row_count 但没使用 HAVINGLIMIT 来取得很少的一些记录， MySQL 有时会使用索引，但是更通常的情况是做一个全表扫描。
LIMIT row_count 和 ORDER BY 一起使用，则MySQL在找到 row_count 条记录后就会停止排序了，而非对整个表进行排序。
LIMIT row_count 和 DISTINCT 一起联合起来时，MySQL在找到 row_count 条唯一记录后就不再搜索了。
GROUP BY 可以通过按照顺序读取索引键来实现（或者在索引键上做排序）并且计算累计信息直到索引键改变了。在这种情况下，LIMIT row_count 不会计算任何非必须的 GROUP BY 值。
SQL_CALC_FOUND_ROWS。
LIMIT 0 总是返回一个空的结果集。这对于检查查询或者取得结果字段的类型非常有用。
LIMIT row_count 可以用来计算需要多少空间。EXPLAIN 的结果中 type 字段的值是 ALL。在以下几种条件下，MySQL就会做全表扫描：ON 或 WHERE 分句的索引字段。
WHERE Clauses"。
ANALYZE TABLE tbl_name 更新要扫描的表的索引键分布。详情请看"14.5.2.1 ANALYZE TABLE Syntax"。
FORCE INDEX 告诉MySQL，做全表扫描的话会比利用给定的索引更浪费资源。详情请看"14.1.7 SELECT Syntax"。
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
mysqld 时使用参数 --max-seeks-for-key=1000 或者执行 SET max_seeks_for_key=1000 来告诉优化程序，所有的索引都不会导致超过1000次的索引搜索。请查看章节"5.2.3 Server System Variables"。INSERT这里并没有考虑初始化时打开数据表的开销，因为每次运行查询只会做这么一次。
如果是 B-tree 索引的话，随着索引数量的增加，插入记录的速度以 log N 的比例下降。
可以使用以下几种方法来提高插入速度：
INSERT 语句附带有多个 VALUES 值。这种做法比使用单一值的 INSERT 语句快多了（在一些情况下比较快）。如果是往一个非空的数据表里增加记录，可以调整变量 bulk_insert_buffer_size 的值使之更快。详情请看"5.2.3 Server System Variables"。
INSERT DELAYED 语句也可以提高速度。详情请看"14.1.4 INSERT Syntax"。
MyISAM 而言，可以在 SELECT 语句正在运行时插入记录，只要这时候没有正在删除记录。
LOAD DATA INFILE。这通常是使用大量 INSERT 语句的20倍。详情请看"14.1.5 LOAD DATA INFILE Syntax"。
LOAD DATA INFILE 在数据表有大量索引的情况下运行的更快。步骤如下：CREATE TABLE 随便创建一个表。
FLUSH TABLES 语句或 mysqladmin flush-tables 命令。
myisamchk --keys-used=0 -rq /path/to/db/tbl_name 命令，删掉数据表的所有索引。
LOAD DATA INFILE，数据插入到表中，由于无需更新表索引，因此这将非常快。
myisampack 让数据表变得更小点。详情查看"15.1.3.3 Compressed Table Characteristics"。
myisamchk -r -q /path/to/db/tbl_name 重建索引。创建的索引树在写入磁盘前先保存在内存中，这省去了磁盘搜索，因此速度快多了。重建后的索引树分布非常均衡。FLUSH TABLES 语句或 mysqladmin flush-tables 命令。LOAD DATA INFILE 将数据插入一个空表时，也会做前接优化；主要的不同在于：运行 myisamchk 会分配更多的临时内存用于创建索引，而执行 LOAD DATA INFILE 命令则是让数据库服务器分配内存用于重建索引。从 MySQL 4.0 起，可以运行 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%。MySQL的 INSERT, UPDATE, DELETE 操作都非常快，不过在一个语句中如果有超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多个插入，最好是在每个循环（大约1000次）的前后加上 LOCK TABLES 和 UNLOCK TABLES，从而让其他进程也能访问数据表;这么做性能依然不错。INSERT 总是比 LOAD DATA INFILE 插入数据来得慢，因为二者的实现策略有着分明的不同。
MyISAM 表更快，在 LOAD DATA
INFILE 和 INSERT 时都可以增加系统变量 key_buffer_size 的值，详情请看"7.5.2 Tuning Server Parameters"。UPDATEUPDATE 语句的优化和 SELECT 一样，只不过它多了额外的写入开销。写入的开销取决于要更新的记录数以及索引数。如果索引没有发生变化，则就无需更新。
另一个提高更新速度的办法是推迟更新并且把很多次更新放在后面一起做。如果锁表了，那么同时做很多次更新比分别做更新来得快多了。
注意，如果是在 MyISAM 表中使用了动态的记录格式，那么记录被更新为更长之后就可能会被拆分。如果经常做这个，那么偶尔做一次 OPTIMIZE TABLE 就显得非常重要了。详情请看"14.5.2.5 OPTIMIZE TABLE Syntax"。
DELETE删除单个记录的时间和它的索引个数几乎成正比。想更快地删除记录，可以增加索引键的缓存。详情请看"7.5.2 Tuning Server Parameters"。
如果想要删除数据表的所有记录，请使用 TRUNCATE TABLE tbl_name 而不是 DELETE FROM tbl_name。详情请看"14.1.9 TRUNCATE Syntax"。
本章节列出了一些改善查询处理速度的其他点子：
thread_cache_size 的值，详情请看"7.5.2 Tuning Server Parameters"。 EXPLAIN 语句来分析。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。 MyISAM 表上用太过复杂的 SELECT 语句，这是为了避免在读和写之间争夺锁。 MyISAM 表中，如果没有正在删除记录，则可以在其他查询正在读取数据的同时插入记录。如果这种情况十分重要，那么就要尽量在表没有删除记录时才使用表。另一个可能的办法就是在删除一大堆记录之后执行 OPTIMIZE TABLE 语句。 expr1, expr2, ... 的顺序取得记录，那么请使用 ALTER TABLE ... ORDER BY expr1, expr2, ... 修改表。通过这种方法扩充修改表之后，就可能获得更高的性能表现。 SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(col1,col2))
AND col1='constant' AND col2='constant';
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;
如果只需要表级锁（多个读/一个写），那么采用 MyISAM 存储引擎就非常重要了，例如 MyISAM 和 ISAM 表。这在很多的数据库中也会有不错的性能表现，因为行级锁管理程序在这种情况下也基本上没什么用。
BLOB 类型字段中更方便。那么在应用程序中就需要增加额外的命令来组装和拆开 BLOB 字段中的值，不过这么做在一些时候就可以节省很多存储开销。这在数据无需遵从 记录-和-字段 格式的表结构是很实用。 UDFs（用户定义函数） 的方式在执行一些任务时可能性能更高。尽管如此，当数据库不支持这些特性时，还是有其他的替代方法可以达到目的，即使它们有点慢。 INSERT DELAYED。这就会提高速度，因为多条记录同时在一起做一次磁盘写入操作。SELECT 语句的优先级比插入操作还高时，用 INSERT LOW_PRIORITY。SELECT HIGH_PRIORITY 来使检索记录跳过队列，也就是说即使有其他客户端正要写入数据，也会先让 SELECT 执行完。INSERT 语句中采用多重记录插入格式（很多数据库都支持）。LOAD DATA INFILE 来导入大量数据，这比 INSERT 快。AUTO_INCREMENT 字段来生成唯一值。OPTIMIZE TABLE 防止使用动态记录格式的 MyISAM 表产生碎片。详情请看"15.1.3 MyISAM Table Storage Formats"。HEAP 表，它可能会提高速度。详情请看"15.1.3 MyISAM Table Storage Formats"。customer 的表中，用 name 来代替 customer_name 作为字段名。为了让字段名在其他数据库系统中也能移植，应该保持在18个字符长度以内。MyISAM 存储引擎，会比通过其他的SQL接口快2-5倍。这要求数据必须和应用程序在同一个服务器上，并且它通常只被一个进程访问（因为外部文件锁确实慢）。只用一个进程就可以消除在MySQL服务器上引入底层的 MyISAM 指令引发的问题了（这容易获得更高性能，如果需要的话）。由于数据库接口设计的比较细心，就很容易支持这种优化方式了。MyISAM 表时增加选项 DELAY_KEY_WRITE=1，这样的话就会另索引更新更快，因为只有等到数据表关闭了才会刷新磁盘。不过缺点是可能会在数据表还打开时服务器被杀死，可以使用参数 --myisam-recover 来保证数据的安全，或者在数据库重启前运行 myisamchk 命令（尽管如此，在这种情况下，使用 DELAY_KEY_WRITE 的话也不会丢失任何东西，因为索引总是可以从数据中重新生成）。
‹ MySQL 优化（三）向上MySQL 优化（五） ›