表定义如下:
CREATE TABLE course_type
(id
int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',publisher_id
int(10) unsigned DEFAULT '0' COMMENT '出版方id',uid
int(10) unsigned DEFAULT '0' COMMENT '创建者uid',name
varchar(20) DEFAULT '' COMMENT '名称',ctime
int(5) unsigned DEFAULT '0' COMMENT '创建时间',utime
int(5) unsigned DEFAULT '0' COMMENT '最后修改时间',status
tinyint(1) DEFAULT '0' COMMENT '状态:0:正常;-1:已删除;',
KEY id
(id
) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 COMMENT='课程分类信息表';
业务查询逻辑语句如下:
explain SELECT * FROM t_x_course_type WHERE publisher_id = 198 AND status = 0 order by id;(前端列表页面,查询频繁度高)
explain SELECT * FROM t_x_course_type WHERE uid = 30258120 AND status = 0 order by id;(用户添加分类页面)
explain SELECT count(id
) FROM t_x_course_type
WHERE uid
= 30258120 AND status = 0;(用户添加分类页面)
explain SELECT count(id) FROM t_x_course_type WHERE uid = 30258120 AND publisher_id = 198 AND name = '中国武术1' AND status = 0;(用户添加分类页面)
请大神帮忙看看如上这种业务场景,如何为数据库添加索引效率最优?谢谢指点!
一条条来
1 SELECT * FROM t_x_course_type WHERE publisher_id = 198 AND status = 0 order by id;
索引为
publisher_id
,status
,id
explain
2 SELECT * FROM t_x_course_type WHERE uid = 30258120 AND status = 0 order by id;
索引为
uid
,status
,id
explain
3 SELECT count(id) FROM course_type WHERE uid = 30258120 AND status = 0;
索引不用加新的,使用第二条的即可,因为索引有覆盖
explain
4 explain SELECT count(id) FROM course_type WHERE uid = 30258120 AND publisher_id = 198 AND name = '中国武术1' AND status = 0;
索引不用加,mysql会自动从第1条索引和第2条索引中选出能筛选出较少数据的一条作为索引查询,一个用户下面的 name 数据应该不多