index在mysql_MySQL 索引 INDEX-白红宇

index在mysql_MySQL 索引 INDEX

阅读量：5940 次

发布时间：2019-06-19

本文共 3744 字，大约阅读时间需要 12 分钟。

索引用于快速找出在某列中有特定值的行。

不使用索引，MySQL必须从第一条记录开始读完整个表，直到找到相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

MySQL中的索引的存储类型有两种：BTREE和HASH

索引的分类：

索引是在存储引擎中实现的，即不同的存储引擎会使用不同的索引：

MyISAM和InnoDB存储引擎：只支持BTREE索引，即默认使用BTREE索引且不能更换。

MEMORY存储引擎：支持BTREE索引和HASH索引

索引可分为四大类：单列索引(普通索引、唯一索引、主键索引)、组合索引、全文索引、空间索引

单列索引：一个索引只包含单个列，不过一个表中可以有多个单列索引，每个单列索引只包含一列。

普通索引(INDEX)：MySQL中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据。

唯一索引(UNIQUE INDEX uniqIdx)：索引列中的值必须是唯一的，但允许是空值。

主键索引(PRIMARY KEY)：一种特殊的唯一单列索引，不允许有空值

组合索引(INDEX MultiIdx)：在表中的多个字段组上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引(FULLTEXT INDEX FullTxtIdx)：类似于正则表达式，就是通过某个关键字就能找到该关键字所在的记录行。只能在MyISAM存储引擎上才能使用，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。

空间索引(SPATIAL INDEX spatIdx)：对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种：GEOMETRY、POINT、LINESTRING、POLYGON。建立空间索引时，使用SPATIAL关键字。

实例表

CREATE TABLE 'award'('id' int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id','aty_id' varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id','nickname' varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称','is_awarded' tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖','award_time' int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间','account' varchar(12) NOT NULL DEFAULT '' COMMENT '账户','password' char(32) NOT NULL DEFAULT '' COMMENT '密码','message' varchar(255) NOT NULL DEFAULT COMMNET '获奖信息','create_time' int(11) NOT NULL DEFAULT COMMENT '创建时间','update_time' int(11) NOT NULL DEFAULT COMMENT '更新时间',PRIMARY KEY('id')

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF-8 COMMENT='获奖信息表';

在一个表中添加索引的方式：

1)在创建表的同时创建索引

2)也可以向已创建好的表中添加索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名称(创建索引的字段名[length])[ASC|DESC]

ALTER TABLE 表名称 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名称](表中要建索引的字段名)[ASC|DESC]

创建普通索引

普通索引时最基本索引。

如果是 char, varchar 类型，length 可以小于字段的实际长度，如果是 BLOB, TEXT 类型必须指定长度。

CREATE INDEX 索引名称 ON表名称(列名称(length));ALTER TABLE 表名称 ADD INDEX索引名称(列名称(length));CREATE INDEX account_index ON 'award'('account');ALTER TABLE award ADD INDEX account_Index('account');

创建唯一索引

与普通索引类似，但是不同的是唯一索引要求所有列的值是唯一的，不能重复，这一点和主键索引一样，但是唯一索引允许有空格。

CREATE UNIQUE INDEX 索引名称 ON表名称(列名称(length))；ALTER TABLE 表名称 ADDUNIQUEQ(列名称);CREATE UNIQUE INDEX account_unique_index ON 'award'('account');

创建主键索引

主键索引要求列的值是唯一的，不能重复，且不允许有空格。

在BTREE的InnoDB引擎中，主键索引起到了至关重要的地位。

主键索引建立的规则是 int 优于 varchar，一般在建表的时候创建，一般会设为 int 而且是 AUTO_INCREMENT自增类型。最好是与表的其他字段不相关的列或者是业务不相关的列

创建组合索引

一个表中含有多个单列索引不代表是组合索引，通俗一点讲，组合索引是包含多个字段但是只有一个索引名称。

CREATE INDEX 索引名称 ON表名称(列名称(length), 列名称(length), ...);CREATE INDEX nickname_account_createTime_index ON 'award'('nickname', 'account', 'created_time');

如果创建了组合索引 (nickname_account_createTime_index) 那么实际包含3个索引 (nickname), (nickname, account), (nickname, account, created_time)

在使用组合索引查询的时候，MySQL遵循最左前缀原则，即索引 where 时的条件要按照建立索引时的字段的排序方式。

1. 不按照索引最左列开始查询(多列索引)，不能使用索引

2. 查询某个列有范围查询，则其右边的所有列都无法使用查询(多列查询)

3. 不能跳过某个字段来进行查询，这样利用不到索引

创建全文索引

文本字段(TEXT)上如果建立的是普通索引，那么只有对文本的字段内容前面的字符进行索引，其字符大小根据索引建立时的大小来规定。

如果文本中出现多个一样的字符，而且需要查找的话，那么其条件只能是 where culumn like '%XXXXX%' 但是这样会让索引失效。这种情况下就需要使用全文索引。

ALTER TABLE 表名称 ADD FULLTEXT(列1, 列2);

有了全文索引，就可以使用 select 查询命令去检索那些包含着一个或多个给定单词的数据记录了

SELECT * FROM 表名称 WHERE MATCH(列1, 列2) AGAINST('xxx', 'sss', 'ddd');--该条语句将把列1和列2中含有 xxx, sss, ddd 的数据记录全部查询出来。

查询表中的索引：