Schema optimization and indexing

MySQL has two methods for generating sequential results: Using file sorting or sequentially scanning indexes. You can use explain to see if the Type column is index to find out whether MySQL plans to scan the index.

Scanning the index itself is very fast because it simply needs to move from one index entity to the next. However, if MySQL does not overwrite the query with an index, it looks for each row found in the index. This is a random IO process, so reading data from an indexed order is much slower than a continuous-table scan.

MySQL can use the same index to sort and find rows. If possible, it would be a good thing to meet both tasks.

Sorting by index is useful when the order of the indexes is the same as the order by condition and all columns are in the same direction (ascending or descending). If a query has more than one table associated with it, you can use an index sort if the column must be the first table after all order by criteria. The order by condition also has the same limit as the query: it needs to form an index of the left-side prefix. In other cases MySQL uses the file sort.

In one case, the condition after order by is not the leftmost index prefix, and if the Where condition or a join condition gives the values of those missing indexes, the index can still be used to sort.

For example, the rental table has an index (rental_date, inventory_id, customer_id). 、

CREATE TABLE rental (
　　 ...
　　 PRIMARY KEY (rental_id),
　　 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),
　　 ...
);

MySQL uses the rental_date index to sort the following queries

mysql> EXPLAIN SELECT rental_id, staff_id FROM sakila.rental
　　 　-> WHERE rental_date = '2005-05-25'
　　 　-> ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
　　 　 　 　 type: ref
possible_keys: rental_date
　　 　 　 　 　key: rental_date
　　 　 　 　 rows: 1
　　 　 　 　Extra: Using where

This can be sorted using an index, even if the order by condition is not the leftmost index prefix. That's because we've specified the value of the first column index after the Where condition.

The following are some of the queries that can be sorted using indexes. The following one can use the index because the query already uses the first indexed column and specifies the second indexed column with order by. To look at, is a leftmost index prefix.

... WHERE rental_date = ' 2005-05-25 ' ORDER by inventory_id DESC;

The following statement is also possible, because the condition after the order is also the leftmost index prefix.

... WHERE rental_date > ' 2005-05-25 ' ORDER by rental_date, inventory_id;

The following are statements that cannot be sorted using the index:

This query uses different orientations for the sort, but the indexed column is ascending.

... WHERE rental_date = ' 2005-05-25 ' ORDER by inventory_id DESC, customer_id ASC;

The following statement, which is not an index, is the column after the order by.

... WHERE rental_date = ' 2005-05-25 ' ORDER by inventory_id, staff_id;

The following statement is not the leftmost index prefix.

... WHERE rental_date = ' 2005-05-25 ' ORDER by customer_id;

This statement uses a range query for the first field, so MySQL will not use the remaining indexes.

... WHERE rental_date > ' 2005-05-25 ' ORDER by inventory_id, customer_id;

There are many identical conditions for the inventory_id column. For this sort, and query a range.

... WHERE rental_date = ' 2005-05-25 ' and inventory_id in (1,2) order by customer_id;

The following statement, on the surface, can be sorted using an index, but in fact it is not, because the statement optimizer places the Film_actor table in the second position. Let's talk about it later.

mysql> EXPLAIN SELECT actor_id, title FROM sakila.film_actor
　　 　-> INNER JOIN sakila.film USING(film_id) ORDER BY actor_id\G
+------------+----------------------------------------------+
| table 　 　 　| Extra 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　|
+------------+----------------------------------------------+
| film 　 　 　 | Using index; Using temporary; Using filesort |
| film_actor | Using index 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　|
+------------+----------------------------------------------+

Use indexes to sort the most important thing is that a query has order by and limit. This will be said in detail later.