mysql null对组合索引的影响_MySQL_列值为null对索引的影响_实践

最新推荐文章于 2024-06-29 10:34:08 发布

三月十六

最新推荐文章于 2024-06-29 10:34:08 发布

阅读量526

点赞数

文章标签： mysql null对组合索引的影响

本文链接：https://blog.csdn.net/weixin_33805938/article/details/113909328

版权

本文通过实例探讨了在SQL查询中，where子句中对null值的判断是否影响索引使用。作者通过创建表、插入数据并对比加索引前后查询效率的变化，说明了针对单个字段的null判断可以利用索引，而多个字段同时判断则可能导致全表扫描。

摘要由CSDN通过智能技术生成

一.首先看一个我在某公众号看到的一个关于数据库优化的举措

二.如果where子句中查询的列执行了 “is null” 或者 “is not null” 或者 “<=> null” 会不会使用索引呢？

先列出结论：where子句中使用上述对null的判断，如果判断的列设置了索引，那就可以使用到索引

三.测试：

1.建表

1 CREATE TABLE`test_null_index` (2 `id` int(11) DEFAULT NULL,3 `mark` varchar(20) DEFAULT NULL,4 `name` varchar(11) DEFAULT NULL

5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插数据

create procedure test_null_index(in num int)BEGIN

DECLARE i int;set i=1;while (i

DOif mod(i,10)!=0 then

insert into test_null_index values (i,concat(‘aaa‘,i),null);else

insert into test_null_index values (null,concat(‘aaa‘,i),‘bbb‘);end if;set i=i+1;END while;END;

call test_null_index(10000);

3.没加索引时，type为All，全表扫描

mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

4.加上索引，可以看到，索引起作用了

mysql> create index idx_test_null ontest_null_index(id);

Query OK,0 rows affected (0.12sec)

Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

1 row in set (0.00 sec)

四.注意，只能使用针对一个字段的关于null的判断，如果同时在两个字段对null进行判断，还是会走全表扫描

1.测试，可以看到，在name字段加上索引，并查询name为空的语句，同样会走索引

mysql> create index idx_test_null2 ontest_null_index(name);

Query OK,0 rows affected (0.13sec)

Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE name is null;+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

1 row in set (0.00 sec)

2.同时针对id和name查询为空的语句，走的是全表扫描

mysql> explain SELECT * from test_null_index WHERE id is null or name is null;+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)