MySQL归纳汇总

MySQL安装

# 安装过程中需要输入root密码
sudo apt install mysql-server
sudo apt install mysql-client
sudo apt install libmysqlclient-dev
# 验证是否安装成功
sudo netstat -tap | grep mysql
# tcp 0 0 localhost:mysql *:* LISTEN 1328/mysqld
# 登录
mysql -uroot -pxxxxxx
# 非本地连接
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
注释掉bind-address = 127.0.0.1
# 赋权操作
grant all on *.* to root@'%' identified by 'xxxxxx' with grant option;
flush privileges;
service mysql restart

参考：在Ubuntu16.04下安装mysql

MySQL是怎样运行的：从根儿上理解MySQL

重新认识MySQL

MySQL的客户端/服务器架构

MySQL服务器进程也被称为MySQL数据库实例

MySQL服务器进程的默认名称为mysqld，MySQL客户端进程的默认名称为mysql

MySQL的安装

启动服务器程序

• mysqld
• mysqld_safe

启动客户端程序

mysql -h主机名 -u用户名 -p密码

连接注意事项

不要在一行命令中输入密码

一行命令输入密码时，-p和密码之间不能有空白字符

客户端与服务器的连接过程

MySQL服务器会默认监听3306端口

在启动服务器程序的命令行添加-P参数指定端口号

如果服务器进程和客户端进程都运行在同一台操作系统为类Unix的机器上的话，可以使用Unix域套接字文件来进行进程间通信。

服务器处理客户端请求

1. 连接管理
   客户端进程可以采用TCP/IP、命名管道或共享内存、Unix域套接字这几种方式之一来与服务器进程建立连接，每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个客户端的交互

2. 解析与优化

   • 查询缓存
     从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

   • 语法解析

   • 查询优化
     MySQL的优化程序会对语句做一些优化，优化的结果是生成一个执行计划，可以使用EXPLAIN语句来查看某个语句的执行计划

3. 存储引擎
   常用的存储引擎是InnoDB和MyISAM，以及Memory。其中InnoDB是MySQL默认的存储引擎

关于存储引擎的操作

# 查看当前服务器程序支持的存储引擎
SHOW ENGINES;
# 创建表时指定存储引擎
CREATE TABLE 表名(
    建表语句;
) ENGINE = 存储引擎名称;
# 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;

启动选项和系统变量

字符集进和比较规则

InnoDB记录存储结构

页是MySQL中磁盘和内存交互的基本单位，也是MySQL管理存储空间的基本单位。页大小一般为16KB

InnoDB行格式

记录在磁盘上的存放方式被称为行格式（记录格式），InnoDB定义了四种行格式

• Compact行格式
  
  把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
  变长字段长度列表中只存储值为 非NULL的列内容占用的长度，值为 NULL 的列的长度是不储存的
  如果表中没有允许存储NULL的列，则NULL值列表也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列
  记录头信息由固定的5个字节组成，不同的位代表不同的意思
  记录的真实数据除了自定义的列的数据以外，MySQL会为每个记录默认添加一些列（也称为隐藏列）
  InnoDB对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有自定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
  InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列。但是 row_id 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）

• Redundant行格式
  Redundant行格式是MySQL5.0之前用的一种行格式

• Dynamic行格式
  当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出
  处理行溢出数据和Compact行格式不同

• Compressed行格式
  Compressed行格式会采用压缩算法对页面进行压缩，以节省空间

指定行格式的操作

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

InnoDB数据页结构

InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做数据页

数据页结构

记录在页中的存储

每当插入一条记录时，都会从Free Space部分申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间用完后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了

• delete_mask：这个属性标记着当前记录是否被删除，值为0代表记录并没有被删除，为1代表记录被删除掉了

• min_rec_mask： B+树的每层非叶子节点中的最小记录都会添加该标记

• n_owned：表示该组有多少个记录

• heap_no： 当前记录在本页中的位置
  InnoDB自动给每个页加了两个记录，称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录
  对于一条完整的记录来说，比较记录的大小就是比较主键的大小

• record_type： 这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录

• next_record： 表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量
  下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录
  
  我们的记录按照主键从小到大的顺序形成了一个单链表
  当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间

Page Directory（页目录）

InnoDB会把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：

• 通过二分法确定该记录所在的槽
• 通过记录的next_record属性遍历该槽所在的组中的各个记录

Page Header（页面头部）

数据页中存储的记录的状态信息

File Header（文件头部）

描述了一些针对各种页都通用的一些信息

每个数据页的File Header部分都有上一个和下一个页的编号，所有的数据页会组成一个双链表

File Trailer（文件尾部）

前4个字节代表页的校验和

后4个字节代表页面被最后修改时对应的日志序列位置（LSN）

保证从内存中同步到磁盘的页的完整性

B+树索引

各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

无索引的查找

在单个页中查找某条记录分为两种情况：

• 以主键为搜索条件，可以使用Page Directory通过二分法快速定位相应的用户记录
• 以其他列为搜索条件，需要按照记录组成的单链表依次遍历各条记录

在多页中中查找某条记录，在没有索引的情况下，不论是以主键还是其他列作为搜索条件，只能沿着页的双链表从左到右依次遍历各个页

索引

保证下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值

InnoDB的索引方案

复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录， 记录头信息里的record_type属性用来区分不同的记录

目录项记录和普通的用户记录的不同点：

• 目录项记录的record_type值是1，而普通用户记录的record_type值是0
• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自定义的，可能包含很多列，另外还有InnoDB添加的隐藏列
• 只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0

InnoDB存储引擎的索引是一棵B+树，完整的用户记录都存储在B+树第0层的叶子节点，其他层次的节点都属于内节点，内节点里存储的是目录项记录。InnoDB的索引分为两大种：

• 聚簇索引
  以主键值的大小为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列

• 二级索引
  以自定义的列的大小为页和记录的排序规则，在叶子节点处存储的记录内容是列 + 主键
  回表操作

联合索引：同时以多个列的大小作为排序规则，也就是同时为多个列建立索引

联合索引与通过不同的列分别建立索引的表述是不同的：

• 建立联合索引只会建立如上图一样的1棵B+树
• 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树

MyISAM存储引擎的数据和索引分开存储，这种存储引擎的索引全部都是二级索引，在叶子节点处存储的是列 + 页号

B+树索引的使用

• 每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点
• InnoDB存储引擎会自动为主键（如果没有会主动添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录
• 我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录
• B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序
• 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快

索引的代价

• 空间上的代价
  每建立一个索引都为建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间

• 时间上的代价
  存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果建了许多索引，每个索引对应的B+树都要进行相关的维护操作

B+树索引适用的条件

• 全值匹配
  搜索条件中的列和索引列一致的话，称为全值匹配
  WHERE子句中的几个搜索条件的顺序对查询结果没有影响， MySQL的查询优化器会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件

• 匹配左边的列
  搜索条件只包含索引列左边部分
  如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列

• 匹配列前缀
  对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的， 但如果只给出后缀或者中间的某个字符串，就无法快速定位记录位置

• 匹配范围值
  在使用联合索引进行范围查找的时候，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引，通过name进行范围查找的记录中可能并不是按照birthday列进行排序的

• 精确匹配某一列并范围匹配另外一列
  虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找

• 用于排序

• 用于分组

回表的代价

• 会使用到两个B+树索引，一个二级索引，一个聚簇索引
• 访问二级索引使用顺序IO，访问聚簇索引使用随机IO

覆盖索引

为了省去回表操作带来的性能损耗，把只需要用到索引的查询方式称为覆盖索引

在查询列表里只包含索引列

如何挑选索引

• 只为用于搜索、排序或分组的列创建索引
• 为列的基数大的列创建索引
• 索引列的类型尽量小
• 可以只对字符串值的前缀建立索引
• 只有索引列在比较表达式中单独出现才可以适用索引
• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性
• 定位并删除表中的重复和冗余索引
• 尽量适用覆盖索引进行查询，避免回表带来的性能损耗

MYSQL的数据目录

对于InnoDB、MyISAM这样的存储引擎会把数据存储到文件系统上

# 确定MySQL的数据目录
SHOW VARIABLES LIKE 'datadir';

数据库在文件系统中的表示

每个数据库都对应数据目录下的一个子目录

表在文件系统中的表示

表在文件系统上表示分两部分

• 表结构的定义
  不论是InnoDB还是MyISAM，都会在数据库子目录下创建一个和表名同名的.frm文件

• 表中的数据
  针对InnoDB和MyISAM对于表数据有不同的存储方式

对于InnoDB存储引擎来说，使用表空间来存储表中的数据，表空间分两种类型：

• 系统表空间
  默认情况下，InnoDB将所有的表数据都存储到这个系统表空间内，它是一个抽象的概念，实际可以对应着文件系统中若干个真实文件

• 独立表空间
  如果有需要的话，可以为每个表分配独立的表空间，只需要在启动服务器的时候将innodb_file_per_table参数设置为1即可。每个表的独立表空间对应的文件系统中的文件是在数据库子目录下的与表名同名的.ibd文件

文件系统对数据库的影响

• 数据库名称和表名称不得超过文件系统所允许的最大长度
• 会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成@+编码值的形式作为文件名
• 文件长度受文件系统最大长度限制
• 如果同时访问的表的数量非常多，可能会受到文件系统的文件描述符有限的影响

InnoDB的表空间

可以把表空间想象成被切分为许多个页的池子，当为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去

页面类型

• 表空间中的每一个页都对应着一个页号，也就是FIL_PAGE_OFFSET，这个页号由4个字节组成，也就是32个比特位，所以一个表空间最多可以拥有2³²个页，如果按照页的默认大小16KB来算，一个表空间最多支持64TB的数据。表空间的第一个页的页号为0，之后的页号分别是1，2，3…依此类推
• 某些类型的页可以组成链表，链表中的页可以不按照物理顺序存储，而是根据FIL_PAGE_PREV和FIL_PAGE_NEXT来存储上一个页和下一个页的页号。需要注意的是，这两个字段主要是为了INDEX类型的页，也就是我们之前一直说的数据页建立B+树后，为每层节点建立双向链表用的，一般类型的页是不使用这两个字段的。
• 每个页的类型由FIL_PAGE_TYPE表示，比如像数据页的该字段的值就是0x45BF，我们后边会介绍各种不同类型的页，不同类型的页在该字段上的值是不同的

区（extent）的概念

表空间被划分为许多连续的区，每个区默认由64个页组成，每256个区划分为一组，每个组的最开始的几个页面类型是固定的

一个区就是在物理位置上连续的64个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足填充满整个区），但是从性能角度看，可以消除很多的随机I/O

存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段

碎片（fragment）区：在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的
• 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间

独立表空间结构

系统表空间结构

单表访问方法

访问方法的概念

• 使用全表扫描进行查询
• 使用索引进行查询

const

通过主键或者唯一二级索引列来定位一条记录的访问方法定义为const

ref

二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref

ref_or_null

range

index

all

连接的原理

MYSQL基于成本的优化

InnoDB统计数据是如何收集的

MYSQL基于规则的优化

Explain详解

optimizer trace的神奇功效

InnoDB的Buffer Pool

Buffer Pool

在启动服务器的时候配置innodb_buffer_pool_size参数的值，它表示Buffer Pool的大小

每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息等

控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边

free链表的管理

缓存页的哈希处理

用表空间号 + 页号作为key，缓存页作为value创建一个哈希表，在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从free链表中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置

flush链表的管理

修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（英文名：dirty page）

LRU链表的管理

创建一个链表，由于这个链表是为了按照最近最少使用的原则去淘汰缓存页的，所以这个链表可以被称为LRU链表（LRU的英文全称：Least Recently Used）。当我们需要访问某个页时，可以这样处理LRU链表：

• 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页对应的控制块作为节点塞到链表的头部。
• 如果该页已经缓存在Buffer Pool中，则直接把该页对应的控制块移动到LRU链表的头部

划分区域的LRU链表

预读，就是InnoDB认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到Buffer Pool中。根据触发方式的不同，预读又可以细分为下边两种：

• 线性预读
  如果顺序访问了某个区（extent）的页面超过这个系统变量的值，就会触发一次异步读取下一个区中全部的页面到Buffer Pool的请求

• 随机预读

事务简介

ACID

• 原子性
• 一致性
• 隔离性
• 持久性

把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务

事务的状态

• 活动的
• 部分提交的
• 失败的
• 中止的
• 提交的

事务的语法

• 开启事务
• 提交事务

支持事务的存储引擎：InnoDB和NDB

自动提交

隐式提交

保存点

redo日志

在系统奔溃重启时需要按照上述内容所记录的步骤重新更新数据页，所以上述内容也被称之为redo日志

undo日志

设计数据库的大叔把这些为了回滚而记录的这些东东称之为undo日志

回滚和undo日志有关，持久性通过redo日志保证

事务的隔离级别和MVCC

事务并发执行遇到的问题

• 脏写
• 脏读
• 不可重复读
• 幻读

脏写 > 脏读 > 不可重复读 > 幻读

SQL标准的四种隔离级别

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

MVCC（多版本并发控制）

锁

MySQL归纳汇总

索引相关

什么是索引

• 索引是一种数据结构，用来快速地进行数据查找

索引是个什么样的数据结构

• 跟具体的存储引擎实现相关，使用较多的有Hash索引，B+树索引等
• InnoDB存储引擎的默认索引实现是B+树索引

Hash索引和B+树索引有什么区别

• Hash索引底层就是Hash表，进行查找时，调用一次Hash函数就可以获取到相应的键值，然后进行回表查询获得实际数据

• B+树索引底层实现是多路平衡查找树，对于每一次查询都是从根节点出发，查找到叶子节点后获得所查键值，然后根据查询判断是否需要回表查询获得实际数据

• Hash索引进行等值查询时更快，但是无法进行范围查询
  经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询

• B+树的的所有节点皆遵循左节点小于父节点，右节点大于父节点，天然支持范围

• Hash索引不支持使用索引进行排序

• Hash索引不支持模糊查询以及多列索引的最左前缀匹配

• Hash索引任何时候都避免不了回表查询数据（哈希的值是指针），而B+树在符合某些条件（聚簇索引，覆盖索引等）的时候可以只通过索引完成查询

• Hash索引查询不稳定，性能不可预测，当某个键存在大量重复的时候发生hash碰撞，此时效率可能很差，而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低

• 在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度

什么是聚簇索引

• 在B+树索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引
• 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键，则隐式的生成一个键来建立聚簇索引
• 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询

非聚簇索引一定会回表查询吗

• 不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询
• 刚好查找的是主键

在建立索引的时候，都有哪些需要考虑的因素呢

• 建立索引的时候一般要考虑到字段的使用频率，经常作为条件进行查询的字段比较适合
• 如果需要建立联合索引的话，还需要考虑联合索引中的顺序
• 此外也要考虑其他方面，比如防止过多的索引对表造成太大的压力，这些都和实际的表结构以及查询方式有关

联合索引是什么？为什么需要注意联合索引中的顺序

• MySQL可以使用多个字段同时建立一个索引，叫做联合索引

• 在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引

• MySQL使用索引时需要索引有序
  假设现在建立了”name、age、school”的联合索引，那么索引的排序为：先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序
  当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推，因此在建立联合索引的时候应该注意索引列的顺序
  一般情况下，将查询需求频繁或者字段选择性高的列放在前面，此外可以根据特例的查询或者表结构进行单独的调整

创建的索引有没有被使用到？或者说怎样才能知道某条语句运行很慢的原因

• MySQL提供了explain命令来查看语句的执行计划，MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息
• 可以通过其中和索引有关的信息来分析是否命中了索引，例如possilbe_key、key、key_len等字段，分别说明了此语句可能会使用的索引，实际使用的索引以及使用的索引长度

在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用

• 使用不等于查询
• 列参与了数学运算或者函数
• 在字符串like时左边是通配符，类似于’%aaa’
• 当MySQL分析全表扫描比使用索引快的时候不使用索引
• 当使用联合索引，前面一个条件为范围查询，后面的即使符合最左前缀原则，也无法使用索引

一条SQL语句执行很慢的原因

1. 多数情况下正常，偶尔很慢
   数据库在刷新脏页，例如redo log写满了需要同步到磁盘
   执行的时候遇到锁，如表锁和行锁

2. SQL语句一直执行很慢
   没有用上索引：该字段没有索引；对该字段进行运算、函数操作等导致无法使用索引
   数据库选错了索引

事务相关

什么是事务

事务是一系列的操作，他们要符合ACID特性，最常见的理解就是：事务中的操作要么全部成功，要么全部失败，但是只是这样还不够的

ACID是什么

• Atomicity（原子性）：要么全部成功，要么全部失败，不可能只执行一部分操作
• Consistency（一致性）：数据库总是从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态
• Isolation（隔离性）：一个事务在完全提交之前，对其他事务是不可见的
• Durability（持久性）：一旦事务提交，那么就永远是这样子了，哪怕系统崩溃也不会影响到这个事务的结果

InnoDB如何实现ACID

数据库通过原子性，隔离性，持久性来保证一致性

保证原子性：利用InnoDB的回滚日志（undo log）

undo log记录了回滚需要的信息，当事务执行失败后就可以利用undo log的信息把数据回滚到修改之前的样子

保证持久性：利用InnoDB的redo log

当对数据进行修改时，不仅在内存中操作，还会在redo log中记录，当数据库宕机重启后，会将redo log的内容恢复到数据库中

同时有多个事务在进行会怎么样

• 脏读：A事务读取到了B事务未提交的内容，而B事务后面进行了回滚
• 不可重复读：当设置A事务只能读取B事务已经提交的部分，会造成在A事务内的两次查询结果竟然不一样，因为在此期间B事务进行了提交操作
• 幻读：A事务读取了一个范围的内容，而同时B事务在此期间插入了一条数据，造成幻觉

MySQL的事务隔离级别了解吗

MySQL有四种隔离级别

• 未提交读（READ UNCOMMITTED）
  其他事务可以看到本事务没有提交的部分修改，因此会造成脏读的问题
  这个级别的性能没有足够大的优势，但是又有很多的问题，因此很少使用

• 已提交读（READ COMMITTED）
  其他事务只能读取到本事务已经提交的部分，这个隔离级别有不可重复读的问题

• 可重复读（REPEATABLE READ）
  可重复读隔离级别解决了上面不可重复读的问题，但是仍然有一个新问题——幻读
  当你读取id > 10的数据行时，对涉及到的所有行加上了读锁，此时另外一个事务新插入了一条id = 11的数据，因为是新插入的，所以不会触发上面的锁的排斥，那么本事务进行下一次的查询时会发现有一条id = 11的数据，而上次的查询操作并没有获取到，再进行插入就会有主键冲突的问题

• 串行化（SERIALIZABLE）
  这是最高的隔离级别，可以解决上面提到的所有问题，因为强制将所以的操作串行执行，这会导致并发性能极速下降，因此也不是很常用

InnoDB使用的是哪种隔离级别

InnoDB默认使用的是可重复读隔离级别

对MySQL的锁了解吗

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制

MySQL都有哪些锁？像上面那样子进行锁定岂不是有点阻碍并发效率了

从锁的类别上来讲，有共享锁和排他锁

• 共享锁：又叫做读锁，当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个
• 排他锁：又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁，排他锁只可以加一个，他和其他的排他锁，共享锁都相斥

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁，加锁开销从大到小，并发能力也是从大到小

表结构设计

为什么要尽量设定一个主键

• 主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键
• 设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全

主键使用自增ID还是UUID

• 推荐使用自增ID，不要使用UUID
• 在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小关系不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降
• 总之，在数据量大一些的情况下，用自增主键性能会好一些

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.
If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

字段为什么要求定义为not null

NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

如果要存储用户的密码散列，应该使用什么字段进行存储

密码散列，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率

存储引擎相关

MySQL支持哪些存储引擎

MySQL支持多种存储引擎，比如InnoDB、MyISAM、Memory等，在大多数的情况下，直接选择使用InnoDB引擎都是最合适的，InnoDB也是MySQL的默认存储引擎

InnoDB和MyISAM有什么区别

• InnoDB支持事务，MyISAM不支持事务
• InnoDB支持行级锁，MyISAM支持表级锁
• InnoDB支持MVCC, MyISAM不支持
• InnoDB支持外键，MyISAM不支持
• InnoDB不支持全文索引，MyISAM支持

零散问题

MySQL中的varchar和char有什么区别

• char是一个定长字段，假如申请了char(10)的空间，那么无论实际存储多少内容，该字段都占用10个字符
• varchar是变长的，也就是说申请的只是最大长度，占用的空间为实际字符长度+1，最后一个字符存储使用了多长的空间
• 从检索效率上来讲，char > varchar，因此在使用中，如果确定某个字段的值的长度，应该使用char，否则应该使用varchar，例如存储用户MD5加密后的密码，应该使用char

varchar(10)和int(10)分别代表什么含义

• varchar的10代表了申请的空间长度，也是可以存储的数据的最大长度
• int的10只是代表了展示的长度，不足10位以0填充，也就是说，int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示的长度不同

MySQL的binlog有有几种录入格式？分别有什么区别

有三种格式：statement、row和mixed

• statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.
• row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.
• mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.

此外，新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录

主从复制

建立一个和主数据库完全一样的数据库环境，称为从数据库

作用

• 数据备份

• 架构扩展

• 读写分离

原理

1.数据库有个bin-log二进制文件，记录了所有sql语句

2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来

3.让其在从数据的relay-log重做日志文件中再执行一次

三个线程

• binlog输出线程

• 从库IO线程

• 从库SQL线程

ACID关系

只有满足一致性，事务的执行结果才是正确的

在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性

在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性

事务满足持久化是为了能应对数据库崩溃的情况

MySQL默认采用自动提交模式。如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询都会被当做一个事务自动提交

并发一致性问题

丢失修改
脏读
不可重复读
幻读

产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性
并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂
数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题

封锁粒度
MySQL 中提供了两种封锁粒度：行级锁以及表级锁
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高
但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大
在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡

封锁类型
读写锁
排它锁(Exclusive)，简写为X锁，又称写锁
共享锁(Shared)，简写为S锁，又称读锁
一个事务对数据对象A加了X锁，就可以对A进行读取和更新。加锁期间其它事务不能对A加任何锁
一个事务对数据对象A加了S锁，可以对A进行读取操作，但是不能进行更新操作。加锁期间其它事务能对A加S锁，但是不能加X锁
意向锁
使用意向锁(Intention Locks)可以更容易地支持多粒度封锁
在存在行级锁和表级锁的情况下，事务T想要对表A加X锁，就需要先检测是否有其它事务对表A或者表A中的任意一行加了锁，那么就需要对表A的每一行都检测一次，这是非常耗时的
意向锁在原来的X/S锁之上引入了IX/IS，IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁。有以下两个规定：
一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁
一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁

通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其它事务对表A加了X/IX/S/IS锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败

数据库三大范式

• 第一范式（1NF）：列的原子性，列不能再分成其他几列

• 第二范式：当存在多个主键时，不能有某个非关键字段部分依赖于某个主键。第二范式就是在第一范式的基础上属性完全依赖于主键

• 第三范式：非主键列必须直接依赖于主键，不能存在传递依赖，是第二范式的子集

UNION

UNION与UNION ALL的区别

• UNION操作符用于合并两个或多个 SELECT语句的结果集，会筛选掉重复的记录
• 如果允许重复的值，使用UNION ALL，效率高于UNION
• 注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

数据类型

• 数值类型：整数类型（tinyint、smallint、mediumint、int和 bigint）+小数类型（精确or浮点）

  • 精确小数（小数点位数确定）
  • 浮点数类型（小数点位数不确定），浮点数又分为单精度（float）or双精度（double）
  • 默认有符号，可以用unsigned指定无符号，取值必须为0或正数

• 字符串类型：固定长度+可变长度（varchar or text）

  • 对于固定长度char(255)，里面无论存多少，都占255个字符长度的存储空间（具体字节数取决于字符集），对于可变长度varchar(255)，根据实际存储的字符来计算存储空间
  • char(n)的n最大值为255，可变长度的最大n值与字符集有关

• 日期类型：以下5种，日期也可以参与简单的算术运算

  • date：YYYY-MM-DD
  • time：HH:ii:ss
  • year：YYYY
  • datetime：YYYY-MM-DD HH:ii:ss，取值范围远远大于timestamp

  • timestamp：YYYY-MM-DD，HH:ii:ss

    • 将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间
    • 对于同一个timestamp类型的日期或时间，不同的时区显示结果不同
    • 当对包含timestamp数据的记录进行修改时，该timestamp会自动更新为MySQL服务器当前的日期与事件

• 复合类型：enum类型+set类型

  • enum：只允许从一个集合中取得某一个值
  • set：允许从一个集合中取得多个值
  • 使用复合数据类型可以实现简单的字符串类型数据的检查约束。

• 二进制类型：binary、varbinary、bit、 tinyblob、blob、mediumblob和longblob

  • 二进制类型的数据是一种特殊格式的字符串
  • 字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序
  • 除了bit数据类型按位为单位进行存储，其他二进制类型的数据按字节为单位进行存储，仅存在二进制字符集binary

特殊的字符

`\"` --转义后代表双引号"
`\'` --单引号'
`\\` --反斜线\
`\n` --换行符
`\r` --回车符
`\t` --制表符
`\0` --ASCII 0（NUL)
`\b` --退格符
`\_` --转义后代表下划线_
`\%` --转义后代表百分号%

• NULL与空字符串是两个不同的概念。
• NULL与整数零以及空格字符' '的概念也不相同。

• NULL与NUL（\0）不同

  • \0可以与数值进行算术运算，此时当做整数0处理
  • ‘\0`可以与其他字符串拼接，此时当做空字符串处理
  • NULL与其他数据运算时，结果永远为NULL

变量分为系统变量(以@@开头)以及用户自定义变量。

• 系统变量分为会话系统变量（或称为local变量、系统会话变量）以及全局系统变量（或称为全局变量），静态变量属于特殊的全局系统变量。

• 用户自定义变量分为用户会话变量(以@开头)以及局部变量(不以@开头)

  • 用户会话变量名以“@”开头，而局部变量名前面没有“@”符 号。
  • 局部变量使用declare命令定义(存储过程参数、函数参数除 外)，定义时必须指定局部变量的数据类型。局部变量定义后，才可以使用set命令或者select语句为其赋值。
  • 用户会话变量的作用范围与生存周期大于局部变量。局部变量仅在当前begin-end语句块内有效。

内置数据库

MySQL内置的几个数据库，可以用命令查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

• information_schema数据库存储了所有数据库的元数据。元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
• mysql：这个是mysql的核心数据库，存储数据库的用户、权限设置、关键字等。不可以删除。
• performance_schema：主要用于收集数据库服务器性能参数。提供进程等待的详细信息，包括锁、互斥变量、文件信息。
• test：没有任何东西，没有任何表，可以删除。

主键（primary key）和唯一键（unique key）的区别

SQL联合查询

内联结（inner join或join）：仅显示两个表中匹配行，即两个表都有才显示
左外联结（left outer join或left join）：左表有就显示，不论右表
右外联结（right outer join或right join）：右表有就显示，不论左表
全外联结（full outer join或full join）：左表和右表，有一个就显示，MySQL不支持全外联结
交叉联结（cross join）：笛卡尔积

参考：SQL联合查询