MySQL索引类型

概述

    今天为大家详细介绍一下数据库的几种索引类型、使用方式和场景。

1、普通索引

    最基本的索引，它没有任何限制，用于查询速度优化。

-- 建立表时创建
CREATE TABLE mytable ( name VARCHAR(32), INDEX index_mytable_name(name));
-- 建表后，直接创建
CREATE INDEX index_mytable_name ON mytable(name);
-- 修改表结构
ALTER TABLE mytable ADD INDEX index_mytable_name(name);
-- 删除索引
DROP INDEX index_mytable_name ON mytable;

2、唯一索引

    索引列的值必须唯一，但允许空值。常用做不重复的字段。

-- 建立表时创建
CREATE TABLE mytable ( name VARCHAR(32), UNIQUE index_unique_mytable_name (name));
-- 建表后，直接创建
CREATE UNIQUE INDEX index_unique_mytable_name ON mytable(name);
-- 修改表结构
ALTER TABLE mytable ADD UNIQUE INDEX index_unique_mytable_name(name);
-- 删除索引
DROP INDEX index_unique_mytable_name ON mytable;

3、主键索引

    是一种特殊的唯一索引，一个表只能有一个主键，不允许空值。一般是在建表时候同时创建主键索引。常见的主键自增ID。

-- 建立表时创建
CREATE TABLE mytable (id int(11) NOT NULL AUTO_INCREMENT,name VARCHAR(32),PRIMARY KEY(id));
-- 修改表结构
ALTER TABLE mytable ADD CONSTRAINT id PRIMARY KEY (id);

4、组合索引

    指多个字段上创建的索引，使用组合索引时遵循最左前缀组合。创建组合索引可以形成索引覆盖，提高where语句查询效率。

-- 建立表时创建
CREATE TABLE mytable ( `id` int(11) , `name` VARCHAR(32) , INDEX index_mytable_id_name (`id`,`name`) );
-- 建表后，直接创建
CREATE INDEX index_mytable_id_name ON mytable(id,name);
-- 修改表结构
ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);

6、全文索引

    主要用于查找文本中的关键字，而不是直接与索引中的值相比较。全文索引与普通的索引不大相同，它需要配合match agains操作使用，而不是一般的where语句加like。目前只有char、varchar、text列上可以创建全文索引。

-- 建立表时创建
CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(250) NOT NULL , `contents` text NULL , `create_at` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (contents) );
-- 建表后，直接创建
CREATE FULLTEXT INDEX index_article_contents ON article(contents);
-- 修改表结构
ALTER TABLE article ADD FULLTEXT INDEX index_article_contents　(contents);
-- 查询语句示例
SELECT * FROM article WHERE MATCH (contents) AGAINST ('精神' IN BOOLEAN MODE);

总结

    虽然索引可以增加查询效率，但对于跟新、创建或者删除的时候，需要去维护索引，反而会导致性能下降；因此建立索引，需要考虑诸多因素。