1.主键索引
create table `example1` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例1';
2.唯一索引
create table `example2` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`student_id` int unsigned NOT NULL DEFAULT 0 COMMENT '学号',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_sid` (`student_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例2';
3.普通索引
- 建立索引应在区分度大的字段上,不要在低基数列上建立索引,比如 性别
create table `example3` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`student_id` int unsigned NOT NULL DEFAULT 0 COMMENT '学号',
`grade` int unsigned NOT NULL DEFAULT 0 COMMENT '分数',
PRIMARY KEY (`id`),
KEY `idx_grade` (`grade`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例3';
4.组合索引
- 区分度大的列放在前,有范围性的列放在后
- 联合索引
(a, b, c)
相当于(a)
, (a, b )
, (a, b, c)
- 合理使用索引覆盖减少IO,避免排序
- 单个索引字段不应超过5个
create table `example3` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`student_id` int unsigned NOT NULL DEFAULT 0 COMMENT '学号',
`grade` int unsigned NOT NULL DEFAULT 0 COMMENT '分数',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_sid_grede_ctime` (`student_id`, `grade`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例3';
网友评论