《MySQL技术内幕》_笔记

《MySQL技术内幕 》 第5版_（美）迪布瓦_人民邮电出版社
《MySQL技术内幕：InnoDB存储引擎》
要点：sql、mysql指令(help、show/set、explain)、配置文件(连接池、缓存)
了解：索引、MVCC(默认读策略，一致性非锁定读；有锁时读快照)、锁和事务

MySQL技术内幕

sql、mysql数据体系、mysql配置。
mysql函数：https://www.runoob.com/mysql/mysql-functions.html

1，MySQL入门

数据库系统本质上是一种高效的管理大量列表信息的方法。
mysqld，MySQL服务器；mysql，最常见的客户端。
help INFO；查看帮助文档。help like
1.4.4 执行sql语句
\g；sql语句结尾，go，作用和分号;相同。
\G；垂直显示。
\c；清除已输入的sql语句，不执行sql
函数：

• now()；时间
• user()；当前用户
• version()；数据库版本
• database()；选择的数据库

命令：

• source；source path，执行sql文件，路径用/分隔，不能使用引号括起。简写/. path。
• desc、explain、show columns from tbName；查看表结构
• show create table tbName；查看建表语句

算术运算符：+-*/、div整除、%取模
比较运算符：><=、>=、<=、<>、!=
逻辑运算符：and、or、xor、not
其他操作符：like、between-and、in
null运算：is null、is not null。null，不确定值。
1.4.9 时间处理
day()、month()、year()；对日期取日、月、年
dayofyear()、dayofweek()、week()；年天、周天(周日为1)、年周
date()、time()；取日期、时间
now()、sysdate()；当前日期时间
curdate()、curtime()；当前日期、时间
timestampdiff()；计算相差时间
to_days()；日期转为天数，相对公元元年1-1.
date_add()、date_sub()；时间增减
1.4.9.7 模式匹配
like、not like
_匹配单个字符，%匹配任意字符。
1.4.9.8 自定义变量
@varName；自定义变量
set @varName=val；赋值
set varName=val；系统变量赋值
select @varName:=COL from tbName；select赋值，select只能单条记录
with rollup；用于group by子句，按查询语句生成结果集的聚合信息。

2，使用SQL管理数据

show语句、DDL、DML

2.1 服务器模式

sql_mode；服务器模式变量，不同值有不同表现
set sql_mode=val；修改

2.2 MySQL标识符

`反引号；括起保留字。
不允许将多级的完全限定名用一个反引号括起。
一个完全限定名中，要么全部用反引号括起，要么都不括起。
2.3 大小写规则
不同平台不一致；预先做好大小写规划。
2.4 字符集支持
数据库、表均可指定字符集。

2.5 数据库的选择、创建、删除和更改

use；选择
create database；创建
show create database；查看数据库创建语句
drop database；删除
alter database；修改数据库全局属性
schema关键字和database同义。

2.6 表、索引操作

create/drop/alter table；表操作

• create table if not exists A；要求表名存在，表结构相同才忽略。
• create temporary table A；创建临时表，会话结束后自动删除。仅本会话可见。
• create table A like B；创建与B同结构的空表A，索引也会复制
• create table A select；根据查询结果创建A表

2.6.1 存储引擎特性
innoDB，OLTP。事务、行锁。
MyISAM，OLAP。不支持事务。
frm文件存放表定义；表数据存储文件，各引擎不同。innoDB为ibd数据索引，MyISAM为MYD数据、MYI索引
2.6.4 索引操作
create/drop index；索引操作
alter table A add index iName (cols)；增加索引，create index会被映射为altertable。
索引类型：unique、fulltext、spatial、普通。
alter table A add primary key (cols)；增加主键，索引名为primary

2.7 获取数据库元数据

show语句、information_schema表。
2.7.1 show语句获取元数据
help show；查看所有show语句
show databases/tables；展示数据库、表
show create database/table；展示数据库、表的创建语句
show tables from dbName；展示指定数据库的表
show columns/index from tbName；展示指定表的列、索引
desc/explain tbName；展示指定表的列
show table status [from dbName]；展示表描述信息
show variables；展示变量
like、where子句筛选。
show tables like “lkk”；展示指定表，可判定表是否存在
2.7.2 information_schema获取元数据
INFORMATION_SCHEMA 表：https://blog.csdn.net/wanbin6470398/article/details/81780803
information_schema数据库，DBMS元数据，以SQL标准为基础构建，标准化访问，可移植性好。
schemas、tables、views、partitions、columns；数据库、表、视图、分区、列
routines/parameters、triggers、events；存储过程/存储过程参数、触发器、事件、参数
files；表数据存储文件信息
table_constraints、referential_constraints、key_column_usage；约束
statistics；与表索引特性有关信息
character_sets、collations、collation_character_set_applicablity；字符集相关
engines、plugins；存储引擎、服务器插件
user_privileges、schema_privileges、table_privileges、column_privileges；权限。来自mysql数据库user、db、tables_priv、column_priv表
global_variables、session_variables、global_status、session_status；全局和会话的变量、状态。默认禁用，需设置show_compatibility_56=on。
processlist；服务器内的执行线程的相关信息
存储引擎可能会增加特性表。

2.8 连接

基础为笛卡尔连接，通过条件筛选。
内连接

• [inner] join；等值连接=、不等连接(><!=等)。不去重
• natural join；自然连接，去重。比全部相同列，要求参与比较的属性列必须是同名、同属性。

外连接

• left/right/full join；左、右、全连接。mysql不支持带条件full join

交叉连接

• cross join；笛卡尔积

2.9 子查询

标量子查询；使用关系比较运算符。
in/not in子查询；in判定，同=any，<>any
all、any/some子查询；与关系比较运算符联用，全部满足、部分满足。any、some同义。
多列判定时，使用行构造器：where (name,age)=(select name,age from lkk)
exists/not exists；是否存在子查询。返回boolean。
select t1.name,t1.age from t1 where exists (select * from t2 where t2.name=t1.name)

2.10 UNION

union；去重
union all；不去重
需排序union结果集时，小括号括起select语句。

2.11 多表删除和更新

连接后进行多表删除
delete t1 from t1 join t1 on t1.id=t2.id
delete t1,t2 from t1 join t2 on t1.id=t2.id where EXPR
update后跟多个table，构建相关性。
update t1,t2 set t1.name="" where t1.id=t2.id
update t1,t2 set t1.name=t2.name where t1.id=t2.id

2.12 事务处理

事务，指一组sql语句，他们是一个执行单位。
格式：

start transaction;
SQL;
commit/rollback;

autocommit；自动提交。set autocommit=0；禁用自动提交。
事务隔离级别：read uncommitted、read committed、repeatable read(默认)、serializable

2.13 外键和引用完整性

设置外键时，指定delete、update行为。

FOREIGN KEY [fkName] (COLS) 
	REFERENCES tb_name (COLS)
	ON delete ACTION
	ON update ACTION

on delete/update

• 默认拒绝删除
• no action；延迟检查，innodb不支持
• set null；设为null
• set default；设为默认值
• cascade；级联删除

3，数据类型

3.1 数据值类别

数值、字符串值、时态值、空间值、null值。
3.1.1 数值
整数、小数、位域值(二进制，b’1001’，0b1001)。
整数精确运算，小数近似运算。
位域值转整数，b’1001+0，cast(b’1001’ as unsigned)
3.1.2 字符串值
mysql字符串，尽量使用单引号括起；双引号为标识符。
\斜杠转义特殊字符。
字符串值分两类：二进制串、非二进制串。
字符串排序规则collation，_ci不区分大小写，_cs区分大小写，_bin按位排序
charset()；查看字符集
collation()；查看排序规则
3.1.3 时态值(日期/时间)
时态值包括日期值、时间值。
date_format()；格式化时间
str_to_date()；字符串转为时间
3.1.5 布尔值
布尔常量：true、false；不区分大小写。
表达式中，0为false，非0非null为true。
3.1.6 null值
null值为不确定值，null、\N(区分大小写)
isnull()；判定是否为null
is null、is not null；过滤null

3.2 MySQL数据类型

3.2.1 数据类型概述
常用数据类型：

• 数值；int、bigint、decimal、double、float、bit(位域)
• 字符串值；char、varchar、text非二进制串、binary、varbinary、blob二进制对象
• 时态值；date、time、datetime、timestamp、year
  default子句，设置默认值。

3.4 处理序列

auto_increment；自增
last_insert_id()；返回最近生成的自增id

4，视图和存储程序

4.1 使用视图

视图是一个虚表，它是在表或其他视图视图的基础上，使用select语句定义的。
查询视图等效于查询定义它的那条语句。
create view vName as select；定义视图
drop view vName；删除视图
可更新视图，直接映射到一个表，且是对表中各列的简单引用。视图中的行可对应到表中的一行。

4.2 使用存储程序

存储程序：存储函数、存储过程、触发器、事件。
begin-end；包裹复合sql语句。delimiter，修改分隔符。
create function fName()；创建函数，返回单一值
create procedure pName()；创建存储过程，不返回，或返回多个值。使用call调用存储过程。

5，查询优化

5.1 使用索引

索引加快查询速度，降低了增删改速度。
create index；创建索引

5.2 查询优化程序

explain；查看执行计划
不要转换、计算索引列，而是对其运算条件进行计算。

• select * from t1 where year(t1.time)<2000；此时不使用索引
• select * from t1 where t1.time<str_to_date(‘2000-1-1’,’%Y-%m-%d’)；使用索引

5.6 调度、锁和并发

MySQL调度策略：

• 写的优先级比读高
• 表的写入操作一次只能进行一个，多个写操作，按序处理。事务中，通过IX实现；innodb非同行写入，可并行。
• 可同时处理多个读操作。

10，mysql管理简介

mysql服务器；mysqld。
mysql客户端及工具；mysql、mysqladmin、mysqldump
服务器语言；sql。
MySQL数据目录。数据库和状态文件存储在数据目录中。

12.3 使用系统变量和状态变量、自定义变量

系统变量
全局变量global；会话变量session、local(两者同义)。
查看

• show [global | session] variables like/where；
• select @@global/session.vName；
• select @@vName；不使用限定，先查session，后global，无时报错。

设置

• mysqld --vName=val；启动时设置
• vName=val；配置文件中设置
• set global/session vName=val；
• set @@global/session.vName=val；
• set @@vName/vName=val；修改会话变量

状态变量
全局状态global，会话状态session。
查看

• show [global、session] status like/where；
• information_schema.global_status、session_status表中select。若报错，修改@@global.show_compatibility_56。

自定义变量
@varName；自定义变量
声明、设置

• set @varName=val；赋值
• select @varName:=COL from tbName；select赋值，select只能单条记录

查看

• select @varName；

12.6 全球化问题

时区
global.system_time_zone；系统时区，不可修改
global.time_zone、session.time_zone；全局时区、会话时区。默认为系统时区，按需修改。
字符集
mysql --default_character_set；设置默认客户端字符集
character-set-server；配置文件设置服务器字符集、

13 安全性与访问控制

13.2 管理MySQL用户账户

create/drop/rename user；创建、删除、重命名。user格式：‘username’@‘hostname’

• create user ltl identified by ‘lkk’；创建并设置密码
• drop user ltl;
• rename user ltl to lkk;
• alter user ‘ltl’@’%’ identified with mysql_native_password by ‘lkk’；修改密码

grant；授权

• grant all on . to ‘lkk’@’%’；授予所有库所有表全部权限

show grants；展示权限
revoke；移除权限

14，数据库维护、备份和复制

14.2 服务器运行时 维护数据库

check/repair table；检查表、修复表。
lock/flush table，unlock tables；锁定、刷新、解锁。必须在一个会话中进行锁定刷新解锁操作，会话退出时自动解锁表。
lock table lkk read/write；读锁定、写锁定。
set global read_only=on；设置全局只读

14.4 数据库备份

mysqldump；sql语句备份。source命令恢复。
mysqldump 登录信息 dbName > fName；将sql备份
二进制备份，直接备份data目录下内容。

14.8 设置复制服务器

https://www.cnblogs.com/eleven24/p/7350000.html

个人

语言：sql、mysql指令(show/set、用户权限)
结构：schema、table、view、partition、column、index、constraint
数据类型：数值、字符串值、时态值、空间值、null值(null、\N)
并发处理：事务、锁、MVCC多版本并发控制
存储程序：routine(存储过程、存储函数)、trigger、event。
变量：系统变量、状态变量、自定义变量
组件：mysqld、mysql、mysqldump
配置：my.cnf；https://www.cnblogs.com/panwenbin-logs/p/8360703.html

SQL

structure query language，分6类；https://baike.baidu.com/item/%E7%BB%93%E6%9E%84%E5%8C%96%E6%9F%A5%E8%AF%A2%E8%AF%AD%E8%A8%80/10450182?fromtitle=sql&fromid=86007&fr=aladdin
DQL；select
DML；insert、delete、update
DDL；create、drop、alter、rename
DCL；grant、revoke。数据控制，权限控制。
TCL；begin transaction、savepoint、commit、rollback。事务控制。
CCL；指针控制语言。

mysql指令

help；帮助
show；展示信息
use；选择数据库
describe/desc、explain；获取表结构、查询执行计划
begin-end块；包裹sql语句。
set；设置变量值
lock、flush、unlock；锁定、刷入、解锁

MySQL技术内幕：InnoDB存储引擎

MySQL技术内幕：InnoDB存储引擎

1，存储引擎

数据库，物理操作系统文件或其他形式文件类型的集合。文件集合。
数据库实例，由数据库后台进程/线程和共享内存区组成。应用程序，操作数据库文件。
存储引擎，也称表处理器，规定数据库对表的操作规则；存储、索引、并发、一致性。

1.2 mysql体系结构

连接池组件
管理服务、工具组件
sql相关组件；包括：SQL接口组件、查询分析组件、优化器组件、缓存(Cache)组件
插件式存储引擎。表处理器，存储引擎基于表。数据存储、索引、并发(锁、MVCC)、一致性
物理文件。数据目录中。
内存
hash索引(mysql按查询自建的索引)
change buffer(增删改缓存)
log buffer
硬盘
表(表空间、段、区、页、Row；行溢出数据)
索引(主键索引、聚簇索引)
日志(redo log, undo log)

2 InnoDB存储引擎

支持ACID事务，行锁设计，支持MVCC，支持外键。
Oracle是多进程架构，window下除外。
InnoDB多线程架构，默认7个后台线程，4 IO，1 master，1 lock监控，1错误监控。

4 表

InnoDB表主键，显式定义，自动选择(非空唯一索引、自动生成6字节指针)。
innodb存储引擎表，是索引组织表；表中数据按主键顺序存放。
表空间，存放所有数据的空间。逻辑上分：段、区、页。区64页，页16KB。
索引最终定位到页，存储引擎读取页，查找数据。
innodb中，B+树叶节点为数据段，非叶节点为索引段。

5，索引

索引，目录，快速定位数据记录。
InnoDB支持B+索引、hash索引(按使用情况自动生成)
二分查找。

5.3 二叉树

二叉树；所有节点最多有2个子节点。
二叉查找树；左子树小于根节点，右子树大于根节点。顺序化。
平衡树；任何节点的左右子树最大高度差为1。
平衡二叉树；平衡的二叉查找树。通过左旋、右旋维持平衡。
找到高度差为2的子树，反向旋转；若还不平衡，向上一级，再次反向旋转。

5.4 B+树

B+树，平衡查找树；
增加记录时，通过拆分页、旋转维持平衡。
删除记录时，按填充因子(filter factor)控制合并页，填充因子最小50%。
B+树索引，B+树在数据库中的实现，高扇出。分为：聚集索引、辅助聚集索引。
聚集索引，主键B+树索引；叶节点存放整张表的主键数据，故让其成为数据页。存储在逻辑上连续。
辅助聚集索引，指向聚集索引。
索引页存放偏移量，数据页存放行记录。
B、B+
https://blog.csdn.net/mine_song/article/details/63251546
B树；平衡多路搜索树。关键字分布在整棵树，性能等价于二分查找。
B+树；平衡多路搜索树。叶子节点为全部关键字，叶子节点间为有序链表。
B+树非叶子节点为稀疏索引，叶子节点(有序链表)为稠密索引。
B树只适合随机检索，B+树适合随机检索、顺序/范围检索。

5.7 hash算法

hash计算；碰撞时使用链表结构。

6，锁

并发控制：锁、MVCC(可能丢更新)。
锁机制，管理对共享资源的访问。
执行加锁语句时加锁、事务结束时释放锁；

6.2 InnoDB锁

InnoDB锁类型：

• 共享锁S、排他锁X。行锁。
• 意向锁(IS、IX)。表锁。只阻塞全表扫描的请求。

一致性的非锁定读操作；通过读取快照数据(undo段，行历史数据版本)，避开等待排他锁的释放。InnoDB默认使用非锁定读。

• read committed；读取最新的行数据版本
• repeatable read；读取事务开始时的行数据版本

select…for update/lock in share mode；事务中使用，加X/S锁。获取最新版本数据，若有x锁，则等待。DML默认加for update。
外键，用于引用完整性的约束检查。InnoDB中，一个外键列，若未显示添加索引，会自动创建，避免表锁。
对外键值的插入、更新；会先检查父表，使用lock in share mode方式加锁，防止数据不一致。
查看锁
show engine innodb status；查看innodb引擎锁情况
information_schema.innodb_locks；查看锁信息
行锁通过索引实现，若无法确定行锁，使用表锁。

6.3 锁的算法

InnoDB有3中锁算法，默认为Next-Key Lock。

• Record Lock；单个行记录上的锁，不允许删改。锁住索引记录，若无索引则锁主键。
• Gap Lock；间隙锁，不允许插入，锁定一个范围，不包括记录本身。age<20
• Next-Key Lock；Gap+Record，锁定一个范围，包括记录本身。salary>=10000

record lock；写锁。
gap lock，防幻读；next-key lock，一致性读。

7，事务

acid特性；通过redo、undo实现，先写日志，再操作。

7.3 事务控制语句

begin；start transaction；set autocommit=0；开始事务
commit；rollback；set autocommit=1；提交，回滚，结束事务。
savepoint；release savepoint；标记点，释放标记点
set transaction；设置事务隔离级别。
DDL语句会隐式提交。

7.6 事务隔离级别

通过锁，保证一致性。
read uncommitted
read committed；仅唯一性约束、外键检查使用gap lock
repeatable read；Next_Key Lock算法。
serializable；select语句加lock in share mode。主要用于XA事务。
set [global | session] transaction isolation level；设置事务隔离级别