命令行操作MySQL - 《表索引》汇总

最新推荐文章于 2024-03-03 19:31:11 发布
最新推荐文章于 2024-03-03 19:31:11 发布
阅读量670 收藏 5
点赞数 1
分类专栏： MySQL 文章标签： 数据库 索引 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cpp_learner/article/details/110447285
版权

这是命令行操作MySQL数据库系列博客的第十二篇，今天这篇博客记录《表索引》如何进行操作。（普通索引、唯一索引、全文索引、多列索引、隐藏索引、删除索引和修改索引）

目录

索引概述

  1. 基本概念
    日常生活中，我们经常会在电话号码簿中查阅“某人”的电话号码，按姓查询或者按字母排序查询； 在字典中查阅“某个词”的读音和含义等等，以快速的找到特定记录。在这里，“姓”和“字母”都可看作是索引， 而按“姓”或者“字母”查询则是按索引查询！

    索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

    通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表，如果要查“过”字，如果不适用音序，就需要从字典的第一页开始翻几百页；如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找，这样就可以大大节省时间。因此，使用索引可以在很大程度上提高数据库的查询速度，有效地提高了数据库系统的性能。

  2. 索引类型

    索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。下文中将详细介绍~

  3. 索引存储

    数据库底层索引实现主要有两种存储类型，B树(BTREE)和哈希(HASH)索引。

  4. 索引优点
    可以提高检索数据的速度。

  5. 索引缺点

    创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低了。

  6. 使用建议

    索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

创建和查看索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。

创建索引有3种方式：

  1. 创建表的时候创建索引
  2. 在已经存在的表上创建索引
  3. 使用ALTER TABLE语句来创建索引。

本篇博客将根据具体的索引分类详细的讲解这3种创建方法。

一、普通索引

所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

1. 创建表时定义索引

create table 表名(字段1 类型 , ..., 字段n 类型, INDEX [索引别名] (字段名[(长度)] [ASC | DESC]));
(括号)内是必填，[中括号]是可填可不填
索引别名：自己填的索引别名，可不填；
字段名：索引对应字段的名称，该字段必须在前面定好；
长度：可不填的参数，其指定索引的长度，必须是字符串类型才可以使用；
ASC：表示升序排列，不填则默认为升序；
DESC：表示降序排列。

关于长度，使用字符串作为索引时，不指定长度也是可以的，下面全文索引中会有例子。

例：

  1. 创建

    create table index1(id int, name varchar(32), sex enum('男', '女', '未知'), INDEX index_no(id DESC));

    在这里插入图片描述
    定义id字段为索引，且索引别名为index_no，设置为降序排序。

  2. 插入与查询
    在这里插入图片描述

2. 已存在的表上创建索引

第二种和第三种创建索引的方式包含再次。

(1). 执行create 语句

create INDEX 索引别名 ON 表名(字段名[(长度)] [ASC | DESC]);
(括号)内是必填，[中括号]是可填可不填
INDEX：用来指定字段为索引；
索引别名：自己填的索引别名，此处必须填；
NO：关键字；
表名：是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；
字段名：指定索引对应的字段的名称，该字段必须为前面定义好的字段；
长度：可不填，表示索引的长度，必须是字符串类型才可以使用；
ASC：表示升序排列，默认为升序；
DESC：表示降序排列。

关于长度，使用字符串作为索引时，不指定长度也是可以的，下面全文索引中会有例子。

例：
新建该表用于新增索引：

create table index2(id int, name varchar(32), sex enum('男', '女', '未知'));

追加升序排序索引 ：

create INDEX id_no ON index2(id ASC);

在这里插入图片描述

可以通过show create table index2;语句进行查询
在这里插入图片描述

(2). 执行alter table 语句

alter table 表名 ADD INDEX 索引别名(字段名[(长度)] [ASC | DESC]);
(括号)内是必填，[中括号]是可填可不填
表名：需要创建索引的表，该表必须是已经存在的，如果不存在，需要先创建；
ADD：关键字；
INDEX：用来指定字段为索引；
索引别名：自己填的索引别名，此处必须填；
字段名：指定索引对应的字段的名称，该字段必须为前面定义好的字段；
长度：可不填，表示索引的长度，必须是字符串类型才可以使用；
ASC：表示升序排列，默认为升序；
DESC：表示降序排列。

关于长度，使用字符串作为索引时，不指定长度也是可以的，下面全文索引中会有例子。

例：
新建该表用于新增索引：

create table index3(id int, name varchar(32));

追加降序排序索引 ：

alter table index3 ADD INDEX index_id(id DESC);

在这里插入图片描述

3. 查看索引执行情况

EXPLAIN 查询语句

在这里插入图片描述
注意：如果需要促发索引去查询数据，必须指定查询条件。

key： 实际使用的索引。如果为NULL，则没有使用索引；
possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引 将被列出，但不一定被查询实际使用；
key_len： 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！

二、唯一索引

所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。

其实也是和普通索引差不多，唯一索引在创建时多写UNIQUE关键字。

1. 创建表时定义索引

create table 表名(字段1 类型, ..., 字段n 类型, UNIQUE INDEX [索引别名] (字段名[(长度)] [ASC | DESC]));

例：

create table index4(id int, name varchar(32), UNIQUE INDEX index_id(id DESC));

在这里插入图片描述

2. 已存在的表上创建索引

(1). 执行create 语句

create UNIQUE INDEX 索引别名 ON 表名(字段名[(长度)] [ASC | DESC]);

例：

新建该表用于新增索引：

create table index5(id int, name varchar(32));

创建索引：

create UNIQUE INDEX index_name ON index5(name(32));

在这里插入图片描述

(2). 执行alter table 语句

alter table 表名 ADD UNIQUE INDEX 索引别名(字段名[(长度)] [ASC | DESC]);

例：

新建该表用于新增索引：

create table index6(id int, name varchar(32));

创建索引：

 alter table index6 ADD UNIQUE INDEX index_name(name(32) DESC);

在这里插入图片描述

三、全文索引

全文索引主要对字符串类型建立基于分词的索引，主要是基于char、varchar和text的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。

在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。

通俗讲解：大家都用过百度引擎搜索东西吧，当我们输入关键字搜索时，页面会将有相同关键字的连接显示出来。这也就是全文索引的使用。
在这里插入图片描述

1. 创建表时定义索引

create table 表名(字段1 类型, ..., 字段n 类型 FULLTEXT INDEX [索引别名](字段名[长度]));

例：

 create table index7(id int, _text varchar(128), FULLTEXT INDEX index_text(_text(128)));

在这里插入图片描述

插入数据：

insert into index7 values(1, '我是一名共青团员，我爱祖国');

用全文检索索引快速查询记录：
select * from 表名 where match(字段名) AGAINST('需要匹配的字符串');

select * from index7 where match(_text) AGAINST('我爱祖国');

在这里插入图片描述
注意，必须输入以逗号隔开的一整段才行。

否则：
在这里插入图片描述

但是MySQL8也支持中文分词查询；相关设置如下第四步骤。

2. 已存在的表上创建索引

(1). 执行create 语句

在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句create FULLTEXT INDEX来实现，其语法形式如下：

create FULLTEXT INDEX 索引别名 ON 表名(字段名[(长度)]);

例:

新建该表用于新增索引：

create table index8(id int, _text varchar(128));

新增索引：

create FULLTEXT INDEX index_text ON index8(_text(128));

在这里插入图片描述

可以通过show create table index8;语句查看
在这里插入图片描述

(2). 执行alter table 语句

除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句alter来实现，其语法形式如下：

alter table 表名 ADD FULLTEXT INDEX 索引别名(字段名([长度]));

例：

新建该表用于新增索引：

create table index9(id int, _text varchar(128));

新增索引：

alter table index9 ADD FULLTEXT INDEX index_text(_text(128));

在这里插入图片描述

3. 使用场景

根据全文索引字段进行全文检索数据：

select * from 表名 where MATCH(字段名) AGAINST('关键字');

例:

insert into index9 values(1, '我是一名光荣的打工人，我爱打工');

select * from index9 where MATCH(_text) AGAINST('我爱打工');

在这里插入图片描述

只能通过以逗号隔开的语段进行查询，但是MySQL8也支持中分分词查询；相关设置如下第四步骤。

4. MySQL8 中文分词支持

配置文件my.ini（Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0） 中增加如下配置项，同时重启MySQL80 服务。

[mysqld]
ngram_token_size=2

找到如下图片对应的.ini文件，打开并找到[mysqld]项，在其下面添加ngram_token_size=2 MySQL就可以支持以两个中文字符作为分词的检索了。

正确的来说是支持两个中文字符以上的中文字符进行检索
在这里插入图片描述

在这里插入图片描述

添加好后，重启MySQL服务。

设置好后，MySQL以两个以上的中文字符作为分词。

如下图：
在这里插入图片描述
“我是”：是一组；“是谁”：是一组；…；“地方”：是一组。

正确的来说是支持两个中文字符以上的中文字符进行检索

例:

  1. 创建新表时：

    create table index10(id int, _text varchar(128), FULLTEXT INDEX index_text(_text) with parser ngram);

    在后面加上with parser ngram
    在这里插入图片描述

    目前博主只会在新创建表时添加设置，如果是在已有的表上添加，这个博主目前貌似还不知道怎么操作，也不知可不可以进行这样的操作。

  2. 插入数据，并测试：

    insert into index10 values(1, '我是谁？我在那里？这是什么地方？');

select * from index10 where MATCH(_text) AGAINST('我是');
select * from index10 where MATCH(_text) AGAINST('么地方');#也支持三个以上的中文检索
select * from index10 where MATCH(_text) AGAINST('么地');

    在这里插入图片描述

四、多列索引

多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

1. 创建表时定义索引

create table 表名(字段1 类型, 字段2 类型, ..., 字段n 类型, INDEX [索引别名](字段名1[长度] [ASC | DESC], ..., 字段名n[长度] [ASC | DESC]));

注意：和普通索引定义基本相同，不同之处就是增加了多个索引列。

例：

create table index11(id int, _text varchar(128), sex enum('男', '女'), INDEX index_id_text(id DESC, _text));

创建表以id字段和_text字段为索引
在这里插入图片描述
使用show create table index11;查看表定义
在这里插入图片描述

插入数据并使用索引进行查询
在这里插入图片描述
仅根据id查询记录才会启用多列索引

2. 已存在的表上创建索引

(1). 执行create 语句

create INDEX 索引别名 ON 表名(字段名1[(长度)][ASC | DESC], ..., 字段名n[(长度)][ASC | DESC]);

例：

新建该表用于新增索引：

create table index12(id int, _text varchar(128));

新增索引：

create INDEX index_id_text ON index12(id, _text);

在这里插入图片描述
可以通过show create table index12;语句查看
在这里插入图片描述

(2). 执行alter table 语句

除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句alter来实现，其语法形式如下：

alter table 表名 ADD INDEX 索引别名(字段名1([长度])[ASC | DESC], ..., 字段名n([长度])[ASC | DESC]);

例：

新建该表用于新增索引：

create table index13(id int, _text varchar(128));

新增索引：

alter table index13 ADD INDEX index_id_text(id DESC, _text(128));

在这里插入图片描述

五、隐藏索引

MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。

隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

alter table 表名 ALTER INDEX 索引别名 INVISIBLE; #隐藏索引
alter table 表名 ALTER INDEX 索引别名 VISIBLE; #取消隐藏

例：
我们以表index13为例，先看看index13的索引：
show create table index13;
在这里插入图片描述

隐藏索引：

alter table index13 ALTER INDEX index_id_text INVISIBLE;

在这里插入图片描述

再次查看表index13的索引：
在这里插入图片描述
注意如果出现图片中的字样，说明已经被隐藏了。

有兴趣的小伙伴，可以自行插入数据进行测试。
简单粗暴的测试：插入上千万条数据，在表没有索引的情况下进行查询和表有索引的情况下进行查询，然后再对比两者所用的时间，就可以知道结果啦！

取消隐藏：

alter table index13 ALTER INDEX index_id_text VISIBLE;

在这里插入图片描述

再次查看表index13的索引：
在这里插入图片描述

六、删除索引

所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。

drop INDEX 索引别名 ON 表名;

例：

drop INDEX index_id_text ON index13;

在这里插入图片描述
查看：
在这里插入图片描述
表index13的索引已经被删除了。

七、修改索引

最好的办法就是：先删除，后增加。

总结：

MySQL的索引操作已经全部记录下来了，可以根据项目需要进行变更。索引对于数据查询还是有很多好处的，如果表中有索引，且需要插入大量数据时，推荐先删除然后再进行插入。

今天是冬至，加班写下这篇博客，记录笔记并分享，希望对看到此篇博客的小伙伴有帮助。❀冬至快乐❀

MySQL - 普通索引
骆驼胡杨的博客
07-20 4378
参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数length是可选参数，表示索引的长度，必须是字符串类型才可以使用；是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；参数propname用来指定索引所关联的字段的名称；..
MySQL-数据库基础与索引解析
weixin_42011493的博客
05-09 1227
1.操作与落地 1.1.数据库基础知识 1.1.1.范式化设计 1.1.1.1.什么是范式 范式来自英文Normal Form，简称NF。 实际上你可以把它粗略地理解为 一张数据表的表结构所符合的某种设计标准的级别 。就像家里装修买建材，最环保的是E0级，其次是E1级，还有E2级等等 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式5NF，又称完美范式）。 满足最低要求的范式是第一范式（1NF），在第一范式的基础上
查找表索引
weixin_33720078的博客
08-31 115
SELECT 索引名称=a.name ,表名=c.name ,索引字段名=d.name ,索引字段位置=d.colid FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN ...
表索引
木子皿--啥都不会的菜鸟
06-09 1074
文章目录1 索引概述 1 索引概述 基本概念： 日常生活中，我们经常会在电话号码簿中查阅“某人”的电话号码，按姓查询或者按字母排序查询； 在字典中查阅“某个词”的读音和含义等等，以快速的找到特定记录。在这里，“姓”和“字母”都可看作是索引， 而按“姓”或者“字母”查询则是按索引查询！ 索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。 通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库
数据库表 索引
最新发布
weixin_68547003的博客
03-03 977
数据库表 索引
表的索引
Q_feifeiyu的博客
06-29 782
表的索引 1、索引 目的：提高检索速度 原理：在操作系统高层次使用 二分思想 和 Binary Tree 来维护这个索引 特点：索引不用加内存，不用改程序，不用调sql，只要执行正确的create index，就可以提高查询速度。 索引占用磁盘空间，是以增加大量的IO操作来提高检索速度，空间换时间 添...
hive-1.1.0-cdh5.14.2.tar.gz和mysql-connector-java-5.1.38.jar连接包
06-26
10. **Hive优化**: 了解如何优化Hive查询，如使用合适的文件格式（如ORC或Parquet）、创建适当的索引、优化JOIN操作等，对于提升Hive在大数据场景下的性能至关重要。 在实际操作中，用户需要安装和配置Hadoop、CDH...
mysql-8.0.23-windows64位免安装版，功能强大，向下兼容
01-21
- **MySQL Shell**：一个统一的命令行工具，支持JavaScript和Python，简化数据库管理和脚本编写。 7. **向下兼容性**： - MySQL 8.0.23保持了对早期版本的兼容，这意味着已经存在的数据库和应用程序在升级后依然...
mysql-5.0.18
09-09
- **索引**：合理创建索引可以极大提升查询效率，但过多的索引可能会影响写操作速度。 总之，MySQL 5.0.18 是一个功能强大的数据库系统，尤其适合需要高效、稳定数据存储的 Web 应用。了解并掌握其特性和使用方法...
Percona Toolkit使用测试（13）PT-MYSQL-SUMMARY
m0_37827567的博客
06-19 353
pt-mysql-summary - 很好地总结MySQL信息。 pt-mysql-summary方便地总结了MySQL数据库服务器的状态和配置，以便您可以快速了解它。它不是一个调优工具或诊断工具。它生成一个易于区分的报告，可以粘贴到电子邮件中而不丢失格式。它应该可以在任何现代UNIX系统上很好地工作。 pt-mysql-summary通过连接到MySQL数据库服务器并查询它的状态和配置信息来工作...
数据库索引实例
weixin_33973609的博客
05-03 405
参考文献： [1].漫谈数据库索引 1.创建表并插入数据 在Sql Server2008中创建测试数据库Test，接着创建数据库表并插入数据，sql代码如下： USE Test IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'emp_pay') DROP TABLE ...
表格索引A--Z
LCSDN695的博客
10-25 436
在VC.m里面写: #import “ViewController.h” @interface ViewController ()<UITableViewDelegate,UITableViewDataSource> @property(nonatomic,strong) UITableView * tableView; @property(nonatomic,strong) NSMu...
MySQL - 全文索引
骆驼胡杨的博客
07-21 929
配置文件my.ini（Windows10默认路径C\ProgramData\MySQL\MySQLServer8.0）中增加如下配置项，同时。在上述语句中，关键字CREATEFULLTEXTINDEX表示用来创建全文索引。
表和索引
jjstu的专栏
04-21 744
 表和索引 表是用来存储数据和操作数据的逻辑结构，关系数据库中的数据都表现为表的形式。表的结构包括列（column）和行（row）。索引是有效组织表数据的方式，它由表中一列或多列按照一定得排列顺序组成。也是一种数据对象。  1 掌握创建、删除表的方法 T_sql   Create table   [ database_name.[owner].| [ow
MySQL表索引
sunny05296的博客
08-10 549
单列索引 单列索引对where条件中出现的位置无要求，都有效。 @Table(name = "tb_person", indexes = {@Index(name = "name_idx", columnList = "name")}) public class Person implements Serializable { } 联合索引 select * from my
数据库索引
weixin_30443813的博客
03-26 107
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。 索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。 基本特点 建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是...
表索引的定义、创建和使用
JavaWHD的博客
05-12 4578
什么是索引 索引的描述： 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 索引的作用： 当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后
表索引(索引的设计原则)详解
m0_65635427的博客
04-29 2077
表索引(索引的设计原则)详解
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

cpp_learners

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值