MySQL -- MySQL 8.0 Reference Manual -- 8.2.1.16 ORDER BY Optimization()

  本篇文章为你整理了MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization()的详细内容,包含有 MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization,希望能帮助你了解 MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization。

   This section describes when MySQL can use an index to satisfy

   an ORDER BY clause, the

   filesort operation used when an index

   cannot be used, and execution plan information available from

   the optimizer about ORDER BY.

   An ORDER BY with and without

   LIMIT may return rows in different orders,

   as discussed in Section 8.2.1.19, “LIMIT Query Optimization”.

  Use of Indexes to Satisfy ORDER BY

  Use of filesort to Satisfy ORDER BY

  Influencing ORDER BY Optimization

  ORDER BY Execution Plan Information Available

  
In some cases, MySQL may use an index to satisfy an

   ORDER BY clause and avoid the extra

   sorting involved in performing a filesort

   operation.

   The index may also be used even if the ORDER

   BY does not match the index exactly, as long as

   all unused portions of the index and all extra

   ORDER BY columns are constants in the

   WHERE clause. If the index does not

   contain all columns accessed by the query, the index is used

   only if index access is cheaper than other access methods.

   Assuming that there is an index on

   (key_part1,

   key_part2), the

   following queries may use the index to resolve the

   ORDER BY part. Whether the optimizer

   actually does so depends on whether reading the index is

   more efficient than a table scan if columns not in the index

   must also be read.

   In this query, the index on

   (key_part1,

   key_part2) enables

   the optimizer to avoid sorting:

  

SELECT * FROM t1

 

   ORDER BY key_part1, key_part2;

 

   However, the query uses SELECT *,

   which may select more columns than

   key_part1 and

   key_part2. In that case,

   scanning an entire index and looking up table rows to

   find columns not in the index may be more expensive than

   scanning the table and sorting the results. If so, the

   optimizer probably does not use the index. If

   SELECT * selects only the index

   columns, the index is used and sorting avoided.

   If t1 is an InnoDB

   table, the table primary key is implicitly part of the

   index, and the index can be used to resolve the

   ORDER BY for this query:

  

SELECT pk, key_part1, key_part2 FROM t1

 

   ORDER BY key_part1, key_part2;

 

   In this query, key_part1 is

   constant, so all rows accessed through the index are in

   key_part2 order, and an index

   on (key_part1,

   key_part2) avoids

   sorting if the WHERE clause is

   selective enough to make an index range scan cheaper

   than a table scan:

  

SELECT * FROM t1

 

   WHERE key_part1 = constant

   ORDER BY key_part2;

 

   In the next two queries, whether the index is used is

   similar to the same queries without

   DESC shown previously:

  

SELECT * FROM t1

 

   ORDER BY key_part1 DESC, key_part2 DESC;

  SELECT * FROM t1

   WHERE key_part1 = constant

   ORDER BY key_part2 DESC;

 

   Two columns in an ORDER BY can sort

   in the same direction (both ASC, or

   both DESC) or in opposite directions

   (one ASC, one

   DESC). A condition for index use is

   that the index must have the same homogeneity, but need

   not have the same actual direction.

   If a query mixes ASC and

   DESC, the optimizer can use an index

   on the columns if the index also uses corresponding

   mixed ascending and descending columns:

  

SELECT * FROM t1

 

   ORDER BY key_part1 DESC, key_part2 ASC;

 

   The optimizer can use an index on

   (key_part1,

   key_part2) if

   key_part1 is descending and

   key_part2 is ascending. It

   can also use an index on those columns (with a backward

   scan) if key_part1 is

   ascending and key_part2 is

   descending. See Section 8.3.13, “Descending Indexes”.

   In the next two queries,

   key_part1 is compared to a

   constant. The index is used if the

   WHERE clause is selective enough to

   make an index range scan cheaper than a table scan:

  

SELECT * FROM t1

 

   WHERE key_part1 constant

   ORDER BY key_part1 ASC;

  SELECT * FROM t1

   WHERE key_part1 constant

   ORDER BY key_part1 DESC;

 

   In the next query, the ORDER BY does

   not name key_part1, but all

   rows selected have a constant

   key_part1 value, so the index

   can still be used:

  

SELECT * FROM t1

 

   WHERE key_part1 = constant1 AND key_part2 constant2

   ORDER BY key_part2;

 

   In some cases, MySQL cannot use indexes

   to resolve the ORDER BY, although it may

   still use indexes to find the rows that match the

   WHERE clause. Examples:

   The query uses ORDER BY on different

   indexes:

  

SELECT * FROM t1 ORDER BY key1, key2;

 

   The query uses ORDER BY on

   nonconsecutive parts of an index:

  

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

 

   The index used to fetch the rows differs from the one

   used in the ORDER BY:

  

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

 

   The query uses ORDER BY with an

   expression that includes terms other than the index

   column name:

  

SELECT * FROM t1 ORDER BY ABS(key);

 

  SELECT * FROM t1 ORDER BY -key;

 

   The query joins many tables, and the columns in the

   ORDER BY are not all from the first

   nonconstant table that is used to retrieve rows. (This

   is the first table in the

   EXPLAIN output that does

   not have a const join

   type.)

   The query has different ORDER BY and

   GROUP BY expressions.

   There is an index on only a prefix of a column named in

   the ORDER BY clause. In this case,

   the index cannot be used to fully resolve the sort

   order. For example, if only the first 10 bytes of a

   CHAR(20) column are

   indexed, the index cannot distinguish values past the

   10th byte and a filesort is needed.

   The index does not store rows in order. For example,

   this is true for a HASH index in a

   MEMORY table.

   Availability of an index for sorting may be affected by the

   use of column aliases. Suppose that the column

   t1.a is indexed. In this statement, the

   name of the column in the select list is

   a. It refers to t1.a,

   as does the reference to a in the

   ORDER BY, so the index on

   t1.a can be used:

  

SELECT a FROM t1 ORDER BY a;

 

   In this statement, the name of the column in the select list

   is also a, but it is the alias name. It

   refers to ABS(a), as does the reference

   to a in the ORDER BY,

   so the index on t1.a cannot be used:

  

SELECT ABS(a) AS a FROM t1 ORDER BY a;

 

   In the following statement, the ORDER BY

   refers to a name that is not the name of a column in the

   select list. But there is a column in t1

   named a, so the ORDER

   BY refers to t1.a and the index

   on t1.a can be used. (The resulting sort

   order may be completely different from the order for

   ABS(a), of course.)

  

SELECT ABS(a) AS b FROM t1 ORDER BY a;

 

   Previously (MySQL 5.7 and lower),

   GROUP BY sorted implicitly under certain

   conditions. In MySQL 8.0, that no longer

   occurs, so specifying ORDER BY NULL at

   the end to suppress implicit sorting (as was done

   previously) is no longer necessary. However, query results

   may differ from previous MySQL versions. To produce a given

   sort order, provide an ORDER BY clause.

  
 

   If an index cannot be used to satisfy an ORDER

   BY clause, MySQL performs a

   filesort operation that reads table rows

   and sorts them. A filesort constitutes an

   extra sorting phase in query execution.

   To obtain memory for filesort operations,

   as of MySQL 8.0.12, the optimizer allocates memory buffers

   incrementally as needed, up to the size indicated by the

   sort_buffer_size system

   variable, rather than allocating a fixed amount of

   sort_buffer_size bytes up

   front, as was done prior to MySQL 8.0.12. This enables users

   to set sort_buffer_size to

   larger values to speed up larger sorts, without concern for

   excessive memory use for small sorts. (This benefit may not

   occur for multiple concurrent sorts on Windows, which has a

   weak multithreaded malloc.)

   A filesort operation uses temporary disk

   files as necessary if the result set is too large to fit in

   memory. Some types of queries are particularly suited to

   completely in-memory filesort operations.

   For example, the optimizer can use

   filesort to efficiently handle in memory,

   without temporary files, the ORDER BY

   operation for queries (and subqueries) of the following

   form:

  

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

 

   Such queries are common in web applications that display

   only a few rows from a larger result set. Examples:

  

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

 

  SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

 

  
filesort is not used, try lowering the

   max_length_for_sort_data

   system variable to a value that is appropriate to trigger a

   filesort. (A symptom of setting the value

   of this variable too high is a combination of high disk

   activity and low CPU activity.) This technique applies only

   before MySQL 8.0.20. As of 8.0.20,

   max_length_for_sort_data is

   deprecated due to optimizer changes that make it obsolete

   and of no effect.

   To increase ORDER BY speed, check whether

   you can get MySQL to use indexes rather than an extra

   sorting phase. If this is not possible, try the following

   strategies:

   Increase the

   sort_buffer_size

   variable value. Ideally, the value should be large

   enough for the entire result set to fit in the sort

   buffer (to avoid writes to disk and merge passes).

   Take into account that the size of column values stored

   in the sort buffer is affected by the

   max_sort_length system

   variable value. For example, if tuples store values of

   long string columns and you increase the value of

   max_sort_length, the

   size of sort buffer tuples increases as well and may

   require you to increase

   sort_buffer_size.

   To monitor the number of merge passes (to merge

   temporary files), check the

   Sort_merge_passes

   status variable.

   Increase the

   read_rnd_buffer_size

   variable value so that more rows are read at a time.

   Change the tmpdir

   system variable to point to a dedicated file system with

   large amounts of free space. The variable value can list

   several paths that are used in round-robin fashion; you

   can use this feature to spread the load across several

   directories. Separate the paths by colon characters

   (:) on Unix and semicolon characters

   (;) on Windows. The paths should name

   directories in file systems located on different

   physical disks, not different

   partitions on the same disk.

  
ORDER BY Execution Plan Information Available

  
EXPLAIN

   (see Section 8.8.1, “Optimizing Queries with EXPLAIN”), you can check whether

   MySQL can use indexes to resolve an ORDER

   BY clause:

   If the Extra column of

   EXPLAIN output does not

   contain Using filesort, the index is

   used and a filesort is not performed.

   If the Extra column of

   EXPLAIN output contains

   Using filesort, the index is not used

   and a filesort is performed.

   In addition, if a filesort is performed,

   optimizer trace output includes a

   filesort_summary block. For example:

  

"filesort_summary": {

 

   "rows": 100,

   "examined_rows": 100,

   "number_of_tmp_files": 0,

   "peak_memory_used": 25192,

   "sort_mode": " sort_key, packed_additional_fields "

  }

 

   peak_memory_used indicates the maximum

   memory used at any one time during the sort. This is a value

   up to but not necessarily as large as the value of the

   sort_buffer_size system

   variable. Prior to MySQL 8.0.12, the output shows

   sort_buffer_size instead, indicating the

   value of sort_buffer_size.

   (Prior to MySQL 8.0.12, the optimizer always allocates

   sort_buffer_size bytes for

   the sort buffer. As of 8.0.12, the optimizer allocates

   sort-buffer memory incrementally, beginning with a small

   amount and adding more as necessary, up to

   sort_buffer_size bytes.)

   The sort_mode value provides information

   about the contents of tuples in the sort buffer:

   sort_key, rowid : This

   indicates that sort buffer tuples are pairs that contain

   the sort key value and row ID of the original table row.

   Tuples are sorted by sort key value and the row ID is

   used to read the row from the table.

   sort_key, additional_fields :

   This indicates that sort buffer tuples contain the sort

   key value and columns referenced by the query. Tuples

   are sorted by sort key value and column values are read

   directly from the tuple.

   sort_key,

   packed_additional_fields : Like the

   previous variant, but the additional columns are packed

   tightly together instead of using a fixed-length

   encoding.

   EXPLAIN does not distinguish

   whether the optimizer does or does not perform a

   filesort in memory. Use of an in-memory

   filesort can be seen in optimizer trace

   output. Look for

   filesort_priority_queue_optimization. For

   information about the optimizer trace, see

   MySQL Internals: Tracing the Optimizer.

  以上就是MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization()的详细内容,想要了解更多 MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization的内容,请持续关注盛行IT软件开发工作室。

郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

留言与评论(共有 条评论)
   
验证码: