用十万级数据进行讲解MySQL索引基础

索引简介

索引是为了提高数据库查询效率而生的。对于一些查询多，修改少的字段很适合用索引，以提高查询效率。如果是修改多的话，用索引会降低其效率，因为维护这个索引表很耗时。索引分为，主键索引(primary key)、唯一索引(unique index)、普通索引(index)、组合索引、全文索引。
下面先演示一下用索引和不用索引的区别。

• 查看表中数据数量
  select count(*) from 表名;
  
• 查看表中索引
  show index from 表名;
  
  可以看出，这里只有一个主键索引(userId)。而username字段不是索引字段。
  这里我们通过查询username为70000的数据，进行比较用索引和不用索引的区别。
  
  可以清楚的看到，用索引查询速度很快。
• 删除索引
  drop index 索引名 on 表名;
• 删除主键索引，也就是删除了该字段
  alter table 表名 drop 主键字段名;

主键索引

主键索引，也就是我们常用的主键，主键只能有一个，但一个主键里可以有多个字段。
创建表的时候创建主键索引

create table test(
   id int(11),
   name varchar(25),
   primary key (id)
);

• 创建表之后给表添加主键索引
  alter table test add constraint id primary key(id);

唯一索引

唯一索引，索引字段中的值必须是唯一的，但是允许为空值。

• 创建表的时候创建唯一索引

create table test(   
	id int(11),   
	name varchar(25),   
	unique index_unique_test_name (name)
);

• 创建表之后创建唯一索引
  create unique index index_unique_test_name on test(name);
• 修改表结构为唯一索引
  alter table test add unique index index_unique_test_name (name);

普通索引

普通索引是最基本的索引，仅用来加速查询。

create table test(   
	id int(11),   
	name varchar(25),   
	index index_test_name (name)
);

• 创建表之后创建普通索引
  create index index_test_name on test(name);
• 修改表结构为普通索引
  alter table test add index index_test_name (name);

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

create table test(   
	id int(11),   
	name varchar(25),   
	index index_test_id_name (id,name)
);

• 创建表之后创建组合索引
  create index index_test_id_name on test(id,name);
• 修改表结构为普通索引
  alter table test add index index_test_id_name (id,name);

全文索引

全文索引，是在char，varchar，text类型字段上使用全文索引。就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"欢迎阅读此文章"通过此文章，可能就可以找到该条记录。

• 创建表的时候创建全文索引

create table test(   
	id int(11),   
	name varchar(25),   
	content text,
	fulltext (text)
);

• 创建表之后创建组合索引
  create fulltext index index_content on test(content);
• 修改表结构为普通索引
  alter table test add fulltext index index_content (content);