mysql数据库索引_MySQL数据库应用总结（八）—MySQL数据库索引的操作

最新推荐文章于 2022-12-19 18:21:29 发布

苏承心

最新推荐文章于 2022-12-19 18:21:29 发布

阅读量255

点赞数 1

文章标签： mysql数据库索引

本文链接：https://blog.csdn.net/weixin_42311672/article/details/113114389

版权

长文预警，多图预警...

------------------------------------------------------------------------------

SQL语法预览：

创建表的时候创建索引：【create table 表名(字段名称数据类型,unique|fulltext|spatial index 索引名(索引字段名(索引长度)|…));】

在已有表上增加索引：【alter table 表名 add unique|fulltext|spatial index 索引名(索引字段名(索引长度)|…);】

在已有表上创建索引：【create unique|fulltext|spatial index 索引名on表名(索引字段名(索引长度)|…);】

删除索引：【alter table 表名drop index 索引名;】

删除索引：【drop index索引名on 表名;】

详解：

一、索引简介

索引是对数据库表中的一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。

1. 索引的含义

索引是一个单独的、存储在磁盘上的数据库结构，包含对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一定值的行，MySQL中所有的列类型都可以被索引，对相关列使用索引是提高查询造作速度的最佳途径。

例如：假设某数据库有50000条记录，现在要执行这样一个查询：select * from table where

num=20000。如果没有索引，必须遍历整个表，直到num等于20000的行被找到为止；如果在num列上创建索引，MySQL不需要逐一进行查找，直接在索引里面寻找20000，就可以得知这一行的位置。因此，提高了查询的速度。

索引的优缺点

2. 索引的分类

MySQL数据库中的索引分一下几类：

3. 创建索引原则

(1)索引并非越多越好

多了站磁盘空间，也影响一些语句执行速度。

(2)避免对经常更新的表做过多索引

对于经常要查询的字段创建索引。

(3)数据量小的表最好不要使用索引

数据量小的时候，遍历数据的时间较短，创建索引与否对查询效率影响不大时，可以不建立索引。

(4)在不同值少的列上不要建立索引

枚举数量少的字段值，可以不建立索引。例如性别字段值只有两个，则无需建立索引。

(5)数据唯一时指定唯一索引

使用唯一索引能保证列的数据完整性。

(6)在频繁进行排序或分组的列上建立组合索引

即在频繁进行group by或 order by的操作时，如果待排列的列有多个，则建立组合索引。

二、创建索引

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句create table…index(字段名)中指定索引列，使用修改表的语句alter table在已经存在的表中用create index语句创建索引列。

1. 创建表的时候创建索引

a) 创建普通索引

语法结构：create table 表名(字段名,数据类型列级别约束条件…index(字段名));

案例：创建数据库db_demo3，在该数据库中定义数据表tb_book1，包含字段图书编号、图书名称、作者、出版社、出版时间和图书信息，创建索引为出版时间，SQL语句如下：

命令语句：

create database db_demo3;

use db_demo3;

create table tb_book1(bookid int(11) primary key not null,bookname varchar(255) not null,authors varchar(255) not null,press varchar(255), year_publication year not null,info varchar(255),index(year_publication));

执行结果：

由结果看到book1表上的year_publication字段成功建立索引，其索引名称是MySQL自动添加的。使用explain语句可以查看索引是否正在使用：

Explain语句输出结果的各行解释如下：select_type行：查询类型，简单查询。

table行：数据库读取数据表的名称。

type行：指本数据库表与其他数据表之间的关联关系，可取值有system、const、eq_ref、ref、range、index和all。

possible_keys行：给出MySQL在搜素数据记录时渴望选用的各个索引。

key行：MySQL实际选用的索引

key_len行：给出索引按字节计算的长度，值越小，则查询速度越快。

ref行：给出关联关系中另一个数据表里数据列的名称。

rows行：MySQL从表里读出数据行的个数。

extra行：提供与关联有关的信息。

可以看到，possible_keys和key的值都为year_publication，说明查询时使用了索引。

b) 创建唯一索引

语法结构：create table 表名(字段名,数据类型列级别约束条件…unique index 索引名(字段名));

案例：创建数据表tb_index1，包含字段name和id，在id字段上创建唯一索引，SQL语句如下：

命令语句：create table tb_index1(id int(11) primary key not null,name varchar(25) not null, unique index UniqIdx(id));

执行结果：

c) 创建单列索引

语法结构：create table 表名(字段名,数据类型列级别约束条件…index 索引名(字段名(索引长度)));

案例：创建数据表tb_index2，包含字段name和id，在name字段上创建单列索引，SQL语句如下：

命令语句：create table tb_index2(id int(11) primary key not null,name varchar(25) not null, index SingleIdx(name(20)));

执行结果：

d) 创建组合索引

组合索引是在多个字段上创建的索引。

语法结构：create table 表名(字段名,数据类型列级别约束条件…index 索引名(字段名1,字段名2,…,字段名n));

案例：创建数据表tb_index3，包含字段id、name、info和age字段，在id、name和age字段上创建组合索引，SQL语句如下：

命令语句：create table tb_index3(id int(11) not null,name char(30) not null,age int not null,info varchar(255),index MultiIdx(id,name,age));

执行结果：

e) 创建全文索引

FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只能在数据类型为char、varchar和text的列上建立全文索引。

语法结构：create table 表名(字段名,数据类型列级别约束条件…fulltext index 索引名(字段名))ENGINE=MyISAM;

案例：创建数据表tb_index4，包含字段id、name、info和age字段，在info字段上创建全文索引，SQL语句如下：

命令语句：create table tb_index4(id int(11) not null,name char(30) not null,age int(11) not null,info varchar(255), fulltext index MultiIdx(info)) ENGINE=MyISAM;

执行结果：

f) 创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型的字段不能为空。

语法结构：create table 表名(字段名,数据类型列级别约束条件…spatial index 索引名(字段名))ENGINE=MyISAM;

案例：创建数据表tb_index5，包含字段id、name、info和age字段，在info字段上创建空间索引，SQL语句如下：

命令语句：create table tb_index5(jh geometry not null, spatial index SpatIdx(jh)) ENGINE=MyISAM;

执行结果：

2. 在已有表上创建索引

在已有字段的表中创建索引，可以使用alter table语句或者create index语句。

a) 使用alter table … add index…语法创建索引

i. 创建普通索引

与创建表时创建索引的语法不同的是，这里使用了alter table 语句和add关键字，add表示向表中增加索引。

语法结构：alter table 表名add index 索引名(字段名(索引长度));

增加索引之前，先用show index语句查看指定表中已经创建的索引：

语法结构：show index from表名 \G;

上面主要参数各行解释如下：table行：查看的当前数据表的名称。

Non_unique行：索引非唯一。1代表真表示不是唯一索引，0表示假表示是唯一索引。

Key_name行：主键，也是索引。

Seq_in_index行：字段在索引中的位置，单列索引该值为1，组合字段为每个字段在索引定义中的顺序。

Column_name行：当前索引的字段名。

Cardinality行：索引基数。

Sub_part行：表示索引长度。

Packed行：索引包。

Null行：表示该字段是否能为空值。

Index_type行：表示索引类型。

Comment行：表示注释。

可以看到Key_name的值都为PRIMARY和year_publication(未定义索引名默认为索引字段名)，说明查有两个索引。

下面给表中增加一个普通索引。

案例：在tb_book1表中的字段bookname字段上增加索引名为BKNameIdx的普通索引，SQL语句如下：

命令语句：alter table tb_book1 add index BKNameIdx(bookname(30));

执行结果：

可以看到，现在表中又新增一个索引，即通过alter table语句添加的名称为BKNameIdx的索引，该索引为非唯一索引，长度为30。

ii. 创建唯一索引语法结构：alter table 表名add unique index 索引名(字段名);

案例：在数据表tb_book1字段bookid上建立名为UniqidIdx的唯一索引，SQL语句如下：

命令语句：alter table tb_book1 add unique index UniqidIdx(bookid);

执行结果：

iii. 创建单列索引

语法结构：alter table 表名add index 索引名(字段名(索引长度));

案例：在数据表tb_book1字段press上建立名为SigBKIdx的单列索引，SQL语句如下：

命令语句：alter table tb_book1 add index SigBKIdx(press);

执行结果：

iv. 创建组合索引

语法结构：alter table 表名add index 索引名(字段名1(索引长度),字段名2(索引长度)…);

案例：在数据表tb_book1字段authors和info上建立名为ZHIdx的组合索引，SQL语句如下：

命令语句：alter table tb_book1 add index ZHIdx(authors(20),info(50));

执行结果：

v. 创建全文索引

语法结构：alter table 表名add fulltext index 索引名(字段名);

案例：创建表tb_index6，在数据表tb_index6字段info上建立名为infoFTIdx的全文索引，SQL语句如下：

先创建表：

命令语句：create table tb_index6(id int not null,info char(255)) ENGINE=MyISAM;

alter table tb_index6 add fulltext index infoFTIdx(info);

执行结果：

vi. 创建空间索引

语法结构：alter table 表名add spatial index 索引名(字段名);

案例：创建表tb_index7，在数据表tb_index7字段jh上建立名为spatIdx的空间索引，SQL语句如下：

先创建表：

命令语句：create table tb_index7(jh geometry not null) ENGINE=MyISAM;

alter table tb_index7 add spatial index spatIdx(jh);

执行结果：

b) 使用create index …语法创建索引

i. 创建普通索引

这里使用了create index语句向表中增加索引。先创建一个新表tb_book2,方便案例师范，因为同一字段不能同时创建多个索引。

命令语句： create table tb_book2(bookid int(11) not null,bookname varchar(255) not null,authors varchar(255) not null,press varchar(255),year_publication year not null,info varchar(255));

语法结构：create index 索引名on 表名(字段名(索引长度));

案例：在tb_book2表中的字段bookname字段上增加索引名为BKNameIdx的普通索引，SQL语句如下：

命令语句：create index BKNameIdx on tb_book2(bookname(30));

执行结果：

可以看到，create index语句成功创建名称为BKNameIdx的索引，该索引为非唯一索引，长度为30。

ii. 创建唯一索引

语法结构：create unique index 索引名on 表名(字段名);

案例：在数据表tb_book2字段bookid上建立名为UniqidIdx的唯一索引，SQL语句如下：

命令语句：create unique index UniqidIdx on tb_book2(bookid);

执行结果：

iii. 创建单列索引

语法结构：create index 索引名 on 表名(字段名(索引长度));