MySQL index (III)

6、 Index usage

The leftmost prefix rule ：

If you index multiple columns ( Joint index ), Follow the leftmost prefix rule . The leftmost prefix rule means that queries start from the leftmost column of the index and do not skip columns in the index . If you jump a column , The index will be partially invalidated ( The following field index is invalid ).

mysql> explain select * from tb_user where profession = ' Software Engineering ' and age = 22 and gender = 1;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 1028    | const,const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_user where profession = ' Software Engineering ' and age = 22 ;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 1028    | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#  Skipped an age 
mysql> explain select * from tb_user where profession = ' Software Engineering ' and gender = 1;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 1023    | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

#  Skip the profession
mysql> explain select * from tb_user where age = 22 and gender = 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Range queries ：

In the union index , Range query appears （>,<） , The column index on the right side of the range query is invalid ：

mysql> explain select * from tb_user where profession = ' Software Engineering ' and age > 22 and gender = 1;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 1028    | NULL |    1 |   100.00 | Using index condition; Using where |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

#  Use  >=  You can avoid 
mysql> explain select * from tb_user where profession = ' Software Engineering ' and age > 22 and gender = 1;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 1028    | NULL |    1 |   100.00 | Using index condition; Using where |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

Index column operation ：

Do not operate on index columns , Index will fail .

mysql> explain select * from tb_user where substring(phone,10,2) = '27';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

String type without string :

mysql> explain select * from tb_user where phone = 18273595827;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | idx_user_phone | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

Fuzzy query ：

If it's just tail blur matching , The index will not fail . If it's a fuzzy head match , Index failure .

mysql> explain select * from tb_user where name like " Lu %";
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_name | idx_user_name | 1023    | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from tb_user where name like "_ cloth %";
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

or Connection condition ：

use or The conditions of separation , If or The columns in the previous condition are indexed , And there's no index in the next column , Then the indexes involved will not be used .

mysql> explain select * from tb_user where id = 1 or age = 22;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

because age No index , So even id、phone There is an index , Indexes will also fail . So you need a needle for age Also want resume index .

Data distribution affects :

If MySQL Evaluation uses indexes more slowly than full tables , Index is not used .

SQL Tips ：

SQL Tips , Is an important means to optimize the database , Simply speaking , Is in the SQL Add some artificial prompts to the statement to optimize the operation .

# use index
explain select * from tb_user use index(idx_user_pro) where profession = ' Software Engineering ';

# ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession = ' Software Engineering ';

# force index
explain select * from tb_user force index(idx_user_pro) where profession = ' Software Engineering ';

Overlay index ：

Try to use overlay index （ The query uses an index , And the columns that need to be returned , All can be found in the index ）, Reduce select *

mysql> explain select profession,age,status from tb_user where profession = ' Software Engineering ' and age = 22 and status = 1;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 1033    | const,const,const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• using index condition: Search uses index , But you need to return the table to query the data
• using where;using index： Search uses index , But all the data needed can be found in the index column , So you don't need to go back to the table to query the data

reflection ：

A watch , There are four fields ( id, username, password, status), Because of the amount of data , The following SQL Statement optimization , How to enter
OK is the best solution :

select id,username,password where username= 'itcast';

Use username and password Joint index ？

Prefix index ：

When the field type is string (varchar,text etc. ), when , Sometimes you need to index long strings , This makes the index big , When inquiring , Waste a lot of disk lO, Affecting query efficiency . At this point, you can prefix only part of the string with , Index , This can greatly save index space , To improve index efficiency .

create index idx_xxxx on table_name (column(n));

Prefix length ：

It can be determined according to the selectivity of the index , Selectivity refers to index values that are not repeated （ base ） And the total number of records in the data table , The higher the index selectivity, the higher the query efficiency , The only index selectivity is 1, This is the best index selectivity , Performance is also the best .

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;

Single column index and joint index ：

Single index : That is, an index contains only a single column .

Joint index : That is, an index contains multiple columns .

7、 Index design principles

1. For large amount of data , The tables that are frequently queried are indexed .
2． For often used as query criteria （where)、 Sort （order by)、 grouping (group by) Index the fields of the operation .
3. Try to select highly differentiated columns as indexes , Try to build a unique index , The more distinguishable , The more efficient the index is .
4. If it is a string type field , The length of the field is long , You can focus on the characteristics of the field , Building prefix index .
5、 Try to use a federated index , Reduce single column index , When inquiring , Joint indexes can often overwrite indexes , Save storage space , Avoid returning to your watch , Improve query efficiency .
6． To control the number of indexes , The index is not that more is better , More indexes , The greater the cost of maintaining the index structure , It will affect the efficiency of addition, deletion and modification .
7． If the index column cannot store NULL value , Please use... When creating the table NOTNULL Constrain it . When the optimizer knows whether each column contains NULL When the value of , It can better determine which index is most effectively used for queries .

Summary ：

• Create an index. Try to create a federated index