MySQL管理之索引

序号类型地址
1MySQLMySQL操作之概念、SQL约束(一)
2MySQLMySQL操作之数据定义语言(DDL)(二)
3MySQLMySQL操作之数据操作语言(DML)(三)
4MySQLMySQL操作之数据查询语言:(DQL)(四-1)(单表操作)
5MySQLMySQL操作之数据查询语言:(DQL)(四-2)(多表查询)
6MySQLMySQL操作之数据控制语言:(DC)(五)
7MySQLMySQL操作之数据库函数
8MySQLMySQL管理之数据类型
9MySQLMySQL管理之索引
10MySQLMySQL管理之事务管理
11MySQLMySQL管理之存储过程
12MySQLMySQL管理之视图
13MySQLMySQL管理之数据备份与还原
14MySQLLinux(centos 7.5)服务器安装MySQL
15MyBatisMyBatis从入门到多表关联
16MyBatisMyBatis常用方法
17MyBatisMybatis逆向工程的使用(附文件地址)
18MyBatisspring boot连接Mybatis数据库的配置文件(MySql、SQLserver、Oracle)
19MyBatis-PlusMybatis-Plus使用案例(包括初始化以及常用插件)
20MyBatis-Plusmybatis-plus代码生成器
21MyBatis-Plus自定义SQL
22MyBatis-PlusMybatis-Plus(连接Hive)
23MyBatis-PlusMyBatis-plus配置自定义SQL(执行用户传入SQL)
24MyBatis-PlusMybatis-Plus(Service CRUD 接口)


一、简介

数据索引就好比新华字典的音序表。它是对数据表中一列或者多列的值进行排序后的一种结构,其作用就是提高表中数据的查询速度。

虽然索引可以提高数据的查询速度,但索引会占用一定的磁盘空间,并且在创建和维护索引时,其消耗的时间随着数据量的增加而增加的。

二、分类

1.普通索引

普通索引是由KEY或INDEX定义的索引。是MySQL中的基础索引类型,可以创建在任何数据类型中,其值是否唯一和非空由字段本身的约束条件所决定。

2.唯一性索引

是由UNIQUE定义的索引,该索引所在的值必须是唯一的。

3.全文索引

是由FULLTEXT定义的索引,它只能创建在CHARVARCHARTEXT类型的字段上。现在只有MyISAM存储引擎支持全文索引。

4.单列索引

在表中单个字段上创建索引,可以是普通索引、唯一索引或者全文索引,只要保证该索引只对应表中一个字段既可。

5.多列索引

在表中多个字段上创建索引。只有在查询条件中使用了这些字段中的第一个字段时,该索引才会被使用。

6.空间索引

是由SPATIAL定义的索引,只能创建在空间数据类型的字段上。

MySQL中的空间数据类型有4种。Geometry、Point、LineString和Polygon。

需要注意的是创建空间索引的字段,必须声明其为NOT NULL,并且空间索引只能在存储引擎为MyISAM的表中创建。

三、创建索引

创建索引的索引方法有三种,具体为:

  • 创建表时创建索引
  • 使用Create Index语句在已经存在的表上创建索引
  • 使用Alter Table语句在已经存在的表上创建索引

3.1 创建表时创建索引

CREATE TABLE 表名 (
  字段名1 数据类型 约束条件,
  字段名2 数据类型 约束条件,
  [UUNIQUE|FULLTEXT|SPATIALINDEX|KEY
   	[别名](字段1[(长度)][AES|DESC])

解释:

  • UNIQUE:可选参数,表示唯一索引。
  • FULLTEXT:可选参数,表示全文索引。
  • SPATIAL:可选参数,表示空间索引
  • INDEX和KEY:用来表示字段的索引,二者选一既可。(普通索引)
  • 别名:可选参数,表示创建的索引名称。
  • 字段名1:指定索引对应字段的名称。
  • 长度:可选参数,用于表示索引的长度。
  • AES和DESC:可选参数,其中,AES表示升序排列。DESC表示倒序排列。

1.创建普通索引

CREATE TABLE t1 (
  id INT,
  name VARCHAR (32),
  age INT (16),
  INDEX (id)
);

查看数据结构

EXPLAIN SELECT * FROM t1 WHERE id =1;

在这里插入图片描述

其中,possible_keys和key为id,则证明已经通过id进行查询。

2.创建唯一性索引

CREATE TABLE t2 (
  id INT,
  name VARCHAR (32),
  UNIQUE INDEX unique_id ( id DESC )
)

3.创建全文索引

只有MyIsam存储引擎支持全文索引。

CREATE TABLE t4 (
  id INT,
  name VARCHAR (32), 
  FULLTEXT INDEX fulltext_name ( NAME DESC ) 
) ENGINE = Myisam;

4. 创建单列索引

CREATE TABLE t5 (
  id INT,
  name VARCHAR (32),
  INDEX single_name (name(20))
)

5. 创建多列索引

只有在查询条件中使用了第一个字段时,才会走多列索引。

CREATE TABLE t6 (
  id INT,
  name VARCHAR (32),
  INDEX double_name ( id, NAME(20))
)

6. 创建空间索引

在类型为GEOMETRY的字段上创建空间索引。

创建空间索引时,所在字段的值不能为空值,且表的存储引擎为MyISAM。

CREATE TABLE t7 (
  id INT,
  name GEOMETRY NOT NULL,
  SPATIAL INDEX name(name)
) ENGINE = MyISAM;

3.2 使用Create Index语句在已经存在的表上创建索引

若想在已经存在的表上创建索引,可以使用CREATE INDEX语句。

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (字段名[(长度)] [AES|DESC])

1.创建普通索引

CREATE INDEX index_id ON book(bookid);

2.创建唯一性索引

CREATE UNIQUE INDEX unique_id ON book(bookid);

3.创建单列索引

CREATE INDEX singleidx ON book(bookid);

4.创建多列索引

在表book上,nameaddress列,创建名为mulitidx的多列索引。

CREATE INDEX mulitidx ON book(name(20),address(20));

5.创建全文索引

CREATE FULLTEXT INDEX fulltextidx ON book(bookid);

6.空间索引

在book表的bookid字段,创建名为spatidx的空间索引。

CREATE SPATIAL INDEX spatidx ON book(bookid);

3.3 使用 Alter Table 语句在已经存在的表上创建索引

若想在已经存在的表上创建索引,可以使用CREATE INDEX语句。

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 
	索引名 (字段名[(长度)] [AES|DESC])

1.创建普通索引

ALTER TABLE book ADD INDEX index_id(bookid);

2.创建唯一性索引

ALTER TABLE book ADD UNIQUE uniqueidx(bookid);

3.创建单列索引

ALTER TABLE book ADD INDEX singleidx(bookid);

4.创建多列索引

为book表authorsinfo字段,添加名称为multidx的多列索引

ALTER TABLE book ADD INDEX multidx(authors(20),info(20));

5.创建全文索引

ALTER TABLE book ADD FULLTEXT INDEX fulltextidx(info);

6.空间索引

ALTER TABLE book ADD SPATIAL INDEX spatidx(spae);

四、删除索引

4.1使用 Alter Table 删除索引

ALTER TABLE 表名 DROP INDEX 索引名

语句:

ALTER TABLE book DROP INDEX fulltextidx;

4.2使用 Drop Index 删除索引

DROP INDEX 索引名 ON 表名;

语句:

DROP INDEX spatidx ON book;

五、Xmind整理

SCDN地址

https://download.csdn.net/download/weixin_44624117/23754601
posted @ 2022-08-03 23:56  ah_lydms  阅读(81)  评论(0编辑  收藏  举报