MySQL | 索引

1. 前言

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调SQL，只要执行正确的create index，查询速度就可能提高成百上千倍。但是天下吗没有免费的午餐，查询速度的提高是以插入，更新，删除的速度为代价的，这些写操作，增加了大量的IO所以它的价值，在于提高一个海量数据的检索速度。

常见索引：

1. 主键索引(PRIMARY KEY)
2. 唯一索引(UNIQUE)
3. 普通索引(INDEX)
4. 全文索引(FULLTEXT) -- 解决中子文索引问题

2. 创建主键索引

1. 第一种方式

在创建表的时候，直接在字段名后指定PRIMARY KEY
create table user1 (
    id int primary key, name varchar(20)
);

1. 第二种方式

在创建表的最后，指定某列或某几列为主键索引
create table user3 (
    id int,
    name varchar(30),
    primary key(id)
);

1. 第三种方式

创建表以后在添加主键
create  table  user2 (
    id int,
    name varchar(30)
);

alter table user2 add primary key(id);

主键索引的特点

1. 一个表中，最多有一个主键索引
2. 主键索引的效率高(主键不可重复)
3. 创建主键索引的列，它的值不能为null，且不能重复
4. 主键索引的列基本上是int

3. 唯一索引的创建

创建索引的三种方式

1. 在表定义时，在某列后直接指定unique唯一属性。
create table user4(
    id int primary key,
    name varchar(30) unique
);
2. 创建表时，在表的后面指定某列或某几列为unique
create table user5(
    id int primary key,
    name varchar(30),
    unique(name)
);
3. 通过add的方式添加索引
create table user6(
    id int primary key,
    name varchar(30)
);

alter table user6 add unique(name);

唯一索引的特点

1. 一个表中，可以有多个唯一索引
2. 查询效率高
3. 如果在某一列建立唯一索引，必须保证这列不能有重复的数据
4. 如果一个唯一索引上指定not null，等价于主键索引

4. 普通索引的创建

创建普通索引的三种方式

1. 在表的定义最后，指定某列为索引
create table user8 (
    id int primary key ,
    name varchar(20),
    email varchar(30),
    index(name)
);
2. 创建完表以后指定某列为普通索引
create table test1 (
    id int primary key,
    name varchar(20),
    email varchar(30)
);

alter table test1 add index(name);
3. 创建一个索引名为idx_name的索引
create table test2 (
    id int primary key ,
    name varchar(20),
    email varchar(30)
);

create index idx_name on test2(name);

普通索引的特点

1. 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
2. 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

5. 查询索引

三种方法

1. show keys from 表名

show keys from user1;

1. show index from 表名

show index from user1;

1. desc 表明

desc user1;

6. 删除索引

1. 删除主键索引

alter table user1 drop primary key;

1. 其他索引的删除

alter table table_name drop index index_name

alter table user1 drop index `PRIMARY`;

7. 索引创建原则

1. 比较频繁作为查询条件
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合作为创建索引
4. 不会出现在where子句的字段不该创建索引