MySQL_列值为null对索引的影响_实践
2024-04-01 10:17:24
一.首先看一个我在某公众号看到的一个关于数据库优化的举措
二.如果where子句中查询的列执行了 “is null” 或者 “is not null” 或者 “<=> null” 会不会使用索引呢？
先列出结论：where子句中使用上述对null的判断，如果判断的列设置了索引，那就可以使用到索引
官方依据在：https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html
三.测试：
1.建表
CREATE TABLE `test_null_index` (
`id` int(11) DEFAULT NULL,
`mark` varchar(20) DEFAULT NULL,
`name` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.插数据
create procedure test_null_index(in num int)
BEGIN
DECLARE i int;
set i=1;
while (i<num)
DO
if 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;
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | test_null_index | ALL | NULL | NULL | NULL | NULL | 10589 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
4.加上索引，可以看到，索引起作用了
mysql> create index idx_test_null on test_null_index(id);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> explain SELECT * from test_null_index WHERE id is null;
+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | test_null_index | ref | idx_test_null | idx_test_null | 5 | const | 998 | Using where |
+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
四.注意，只能使用针对一个字段的关于null的判断，如果同时在两个字段对null进行判断，还是会走全表扫描
1.测试，可以看到，在name字段加上索引，并查询name为空的语句，同样会走索引
mysql> create index idx_test_null2 on test_null_index(name);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> explain SELECT * from test_null_index WHERE name is null;
+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | test_null_index | ref | idx_test_null2 | idx_test_null2 | 36 | const | 8994 | Using where |
+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+
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;
+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | test_null_index | ALL | idx_test_null,idx_test_null2 | NULL | NULL | NULL | 10589 | Using where |
+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)
最新文章
- HTML基本结构
- Chrome-Console（ Command Line API Reference）
- Hibernate学习总结
- js 的一点用法
- （转）linuxmint,ubuntu 下修改guake宽度方法
- Code First to an Existing Database
- git的使用说明详解
- bzoj2015 [Usaco2010 Feb]Chocolate Giving
- 模拟spring框架注入实现原理
- Activiti----hellowWorld(使用H2数据库)
- JSP知识点大纲图
- mysql 索引type介绍
- 使用Dockerfile制作JDK+tomcat镜像
- 学习windows编程 day4 之 矩形的操作
- Netty入门(4) - 附带的ChannelHandler和Codec
- uvalive 3126 Taxi Cab Scheme
- Ng第二课：单变量线性回归(Linear Regression with One Variable)
- stm32keilIDE遇到的bug
- osg::Vec2 Vec3 Vec4
- web资料收集