【MySQL】 索引

1.为什么要有索引？

索引：提高数据库的性能，它的价值，在于提高一个海量数据的检索速度

• 常见索引分为：
  • 主键索引(primary key)
  • 唯一索引(unique)
  • 普通索引(index)
  • 全文索引(fulltext)

2.索引的操作

2.1 索引创建的原则

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

2.2 创建主键索引

• 主键索引的特点：
  • 一个表中，最多有一个主键索引，当然可以使用复合主键
  • 主键索引的效率高（主键不可重复）
  • 创建主键索引的列，它的值不能作为null，而且不能重复
  • 主键索引的列基本上是int

// 1.在创建表的时候，直接在字段名后指定 primary key
create table test1(id int primary key,name varchar(20) not null);

// 2.在创建表的最后，指定某列/某几列为主键索引
create table test1(id int,name varchar(20),primary key(id));

// 3.创建表以后再添加主键
create table test1(id int,name varchar(20));
alter table test1 add primary key(id);

2.3 创建唯一索引

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

// 1.在表定义时，在某列后直接指定unique唯一属性
create table test1(id int primary key,name varchar(20) unique);

// 2.创建表时，在表后面指定某列/某几列作为unique
create table test1(id int primary key,name varchar(20),unique(name));

// 3.创建表以后添加唯一键
create table test1(id int primary key,name varchar(20));
alter table test1 add unique(name);

2.4 创建普通索引

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

// 1.在表的定义最后，指定某列为索引
create table test1(id int primary key,namevarchar(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.创建一个索引名为myindex的索引
create table test1(id int primary key,name varchar(20),email varchar(30));
create index myindex on test1(name);

2.5 查询索引

//方法1：show keys from 表名;
mysql> show keys from test1\G
*************************** 1. row ***************************
        Table: test1          //表名
   Non_unique: 0              //0表示唯一索引
     Key_name: PRIMARY        //主键索引
 Seq_in_index: 1
  Column_name: id             //索引在那列
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE         //以二叉树形式的索引
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

//方法2：show index from 表名;

//方法3：desc 表名;

2.6 删除索引

//1,删除主键索引：
alter table 表名 drop primary key;

//2.删除其他索引：
alter table 表名 drop index 索引名;

//方法3：
drop index 索引名 on 表名;