MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。
索引分单列索引和组合索引:
- 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
- 组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)
索引的优点
- 创建索引可以大幅提高系统性能,帮助用户提高查询的速度;
- 通过索引的唯一性,可以保证数据库表中的每一行数据的唯一性;
- 可以加速表与表之间的链接;
- 降低查询中分组和排序的时间。
索引的缺点
- 索引的存储需要占用磁盘空间;
- 当数据的量非常巨大时,索引的创建和维护所耗费的时间也是相当大的;
- 当每次执行CRU操作时,索引也需要动态维护,降低了数据的维护速度。
普通索引
创建索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
CREATE INDEX indexName ON tableName (columnName)
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定
CREATE TABLE tableName ( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
删除索引的语法
DROP INDEX [indexName] ON tableName ;
唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(可以有多个空值)。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
创建索引
CREATE UNIQUE INDEX indexName ON tableName (columnName(length))
修改表结构
ALTER table tableName ADD UNIQUE [indexName] (columnName(length))
创建表的时候直接指定
CREATE TABLE tableName ( ID INT NOT NULL, columnName VARCHAR(16) NOT NULL, UNIQUE [indexName] (columnName(length)) );
实例
使用ALTER 命令添加和删除索引
四种方式来添加数据表的索引:
- ALTER TABLE table_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,索引值必须是唯一的,且不能为NULL。
- ALTER TABLE table_name ADD UNIQUE index_name (column_list): 该语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
- ALTER TABLE table_name ADD INDEX index_name (column_list): 该语句创建普通索引,索引值可出现多次。
- ALTER TABLE table_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
使用DROP 子句来删除索引
- ALTER TABLE table_name ADD PRIMARY KEY (column_list): 该语句用来删除主键,删除主键时只需指定PRIMARY KEY。
- ALTER TABLE table_name DROP INDEX index_name: 该语句用来删除一个索引,删除索引时,必须知道索引名。
显示索引信息
使用 SHOW INDEX 命令来列出表中的相关的索引信息。
SHOW INDEX FROM table_name
实例