【MySQL】索引

索引的特性

使查询的速度增加，但是插入、更新、删除的速度减少.

索引的分类

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)

索引的创建方式

• 1、创建表的时候指定索引，例如create table 表名 ( […], INDEX [索引的名字] (列的列表) );
• 2、创建索引，例如 create index <索引的名字> on table_name (列的列表);
• 3、修改表，例如 alter table 表名 add index[索引的名字] (列的列表);

主键索引(primary key)

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

create table student(id int primary key ,name varchar(10));

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

create table student (id int,name varchar(10),primary key(id));

• 创建表以后再添加主键

create table student (id int ,name vachar(10));
alter table student add primary key(id);

唯一索引(unique)

• 在表定义时,在某列后直接指定unique唯一属性

create table student (id int primary key ,name varchar(10) unique);

• 创建表时,在表的后面指定某列或某几列为unique

create table student (id int primary key ,name varchar (10),unique(name));

• 在创建表之后在创建唯一索引

create table student (id int primary key ,name varchar(10));
alter table student add unique(name);

普通索引(index)

• 在表的定义最后,指定某列为索引

create table student (id int primary key ,name varchar(20),index (name));

• 创建完表之后在指定某列为普通索引

create table (id int primary key ,name varchar(10));
alter table student add index (name);

• 创建一个索引名为XXX的索引

create table (id int primary key ,name varchar(10));
create index idx_name on student (name);

全文索引(fulltext)

在MySQL5.6以前的版本,只有MySIAM引擎支持全文索引,在5.6以后的版本中MySIAM和InnoDB存储引擎均支持全文索引.
只有字段的数据类型为char、varchar、text及其系列才可以创建全文索引.
`测试或使用全文索引的时候,要先查看一下自己的MySQL版本,存储引擎和数据类型是否支持全文索引.

• 创建表时创建权威呢索引

create table student (id int,
name varchar(10),body text, fulltext (name,body))engine=MySIAM;

• 在已存在的表上创建全文索引

create fulltext index student_name_body on student(name ,body);

• 通过sql语句alter table 创建全文索引

alter table student add fulltext index student_name_body(name,body);

• 使用全文索引

select * from student where match (name,body) against('XXX XXX')

查询索引

-- 第一种方式
show keys from 表名;
-- 第二种方式
show index from 表名;
-- 第三种方式
desc 表名;

删除索引

--删除主键索引
alter table 表名 drop primary key;
-- 其他索引的删除
alter table 表名 drop index 索引名;
-- 第三种
drop index 索引名 on 表名

索引创建原则

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

四种索引的特点

索引	特点
主键索引(primary key)	1.一个表中，最多有一个主键索引2.主键索引的效率高（主键不可重复）3.创建主键索引的列，它的值不能为null，且不能重复4.主键索引的列基本上是int
唯一索引(unique)	1.一个表中，可以有多个唯一索引2.查询效率高3.如果在某一列建立唯一索引，必须保证这列不能有重复数据4.如果一个唯一索引上指定not null，等价于主键索引
普通索引(index)	1.一个表中可以有多个普通索引，普通索引在实际开发中用的比较多2.如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引
全文索引(fulltext)	MyISAM存储引擎对全文索引的支持与很多限制,例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得MyISAM的全文索引对于很多的应用场景并不适合。所以，多数情况下的建议是使用别的解决方案，例如 Sphinx、Lucene 等等第三方的插件，亦或是使用 InnoDB 存储引擎的全文索引。