关于MySQL的基础知识
ps：一些图片来自网络，若有冒犯，请多包涵
1. MySQL基本架构
1.1 MySQL的应用架构
用户<= = = = =>客户端<= = = = = =>服务端
1.2 MySQL的逻辑架构
1.3 SQL查询语句的执行逻辑
1.4 SQL修改语句的执行逻辑
- 获取客户端更新语句的请求，查询引擎缓存池中是否有数据
- 没有则从磁盘文件加载，对此行记录加锁
- 将更新的旧值写入undolog，便于回滚
- 更新buffer pool中的数据（成脏数据）
- 将操作结果写入redo log buffer中，buffer中可能存储多个修改事务的结果
- 准备提交事务时，将redo log刷到redo log文件（磁盘）中
- 执行器生成本次更新的bin log，刷到bin log文件（磁盘）中
- 执行器调用引擎的提交事务接口，完成提交（此时，将本次事务的bin log文件名，文件位置写入redo log，并做一个commit标记）
- 修改后的数据写入磁盘
1.5 关于查看和配置查询缓存的一些操作
- show variables like ‘have_query_cache’：查看当前MySQL是否支持查询缓存
- show variables like ‘query_cache_type’：查看当前MySQL是否开启了查询缓存
- show variables like ‘query_cache_size’：查看查询缓存占用的大小
- query_cache_type=1：配置文件(my.ini)中开启查询缓存
- net stop(start) mysql：重启mysql
- show varaiables like ‘profiling’：查看profile状态(系统分析）
- set profiling=1：开启profile
- show profiles：查看当前会话的所有profile信息
- show profile for query 查询id：查看具体查询的profile信息
2. MySQL的存储引擎
2.1 查看数据库支持哪些引擎的语句
- select version();—— 查看数据库版本
- show engines——查看存储引擎
2.2 InnoDB和MyISAM存储引擎
InnoDB优点：支持事务、外键、行锁。这几个特点能满足高并发下的一致性需求
创建表时指定引擎：
CREATE TABLE xxx
(
region_id int primary key auto_increment,
region_name VARCHAR(25)
) engine=innodb default character set utf8;
修改表的引擎：
alter table t1 engine=innodb;
2.2.1 对比
| InnoDB | MyISAM |
|---|---|
| 支持事务，对每条SQL默认封装为事务，自动提交 | 不支持事务 |
| 支持外键，基本不用，需要关联表可以用table a = table b on a.id = b.id | 不支持外键 |
| 主索引是聚集索引，B+tree结构，索引和数据绑定 | 主索引是非聚集索引，B+tree结构，索引和数据分离，索引保存数据地址 |
| 必须有主键，不指定则默认6字节int类型 | 可以没有主键 |
| 主键索引和辅助索引存在层级关系，使用辅助索引查询需要回表(1.获得结果为主键id；2.通过id查询得到数据) | 主键索引和辅助索引是平级关系 |
| 不保存表的具体行数，所以不指定count时，需要全表扫描 | 用变量保存表的行数。（但是不能用where条件） |
| 不支持全文索引（5.7以后也支持） | 支持全文索引 |
| 支持表锁、行锁。默认行锁(实现在索引上的) | 只支持表锁 |
| 存储文件类型有——.frm(存表结构定义描述文件)；.ibd(存数据+索引))【8.0.frm也没了】 | 存储文件类型有——.frm(表结构定义描述文件)；.MYD(数据内容)；.MYI(存索引内容) |
2.3 InnoDB为什么推荐使用自增ID作为主键
自增id可以保证每次插入数据B+索引是从右侧扩展的，避免B+数频繁合并分裂。使用别的主键数据插入随机，效率差
2.4 InnoDB四大特性（简单概述）
2.4.1 插入缓冲（insert buffer/change buffer)
将多个插入操作合并为一次操作——只对非聚簇索引的插入和更新有效。先判断插入的非聚集索引是否在缓冲池中，不再则先放在insert buffer中，按照一定频率合并。
2.4.2 二次写（double write）
是一个缓存区域。2M。
位置：在buffer pool和数据文件之间。
运作方式：防止系统在写磁盘过程中崩溃。可以从double write区域中找到备份恢复。
特点：块写，IO消耗低
2.4.3 自适应哈希索引（用innodb_adapitve_hash_index开启）
针对被频繁访问的二级索引，建立哈希索引提升速度。使用B+数构造，建立的速度也快
innodb会监控对表上索引页的查询，发现可以使用，则自动建立
使用范围：对页的连续访问必须一样
2.4.4 预读(read ahead)
线性预读：将下一个extent预读到buffer pool中
随机预读：废弃
3. MySQL日志系统
3.1 有哪些日志
- 重做日志redo log
- 回滚日志undo log
- 二进制日志binary log
- 错误日志error log
- 慢查询日志slow query log
3.2 WAL预写日志
MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。原子性和持久性
3.3 重做日志
- 记录数据库数据变化前后的值，不管事务是否提交，都记录。
- 两部分组成：redo log buffer和redo log file
- 当数据页从磁盘读到buffer pool中进行修改时，修改后的数据与数据页上的不一样，称之为脏页。
- 数据还没有刷到磁盘中，服务重启，数据就会丢失
- redo log会记录数据页发生的改变，防止服务重启导致数据丢失
3.4 重做日志的刷盘机制
- 用innodb_flush_log_at_trx_commit配置
- 0：主线程周期刷新
- 1：事务提交时，日志缓冲区写入磁盘
- 2：事务提交时，将redo log buffer写入文件系统缓存，由系统内部来fsync磁盘文件
3.5 回滚日志
- 逻辑记录。delete一条数据，undo log中会insert
- 执行rollback，可以读取内容进行回滚
- 记录事务开始前的状态
3.6 回滚日志存在形式
- 段管理。默认128个rollback segment回滚段。每个回滚段有1024个redo log segment
- 回滚段分配：
- slot 0：预留给系统表空间
- slot 1-32：预留给临时表空间，每次重启数据库，重建临时表空间
- slot 33-127：有独立表空间，预留给undo独立表空间；没有则预留给系统表空间
3.7 两阶段提交
即redo log和binlog的一致性。事务完成且bin log写入完成，从prepare状态转变为commit 状态；若发生故障，从prepare状态恢复事务前的状态
3.8 日志特性比较
| redo log | undo log | bin log | |
|---|---|---|---|
| 作用 | 保持事务持久性 | 事务回滚，原子性 | 主从赋值环境的建立 |
| 产生主体 | InnoDB | InnoDB | MySQL |
| 类型 | 物理日志 | 逻辑日志 | 逻辑日志 |
| 内容 | 每个页的修改 | 修改前的数据 | 执行的SQL语句 |
| 每个事务的日志数量 | 修改的行数据量 | 修改的行数据量 | 事务提交后的一条SQL语句 |
| 写入方式 | 循环写 | 循环写 | 追加写 |
4. 数据库表设计
4.1 常用数据类型
- 字符串类型：char varchar text
- 日期时间类型：date time datetime timestamp
- 数值类型：tinyint int bigint decimal
- 二进制类型：blob mediumblob longblob
- 其他：enum set json
4.2 数据类型应用有哪些原则
- 简单（能用int不用varchar）
- 使用最小数据类型（能用tinyint不用int）
- 要存储小数可以考虑使用decimal类型
- 避免使用text、blob等大字段类型
4.3 常用字段约束
- 非空约束(not null)：字段的值不允许为空
- 主键约束(primary key)：字段值不允许为空并且唯一
- 唯一约束(unique key)：字段值必须唯一
- 检查约束(check)：字段值需要在指定范围
- 外键约束(foreign key)：字段值需要参考引用表中的字段值
- 字段默认值(Default Value): 允许为空的字段给一个默认值
4.4 三大范式
三大范式的提出已经过于久远，现如今实际开发中，已经被逐渐淡化。简单了解即可。
- 第一范式(1NF)：字段不可再分(原子性)。例如姓名可再分为姓和名，这属于可再分。
- 第二范式(2NF)：首先要满足1NF，保证数据库表中每一列都与主键相关，而不是与主键的某一部分相关。
- 第三范式(3NF)：在2NF基础上，任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
4.5 数据库表设计要考虑什么？
- 命名规范(可读性-见名知意)
- 合适的数据类型以及注释
- 主键设计要合理(最好没有业务含义)
- 表中要有标识逻辑删除的字段(is_deleted)
- 表中建议有created_time,modified_time字段
- 表中不建议有太多字段,例如超过50个是不推荐的
- 尽量使用not null对字段进行标识
- 评估哪些字段添加什么约束,是否使用索引.
- 为提高查询效率可以适当添加字段冗余
- 不推荐做物理外键,可以有逻辑外键(业务上有关系)
- 表的存储引擎推荐使用InnoDB
- 大字段建议放在一张独立的表中
- 分库分表
4.6 SQL语句语法的执行顺序
- from
- on
- join
- where
- group by
- having
- select
- order by
- limit
5. MySQL中的锁和MVCC
5.1 ACID
- 原子性:通过undolog实现-执行回滚;
- 一致性:通过undolog,redolog,binlog;
- 隔离性:通过锁,MVCC-多版本并发控制;
- 持久性:通过redolog日志实现;
5.2 隔离性
5.2.1 四种隔离级别
- 读未提交
- 读已提交
- 可重复读
- 序列化
5.2.2 可能出现的问题
-
脏读：A事务修改数据没有提交，B也读取了数据还进行了操作，A撤销回滚，恢复数据。B事务读到的数据和数据库中不一致。排它锁解决
-
不可重复读：A事务多次读取表中某一行数据，B事务在此期间对数据进行修改，导致A的读取结果不同。行级锁、共享锁解决
-
虚读：A事务读取到了B事务新增或删除的数据，导致前后两次读取结果不同。表级锁解决
-
记忆表格：
隔离级别 脏读 不可重复读 虚读（幻读） 读未提交 可能 可能 可能 读已提交 不可能 可能 可能 可重复读 不可能 不可能 可能 串行化（序列化） 不可能 不可能 不肯能
5.3 MySQL的锁分类
-
性能：
乐观锁：认为没有其他事务对该数据进行操作，仅在提交前进行比对，被修改则重新操作
悲观锁：认为会有其他事务对其进行操作，而直接加锁
-
操作类型：共享锁(读锁)、排它锁(写锁)
-
粒度：全局所、表锁、行锁、间隙锁
5.4 锁的应用
5.4.1 全局锁的应用
- 所定所有表，用于逻辑备份
- fiush tables with read lock 加锁，只能读
- unlock tables
5.4.2 表锁的应用
- 针对表。有表读锁、表写锁、元数据锁
- 表读锁：lock rable XXX read。只读。其他线程阻塞
- 表写锁：lock table XXX write。可读写。其他线程阻塞
5.4.3 行锁的应用
- 针对某一行。可以在某一行添加共享锁和排它锁
- 共享锁：(select * from XX where xxx=xxx lock in share mode)针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 排它锁：(…for update) 当前写操作没有完成前，它会阻断其他写锁和读锁
5.4.4 死锁
事务A持有资源1，事务B持有资源2，事务A完成业务需要资源2且事务B完成业务需要资源1。此时出现资源竞争，两个事务都阻塞。
5.5 MVCC
5.5.1 用处以及理解
Multi Version Concurrent Control。用来解决事务隔离性的一种方式，针对于读已提交和可重复读。
5.5.2 底层逻辑
undo log日志、ReadView快照读、三个隐藏字段（DB_TRX_ID/DB_ROLL_PTR/DB_ROW_ID）
5.5.2.1 三个隐藏字段
- DB_ROW_ID隐藏ID：数据表没有主键，也没有非空唯一字段。自动生成
- DB_TRX_ID当前事务ID：记录创建这条记录或者最后一次修改该记录的事务id
- DB_ROLL_PTR上版本指针：指向本记录上一个版本，配合undo log实现回滚
5.5.2.2 undo日志版本链
当前事务，对age数据进行修改并更新，产生一条undolog日志，多个事务同时操作此数据，有多条日志。其中DB_ROLL_PTR(上版本指针)会指向上一版本
5.5.2.3 快照读和当前读
-
快照读
- 也叫普通读。读取记录中的可见版本数据，不加锁，是普通select
- 小提示——如果隔离级别是串行，会变成当前读
-
当前读
-
也叫锁定读。读取最新版本数据，读之前要获得对应记录的锁。举例：
select * from xxx where id = 1 lock in share mode; select * from xxx where id = 1 for update;
-
5.5.2.4 ReadView
-
理解：一个视图。记录的是事务数据。这些事务数据是当前事务能读取的
-
组成：
-
create_trx_id: 保存创建ReadView的当前事务id。
-
m_ids: 截止到当前事务id之前,所有活跃的事务id(还没有commit的事务)
-
min_trx_id: 记录活跃事务id中(m_ids)的最小值
-
max_trx_id: 记录当前事务结束后应分配的下一个事务id值
-
5.5.2.5 重复读底层原理
启动事务时生成一个ReadView，事务期间使用ReadView保证读到的数据都是事务启动前的记录。
举例说明：
5.5.2.6 读已提交底层原理
A事务每次select都会生成一个新的ReadView，那么事务期间，B事务多次读取同一数据，前后两此的结果可能不同。
6. MySQL中的索引
6.1 啥是索引？为啥要用？有啥优缺点？
- 索引是一种数据结构。表就像是书，索引就像是目录
- 方便查找数据（查字典要翻目录）
- 缺点是占用额外空间（目录要占好几页），更新数据时要更新索引（内容更新，目录要变动）
6.2 索引的分类
- 逻辑应用角度：主键、普通、唯一、联合、空间索引等
- 物理存储角度：聚集、非聚集索引
- 数据结构角度：hash索引、B+数索引
查看索引的语句是：
show index from 表名
6.3 添加、删除索引的sql语句
添加索引：
普通索引：
创建表时添加：create table 表名 (.... , index 索引名 字段名)
创建表后添加：create index 索引名 on 表名(字段名)
alter语句添加：alter table 表名 add index 索引名(字段名)
唯一索引：
create unique index 索引名 on 表名(字段名)
alter table 表名 add unique index 索引名(字段名)
创建联合索引:
create index 索引名 on (字段名，字段名，...)
alter table 表名 add 索引名(字段名，字段名，...)
删除索引：
drop index 索引名 on 表名
drop index index_first_last on student;
6.4 B-树
-
特点：每个非叶子节点允许有多个分叉，每个非叶子节点存储指针(子节点地址)、索引、数据。叶子节点存索引和数据
-
劣势：
非叶子节点存数据，则存的索引就少了，分叉就少了，树就高了，查询效率就低了
叶子节点不支持范围查询，查询需要从根节点开始
6.5 B+树
- 特点：非叶子节点存索引和指针，叶子节点存索引和数据。
- 优势：叶子节点有序，使用双向链表连接，支持范围查询
6.6 聚集索引和非聚集索引
- 聚集索引：索引和数据不分离
- 非聚集索引：索引和数据分开存储。查找时，先找到索引，获得索引下的数据地址，根据地址找到数据。
7. SQL调优
7.1 调优的一些原则
- 减少表数据量——分表
- 减少数据访问量——建立索引
- 减少数据计算——数据库中的计算拿到程序中
7.2 优化的基本逻辑
- 规范编写语句
- 语句逻辑正确——小表驱动大表
- 定位慢SQL——耗时长的语句进行优化
- 优化策略——sql结构调整，索引应用
- 结合业务分库分表——减少单表数据量
7.3 常见案例
- 查询避免使用select *
- where语句中不适用or
- where语句中不出现与null比较
- 避免查询中存在隐式转换
- 避免where中使用 != 或<>
- 避免like后前缀使用“%”
- 查询时采用最左匹配
- 避免查询中使用SQL函数
- 避免in后面的数据太多，避免使用in
- 多个查询条件、分组条件、排序条件时，使用联合索引
- 表连接优先用inner join ，小表驱动大表
- 表关联的字段使用相同编码
- 批量插入代替循环插入
- 清空数据使用truncate
- limit限制返回条数，list 、query接口必须分页。有效避免内存溢出
- 同步数据，使用update_at检索出增量数据。例如ES索引构建的定时任务，只会去跑最近时间往前2分钟的数据
- 分页查询使用limit，当pageNum增大时，影响效率。此时可以借助上次查询的最大Id，也可以是Orderby的最大值最小值做条件的过滤。
- 缩小数据集的条件可以放在前面
7.4 慢SQL的一些操作演示
1.查看慢查询日志是否打开：
show variables like '%slow_query_log%';
2.开启慢查询日志：
set global slow_query_log = ON;
3.查看默认的慢查询阈值（默认10秒）：
show variables like '%long_query_time';
4.设置慢查询时间：
set slow_query_time = 1;
5.慢查询日志的路径：
show global variables like 'datadir';
6.慢查询日志文件名：
show global variables like 'slow_query_log_file';
7.5 分析慢SQL——Explain执行计划
- 语句前加explain即可
- 可以查看关联查询的执行顺序
- 查看查询操作的具体类型
- 哪些索引会命中
- 表中有几条记录参与
7.5.1 常见字段
-
id：
-
id相同：执行顺序由上至下
-
id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
-
id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
-
-
select_type：查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询
- SIMPLE：简单的select查询，查询中不包含子查询或者union
- PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
- SUBQUERY：在select 或 where列表中包含了子查询
- DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
- UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
- UNION RESULT：从union表获取结果的select
-
type：访问类型。最少是range，最好能是ref
-
system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
-
const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const
-
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描
注意：ALL全表扫描的表记录最少的表如t1表
-
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
-
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引
-
index：index与ALL区别为index类型只遍历索引树。index是从索引中读取，而ALL是从硬盘读取
-
ALL：遍历全表以找到匹配的行
-
-
possible_keys：查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
-
key：实际使用的索引，如果为NULL，则没有使用索引。
-
key_len：查询中使用的索引的长度
-
ref：索引的那一列被使用了
-
rows：找到所需的记录所需要读取的行数
-
Extra：额外信息
- Using filesort ：mysql无法利用索引完成的排序操作
- Using temporary：mysql在对查询结果排序时使用了临时表，常见于order by 和 group by
- Using index：select操作中使用了覆盖索引，出现Using where，说明索引用来查找，没有则用来读取
- Using where：
- Using join buffer：使用了链接缓存
- Impossible WHERE：where子句的值总是false
7.6 分析慢SQL——Profile工具
Profile的演示：
1.检查是否支持
select @@have_profiling
2.检查是否关闭
select @@profiling
3.开启和查看
set progiling = 1
4.执行一个sql语句
select * from employees where salary > 5000;
5.查看sql的query id
show profiles
6.查看具体sql的执行情况
show profile for query 1;
808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
