mysql聚合索引失效情况讨论

最新推荐文章于 2024-02-05 00:18:49 发布

槑槑123

最新推荐文章于 2024-02-05 00:18:49 发布

阅读量1k

点赞数 3

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_51884463/article/details/125998056

版权

1、创建测试环境

创建student表进行测试,将其中三列创建索引，创建表语句如下所示：

mysql> create table student(
    -> id int(10)  NOT NULL AUTO_INCREMENT COMMENT 'ID',
    -> name varchar(10) NOT NULL ,
    -> passworld varchar(10) NOT NULL ,
    -> age int(11) not null,
    -> sex varchar(1) not null,
    -> primary key(id),
    -> key `ind_stu` (`name`,`passworld`,`age`)
    -> )
    -> ;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

插入数据:

mysql> insert into student values(1,'zhangsan','123456','18','1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(2,'lisi','123456','18','2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(3,'admin','123456','18','2');
Query OK, 1 row affected (0.01 sec)

结果查看：

mysql> select *from student;
+----+----------+-----------+-----+-----+
| id | name     | passworld | age | sex |
+----+----------+-----------+-----+-----+
|  1 | zhangsan | 123456    |  18 | 1   |
|  2 | lisi     | 123456    |  18 | 2   |
|  3 | admin    | 123456    |  18 | 2   |
+----+----------+-----------+-----+-----+
3 rows in set (0.01 sec)

2、explain语句简介

explain：在select语句之前增加explain关键字，执行后MySQL就会返回执行计划的信息，而不是执行sql。但如果from中包含子查询，MySQL仍会执行该子查询，并把子查询的结果放入临时表中。

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

id列：
id列的编号是select的序列号，有几个select就有几个id，并且id是按照select出现的顺序增长的，id列的值越大优先级越高，id相同则是按照执行计划列从上往下执行，id为空则是最后执行。
select_type列：
表示对应行是简单查询还是复杂查询。

1、simple：不包含子查询和union的简单查询
2、primary：复杂查询中最外层的select
3、subquery：包含在select中的子查询（不在from的子句中）

table列：
表示当前行访问的是哪张表。
partitions列：
查询将匹配记录的分区。对于非分区表，该值为 NULL
type列：
此列表示关联类型或访问类型。也就是MySQL决定如何查找表中的行。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all。
possible_keys :
此列显示在查询中可能用到的索引。
key 列：
此列显示MySQL在查询时实际用到的索引。在执行计划中可能出现possible_keys列有值，而key列为null，这种情况可能是表中数据不多，MySQL认为索引对当前查询帮助不大而选择了全表查询。
key_len 列：
此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。
ref列：
此列显示key列记录的索引中，表查找值时使用到的列或常量。常见的有const、字段名
rows 列：
此列是MySQL在查询中估计要读取的行数。注意这里不是结果集的行数。

filtered 列：
此列是一些额外信息。常见的重要值如下：

          1）Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。

          2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。

          3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。

          4）Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。

          5）Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。

          6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。

3、验证

(1):全列查询、顺序不同

情况一：正常查询：使用 ind_stu (name,passworld,age)

mysql> explain select * from student where name = 'admin' and passworld = '123456' and age = '18';
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 88      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

结论一：使用name 、passworld、age、进行查询可以命中索引。

情况二：将查询顺序改为name 、age、password
explain select * from student where name = 'admin' and age = '18' and passworld ='123456';

+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 88      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

结论二：顺序为name、age、passworld时依旧可以命中索引。

情况三：将查询顺序改为password、name、age
查询语句：mysql> explain select * from student where passworld = '123456' and name ='admin' and age = '18';

+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 88      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

结论三：将查询顺序改为password、name、age可以命中索引

情况四：将查询顺序改为age、name、passworld
查询语句：mysql> explain select * from student where age = '18' and name = 'admin' and passworld = '123456';

+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 88      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

将查询顺序改为age、name、passworld可以命中索引。

因本文篇幅原因：三列组成聚合索引，其他查询顺序情况不继续展示。直接在下方给出结论：

总结一;由上述可知在用聚合索引的全部列组成条件进行查询时，使用and连接时，列的顺序不会产生影响，即都可以命中索引。

2、聚合索引部分列查询

情况五：使用name 、passworld查询：
查询语句: explain select * from student where name = 'admin' and passworld = '123456'

+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 84      | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

结论五：使用name 、passworld查询可以命中索引。

情况六：使用name、age查询
查询语句：mysql> explain select * from student where name = 'admin' and age = '18';

+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 42      | const |    1 |    33.33 | Using index condition |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

结论七：使用name、age查询可以命中索引。

提示重点来了:
情况八：使用passworld、age查询
查询语句： explain select * from student where passworld = '123456' and age = '18';

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

* 结论八：使用passworld、age查询不能命中索引 *

结论七使用部分列都可以通过索引，情况八为什么不可以？我们继续看情况九
情况九：使用age、passworld查询
查询语句：mysql> explain select * from student where age = '18' and passworld = '123456' ;

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结论九：由上述情况可知，在使用部分列查询时，影响索引命中的是创建索引时，最左列。那么最左列的顺序是否对索引命中产生影响？看情况十。

情况十：使用passworld、name进行查询：
查询语句：explain select * from student where passworld='123456'and name = 'admin';

+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 84      | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

结论十：使用部分列进行查询时，在最左列存在时，与顺序无关。可命中索引。

3、使用聚合索引部分列和非索引列查询

情况十一：使用聚合索引和非索引列
查询语句:explain select * from student where name ='admin' and passworld = '123456' and age ='18' and sex ='1';

+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 88      | const,const,const |    1 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结论十一：使用聚合索引和非索引列可命中索引。

情况十二：使用name和sex查询

mysql> explain select * from student where name ='admin' and sex ='1';
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | ind_stu       | ind_stu | 42      | const |    1 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结论十二：使用最左列和非索引列可命中索引。

情况十三：使用非最左列和非索引列

mysql> explain select * from student where passworld = '123456' and sex ='1';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

```shell

mysql> explain select * from student where age = '18' and sex ='1';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where passworld = '123456'and age = '18' and sex ='1';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+-----
```-+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结论十三：使用非最左列和非索引列全表扫描。

总结：
     1、在使用聚合索引查询时，顺序不会产生印象，索引会生效。
     2、在使用聚合索引+非索引列时，索引生效。
     3、在使用部分聚合索引列时，最左列存在时，与顺序无关，索引生效。
     4、在使用部分聚合索引列时，最左列不存在时，与顺序无关索引失效。
     5、在使用部分聚合索引+非索引列时，最左列存在时，与顺序无关，索引生效。
     6、在使用部分聚合索引+非索引列时，最左列不存在时，与顺序无关，索引失效。