环境:mysql5.6
数据量:900W
表结构:
CREATE TABLE `platform_comment_info` (
`id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT '数据库主键',
`kid` bigint(18) unsigned NOT NULL COMMENT '业务主键唯一标识',
`tenant_id` varchar(32) DEFAULT NULL COMMENT '租户标识',
`create_user_id` varchar(64) NOT NULL COMMENT '创建人ID',
`create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`last_update_user_id` varchar(64) DEFAULT NULL COMMENT '修改人ID',
`last_update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`create_user_type` varchar(32) DEFAULT '0' COMMENT '创建用户类型 0正常用户 1马甲',
`del_flag` tinyint(1) DEFAULT '0' COMMENT '删除标识0 正常 1删除',
`target_kid` bigint(18) DEFAULT NULL COMMENT '目标资源标识',
`target_type` varchar(32) DEFAULT NULL COMMENT '目标资源类型',
`root_kid` bigint(18) DEFAULT NULL COMMENT '评论根标识',
`parent_kid` bigint(18) DEFAULT NULL COMMENT '评论父级标识',
`content` text COMMENT '评论内容',
`comment_type` varchar(32) DEFAULT '0' COMMENT '评论类型 0正常评论 1灌水评论',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_kid` (`kid`) USING BTREE,
KEY `idx_root_kid` (`root_kid`) USING BTREE,
KEY `idx_target` (`target_kid`,`del_flag`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9388792 DEFAULT CHARSET=utf8mb4;
==============================================================================
示例1:
SELECT * FROM platform_comment_info
WHERE target_kid = 1429522297 AND del_flag = 0
LIMIT 10;
平均执行时长:0.025s
本条SQL使用到的索引为: idx_target
示例2:
SELECT * FROM platform_comment_info
WHERE target_kid = 1429522297 AND del_flag = 0 AND root_kid=226669572415494
LIMIT 10
平均执行时长:0.025
本条SQL使用到的索引为:root_kid ,idx_target
进行的索引合并,使用到两个索引
示例3:
SELECT * FROM platform_comment_info
WHERE target_kid = 1429522297 AND del_flag = 0 AND root_kid is null
LIMIT 10;
平均执行时长:1.3s
本条SQL使用到的索引为:root_kid ,idx_target
从执行计划上看,进行的索引合并,使用到两个索引,但是,root_kid索引中并没有null值,所以无法使用到索引,需要进行全表扫描。
原理:
单列索引无法存储NULL值。索引是有序的,NULL值进入索引时,无法确定其应该放在哪里。如果需要把空值存入索引,方法有二:其一,把NULL值转为一个特定的值,在WHERE中检索时,用该特定值查找。其二,建立一个复合索引。
查询时,采用is null条件时,不能利用到索引,只能全表扫描。
注意:Mysql中NULL其实是占用空间的,''空字符串是不占用空间的。
建议:在创建字段时将列设置为NOT NULL ,并在需要存储为NULL的时候指定一个特定的值。在使用where ...is null时替换为where ...= 特定的值。