MySQL数据库高级特性：存储引擎、索引、表关系和外键、存储过程

一.存储引擎

存储引擎就是如何存储数据、如何为数据建立索引和如何更新、查询数据等技术的实现方法。
MySQL 默认支持多种存储引擎，以适用于不同领域 的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储。

1.查看当前存储引擎

show variables like '%storage_engine';
# 或
show engines;

2.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 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

3.存储引擎的选择

⼀般来说，对插入和并发性能要求较高的，或者需要外键，或者需要事务支持的情况下，需要选择 InnoDB，插入较少，查询较多的场景，优先考虑 MyISAM。

4.使用引擎

⼀般在建表时添加

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

5.InnoDB 和 MyISAM 在文件方面的区别

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

二.索引

1.概念

索引就是为特定的 mysql 字段进行⼀些特定的算法排序，比如⼆叉树的算法和哈希算法，哈希算法是通过建立特征值，然后根据特征值来快速查找。
MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

用的最多，并且是MySQL默认的索引数据结构：btree，通过btree算法建立索引的字段。

哈希索引比较特殊，时间复杂度为 O(1)，但只适合等值比较方式的查询，不适合范围或大小比较进行查询
索引的优点:
　　速度快！使用索引能极大提升查询速度。
索引的缺点:
　　额外的使用了⼀些存储的空间
　　索引会让写的操作变慢

2.索引创建原则

1. 适合用于频繁查找的列
2. 适合经常用于条件判断的列
3. 适合经常由于排序的列
4. 不适合数据不多的列
5. 不适合很少查询的列

3.创建索引

（1）建表时添加索引

create table 表 (
   id int not null,
   name varchar(16) not null,
   index 索引名(字段名(⻓度))
);

说明：主键id和唯一值的name有索引
　　　长度：要索引查询的长度
　　　如：index idx_name(name(2)) ：索引名字中前两个字，只要符合，就被索引到。

（2）后期添加索引

create index `索引名` on 表名(字段名(⻓度));

示例：

create index idx_birthday on student(birthday); 

birthday后面没加长度，表示直接索引原本birthday的长度

运行结果：

4.删除索引

drop index [索引名] on 表;

示例：

drop index idx_birthday on student;

5.唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯⼀，但允许有空值。如果是组合索引，则列值的组合必须唯⼀。

create unique index 索引名 on 表(字段名(⻓度));
-- 或
create table 表(
   id int not null,
   username varchar(16) not null,
   unique 索引名 (字段名(⻓度))
);

name使用的是唯一索引

6.查看索引

（1）查看表中全部索引

show index from table_name;

在查询索引语句后面加上‘\G’，是为了换行，使页面更好看

（2）查看语句中有没有使用索引

explain select * from student where birthday between '1996-01-01' and '2000-01-01';

三.关系与外键

1.表关系

（1）一对一
在A表中有一条记录，在B表中同样有唯一条记录相匹配
如：学生表和成绩表
（2）一对多/多对一
在 A 表中有⼀条记录，在 B 表中有多条记录⼀直对应
如: 博客中的用户表和文章表

建立关联：在‘多’表中添加‘一’表的字段

（3）多对多
A 表中的⼀条记录有多条 B 表数据对应, 同样 B 表中⼀条数据在 A 表中也有多条与之对应
如: 博客中的收藏表

建立关联：创建关系表进行关联

构造数据：

-- ⽤户表
create table `user` (
       `id` int unsigned primary key auto_increment,
       `name` char(32) not null unique
) charset=utf8mb4;
 -- 商品表
create table `product` (
       `id` int unsigned primary key auto_increment,
       `name` char(32) not null unique,
       `price` float
 ) charset=utf8mb4;

 -- ⽤户信息表: ⼀对⼀
 create table `userinfo` (
       `id` int unsigned primary key auto_increment,
       `phone` int unsigned unique,
       `age` int unsigned,
       `location` varchar(128)
 ) charset=utf8mb4;

 -- ⽤户组表: ⼀对多
 create table `group` (
       `id` int unsigned primary key auto_increment,
       `name` char(32) not null unique
 ) charset=utf8mb4;

 -- 订单表: 多对多
 create table `order` (
       `id` int unsigned primary key auto_increment,
       `uid` int unsigned,
       `pid` int unsigned
 ) charset=utf8mb4;

2.外键

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

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

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

添加外键：

-- 为 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_order_uid` foreign key(`uid`) references `user`(`id`);
alter table `order` add constraint `fk_order_pid` foreign key(`pid`) references `user`(`id`);

四.存储过程

存储过程（Stored Procedure）是⼀种在数据库中存储复杂程序，以便外部程序调用的⼀种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语⾔层面的代码封装与重用。

1.优缺点

（1）优点
存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。
（2）缺点
存储过程，往往定制化于特定的数据库上，因为支持的编程语⾔不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

2.语法

（1）声明语句结束符，可以自定义:
存储过程中有很多的SQL语句，SQL语句的后面为了保证语法结构必须要有分号（;），但是默认情况下分号表示客户端代码发送到服务器执行，必须更改结束符。

DELIMITER $$
-- 或者
DELIMITER //

效果图：

（2）声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

（3）存储过程开始和结束符号:

BEGIN .... END

（4）变量赋值：

SET @p_in=10

（5）变量定义：

DECLARE l_int int unsigned default 10000;

（6）创建mysql存储过程、存储函数:

create procedure 存储过程名(参数)

（7）存储过程体:

create function 存储函数名(参数)

3.使用

（1）简单用法：

-- 定义
-- 如果存储过程中就⼀条SQL语句，begin…end两个关键字可以省略
create procedure get_info()
select * from student;
-- 调⽤
call get_info();

（2）复杂用法 （备注：只能在标准 mysql 客户端中执⾏，mycli ⽆法识别）

delimiter // -- 定义前，将分隔符改成 //
create procedure  demo_in_parameter(in user_id int)
begin
select * from student where `id`=user_id;
update student set `money`=1000 where `id`=user_id;
select * from student where `id`=user_id;
end//
delimiter ; -- 定义完以后可以将分隔符改回 分号
call  demo_in_parameter(10);

4.扩展

https://www.zhihu.com/question/19749126
https://segmentfault.com/q/1010000004907411