Writing in the front

Articles involved in the customer table from sakila case, download address to http://downloads.mysql.com/docs/sakila-db.zip

MySQL > alter database

  • Returns ordered data directly through an index sequential scan

  • By sorting the returned data, known as FileSort sort.

All sorts that do not return a sort directly by index are called FileSort sorts. FileSort does not stand for sorting by disk files, only that a sort operation is performed. Whether the sort operation uses disk files or temporary tables depends on the MySQL server’s sorting parameters and the size of the data to be sorted.

EXPLAIN sort analysis

customer DDL

CREATE TABLE `customer` (
  `customer_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `store_id` tinyint unsigned NOT NULL.  `first_name` varchar(45) NOT NULL.  `last_name` varchar(45) NOT NULL. `email` varchar(50) DEFAULT NULL. `address_id` smallint unsigned NOT NULL. `active` tinyint(1) NOT NULL DEFAULT '1'. `create_date` datetime NOT NULL. `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. PRIMARY KEY (`customer_id`),  KEY `idx_fk_store_id` (`store_id`),  KEY `idx_fk_address_id` (`address_id`),  KEY `idx_last_name` (`last_name`),  KEY `idx_storeid_email` (`store_id`.`email`),  CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE. CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  CREATE DEFINER=`root`@` % ` TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW(a);Copy the code

Returns all user records, sorted by customer_id. Because customer_id is the primary key, the records are sorted by the primary key, so no additional sorting is required and all data is returned.

EXPLAIN SELECT * FROM customer ORDER BY customer_id;
Copy the code

By default, customer_ID is used to sort the customer_id. In this case, a full table scan is required, and then the results of a full table scan are sorted according to store_id. Therefore, FileSort is used.

EXPLAIN SELECT * FROM customer ORDER BY store_id;
Copy the code

The store_id and email fields have a joint index idx_storeid_email. Only the store_id and email fields are queried. The query data is returned directly from the B+ tree where the union index is located (sorted first by the store_id field and then by the email field), so the query results here are sorted.

EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id;
Copy the code

The idx_storeid_email joint index sorts by the store_id field and then by the email field. The order of the idx_storeid_email index tree cannot be used.

EXPLAIN SELECT store_id , email FROM customer ORDER BY email;
Copy the code

ORDER BY uses the federated index to sort

EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id , email;
Copy the code

Mixing ASC and DESC in the ORDER BY field results in ordering using FileSort.

EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id ASC , email DESC;
Copy the code

The idx_storeid_email index is used to sort email fields when store_id is 1

EXPLAIN SELECT store_id , email FROM customer WHERE store_id  = 1 ORDER BY email;
Copy the code

The ORDER BY email field cannot be sorted using the idx_storeid_email index.

EXPLAIN SELECT store_id , email FROM customer WHERE store_id  >= 1 AND store_id <= 3 ORDER BY email;
Copy the code

ORDER BY FileSort

  1. The order by field uses a mixture of ASC and DESC.
  2. FileSort may result if the order by sort field is not the primary key when SELECT *.
  3. In the case of the joint index, the left-right order of the fields sorted by order by is inconsistent with the left-right order of the fields in the joint index, resulting in FileSort.
  4. In the case of a joint index, the left-right order of the WHERE and ORDER by fields, the left-right order of the joint index fields, or a range query for the WHERE field may result in FileSort.

FileSort optimization

The presence of FileSort can be reduced by creating appropriate indexes, but in some cases, the conditions cannot eliminate FileSort completely, so you need to optimize FileSort. For FileSort, MySQL has two sorting algorithms.

  • Two Passes: First fetches the sort field and row pointer based on the criteria and then sorts the rows in sort buffer. If the sort buffer is not enough, the sort result is stored in the Temporary Table. The complete record is read from the row pointer back to the table after sorting. This algorithm is used before MySQL 4.1. It requires two accesses to the data. The first one obtains the sorted field and row pointer information, and the second one obtains the complete record according to the row pointer. One of them is the memory overhead of sorting.

  • Single Passes: Fetches all columns of a row that meet the criteria at one time and outputs the result set after sorting in sort buffer. The memory overhead of sorting is relatively large, but the sorting efficiency is higher than that of two-scan algorithm.

MySQL determines which sort algorithm to use by comparing the size of the system variable max_LENGTH_FOR_sort_data with the total size of the fields retrieved by the Query statement. If the max_LENGTH_FOR_SORt_DATA setting is large enough, a scan algorithm is used; Otherwise, use the two-scan algorithm. If the value of max_LENGTH_FOR_sort_data is appropriately increased, MySQL can choose a more optimized FileSort sorting algorithm. Of course, if max_LENGTH_FOR_SORt_data is set too high, CPU utilization will be too low and disk I/O will be too high.

Increase sorT_buffer_size to allow sorting to be done in memory rather than in files by creating temporary tables; Sort_buffer_size is not allowed to be used indefinitely. Sort_buffer_size is a per-thread exclusive parameter. If you set sort_BUFFer_size, it will cause server SWAP.

Try to use only necessary fields. SELECT specific field names instead of SELECT * to SELECT all fields. This can reduce the use of sorting areas and improve SQL performance.

reference

MySQL Database Development, Optimization, Management and Maintenance edition 2

MySQL tutorial 45

The last

If you want to follow my updated articles and the dry goods I share in real time, you can follow my official account we are all guinea pigs.


Thank you for your attention, if you like, you can click on the bottom right to see, and you are welcome to share this article with more friends, thank you!

Make progress every day!!

In 2020.05.28