Mysql索引

索引分为主键索引、唯一索引、普通索引、全文索引

1.主键索引（三种方式）

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

– 在创建表的时候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar(30));

– 在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30), primary key(id));

– 创建表以后再添加主键

create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

2.唯一索引（三种方式）

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

– 在表定义时，在某列后直接指定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));

– 创建表以后再添加主键

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

3.普通索引（三种方式）

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

–在表的定义最后，指定某列为索引

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) 
);

–创建完表以后指定某列为普通索引

create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name);

– 创建一个索引名为 idx_name 的索引

create table user10(id int primary key, name varchar(20), email
varchar(30));
create index idx_name on user10(name);

4.全文索引

• 当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。
• MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。
• 如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

示例：

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;

使用全文索引： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

5.如何查询索引（三种方式）

第一种方法： show keys from 表名

mysql> show index from goods\G
*********** 1. row ***********
Table: goods <= 表名
Non_unique: 0 <= 0表示唯一索引
Key_name: PRIMARY <= 主键索引
Seq_in_index: 1
Column_name: goods_id <= 索引在哪列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE <= 以二叉树形式的索引
Comment:
1 row in set (0.00 sec)

第二种方法: show index from 表名;
第三种方法（信息比较简略）： desc 表名；

6.如何删除索引（三种方式）

第一种方法-删除主键索引： alter table 表名 drop primary key;
第二种方法-其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys
from 表名中的 Key_name 字段

alter table user10 drop index idx_name;

第三种方法方法： drop index 索引名 on 表名

drop index name on user8;