创建索引
MySQL 创建索引的语法如下:
- CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
- [USING index_type]
- ON table_name (index_col_name,...)
其中对应的语法变量信息如下:
[UNIQUE|FULLTEXT|SPATIAL]
中括号中的这三个关键字表示创建的索引类型, 它们分别表示唯一索引全文索引空间索引三种不同的索引类型如果我们不指定任何关键字, 则默认为普通索引
index_name
index_name 表示索引的名称, 由用户自行定义, 以便于以后对该索引进行修改等管理操作
index_type
index_type 表示索引的具体实现方式, 在 MySQL 中, 有两种不同形式的索引 BTREE 索引和 HASH 索引在存储引擎为 MyISAM 和 InnoDB 的表中只能使用 BTREE, 其默认值就是 BTREE; 在存储引擎为 MEMORY 或者 HEAP 的表中可以使用 HASH 和 BTREE 两种类型的索引, 其默认值为 HASH
index_col_name
index_col_name 表示需要创建索引的字段名称, 我们还可以针对多个字段创建复合索引, 只需要在多个字段名称之间以英文逗号隔开即可
此外, 对于 CHAR 或 VARCHAR 类型的字段, 我们还可以只使用字段内容前面的一部分来创建索引, 只需要在对应的字段名称后面加上形如 (length) 的指令即可, 表示只需要使用字段内容前面的 length 个字符来创建索引在这里, 我们以 User 表的 username 字段 (类型为 VARCHAR(50)) 为例, 使用 username 字段的 6 个字符前缀来创建索引
CREATE INDEX idx_user_username ON user (username(6));
由于多数字段的前 6 个字符通常不同, 所以此索引不会比使用字段的全部内容创建的索引速度慢很多另外, 使用字段的一部分创建索引可以使索引文件大大减小, 从而节省了大量的磁盘空间, 有可能提高 INSERT 操作的速度
在 MySQL 中, 前缀长度最大值为 255 字节对于存储引擎为 MyISAM 或 InnoDB 的数据表, 前缀最长为 1000 字节
必须注意的是, 在 MySQL 中, 对于 TEXT 和 BLOB 这种大数据类型的字段, 必须给出前缀长度 (length) 才能成功创建索引
备注 1: 上述创建索引的语法还具有以下变体:
- ALTER TABLE table_name
- ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]
备注 2: 在 MySQL 中, 只有当数据表的存储引擎为 MyISAM, InnoDB 或 BDB 类型时, 你才能向具有 NULL 值的列或者数据类型为 TEXT 或 BLOB 的列添加索引
删除索引
在 MySQL 中删除索引的方法非常简单, 其完整语法如下:
-- 删除指定表中指定名称的索引
- ALTER TABLE table_name
- DROP INDEX index_name;
在这里, 我们编写 SQL 语句将上面创建索引的例子中的索引 idx_user_username 删除掉, 代码详情如下:
-- 删除名称为 idx_user_username 的索引
- ALTER TABLE user
- DROP INDEX idx_user_username;
修改索引
在 MySQL 中并没有提供修改索引的直接指令, 一般情况下, 我们需要先删除掉原索引, 再根据需要创建一个同名的索引, 从而变相地实现修改索引操作
-- 先删除
- ALTER TABLE user
- DROP INDEX idx_user_username;
-- 再以修改后的内容创建同名索引
CREATE INDEX idx_user_username ON user (username(8));
查看索引
在 MySQL 中, 要查看某个数据库表中的索引也非常简单, 只需要使用以下两个命令中的任意一种即可
-- 如果查看索引前, 没有使用 user db_name 等命令指定具体的数据库, 则必须加上 FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
-- 如果查看索引前, 没有使用 user db_name 等命令指定具体的数据库, 则必须加上 db_name. 前缀
SHOW INDEX FROM [db_name.]table_name
来源: http://www.bubuko.com/infodetail-2503797.html