昆山市住房和建设局网站,做网站需要具备什么,族蚂建站怎么样,苏州网站建设问问q778925409强涵文章目录一 存储引擎1 MyISAM 与 InnoDB 的差异二 索引1 主键索引与二级索引、索引覆盖、延迟关联2 聚簇索引与非聚簇索引3 数据结构3.1 哈希表3.2 B树3.3 B树3.4 跳表3.5 为什么不使用红黑树3.6 为什么不使用B树**4 索引下推 ICP **5 索引失效#xff08;索引不命中#xff…
文章目录一 存储引擎1 MyISAM 与 InnoDB 的差异二 索引1 主键索引与二级索引、索引覆盖、延迟关联2 聚簇索引与非聚簇索引3 数据结构3.1 哈希表3.2 B树3.3 B树3.4 跳表3.5 为什么不使用红黑树3.6 为什么不使用B树**4 索引下推 ICP **5 索引失效索引不命中的情况6 前缀索引和索引选择性7 索引用于 ORDER BY三 MySQL 事务1 ACID 及其保证手段2 事务的隔离级别3 多版本并发控制 MVCC3.1 MVCC 实现流程3.2 RR 等级下使用 MVCC 防止幻读四 设计规范与优化建议1 索引、数据表设计3 连接查询优化4 分库和分表5 ALTER TABLE6 SQL 执行时间过长如何优化五 锁服务器层与 InnoDB 存储引擎1 表锁S、X、IS、IX2 行锁S、X3 根据位置的行锁划分六 SQL 查询1 语法2 执行顺序3 常用函数 / 关键字4 SQL 更新数据的执行流程 两阶段提交七 InnoDB 日志1 日志对比2 Redo Log2.1 解决持久性的问题2.2 执行流程2.3 Redo Log 刷盘策略3 Undo Log3.1 数据分类3.2 Undo Log 的两种类型3.3 Redo Log Undo Log 生成过程SQL在执行引擎层的流程*4 Bin Log4.1 记录 Bin Log 的三种模式4.2 Bin Log 的刷盘策略4.3 使用 Bin Log 进行主从复制一 存储引擎
MySQL 服务器通过 API 与存储引擎交互接口屏蔽了不同存储引擎之间的差异对上层是透明的不同存储引擎之间不会相互通信只是简单地响应上层服务器的请求索引在存储引擎层实现
1 MyISAM 与 InnoDB 的差异
MySQL 5.5 之前MyISAM 引擎是 MySQL 的默认存储引擎之后的默认引擎是 InnoDB相比之下InnoDB 具有 支持事务、支持行级锁、支持外键、支持数据库异常崩溃后的安全恢复redo log 的特性 二 索引
索引在存储引擎层实现而非服务器层使用索引不一定能提升查询性能在表比较小时全表扫描的速度可能比使用索引更快索引的优点 减少服务器需要扫描的数据量避免创建排序表和临时表将随机 I/O 变为顺序 I/O 联合索引中把选择性最高的属性放在前面把需要范围查询的属性放在后面
1 主键索引与二级索引、索引覆盖、延迟关联
主键索引 主键索引存放的数据是表中的一条数据二级索引存放的数据是主键索引一张数据表有只能有一个主键并且主键不能为 null不能重复不发生索引覆盖时在根据主索引搜索时直接找到 key 所在的节点即可取出数据在根据二级索引查找时则需要先取出主键的值再使用主索引进行数据查找如果没有主键也没有合适的唯一索引那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引这个隐藏的主键是一个6个字节类型为长整型的列该列的值会随着数据的插入自增 二级索引 联合索引是二级索引多个属性建立联合索引只会建立一棵B树二级索引的内节点也保存了主键的值避免相同的二级索引值造成歧义 索引覆盖 除非发生索引覆盖的情况否则使用二级索引会导致回表回表当查到索引对应的主键后还需要根据主键再到主键索引查询索引覆盖需要查询的字段正好是索引的字段无论主索引或二级索引# 一种索引覆盖的特殊情况假设在 age 属性建立二级索引id 属性是主键索引
# 因为二级索引的叶子节点存放了主键索引所以下面的查询发生索引覆盖
select id from test_table where age 20;索引覆盖直接从内存中的索引读取数据避免了磁盘 I/O 延迟关联 使用索引覆盖查找目标数据行的主键 ID再和原表根据主键进行关联查询核心思想是在二级索引中利用查询条件得到主键再访问主键索引而非直接扫描数据
# 场景当偏移量很大时如 limit 100000, 10
# 取第100001-100010条记录会取出100010条记录然后将前100000条记录丢弃这无疑是一种巨大的性能浪费# 优化前先访问所有数据再截取
SELECT * FROM orders LIMIT 10000, 20;# 优化后先截取获得目标数据的主键再访问数据
SELECT * FROM orders AS o1 JOIN (SELECT id FROM orders LIMIT 10000, 20) AS o2 # 内层扫描使用了索引覆盖
ON o1.id o2.id;2 聚簇索引与非聚簇索引
聚簇索引指的是索引和数据存放在一起 对于 InnoDB 引擎的表来说该表的索引(B树)的每个非叶子节点存储索引叶子节点存储索引和索引对应的数据一个表只能存在一个聚簇索引因为无法把数据行存放在两个不同的地方主键索引是聚簇索引二级索引是非聚簇索引 聚簇索引提高了 I/O 密集型应用的性能如果数据全部存放在内存中则访问顺序没那么重要聚簇索引无法体现优势 索引类型优点缺点聚簇索引查询速度快无需回表需要依赖有序的数据因为数据结构是B树无序插入会导致页分裂更新代价大如果对索引列的数据被修改时那么对应的索引也将会被修改非聚簇索引更新代价比聚簇索引小叶子节点存放的是主键值数据移动时不需要修改二级索引需要依赖有序的数据可能会回表
3 数据结构
3.1 哈希表 插入数据时根据 key 进行哈希运算得到 bucket 的位置如果该位置无 value 则插入成功如果有则发生了哈希冲突使用拉链法或者红黑树解决类似 Java 的 HashMap哈希索引的缺点 只支持全值查询不能使用部分索引字段因为根据全部字段才能计算哈希值只支持等值比较查询包括 , IN(), 不支持范围查询同样也无法用于 ORDER BY 排序 自定义哈希索引 适用场景需要在长字符串 url 上建立索引使用方法 选择合适的哈希函数 MyHashFunc新增一个列 url_hash用该列存储字符串的哈希值并在该列上建立索引设置触发器维护该列使哈希值随 url 的修改同步更新执行查询 SELECT id FROM url_table WHERE urlhttp://www.mysql.com AND url_hashMyHashFunc(http://www.mysql.com)如果发生哈希冲突仅需比较相同哈希值的行的 url 是否相同
3.2 B树
MongoDB 采用的索引类型多路平衡查找树B树的中间节点存放的也是数据叶节点之间没有连接B树的检索的过程相当于对范围内的每个节点的关键字做二分查找可能还没有到达叶子节点检索就结束了 B树的阶树的节点最多的孩子结点子树数 根节点至少2个子树一个关键字非根节点至少有 ⌈M /2⌉ 个子树⌈M /2⌉ - 1 个关键字 3.3 B树 MyISAM 和 InnoDB 均采用的数据结构是多路平衡查找树B树的中间节点只存放索引数据全部都在叶节点上叶节点之间有连接相对于B树而言对范围查找的支持更好查询效率更加稳定因为每次查询必定会访问到叶节点叶子节点构成一个有序链表而且叶子节点本身按照关键字的大小从小到大顺序链接B树的阶数并非越大越好 阶数很大时一个节点的大小会超过一个页的大小读取这样一个节点会导致多次I/O操作造成性能下降要尽量让每个节点的大小等于一个页的大小
3.4 跳表
在 Redis 中的 Zset 使用到的数据结构在 Java 容器类中也有实现基于链表能够实现近似二分查找为什么在 MySQL 中选择 B 树而非跳表作为索引的数据结构 如果是查询磁盘文件B 树会比跳表的性能好很多因为磁盘查询性能比内存差所以要尽量减少查询的次数B 树每个节点按页存放数据每次查询可以查询一批数据到内存中而且 B 树的层数低可以减少访问磁盘的次数
3.5 为什么不使用红黑树
MySQL 用 I/O 次数衡量查询效率磁盘查找存取的次数往往由树的高度所决定 红黑树是内排序使用的数据结构B 树常用于外排序红黑树的高度通常更高因为是二叉树需要的磁盘 I/O 次数更多效率低B树可以有多个子节点树的高度通常为1~3层效率高B树的一个节点对应一个页如果使用红黑树的话每个节点页存放一条数据造成很大的浪费
3.6 为什么不使用B树**
B树执行范围查询时只能按照类似于中序遍历的方式进行效率不如B树在叶节点的顺序访问B树/B树的特点就是每层节点数目非常多层数很少就是为了减少 I/O 次数但是B树的每个节点都存放数据这无疑增大了节点大小增加了磁盘 I/O 次数磁盘I/O一次读出的数据量大小是固定的单个数据变大每次读出的就少导致 I/O次数增多而B树除了叶子节点均不存储数据节点小磁盘 I/O 次数就少
4 索引下推 ICP **
参考链接
索引下推的目的是减少回表次数即减少 I/O 操作对 InnoDB 存储引擎来说索引下推 只适用于二级索引因为对于 InnoDB 的主索引聚簇索引来说完整的行记录已经加载到缓存区了索引下推也就失去意义在使用 ICP 的情况下如果存在某些被索引的列的判断条件时即使这些列无法使用索引服务器层将这一部分判断条件传递给存储引擎然后由存储引擎判断索引是否符合服务器传递的条件只有当 索引符合条件时才会将数据检索出来返回给服务器
举例 假设在 (name, city) 建立了联合索引查询条件为 name LiSi and city like %z% and age 25 不使用索引下推 回表4次 存储引擎根据 (name, city) 联合索引找到 name LiSi 的记录共4条记录因为 city 的模糊查询违反了最佳左前缀原则所以只能根据 name 过滤的这4条记录中的 id 值逐一进行回表扫描去聚簇索引中取出完整的行记录并把这些记录返回给 Server 层Server 层接收到这些记录并按条件 nameLiSi and city like %Z% and age 25 进行过滤最终留下 (LiSi, ZhengZhou, 30) 这条记录 使用索引下推 回表2次 存储引擎根据 (name, city) 联合索引找到 nameLiSi 的记录共4条由于联合索引中包含 city 列存储引擎直接在联合索引中按 city like %Z% 进行过滤即使它已经无法使用索引过滤后剩下2条记录根据过滤后的记录的 id 值逐一进行回表扫描去聚簇索引中取出完整的行记录并把这些记录返回给 Server 层Server 层根据 WHERE 语句的其它条件 age 25再次对行记录进行筛选最终只留下 (LiSi, ZhengZhou, 30) 这条记录 5 索引失效索引不命中的情况
违背最佳左前缀法则 对于列 (age, class, name) 建立了联合索引如果查询条件是 class5 and namezy 等违背左前缀则无法使用该索引优化器可以决定 where 后面条件的顺序所以 class5 and namezy and age10 可以使用索引 计算、函数、类型转换导致索引失效索引列是表达式的一部分 WHERE name123 不能使用索引WHERE name123 可以使用索引 IS NULL 可以使用索引IS NOT NULL 不能使用索引范围条件右边指的是定义联合索引的右而非 where 条件中的右的列索引失效 对于列 (age, class, name) 建立了联合索引WHERE student.age30 AND student.name abc AND student.classId20 会导致 name 索引失效不等于 ! 导致索引失效OR 前后存在非索引的列索引失效因为对于非索引的条件需要全表扫描该情况下直接放弃使用索引而执行全表扫描对于模糊查询通配符%在搜寻词首出现一般会导致不使用索引%ABC不能使用但A%BC可以使用
6 前缀索引和索引选择性
为类型 TEXT/BLOB/长的VARCHAR 添加索引时不允许索引列的完整长度需要使用自定义哈希索引或前缀索引前缀索引需要选择合适的前缀长度使前缀选择性接近于完整列的选择性# 例如对于 TEXT 类型的字段 city
# 1.计算完整的列的选择性
SELECT COUNT(DISTINCT city) / COUNT(*) FROM demo;# 2.计算前5个字符的选择性
SELECT COUNT(DISTINCT LEFT(city, 5)) / COUNT(*) FROM demo;# 添加前缀索引
ALTER TABLE demo ADD KEY (city(5));显然前缀索引无法用于索引覆盖因为索引中不包含完整的列
7 索引用于 ORDER BY
索引的列顺序和 ORDER BY 子句的顺序完全一致且排序方向相同时才能使用索引对结果排序如果是关联查询只有 ORDER BY 子句引用的字段完全属于第一个表的时候才能用索引排序ORDER BY 子句需要满足最佳左前缀的要求除非前导列是常量
# 例如对于联合索引(rental_date, inventory_id, customer_id)# 正确示例1
... WHERE rental_date 2022-12-24 # 索引的第一列被指定为常数该索引可以用于 ORDER BY 排序
ORDER BY inventory_id, customer_id
# 正确示例2
... WHERE rental_date 2022-12-24
ORDER BY rental_date, inventory_id# 错误示例1排序方向不同
... WHERE rental_date 2022-12-24
ORDER BY inventory_id ASC, customer_id DESC
# 错误示例2字段不在索引中
... WHERE rental_date 2022-12-24
ORDER BY inventory_id
# 错误示例3违反最佳左前缀
... WHERE rental_date 2022-12-24
ORDER BY customer_id
# 错误示例4第一列是范围条件违反最佳左前缀
... WHERE rental_date 2022-12-24
ORDER BY inventory_id
# 错误示例5多个等于条件对排序而言是范围查询
... WHERE rental_date 2022-12-24
AND inventory_id IN(1, 2)
ORDER BY customer_id前缀索引无法用于 ORDER BY / GROUP BY 三 MySQL 事务
1 ACID 及其保证手段
原子性事务是最小的执行单位不允许分割事务内的一系列操作要么全都做要么全不做一致性事务执行前后数据库的一致性保持不变隔离性并发进行的事务之间互不影响持久性事务提交后对数据库中数据的修改是永久的InnoDB 引擎如何保证 ACID 使用 redo log 保证事务的持久性使用 undo log 来保证事务的原子性锁机制、MVCC 等手段来保证事务的隔离性 默认隔离级别是可重复读保证了事务的持久性、原子性、隔离性之后一致性才能得到保障
2 事务的隔离级别
隔离级别出现的异常脏读读到未提交数据不可重复读两次读取同一数据结果不同幻读两次查询结果数目不同读未提交√√√读提交×√√可重复读××√串行化×××
可重复读的隔离级别下使用 MVCC Next-key Lock 也可以避免幻读
3 多版本并发控制 MVCC
MVCC 处理的是 读数据 的问题避免读加锁写数据必须依靠加锁普通的 SELECT 语句在 读提交 和 可重复读 隔离级别下会使用到 MVCC主要针对的也是这两种隔离级别因为这两种隔离级别要求读到的是 已经提交了的 事务修改过的记录另外两种隔离级别不适用 MVCC 可串行化对读取的每一行记录加锁读未提交总是读取最新的数据无需同步 MVCC 的读指的是 快照读读取的是快照数据 , 而非当前读当前读读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁 普通的 SELECT 操作是快照读SELECT ... FOR UPDATE 和 SELECT ... IN SHARE MODE 是当前读
3.1 MVCC 实现流程
其实现主要依赖于 记录的隐藏字段 UndoLog ReadView读提交 和 可重复读 关于 ReadView 的区别是
读提交可重复读快照读每次查询时创建 ReadView事务开始时创建 ReadView当前读加锁加锁
ReadView 包含主要结构
creator_trx_id创建这个 ReadView 的事务 ID只读事务为0修改记录的事务会被分配 IDtrx_ids生成 ReadView 时活跃的读写事务的事务 ID 列表up_limit_id活跃的事务中最小的事务 IDlow_limit_id生成 ReadView 时系统中应该分配给下一个事务的 ID 值注意low_limit_id 并不是 trx_ids 中的最大值事务id是递增分配的。比如现在有id为123这三个事务之后id为3的事务提交了。那么一个新的读事务在生成 ReadView 时trx_ids就包括1和2up_limit_id的值就是1low_limit_id的值就是4 ReadView 规则 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同说明该记录的修改是由当前事务创建的允许访问如果被访问版本的 trx_id 属性值小于 ReadView 中的 up_limit_id 值表明生成该版本的事务在当前事务生成 ReadView 之前已经提交允许访问如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 low_limit_id 值表明生成该版本的事务在当前事务生成 ReadView 后才开启不允许访问如果被访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limit_id 之间那就需要判断一下 trx_id 属性值是不是在 trx_ids 列表中如果在说明生成该版本的事务在当前事务开启时尚未提交不允许访问反之说明生成该版本的事务在当前事务开启时已经提交允许访问 MVCC 整体流程 首先获取事务自己的版本号也就是事务 ID获取 ReadView查询得到的数据然后与 ReadView 中的事务版本号进行比较如果不符合 ReadView 规则就需要从 Undo Log 中获取历史快照最后返回符合规则的数据如果无符合规则的数据则返回空
3.2 RR 等级下使用 MVCC 防止幻读
幻读某个事务读取某个范围内的记录时另外一个事务在该范围内插入了新的记录导致当前事务再次读取该范围的记录时产生幻行针对两种不同的查询操作 快照读可重复读只会在事务开启后的第一次查询生成 Read View 并使用至事务提交。所以在生成 ReadView 之后其它事务所做的更新、插入记录版本对当前事务并不可见防止快照读下的幻读当前读InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时会锁定读取到的记录的同时锁定它们的间隙防止其它事务在查询范围内插入数据 四 设计规范与优化建议
1 索引、数据表设计
让主键具有 AUTO_INCREMENT 让存储引擎自己生成主键而不是手动插入让插入的记录的主键值依次递增避免页面分裂带来的性能损耗
页分裂的负面影响 插入之前需要从磁盘中找到目标页并读取到内存中产生大量随机 I/O导致大量的数据移动一次插入至少需要修改三个页页变得稀疏产生碎片
字段的数据类型定义准确设计数据表时适当遵循范式规则 1/2/3NF 1NF数据表的每个字段不可拆分2NF数据表中非主属性完全依赖于主属性3NF数据表中的非主属性不传递依赖于主属性 对数据表进行适当的行、列拆分分表数据库和表的字符集统一使用 utf8mb4表属性尽量设置为 NOT NULL因为 IS NOT NULL 不能使用索引且 NULL 会带来额外的问题联合索引中把选择性最高的属性放在前面把需要范围查询的属性放在后面
3 连接查询优化
驱动表无法避免全表扫描的表称为驱动表 在决定哪个表做驱动表的时候两个表按照各自的条件过滤过滤后计算参与 join 的各个字段的总数据量数据量小的那个表就是“小表”应该作为驱动表为了提高连接查询效率需要将小表作为驱动表大表作为被驱动表减少外层循环的次数LEFT JOIN 保证左表的每个记录都会出现即左表为驱动表所以右表是关键一定需要建立索引右外连接反之INNER JOIN 会自动决定驱动表、被驱动表对于连接属性如果两表只有一个具有索引则它作为被驱动表如果两个表都在该属性有索引小表作为驱动表大表作为被驱动表 需要JOIN 的字段数据类型保持绝对一致避免类型转换导致索引失效
4 分库和分表
切分定义应用场景分库数据库中的数据分散到不同的数据库上应用的并发量太大数据库中的数据占用的空间越来越大备份时间越来越长分表对单表的数据进行拆分可以是垂直拆分也可以是水平拆分单表的数据达到千万级别以上数据库读写速度比较缓慢
分库带来的问题 join 操作 同一个数据库中的表分布在了不同的数据库中导致无法使用 join 操作需要手动进行数据的封装比如在一个数据库中查询到一个数据之后再根据这个数据去另外一个数据库中找对应的数据事务问题 同一个数据库中的表分布在了不同的数据库中如果单个操作涉及到多个数据库那么数据库自带的事务就无法满足要求分布式 id 分库之后 数据遍布在不同服务器上的数据库数据库的自增主键已经没办法满足生成的主键唯一了需要为系统引入分布式 id
5 ALTER TABLE
MySQL 执行的大部分修改表结构的操作是用新结构创建一个空表将旧表数据插入新表最后删除旧表大部分的 ALTER TABLE 操作会导致 MySQL 服务中断一般解决方案 在不提供服务的机器上执行 ALTER TABLE再和提供服务的主机进行切换影子拷贝用新结构创建一个空表和原表无关通过原子的重命名操作切换两张表 ALTER TABLE 修改列的三种操作 ALTER COLUMN 改变、删除列的默认值直接修改 .frm 文件而不涉及表数据所以操作很快 CHANGE COLUMN 重命名列和修改列的数据类型 MODIFY COLUMN 修改列数据类型改变、删除列的默认值这个操作会有数据的读取和插入操作拷贝整张表到一张新表
6 SQL 执行时间过长如何优化
explain 分析 SQL 语句查看执行计划优化 SQL优化索引结构适当添加索引对数量大的表可以考虑进行分表数据库主从分离读写分离查看执行日志分析是否有其他方面的问题 五 锁服务器层与 InnoDB 存储引擎
该部分的参考
服务器层实现了最基本的表锁该表锁也实现了读锁、写锁的划分 读锁之间不阻塞其它情况存在阻塞 存储引擎层管理自己的锁策略设置自己的锁粒度服务器层是不可见的 InnoDB 采用两阶段锁协议事务执行过程中随时可以执行锁定只有执行 COMMIT 和 ROLLBACK事务提交或回滚时同时释放所有的锁
1 表锁S、X、IS、IX
表锁是 MySQL 中锁定 粒度最大 的一种锁是最基本的锁策略对当前操作的整张表加锁实现简单资源消耗也比较少加锁快不会出现死锁锁定粒度最大触发锁冲突的概率最高并发度最低意向锁 当事务要在记录上加上行锁时要首先在表上加上意向锁使得判断表中是否有记录正在加锁更简单无需遍历整张表的数据意向锁不与行级锁发生冲突意向锁之间不发生冲突 表级锁的兼容矩阵
2 行锁S、X
行锁在存储引擎层实现以下结论均基于 InnoDB行级锁是 MySQL 中锁定 粒度最小 的一种锁只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突加锁粒度最小并发度高但加锁的开销也最大加锁慢会出现死锁行锁是 加在索引 上的如果查询语句不使用索引索引失效/索引不命中的话那么它就会升级到表锁行锁根据行为划分为 共享锁S加了锁的记录所有事务都能去读取但不能修改同时阻止其他事务获得相同数据集的排他锁排他锁X允许已经获得排他锁的事务去更新数据阻止其他事务取得相同数据集的共享读锁和排他写锁 执行的命令与加锁的情况 修改语句加排他锁快照读不加锁当前读加锁select ... for update 加排他锁select ... in share mode 加共享锁
3 根据位置的行锁划分
根据行锁的位置不同进行划分每种情况又会分为共享锁读锁/排他锁写锁 Next-Key Lock 本质上是 Gap Lock Record Lock左开右闭MySQL 默认隔离级别是RR在这种级别下如果使用 select ... in share mode 或者 select ... for update 语句即当前读那么 InnoDB 会使用 Next-Key Lock防止幻读在上面已经讨论过 Gap Lock 使用范围条件而不是相等条件去检索并请求锁时InnoDB就会给符合条件的记录的索引项加上锁而对于键值在条件范围内但并不存在的记录就叫做间隙InnoDB此时也会对间隙加锁在 RU 和 RC 两种隔离级别下即使你使用 select in share mode 或 select for update也无法防止幻读。因为这两种隔离级别下只会有行锁而不会有间隙锁而如果是 RR 隔离级别的话就会在间隙上加上间隙锁 Record Lock 最简单的行锁 Insert Intention Lock 插入意图锁是一种间隙锁在行执行 INSERT 之前的插入操作设置。如果多个事务 INSERT 到同一个索引间隙之间但没有在同一位置上插入则不会产生任何的冲突假设有值为4和7的索引记录现在有两事务分别尝试插入值为 5 和 6 的记录在获得插入行的排他锁之前都使用插入意向锁锁住 4 和 7 之间的间隙但两者之间并不会相互阻塞因为这两行并不冲突 六 SQL 查询
1 语法
select 的属性不包括函数使用的必须在 group by 后面出现where 子句不能跟聚合函数min/max/sum/count/avg但可以跟 year/month/date/timestampdiff 等函数
select [distinct]
from
join
on
where
group by
having
union
order by
limit2 执行顺序
步骤6执行的是聚合函数特征是要结合多个行才能得到值非聚合函数 year()、substring()、if() 等执行时机更早having 字句出现的属性必须在 select 字句里出现在 select 字句出现的属性必须在 group by 字句里出现
1. from # 首先进入from字句
2. on # 根据条件选择需要连接的行
3. join # 执行连接
4. where # 对连接结果过滤
5. group by # 分组在分组之前执行了 select 后面的 if、substring_index... 等非运算操作
6. avg(), sum(), count()... # **聚合**函数
7. having # 对各个分组分别进行过滤
8. select # 选择结果
9. distinct # 结果去重
10. union # 将当前结果并上其它结果
11. order by [asc, desc] # 排序
12. limit # 选择指定行作为结果3 常用函数 / 关键字
day(date) / month(date) / year(date)获取日/月/年
# 获取2021年8月里每天的练习量
select day(date) day, count(*) question_cnt
from question_practice_detail
where year(date) 2021 and month(date) 8
group by dayif(cond, true_val, false_val)条件正确时选择前者否则后者
# 分别查看 25岁以下和25岁及以上 两个年龄段的用户数量
# 这里先执行了 if 再执行了 group by
select if(age25, 25岁及以上, 25岁以下) as age_cut, count(device_id) as number
from user_profile
group by age_cut# 复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况
# 主要问题是统计 result right 的行数count(result right) 是错误的写法应该 sum(if(result right, 1, 0))
# count 只能统计非空可选择不重复的字段的个数
select t1.device_id, t1.university, count(t2.question_id) question_cnt, sum(if(t2.result right, 1, 0)) right_question_cnt
from user_profile t1
left join (select device_id, result, question_idfrom question_practice_detailwhere month(date) 8) t2
on t1.device_id t2.device_id
where university 复旦大学
group by device_idcase...when...then...else...end
# 将用户划分为20岁以下20-24岁25岁及以上三个年龄段
selectdevice_id,gender,casewhen age 25 then 25岁及以上when age 20 then 20-24岁when age 20 then 20岁以下else 其他end age_cut
from user_profile# bonus类型btype为1其奖金为薪水salary的10%btype为2其奖金为薪水的20%其他类型均为薪水的30%
# 请你给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus
select eb.emp_no, e.first_name, e.last_name, eb.btype, s.salary, round(s.salary * (case eb.btype when 1 then 0.1when 2 then 0.2 else 0.3end), 1) bonus
from employees as e join salaries as s on e.emp_no s.emp_no join emp_bonus as eb on eb.emp_no s.emp_no and eb.recevied s.to_date and eb.recevied s.from_date
order by emp_no asc union [all]不加 all 会自动去重 substring_index(field, separator, index)如果 index 0 则取前缀否则取后缀 upper(str)转为大写 concat(str1, str2)拼接字符串 timestampdiff(unit,begin,end)获取时间差需要指定单位
4 SQL 更新数据的执行流程 两阶段提交 上图存疑先将数据加载到内存再将修改记录到 Redo Log
两阶段提交是为了保证 Bin Log 和 Redo Log 的一致性 一阶段将 Redo Log 写入到磁盘并将其状态设置为 Prepare二阶段将 Bin Log 写入磁盘并将 Redo Log 状态设置为 Commit 数据库崩溃时根据 Bin Log 和 Redo Log 的一致性判断恢复行为 Bin Log 有记录Redo Log 状态 Commit事务正常完成不需要恢复Bin Log 有记录Redo Log 状态 Prepare如果 Bin Log 事务完整则提交事务否则回滚事务Bin Log 无记录Redo Log 状态 Prepare回滚事务 七 InnoDB 日志
1 日志对比
类型执行任务作用所属层Redo Log记录物理级别的页的修改操作页号, 偏移量, 数据保证事务的持久性存储引擎层事务在内存中修改后写入 Buffer在事务发起提交后写入磁盘Undo Log记录逻辑操作日志比如执行 INSERT 时在 Undo Log 中记录与之相反的 DELETE 操作即每个修改操作的逆操作保证事务的原子性MVCC存储引擎层事务在内存中修改前写入Bin Log记录逻辑操作日志数据恢复、数据复制数据库层事务发起提交后根据策略写入
Redo Log 针对的是已提交数据的恢复操作即提交后未刷盘的情况下操作系统 / 数据库 发生停机时用到 Redo LogUndo Log 针对的是未提交数据的恢复操作在事务未提交的情况下需要 rollback用到 Undo LogUndo Log 不是 Redo Log的逆过程Undo Log 无需持久化而 Redo Log 需要
2 Redo Log
2.1 解决持久性的问题
事务执行并且 COMMIT 后如果没有 COMMIT 则无需进行任何恢复原子性数据的修改只在内存中完成并未同步到外存。此时发生宕机持久性的保持问题Redo Log 的解决方法是每次仅仅记录如何修改而非具体的数据所以占用空间很小数据库按一定频率将已提交的内存的数据同步到外存中。在此之前采用 WALWrite-Ahead Logging机制先将日志写入到外存再将数据写入到外存只有日志写入成功事务才算提交成功
2.2 执行流程 2.3 Redo Log 刷盘策略
此处讨论的是 Redo Log 的持久化策略而非数据只有 Redo Log 持久化过的数据修改这些修改后的数据才能写入外存
innodb_flush_log_at_trx_commit行为0事务提交时不写入 page cache也不刷盘交给后台线程完成1默认事务提交时立刻写入 page cache 并刷盘2事务提交时立即写入 page cache刷盘交给后台线程完成
下图中的编号代表事务提交时不同策略下 Redo Log 的位置
3 Undo Log
3.1 数据分类
未提交的回滚数据 (uncommitted undo information)已经提交但未过期的回滚数据 (committed undo information)特指未过期的 Update Undo Log用于 MVCC 的实现事务已经提交并过期的数据 (expired undo information)
3.2 Undo Log 的两种类型 Insert Undo Log INSERT 过程中产生的 Undo Log因为 INSERT 操作的记录只对事务本身可见其它事务不可见所以可以在事务提交后直接删除 Update Undo Log DELETE 和 UPDATE 过程中产生的 Undo Log用于实现 MVCC所以不能在事务提交后就删除而是放入 Undo Log 链表等待过期后再删除
3.3 Redo Log Undo Log 生成过程SQL在执行引擎层的流程* 事务开始发起更新数据的请求对于内存不存在的数据需要先从外存加载到内存INSERT / UPDATE / DELETE 数据前首先更新 Undo Log在内存 Buffer Pool 中更新数据更新 Redo Log Buffer事务操作执行完毕准备提交进入两阶段提交一阶段将 Redo Log 刷新到外存 状态设置为 Prepare二阶段将 Bin Log 刷新到外存将 Redo Log 状态设置为 Commit此时才算提交成功内存中修改后的数据按一定策略同步到外存
4 Bin Log
4.1 记录 Bin Log 的三种模式 模式行为优点缺点Statement Level默认记录每一条修改数据的SQL语句日志占用空间小节约磁盘I/O对一些特殊功能的复制效果不是很好比如函数、存储过程的复制Row Level记录的方式是行如果批量修改数据记录的不是批量修改的SQL语句而是每条记录被更改的SQL语句记录每一行数据被修改的细节不会出现特殊操作无法被复制的情况日志占用空间大Mixed两种模式的结合根据具体的SQL语句决定记录的日志形式--
如果使用 MySQL 的特殊功能相对少存储过程、触发器、函数选择 Statement Level如果使用 MySQL 的特殊功能较多可以选择 Mixed如果使用 MySQL 的特殊功能较多又希望数据最大化一致选择 Row level
4.2 Bin Log 的刷盘策略
sync_binlog策略0由系统决定写入时机1默认每次事务提交时将写入磁盘N每N次事务提交时写入磁盘牺牲一致性换取更高的性能
4.3 使用 Bin Log 进行主从复制 整个过程需要依赖三个线程主库的二进制日志转储线程从库的I/O线程和SQL线程 主库的数据更新事件update、insert、delete写入 Bin Log从库发起连接连接到主库主库创建一个 BinLog Dump Thread把 Bin Log 的内容发送到从库从库启动之后创建一个 I/O Thread读取主库传过来的 Bin Log 内容并写入到 Relay Log从库创建一个 SQL Thread从 Relay Log 里面读取内容并执行其中包含的事务使从库数据和主库保持一致 数据库主库和从库不一致常见优化方案 业务可以接受系统不优化强制读主使用高可用主库把读写任务都交给主库选择性读主在 cache 里记录哪些记录发生过写请求用来路由读主还是读从
