mysql添加主键索引命令 mysql主键和索引

转载

蓝色忧郁花 2023-08-07 14:13:22

文章标签 mysql添加主键索引命令 mysql 主键存储引擎 文章分类 MySQL 数据库

文章目录

mysql 主键与索引

参考
涉及术语解释
操作

建立主键
索引
主键和唯一索引

主键
索引
B树

建议

mysql 主键与索引

参考

以下链接均为mysql 官网

mysql 专业术语 PRIMARY KEY和UNIQUE索引约束 mysql 建表语句 mysql 建立索引数据库设计规范数据库范式

涉及术语解释

当然你也可以看mysql官网的解释，以下的解释是为了快速理解，可能解释的不全。

PRIMARY KEY

主键必须包含唯一值。如果主键由多列组成，则这些列中的值组合必须是唯一的。
主键列不能包含NULL值。这意味着您必须使用该NOT NULL 属性声明主键列。如果不这样做，MySQL将NOT NULL 隐式强制主键列。
表只有一个主键。
主键的名称总是主键，因此不能用作任何其他类型索引的名称。

键通常是索引的同义词。key属性主键也可以在列定义中指定为key。这是为了与其他数据库系统兼容而实现的。

索引

数据库索引是一种提高表中操作速度的数据结构。可以使用一列或多列创建索引，为快速随机查找和对记录的访问的有效排序提供基础。
在创建索引时，应该考虑所有列将用于进行SQL查询并在这些列上创建一个或多个索引。
实际上，索引也是一种表，它将主键或索引字段以及指向每个记录的指针保存到实际表中。
用户无法看到索引，它们仅用于加速查询，数据库搜索引擎将使用它来快速定位记录。
INSERT和UPDATE语句在具有索引的表上花费更多时间，而SELECT语句在这些表上变得快速。原因是在进行插入或更新时，数据库也需要插入或更新索引值。
操作

建立主键

例如以下，以下三个例子均可建立主键

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);


CONSTRAINT [constraint_name]  PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)

CREATE TABLE users(
   user_id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40),
   password VARCHAR(255),
   email VARCHAR(255)
);

ALTER TABLE table_name ADD PRIMARY KEY(primary_key_column);

删除主键
ALTER TABLE table_name  DROP PRIMARY KEY;

索引

ALTER TABLE tbl_name ADD UNIQUE index_name（column_list）
此语句创建一个值必须唯一的索引（NULL值除外，它可能多次出现）。
这种索引和“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。可为null

ALTER TABLE tbl_name ADD INDEX index_name（column_list）

这会添加一个普通索引，其中任何值可能会出现多次。
这是最基本的索引类型，而且它没有唯一性之类的限制。
创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

ALTER TABLE tbl_name ADD FULLTEXT index_name（column_list）
 这将创建一个特殊的FULLTEXT索引，用于文本搜索。
 全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

主键和唯一索引

了解主键和唯一索引，需要先了解以下几点。

mysql InnoDB 的 clustered index
B-tree

主键

此部分针对mysql innoDB，以下部分摘自mysql官网

一组列 - 并且暗示，基于这组列的索引 - 可以唯一地标识表中的每一行。因此，它必须是不包含任何NULL值的唯一索引。

InnoDB要求每个表都有这样的索引（也称为聚簇索引或集群索引），并根据主键的列值组织表存储。

选择主键值时，请考虑使用任意值（合成键），而不是依赖于从某些其他源（自然键）派生的值 。

另请参见聚簇索引，索引，自然键，合成键。

聚集索引
主键索引 的InnoDB术语 。 表存储是根据主键列的值组织的，以加快涉及主键列的查询和排序。为获得最佳性能，请根据性能最关键的查询仔细选择主键列。因为修改聚簇索引的列是一项昂贵的操作，所以选择很少或从不更新的主列。 InnoDB

在Oracle数据库产品中，此类表称为索引组织表。

索引

一种数据结构，通常通过形成 表示特定列或列集的所有值的 树结构（B树），为 表的行提供快速查找功能 。

InnoDB表始终具有表示主键的 聚簇索引。它们还可以在一列或多列上定义一个或多个二级索引。根据其结构，二级索引可以分为 部分索引， 列索引或 复合索引。

索引是查询性能的关键方面 。数据库架构师设计表，查询和索引，以允许快速查找应用程序所需的数据。理想的数据库设计 在可行的情况下使用覆盖索引 ; 查询结果完全从索引计算，而不读取实际的表数据。每个 外键约束还需要一个索引，以有效地检查父表和 子表中是否存在值。

虽然B树索引是最常见的，不同种类的数据结构的用于散列索引，如在MEMORY存储引擎和InnoDB 自适应散列索引。 R树索引用于多维信息的空间索引。

B树

一种在数据库索引中很常用的树数据结构。结构始终保持排序，从而能够快速查找完全匹配（等于运算符）和范围（例如，大于，小于和BETWEEN 运算符）。这种类型的索引可用于大多数存储引擎，例如InnoDB和 MyISAM。

因为B树节点可以有许多子节点，所以B树与二叉树不同，二叉树每个节点限制为2个子节点。

与哈希索引形成对比，哈希索引仅在MEMORY 存储引擎中可用。该MEMORY存储引擎也可以用B-tree索引，你应该选择B树索引MEMORY的表，如果某些查询使用范围经营。

术语B树的使用旨在作为一般索引设计类的参考。由于经典B树设计中不存在的复杂性，MySQL存储引擎使用的B树结构可以被视为变体。有关相关信息，请参阅MySQL Internals Manual的InnoDB页面结构 Fil Header部分 。

另请参见哈希索引。