无棣县建设局网站,网站站点建立,公司内部网站,济宁人才网招聘信息网在MySQL中#xff0c;即使表中没有数据#xff0c;查询优化器也会考虑使用索引来执行查询。但在某些情况下#xff0c;查询优化器可能会选择不使用索引#xff0c;这通常是基于成本效益分析的结果。 表中没有任何数据时#xff0c;无论是否使用索引#xff0c;查询结果都…在MySQL中即使表中没有数据查询优化器也会考虑使用索引来执行查询。但在某些情况下查询优化器可能会选择不使用索引这通常是基于成本效益分析的结果。 表中没有任何数据时无论是否使用索引查询结果都是一样的。但直接扫描表全表扫描可能比使用索引更快因为即使是空表访问索引也需要一定的开销但并不会带来性能提升。 create table test(id int primary key, name varchar(10));
Query OK, 0 rows affected (0.10 sec)mysql explain select * from test where id1;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql insert into test value(1, a);
Query OK, 1 row affected (0.01 sec)mysql explain select * from test where id1;
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)使用如下SQL语句建表并使用b列作为条件查询。按照索引最左匹配原则此时应该无法使用索引。但如果表中只包含主键列和索引列此时查询优化器还是会使用索引。示例如下
CREATE TABLE t1 (id int NOT NULL,a int DEFAULT NULL,b int DEFAULT NULL,c int DEFAULT NULL,PRIMARY KEY (id),KEY inx_abc (a,b,c)
);CREATE TABLE t2 (id int NOT NULL,a int DEFAULT NULL,b int DEFAULT NULL,c int DEFAULT NULL,d int DEFAULT NULL,PRIMARY KEY (id),KEY inx_abc (a,b,c)
);# 插入两条数据
insert into t1 value(1, 1, 1, 1);
insert into t2 value(1, 1, 1, 1, 1); explain select * from t1 where b1;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | index | inx_abc | inx_abc | 15 | NULL | 1 | 100.00 | Using where; Using index |
----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec) explain select * from t2 where b1;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)同理以下SQL也有类似现象 explain select * from t1 where b1 and c1;explain select * from t1 where c1;explain select * from t2 where b1 and c1;explain select * from t2 where c1;
