MySQL 实验 7：索引的操作

MySQL 实验 7：索引的操作

索引是对数据表中一列或多列的值进行排序的一种结构，索引可以大大提高 MySQL 的检索速度。合理使用索引，可以大大提升 SQL 查询的性能。

索引好比是一本书前面的目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以从目录中查找，定位到 xx 内容所在页面，如果没有设置目录（索引），则只能逐字逐页阅读文本查找。

当执行查询操作时，如果不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相关的行。如果表中查询的列有一个索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据。

一、索引的优缺点

索引虽然可以提高检索的速度，但创建过多的、不必要的索引还会影响数据增、删、改的效率。

1、索引的优点

（1）索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。

（2）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（3）可以加速表和表之间的连接。

（4）在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间。

2、索引的缺点

（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

（2）索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大。

（3）对表中的数据进行增、删、改的时候，索引也要动态的维护，降低了数据的维护速度。

3、创建索引的原则

（1）在经常需要检索的列上创建索引可以加快检索的速度。

（2）在作为主键的列上创建聚簇索引可以保证该列的唯一性和组织表中数据的排列顺序。

（3）在经常用在连接的列上，一般是表中的外键创建索引，可以提高连接的速度。

（4）在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引。

（4）在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

（5）在经常使用在 WHERE 子句中的列上面创建索引，可以加快条件的判断速度。

（6）对于那些在查询中很少使用的列不应该创建索引。

（7）对于那些重复值过多的列也不应该增加索引。

（8）经常进行数据更新的列不应该创建索引。

二、索引的分类

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

1、逻辑分类

（1）按功能划分索引可以分为四类：主键索引、唯一索引、普通索引和全文索引。

主键索引：主键索引默认创建为聚簇索引，主键索引会改变表中记录的物理顺序。一张表只能创建一个主键索引，主键索引所包含的列不允许重复、不允许为 NULL。

唯一索引：唯一索引包含的数据列不允许取重复值，但允许为 NULL 值。一张表可以创建多个唯一索引，索引列的值必须唯一，如果是组合索引，则唯一索引包含的所有列的组合必须取值唯一。

普通索引：一张表可以创建多个普通索引，普通索引允许数据重复，索引所包含的列允许取 NULL 值。

全文索引：查找文本中的关键词，主要用于全文检索。

（2）按索引包含的列数可以分为两类：单列索引和多列索引（又叫组合索引）。

单例索引：一个索引只包含一个列，一个表可以有多个单例索引。

组合索引：一个组合索引包含两个或两个以上的列。查询时遵循组合索引的【最左前缀】原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

2、物理分类

按索引的存储结构划分，可以把索引分为聚簇索引（也叫聚集索引）和非聚簇索引。

（1）聚簇索引：聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式，每张表最多只能拥有一个聚簇索引。表中的数据其实就是按照聚簇索引的顺序进行排列。因此：按照聚簇索引查询速度更快。

（2）非聚簇索引：聚簇索引之外的索引称之为非聚簇索引，又称为辅助索引。查找数据时首先通过非聚簇索引找到主键值，然后到主键索引树中通过主键值找到数据行。

三、创建表的同时创建索引

（1）主键索引和唯一索引的创建请参见【MySQL 实验6：定义数据的完整性】。

（2）创建普通索引：可以使用关键字 key 或 index 创建普通索引。语法格式如下：

create table table_name(
    col_name data_type primary key,
    col_name data_type,
    ... ,
    col_name data_type,
    INDEX|KEY [索引名] (列名[(长度)] , ...)
);
-- 说明：
（1）索引名：给创建的索引取一个新名称。如果不指定则采用字段名作为索引名。
（2）列名：指定索引对应的列的名称。列名可以有多个，即创建多列索引。
（3）长度：指索引的长度，字符串类型才可以使用。
（4）可以根据表达式创建索引。

例如：

create table emp02(
    e_id int primary key,
    e_name char(20),
    birth date,
    salary decimal(10,2),
    phone char(20),
    address varchar(200),
    dept_name char(30),
    key(e_name),                               -- 不指定索引名称
    index idx_salary(salary),                  -- 指定索引名称
    index idx_dept_salary(dept_name,salary),   -- 指定多列索引
    key idx_phone(phone(11))                   -- 指定索引长度
);


-- 查看表结构
mysql> desc emp02;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  | MUL | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

-- 查看索引
mysql> show create table emp02\G
*************************** 1. row ***************************
       Table: emp02
Create Table: CREATE TABLE `emp02` (
  `e_id` int(11) NOT NULL,
  `e_name` char(20) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `phone` char(20) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `dept_name` char(30) DEFAULT NULL,
  PRIMARY KEY (`e_id`),
  KEY `e_name` (`e_name`),
  KEY `idx_salary` (`salary`),
  KEY `idx_dept_salary` (`dept_name`,`salary`),
  KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

00试环境进行据库

create table emp02(
    e_id int primary key,
    e_name char(20),
    birth date,
    salary decimal(10,2),
    phone char(20),
    address varchar(200),
    dept_name char(30),
    key(e_name),                               -- 不指定索引名称
    index idx_salary(salary),                  -- 指定索引名称
    index idx_dept_salary(dept_name,salary),   -- 指定多列索引
    key idx_phone(phone(11)) 
);

四、添加索引

数据表创建完成后可以使用 alter table 或 create index 命令添加所需的索引。

1、使用 alter table 添加索引

语法格式如下：

ALTER TABLE 表名 
ADD INDEX|KEY [索引名](字段名[(长度)], ...);

例如：

create table emp03(
    e_id int primary key,
    e_name char(20),
    birth date,
    salary decimal(10,2),
    phone char(20),
    address varchar(200),
    dept_name char(30)
);

-- 添加索引，不指定索引名
alter table emp03 add index(e_name);

-- 添加索引，指定索引名
alter table emp03 add index idx_salary(salary);

-- 添加多列索引，指定索引名
alter table emp03 add index idx_dept_salary(dept_name, salary);

-- 添加索引，指定长度
alter table emp03 add index idx_phone(phone(11));


-- 查看表结构
mysql> desc emp03;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  | MUL | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

-- 查看索引
mysql> show create table emp03\G
*************************** 1. row ***************************
       Table: emp03
Create Table: CREATE TABLE `emp03` (
  `e_id` int(11) NOT NULL,
  `e_name` char(20) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `phone` char(20) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `dept_name` char(30) DEFAULT NULL,
  PRIMARY KEY (`e_id`),
  KEY `e_name` (`e_name`),
  KEY `idx_salary` (`salary`),
  KEY `idx_dept_salary` (`dept_name`,`salary`),
  KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
2、使用 create index 添加索引

语法格式如下：

CREATE INDEX 索引名 ON 表名(字段名[(长度)], ...);

例如：

create table emp04(
    e_id int primary key,
    e_name char(20),
    birth date,
    salary decimal(10,2),
    phone char(20),
    address varchar(200),
    dept_name char(30)
);

-- 添加单列索引
create index idx_name on emp04(e_name);

-- 添加多列索引
create index idx_dept_salary on emp04(dept_name,salary);

-- 指定索引长度
create index idx_phone on emp04(phone(11));

-- 查看表结构
mysql> desc emp04;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

-- 查看索引
mysql> show create table emp04\G
*************************** 1. row ***************************
       Table: emp04
Create Table: CREATE TABLE `emp04` (
  `e_id` int(11) NOT NULL,
  `e_name` char(20) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `phone` char(20) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `dept_name` char(30) DEFAULT NULL,
  PRIMARY KEY (`e_id`),
  KEY `idx_name` (`e_name`),
  KEY `idx_dept_salary` (`dept_name`,`salary`),
  KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

五、删除索引

删除索引的语法格式如下：

-- 使用 alter table 命令删除索引
LTER TABLE 表名 DROP INDEX 索引名;
-- 使用 drop index 命令删除索引
DROP INDEX 索引名 ON 表名;

例如：

-- 查看 emp03 表中的索引
mysql> show create table emp03\G
*************************** 1. row ***************************
       Table: emp03
Create Table: CREATE TABLE `emp03` (
  `e_id` int(11) NOT NULL,
  `e_name` char(20) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `phone` char(20) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `dept_name` char(30) DEFAULT NULL,
  PRIMARY KEY (`e_id`),
  KEY `e_name` (`e_name`),
  KEY `idx_salary` (`salary`),
  KEY `idx_dept_salary` (`dept_name`,`salary`),
  KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- 删除索引：e_name
alter table emp03 drop index e_name;

-- 删除索引：idx_salary
drop index idx_salary on emp03;

-- 重新查看 emp03 表中的索引
mysql> show create table emp03\G
*************************** 1. row ***************************
       Table: emp03
Create Table: CREATE TABLE `emp03` (
  `e_id` int(11) NOT NULL,
  `e_name` char(20) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `phone` char(20) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `dept_name` char(30) DEFAULT NULL,
  PRIMARY KEY (`e_id`),
  KEY `idx_dept_salary` (`dept_name`,`salary`),
  KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/887828.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

常见的基础系统

常见的基础系统

权限管理系统支付系统搜索系统报表系统API网关系统待定。。。 Java 优质开源系统设计项目 来源&#xff1a;Java 优质开源系统设计项目 | JavaGuide 备注&#xff1a;github和gitee上可以搜索到相关项目
阅读更多...
【含文档】基于Springboot+Android的房屋租赁App（含源码+数据库+lw）

【含文档】基于Springboot+Android的房屋租赁App（含源码+数据库+lw）

1.开发环境 开发系统:Windows10/11 架构模式:MVC/前后端分离 JDK版本: Java JDK1.8 开发工具:IDEA 数据库版本: mysql5.7或8.0 数据库可视化工具: navicat 服务器: SpringBoot自带 apache tomcat 主要技术: Java,Springboot,mybatis,mysql,vue 2.视频演示地址 3.功能 系统定…
阅读更多...
【JavaEE】【多线程】进程与线程的概念

【JavaEE】【多线程】进程与线程的概念

目录 进程系统管理进程系统操作进程进程控制块PCB关键属性cpu对进程的操作进程调度 线程线程与进程线程资源分配线程调度 线程与进程区别线程简单操作代码创建线程查看线程 进程 进程是操作系统对一个正在运行的程序的一种抽象&#xff0c;可以把进程看做程序的一次运行过程&a…
阅读更多...
IMS添加实体按键流程 - Android14

IMS添加实体按键流程 - Android14

IMS添加实体按键流程 - Android14 1、实体按键信息&#xff08;Mi 9 左侧实体按键&#xff09;2、硬件添加2.1 内核添加设备节点2.2 Generic.kl映射文件2.3 映射文件文件加载loadKeyMapLocked2.4 addDeviceLocked 添加设备相关对象 3、keycode对应scankode4、KeyEvent.java 添加…
阅读更多...
京东云主机怎么用？使用京东云服务器建网站（图文教程）

京东云主机怎么用？使用京东云服务器建网站（图文教程）

京东云主机怎么用&#xff1f;非常简单&#xff0c;本文京东云服务器网jdyfwq.com使用以使用京东云服务器搭建WordPress博客网站为例&#xff0c;来详细说下京东云主机的使用方法。使用京东云服务器快速搭建WordPress网站教程&#xff0c;3分钟基于应用镜像一键搞定&#xff0c…
阅读更多...
python之详解字符串

python之详解字符串

由字符组成的序列&#xff0c;可以用单引号或双引号括起来。 1、通过下标获取字符串的字符 1.1、获取单个字符 若要获取字符串中某一个字符&#xff0c;可以通过 字符串名[index] 索引下标的方式获取。 索引的初始值为0&#xff0c;最大值为字符串长度-1。 切记&#xff0…
阅读更多...
一、图解C#教程

一、图解C#教程

一、堆和栈 程序运行时&#xff0c;数据存储在内存中。 使用堆和栈来存储数据 1、栈 栈是一个内存数组&#xff0c;先进后出原则。 可以存储&#xff1a;某些类型变量的值&#xff1b;程序当前执行环境&#xff1b;传递给方法的参数&#xff1b; 入栈&#xff1a;把数据放…
阅读更多...
自动驾驶-问题笔记-待解决

自动驾驶-问题笔记-待解决

参考线的平滑方法 参考线平滑算法主要有三种&#xff1a; 离散点平滑&#xff1b;螺旋曲线平滑&#xff1b;多项式平滑&#xff1b; 参考链接&#xff1a;参考线平滑 对于平滑方法&#xff0c;一直不太理解平滑、拟合以及滤波三者的作用与区别&#xff1b; 规划的起点&#x…
阅读更多...
计算机网络——email

计算机网络——email

pop3拉出来 超出ASCII码范围就不让传了 这样就可以传更大的文件
阅读更多...
Ubuntu 安装 Docker Compose

Ubuntu 安装 Docker Compose

安装Docker Compose # 删除现有的 docker-compose&#xff08;如果存在&#xff09; sudo rm -f /usr/local/bin/docker-compose ​ # 下载最新的 docker-compose 二进制文件 sudo curl -L "https://github.com/docker/compose/releases/latest/download/docker-compose-…
阅读更多...
JavaScript for循环语句

JavaScript for循环语句

for循环 循环语句用于重复执行某个操作&#xff0c;for语句就是循环命令&#xff0c;可以指定循环的起点、终点和终止条件。它的格式如下 for(初始化表达式;条件;迭代因子){语句} for语句后面的括号里面&#xff0c;有三个表达式 初始化表达式(initialize):确定循环变量的初始…
阅读更多...
[C语言]指针和数组

[C语言]指针和数组

目录 1.数组的地址 2.通过指针访问数组 3.数组和指针的不同点 4.指针数组 1.数组的地址 数组的地址是什么&#xff1f; 看下面一组代码 #include <stdio.h> int main() { int arr[5] {5,4,3,2,1}; printf("&arr[0] %p\n", &arr[0]); printf(&qu…
阅读更多...
LeetCode讲解篇之139. 单词拆分

LeetCode讲解篇之139. 单词拆分

文章目录 题目描述题解思路题解代码题目链接 题目描述 题解思路 我们使用一个数组记录字符串s在[0, i)区间能否使用wordDict组成 我们使用左右指针遍历字符串s的子串&#xff0c;左指针 j 为子串的左端点下标&#xff0c;右指针 i 为右端点下标的下一个 遍历过程中如果字符串s…
阅读更多...
自然语言处理问答系统

自然语言处理问答系统

✅作者简介&#xff1a;2022年博客新星 第八。热爱国学的Java后端开发者&#xff0c;修心和技术同步精进。 &#x1f34e;个人主页&#xff1a;Java Fans的博客 &#x1f34a;个人信条&#xff1a;不迁怒&#xff0c;不贰过。小知识&#xff0c;大智慧。 &#x1f49e;当前专栏…
阅读更多...
字节放大招：无需LORA训练，小红书写真轻松搞定，Pulid-Flux换脸方案来了

字节放大招：无需LORA训练，小红书写真轻松搞定，Pulid-Flux换脸方案来了

前言 在这之前&#xff0c;SD常用的换脸节点还不支持Flux模型&#xff0c;使用Flux 做虚拟模特最好的方法是炼制人脸lora&#xff0c;但是炼丹是个有技术门槛的活。 之前文章有提过字节跳动的 Pulid团队&#xff0c;率先推出了Pulid-Flux模型&#xff0c;但是之前只能在线上使用…
阅读更多...
【Redis】Hash类型的常用命令

【Redis】Hash类型的常用命令

背景&#xff1a;redis中存储数据采取key-value键值对的形式&#xff0c;而hash内部也是键值对&#xff0c;为了区别这两个东西&#xff0c;hash内部的键值对称为&#xff1a;field-value&#xff0c;而redis的为key-value&#xff0c;这里的value包括&#xff1a;field-value。…
阅读更多...
Elasticsearch基础_5.ES聚合功能

Elasticsearch基础_5.ES聚合功能

文章目录 一、数据聚合1.1、桶聚合1.1.1、单维度桶聚合1.1.2、聚合结果排序1.1.3、限定聚合范围 1.2、Metric聚合 二、聚合总结 本文只记录ES聚合基本用法&#xff0c;后续有更复杂的需求可以查看相关书籍&#xff0c;如《Elasticsearch搜索引擎构建入门与实战》 一、数据聚合…
阅读更多...
通过 Groovy 实现业务逻辑的动态变更

通过 Groovy 实现业务逻辑的动态变更

Groovy 1、需求的提出2、为什么是Groovy3、设计参考1_引入Maven依赖2_GroovyEngineUtils工具类3_GroovyScriptVar类4_脚本规则表设计5_对应的实体类6_数据库访问层7_GroovyExecService通用接口 4、测试5、其他的注意事项6、总结 1、需求的提出 在我们日常的开发过程中&#xf…
阅读更多...
【机器学习】智驭未来：探索机器学习在食品生产中的革新之路

【机器学习】智驭未来：探索机器学习在食品生产中的革新之路

&#x1f4dd;个人主页&#x1f339;&#xff1a;Eternity._ &#x1f339;&#x1f339;期待您的关注 &#x1f339;&#x1f339; ❀目录 &#x1f50d;1. 引言&#xff1a;探索机器学习在食品生产中的革新之路&#x1f4d2;2. 机器学习在食品质量控制中的应用&#x1f31e;实…
阅读更多...
MySQL之复合查询与内外连接

MySQL之复合查询与内外连接

目录 一、多表查询 二、自连接 三、子查询 四、合并查询 五、表的内连接和外连接 1、内连接 2、外连接 前面我们讲解的mysql表的查询都是对一张表进行查询&#xff0c;即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中&#xff0c;我们往往还需要对多个表…
阅读更多...
最新文章

Copyright @ 2022~2023