文章目录
一.存储引擎
存储引擎就是如何存储数据、如何为数据建立索引和如何更新、查询数据等技术的实现方法。
MySQL 默认支持多种存储引擎,以适用于不同领域 的数据库应用需要,用户可以通过选择使用不同的存储引擎提高应用的效率,提供灵活的存储。
1.查看当前存储引擎
show variables like '%storage_engine';
# 或
show engines;
2.MySQL常用存储引擎
(1) InnoDB
事务型数据库的首选引擎,⽀持事务安全表(ACID),支持行锁定和外键,InnoDB是默认的MySQL引擎。
InnoDB主要特性有:
- InnoDB 给 MySQL 提供了具有提交、回滚、崩溃恢复能力的事务安全存储引擎。
- InnoDB 是为处理巨大数据量的最大性能设计。它的 CPU 效率比其他基于磁盘的关系型数据库引擎高。
- InnoDB 存储引擎自带缓冲池,可以将数据和索引缓存在内存中。
- InnoDB 支持外键完整性约束。
- InnoDB 被用在众多需要高性能的大型数据库站点上
- InnoDB 支持行级锁
(2)MyISAM
MyISAM 基于 ISAM 存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之⼀。MyISAM 拥有较高的插入、查询速度,但不支持事物。
MyISAM主要特性有:
- 大文件支持更好
- 当删除、更新、插入混用时,产生更少碎片。
- 每个 MyISAM 表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16
- 最大的键长度是1000字节。
- BLOB和TEXT列可以被索引
- NULL 被允许在索引的列中,这个值占每个键的0~1个字节
- 所有数字键值以高字节优先被存储以允许⼀个更高的索引压缩
- MyISAM 类型表的 AUTO_INCREMENT 列更新比 InnoDB 类型的AUTO_INCREMENT 更快
- 可以把数据文件和索引文件放在不同目录
- 每个字符列可以有不同的字符集
- 有 VARCHAR 的表可以固定或动态记录长度
- VARCHAR 和 CHAR 列可以多达 64KB
- 只支持表锁
(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.索引创建原则
- 适合用于频繁查找的列
- 适合经常用于条件判断的列
- 适合经常由于排序的列
- 不适合数据不多的列
- 不适合很少查询的列
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