Mysql 精髓

一、基础

1、表的三范式：

• 第一范式：列属性不可再分（例如 用户表中常有姓名和电话，如果直接用了一个字段叫 ‘用户信息’，里面存 姓名加电话，这种就不符合一范式，因为属性里还可以再分；再比如收货地址应该单独分‘省市区’，而不是放在一起；又比如班级和学院应该单独分开而不是放同一个字段；等等这些例子）
• 第二范式：满足了 一范式，并且表中是联合主键，则其他非主键字段需依赖于联合主键而不能只依赖于主键中的某一个字段，即不能出现部分函数依赖（比如表中 课程&学号 共同确定成绩是 ok的，但表里还有 ‘课程学分’ 字段，它其实只需要 ‘课程’ 自己就可以确定，不需要 ‘学号’，这个就不满足 二范式了）（注：如果没有联合主键，二范式就没问题了）
• 第三范式：满足了 二范式，并且表中非主键字段都得直接依赖主键，而不能是间接，即不能出现 传递依赖（例如表中 ‘学号’ 来确定 ‘班级’是 ok的，但是如果表里还有 ‘班级地址’，它是通过 学号确定班级，班级再确定 班级地址的，这样产生了传递依赖，就不满足 三范式了 ）

2、mysql 语法顺序 & 执行顺序：

• 语法顺序（查询）

SELECT
FROM
LEFT JOIN ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

范例：
SELECT user.name
FROM user 
LEFT JOIN order ON user.id = order.uid 
WHERE order.price > 100
GROUP BY user.name 
HAVING count(1) > 4
ORDER BY user.name 
LIMIT 0,10

• 执行顺序（查询）

整个语句顺序：
FROM（将最近的两张表，进行笛卡尔积）---VT1
ON（将VT1按照它的条件进行过滤，对笛卡尔积进行筛选）---VT2
LEFT JOIN（以左表为基准，对筛选后的笛卡尔积记录处理）---VT3
WHERE（过滤VT3中的记录）--VT4...VTn
GROUP BY（对VT4的记录进行分组）---VT5
HAVING（对VT5中的记录进行过滤）---VT6
SELECT（对VT6中的记录，选取指定的列）--VT7
DISTINCT（如果还有这个字段，则会进行去重）--VT8
ORDER BY（对VT8的记录进行排序）--游标
LIMIT（对排序之后的值进行分页）
注意：如果 JOIN了多个表，那么重复 ON 和 JOIN步骤

WHERE 后面条件的顺序：
mysql 是从左到右
oracle 是从右到左

3、mysql 体系架构

• Connectors：客户端连接
• Management Serveices & Utilities：管理服务和工具
• Connection Pool：连接池，重用客户端连接
• SQL Interface：SQL 接口，接收 SQL命令
• Parser：解析器，解析 SQL语句并进行 词法 & 语法分析，最后构造成 解析树
• Optimizer：优化器，对解析好的 SQL语句进行优化，解析树转换为 查询计划（经过 选取-投影-联接）
• Cache和Buffer：查询缓存，对 SQL语句进行 hash作为 key，查询结果为 value
• Storage Engines：存储引擎接口，给上层服务 调用（它是基于表的，就是表可以单独设置 引擎类型，而不是数据库的）
• File System：文件系统，最终数据都是存储到 硬盘文件里

4、mysql 执行流程

1. 服务器通过用户名/密码或者SSL证书进行认证，认证后验证操作权限
2. 查询缓存，如有则返回，没有则进行后面的解析、优化、执行
3. mysql解析器根据查询sql 经过词法分析、语法分析构造解析树，并按照语法规则验证语句正确性（关键字或者顺序等），然后预处理器进一步校验（表名、字段名等正确性）
4. 查询优化器找到最优执行计划把解析树转化为查询计划（经过选取、投影、联接）
5. 执行计划调用查询执行引擎，查询引擎通过API接口查询数据
6. 查到数据，返回，并存入缓存

5、mysql 存储引擎

• InnoDB（5.5.8 之后默认）
• MyISAM

6、文件

1、参数文件：

• windows：my.ini
• linux：my.cnf

2、数据文件：（SHOW VARIABLES LIKE ‘%datadir%’）

• InnoDB：.frm 表定义文件、.ibd 独立表空间文件、.ibdata 共享表空间文件
• MyISAM：.frm 表定义文件、.myd 数据文件、.myi 索引文件

3、日志文件：

• 错误 err log：记录错误信息，参数 log_error配置的路径
• 二进制 binlog：记录了 DDL & DML 的语句，可用来恢复数据，配置 log_bin才能开启，mysql Server层负责而不是存储引擎负责，用来恢复/复制/审查，查看要借助工具 mysqlbinlog，InnoDB的事务未提交前二进制日志写到 binlog_cache_size大小的缓存中，提交后直接就写入二进制文件
• 事务日志 redo & undo：redo log用于 事务提交和回滚，存储引擎负责，ib_logfile0/ib_logfile1，每个文件默认 8M，写完1再2再1循环，undo放 .ibdata 文件
• 通用日志 general query log：CRUD 都记录，默认不开启，参数 general_log_file配置的路径
• 慢查询 slow query log：记录执行超过设定时间 long_query_time的 SQL，配置 slow_query_log=ON 开启，修改 log_output参数为 TABLE，则输出到mysql库下 slow_log表
• 中继日志：主从复制时， 从机 slave 会用到

7、存储结构

• InnoDB逻辑存储结构：表空间 tablespace -> 段 segment -> 区 extent -> 页/块 page/block -> 行 row（区 1M，Innodb页默认 16K，所以单个区有 64个页）

• 存储方式：数据页之间是双向链表，页内是单向链表
• 查找过程：

    （1）无索引：遍历双向链表，找到对应的页，再从页中遍历单向链表找到数据

    （2）有索引：二分查找找到对应的页，若是 辅助索引，则先找到索引对应的主键，用主键去 主键索引 拿数据，若是 主键索引 就直接拿数据

• 存储机制：日志文件是用 顺序IO 存储，数据文件采用 随机IO存储

8、数据类型

MySQL数据类型的选择（精讲版）

https://www.cnblogs.com/hzcya1995/p/13309479.html

注：数值中字段长度和类型取值范围无关，字段长度只是指明可能显示的最大长度，数据小于指定长度则用空格填充，大于指定长度如果没超过取值范围也是可以正常使用的。

9、常用内置函数

MySQL 常用内置函数与所有内置函数_返回1——x是正数,0——x为0,或者-1——x是负数,约束条件:仅能使用!,~,& ^,|,+,<-CSDN博客MySQL函数大全-CSDN博客

二、索引

1、简介：索引是一种数据结构，为了加快数据库 查询效率而产生，它是基于 存储引擎的，不同引擎实现索引方式不同，它也占用空间

2、InnoDB & MyISAM 的索引：都是基于 B+Tree实现（B+Tree只有叶子节点存数据， BTree是叶子和非叶子都存数据）

3、分类-存储维度：

• 聚集索引：按照唯一标识构造B+树，叶子存放行数据（如没主键，则用 唯一索引作为 聚集索引，如还没唯一索引，则 mysql 生成一个隐式的主键），innodb的唯一标识用聚簇索引因为它数据放在一起，但普通索引因为需要先通过普通索引找到叶子节点中的 聚簇索引key，再回表查数据因此它是非聚簇索引。
• 非聚集索引：叶子存放 数据地址，再通过 数据地址去拿真正的行数据，MyIsam就是非聚簇索引因为需要先通过索引再用 数据地址去找数据

4、分类-使用维度：

• 主键索引：非空，唯一
• 唯一索引：可空，唯一
• 普通索引：可空，不唯一
• 组合索引：多个列组成
• 全文索引：MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

5、最左前缀原则：索引匹配根据建立的顺序，从左到右匹配，一旦断层/范围查询后面就用不到索引，组合索引中使用（参考 https://www.cnblogs.com/ljl150/p/12934071.html）

组合索引 (name,age,sex)
where name='' and age='' and sex='' --可全部用到索引
where name='' and sex='' and age='' --可全部用到索引（因为优化器会调整顺序）
where name='' and sex='' --只有 name用到索引
where age='' and sex='' --没有用到索引
where name='' and address='' and age='' -- name,age 用到索引
where name='' and age != '' and sex='' --只有 name用到索引，但如果建立的是 (name,sex,age)那么 sex也能用，因为优化器会优化

存储在 B+Tree里是 (1,1,1),(1,1,2),(1,2,1),(1,2,2),(2,1,1)，它是先按第一个排序如 1到2，然后在第一个排好的基础上再对第二个排序

6、索引覆盖 & 回表 & ICP下推：

• 索引覆盖：查询的列都包含在 索引中（此时 extra中显示是 Using Index）
• 回表查询：先用 非主键索引拿到 主键索引的值，再通过 主键索引 查其他列的数据，多少条记录就回表几次
• 索引下推ICP：某些被索引的列，由原来的 MysqlServer 层判断下推到 引擎层去判断，然后再返回 Server层，加快效率，因为 Server和引擎交互次数也是费时间的（5.6开始有的，Extra的值为Using index condition）；比如 (name,age)的组合索引在 5.6之前是先用 name去存储引擎查到数据然后返回给 server层进行数据过滤，但 5.6开始就是利用 name,age两个值都放到存储引擎查询数据再返回 server层。

7、索引失效：

• 不符合 最左原则
• 索引上用了范围查询/不等号/判空/计算(如 age=age+1)
• like 时 % 号在前面
• 索引字符串没加 引号
• 索引用了 or

8、创建原则：

（1）需要索引：

• 主键自动是 索引
• 频繁查询的字段应该建 索引
• 多表 关联，关联字段应该建 索引
• 排序字段可有索引（因为索引用了 B+Tree，B+Tree是有顺序的）
• 统计或者分组的字段该有索引

（2）不需要索引

• 表记录少（因为索引也占用空间）
• 经常要 增删改
• 重复性较高的字段
• 查询字段使用 频率不高

9、索引原理：

（1）排好序的数据结构，不用全表扫描

（2）局部性原理 & 磁盘预读：磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，以 页为单位一般为4k或8k。

（3）选用 B+Tree：叶子节点才存数据，非叶子不存数据

三、执行计划 Explain

1、简介：查看 select 执行计划，分析执行过程（测试这个时记得数据库保持一定量的数据，如果只有一两条有时候优化器反而直接用全表扫描更快，会导致现象不是预期的）

2、参数：（参考 滑动验证页面）

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符
• select_type: SELECT 查询的类型（1、SIMPLE 表示此查询不包含 UNION 查询或子查询，2、PRIMARY  表示此查询是最外层的查询，3、SUBQUERY  子查询中的第一个 SELECT，4、DEPENDENT SUBQUERY 子查询依赖于外层查询的结果）
• table: 查询的表
• partitions: 匹配的分区
• type: join 类型（ALL < index < range ~ index_merge < ref < eq_ref < const < system）
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息

3、show profiles

四、事务 & MVCC

1、事务基础可看：springboot 事务与并发及回滚_springboot 事务未提交时的并发问题-CSDN博客

2、redo Log：InnoDB引擎中的重做日志，采用了 预写日志 的策略，数据写到 buffer pool之后，也把 redo日志写到 redolog Buffer（记录索引页、数据页、undolog页），事务提交后，再写到 redo日志文件（可通过配置修改事务提交和 redo日志落盘机制），成功写入 redo日志，则认为 事务提交成功，如果宕机可通过该日志恢复保证数据可靠性。存储引擎负责，ib_logfile0/ib_logfile1，写完1再2再1循环，因为日志是 顺序IO 存储，数据文件采用 随机IO存储，所以 redo log 比较快。等到 buffer pool中的数据落盘之后，则会清除 redolog日志。

3、undo Log：存储在共享表空间 .ibada文件中的回滚段中，每次事务更新都会新增一条事务 undo日志记录，里面有回滚指针记录上一条id值，可用来做回滚。

4、InnoDB 之 MVCC（依赖于 undolog 和 read view）

• 快照读：普通查询，不加锁，读的是历史版本，在 undolog中。
• 当前读：增删改，加 写锁，读的是最新版本。
• 一致性非锁定读：通过 MVCC，如果读的行在 删改，不需要等锁释放，而是直接去 undo读最新可见快照，也就是历史版本。
• 一致性锁定读：某些情况需要对读加锁保证一致性，可通过 for update加写锁 或者 lock in share mode加读锁。
• 适用：两种隔离级别，read_commited 时快照都是最新一份快照，而 repeatbale read则是读第一次查时的那一份。
• 实现：它是通过在每行记录后面增加两个隐藏列来进行版本控制（其实还多一个 row_id表示行唯一标识，不过事务我们用到另外两个字段而已），存了两个版本号；新增或者更新时就把事务号作为 当前版本号，删除或更新的旧记录的事务号就作为 删除版本号。如果恢复数据，是需要根据 undolog 一条条往回找，因为每行记录只记录了上一行的历史事务号，即 事务链表。

5、事务实现原理分析

• 原子性：undolog，undolog用于回滚
• 持久性：redolog + force log at commit，redolog 用于恢复、force log at commit保证事务提交后 redolog已落盘。
• 隔离性：MVCC + 锁
• 一致性：依赖 原子+持久+隔离 来完成

• redolog & undolog关联：undolog其实一开始也是在 redolog 里存了一份。一般 commit事务后redolog会刷盘，redolog每秒也会刷盘，如果在 commit前 redolog已经刷盘（即 redologl里的undolog刷盘），那么奔溃重启后就可以去 redolog里找对应的 undolog来回滚数据；但是 commit前如果 redolog还没刷盘，那重启后就只能去存在 .ibdata里找历史的 undolog日志；最后是 commit后的情况，redolog完成即使奔溃后面重启直接 redolog重做新记录即可，不需要恢复数据。

Innodb 中，即使查询语句，其实也是放在一个事务中。

五、锁

1、分类-粒度：

• 表锁：对整个表加锁，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
• 行锁：对一行数据加锁，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
• 元数据锁 MDL：对表做 增删改查时加 MDL读锁，即不能修改表结构；对 表结构做变更加 MDL写锁，即不能增删改

2、表锁分类-功能：（看锁状态：show status like 'table%';）

• 意向共享/IS锁：lock table <table> read
• 意向排他/IX锁：lock table <table> write

3、行锁分类-功能：（看锁状态：show status like 'innodb_row_lock%';）

• 共享/S/读锁：lock in share mode，加了之后其他事务能加 共享锁 但不能加 排他锁，但只读是正常的
• 排他/X/写锁：for update，加了之后其他事务 共享锁 和 排他锁 都不能加，但只读是正常的 （DML会自动加上 排他锁）

4、行锁分类-范围：

• Record Lock记录锁：锁一条记录，使用主键/唯一索引 锁定时产生
• Gap Lock间隙锁：如果用到锁的地方用了 非唯一索引，则会锁定前后记录之间的范围（比如 (id,age) ->(1,3),(3,6),(8,10)，如果我 where 用了 age=6，则会把 id 1~8 且 age 3~10 的记录锁住，这里必须是 id 和 age 同时落在范围才算，当然如果是 前后没有记录，则是锁 无穷小/大）；或者 唯一索引使用了范围条件，则会锁定范围条件内的数据（比如 主键 where id>1 and id < 5，锁定了范围 1~5之后，插入 id 为 3 或 4 的记录都需要等待）
• Next-Key 临键锁：记录+间隙锁，它只有在事务隔离级别为 可重复读 生效（左开右闭）

注：间隙锁是在 可重复度 级别下才会有的

5、死锁：多个事务各自拿了部分资源，都等待对方释放锁

6、InnoDB 锁原理：对索引项加锁，如果一个索引都命不中，则升级为 表锁

7、两阶段锁：同个事务中只能一直加锁，直到最后事务提交或回滚解锁，而不能在事务中边加锁边解锁

8、加锁类型：一条简单的更新语句，MySQL是如何加锁的？

行锁对 索引 加锁，如果执行锁的语句，where 后没有一个用到索引，在 RR级别下则会升级为 表锁

六、性能优化

• 使用自增主键，不要用 uuid（uuid是散列存储，可能造成原有的页分裂）
• 合理创建索引（长度）
• 可以合理反规范化
• 表字段类型和长度
• join表问题，子查询问题
• select 少用  * ，count(*)，where 里不用 1=1
• 创建组合索引，用 exists 而不是 in
• 上缓存

七、主从复制 & 读写分离

1、主从可看：分布式架构之数据库主从复制_分布式架构主从数据库-CSDN博客

2、读写分离：

八、分库分表

1、mycat：

2、sharding JDBC：分布式架构之数据库分库分表，Sharding-JDBC实战_sharding-column-CSDN博客

九、备份

十、参数配置

十一、扩展

1、InnoDB

（1）data page & index page：数据脏页 &索引页，增删改时 缓存的数据和磁盘数据不一致，所以它是脏页

（2）insert buffer page：插入缓冲页

（3）adaptive hash index：自适应 hash，对访问频率高的 热点页建立 哈希索引，提高查询效率

（4）lock info：锁信息，行/表锁信息

（5）data dictionary：数据字典，元数据信息如表结构、数据库/表名、表字段、视图、索引、存储过程、触发器等

（6）redo log buffer：重做日志缓存，

（7）double write：双写，对 缓冲池中的数据进行落盘的时候，数据写到 共享表空间 和 独立表空间 两份，防止写入磁盘奔溃时可以找回。

（8）check point：检查点

（9）局部性原理

（10）查看表结构数据

SELECT
	TABLE_NAME as '表名',	
	COLUMN_NAME as '字段名',
	COLUMN_TYPE as '数据类型',
	DATA_TYPE as '数据类型',
	(case when CHARACTER_MAXIMUM_LENGTH is not null then CHARACTER_MAXIMUM_LENGTH when NUMERIC_PRECISION is not null then NUMERIC_PRECISION else '' end) as '长度',
	CHARACTER_MAXIMUM_LENGTH,
	NUMERIC_PRECISION,
	(case when COLUMN_KEY = 'PRI' then '主键' else '' end) as '主键/外键',
	IS_NULLABLE as '是否为空',
	COLUMN_COMMENT as '备注'
FROM
	information_schema.COLUMNS
WHERE
	TABLE_SCHEMA = ''



SELECT TABLE_NAME,GROUP_CONCAT(COLUMN_COMMENT SEPARATOR ",") FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'ny_dev' group by TABLE_NAME

2、索引为什么不是 哈希表 或者是 其他树？

（1）哈希表：memory中有哈希索引，innodb有自适应哈希

a、哈希冲突可能导致数据散列不均匀，产生很多线性查询导致效率不高

b、范围查询效率不高

c、存储时需要加载所有哈希表到内存，内存耗费大

（2）BST：二叉搜索树

a、如果是递增时，就会变成链表

（3）AVL：二叉平衡树

a、插入的时候需要旋转树，性能较差

（4）Red/Black Tree：红黑树

a、每个节点只能存一个数据，数据多就导致树太深

（5）BTree：B树

a、每个节点能存储多个数据（Innodb每次读取 16K的数据）

3、log buffer刷盘设置

2、逻辑优先级

（1）and 比 or优先级高：如下 select * from user u where u.id = '' or u.id='' and u.type=3，即使前后两个记录都不满足 type=3，但是只有后面的记录会被过滤，因为先执行了后者的 and