How to optimize order by in MySQL 03/29 Update SLTechnology News&Howtos

How to optimize order by in MySQL

2025-03-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to optimize order by in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

A preface

This paper introduces the basic principle and optimization of order by. If you feel that you don't have a thorough understanding of the principle of order by, you can learn what kind of select + order by statements can use indexes and what can't be sorted by indexes.

Second analysis

2.1 introduction of official standards

The official statement on how the index can be utilized by the select order by statement is as follows:

"The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."

Just translate it.

Indexes can be used even if ORDER BY statements cannot exactly match (combine) index columns, as long as all unused index parts and all additional ORDER BY columns in the WHERE condition are listed as constants.

How to understand this sentence? We explain it through specific use cases.

2.2 preparation work

CREATE TABLE `tx` (

`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'record ID'

`shid` int (11) NOT NULL COMMENT 'store ID'

`gid`int (11) NOT NULL COMMENT 'item ID'

`type`tinyint (1) NOT NULL COMMENT 'payment method'

`price`int (10) NOT NULL COMMENT 'item price'

`comment`varchar (200) NOT NULL COMMENT 'remarks'

PRIMARY KEY (`id`)

UNIQUE KEY `uniq_shid_ gid` (`shid`, `gid`)

KEY `idx_ price` (`price`)

KEY `idx_ type` (`type`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

INSERT INTO `tx` (`shid`, `gid`, `type`, `price`, `comment`) VALUES (6, 2, 0, '399,' 2'), (6, 5, 0, '288,' 2'), (6, 11, 0,'10,'2')

(1, 1, 0,'10 minutes, 'sd')

(2, 55, 0, '210,' sa')

(2, 33, 1, '999,' a')

(3, 17, 0, '198,' b')

(3, 22, 1, '800,' e')

(4, 12, 0, '120,' f')

(4, 73, 0, '250,' d')

(5, 61, 0,'10','c')

(6, 1, 0, '210,' 2')

(7, 9, 1, '999,' 44')

(7, 2, 0, '198,' 45')

(8, 3, 1, '800 miles,' rt')

(9, 4, 0, '120,' pr')

(9, 6, 0, '250,' x')

(10, 8, 0,'10','w')

(12, 9, 0, '210,' w')

(12, 10, 1, '999,' Q')

(13, 11, 0, '198,')

(13, 12, 1, '800,')

(14, 13, 0, '120,')

(14, 19, 0, '250,')

CREATE TABLE `goods_ type` (

`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'ID'

`type`int NOT NULL COMMENT 'type'

`name`varchar (20) NOT NULL COMMENT 'name'

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

INSERT INTO `goods_ type` (`id`, `type`, `name`) VALUES

(1, 1,'hw phone')

(2, 0, 'xiaomi')

(3,1, 'apple')

2.3 example analysis that can use the index

There are seven examples in the official documentation that can be sorted using an index. If you use the explain/desc tool to see that Using filesort appears in the extra in the execution plan, sql is not using sort optimization.

Case one

Documentation: SELECT * FROM T1 ORDER BY key_part1,key_part2,...

Test [RW] 06:03:52 > desc select * from tx order by shid,gid

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Analysis:

Obviously, the above sql does not take advantage of index sorting. Type=ALL Extra=Using filesort, because there are no conditions for where words, the optimizer chooses full table scan and memory sorting.

Test [RW] 06:04:39 > desc select gid from tx order by shid,gid

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Test [RW] 06:04:47 > desc select shid,gid from tx order by shid,gid

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Test [RW] 06:04:54 > desc select id,shid,gid from tx order by shid,gid

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Analysis.

From type=index,extra=Using index, we can see that when the fields of select are included in the index, we can make use of the index sorting function to scan the overlay index.

With select *, overlay index scans cannot be used and full table scans are selected and sorted because the where statement has no specific conditions MySQL.

Case two

SELECT * FROM T1 WHERE key_part1 = constant ORDER BY key_part2

Use one part of the composite index to make an equivalent query and the other part as a sort field. To put it more strictly, the where condition uses the left prefix equivalent query of the composite index, and the remaining fields are used for order by sorting.

Test [RW] 06:05:41 > desc select * from tx where shid= 2 order by gid

+-- +

+-- +

| | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where |

+-- +

1 row in set (0.00 sec)

Test [RW] 11:30:13 > desc select * from tx where shid= 2 order by gid desc

+-- +

+-- +

| | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where |

+-- +

1 row in set (0.00 sec)

Analysis:

Where conditional sentences can be indexed based on shid and avoid extra sorting work by making use of the ordering of gid in (shid,gid). Based on this example, we explain that "indexes can be used even if the ORDER BY statement does not exactly match (combine) index columns, as long as all unused index parts of the WHERE condition and all additional ORDER BY columns are constant."

The order by gid of this statement does not exactly match the composite index (shid,gid). The where conditional shid uses the leftmost prefix of the composite index and is an equivalent constant query. For order by, shid is an extra field that does not appear in the order by clause but is part of the composite index. Such conditions can be sorted using an index.

Case three

SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 DESC

In fact, it is similar to case one, except that it chooses the reverse order. The sql cannot take advantage of the ordering of the index and needs to be sorted by the server layer.

Test [RW] 06:06:30 > desc select * from tx order by shid desc,gid desc

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

If you select an index field in select, you can use override index scanning and you can use the index for sorting.

Test [RW] 06:06:31 > desc select shid,gid from tx order by shid desc,gid desc

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Case four

SELECT * FROM T1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC

This example is similar to case 2, except that the order by sentence contains all the combined index columns.

Test [RW] 06:06:55 > desc select * from tx where shid=4 order by shid desc, gid desc

+-- +

+-- +

| | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where |

+-- +

1 row in set (0.00 sec)

Analysis:

Where shid=4 can use the index of shid to locate data records, and select * has fields that are not in the index, so the back table accesses data outside the combined index column, taking advantage of the order of the gid index to avoid sorting.

Case five

SELECT * FROM T1 WHERE key_part1 > constant ORDER BY key_part1 ASC

SELECT * FROM T1 WHERE key_part1

< constant ORDER BY key_part1 DESC; test [RW] 11:40:48 >

Desc select * from tx where shid > 5 order by shid desc

+-+-

+-+-

+-+-

1 row in set (0.00 sec)

Test [RW] 11:47:25 > desc select * from tx where shid > 13 order by shid desc

+-+-

+-+-

+-+-

1 row in set (0.00 sec)

Analysis.

The table has a total of 24 rows, with 16 rows greater than 5 and 2 rows greater than 13, causing the MySQL optimizer to choose a different execution plan. This test shows that it has something to do with the differentiation of shid.

Case 6

SELECT * FROM T1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2

Use the prefix index of the combined index to query the ref equivalent, other fields to query the range, and order by non-equivalent fields.

Test [RW] 06:10:41 > desc select * from tx where shid=6 and gid > 1 order by gid

+-+-

+-+-

+-+-

1 row in set (0.02 sec)

Analysis:

Using the index query of shid=6 to record the ICP feature of MySQL, there is no sorting operation. Why you use ICP remains to be confirmed.

2.4 Analysis that index sorting cannot be used

Case one

The order by statement uses several different indexes

SELECT * FROM T1 ORDER BY key1, key2

Test [RW] 09:44:03 > desc select * from tx order by price, type

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Because sql uses different index columns, there is a possibility of inconsistent order on storage, and MySQL chooses the sort operation.

Special case because all secondary indexes contain the primary key id, when the where field plus the order by field communicates with the complete index, the filesort's

Test [RW] 11:20:10 > desc select * from tx where type=1 order by id

+-- +

+-- +

| | 1 | SIMPLE | tx | ref | idx_type | idx_type | 1 | const | 6 | Using where |

+-- +

1 row in set (0.00 sec)

Case two

When the query condition uses a different index than order by and the value is constant, but the sort field is a discontiguous part of another federated index

SELECT * FROM T1 WHERE key2=constant ORDER BY key_part1, key_part3

Test [RW] 11:19:17 > desc select * from tx where type=1 order by gid

+-+-

+-+-

| | 1 | SIMPLE | tx | ref | idx_type | idx_type | 1 | const | 6 | Using where; Using filesort |

+-+-

1 row in set (0.00 sec)

Test [RW] 11:21:08 > desc select * from tx where type=1 order by shid

+-+-

+-+-

| | 1 | SIMPLE | tx | ref | idx_type | idx_type | 1 | const | 6 | Using where; Using filesort |

+-+-

1 row in set (0.00 sec)

Analysis.

Consistent with the case, key2's sequential statement key1 (key_part1) stores a different sort, and MySQL selects filesort.

Case three

The order by statement uses a different collation from the default of the combined index

SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 ASC

It is suggested in the official document that the use of mixed index collation will lead to additional sorting, which we can do when creating an index (key_part1 DESC, key_part2 ASC)

Case four

When the index used in the where condition is different from the order by index, it is similar to case 2.

SELECT * FROM T1 WHERE key2=constant ORDER BY key1

Test [RW] 11:19:44 > desc select * from tx where type=1 order by shid

+-+-

+-+-

| | 1 | SIMPLE | tx | ref | idx_type | idx_type | 1 | const | 6 | Using where; Using filesort |

+-+-

1 row in set (0.00 sec)

Test [RW] 11:20:07 > desc select * from tx where type=1 order by shid,gid

+-+-

+-+-

| | 1 | SIMPLE | tx | ref | idx_type | idx_type | 1 | const | 6 | Using where; Using filesort |

+-+-

1 row in set (0.00 sec)

Case five

The order by field uses an expression

SELECT * FROM T1 ORDER BY ABS (key)

SELECT * FROM T1 ORDER BY-key

Test [RW] 11:53:39 > desc select * from tx where shid=3 order by-shid

+-+-

+-+-

| | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where; Using filesort |

+-+-

1 row in set (0.00 sec)

Test [RW] 11:56:26 > desc select * from tx where shid=3 order by shid

+-- +

+-- +

| | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | NULL |

+-- +

Analysis.

Fields in order by use functions, and MySQL cannot make use of indexes just as functional indexes are used in where conditions.

Case 6

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.)

When the query statement is a multi-table join, and not all the columns in the ORDER BY come from the first non-constant table used to search rows. (this is the first table in the EXPLAIN output that does not use the const join type)

Test [RW] 12:32:43 > explain select shid,gid from tx a left join goods_type b on a.shid=b.id where a.shid=2 order by a.giddirection b.id

+-+-

+-+-

| | 1 | SIMPLE | a | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using index; Using temporary; Using filesort |

+- -+

2 rows in set (0.00 sec)

Test [RW] 12:32:44 > explain select shid,gid from tx a left join goods_type b on a.shid=b.id where a.shid=2 order by a.gid

+-+-

+-+-

| | 1 | SIMPLE | a | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where; Using index |

+-+-

2 rows in set (0.00 sec)

Analysis.

There are many kinds of indexes that cannot be utilized in the case of join, as long as the access to a does not meet the above-mentioned situation that index sorting can be used, it will lead to additional sorting actions. But when the where + order composite requires that the order by have columns that contain other tables, it results in additional sorting actions.

Case 7

The order by column contained in sql is inconsistent with the group by column

Test [RW] 11:26:54 > desc select * from tx group by shid order by gid

+-+-

+-+-

+-+-

1 row in set (0.00 sec)

Group by itself will sort the operation, we can display the note so that group by does not do additional sorting action.

Test [RW] 12:09:52 > desc select * from tx group by shid order by null

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Case 8

The index itself does not support sort storage such as hash indexes.

CREATE TABLE `hash_ test` (

`id`int (10) unsigned NOT NULL AUTO_INCREMENT

`name`varchar (20) NOT NULL COMMENT 'name'

PRIMARY KEY (`id`)

KEY `name` (`name`)

) ENGINE=MEMORY

INSERT INTO `hash_ test` (`id`, `name`) VALUES

(1, 'Zhang San')

(2,'Li Si')

Test [RW] 12:07:27 > explain select * from hash_test force index (name) order by name

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Test [RW] 12:07:48 > explain select * from hash_test order by name

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Test [RW] 12:07:53 > alter table hash_test ENGINE=innodb

Query OK, 2 rows affected (.45 sec)

Records: 2 Duplicates: 0 Warnings: 0

Test [RW] 12:08:33 > explain select * from hash_test order by name

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Analysis.

The hash index itself does not support sorting storage, so it can not make use of the sorting feature to convert the table into innodb to query again, avoiding filesort

Case 9

The index of order by uses partial strings such as key idx_name (name (2))

Test [RW] 12:08:37 > alter table hash_test drop key name, add key idx_name (name (2))

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Test [RW] 12:09:50 > explain select * from hash_test order by name

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Three platitudes of optimization strategies

In order to improve the speed of order by query, we can make use of the ordering of index to sort as much as possible. If we can't make use of the function of index sorting, we can only optimize the cache parameters related to order by.

1 increase the sort_buffer_size size, and it is recommended that the sort_buffer_size should be large enough to avoid disk sorting and merge sorting times.

(2) increase the size of read_rnd_buffer_size.

3 use the appropriate column size to store specific content, for example, for city fields varchar (20) can achieve better performance than varchar (200).

4 assign the tmpdir directory to the storage with high iops capability that has enough space on the os.

On how to optimize the order by in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.