请帮忙看看如下这样的业务场景，如何设计MYSQL索引，查询效率最优？

Question

表定义如下：

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;(用户添加分类页面)

请大神帮忙看看如上这种业务场景，如何为数据库添加索引效率最优？谢谢指点！

Answer 1

一条条来

1 SELECT * FROM t_x_course_type WHERE publisher_id = 198 AND status = 0 order by id;

索引为publisher_id, status, id

explain

1    SIMPLE    course_type        ref    publisher_id    publisher_id    7    const,const    1    100.00    Using index condition

2 SELECT * FROM t_x_course_type WHERE uid = 30258120 AND status = 0 order by id;

索引为uid,status,id

explain

1    SIMPLE    course_type        ref    uid    uid    7    const,const    1    100.00    Using index condition

3 SELECT count(id) FROM course_type WHERE uid = 30258120 AND status = 0;

索引不用加新的，使用第二条的即可,因为索引有覆盖

explain

1    SIMPLE    course_type        ref    uid    uid    7    const,const    1    100.00    Using index

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 数据应该不多

1    SIMPLE    course_type        ref    publisher_id,uid    publisher_id    7    const,const    1    50.00    Using where