【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.
顺序 选择 循环 总结