数据库高级特性

数据库高级特性

一、存储引擎

存储引擎就是如何存储数据、如何为数据建立索引和如何更新、查询数据等技术的实现方法。

MySQL 默认支持多种存储引擎，以适用于不同领域 的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储。

查看当前的存储引擎

show variables like '%storage_engine';
show engines;

MySQL 常用的存储引擎

1. InnoDB

   事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。

   InnoDB主要特性有：

   1. InnoDB 给 MySQL 提供了具有提交、回滚、崩溃恢复能力的事务安全存储引擎。
   2. InnoDB 是为处理巨大数据量的最大性能设计。它的 CPU 效率比其他基于磁盘的关系型数据库引擎高。
   3. InnoDB 存储引擎自带缓冲池，可以将数据和索引缓存在内存中。
   4. InnoDB 支持外键完整性约束。
   5. InnoDB 被用在众多需要高性能的大型数据库站点上
   6. InnoDB 支持行级锁

2. MyISAM

   MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。

   MyISAM主要特性有：

   1. 大文件支持更好
   2. 当删除、更新、插入混用时，产生更少碎片。
   3. 每个 MyISAM 表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
   4. 最大的键长度是1000字节。
   5. BLOB和TEXT列可以被索引
   6. NULL 被允许在索引的列中，这个值占每个键的0~1个字节
   7. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
   8. MyISAM 类型表的 AUTO_INCREMENT 列更新比 InnoDB 类型的 AUTO_INCREMENT 更快
   9. 可以把数据文件和索引文件放在不同目录
   10. 每个字符列可以有不同的字符集
   11. 有 VARCHAR 的表可以固定或动态记录长度
   12. VARCHAR 和 CHAR 列可以多达 64KB
   13. 只支持表锁

3. MEMORY

   MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

存储引擎的选择

一般来说，对插入和并发性能要求较高的，或者需要外键，或者需要事务支持的情况下，需要选择 InnoDB，

插入较少，查询较多的场景，优先考虑 MyISAM。

使用引擎

一般在建表时添加

create table abc (
    name char(10)
) engine=MyISAM charset=utf8;

create table xyz (
    name char(10)
) engine=InnoDB charset=utf8;

InnoDB 和 MyISAM 在文件方面的区别

1. InnoDB 将一张表存储为两个文件
   • demo.frm -> 存储表的结构和索引
   • demo.ibd -> 存储数据，ibd 存储是有限的, 存储不足自动创建 ibd1, ibd2
   • InnoDB 的文件创建在对应的数据库中, 不能任意的移动
2. MyISAM 将一张表存储为三个文件
   • demo.frm -> 存储表的结构
   • demo.myd -> 存储数据
   • demo.myi -> 存储表的索引
   • MyISAM 的文件可以任意的移动

二、关系与外键

关系

• 一对一
  • 在 A 表中有一条记录，在 B 表中同样有唯一条记录相匹配
  • 比如: 学生表和成绩表
• 一对多 / 多对一
  • 在 A 表中有一条记录，在 B 表中有多条记录一直对应
  • 比如: 博客中的用户表和文章表
• 多对多
  • A 表中的一条记录有多条 B 表数据对应, 同样 B 表中一条数据在 A 表中也有多条与之对应
  • 比如: 博客中的收藏表

外键

外键是一种约束。他只是保证数据的一致性，并不能给系统性能带来任何好处。

建立外键时，都会在外键列上建立对应的索引。外键的存在会在每一次数据插入、修改时进行约束检查，如果不满足外键约束，则禁止数据的插入或修改，这必然带来一个问题，就是在数据量特别大的情况下，每一次约束检查必然导致性能的下降。

出于性能的考虑，如果我们的系统对性能要求较高，那么可以考虑在生产环境中不使用外键。构造数据

-- 用户表
create table `user` (
`id` int unsigned primary key auto_increment,
 `name` char(32) not null
 ) charset=utf8;
 -- 商品表
create table `product` (
   `id` int unsigned primary key auto_increment,
    `name` char(32) not null unique,
      `price` float
      ) charset=utf8;
-- 用户信息表: 一对一
 create table `userinfo` (
		 `id` int unsigned primary key auto_increment,
 		 `phone` int unsigned unique,
   		 `age` int unsigned,
    	  `location` varchar(128)
      ) charset=utf8;
-- 用户组表: 一对多
      create table `group` (
       		`id` int unsigned primary key auto_increment,
      	    `name` char(32) not null unique
        ) charset=utf8;
-- 订单表: 多对多
        create table `order` (
            `id` int unsigned primary key auto_increment,
            `uid` int unsigned,
            `pid` int unsigned
           ) charset=utf8;
                                 

1. 添加外键

   -- 为 user 和 userinfo 建立关联的外键
   alter table userinfo add constraint fk_user_id foreign key(id) references user(id);
   
   -- 建立用户与组的外键约束
   alter table `user` add `gid` int unsigned;
   alter table `user` add constraint `fk_group_id` foreign key(`gid`) references `group`(`id`);
   -- 建立用户、商品、订单的外键约束
   alter table `order` add constraint `fk_user_id` foreign key(`uid`) references `user`(`id`);
   alter table `order` add constraint `fk_prod_id` foreign key(`pid`) references `product`(`id`);
   

2. 尝试插入数据后在删除，分别先对主表和子表进行一次删除。

3. 删除外键。

   alter table `表名` drop foreign key `外键名`