-- Example table
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT 'Full name',
  `age` int(20) NOT NULL DEFAULT '0' COMMENT 'Age',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT 'position',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Enrollment Time',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=136326 DEFAULT CHARSET=utf8 COMMENT='Employee Sheet'

Order by and Group by optimization

EXPLAIN select * from employees WHERE name='LiLei' and position='dev' order by age;

Using the leftmost prefix rule, the middle field cannot be broken, so the query uses a name index, which can also be seen from key_len=74, and the age index column is used in the sorting process because there is no using filesort in the Extra field.

EXPLAIN select * from employees WHERE name='LiLei'  order by position;

From the execution results of explain lain, key_len=74, the query uses the name index, skipping age due to position ing, and Using filesort appears.

EXPLAIN select * from employees WHERE name='LiLei'  order by age,position;

Lookup uses only the name index, age and position are used for sorting, and there is no Using filesort.

EXPLAIN select * from employees WHERE name='LiLei'  order by position,age;

Unlike the previous case, Using filesort appears in Extra because the index was created in the order name, age, position, but the position and age were reversed when sorting.

EXPLAIN select * from employees WHERE name='LiLei'  order by age asc, position desc;

Although the sorted fields are in the same order as the union index, and order by is the default ascending order, where position desc is descending, resulting in a Using filesort that is different from how the index is sorted.This query can be supported by a descending index in versions above Mysql8.

EXPLAIN select * from employees WHERE name in('LiLei', 'zhuge')  order by age, position ;

For sorting, multiple equality criteria are also range queries.

EXPLAIN select * from employees WHERE name > 'a' order by name;

Can be optimized with override index

EXPLAIN select name,age,position from employees WHERE name > 'a' order by name;

filesort sorting

EXPLAIN select * from employees where name='LiLei' order by position;

View the corresponding trace result for this sql (show only the sorting section):

set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐open trace
select * from employees where name = 'LiLei' order by position;
select * from information_schema.OPTIMIZER_TRACE;

{
      "join_execution": {  --sql Execution phase
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {  --File Sorting Information
              "rows": 1,  --Estimated number of rows scanned
              "examined_rows": 1,  --Rows participating in sorting
              "number_of_tmp_files": 0, --Number of temporary files used, 0 for all sort_buffer Memory sorting, otherwise use disk file sorting
              "sort_buffer_size": 200704,  --Sort Cache Size
              "sort_mode": "<sort_key, additional_fields>"  --Sort order, the one-way sort used here
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }

Modify max_length_for_sort_data=10

set max_length_for_sort_data = 10;  --employees The sum of all the field lengths in the table must be greater than 10 bytes
select * from employees where name = 'LiLei' order by position;
select * from information_schema.OPTIMIZER_TRACE;

{
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 1,
              "examined_rows": 1,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 53248,
              "sort_mode": "<sort_key, rowid>"  --Sort by two way
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }

By comparing the two sorting modes, one-way sorting places all the field data that needs to be queried in sort_buffer, while two-way sorting only places the primary key id and the field that needs to be sorted in sort_buffer, and then returns the field data that needs to be queried in the original table through the primary key id.MySQL uses the parameter max_length_for_sort_data to control sorting and uses different sorting modes in different scenarios to improve sorting efficiency.

Summary of optimization

• Mysql supports two ways of sorting filesort and index, using index means that Mysql scans the index itself to complete the sorting.Index efficiency, filesort efficiency is low.
• order by satisfies both cases using index.

The order by statement uses the leftmost front column of the index.
Use the where clause and order by clause conditional column combination to satisfy the leftmost front column of the index.

• Complete the sorting on the index columns as far as possible, following the leftmost prefix rule when indexing (the order in which the index is created).
• If the order by condition is not on the index column, a using filesort is generated.

Not noticing my public number yet?

• The two-dimensional code at the end of the scanner pays attention to the public number, which can be drawn as follows:
• Learning materials: 1T video tutorials: covering front-end and back-end teaching videos of Java web, machine learning/artificial intelligence teaching videos, Linux system tutorial videos, IELTS video tutorials;
• More than 100 books: Classic Must See Books, LeetCode Exercises Complete with C/C++, Java, Python programming languages;
• Software Tools: Almost all the software you might use on the programming path;
• Project source: 20 JavaWeb project sources.