一、MySQL索引概念
- 索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,有500也是目录,它当然效率低,目录是要占纸张的,而索引是要占磁盘空间的。
- 索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
- 创建索引时,需要确保该索引是应用在 SQL 查询语句的条件(一般作为where子句的条件)。
- 索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
- 优点:大大提高MySQL的检索速度;
- 缺点:降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
- 建立索引会占用磁盘空间的索引文件。
二、常见索引
- 主键索引、唯一索引、普通索引、全文索引、组合索引
- PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` )
- UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`col`)
- INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name (`col`)
- FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `col` )
- 组合索引 ALTER TABLE `table_name` ADD INDEX index_name (`col1`, `col2`, `col3` )
三、创建索引的方式
1. 创建索引
CREATE INDEX indexName ON mytable(username(length));//普通索引
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
CREATE UNIQUE INDEX indexName ON mytable(username(length)); //唯一索引。索引列的值必须唯一,但允许有空值。
2. 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName);
ALTER table mytable ADD UNIQUE [indexName] (username(length));
3. 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
4. 删除索引的语法
DROP INDEX [indexName] ON mytable;
5. 显示索引信息
SHOW INDEX FROM table_name; \G //通过添加\G格式化输出信息
四、Mysql各种索引区别:
- 普通索引:最基本的索引,没有任何限制
- 唯一索引:与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
- 主键索引:它是一种特殊的唯一索引,不允许有空值。
- 全文索引:仅可用于 MyISAM表,针对较大的数据,生成全文索引很耗时耗空间。
- 组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。创建复合索引时应该将最常用(频率)作限制条件的列放在最左边,依次递减。
- 组合索引最左字段用in是可以用到索引的,最好explain一下select。