Source: author: WenAnShi my.oschina.net/floor/blog/…

The introduction

A leisurely morning before the Spring Festival, small navigation sent me, a bag of nuts, he looked at me to eat the incense, slowly asked: “Brother Wen, mysql sort, what should pay attention to, is not the inverted row?”

As SOON as I heard the question he asked me, I felt a little nutty, but for the sake of mainzi, I pretended to be a master and said,

“Forward and reverse, of course, not all, at least you need to know, 2 parameters, 1 optimization, one special case.”

Note: Food can not eat randomly

Two core parameters

Sort_buffer_size determines inner and outer row. Internal row is to remove memory, external row is to use merge sort to remove disks.

Max_length_for_sort_data determines whether full-field sort or rowid sort.

Full field sort

The fields are placed in sort_buffer, sorted, and the query results are returned directly from memory

The Rowid sorting

Rowid and sort field, sort, and then from the library to find the data, join back.

Optimizing means of overwriting indexes

Overwriting an index means that there is enough information on the index to satisfy the query request without going back to the primary key index to fetch data.

example

explain
SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc
Copy the code

Use filesort, which means you need to sort.

Adjust the index

ALTER TABLE `orders_detail` DROP INDEX `order_id`,ADD INDEX `order_id` (`order_id`, `pay_date`);
Copy the code

after

explain
SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc
Copy the code

Filesort is not used because the fields are ordered after composite indexes

Order by+ Limit in special cases

Limit may use a priority queue sorting algorithm

Example:

  1. Enable optimization tracking
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=off;
SET optimizer_trace_offset=- 30, optimizer_trace_limit=30;
Copy the code
  1. View field index
SHOW INDEX FROM oc_order_online WHERE COLUMN_NAME='order_name';
Copy the code

The result shows no index

  1. Execute the order by+limit statement
select * from `oc_order_online`  order by  `order_name`  limit 20
Copy the code
  1. Query optimization trace information
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30
Copy the code

The corresponding results are as follows:

Query Paste the data in the red box to json.cn to view the formatted data

filesort_priority_queue_optimizationChosen: true indicates that priority queue sorting is used.

conclusion

  1. Sort_buffer_size determines inner and outer row
  2. Max_length_for_sort_data determines whether full-field sort or rowid sort
  3. Overwriting an index is an optimization tool
  4. Limit may involve priority queue sorting

Recent hot articles recommended:

1.1,000+ Java Interview Questions and Answers (2021)

2. I finally got the IntelliJ IDEA activation code thanks to the open source project. How sweet!

3. Ali Mock is officially open source, killing all Mock tools on the market!

4.Spring Cloud 2020.0.0 is officially released, a new and disruptive version!

5. “Java Development Manual (Songshan version)” the latest release, quick download!

Feel good, don’t forget to click on + forward oh!