索引是为了提高数据库的查询性能,提高效率特别高,其会降低插入效率不过相比查询提高的效率这些是值得的。
索引的类型:
- 主键索引(primary key)
- 唯一索引(unique)
- 普通索引(index)
- 全文索引(fulltext)
需要注意的是不同的存储引擎对索引的支持不一样,在旧的版本InnoDB不支持全文索引,全文索引只有MyISAM支持,不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引 。
创建索引
-
主键索引创建和主键约束相同,只要其是主键,就会自动创建索引
-
唯一索引和唯一键相同
-
普通索引
第一种方式
create table user(id int primary key, name varchar(20), email varchar(30), index(name) --在表的定义最后,指定某列为索引 );
第二种方式
create table user(id int primary key, name varchar(20), email varchar(30)); alter table user add index(name); --创建完表以后指定某列为普通索引
第三种方式
create table user(id int primary key, name varchar(20), email varchar(30)); -- 创建一个索引名为 idx_name 的索引 create index idx_name on user(name);
第一种和第二种创建的索引的名称默认为该项名称(name)
-
全文索引
当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求,默认的全文索引支持英文,不支持中文, 如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。--新版InooDB支持全文索引 CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) );
同样也可以用alter table 添加会这用create fulltext index添加
全文索引使用SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
可以通过explain分析sql语句的使用查看是否使用了索引
explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')
查询索引
show keys from 表名
或 show index from 表名
删除索引
删除主键索引 alter table 表名 drop primary key;
删除其他索引 alter table 表名 drop index 索引名 或 drop index 索引名 on 表名
索引创建原则
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
- 更新非常频繁的字段不适合作创建索引