SQL进阶

July 9, 2017

Mysql, SQL

本文不介绍最简单的增删改查，而是对于SQL的进阶语句的介绍，顺便研究一下其内部实现。

Join #

由浅入深，从使用到优化。

概述 #

Join一般会有人称为级联查询，我表示这个名称还算直观。其应用场景有那么几种：

某个表中的某些字段需要另一个表来做精细描述。比如职工表中职工等级需要等级表来进一步详细描述该等级的权限、福利等。当我们需要某个人的信息的时候一定是从职工表拉取记录并且需要再从等级表中拉取具体信息合并呈现。
某个人的全方位信息存放在不同的表中，要想得到完整信息就要多表查询合并结果。

上面两种场景都是涉及了两个及以上的表，这就是join的使用场景特点。

SQL语句 #

select col_a, col_b, ... from tb_1 join tb_2 on tb_1.col_a = tb_2.col_a where ... ;

其最主要的特点就是from的来源发生了变化，所以我们可以理解为这个sql语句生成了一个新的表，并从这个新表中获取了符合where条件的记录。所以这个join我们看作是对表的一种运算，运算的结果是一张新的表。这个join运算有一个on的条件，也就是在这个on的基础上进行运算。

Join 运算一共有四种：

inner join （内连接）
outer join （外连接）
left outer join （左外连接）
right outer join （右外连接）

在讲这四种运算之前先要有一个笛卡尔积的概念。笛卡尔积来源于离散数学中的集合操作，对于两个集合做笛卡尔积就是将两个集合中元素两两组合，生成一个新的集合，新集合中的每一项都是一个二元组(可以理解为一个数据包，并且这个数据包是有序的，总是某一个集合的元素在前，另一个在后)。所以，如果两个集合的size分别是n,m，那么笛卡尔积生成的集合size=n*m; 对于表做笛卡尔积，同理就是将两个表的记录两两组合生成一个巨大的新表，每条记录都是由两个表的记录拼接而成。该概念仅用于理解运算结果的组成，并不是其实现方式。以下的操作可以理解为是在此基础上进行条件筛选，但事实上SQL使用的是循环的方式去查找结果。

inner join #

其结果为：笛卡尔积中，符合on 条件的记录。

outer join #

对于一条A表的记录，如果没有找到符合on条件的对应的B表记录，那么A表的此记录依然出现在结果集中，其对应的B表部分为NULL。 The same to B.

left outer join #

根据outer join 的意思，可想而知，对于A找不到B的情况，保留A的记录，而对于B则滤掉相应记录。

right outer join #

同理。

Tips #

如果对于A的一条记录，有多条B记录对应，那么会以多条记录的形式出现。这一点跟高级语言的思维方式不太一样。高级语言用OOP的思维来想，就是在这个对象的对应成员上挂个链表，有多少条记录就都挂在下面，但是A的这条记录的实例只有一个。

问题 #

join 的内部实现是啥?

分页 #

这个事实上是非常常用的技巧，也非常简单。

select * from TableName limit cnt, pagesize;

其中主要就是这个limit的用法，其支持两个参数，cnt-[偏移量], pagesize-[记录数量]。很简单的计算机思维，起点加长度，当然起点是0。这个地方我还绕了一下，看了一个博客，结果这个博主很奇葩，人家都下标从0开始了，自己却还让记录从1开始记，害我吃了三秒钟的屎。就是你把记录下标从0开始记就好了。

example #

下面这个就是从下标为5的记录开始，查询10条记录。使用的时候只要定一下pagesize，然后接受个page的参数，计算一下偏移量就Over啦。（曾经我还手动撸过，Too Young）

select * from TableName limit 5, 10;

索引 #

索引就是目录。但不仅仅是目录，还是整本书。有几点需要知道：

数据库中的数据是持久化的。
索引就是文件，也是持久化的。
索引比数据本身更大。

震惊吧，我看到1G的数据有2G的索引的时候我也很震惊，然后就有了这篇文章。

索引是干嘛的，为什么有索引，索引的好处。。。这些你都想的到，所以不说了。但是有几个数据你需要知道：

内存速度比硬盘IO快十万倍，1e5。
一次硬盘IO的时间差不多在9ms左右，一台500MIPS的机器可以跑40万条指令。
一个三层B+树索引可以优化百万条的数据查询到3次IO。

索引有哪些？（高频面试问题） #

关于这个问题，如果你回答的是：唯一索引、聚集索引、非聚集索引、主键索引等，一般的开发岗就算答对了。如果是技术实力强的团队，尤其是数据库相关的团队，such as 我所在的阿里中间件存储事业部，这个就算是个菜鸡回答了。如果你眼中的索引是：B+树索引、B-树索引、B树索引、位图索引、Hash索引，你就很有可能要被发offer了。这里就挑个B+树索引来说吧。

不了解的人一定更关心B树和B-树和这个B+树的区别，那就瞎说一下吧。

B树 #

B树就是二叉搜索树啦，一般要用平衡二叉树，不平衡的还有人用? (B树的原名为B-tree，是多路二叉树，被直译为了B-树。乱糟糟的，你懂就好，不必纠结那些东东)

B-树 #

是个多路搜索树，也就是非二叉。其最大设定为M叉，在每个结点中，数据有序排列，且子树之间有序，所以可以直接节点上二分查找。其数据分布在整个树中，也就是说不一定走到叶子就找到数据就返回了。

B+树 #

基本结构与B-树一样，不一样的就是所有的数据都分布在叶子节点上，中间结点只是做索引的功能不保存数据。

其实还有B*树，哈哈，是不是很绝望。这种树在非根和非叶节点还放了一个指向兄弟的指针，在数据膨胀的时候空间利用那个率更高。什么是数据膨胀呢，就是你数据库在变大嘛，你就要不断往索引树里面塞新的数据。这个时候你不断往某个结点里放，结果放满了，但是还有数据要分在这个叉里啊。所以你要把这个结点分裂出子节点，这个结点里把数据放下去，留个指针就好了。对于B+树来说，要求每个节点要至少放满1/2的数据的时候才能分裂;那么对于B*树来说，如果他的节点满了，可以把数据往兄弟节点里放，将分裂水位升到了2/3，所以空间利用率更高。

但是这并不是我真正想说的。。。我们还是来看这个B+树好了。

首先普及一下操作系统数据加载的一个知识：由于我们可以想像，当硬盘上某条数据被加载到内存即将被使用的时候，那么其周围存储的数据有十分有可能就是下一个被用的数据，所以在加载数据的时候都是直接把整个数据页加载到内存里，数据页大小由操作系统决定，一般是4-8K。这个设计之优美就在于利用了这个特性。每个东西被使用都是有原因的！基于这个特性，我们就可以尽可能的减小我们的磁盘IO次数，How to？我们让B+树的一个结点去记录一个磁盘页的信息，这样我们在树上访问一个节点的时候只做一次IO就将节点中的数据提取出来了，然后我们在内存中做二分，定出下一个节点，然后去访问这个结点，同样只要一次IO。这样索引的查找效率就提升到logM(N)了。

但是这只是索引的优美，下面是索引的神奇。

索引查询优化 #

这部分的内容很有生产力，当我还在学校的时候，某个不认识的学长曾经跟我说数据库优化多么重要，一条SQL语句优化就省了好几台服务器的钱。当然他也没能举出例子，说理有些苍白无力啊，那么例子就来了。

select * from TableName where a=1 and b>1 and c=2;
vs
select * from TableName where a=1 and c=2 and b>1;

这是个非常简单的例子，但是很能说明问题。需要补充几点知识，B+树在节点内做匹配的时候很多时候都是多个字段都有索引的，他是按照最左前缀匹配原则来二分的，通俗点讲就是，贪心匹配，当当前条件满足他就往下走，例如当碰到这个b>1, 那就不管你后面c=2的限制了，先去把所有的b>1的结点访问一遍，去那里再搞你这个c=2。所以肯定是下面一个更快啦。

会快多少呢？就看你数据库多大了，越大差的越多。这个简单的优化很容易学啊，也是最基础的必须做的优化。一般=、in之间是可以乱序的，索引会自己调整。

第二个优化就是：尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

关于慢查询的部分，再补充吧。。。