MySQL - key、primary key、unique key、index 区别(一)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL - key、primary key、unique key、index 区别(一)

索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。

表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。

如果一个表有1000行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。


所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。

索引用于:

  • 快速找出匹配一个WHERE子句的行
  • 当执行联结时,从其他表检索行
  • 对特定的索引列找出MAX()或MIN()值

如果排序或分组在一个可用键的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。

如果所有键值部分跟随DESC,键以倒序被读取。 在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。

如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。



建表的语句:


CREATE TABLE `phpcolor_ad` (  
    `id` mediumint(8) NOT NULL AUTO_INCREMENT,  
    `name` varchar(30) NOT NULL,  
    `type` mediumint(1) NOT NULL,  
    `code` text,  
    PRIMARY KEY (`id`),  
    KEY `type` (`type`)  
);  

最后一句的KEY `type` (`type`)是什么意思?

如果只是key的话,就是普通索引

mysql 的 key 和 index 多少有点令人迷惑,单独的key和其它关键词结合的 key(primary key)实际表示的意义是不同,这实际上考察对数据库体系结构的了解的。


一、key 是数据库的物理结构,它包含两层意义和作用,

(1)是约束(偏重于约束和规范数据库的结构完整性)

(2)是索引(辅助查询用的)

Ps:包括primary key, unique key, foreign key 等。



primary key 有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个主键索引;    

PRIMARY KEY 约束:唯一标识数据库表中的每条记录;

主键必须包含唯一的值;

主键列不能包含 NULL 值;

每个表都应该有一个主键,并且每个表只能有一个主键。(PRIMARY KEY 拥有自动定义的 UNIQUE 约束)



unique key 也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个唯一索引;

UNIQUE 约束:唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

(每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束)



foreign key 也有两个作用,一是约束作用(constraint),规范数据的引用完整性,但同时也在这个key上建立了一个index。



可见,mysql的key是同时具有constraint和index的意义,这点和其他数据库表现的可能有区别。

(至少在Oracle上建立外键,不会自动建立index),因此创建key也有如下几种方式:

(1)在字段级key方式建立, 如 create table t (id int not null primary key);

(2)在表级constraint方式建立,如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));

(3)在表级key方式建立,如create table t(id int, primary key (id));

其它key创建类似,但不管那种方式,既建立了constraint,又建立了index,只不过index使用的就是这个constraint或key。



二、index是数据库的物理结构,它只是辅助查询的,它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。

索引要分类的话,分为前缀索引、全文本索引等。

因此,索引只是索引,它不会去约束索引的字段的行为(那是key要做的事情)。如:create table t(id int,index inx_tx_id (id));



三、总结,最后的释疑:

(1)我们说索引分类,分为:

主键索引(必须指定为“PRIMARY KEY”,没有PRIMARY Index)

唯一索引(unique index,一般写成unique key)

普通索引(index,只有这一种才是纯粹的index)等,也是基于是不是把index看作了key。比如 create table t(id int, unique indexinx_tx_id (id));--index当作了key使用

(2)最重要的也就是,不管如何描述,需要理解index是纯粹的index(普通的key,或者普通索引index),还是被当作key(如:unique index、unique key和primary key),若当作key时则会有两种意义或起两种作用。



MySQL Key值(PRI, UNI, MUL)的含义:

PRI主键约束;

UNI唯一约束;

MUL可以重复。

注:若是普通的key或者普通的index(实际上,普通的key与普通的index同义)。

当我们在desc 表名; 的时候,有一个Key值,表示该列是否含有索引。假设表结构如下所示:


image.png



我们看到Key那一栏,可能会有4种值,即'啥也没有','PRI','UNI','MUL'

1. 如果Key是空的, 那么该列值的可以重复,表示该列没有索引, 或者是一个非唯一的复合索引的前导列

2. 如果Key是PRI,  那么该列是主键的组成部分

3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列),且不能含有空值(NULL)

4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL



注:

1、如果对于一个列的定义,同时满足上述4种情况的多种,比如一个列既是PRI,又是UNI(如果是PRI,则一定是UNI)

那么"desc 表名"; 的时候,显示的Key值按照优先级来显示 PRI->UNI->MUL,那么此时,显示PRI。

2、如果某列不能含有空值,同时该表没有主键,则一个唯一性索引列可以显示为PRI。

3、如果多列构成了一个唯一性复合索引,那么一个唯一性索引列可以显示为MUL。(因为虽然索引的多列组合是唯一的,比如ID+NAME是唯一的,但是每一个单独的列依然可以有重复的值,因为只要ID+NAME是唯一的即可)。

 

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
37
分享
相关文章
mysql 的ReLog和BinLog区别
MySQL中的重做日志和二进制日志是确保数据库稳定性和可靠性的关键组件。重做日志主要用于事务的持久性和原子性,通过记录数据页的物理修改信息来恢复未提交的事务;而二进制日志记录SQL语句的逻辑变化,支持数据复制、恢复和审计。两者在写入时机、存储方式及配置参数等方面存在显著差异。
MySQL 和 Oracle 的区别?
本文对比了Oracle和MySQL数据库的多个方面。Oracle适用于大型数据库,支持高并发和大访问量,市场占有率为40%,安装占用空间较大,约3G;而MySQL适合中小型应用,是开源免费的,安装仅需152M。两者在主键生成、字符串处理、SQL语句、事务处理等方面存在差异。Oracle功能更为强大,尤其在企业级应用中表现突出,而MySQL则以简单易用见长。
MySQL 和 Oracle 的区别?
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
176 10
MySQL引擎InnoDB和MyISAM的区别?
InnoDB是MySQL默认的事务型存储引擎,支持事务、行级锁、MVCC、在线热备份等特性,主索引为聚簇索引,适用于高并发、高可靠性的场景。MyISAM设计简单,支持压缩表、空间索引,但不支持事务和行级锁,适合读多写少、不要求事务的场景。
85 9
MySQL和Redis的区别
**MySQL和Redis的区别** MySQL和Redis都是流行的数据存储解决方案,但它们在设计、用途和特性上有显著区别。理解这些区别有助于选择合适的数据库来满足不同的应用需求。本文将详细介绍MySQL和Redis的区别,包括它们的架构、使用场景、性能和其他关键特性。 ### 一、基本概述 **MySQL**: MySQL是一个关系型数据库管理系统(RDBMS),使用结构化查询语言(SQL)进行数据管理。它支持事务、复杂查询和多种存储引擎,广泛应用于各种Web应用、企业系统和数据分析项目。 **Redis**: Redis是一个基于内存的键值数据库,通常被称为NoSQL数
299 4
mysql 的ReLog和BinLog区别
MySQL中的重做日志(Redo Log)和二进制日志(Binary Log)是两种重要的日志系统。重做日志主要用于保证事务的持久性和原子性,通过记录数据页的物理修改信息来恢复未提交的事务更改。二进制日志则记录了数据库的所有逻辑变化操作,用于数据的复制、恢复和审计。两者在写入时机、存储方式、配置参数和使用范围上有所不同,共同确保了数据库的稳定性和可靠性。
138 2
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
154 42
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
97 25
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等