Database index failure

Database index
MySQL optimization (I)
MySQL optimization (II)
MySQL optimization (III)
MySQL optimization (IV)

Common cases of index failure

1. or keyword in sql statement;
2. The left column field is not used in the composite index;
3. like starts with%;
4. Type conversion required;
5. There are operations in the index column in where;
6. The index column in where uses the function;
7. If mysql thinks the full table scan is faster (less data);

EXPLAIN

  id(important): identification SQL Execution order of statements
		id Same situation - Execution sequence from top to bottom
	    id Different situations - id The higher the value, the higher the priority
        id The same and different situations exist - id The higher the value, the higher the priority, the same from top to bottom
	    id by null Situation - Always the last execution
 
 select_type: Represents the query type of the current query part
	     SQL Classification of statements: simple query and complex query
	         Simple query - Excluding subqueries union Keyword SQL sentence
	         Complex query - Include subquery( select/where The back bread contains sub queries from Post query (including sub query) union Keyword SQL sentence

 Optional values:
	    SIMPLE:The query does not contain any subqueries or union   PRIMARY:If the query contains any complex sub parts, the outermost will become PRIMARY (Last executed query)   SUBQUERY:stay SELECT perhaps WHERE Subqueries in the list are marked as SUBQUERY     
	 DERIVED: stay FROM The subquery contained in is marked as DERIVED(Derivative table)    
	 UNION:If the second SELECT Appear in UNION After that, it is marked as UNION，If UNION Included in FROM Clause, the first SELECT Will be marked as: DERIVED
	UNION RESULT: from UNION Table to get results select
	 
 table: Table name of the operation

type(Important): indicates MySQL How do you get data rows from the table
    Optional values:
    all - Full table scanning with the lowest efficiency
    index - The efficiency of full index scanning is slightly better than that of full table scanning    
    range - Index range scan, only partial indexes were scanned
    ref - Appears in the non unique index, indicating that only the local range of accurate values needs to be scanned
    eq_ref - Appears in the join query of the uniqueness index
    const - Appears when you directly operate the primary key query, indicating that the system has changed the current query into a constant
    system - It means that the system clearly knows that there must be only one record in the table
    null - express SQL Statement gets the result directly at compile time
	(type Advantages and disadvantages of: system > const > eq_ref > ref > range > index > All)
possible_keys: Indexes that can be used are not necessarily used

keys(Important): the index on the current execution plan
    Note: possible possible_keys No index, but keys It appears and may appear possible_keys There is an index, but keys by null

key_len: Indicates the index length used. The larger the value, the more efficient the index is

rows(important): Indicates the number of rows of records that may need to be scanned in the query results. The smaller the value, the better

ref: Displays which column or constant to use with key Select rows from the table together.

Extra(Important): indicates some additional information during execution
    Using index - Indicates that an overlay index is used
    const row not found - Indicates that cannot be found
    Using where - express Mysql Will be right storage engine The extracted results are filtered, and the filter condition field has no index
    Using index condition - Indicates that the index will be filtered first. After filtering the index, all data rows that meet the index conditions will be found, and then WHERE Clause to filter these data rows
    Distinct - MySQL After finding the first matching line,Stop searching for more rows for the current row combination.
	Not exists - MySQL Ability to query LEFT JOIN optimization,1 match found LEFT JOIN After standard line,No more rows are checked in the table for the previous row combination.
	range checked for each record (index map: #)- MySQL did not find a good index that can be used, but found that if the column values from the previous table are known, some indexes may be used.
	Using filesort - MySQL An additional pass is required,To find out how to retrieve rows in sort order.

Example of index failure

CREATE INDEX bcd ON AAA(BB,CC,DD);
SHOW INDEX FROM AAA;

1. Full value matching

EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND CC = 'aaa' AND dd = 'a1';

type is ref, key is bcd, key_ There is a value on len, use bcd index

2. Optimal left prefix rule
Missing head

EXPLAIN SELECT * FROM AAA WHERE CC = 'aaa' AND DD = 'a1';

type is ALL, key is empty, and the bcd index is not used

Missing middle

EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND DD = 'a1';

type is ref, possible_key is bcd, key is bcd, key_ If len has a value, you can still use the bcd index

Missing last

EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND CC = 'aaa';

type is ref, possible_key is bcd, key is bcd, key_ If len has a value, you can still use the bcd index

3. Do anything on the index column

EXPLAIN SELECT * FROM AAA WHERE BB = 111 AND LEFT(CC,1) = 'a' AND dd = 'a1';

type is ref, possible_key is bcd, key is bcd, key_len has a value, but the value is relatively small. bcd index can still be used, but the efficiency is low

4. The index on the column after the range condition is invalid

EXPLAIN SELECT * FROM AAA WHERE BB > 111 AND CC = 'bbb' AND dd = 'a1';

The search level is range, and the index on name is invalid.

type is ALL, possible_key is bcd, key is null, key_len has no value and no index is used

5. Try to use overlay index and reduce the use of select full field

EXPLAIN SELECT * FROM AAA WHERE BB = 111;

type is ref, possible_key is bcd, key is bcd, key_len has a value, but the value is relatively small. bcd index can still be used, but the efficiency is low

EXPLAIN SELECT BB FROM AAA WHERE BB = 111;

type is ref, possible_key is bcd, key is bcd, key_len has a value, but the value is relatively small. Extra is Using index. bcd index and overlay index can still be used

6. Use is not equal to (! = or < >) index cannot be used

EXPLAIN SELECT * FROM AAA WHERE BB != 111;

7. The index cannot be used with is null or is not null

EXPLAIN SELECT * FROM AAA WHERE BB IS NOT NULL;

8.like has led to index invalidation due to the beginning of wildcard (% XX) (solution: use overwrite index)

EXPLAIN SELECT * FROM AAA WHERE BB LIKE '%1%';

If you want to solve the problem, use the overlay index

EXPLAIN SELECT BB FROM AAA WHERE BB LIKE '%1%';

type is index, possible_key is null, key is bcd, key_len has the values of Using index and Using Where, or can you use the upper bcd index and use the upper overlay index

9. Use less or. Using it to connect indexes will fail

EXPLAIN SELECT * FROM AAA WHERE BB = 111 OR CC = 'bbb' OR dd = 'a1';

type is ALL, possible_ The key is bcd, the key is null, and the bcd index is not used

Overlay index

The InnoDB storage engine supports coverage index (or index coverage), that is, you can get the queried records from the secondary index without querying the records in the clustered index. One advantage of using an overlay index is that the secondary index does not contain all the information of the entire row of records, so its size is much smaller than the clustered index, so it can reduce a lot of IO operations

The required data can be obtained directly from the non clustered index without looking up the table. For example, select id from table where name =xxx;(id is the primary key and name is the index column) the overlay index will also be used in statistical operations. For example, (a,b) joint index, the select * from table where b = xxx statement will not go through the index according to the leftmost prefix principle, but if it is a statistical statement, select count(*) from table where b = xxx; The overlay index is used.