当前位置：网站首页>MySQL索引（三）

MySQL索引（三）

2022-07-17 08:31:00 【毕竟尹稳健】

6、索引使用

最左前缀法则：

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

mysql> explain select * from tb_user where profession = '软件工程' 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 = '软件工程' 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)

# 跳过了一个年龄
mysql> explain select * from tb_user where profession = '软件工程' 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)

# 跳过了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)

范围查询：

联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效：

mysql> explain select * from tb_user where profession = '软件工程' 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)

# 使用 >= 可以避免
mysql> explain select * from tb_user where profession = '软件工程' 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)

索引列运算：

不要在索引列上进行运算操作，索引将失效。

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)

字符串类型不加字符串:

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)

模糊查询：

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

mysql> explain select * from tb_user where name like "吕%";
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 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 "_布%";
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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连接条件：

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

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)

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要简历索引。

数据分布影响:

如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示：

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示俩达到优化操作的目的。

# use index
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

# ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

# force index
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引：

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在改索引中已经全部能够找到），减少 select *

mysql> explain select profession,age,status from tb_user where profession = '软件工程' 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: 查找使用了索引，但是需要回表查询数据
using where；using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

思考：

一张表,有四个字段( id, username, password, status),由于数据量大,需要对以下SQL语句进行优化,该如何进
行才是最优方案:

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

使用username和password联合索引？

前缀索引：

当字段类型为字符串(varchar，text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘lO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

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

前缀长度：

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

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

单列索引与联合索引：

单列索引:即一个索引只包含单个列。

联合索引:即一个索引包含了多个列。

7、索引设计原则

1.针对于数据量较大，且查询比较频繁的表建立索引。
2．针对于常作为查询条件（where)、排序（order by)、分组(group by)操作的字段建立索引。
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6．要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7．如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。