索引:提高数据库的性能。
本质就是对添加索引的字段进行拷贝,并把它放进B+树(MySQL)中,消耗空间来获取时间。
所以说每给表增加一个索引,就会多一份拷贝,也就浪费一部分空间。
添加索引也要选择合适的字段:
- 经常查找的字段(where)
- 唯一的字段
- 不为null
优点:查找效率高
缺点:创建索引耗时、占用物理空间、维护索引(树)浪费资源(添加、删除等操作)
索引失效的几个例子:
- 用or查询
- 不用索引字段查询
- 模糊查询(like %)
- MySQL预计全表查找优于索引的时候
常见的索引:
- 主键索引(primary key)
- 唯一索引(unique)
- 普通索引(index)
- 全文索引(fulltext)
1. 主键索引(primary key)
一个表中,最多有一个主键索引,当然可以使符合主键
主键索引的效率高(主键不可重复)
创建主键索引的列,它的值不能为null,且不能重复
主键索引的列基本上是int
1.1 创建
-
在创建表的时候,直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30)); -
在创建表的最后,指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id)); -
创建表以后再添加主键
alter table user3 add primary key(id);
1.2 删除主键索引
alter table 表名 drop primary key;
2. 唯一索引(unique)
一个表中,可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引,必须保证这列不能有重复数据
如果一个唯一索引上指定not null,等价于主键索引;
2.1 创建
-
在表定义时,在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique); -
创建表时,在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name)); -
alter table user6 add unique(name);
3. 普通索引(index)
一个表中,可以有多个唯一索引查询效率高
如果在某一列建立唯一索引,必须保证这列不能有重复数据如果一个唯一索引上指定not null,等价于主键索引;
3.1 创建
-
创建一个索引名为 idx_name 的索引
create index idx_name on user10(name); -
创建表时,在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), index(name)); -
alter table user6 add index(name);
4. 全文索引(fulltext)
当对文章字段或有大量文字的字段进行检索时,会使用到全文索引.
4.1 创建
- 创建表时,在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), fulltext(name));
删除索引(通用)
- alter table 表名 drop index 索引名;
- drop index 索引名 on 表名
查看索引
- show keys from 表名
- show index from 表名;
- desc 表名;