主键索引基于表结构创建主键,索引

转载

mob6454cc743894 2024-07-28 21:03:48

文章标签 主键索引基于表结构创建数据库主键数据外键 文章分类 数据仓库大数据

主键的任务是帮助mysql以最快的速度把一条特定的数据记录在数据表里的位置确定下来。主键必须满足以下两个条件：

1.主键必须是惟一的，任意两条数据记录里的主键字段不允许是同样的内容。

2.主键应该是紧凑的。因为主键都必须有索引，主键越紧凑主索引上的管理效率越高，所以主键最好是整数类型。另外，主键一般都被用做另外一个表的外键，外键紧凑，工作效率也会提高。

create 
    
  table 
   publishers
(publID  
  int 
    
  not 
    
  null 
   auto_increment,
 other columns
,
  
  primary 
    
  key 
   (publID))

外键的任务是在一个表中引用另外一个数据表的某条记录。

只要数据库里发生了变化，所谓的“外键约束条件”(也叫一致性约束)就回去检查是否有数据表之间的交叉引用关系受到影响。根据外键的具体声明情况，数据记录删除操作的最终结果不外乎两种：一是不允许执行这一操作；二是把其他数据表里与之相关的数据记录也全部删掉。具体采用哪种要看你使用了哪个选项。

下面的SQL代码演示了如何声明一个外键字段时给它加上一致性原则(外键约束条件)：

主键索引基于表结构创建主键,索引_外键

create     
  table 
   titles
  (column1,column2,
,
   publID    int 
  ,
      foreign 
    
  key 
   (publID)  
  references 
   publishers (publisherID)
  )

上面这条命令把titles.publID字段明确的定义为一个指向主键publishers .publisherID的外键。在此基础上，还可以利用on delete, restrict和on delete cascade等几个选项对数据库系统在它的引用一致性遭到破环是应该如何响应。

下面是为外键字段table1.column1设置外键约束条件的基本语法：

主键索引基于表结构创建主键,索引_外键

foreign       key 
    
  [ 
  name 
  ] 
   (column1)  
  references 
   table2 (column2)
    [   on delete {cascade | set null | no action | restrict} 
  ] 
  
    [   on update {cascade | set null | no action | restrict} 
  ]

这个外键约束表示的是table2有变化时，table1应该作何反应。

设置外键约束条件的前提

①数据列table1.column1和table2.column2都必须配有至少一个普通索引。如果没有的话，必须先用alter table命令自行创建。table2.column2通常是主键，但不必须是。

②数据列table1.column1和table2.column2必须匹配到无须进行数据类型装换，就可以直接比较。最好都是int或bigint。

③如果还选了on delete/update set null，就必须允许table1.column1取值为null。

④外键约束必须从一开始就得到满足。

索引

索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。数据库索引和书的索引具有相同的功能。

1.普通索引

普通索引(由关键字key或index定义的索引)的唯一任务是加快对数据的访问速度。因此，应该为那些最经常出现的查询条件（where column=…）或排序条件（order by column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐，最紧凑的数据列来创建索引。

2.唯一索引

普通索引允许被索引的列包含重复的值。如果能确定某个数据列只包含各不相同的值，为它创立索引时就可以用关键字unique把它定义为唯一索引。这样做好处有两个：①管理更有效率；②有新纪录插入数据表时，自动检查新纪录的这个字段是否已经出现了，如果是则拒绝插入。很多情况下，唯一索引不是为了提高访问速度，二是为了避免数据出现重复。

3.主索引

主索引就是为主键字段建立的索引。使用关键词primary。因为主键值必须是唯一的，索引主索引也是唯一索引。
注意唯一和主键的区别，它们都是创建一个唯一的索引，一个表格仅含有一个主键约束列，但是，它有可能在其他列中含有许多的唯一约束。

4.复合索引

索引可以覆盖多个数据列，就像index(columnA,columnB)。index(A,B,C)可以当做A或（A，B）的索引来使用，但不能当做B,C,(B,C)的索引。符合索引主要是为了提高索引效率，复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。
当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。

索引的另一种分类：聚簇索引和非聚簇索引。

在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。

聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。

主键和聚簇索引的比较

	主键	聚簇索引
用途	强制表的实体完整性	对数据行的排序，方便查询用
一个表多少个	一个表最多一个主键	一个表最多一个聚集索引
是否允许多个字段来定义	一个主键可以多个字段来定义	一个索引可以多个字段来定义

是否允许 null 数据行出现	如果要创建的数据列中数据存在null，无法建立主键。创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL。	没有限制建立聚集索引的列一定必须 not null . 也就是可以列的数据是 null 参看最后一项比较
是否要求数据必须唯一	要求数据必须唯一	数据即可以唯一，也可以不唯一。看你定义这个索引的 UNIQUE 设置。（这一点需要看后面的一个比较，虽然你的数据列可能不唯一，但是系统会替你产生一个你看不到的唯一列）

创建的逻辑	数据库在创建主键同时，会自动建立一个唯一索引。如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引	如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。

索引的缺陷：

①虽然它们可以加快数据检索操作，但是这会使数据修改操作变慢。每修改一条数据记录，索引就必须刷新一次。如果有许多记录需要修改时，可以用delay_key_write选项把更新暂时关闭，等全部记录修改或插入完成后再更新索引。

②索引会在硬盘上占用相当大的空间。因此，应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复内容，为它建立索引就没有太大的意义了。

视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

所以视图不是真实存在的基础表而是一张虚表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。

创建视图——CREATE VIEW

主键索引基于表结构创建主键,索引_外键

CREATE       [   OR REPLACE 
  ] 
    
  [ 
  ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} 
  ] 
  
        VIEW       [ 
  db_name. 
  ] 
  view_name  
  [ 
  (column_list) 
  ] 
  
        AS    select_statement
        [   WITH [CASCADED | LOCAL   ] 
    
  CHECK 
    
  OPTION 
  ]  
 
     Code

主键索引基于表结构创建主键,索引_外键

本例创建一个产品表（product）和一个购买记录表（purchase），再通过视图purchase_detail查询出购买的详细信息。

主键索引基于表结构创建主键,索引_外键

CREATE TABLE product
(
        product_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    price DOUBLE NOT NULL
);
INSERT INTO product VALUES(1, 'apple ', 5.5);
CREATE TABLE purchase
(
        id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL DEFAULT 0,
    gen_time DATETIME NOT NULL
);
INSERT INTO purchase VALUES(1, 1, 10, NOW());
CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

主键索引基于表结构创建主键,索引_外键

创建成功后，输入：SELECT * FROM purchase_detail;

主键索引基于表结构创建主键,索引_外键

运行效果如下：

主键索引基于表结构创建主键,索引_外键

+-------+-------+-----+-------------+
| name | price | qty | total_value |
+-------+-------+-----+-------------+
| apple |   5.5 | 10 |          55 |
+-------+-------+-----+-------------+
1 row in set (0.01 sec)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。