Schema optimization and indexing

Indexes are a way to efficiently find rows, but MySQL can also use indexes to find data for a column, so it does not have to read the entire row. After all, index leaf nodes store the data they index; When you can get the data you want by reading the index, you don't need to read the rows. An index that contains (or overwrites) data that satisfies the query's results is called the Overlay Index (covering Indexex)

Overwriting an index is a very powerful tool and can greatly improve performance. Consider the benefits of reading only indexes:

The entities that are indexed tend to be smaller than the entire row size. If MySQL reads only the index, it means there is very little data to access. This is useful for caching work, so that the corresponding time is basically from replicated data. Also useful for IO limits, because indexes are smaller and easier to write into memory than data. (This is especially useful for MyISAM, which compresses the index so that the index becomes smaller).

Indexes are sorted based on index values, so access to IO limits is relatively less than the IO required from a random hard disk location. For some storage engines, such as MyISAM, you can even use the Optimize table to get all sorts of indexes. This enables a simple scope query to use Access to a fully contiguous index.

Most storage engine cache indexes are better than data. Some storage engines, such as MyISAM, cache indexes only. Because the operating system caches MyISAM data, accessing the data requires a system call. This can lead to very serious performance problems. Especially for caching, system invocation is the most important part of data access consumption.

The overlay index has some special utility with the InnoDB table. Because InnoDB is a clustered index. InnoDB secondary indexes hold the primary key of the row in their leaf nodes. Therefore, overrides of secondary indexes can avoid the lookup of another index on the primary key.

In these scenarios, it is much lower to satisfy a query consumption from the index than the query row.

Overwriting an index does not apply to any index type, and the index must store the value of the column. Hash, spatial, and Full-text indexes do not store values, so MySQL can only use B-tree. and the different storage Engine implementation Overlay Index is different. Not all storage engines support them.

When a query is overwritten by an index. (an index-covered query). You use explain to find that the value of the extra column is "Using index". As an example, the Sakila.inventory table consists of a multiple-column index on the store_id, film_id column. MySQL can use the index to access both columns. As follows

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
　　 　 　 　 　 id: 1
　　select_type: SIMPLE
　　 　 　 　table: inventory
　　 　 　 　 type: index
possible_keys: NULL
　　 　 　 　 　key: idx_store_id_film_id
　　 　 　key_len: 3
　　 　 　 　 　ref: NULL
　　 　 　 　 rows: 4673
　　 　 　 　Extra: Using index

The statement that overwrites the index is somewhat tricky to turn off optimizations. The MySQL statement optimizer determines whether an index overrides the statement before executing it. If the index overrides a where condition, it is not the entire query. If this condition evaluates to FALSE,MYSQL51 and previous versions will take out rows.

Let's see why this is so. And how to rewrite the query to solve the problem mentioned above.

mysql> EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY'
　　 　-> AND title like '%APOLLO%'\G
*************************** 1. row ***************************
　　 id: 1
　　select_type: SIMPLE
　　table: products
　　type: ref
　　possible_keys: ACTOR,IX_PROD_ACTOR
　　key: ACTOR
　　key_len: 52
　　ref: const
　　rows: 10
Extra: Using where