Let's first review the basic knowledge of field selectivity.
-- Basic knowledge of field selectivity --
Introduction: Can I create indexes for all fields?
As shown in the following table, the sort field has very poor selectivity. You can run the show index from ads command to see that sort's Cardinality (hash level) is only 9, and this field should not be indexed:
Table |
Non_unique |
Key_name |
Seq_in_index |
Column_name |
Collation |
Cardinality |
Sub_part |
Packed |
Null |
Index_type |
Comment |
Ads |
1 |
Sort |
1 |
Sort |
A |
9 |
\ N |
\ N |
|
BTREE |
|
Optimization policy A: Field Selectivity
- Low selectivity index may cause performance problems
- Index selectivity = unique value of the index column/number of table records;
- The higher the selectivity, the higher the index retrieval value, and the less system resources are consumed. The lower the selectivity, the lower the index retrieval value, and the more system resources are consumed;
- When a query condition contains multiple fields, do not create an index on a field with low selectivity.
- You can create a composite index to enhance the low field selectivity and avoid the side effects of creating an index with low selectivity;
- Minimize possible_keys. Correct indexes will increase the SQL query speed. Excessive indexes will increase the cost of selecting indexes by the optimizer. Do not abuse indexes;
Review the business scenarios of combined index and range query.
-- Relationship between the order of combined index fields and Range Query --
Reference: Can I use the combined index (ads_id, city_id) to query city_id in (, 10) in a range?
For example,
The ac table has a composite index (ads_id, city_id ).
Can the following ac. city_id IN (0, 8005) query conditions use the combined index (ads_id, city_id) of the ac table?
Ac. city_id IN (0, 8005)
Because mysql indexes are based on B-Tree, composite indexes have the concept of "field order.
Therefore, the query conditions includeAc. city_id IN (0, 8005)And the composite index is(Ads_id, city_id)The composite index cannot be used for this query.
DBA concluded:
Various scenarios of combined index QueryIndex (A, B, C) -- composite Index multi-field is ordered, and is A complete BTree Index.
- The following conditions can be queried using the combined index:
- A> 5
- A = 5 and B> 6
- A = 5 and B = 6 AND C = 7
- A = 5 and B IN (2, 3) AND C> 5
- The following conditions cannot be queried using the composite index:
- B> 5 -- the query condition does not contain the fields in the first column of the composite index.
- B = 6 AND C = 7 -- the query condition does not contain the fields in the first column of the composite index.
- The following conditions can be queried using the combination of the preceding indexes:
- A> 5 and B = 2 -- when the range query uses the first column, the query condition can only use the first column
- A = 5 and B> 6 AND C = 2 -- use the second column for Range Query. The query condition can only use the first two columns.
Various scenarios of composite index sortingThere is A composite Index (A, B ).
- The following conditions can be sorted by composite indexes:
- Order by a -- Sort the first column
- A = 5 order by B -- sort BY the second column after filtering in the first column
- ORDER BY A DESC, B DESC-- Note that the two columns are sorted in the same order.
- A> 5 order by a -- data retrieval and sorting are in the first column
- The following conditions cannot be sorted by composite indexes:
- Order by B -- sort in the second column of the Index
- A> 5 order by B -- the range query is in the first column, and the sorting is in the second column.
- A in (1, 2) order by B -- same reason as above
- ORDER BY A ASC, B DESC-- Note that the two columns are sorted in different order.
Follow the steps of creating a composite index and continue to extend. Please pay attention to the concept of "index merge:
- For MySQL versions earlier than, only one index (use at most only one index for each referenced table) can be used for a table during SQL query ),
- Starting from MySQL 5.0Index mergeConcept, including Index Merge Union Access Algorithm (Multiple indexes are accessed in parallel), including Index Merge Intersection Access Algorithm (Multiple indexes are accessed at Intersection ), multiple indexes in a table can be used in an SQL query.
- Before MySQL 5.6.7, using index merge has an important precondition: no range is available. [Reference Resource 2]
- MySQL index combination can use multiple Indexes
- SELECT * from tb where a = 5 ANDB = 6
- Indexes (A), (B), and indexes can be merged respectively;
- It is better to create A composite index (A, B;
- SELECT * from tb where a = 5 ORB = 6
- Indexes (A), (B), and indexes can be merged respectively;
- Composite indexes (A, B) cannot be used in this query. It is better to create indexes (A) and (B) respectively;
It is still emphasized again:
Remember, it is a virtue to explain and try again!
Reference resources:1) Chinese translation, 2013, MySQL index best practice feedback; 2) orczhou, 2013, MySQL optimizer: index merge introduction; 3) orczhou, 2013, supplementary description of index merge; 4) zhengyun, 2013, [Slow query optimization] pay attention to who is the driving table for Table query. You don't know who is better to join. Please let mysql determine it by yourself. MySQL Internals-Index Merge optimization Figure 1: Turn: I have meed a mi, and the boss urged me to refactor the legacy Python code ......
