函数

字符串函数

函数	功能
CONCAT(S1, S2, …, Sn)	字符串拼接，将S1, S2, …, Sn拼接成一个字符串
LOWER(str)	将字符串转为小写
UPPER(str)	将字符串转为大写
LPAD(str, n, pad)	左填充，用字符串pad对str的左边进行填充，达到长度n
RPAD(str, n, pad)	右填充，用字符串pad对str的右边进行填充，达到长度n
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)	返回从字符串str的start位置起的len个长度的字符串

trim不能去除中间的空格
substring的start从1开始，不是0
substring()可以只输入两个参数，表示从start的位置截取到字符串末尾
start参数可以为负，如果是负数，则自动忽略len参数，表示截取最后几个字符
lpad和rpad如果输入的字符串过长，超过了第二个参数n，则自动变为从左往右的n个字符

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x, y)	取`x/y`的模
RAND( )	返回0~1内随机数
ROUND(x, y)	x四舍五入，并保留y位小数

日期函数

函数	功能
CURDTE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr, type)	在date后的日期加上时间长度
DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

date_add(date, interval expr, type);中，interval是固定的。

流程控制函数

函数	功能
IF(value, t, f)	如果value为true，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，则返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END	如果val1为true，返回res1，…，否则返回default
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果expr值等于val1，返回res1，…，否则返回default

ifnull('OK', 'D'); 返回’OK’
ifnull('', 'D'); 返回’’
ifnull(null, 'D'); 返回’D’

约束

关键字	约束	描述
NOT NULL	非空约束
UNIQUE	唯一约束
PRIMARY KEY	主键约束	一行数据的唯一标识，非空且唯一
DEFAULT	默认约束	保存数据时，未指定某个字段，则使用默认值
CHECK	检查约束	保证字段值满足某条件
FOREIGN KEY	外键约束	让两张表的数据建立连接，保证数据的完整性和一致性

删除/更新行为	说明
NOT ACTION	在父表中删除/更新对应记录时，首先检查该记录是否有外键，如果有则不允许删除/更新。(与RESTRICT一致)
RESTRICT	在父表中删除/更新对应记录时，首先检查该记录是否有外键，如果有则不允许删除/更新。(与NOT ACTION一致)
CASCADE	在父表中删除/更新对应记录时，首先检查该记录是否有外键，如果有则也删除/更新外键在子表中的记录
SET NULL	在父表中删除对应记录时，检查该记录是否有对应外键，如果有则设置子表中该外键的值为NULL(这要求该外键允许为NULL)
SET DEFAULT	父表有变更时，子表将外键列设为一个默认值 (InnoDB不支持)

自增 auto_increment
外键约束一般不使用
添加外键约束: alter table 表名 add constraint 外键名 foreign key (外键字段名) references 主表(主表列名);括号是必须要有的
删除外键约束: alter table 表名 drop foreign key 外键名;
设置外键在删除/更新都是级联的: alter table 表名 add constraint 外键名 foreign key (外键字段) references 主表(主表列名) on update cascade on delete cascade;

多表查询

关系	说明
一对多	多的一方建立外键，指向一，比如员工和部门
多对多	建立中间表，包含两个外键，分别关联二者主键，比如学生课程
一对一	随便在一个表中建立外键，主要用于单表拆分，对外建需要设置唯一约束

内连接:

隐式内连接: select 字段列表 from A, B where 条件;
显式内连接: select 字段列表 from A [inner] join B on 条件;

inner可以省略
查询AB交集，查询不到某张表的NULL

外连接:

左外连接: select 字段列表 from A left [outer] join B on 条件;
右外连接: select 字段列表 from A right [outer] join B on 条件;

外连接可以查找出NULL
左外连接查找左表中所有数据以及两个表的交集
右外连接查找右表中所有数据以及两个表的交集

自连接:

select 字段列表 from A 别名A join A 别名B on 条件;

当前表和自身进行连接查询，自连接需要使用表别名

联合查询:

select 字段列表 from A ... union [all] select 字段列表 from B;

联合查询是将多次查询结果合并起来，形成新查询结果集
all将查询结果合并，如果不使用all，则会去重
两个不同的select中字段列表必须保持一致

子查询:

select * from A where c1 = (select c1 from B);

子查询内部可以使用insert/delete/update/select
标量子查询，列子查询，行子查询，表子查询，分别表示子查询返回的结果数量是一个值，一列，一行，还是一个表
where/from/select之后都可以出现子查询
列子查询中any与some等价，都表示其中任意一个满足即可

例子:

查询员工姓名，年龄，职位，部门信息(隐式内连接):
select e.name, e.age e.job d.name from emp e, dept d where e.dept_id = d.id;
查询年龄小于30的员工的姓名年龄，职位，部门信息(显式内连接):
select e.name, e.age, e.job d.name from emp e join dept d on e.dept_id = d.id where e.age < 30;
查询拥有员工的部门id和部门名称:
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id; (内连接求交集)
查询所有年龄大于40的员工，及其归属的部门名称，如果员工没有分配部门，也需要展示:
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
查询所有员工的工资等级:
select e.*, s.grade from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
查询研发部所有员工的信息以及工资等级:
select e.*, s.grade from emp e, salgrade s, dept d where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发';
查询研发部员工的平均工资:
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发';
查询工资比A高的员工信息:
select * from emp where salary > (select salary from emp where name = 'A');
查询比平均工资高的员工信息:
select * from emp where salary > (select avg(salary) from emp);
查询低于本部门平均工资的员工:
select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = d2.dept_id);
查询所有部门信息，并统计部门员工人数:
select d.id , d.name, (select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;
查询所有学生的选课情况，展示出学生名称，学号，课程名称:
select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;

事务

MySQL事务默认自动提交，执行一条DML语句，会立刻隐式提交事务
select @@autocommit;查看当前事务是否自动提交，如果为1，则表示自动提交
set @@autocommit=0;设置手动提交，需要手动写commit才会提交事务
出错可以使用rollback;回滚事务
开启事务start transaction或者begin，这样不需要修改@@autocommit

四大特性 ACID

原子性: 事务是不可分割的最小单元，要么全部成功，要么全部失败
一致性: 事务完成时，所有数据都需要保持一致状态
隔离性: 数据库提供隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性: 事务一旦提交或者回滚，对数据库中的数据改变就是永久的

并发问题与隔离级别

并发问题

脏读: 一个事务读到另一个事务还没有提交的数据
不可重复读: 一个事务先后读取同一条记录，但是两次读取的数据不同
幻读: 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现数据已经存在

隔离级别

隔离级别	脏读	不可重复读	幻读
Read Uncommitted
Read Committed	解决
Repeatable Read (默认)	解决	解决
Serializable	解决	解决	解决

读未提交的隔离级别下，三种并发问题都有可能出现
可重复读是MySQL的默认配置，Orical是读可提交
串行化，性能最差，隔离级别最高，失去并发能力
select @@transaction_isolation;查看事务的隔离级别
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable} session表示对当前会话窗口有效，global是对所有窗口有效
尽管相比于默认的可重复读，读提交不能解决不可重复读的问题，但是可重复读这一隔离级别在并发时会导致死锁，未来打算放一篇专门的笔记分析。

读未提交RU: 别的事务可以读到自己还没有提交的变更
读已提交RC: 只有自己提交以后，别的事务才能读取到变更
可重复读RR: 一个事务的执行过程中看到的数据，总是和这个事务启动时看到的数据一样。
- 因此RR下，没有提交的数据也是不可见的；并且别人就算已经提交了，我也不会去读
串行化S:对于同一行记录，写加写锁，读加读锁，读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

数据库会创建一个视图，实际返回的结果以视图的逻辑结果为准。
- RR的视图在事务启动时候创建
- RC在SQL语句开始的时候创建
- RU直接返回记录的最新值，没有视图的概念，串行化直接使用加锁避免并行访问
现在两个表，一个存余额，一个存明细。如果需要校对明细是否可以对的上上个月的余额与本月余额的差值，并且希望校对过程中即便数据更新，也不会产生影响，就可以使用RR，这样事务启动时就是静态的，不会被其他事务影响。

每条操作除了会记录redo log，还会记录相反的undo log
在没有比回滚日志更早的读视图时，也就是事务开启时创建的读视图都比回滚日志更晚，那么这个数据就不会再回滚了，就可以删除undo log，由purge线程自动删除
长事务会导致undo log一直存在，不能被删除，也就是在这个事务提交之前，数据库中跟他有关的回滚记录都必须保留，5.5版本之前，回滚日志跟数据字典一起放在ibdata中，所以长事务被提交了，文件也不会变小。并且长事务占用锁资源，导致整个库都拖垮了。

事务启动方式

begin 或者 start transaction;commit 或者 rollback
set autocommit = 0，将线程自动提交关闭，表明使用select就会开启事务，直到主动commit为止
- 为了避免默认的关闭自动提交，所以应该set autocommit = 1，主动显式开启事务(默认就是autocommit=1)
- 也可以使用commit work and chain可以提交本次事务，并开启下一个事务
  查找持续时间超过60s的事务: select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

begin/start transaction不是事务的起点，后面第一个操作InnoDB的表才是事务的起点
start transaction with consistent snapshot这个语句会立马开启事务

如果只有读取的话，在MySQL的RC和RR隔离标准下，不需要添加事务，否则会导致undo log片段过大
业务连接数据库的时候
- 可以全局设置SET GLOBAL MAX_EXECUTION_TIME=1000: 避免事务执行时间过长。
- 对某个session设置SET SESSION MAX_EXECUTION_TIME=1000;
- 对某个语句设置SELECT max_execution_time=1000 SLEEP(10), a.* from test a;
监控information_schema.Innodb_trx，如果出现超过阈值的长事务，就直接kill掉

MySQL中存在两个视图

create view as 调用时执行查询语句生成结果
MVCC一致性视图，consistent read view，用于支持RC和RR, 没有物理结构，定义执行时能看到什么数据

快照读

InnoDB事务都具有唯一，自增的事务ID(transaction ID)，事务开始的时候申请，只读事务的ID很大，而非只读事务的ID从1开始自增
每次更新数据都会生成一个新的数据版本，并且把transaction ID赋值给数据版本事务ID，也就是row trx_id(其实就是指向上一个数据版本的引用)

所以一行记录，可能有多个版本row，都通过row trx_id连接形成版本链
需要旧版本的记录时，就通过最新版本和undo log计算出来

InnoDB每个事务都有一个数组，保存了当事务启动时还没有提交的事务的ID，其中事务最小值为低水位，已经创建过的事务ID的最大值 + 1(这个已经创建过的可能已经不活跃了)为高水位，可见性基于row trx_id对比得到

45_MVCC

低水位之前可见，表明是已经提交的事务，或者是当前事务自己生成的
高水位之后不可见，表明是当前事务启动时，还没启动的事务产生的数据，不可见
如果在中间，判断row trx_id:
- 如果row trx_id在数组中，表明自己事务启动时，这个事务还没提交，不可见
- 如果row trx_id不在数组中，表明自己事务启动时，这个事务已经提交了，可见

更新操作一定是先读后写，并且是当前读，跟隔离级别没有关系；
select如果加锁也是当前读，比如lock in share mode(读锁，S锁，共享锁); for update(X锁，写锁，排他锁)

RC和RR的区别：
- RR下每个事务都会创建一个视图，同一事务其他语句也共用，RC下每个语句都会重新计算生成一个新的view

体系结构

连接层: 最上层，客户端和连接服务，连接处理、授权认证、相关安全方案。服务器安全接入每个客户端并验证它所具有的操作权限:
1
mysql -h <ip> -u <username> -p
- 首先完成TCP三次握手以后，再验证用户名和密码:
  - 有误则会收到Access denied for user，程序结束；
  - 如果正确，则会查询权限表，此后的权限判断都依赖此时查到的权限。
- 因此，即使对权限修改以后，也不会影响到已经存在连接的权限，只有重新建立连接的权限才会被修改。
- 完成连接以后如果没有后续的动作，则连接处于空闲。使用show processlist可以查询到当前状态，Command列为Sleep就表示空闲。如果空闲时长超过了参数wait_timeout(默认8h)，就会自动断开连接
  - 如果连接断开了以后，又发送了请求，则会报错：Lost Connection to MySQL server during query，需要重新连接才能继续执行请求
- 如果客户端有请求并持续使用一个连接,就是长连接；
- 而短连接是执行几次查询就关闭，新建一个请求再执行
- 尽量使用长连接。但是长连接会导致内存占用上涨非常快，因为执行过程中临时使用的内存是管理在连接对象里面的，直到连接断开才会释放内存，因此最后可能导致OOM,即异常重启
- 可以定期断开长连接，比如判断一个占用内存的大查询后就断开连接，之后再重连
- 5.7版本后，可以在每次执行较大查询以后使用mysql_reset_connection重新初始化连接资源，不需要重连和权限验证，但是会恢复到刚刚连接的状态,释放之前占用的内存。
服务层: 完成大多数的核心服务功能，比如SQL接口，完成缓存查询，SQL分析和优化，内置函数执行。所有跨存储引擎的功能也在这层实现，比如过程，函数
- 查询缓存中之前执行过的语句会通过k-v形式存储在内存中，连接完成后直接查询缓存
  - 如果能找到key，则value会直接返回客户端;
  - 如果不在缓存中就会执行到后面的查询数据库阶段，执行完成以后将结果写入缓存中
- 但是大多数情况建议不要查询缓存
  - 因为缓存失效非常频繁，如果更新一张表，那么这个表的所有查询缓存都会被清空，因此缓存命中率会非常的低，除非是存放系统配置的静态表，才会使用查询缓存
- 设置参数query_cache_type为DEMAND, 这样默认就不会使用查询缓存; 如果需要使用缓存，可以使用指定的查询语句显式查询，比如select SQL_CACHE * from table_name进行查询，因为带有SQL_CACHE，所以这个查询会使用缓存
- 8.0版本将查询缓存删除了，不需要考虑这块
- 如果没有查询缓存，就会通过分析器解析语句
  - 分为词法分析，语法分析. 如果语句不对就报错：You have an error in your SQL syntax
- 优化器决定使用哪个索引,以及决定join连接顺序
select * from t1 join t2 using(ID) where t1.c = 10 and t2.d = 20;
t1 t2内连接，using(ID)是简写，表明t1.ID == t2.ID
- 可以t1中取出c=10的记录，然后根据ID关联到t2，再判断t2.d==20是否成立
- 也可以t2中取出d=20的记录，通过ID关联到t1，再判断t1.c==10是否成立
- 结果相同，但是效率不同。
  - 如果连接字段没有索引，就先将t1.c=10和t2.d=20全表扫描，得到的较小的数据放入join_buffer，全表扫描另外一张表，和内存中的行匹配。
- 执行的时候执行器会检查是否有对应的权限，同样查询缓存也会判断是否存在查询权限。
- 执行器通过调用数据库引擎的API实现数据操作
select * from T where ID = 10;
- 如果ID没有索引，那么InnoDB会取第一行判断是否等于10，不等于就跳过，等于就放到结果集中
- 取下一行，重复判断，直到表末，返回结果集。
- 如果ID有索引，就变成了取满足条件的第一行和满足条件的下一行
- 所以有索引，在引擎层就已经将数据过滤了，没有索引的话需要在server层才会进行数据过滤
- 慢查询日志中存在rows_examined字段，表示语句执行过程中扫描了多少行，这个值就是执行器调用引擎获取行数的时候累加的
- rows_examined是获取数据的行数，并不是真正的扫描行数，有可能执行器调用一次，引擎扫描了多行。
引擎层: 负责MySQL的数据存储和提取，服务器通过API和存储引擎进行通信，不同的存储引擎具有不同功能
存储层: 将数据存储在文件系统之上，完成与存储引擎的交互

索引是在存储引擎层实现的
存储引擎基于表的，不是基于库的，所以存储引擎也被称为表类型
创建表的时候指定存储引擎，show engines;显示当前支持的存储引擎
MySQL早期存储引擎为MyISAM，5.5版本后替换为InnoDB

InnoDB

page是磁盘操作的最小单元，大小固定为16K;extent区域大小固定为1M，也就是一个区中包含64个页

MySQL45讲

MySQL45讲中简化了对架构的描述: 大体将引擎架构分为Server层和存储引擎层两块

Server层包含了MySQL所有核心服务和内置函数(日期，时间，加密函数等)，或者跨存储引擎的功能，比如触发器，存储过程和视图

存储引擎层是插件式的，实现了数据的存储和提取，从5.5.5版本后开始默认的是InnoDB
最终，不同的存储引擎共用一个Server层

`MyISAM` VS `InnoDB` VS `Memory`

MyISAM不支持事务，不支持外键，支持表锁而不支持行锁，访问速度快
InnoDB支持事务，支持外键保证完整性和一致性，支持行锁，提高并发性能。
MyISAM涉及三个格式: MYD MYI sdi分别存储数据索引和表结构
InnoDB对应文件是表名.idb，每张表都会存储表结构(早期为frm,8.0版本后改为sdi)、数据和索引。innodb_file_per_table是一个开关，表名是否每张表都对应一个表空间文件idb
Memory 数据只在内存中，只能临时存储，或作为缓存使用，支持hash索引，只有sdi文件，存储表结构

如果数据除了插入和查询以外，还包含很多更新、删除操作，那么选择InnoDB可以保证数据一致性
应用以读和插入操作为主，很少的更新和删除操作，并且给对事物完整性，并发性要求不高，就使用MyISAM
Memory将所有数据保存在内存中，访问速度快，通常用于临时表和缓存。但是对表大小有限制，并且无法保障安全性，(不如用Redis)

特点	InnoDB	MyISAM	Memory
存储限制	64TB	4GB	16MB
事务安全	支持	无	无
锁	行锁	表锁	表锁
B+树索引	支持	支持	支持
Hash索引	无	无	支持
全文索引	支持(5.6)版本后	支持	无
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	无	无

`InnoDB`表结构和数据

8.0版本之前，InnoDB分为表结构定义和数据，表结构定义存储在frm后缀文件中；8.0版本后，可以把表结构定义存储在系统数据表中
innodb_file_per_table = ON表示每个InnoDB表数据存储在ibd后缀文件中
innodb_file_per_table = OFF表示表数据存在系统表空间
5.6版本开始默认为ON
- 建议为ON，这样结构清楚，并且drop table后就会删除文件
- 如果在系统表空间中(OFF)，drop table后空间不会回收

记录如果被删除了，只是标记删除，以后如果插入数据符合对应的位置，就会复用删除的位置
数据页被删除了，任意需要插入新的数据页，都可以进行复用，不需要判断位置
如果两个数据页利用率较低，就会合并，并将另一个数据页标记为可以复用
如果整个表都删除了，会将整个表标记为可复用，但是文件不会变小
增删改都会造成复用空洞，如果随机增加数据，导致页分裂，那原来的页面没有使用的部分就是空洞；修改数据就是先删除，再增加，也会导致空洞
ELECT DATA_LENGTH,DATA_FREE from information_schema.tables where table_schema='test' and table_name='test'; 查看空洞数data_free
如果空洞数较多，可以重建表，alter table A engine=InnoDB
- 5.5版本之前，这个操作不能有更新数据，但是5.6版本开始支持Online的重建表(Online DDL)
  - 建立临时文件，扫描表A主键所有数据页
  - 用数据页中表A的记录生成B+树，存储到临时文件中
  - 生成临时文件中所有对A的操作记录再日志row log中
  - 生成后将row log中的操作应用到临时文件中
  - 临时文件替换表A
- Online DDL会有短暂的MDL写锁，但是再真正拷贝数据之前就已经退化为读锁了，只会阻塞修改表结构的DDL
github有一个开源的缩小表空间的工具，gh-ost
- Online DDL一定是inplace的，原地的，但是inplace不一定是Online的，比如全文索引