Mysql 索引增加与删除
【1】索引
索引,通俗理解,即目录。
之前说过,计算机是对现实世界的模拟。目录应用在数据库领域,即所谓的索引。
目录的作用显而易见,所以建立索引可以大大提高检索的速度。
但是,会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。
因更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
【2】索引分类
Mysql的索引分为普通索引、唯一索引、主键、全文索引。
有的地方分为单列索引(普通索引、唯一索引、主键索引)和组合索引、全文索引。个人认为太学术性。
(2.1)主键(PRIMARY KEY)
场景1:使用AUTO_INCREMENT
# 场景1: # 创建表SQL特点 # 1.sId列被AUTO_INCREMENT修饰 # 2.没有任何索引 DROP TABLE IF EXISTS students1; CREATE TABLE `students1` ( `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
SQL执行失败。
提示ERROR:
Incorrect table definition; there can be only one auto column and it must be defined as a key
分析原因:没有为sId列创建索引,即key
场景2:创建表同时创建主键
# 场景2: # 创建表SQL特点 # 1.sId列被AUTO_INCREMENT修饰 # 2.创建主键 # 3.主键列为sId DROP TABLE IF EXISTS students2; CREATE TABLE `students2` ( `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`sId`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb
SQL执行成功。
查询主键:
# 查询表索引 SHOW INDEX FROM students2;
结果:
说明:场景2为创建主键的方式之一。
场景3:创建表不添加任何索引
# 场景3: # 创建表SQL特点 # 1.sId列没有被AUTO_INCREMENT修饰 # 2.没有任何索引 DROP TABLE IF EXISTS students3; CREATE TABLE `students3` ( `sId` INT(20) UNSIGNED NOT NULL, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
执行成功。
创建主键(单列):
# 创建主键(单列) ALTER TABLE students3 ADD PRIMARY KEY (sId);
查询主键:
# 查询主键 SHOW INDEX FROM students3;
查询结果:
删除主键:
# 删除主键 ALTER TABLE students3 DROP PRIMARY KEY;
创建主键(多列):
# 创建主键(多列) ALTER TABLE students3 ADD PRIMARY KEY (sId, sName);
查询主键:
# 查询主键 SHOW INDEX FROM students3;
查询结果:
删除主键,方法同上。
若不删除这个多列主键,试图再添加一个主键:
# 创建第二个主键索引 ALTER TABLE students3 ADD PRIMARY KEY (sId, sStuId);
执行报错:
查询:ALTER TABLE students3 ADD PRIMARY KEY (sId, sStuId) 错误代码: 1068
Multiple PRIMARY KEY defined
多个主键定义错误!
分析以上三种场景,对主键总结:
(a)主键列的值必须是唯一的
(b)主键列的值不允许为空
(c)主键保证记录的唯一性
(d)主键可以由多列组成
(e)每张表只允许存在一个主键
(2.2)唯一索引
场景1:创建表时创建唯一索引
# 场景1:建表时创建唯一性索引 DROP TABLE IF EXISTS students1; CREATE TABLE `students1` ( `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`sId`), UNIQUE INDEX `idx_name` (`sName`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; # 查询表索引 SHOW INDEX FROM students1;
查询唯一索引结果:
提示:此为创建唯一索引方式之一
场景2:创建表时未创建唯一索引
如果创建表时,忘记创建唯一索引,那么可以通过两种方式添加:
创建唯一索引方式二:
# 创建唯一索引(单列) CREATE UNIQUE INDEX uidx_name ON students2 (sName); # 查询唯一索引(单列) SHOW INDEX FROM students2; # 删除唯一索引(单列) DROP INDEX uidx_name ON students2; # 查询唯一索引(单列) SHOW INDEX FROM students2;
查询唯一索引结果:
创建唯一索引方式三:
# 创建唯一索引(单列) ALTER TABLE students2 ADD UNIQUE INDEX uidx_name (sName); # 查询唯一索引(单列) SHOW INDEX FROM students2; # 删除唯一索引(单列) ALTER TABLE students2 DROP INDEX uidx_name; # 查询唯一索引(单列) SHOW INDEX FROM students2;
查询唯一索引结果:
创建多列唯一索引:
# 创建唯一索引(多列) ALTER TABLE students2 ADD UNIQUE INDEX uidx_addr_age (sAddr, sAge); # 查询唯一索引(多列) SHOW INDEX FROM students2; # 删除唯一索引(多列) ALTER TABLE students2 DROP INDEX uidx_addr_age; # 查询唯一索引(多列) SHOW INDEX FROM students2;
查询多列唯一索引结果:
创建多个唯一索引:
# 创建唯一索引(单列) CREATE UNIQUE INDEX uidx_name ON students2 (sName); # 创建唯一索引(多列) ALTER TABLE students2 ADD UNIQUE INDEX uidx_addr_age (sAddr, sAge); # 查询唯一索引 SHOW INDEX FROM students2; # 删除索引uidx_name DROP INDEX uidx_name ON students2; # 删除索引uidx_addr_age DROP INDEX uidx_addr_age ON students2;
查询唯一索引结果(uidx_name 和 uidx_addr_age两个唯一索引):
分析以上两种场景,对唯一索引总结:
(a)唯一索引的目的不是为了提高访问速度,而只是为了避免数据出现重复。
(b)索引列的值必须唯一,但允许有NULL。如果唯一索引是组合列索引,则组合列的值必须唯一。
(c)每张表可以创建多个唯一索引。
(2.3)普通索引
场景1:创建表时创建普通索引
# 场景1:创建表时创建普通索引 DROP TABLE IF EXISTS students1; CREATE TABLE `students1` ( `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`sId`), INDEX `idx_name` (`sName`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; # 查询普通索引 SHOW INDEX FROM students1;
查询创建索引:
场景2:创建表时未创建普通索引
# 场景2:创建表时未创建普通索引 DROP TABLE IF EXISTS students2; CREATE TABLE `students2` ( `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`sId`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
若创建表时未创建普通索引,可以通过以下两种方式创建普通索引:
创建普通索引方式二:
# 创建普通索引(单列) CREATE INDEX idx_name ON students2 (sName); # 查询普通索引(单列) SHOW INDEX FROM students2; # 删除普通索引(单列) DROP INDEX idx_name ON students2; # 查询普通索引(单列) SHOW INDEX FROM students2;
查询创建索引:
创建普通索引方式三:
# 创建普通索引(单列) ALTER TABLE students2 ADD INDEX idx_addr (sAddr); # 查询普通索引(单列) SHOW INDEX FROM students2; # 删除普通索引(单列) ALTER TABLE students2 DROP INDEX idx_addr; # 查询普通索引(单列) SHOW INDEX FROM students2;
查询创建索引:
创建普通多列索引:
# 创建普通索引(多列) ALTER TABLE students2 ADD INDEX idx_addr_age (sAddr, sAge); # 查询普通索引(多列) SHOW INDEX FROM students2; # 删除普通索引(多列) ALTER TABLE students2 DROP INDEX idx_addr_age; # 查询普通索引(多列) SHOW INDEX FROM students2;
查询创建索引:
创建多个普通索引:
# 创建多个普通索引 # 创建普通索引(单列) CREATE INDEX idx_name ON students2 (sName); # 创建普通索引(多列) ALTER TABLE students2 ADD INDEX idx_addr_age (sAddr, sAge); # 查询普通索引 SHOW INDEX FROM students2; # 删除索引idx_name DROP INDEX idx_name ON students2; # 删除索引idx_addr_age DROP INDEX idx_addr_age ON students2; # 查询普通索引 SHOW INDEX FROM students2;
查询创建索引:
分析以上两种场景,对普通索引(最基本的索引,没有任何限制)总结:
(a)索引列的值可以为NULL,可以重复。
(b)每张表可以创建多个普通索引。
(c)普通索引同样也可以创建多列。
(2.4)全文索引
场景1:创建表时创建全文索引
# 场景1:创建表时创建全文索引 DROP TABLE IF EXISTS students1; CREATE TABLE `students1` ( `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`sId`), FULLTEXT ft_name_stuid (sName, sStuId) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; # 查询表索引 SHOW INDEX FROM students1;
查询索引结果:
场景2:创建表时未创建全文索引
# 场景2:创建表时未创建全文索引 DROP TABLE IF EXISTS students1; CREATE TABLE `students1` ( `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sName` VARCHAR(100) NOT NULL, `sAge` INT(10) NOT NULL, `sAddr` VARCHAR(200) DEFAULT NULL, `sGrade` INT(10) DEFAULT NULL, `sStuId` VARCHAR(20) DEFAULT NULL, `sSex` INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`sId`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; # 查询表索引 SHOW INDEX FROM students1;
若创建表时未创建索引,可以通过以下两种方式创建:
创建全文索引方式二:
# 创建全文索引方式二: # 创建全文索引(多列) CREATE FULLTEXT INDEX ft_name_addr ON students2 (sName, sAddr); # 查询全文索引(多列) SHOW INDEX FROM students2; # 删除全文索引(多列) DROP INDEX ft_name_addr ON students2; # 查询全文索引(多列) SHOW INDEX FROM students2;
查询全文索引:
创建全文索引方式三:
# 创建全文索引方式三: # 创建全文索引(多列) ALTER TABLE students2 ADD FULLTEXT INDEX ft_name_stuid (sName, sStuId); # 查询全文索引(多列) SHOW INDEX FROM students2; # 删除全文索引(多列) ALTER TABLE students2 DROP INDEX ft_name_stuid; # 查询全文索引(多列) SHOW INDEX FROM students2;
查询全文索引:
创建多个全文索引:
# 创建多个全文索引 # 创建全文索引(单列) CREATE FULLTEXT INDEX ft_name_stuid ON students2 (sName, sStuId); # 创建全文索引(多列) ALTER TABLE students2 ADD FULLTEXT INDEX ft_addr (sAddr); # 查询全文索引 SHOW INDEX FROM students2; # 删除索引ft_name_stuid DROP INDEX ft_name_stuid ON students2; # 删除索引ft_addr DROP INDEX ft_addr ON students2; # 查询索引 SHOW INDEX FROM students2;
查询全文索引:
添加整型字段的全文索引:
ALTER TABLE students2 ADD FULLTEXT INDEX ft_age (sAge);
执行失败:
查询:ALTER TABLE students2 ADD FULLTEXT INDEX ft_age (sAge)错误代码: 1283
COLUMN 'sAge' cannot be part of FULLTEXT INDEX
说明:全文索引只可针对字符串类型的字段。
分析以上两种场景,对全文索引总结:
(a)全文索引只针对字段类型为字符串的列。
(b)全文索引可以为多列创建。
【3】总结
(3.1)增加索引:
// 普通索引 alter table table_name add index index_name (column_list); // 唯一索引 alter table table_name add unique index uindex_name (column_list); // 主键索引 alter table table_name add primary key (column_list); // 全文索引 alter table table_name add fulltext index ftindex_name (column_list); // 普通索引 create index index_name on table_name (column_list); // 唯一索引 create unique index index_name on table_name (column_list); // 全文索引 create fulltext index ftindex_name on table_name (column_list);
(3.2)删除索引:
// 非主键索引 drop index index_name on table_name; alter table table_name drop index index_name; // 删除主键 alter table table_name drop primary key;
Good Good Study, Day Day Up.
顺序 选择 循环 总结