前面写过一篇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
网友评论