mysql口径制定_面试准备_mysql

前面写过一篇sql相关的，更多是平常用的语句，现整理一篇体系的文章，但主要还是偏数据分析方向，许多DBA范畴的工作并不涉及(如事务处理、权限管控、分区分表分库)，欢迎指教。

一、基础理论

一次查询所发生的底层流程：

不同存储引擎具备不同的适用性， InnoDB(默认，支持事务、更新行级锁定、外键)、MyISAM(不支持事务，更新表级锁定，高并发受限)、Memory、NDB，具体差异可查询官方文档。

数据类型主要包括：整数类型、浮点数类型、字符串类型、日期类型、其他。

二、主要模块阐述

1、数据查询

日常使用包括过滤查询、排序查询(row_number、order by)、数据连接与聚合(维度和指标)、落表(包括视图)。

CROSS JOIN会产生AB表的笛卡尔积。多条件关联时，如A表左关联B表，多条件写在on(如product.amount=200)，会保留A表全部字段，但未满足条件的记录的字段为NULL，where则只返回符合查询条件的记录。

2、函数

包括数学函数、字符串函数(含正则表达式)、聚合函数、时间和日期函数。

3、事务处理

某些特性了解后，更有利于了解数据的产生，方便进行异常的处理。部分异常可使用MVCC(MultiVersion Concurrency Control)数据多版本并发控制解决。

更新丢失(Lost Update)： 事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题。

脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读(Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交(修改)，导致事务A多次读取同一数据时，结果不一致。

幻读(Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入(或删除)了一些数据。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

三、业务实践

不要并行维护多个版本的数据(维护成本巨大)，当要做数据变更时，一方面可以降低变更的频率，另一方面尽量在原有报表里修改，并替换掉原有口径。

脚本要简洁清晰，拆解为不同的代码块执行，方便后期维护理解、代码调试、结果验证。

考虑未来的可扩展性与鲁棒性，可增加一些基本的约束条件，数据规划可采用总-分结构(原始基础数据应尽量保留)。

四、疑点与难点

1、横纵表转换

纵表变横表-- 也可以使用pivot 函数。

select 姓名,

sum (case 课程 when '语文' then 成绩 else 0 end) as 语文,

sum (case 课程 when '数学' then 成绩 else 0 end) as 数学,

sum (case 课程 when '英语' then 成绩 else 0 end) as 英语

from Table_A

group by 姓名

纵表变横表# 也可以使用unpivot 函数。

select 姓名,'语文' as 课程,语文 as 成绩 from Table_B union all

select 姓名,'数学' as 课程,数学 as 成绩 from Table_B union all

select 姓名,'英语' as 课程,英语 as 成绩 from Table_B

order by 姓名,课程 desc

2、索引

索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 ，数据库还维护一个满足特定查找算法的数据结构(此数据结构即索引)，一般默认索引类型为B+树(多路搜索树)。

索引可以大幅提高查询数据，但会对表的更新速度造成一定的影响。对于DBA工程师来说，根据具体的业务场景，选择不同的索引类型，是一门学问。

3、IN的用法、与EXITST的区别

in查询相当于多个or条件的叠加。exists对外表用loop逐条查询，Exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false。SELECT * FROM A WHERE A.id IN (SELECT id FROM B);

SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

如A、B表大小差不多，则差别不大；一大一小，则子查询表大的用exists，子查询表小的用in。

SQL是三值逻辑(还有UNKNOWN，1==NULL产生)，not in(0,1)等价于：!= 0 AND esex != 1，因此在使用in，特别是包含了子查询时，可能会导致查询的结果与预期不一致(返回空集)。

4、性能分析

常见分析方法有：慢查询日志，EXPLAIN 分析查询，profiling分析、show命令查询系统状态及系统变量。Mysql> show status ——显示状态信息(扩展show status like ‘XXX’)

Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’)

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

慢查询日志(默认未开启)记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time(默认为10s)值的 SQL，则会被记录到慢查询日志中，通过日志分析工具mysqldumpslow分析。

Explain + SQL语句，可知道表的读取顺序，可以使用的索引及实际使用的索引等，最重要的是type字段，如为ALL代表着全表扫描；possible_keys表示可用的索引，key表示实际使用的索引；rows表示找到查询记录大致需要读取的行数，id值越大优先级越高，越先被执行，id相同时则按照从上往下的顺序执行。

一般SQL的优化建议，不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效；永远小表驱动大表；order by子句，尽量使用 Index 方式排序；group by实质是先排序后进行分组，遵照索引建的最佳左前缀；where高于having，优先使用；数据存储类型尽量使用简单、更小的存储类型；数据存储列尽量避免NULL。

附，参考资料：

1、腾讯高级数据分析师的 SQL 经验总结，https://zhuanlan.zhihu.com/p/150016391

2、MySQL 三万字精华总结，https://zhuanlan.zhihu.com/p/162048852