(2) MySQL optimization query using index optimization

Overview

?? Indexing is one of the most common and important tools in database optimization. This article looks at the classification of indexes, the use of scenes, the inability to use scenes, etc.

Index Classification

?? The index is implemented in the MySQL storage engine, not at the server level. indexes for each storage engine are not necessarily the same. MySQL supports 4 types of indexes:

• B-tree index: Most common indexes, most engines support B-Tree indexes.
• Full-text Index: Full-text index.
• Hash index: only memory engine supported.
• R-tree index: A spatial index is a special index of MyISAM, which is used relatively sparingly.

Derived indexes: Primary key, unique index, combined index, prefix index, and so on. The hash index is suitable for key-value queries (equivalent queries) and is not suitable for range queries.

Index	MyISAM engine	InnoDB engine	Memory Engine
B-tree Index	Support	Support	Support
Full-text Index	Support	-	-
Hash index	-	-	Support
R-tree Index	Support	-	-

Typical scenarios for working with indexes 1. Match all values (match the full value)

Specifies a specific value for all columns in the index, that is, a condition that matches all columns in the index.

SELECT * FROM rental WHERE rental_date=‘2005-05-25 17:22:10‘   AND inventory_id=373   AND customer_id=343;

2. Range query for matching values (match a range of values)

Scopes a lookup on an indexed value.

SELECT * FROM rental WHERE customer_id >= 373 AND customer_id < 400;

3. Match the leftmost prefix (match a leftmost prefix)

Query only by using the leftmost column in the index. For example, the combined index (COL1,COL2,COL3) can be used by the COL1,COL1+COL2,COL1+COL2+COL3 equivalent query.

SELECT * FROM payment WHERE payment_date=‘2006-02-14 15:16:03‘ AND last_update = ‘2006-02-15 22:12:32‘;

From the results we can see that the index is used, but row is 182 rows, and all only partial indexes are used.

SELECT * FROM payment WHERE amount=3.98 AND last_update=‘2006-02-15‘;

From the results, this query did not use the index, the full table lookup.

4. Query only for index queries (index only query)

When the query column is in the indexed field. That is, the columns in the select are in the index.

SELECT last_update FROM payment WHERE payment_date=‘2005-08-19 21:21:47‘ AND amount=4.99;

Extra partial using index, indicating that the index is not required to return to the table, using index is usually said to overwrite the index scan (that is, find the index, found the results to query, no longer back to the table to find).

5. Match column prefix (match a column prefix)

Only the first column of the index is used, and it contains only the beginning of the 1th column of the index to find.

SELECT title FROM film_text WHERE title LIKE ‘AFRICAN%‘;

6. Index part equivalent matching, partial range matching

SELECT inventory_id FROM rental WHERE rental_date=‘2006-02-14 15:16:03‘ AND customer_id >= 300 AND customer_id <=400;

Type=ref, indicating that the index was used.

7. Column name is index, COLUMN_NAME is null, using index

SELECT * FROM payment WHERE rental_id IS NULL;

typical scenario where an index exists but cannot use an index 1. A like query that starts with%

SELECT * FROM actor WHERE last_name LIKE ‘%NI%‘;

SELECT * FROM (SELECT actor_id FROM actor WHERE last_name LIKE ‘%NI%‘)a,actor b WHERE a.actor_id=b.actor_id;

2. Implicit conversion of data type, no index is used

SELECT * FROM actor WHERE last_name=1;

SELECT * FROM actor WHERE last_name=‘1‘;

3. Combined index, does not satisfy the leftmost principle, does not use the conforming index

SELECT * FROM payment WHERE amount=3.98 AND last_update=‘2006-02-15 22:12:32‘;

4. Estimate that using an index is slower than a full table scan, do not use the index

If you query a movie with a title beginning with "S", the return record scale is larger, and the MySQL estimated index scan is not as good as a full table scan.

SELECT * FROM film_text WHERE title LIKE ‘S%‘;

5. Use or to split the condition, if or before or after a column without an index, it will not be indexed

SELECT * FROM payment WHERE customer_id=203 OR amount=3.96;

(2) MySQL optimization query using index optimization