元数据笔记

笔记"/>

元数据笔记

元数据

data profile

阿里的 data profile 是一个基于元数据的数据管理和治理平台，它可以为数据仓库、数据湖、湖仓一体等解决方案提供统一的全链路大数据开发治理平台。它的核心思路是为纷繁复杂的数据建立一个脉络清晰的血缘图谱，通过图计算、标签传播算法等技术，系统化、自动化地对计算与存储平台上的数据进行打标、整理、归档。它可以帮助数据使用者和开发者快速找到所需的数据，提高数据的研发效率和质量。

定义

元数据（ Metadata ）是关于数据的数据。元数据打通了数据源、数据仓库、数据应用，记录了数据从产生、加工到消费的全过程。

在数据仓库系统中，元数据主要记录数据仓库中模型的定义、各层级间的映射关系、监控数据仓库的数据状态及 ETL 的任务运行状态。元数据可以帮助数据仓库管理员和开发人员非常方便地找到他们所关心的数据，用于指导其进行数据管理和开发工作，提高工作效率。

元数据分类

技术元数据（ Technical Metadata)

存储关于数据仓库系统技术细节的数据，是用于开发和管理数据仓库使用的数据。阿里巴巴常见的技术元数据有：

1. 分布式计算系统存储元数据，如 MaxCompute 表、列、分区等信息。记录了表的表名。分区信息、责任人信息、文件大小、表类型，生命周期，以及列的字段名、字段类型、字段备注、是否是分区 段等信息。
2. 分布式计算系统运行元数据，如 MaxCompute 上所有作业运行等信息：类似于 Hive Job 日志，包括作业类型、实例名称、输入输出、 SQL 、运行参数、执行时间、最细粒度的 Instance (Max Compute MR 执行的最小单元）执行信息等。
3. 数据开发平台中数据同步、计算任务、任务调度等信息的元数据。a) 数据同步包括输入输出表和字段，以及同步任务本身的节点信息；b) 计算任务主要有输入输出、任务本身的节点信息；c) 任务调度主要有任务的依赖类型、依赖关系等，以及不同类型调度任务的运行日志等。
4. 数据质量和运维相关元数据，如任务监控、运维报警、数据质量、故障等信息，包括任务监控运行日志、告警配置及运行日志、故障信息等。

业务元数据（Business Metadata ）

业务元数据从业务角度描述了数据仓库中的数据，它提供了介于使用者和实际系统之间的语义层，使得不懂计算机技术的业务人员也能够“读懂”数据仓库中的数据。

业务元数据有

1. OneData元数据，如维度及属性、业务过程、指标等的规范化定义，用于更好地管理和使用数据。
2. 数据应用元数据，如数据报表、数据产品等的配置和运行元数据。

元数据体系建设

1. 首先梳理清楚元仓底层数据，对元数据做分类，如计算元数据、储元数据、质量元数据等，减少数据重复建设，保障数据的唯一性。
2. 另外要丰富表和字段使用说明，方便使用和理解。根据元仓底层数据构建元仓中间层，依据 OneData 规范，建设元数据基础宽表，也就是元数据中间层，打通从数据产生到消费整个链路 ，不断丰富中间层数据，如MaxCompute 元数据、调度元数据、同步元数据、产品访问元数据、服务元数据等。
3. 基于元数据中间层，对外提供标准统一的元数据服务出口，保障元数据产出的质量。丰富的元数据 中间层不仅能够为集团数据提供在计算、存储、成本、质量、安全、模型等治理领域上的数据支持，形成一套完整的 ROI 数据体系，而且为集团数据进行数据内容、数据域、数据主题、业务属性等的提取和分析提供了数据素材。

元数据应用

Data Profile

Data Profile的核心思路是为纷繁复杂的数据建立一个脉络清晰的血缘图谱。通过图计算、标签传播算法等技术 ，系统化、自动化地对计算与存储平台上的数据进行打标、整理、归档。形象地说， Data Profile 实际承担的是为元数据“画像”的任务。

Data Profile 开发出了四类标签：

● 基础标签 ：针对数据的存储情况、访问情况、安全等级等进行打标。

● 数仓标签：针对数据是增量还是全量、是否可再生、数据的生命周期来进行标签化处理。

● 业务标签：根据数据归属的主题域、产品线、业务类型为数据打上不同的标签。

● 潜在标签：这类标签主要是为了说明数据潜在的应用场景， 比如社交、媒体、广告、电商、金融等。

利用 Data Profile 不仅可以节约研发人员的时间成本，同时对阿里巴巴内部的非研发人员来说，也可以更直观地理解数据、利用数据，从而提升数据的研发效率。

元数据门户

元数据门户致力打造一站式的数据管理平台、高效的一体化数据市场，包括“前台”和 “后台”。

● “前台”产品为数据地图，定位消费市场，实现检索数据、理解数据等“找数据”需求；

● “后台”产品为数据管理，定位于一站式数据管理，实现成本管理、安全管理、质量管理等。

数据地图

1. 在进行数据分析前，使用数据地图进行关键词搜索，帮助快速缩小范围，找到对应的数据；
2. 使用数据地图根据表名直接查看表详情，快速查阅明细信息，掌握使用规则：
3. 通过数据地图的血缘分析可以查看每个数据表的来源、去向，并查看每个表及字段的加工逻辑。

数据管理平台

围绕数据管理，服务于个人开发者、BU 管理者、系统管理员等用户，提供个人和 BU 全局资产管理、成本管理和质量管理等。

针对个人开发者，主要包括计算费用和健康分管理、存储费用和健康分管理 ，并提供优化建议和优化接口；

针对 BU 管理者和管理员，主要提供BU 、应用、集群等全局资产消耗概览、分析和预测。

应用链路分析

对于某个数据计算任务或表，其重要程度如何，是否还有下游在使用，是否可以下线；阿里巴巴有这么多数据产品，都依赖哪些MaxCompute 表，对这些 MaxCompute 表是否需要根据应用的重要程度进行资源、运维保障……对于这些问题，我们都可以通过元数据血缘来分析产品及应用的链路，通过血缘链路可以清楚地统计到某个产品所用到的数据在计算、存储、质量上存在哪些问题，通过治理优化保障产品数据的稳定性。

通过应用链路分析，产出表级血缘、字段血缘和表的应用血缘。

其中表级血缘主要有两种计算方式：一种是通过 MaxCompute 任务日志进行解析；一种是根据任务依赖进行解析。

数据建模

传统的数据仓库建模一般采用经验建模的方式，效率较低且不准确。基于现有底层数据已经有下游使用的情况，我们可以通过下游所使用的元数据指导数据参考建模。

通过元数据驱动的数据仓库模型建设，可以在 定程度上解决此问题，提高数据仓库建模的数据化指导，提升建模效率。

所使用的元数据主要有：

● 表的基础元数据，包括下游情况、查询次数、关联次数、聚合 数、产出时间等。

● 表的关联关系元数据，包括关联表、关联类型、关联字段、关联次数等。

● 表的字段的基础元数据，包括字段名称、字段注释、查询次数、 关联次数、聚合次数、过滤次数等。

在数据仓库的星形模型设计过程中，可以类似于如下使用元数据：

● 基于下游使用中关联次数大于某个阔值的表或查询次数大于某 个阐值的表等元数据信息，筛选用于数据模型建设的表。

● 基于表的字段元数据，如字段中的时间字段、字段在下游使用中 的过滤次数 ，选择业务过程标识字段。

● 基于主从表的关联关系、关联次数，确定和主表关联的从表。

● 基于 从表的 段使用情况，如字段的查询次数、过滤次数、关 联次数、聚合次数等，确定哪些字段进入目标模型。

mysql

一条sql语句怎么运行的

• 客户端通过连接器与mysql服务器建立连接，获取权限，维持和管理连接；
• 查询缓存，如果开启查询缓存，则先去缓存哈希表查找数据，如果命中缓存，则直接放回数据端，没有命中就执行下面逻辑
• 解析器通过词法分析和语法分析验证sql是否合法，并生成相应的语法树，并通过预处理器进一步检查语法树是否合法
• 接着，优化器将语法树转化为执行计算，执行计划决定了执行器选择存储引擎哪个方式去获取数据
• 执行器：执行计算生成器生成执行计划后，执行引擎负责按照计算执行查询。它处理表的扫描，索引的使用，连接操作，排序和聚合等任务。
• mysql将结果集增量，逐步返回给客户端

sql语句执行顺序

（1）from 子句组装来自不同数据源的数据；
（2）ON 应用ON过滤器
（3）JOIN 添加外部行
（4）where 子句基于指定的条件对记录行进行筛选；
（5）group by 子句将数据划分为多个分组；
（6）使用聚集函数进行计算；
（7）使用 having 子句筛选分组；
（8）计算所有的表达式；
（9）select 的字段；
（10）DISTINCT 行去重
（11）使用 order by 对结果集进行排序。
（12）LIMIT/OFFSET 指定返回行
以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。

数据库三范式

第一范式(字段不能重复且不能分解）

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第二范式(增加主键)

主键可以是一列或者多列组成的，根据主键，马上可以精确到特点的一行数据

第三范式(消除非主键的传递关系)

例如表中主键是商品编号（100），但是商品类别名称和商品类别描述可以根据商品类别编号（3）字段去检索，这样商品类别名称（100/3）和商品类别（100/3）描述编号会有冗余。

in和exit的区别

in 和 exists的区别: in先查询子表，exit后查询子表。如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

union vs unioin all

• union : 对两个结果集进行并集操作，会进行去重，记录不会重复，按字段的默认规则排序
• union all ：对两个结果集直接进行并集操作，记录可能会重复，不会进行排序。

6种join

内连接(inner join)：内连接查询出的数据是两张表的交集。
左外连接（left join)：即包含左边表的全部行（不管右边的表中是否存在与它匹配的行），和右边表中全部匹配的行。
右外连接(right join)：包含右边表的全部行（不管左边的表中是否存在与它匹配的行），和左边表中全部匹配行。
左连接(left join …where B.key is null)：查询出左表独有的数据
右连接(right join …where A.key is null)：查询出右表独有的数据
全连接（full otuter join)：查询出左右两表的所有数据

join优化（补充）

• 小表驱动大表
• 选取适当的join类型
• 避免笛卡尔积:确保join操作关联条件正常
• 不要用*查询列表

常用的sql语句，包括创建表，删表，加锁

• 数据定义语言DDL：CREATE,DROP,ALTER
• 数据查询语言DQL：SELECT
• 数据操纵语言DML（Data Manipulation Language）：INSERT,UPDATE,DELETE
• 数据控制功能DCL（Data Control Language）：GRANT,REVOKE,COMMIT,ROLLBACK

delete、truncate、drop的区别

• delete：delete属于数据库操纵语言DML，表示删除表中的部分或全部数据。delete可以带有where子句指定删除的条件，也可以不带where子句删除表中的所有数据。delete每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行回滚操作。delete删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。delete操作以后可以使用optimize table table_name命令来释放磁盘空间。
• drop：drop属于数据库定义语言DDL，表示删除整张表，包括表中的数据和结构，以及与之相关联的索引、约束、触发器等。drop执行后会自动提交，无法回滚。drop语句将删除表的结构被依赖的约束 (constrain)、触发器 (trigger)、索引 (index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 。
• truncate：truncate也属于数据库定义语言DDL，表示删除表中的所有数据，但保留表结构和属性。truncate不走事务，原数据不放到rollback segment中，操作不触发trigger。truncate执行后会自动提交，无法回滚。truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。truncate能够快速清空一个表，并且重置auto_increment。

视图

MySQL的视图是一种虚拟的表，它的内容由一个查询语句定义，而不是实际存储在数据库中。视图包含一系列带有名称的列和行数据，但是这些数据是从视图所引用的基本表中动态生成的。使用视图可以有以下优势和缺点：

• 优势：
  1视图可以简化复杂的查询，使用户只需使用视图名就可以获取所需的数据，而不必每次都编写完整的查询语句。
  2视图可以提高数据的安全性，通过视图，用户只能访问和修改指定的数据，而不能接触到基本表中的其他敏感信息。
  3视图可以增加数据的逻辑独立性，视图可以屏蔽基本表结构变化对用户的影响，只要视图定义中所涉及的列仍然存在，用户就不需要修改对视图的操作。
• 缺点：
  1视图不占用物理空间，但是每次使用视图时都需要重新执行查询语句，这会增加数据库的负担和响应时间。
  2视图不能添加索引，因此在视图上进行查询可能会比在基本表上进行查询效率低。
  3 视图不一定是可更新的，如果视图涉及多个表或者聚合函数等复杂操作，那么对视图的插入、删除或修改可能会失败或者导致数据不一致。

CREATE VIEW 视图名 AS SELECT 语句;

游标

• MySQL的游标是一种能够从一个查询语句返回的结果集中逐行提取数据的机制。
• 可以提高数据的安全性和一致性，通过游标，我们可以控制对表中数据的访问和修改，避免并发操作导致的冲突或错误。
• 游标可以实现面向过程的编程风格，通过游标，我们可以在存储过程或函数中使用循环、条件判断、变量等控制结构，实现更复杂的业务逻辑。

– 声明一个变量用来存储学生姓名 
DECLARE student_name VARCHAR(20); 
– 声明一个游标用来从student表中逐行提取数据 
DECLARE cur CURSOR FOR SELECT name FROM student;– 打开游标 OPEN cur; – 循环从游标中读取数据 LOOP – 获取游标中的一行数据，并赋值给变量 FETCH cur INTO student_name; – 如果游标已经到达结果集的末尾，退出循环 IF done THEN LEAVE LOOP; END IF; – 对student表中的对应学生的成绩加上10分 UPDATE student SET score = score + 10 WHERE name = student_name; END LOOP; – 关闭游标 CLOSE cur;

mysql慢查询

超过 long_query_time 参数设定的时间阈值（默认10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。使用EXPLAIN命令加上SQL语句，查看SQL的执行计划。有的时候已经命中了索引，但由于关联的表比较多，表数据量大，需要返回的数据多等因素，这时候就可以考虑从业务和架构层面去优化。

分别从索引，sql语句，数据库结构优化，优化器优化和架构优化来介绍

索引：尽量覆盖索引，组合索引符合最左配原则，避免索引失效，在读多写少的情况下，可以选择普通索引而不需要唯一索引，因为普通索引可以使用change buffer进行优化，减少磁盘IO。

sql语句 ：尽量少 join，尽量少排序，尽量避免 select *，尽量少 or，尽量用 union all 代替 union

数据库结构优化：将字段多的表分解成多个表，对于经常联合查询的表，可以建立中间表

优化器优化：优化器使用MRR(Multi-Range Read)将ID或键值读取到buffer排序，通过把随机磁盘读，转化为顺序磁盘读，减少磁盘IO，从而提高索引查询的性能。对于Innodb来说，会按照聚簇索引键值排好序，再顺序读取聚簇索引。

架构优化：读/写分离，主库写，从库读。

binlog

事务

什么是事务

事务是应用程序总一系列操作，所有操作必须成功，否则每个操作中所有做的更改会被撤销。事务只有两种结束方式，一个事务所有步骤全部完成，事务提交；第二种就是有其中一个步骤失败，发生回滚。
事务的目的是是实现可靠性和并发处理
可靠性：当数据库insert或者update操作时，抛出异常或者数据库崩毁时，保证数据库操作前后一致，undo log和redo log
并发处理：当多个并发请求过来，其中一个请求时数据修改操作时，为了避免读到脏数据，需要对事务之间进行读写隔离，需要用到MYSQL事务隔离。

show variables like 'transaction_isolation';
begin;
select * from xxx; 
commit; -- 或者 rollback;

redo log 和 undo log

• redo log ： 重做日志，用来实现事务的持久化。由重做日志缓冲（内存）以及重做日志文件（磁盘）组成。一般情况下，mysql会把每次修改先存在缓冲池中，后台线程去做缓冲池和磁盘同步。到那时同步时宕机了，就可以用redo log来记录已成功提交事务的修改信息。
• undo log：回滚日志，用于记录事务修改之前版本的数据信息。

MVCC

InnoDB的MVCC，通过在记录的后面保存行的创建系统版本号，和行的过期时间版本号实现的。首先用undo log记录某行数据的多个版本的数据，然后用读写锁来判别数据的可见性。

事务四大特性

• 原子性（undo log)
• 持久性(redo log)：redo log是顺序存储，缓存同步是随机操作。同时缓存同步是按数据页为单位的，每次传输的数据大小大于redo log。
• 隔离性(读写锁+MVCC）
• 一致性（前3个共同实现）

四种隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

1读未提交（READ UNCOMMITTED）

set global transaction isolation level read uncommitted;

事务中的修改还没提交，对其他事务时可见的，造成脏读。
设置完成后，只对之后新起的 session 才起作用，对已经启动 session 无效。如果用 shell 客户端那就要重新连接 MySQL，如果用 Navicat 那就要创建新的查询窗口。

启动两个事务，分别为事务A和事务B，在事务A中使用 update 语句，修改 age 的值为10，初始是1 ，在执行完 update 语句之后，在事务B中查询 user 表，会看到 age 的值已经是 10 了，这时候事务A还没有提交，而此时事务B有可能拿着已经修改过的 age=10 去进行其他操作了。在事务B进行操作的过程中，很有可能事务A由于某些原因，进行了事务回滚操作，那其实事务B得到的就是脏数据了，拿着脏数据去进行其他的计算，那结果肯定也是有问题的。

2 读提交 （READ COMMITTED）

set global transaction isolation level read committed;

其他事务只能读到已提交修改变化，使用排他锁，实现读写分离机制。
之后需要重新打开新的 session 窗口，也就是新的 shell 窗口才可以。

同样开启事务A和事务B两个事务，在事务A中使用 update 语句将 id=1 的记录行 age 字段改为 10。此时，在事务B中使用 select 语句进行查询，我们发现在事务A提交之前，事务B中查询到的记录 age 一直是1，直到事务A提交，此时在事务B中 select 查询，发现 age 的值已经是 10 了。

这就出现了一个问题，在同一事务中(本例中的事务B)，事务的不同时刻同样的查询条件，查询出来的记录内容是不一样的，事务A的提交影响了事务B的查询结果，这就是不可重复读，也就是读提交隔离级别。

3可重复读 （REPEATABLE READ）

set global transaction isolation level repeatable read;

在一个事务内的多次读取的结果是一样的。mysql可以用读写锁或者MVCC实现。在使用读写锁时，只要没释放锁，都是读到第一次数据。同时用mvcc时，读取只生成一个版本。
在这个隔离级别下，启动两个事务，两个事务同时开启。首先看一下可重复读的效果，事务A启动后修改了数据，并且在事务B之前提交，事务B在事务开始和事务A提交之后两个时间节点都读取的数据相同，已经可以看出可重复读的效果。可重复读做到了，这只是针对已有行的更改操作有效，但是对于新插入的行记录，就没这么幸运了，幻读就这么产生了。我们看一下这个过程：事务A开始后，执行 update 操作，将 age = 1 的记录的 name 改为“风筝2号”；事务B开始后，在事务执行完 update 后，执行 insert 操作，插入记录 age =1，name = 古时的风筝，这和事务A修改的那条记录值相同，然后提交。事务B提交后，事务A中执行 select，查询 age=1 的数据，这时，会发现多了一行，并且发现还有一条 name = 古时的风筝，age = 1 的记录，这其实就是事务B刚刚插入的，这就是幻读。

4串行化 （SERIALIZABLE）

串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

四种读取错误

1脏读

指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

2可重复读

指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据**更新（UPDATE）**操作。

3不可重复读

指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据**更新（UPDATE）**操作。

4幻读

是针对数据**插入（INSERT）**操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

索引

索引类型

索引优点：
可以提高数据的搜索速度；如果使用分组及排序时，通过索引可以有效减少分组和排序时间
索引缺点：
创建索引占用存储空间，在修改表中的数据时，索引还需要进行动态的维护

• 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引
• 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
• 普通索引：没有唯一性的限制，允许NULL值，结构以B+数和哈希索引未主
• 全文索引：搜索数据表中的字段是不是包含我们搜索的关键字。
• 联合索引：将多个列值组合一起作为索引键。
• 聚簇索引：按照每张表的主键构建一颗B+树，同时叶子节点存放整张表的行记录数据。聚簇索引，数据访问快，范围查找快，但是二次索引需要两次索引查找，第一次找主键，第二次根据主键找行数据。
• 非聚簇索引：非聚簇索引只存储索引字段和记录所在的位置，通过索引位置在获取记录。
• b树索引：在进行范围查询时需要做局部的中旬遍历，同时b树非叶子节点也存放了数据记录的地址，树的层数变高。
• hash索引：不支持范围查询且不支持联合索引的最左配原则。

sql索引失效&优化

• 查询条件中带有or,除非所有的查询条件都建有索引，否则索引失效
• like查询是以%开头
• 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引
• 索引列上参与计算会导致索引失效
• 违背最左匹配原则
• 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描
• 多表关联查询时，小表在前，大表在后。

B树和B+树的区别

B树和B+树都是一种多路搜索树，其中B+树是B树的一种变体。B树和B+树的主要区别在于叶子节点的结构和存储方式。

B树的每个节点都包含key和value，其中key是有序的，而value则是指向数据记录的指针。B树的每个节点可以包含多个key和value，这样可以减少磁盘I/O次数，提高查询效率。

而B+树只有叶子节点包含key和value，而且叶子节点之间使用指针连接起来，形成一个链表。这样可以提高范围查询的效率。

因此，B+树更适合用于范围查询和排序操作，而B树更适合用于随机查找操作。

InnoDB ns MyISAM

• InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

• InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败。

• InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

  • 那么为什么InnoDB没有了这个变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS

• Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

• MyISAM表格可以被压缩后进行查询操作

• InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

  • InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

  • t_user(uid, uname, age, sex) innodb;uid PK无其他索引update t_user set age=10 where uid=1;             命中索引，行锁。update t_user set age=10 where uid != 1;           未命中索引，表锁。update t_user set age=10 where name='chackca';    无索引，表锁。
    

• InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

唯一索引：唯一索引不允许两行具有相同的索引值 ALTER TABLE table_name ADD UNIQUE ( column )
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空 ALTER TABLE table_name ADD PRIMARY KEY ( column )

如何选择

  1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

为什么读查询多的表要使用MyISAM而不是InnoDB

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。而InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值。因此，如果你的应用程序中有大量查询操作而很少更新操作，那么使用MyISAM可能会更快一些。但是，如果你的应用程序中有大量更新操作而很少查询操作，那么使用InnoDB可能会更好一些。这是因为InnoDB支持行级锁定，而MyISAM只支持表级锁定。行级锁定可以提高并发性能，因为它允许多个用户同时访问同一表中的不同行。但是，如果你的应用程序中有大量更新操作而很少查询操作，则行级锁定可能会导致性能下降。

InnoDB为什么推荐使用自增ID作为主键？

答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

为什么MyISAM恢复速度慢

MyISAM和InnoDB在系统奔溃后的恢复速度方面有所不同。MyISAM的恢复速度相对较慢，因为它需要执行一些额外的操作，例如检查表是否损坏，然后进行修复。而InnoDB的恢复速度相对较快，因为它具有事务日志和重做日志，可以在系统奔溃后快速恢复。

但是，InnoDB的恢复速度也会受到影响。如果InnoDB的缓冲池中没有足够的空间来存储所有数据页，则需要从磁盘读取数据页。这会导致恢复速度变慢。事务日志和重做日志是InnoDB存储引擎的两个重要组成部分。事务日志记录了所有对InnoDB表的修改，而重做日志记录了所有对InnoDB表的修改，以便在系统奔溃后可以恢复。事务日志是一个二进制文件，其中包含了所有对InnoDB表的修改。当事务提交时，InnoDB会将事务日志中的内容写入磁盘。如果系统奔溃，则可以使用事务日志来恢复数据。重做日志是一个循环的二进制文件，其中包含了所有对InnoDB表的修改。当事务提交时，InnoDB会将重做日志中的内容写入磁盘。如果系统奔溃，则可以使用重做日志来恢复数据。

mysql有哪些锁

乐观锁 vs 悲观锁

• 乐观锁假设数据一般情况下不会造成冲突，所以在数据提交更新的时候，才会正式对数据冲突进行检测。可以用于读多写少的情况。
  一般使用数据版本记录机制实现，在数据库表增加一个”version“字段。当读取数据时，将version字段值读出，数据每更新一次，version值加一。
• 悲观锁则认为每次拿数据的时候，别人都会修改，在整个数据处理过程中，将数据处于锁定状态。共享锁和排他锁也是悲观锁的不同实现。悲观所适合并发量不大，写入操作频繁的场景。
  关闭mysql的自动提交,set autocommit = 0。

全局锁 vs 表级锁 vs页级锁 vs 行级锁

• 全局锁:对整个数据库实例加锁，flush tables with read lock。常用于全库逻辑备份的情况。
• 表级锁:对当前操作的整张表加锁，使用lock tables … read/write。
• 页级锁:锁定粒度介于行级锁和表级锁之间
• 行级锁:粒度最低，发生锁冲突概率低，并发度高，但是容易发生死锁。在mysql中，行级锁锁的是索引。如果一条sql操作主键索引，mysql就会锁定主键索引；如果一条语句操作非主键索引，mysql会先锁该非主键索引，再锁相关主键索引。

共享锁 vs 排他锁

• 共享锁：当事务A对数据加上读锁后，其他事务只能对该数据加读锁，不能添加写锁。select … lock in share mode。共享锁主要为了支持并发读取数据，出现不可重复读的问题。
• 排他锁：当事务对数据加上写锁后，其他事务即不能对该数据添加读写，也不能对数据添加写锁。select … for update。写锁为了解决修改数据引发的脏读的问题。

意向共享锁 vs 意向排他锁

意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度的锁并存。当事务A有行锁时，mysql会自动为该表添加意向锁，事务B想申请整个表的写锁，就不用每一行判断行锁，可以直接查看意向锁。

间隙锁 vs 临键锁 vs 记录锁

• 记录锁：是行锁，封锁一条记录

select * from goods where ** 'id' = 1 for update

• 间隙锁：基于非唯一索引，可以锁定一定范围内的索引记录。
• 临键锁：是记录锁和间歇锁的组合，它封锁的范围包括索引记录，索引区间，为了避免幻读。

加锁优化

• 使用InnoDB存储引擎，它支持行级锁和事务，可以有效地降低锁的粒度和范围，提高并发访问能力。
• 使用索引，特别是在查询和更新操作中，可以减少扫描的数据量，缩短锁的持有时间，避免表锁升级为行锁。
• 控制事务的大小，避免长事务，因为事务越长，持有的锁越多，锁的等待时间越长，死锁的可能性越大。
• 避免使用锁定读语句（select … lock in share mode 或 select … for update），除非有特殊的业务需求。因为这些语句会给数据行加上共享锁或排他锁，影响其他事务的读写操作。
• 避免在一个事务中操作多个表或多个范围，因为这样会增加锁的复杂度和数量，增加死锁的风险。
• 避免在高并发场景下使用间隙锁和临键锁，因为这些锁会阻止其他事务在索引记录之间插入新记录，影响并发插入性能。
• 根据业务特点和场景，合理地调整一些系统参数，如innodb_lock_wait_timeout（控制事务等待获取资源的最长时间）、concurrent_insert（控制MyISAM表的并发插入行为）、low_priority_updates（控制读写操作的优先级）等。

题目

连续多少天登录

--连续3天登录
select distinct a.user_id
from user_login a
join user_login b on a.user_id = b.user_id and datediff(b.login_date, a.login_date) = 1
join user_login c on a.user_id = c.user_id and datediff(c.login_date, b.login_date) = 1;--连续登录
select name,max(activedays) as 最大连续登录天数
from 
(select name,value,count(1) as activedays from (select name,active_date,row_number() over(partition by name order by active_date) as rank,datediff(day,install_date,active_date) as diff,diff-rank as valuefrom user_active) group by 1,2
) group by 1
order by 1

同比环比

--同比
SELECT  CONCAT(e,'-',a) 年月, b 月销售额, 
CASE WHEN d>0 THEN CONCAT((b-d)/d*100,'％') -- 转化为百分比，此处考虑到销售额为0的情况，分母不能为0，用case进行了条件判断ELSE "同期没有数据" END 同比  -- 这边的文字可以 换掉FROM 
(SELECT YEAR(s.sail_time) f , MONTH(s.sail_time) c , SUM(s.number*p.pro_price) d FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2019 GROUP BY MONTH(s.sail_time)) s2 
RIGHT JOIN 
(SELECT YEAR(s.sail_time) e，MONTH(s.sail_time) a , SUM(s.number*p.pro_price) b  FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1 
ON s1.a=s2.c --环比，注意要连接前年12月份
SELECT  
CONCAT(e,'-',a) 年月,f 月销售额, CASE WHEN h>0 THEN CONCAT((b-h)/h*100,'％')ELSE "上期没有数据" END 环比
FROM 
(SELECT YEAR(s.sail_time) e,MONTH(s.sail_time) a , SUM(s.number*p.pro_price) h  FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1 
LEFT JOIN 
(SELECT YEAR(s.sail_time) f, MONTH(s.sail_time) g, SUM(s.number*p.pro_price) b FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id GROUP BY MONTH(s.sail_time),YEAR(s.sail_time)) s3 
ON ((s1.a-1)=s3.g AND s1.e=s3.f) OR (s1.a=1 AND s3.g=12 AND s3.f=2019)

行转列

select VendorId ,
sum(case when  IncomeDay='MoN' then IncomeAmount else 0 end) MON,
sum(case when  IncomeDay='TUE' then IncomeAmount else 0 end) TUE,
sum(case when  IncomeDay='WED' then IncomeAmount else 0 end) WED,
sum(case when  IncomeDay='THU' then IncomeAmount else 0 end) THU,
sum(case when  IncomeDay='FRI' then IncomeAmount else 0 end) FRI,
sum(case when  IncomeDay='SAT' then IncomeAmount else 0 end) SAT,
sum(case when  IncomeDay='SUN' then IncomeAmount else 0 end) SUN
from DailyIncome group by VendorId

用PIVOT

select * from DailyIncome ----第一步
pivot 
(
sum (IncomeAmount) ----第三步
for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步
) as AvgIncomePerDay

列转行

SELECTNAME,'语文' AS subject ,MAX("语文") AS score
FROM student1 GROUP BY NAME
UNION
SELECTNAME,'数学' AS subject ,MAX("数学") AS score
FROM student1 GROUP BY NAME
UNION
SELECTNAME,'英语' AS subject ,MAX("英语") AS score
FROM student1 GROUP BY NAME

用UNPIVOT

SELECT *
FROM student1
UNPIVOT (score FOR subject IN ("语文","数学","英语")
)