1、普通索引
-- 语法:CREATE INDEX `index_name` ON `table_Name`(column_Name);
create index `index_age` on `student`(age);
create index `index_name` on `student`(name);
-- 修改表结构添加普通索引
-- 语法:ALTER TABLE `table_Name` ADD INDEX `index_name`(columnName)
alter table `student` add index `index_age`(age);
-- 创建表时添加普通索引
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`name` varchar(30) DEFAULT NULL COMMENT '学生姓名',
`adress` varchar(30) DEFAULT NULL COMMENT '家庭住址',
`age` int DEFAULT NULL COMMENT '学生年龄',
`hobby` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `index_age`(age)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COMMENT='学生表';
2、主键索引
-- 修改表结构添加索引
-- 语法:ALTER TABLE `table_Name` ADD PRIMARY KEY(columnName)
alter table `student` add primary key(id);
-- 创建表时添加索引
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`name` varchar(30) DEFAULT NULL COMMENT '学生姓名',
`adress` varchar(30) DEFAULT NULL COMMENT '家庭住址',
`age` int DEFAULT NULL COMMENT '学生年龄',
`hobby` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COMMENT='学生表';
3、唯一索引
-- 唯一索引
-- 语法:CREATE UNIQUE INDEX `index_Name` on `table_Name` (column_Name);
CREATE UNIQUE INDEX unique_Name on `student` (name);
-- 语法:ALTER TABLE `table_Name` ADD UNIQUE `column_Name`(column_Name);
ALTER TABLE `student` ADD UNIQUE `unique_age`(age);
-- 创建表时添加唯一索引
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`name` varchar(30) DEFAULT NULL COMMENT '学生姓名',
`adress` varchar(30) DEFAULT NULL COMMENT '家庭住址',
`age` int DEFAULT NULL COMMENT '学生年龄',
`hobby` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `index_age`(age)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COMMENT='学生表';
4、全文索引
-- 全文索引
-- 语法:CREATE FULLTEXT INDEX `fullText_Name` ON `table_Name` (column_Name);
CREATE FULLTEXT INDEX `fullText_Name` ON `student` (name);
-- 语法:ALTER TABLE `table_Name` ADD FULLTEXT INDEX `fullText_Name`(column_Name);
ALTER TABLE `student` ADD FULLTEXT INDEX `fullText_name`(name);
-- 创建表时添加全文索引
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`name` varchar(30) DEFAULT NULL COMMENT '学生姓名',
`adress` varchar(30) DEFAULT NULL COMMENT '家庭住址',
`age` int DEFAULT NULL COMMENT '学生年龄',
`hobby` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT INDEX `index_name`(name)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COMMENT='学生表';
5、组合索引
-- 组合索引
-- 语法:CREATE INDEX `index_Name` ON `table_Name` (column ...);
CREATE INDEX `student_name_age` ON `student` (name,age);
-- ALTER TABLE `table_Name` ADD INDEX `index_Name` (column ...);
ALTER TABLE `student` ADD INDEX `student_name_age_hobby` (name,age,hobby);
-- 创建表时添加全文索引
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`name` varchar(30) DEFAULT NULL COMMENT '学生姓名',
`adress` varchar(30) DEFAULT NULL COMMENT '家庭住址',
`age` int DEFAULT NULL COMMENT '学生年龄',
`hobby` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `student_name_age_hobby`(name,age,hobby)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COMMENT='学生表';
5、删除索引
-- 删除索引
-- 语法:DROP INDEX `index_Name` ON `table_Name`;
DROP INDEX `index_age` ON `student`;
-- 语法:ALTER TABLE `table_Name` DROP INDEX `index_Name`
alter table `student` drop index `index_age`;
-- 删除主键索引
ALTER TABLE `school` DROP PRIMARY KEY;
6.查看索引
-- 查看指定表的全部索引
-- 语法:SHOW INDEX FROM table_Name [FROM DATABASE_Name];
SHOW INDEX FROM student;
7.查看执行计划详情
-- 查看执行计划 explain sql语句
explain select * from student where age = 18;