MySQL索引介绍,普通索引,全文索引,空间索引,多列索引使用原则,建立索引常用的规则

转自:https://blog.csdn.net/tomorrow_fine/article/details/78337735

1.MySQL在创建数据表的时候创建索引

在MySQL中创建表的时候,可以直接创建索引。基本的语法格式如下:

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
                  [索引名](字段名1 [(长度)] [ASC | DESC])
);

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

1.1 MySQL创建普通索引

创建一个普通索引时,不需要加任何UNIQUE、FULLTEXT或者SPATIAL参数。

实例:创建一个名为index1的数据表,在表内的id字段上建立一个普通索引。

1. 创建普通索引的SQL代码如下:

CREATE TABLE index1(id INT,
    name VARCHAR(20),
    sex BOOLEAN,
    INDEX(id)
); 

查看MySQL创建普通索引的操作效果。如下图所示:
在这里插入图片描述
从上图中可以看出,运行结果显示普通索引创建成功。

2. 使用SHOW CREATE TABLE语句查看表的结构。如下图所示:
或者通过1步骤,可以看出,在id字段上已经建立了一个名为id的普通索引。语句:

KEY `id` (`id`)

圆括号内的id是字段名称,圆括号左侧外面的id是索引名称。

3. 使用EXPLAIN语句查看索引是否被使用。SQL代码如下:

  EXPLAIN SELECT * FROM index1 WHERE id = 1;

使用EXPLAIN语句查看索引是否被使用的操作效果。如下图所示:
在这里插入图片描述

上图中的结果显示,possible_keys和key的值都为id。说明id索引已经存在,并且查询时已经使用了索引。

1.2 MySQL创建唯一性索引

如果使用UNIQUE参数进行约束,则可以创建唯一性索引。

实例:创建一个名为index2的数据表,在表内的id字段上建立一个唯一性索引,并且设置id字段以升序的形式排列。
1. 创建一个唯一性索引的SQL代码如下:

CREATE TABLE index2(
id INT UNIQUE,
NAME VARCHAR(20),
UNIQUE INDEX index2_id(id ASC)
);

index2_id是为唯一性索引起的一个新名字。

查看MySQL创建唯一性索引的操作效果。如下图所示:
在这里插入图片描述
从上图中可以看出,运行结果显示创建成功。

2. 使用SHOW CREATE TABLE语句查看表的结构。SQL代码如下:

SHOW CREATE TABLE index2 \G
在DOS提示符窗口中查看使用SHOW CREATE TABLE语句查看表的结构的效果。也可以通过上面的1步骤看到,在id字段上建立了名为id和index2_id的两个唯一性索引。这样做,可以提高数据的查询速度。

如果在创建index2表时,id字段没有进行唯一性结束。如下所示:

CREATE TABLE index2(
id INT,
name VARCHAR(20),
UNIQUE INDEX index2_id(id ASC)
);

则也可以在id字段上成功创建名为index2_id的唯一性索引。但是,这样可能达不到提高查询速度的目的。

1.3 MySQL创建全文索引

全文索引使用FULLTEXT参数,并且只能在CHAR、VARCHAR或TEXT类型的字段上创建。
全文索引可以用于全文搜索。
现在,MyISAM存储引擎和InnoDB存储引擎都支持全文索引。
实例:创建一个名为index3的数据表,在表中的info字段上建立名为index3_info的全文索引。

1. 创建全文索引的SQL代码如下:

CREATE TABLE index3(id INT,
info VARCHAR(20),
FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM;

如果设置ENGINE=InnoDB,则可以在InnoDB存储引擎上创建全文索引。

查看MySQL创建全文索引的操作效果。如下图所示:
在这里插入图片描述

从上图中可以看出,代码的执行结果显示创建成功。

2. 使用SHOW CREATE TABLE语句查看index3数据表的结构。如下图所示:
从上图中可以看出,在info字段上已经建立了一个名为index3_info的全文索引。
注意
我使用的是MySQL 5.6.19版本,已经可以在InnoDB存储引擎中创建全文索引了。

全文索引非常适合于大型数据集,对于小的数据集,它的用处可能比较小。

1.4 MySQL创建单列索引

单列索引是在数据表的单个字段上创建的索引。一个表中可以创建多个单列索引。唯一性索引和普通索引等都为单列索引。

实例:创建一个名为index4的数据表,在表中的subject字段上建立名为index4_st的单列索引。

1. 创建单列索引的SQL代码如下:

CREATE TABLE index4(
id INT,
subject VARCHAR(30),
INDEX index4_st(subject(10))
);

查看MySQL创建单列索引的操作效果。如下图所示:
在这里插入图片描述

从上图中可以看出,代码执行的结果显示创建成功。

2. 使用SHOW CREATE TABLE语句 或 manage index 查看index4数据表的结构。如下图所示:
在这里插入图片描述

从上图中可以看出,在subject字段上已经建立了一个名为index4_st的单列索引。

注意:subject字段长度为30,而index4_st设置的索引长度只有10,这样做是为了提高查询速度。对于字符型的数据,可以不用查询全部信息,而只查询它前面的若干字符信息。

1.5 MySQL创建多列索引

创建多列索引是在表的多个字段上创建一个索引。

实例:创建一个名为index5的数据表,在表中的name和sex字段上建立名为index5_ns的多列索引。

1. 创建多列索引的SQL代码如下:

CREATE TABLE index5(id INT,
name VARCHAR(20),
sex CHAR(4),

INDEX index5_ns(name,sex)

);
在DOS提示符窗口中或可视化工具中查看MySQL创建多列索引的操作效果。如下图所示:
在这里插入图片描述

从上图中可以看出,代码的执行结果显示index5_ns索引创建成功。

从上图中可以看出,name和sex字段上已经建立了一个名为index5_ns的多列索引。

2. 多列索引中,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。

先在index5数据表中添加一些数据记录,然后使用EXPLAIN语句可以查看索引的使用情况。如果只是使用name字段作为查询条件进行查询。如下图所示:
在这里插入图片描述

EXPLAIN SELECT id,NAME,sex FROM index5;

在这里插入图片描述

EXPLAIN SELECT id,NAME,sex FROM index5 WHERE id = 3;
在这里插入图片描述

在这里插入图片描述

从上图中可以看出,possible_keys和key的值都是index5_ns。Extra(额外信息)显示正在使用索引。这说明使用name字段进行索引时,索引index5_ns已经被使用。

4. 如果只使用sex字段作为查询条件进行查询。如下图所示:

EXPLAIN SELECT id,NAME,sex FROM index5 WHERE sex = '女';

在这里插入图片描述

从上图中可以看出,possible_keys和key的值都是NULL。Extra(额外信息)显示正在使用where条件查询,而未使用索引。

提示

使用多列索引时一定要特别注意,只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段,那么这个多列索引就不会起作用。因此,在优化查询速度时,可以考虑优化多列索引。

1.5 MySQL创建空间索引

使用SPATIAL参数能够创建空间索引。创建空间索引时,表的存储引擎必须是MyISAM类型。而且,索引字段必须有非空约束。

实例:创建一个名为index6的数据表,在表中的space字段上建立名为index6_sp的空间索引。

1. 创建空间索引的SQL代码如下:

CREATE TABLE index6
(
id INT,
SPACE GEOMETRY NOT NULL,
SPATIAL INDEX index6_sp(SPACE)
) ENGINE=MYISAM;

在DOS提示符窗口中或可视化工具中查看MySQL创建空间索引的操作效果。如下图所示:
在这里插入图片描述

从上图可以看出,代码执行的结果显示空间索引创建成功。

  1. 使用SHOW CREATE TABLE语句可看index6数据表的结构。如下图所示:
    从上图中可以看出,在space字段上已经建立了一个名为index6_sp的空间索引。从上图中可以看出,在space字段上已经建立了一个名为index6_sp的空间索引。

注意,space字段是非空的,而且数据类型是GEOMETRY类型。这个类型是空间数据类型。

空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到

2 添加索引

1.添加PRIMARY KEY(主键索引)
mysql>ALTER TABLE table_name ADD PRIMARY KEY ( column )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE table_name ADD UNIQUE (
column
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE table_name ADD INDEX index_name ( column )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE table_name ADD FULLTEXT ( column)
5.添加多列索引
mysql>ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

3 建立索引常用的规则

转:https://www.cnblogs.com/JimLy-BUG/p/6812682.html
建立索引常用的规则如下:
1、表的主键、外键必须有索引;
2、数据量超过300万的表应该有索引;
3、经常与其他表进行连接的表,在连接字段上应该建立索引;
4、经常出现在Where子句中的字段,非凡是大表的字段,应该建立索引;
5、索引应该建在选择性高的字段上(枚举型字段不建索引);
6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?假如是,则可以建立复合索引;否则考虑单字段索引;
C、假如复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
D、假如复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
E、假如既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
8、频繁进行数据操作的表,不要建立太多的索引;
9、删除无用的索引,避免对执行计划造成负面影响;
以上是一些普遍的建立索引时的判定依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,凡是对频繁更新的表来说,负面影响更大

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页