MySQL索引的创建与使用

您所在的位置：网站首页 › mysql怎么创建查询 › MySQL索引的创建与使用

MySQL索引的创建与使用

#MySQL索引的创建与使用| 来源: 网络整理| 查看: 265

索引有很多，且按不同的分类方式，又有很多种分类。不同的数据库，对索引的支持情况也不尽相同。

声明：本人主要简单示例MySQL中的单列索引、组合索引的创建与使用。

索引的创建：建表时创建：

CREATE TABLE 表名(

字段名数据类型 [完整性约束条件],

……，

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

);

说明：

UNIQUE:可选。表示索引为唯一性索引。FULLTEXT:可选。表示索引为全文索引。SPATIAL:可选。表示索引为空间索引。INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。索引名:可选。给创建的索引取一个新名称。字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。长度:可选。指索引的长度，必须是字符串类型才可以使用。ASC:可选。表示升序排列。DESC:可选。表示降序排列。

注：索引方法默认使用B+TREE。

单列索引(示例)：

CREATE TABLE projectfile ( id INT AUTO_INCREMENT COMMENT '附件id', fileuploadercode VARCHAR(128) COMMENT '附件上传者code', projectid INT COMMENT '项目id;此列受project表中的id列约束', filename VARCHAR (512) COMMENT '附件名', fileurl VARCHAR (512) COMMENT '附件下载地址', filesize BIGINT COMMENT '附件大小，单位Byte', -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增） PRIMARY KEY (id), -- 主外键约束（注:project表中的id字段约束了此表中的projectid字段） FOREIGN KEY (projectid) REFERENCES project (id), -- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引) UNIQUE INDEX (projectid), -- 给fileuploadercode字段创建普通索引 INDEX (fileuploadercode) -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码 ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

注：这里只为示例如何创建索引，其他的合理性之类的先放一边。

组合索引(示例)：

CREATE TABLE projectfile ( id INT AUTO_INCREMENT COMMENT '附件id', fileuploadercode VARCHAR(128) COMMENT '附件上传者code', projectid INT COMMENT '项目id;此列受project表中的id列约束', filename VARCHAR (512) COMMENT '附件名', fileurl VARCHAR (512) COMMENT '附件下载地址', filesize BIGINT COMMENT '附件大小，单位Byte', -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增） PRIMARY KEY (id), -- 创建组合索引 INDEX (fileuploadercode,projectid) -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码 ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表'; 建表后创建：

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；

或

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；

示例一：

-- 假设建表时fileuploadercode字段没创建索引(注:同一个字段可以创建多个索引，但一般情况下意义不大) -- 给projectfile表中的fileuploadercode创建索引 ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);

示例二：

ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

示例三：

-- 将id列设置为主键 ALTER TABLE index_demo ADD PRIMARY KEY(id) ; -- 将id列设置为自增 ALTER TABLE index_demo MODIFY id INT auto_increment; 查看已创建的索引：

show index from 表名;

提示:我们也可以直接使用工具查看

示例：

索引的删除：

DROP INDEX 索引名 ON 表名

或

ALTER TABLE 表名 DROP INDEX 索引名

示例一：

drop index fileuploadercode1 on projectfile;

示例二：

alter table projectfile drop index s2123; 查看SQL语句对索引的使用情况(即:查询SQL的查询执行计划QEP)：

在select语句前加上EXPLAIN即可。

示例：

EXPLAIN SELECT * FROM `index_demo` ii WHERE ii.e_name = 'Jane';

分析该SQL的性能为：

提示：我们也可以使用SQL工具查看，如：navicat中的“解释”选项即可查看。

说明：

id：SELECT识别符。这是SELECT的查询序列号。

select_type：SELECT类型。

SIMPLE：简单SELECT(不使用UNION或子查询)PRIMARY：最外面的SELECTUNION：UNION中的第二个或后面的SELECT语句DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询UNION RESULT：UNION的结果SUBQUERY：子查询中的第一个SELECTDEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询DERIVED：导出表的SELECT(FROM子句的子查询)

table：表名

type：联接类型。是SQL性能的非常重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说，得保证查询至少达到range级别。

system：表仅有一行(=系统表)。这是const联接类型的一个特例。const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或操作符的带索引的列。ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、、>、>=、

【本文地址】

MySQL索引的创建与使用

MySQL索引的创建与使用

今日新闻

推荐新闻