MySQL sorting using index scan

Install sakila

We will use the MySQL sample database sakila to demonstrate and explain SQL dev.mysql.com/doc/sakila/…

Index Scan Sort

MySQL has two ways to generate ordered results: through sorting operations; or by scanning in index order; if the value of the type column output by EXPLAIN is "index", it means that MySQL uses index scanning to do the sorting.
Scanning the index itself is fast because only one has to move from one index record to the immediately next record. However, if the index does not cover all the columns required for the query, you will have to go back to the table to query the corresponding row every time you scan an index record. This is essentially random I/O, so reading data in index order is usually slower than a sequential full table scan, especially in IO-intensive workloads. At this time, a full table scan may be used instead of an index search.
If possible, indexes should be designed to accommodate both sorting and finding rows.
MySQL can use the index to sort the results only if the order of the index columns is exactly the same as the order of the ORDER BY clause and the sorting direction (reverse or forward) of all columns is the same. If the query needs to join multiple tables, the index can be used for sorting only when all the fields referenced by the ORDER BY clause are from the first table. The restrictions of the ORDER BY clause are the same as those of a search query: the leftmost prefix of the index must be met; otherwise, MySQL needs to perform a sort operation (filesort) and cannot use the index sort.

Table Structure

We will use the rental table to explain

CREATE TABLE `rental` (
  
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

Check whether Using filesort appears in Extra (the sorting operation that cannot be completed by using the index in MySQL is called "file sorting"). When we try to sort a field without an index, it is filesort. Although there is a file in it, it has nothing to do with the file. It is actually an internal quick sort.

Situations where index scans can be used for sorting

Fill in the leading column

There is one case where the ORDER BY clause does not need to satisfy the leftmost prefix requirement of the index, that is, when the leading column is a constant. If constants are specified for these columns in the WHERE clause or JOIN clause, the lack of indexes can be "made up". We use the Sakila database to test

You can see

The Extra in the book says Using where, but when I executed it, I used Using index condition. The reason is that the version used in high-performance MySQL is 5.5, and the index condition pushdown in version 5.6 is still in the stage of not being officially released. The reason why there is no filesort here is because there is a constant condition of rental_date = '2005-05-25', which is equivalent to filling the first column of the index, thus meeting the leftmost prefix requirement of the index.

The order by contains only one sort

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id desc

You can see

Note that the condition used in the book is rental_date>'2005-05-25'

WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id

At this time, we cannot use the index sorting, but directly scan the entire table to sort it. The reason is that there are too many data items returned, and it is not cost-effective to use the index query at this time.

It should be noted that the number of rows in the explanation here is not accurate, it is just an estimate. In fact, there are 16036 data items in the query according to this condition. To solve this problem, you need to add limit

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id limit 0,10

The corresponding execution plan

You can see that the index is used

Situations where index scans cannot be used

The query conditions contain different sorting directions

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id desc, customer_id asc

Both columns in the index are in ascending order. Now in the order by, one column is in ascending order and the other is in descending order, so a secondary sort is required.

The query condition refers to a column that is not in the index

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY inventory_id ,staff_id

When the leftmost prefix cannot be combined

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY customer_id

When the first column is the query range

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-08-22' ORDER BY inventory_id,customer_id

where there are multiple equal conditions

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' and inventory_id in(1,2) ORDER BY customer_id

Simply put, those that do not meet the leftmost prefix of the index will be sorted.

Summarize

Today we explained index scan sorting in MySQL. Tomorrow we will continue to introduce other methods of building high-performance indexes. Stay tuned and see you in the next article!

The above is the detailed content of the simple use of MySQL index scan. For more information about MySQL index scan sort, please pay attention to other related articles on 123WORDPRESS.COM!