​
数据库表分区是怎么回事？
大家好，这一期呢，我们来说一下，数据库表和索引的分区。讲解这个问题，对于不同的数据库可能有一些技术细节上的不同，因此我们以某个数据库比如sql server为例来探讨这个问题。
分区后的数据和索引分散到多个文件组里面，因此可以说这些数据是平行并列分布的，每组中的行都对应特定的那个分区。这些索引和表的分区都必须存在一个数据库里面。因此，在外部程序看来，不管内部有多少个分区，在外面看来就是一个数据表或者索引。
那么表或者索引的分区有什么好处呢？好处主要是两个方面，一个是管理起来比较容易，另一个方面是性能上更好一些。
相较于访问整表而言，访问某个分区的数据当然更快更有效率。
我们设想一下有这么一个案例，有一个表它有多个分区，这些分区对应的都是不同的文件组，而这些文件组呢又会分散到不同的硬盘上。
如果我们在排序的时候，一个硬盘一个硬盘的去访问的话，性能上就会很低。
要改善性能的话，我们可以使用RAID对分散到多个硬盘上的数据文件，同时进行访问。
再来说一下数据加锁的情况。在有多个分区的情况下，我们只需要对某个分区进行加锁，而不需要对整张表进行加锁，这样也可以提高操作的效率。这要在创建表的时候，修改表的属性，把lock_escalation这个选项设置为Auto。
下面是数据分区的几个重要概念。
首先是分区函数。分区函数用来定义如何进行分区。首先它定义了我们需要分多少个区。这些区的边界在哪里。打个比方说, 一张用户表中包含了用户的注册时间，注册地域等等信息。那我们可以以时间为参考创建分区。可以以月为单位或者以年为单位进行分区，这主要取决于你到底有多大的数据量。在这种情况下，时间，也就是某个月的起始与终止，或者某个年的起始与终止，作为数据边界的参考。
其次是分区列。分区列会被上面的分区函数拿来用作进行表和索引的分区。这个列必须被明确的标注为persisted。 理论上讲所有可以用作索引定义的列都可以当做分区列。
再次就是对应的索引。索引和原始表的分区函数，必须满足如下条件， 分区函数的参数必须具有相同的数据类型，他们必须具有相同数量的分区，他们必须具有相同的数据边界。
通俗的讲，数据进行了分区，索引的分区必须跟数据的分区进行对应，这样才可以提高效率。
对簇索引进行分区。当簇索引的键值并非唯一的时候，簇索引的键值并不需要指定包含分区列，在这种情况下，sql server 会缺省的把分区列添加到簇索引的键值中。如果簇索引的键值是唯一的，你必须显性的指定簇索引的键值包含分区列。
对非簇索引进行分区。当对于一个键值唯一的非簇索引进行分区时，索引键值必须包含分区列。当索引值不唯一时，数据库会缺省的让索引键值包含分区列。
接下来说一下非对应索引的情况。这种索引主要是独立于相关的数据表之外。主要是两种情况，一是这种索引有独立的分区定义，二是这种索引被放置在一个单独的文件组中。这种索引在如下情况下非常有用:
. 数据源表没有分区，
. 索引键是唯一的，并且不包含分区列
. 你会在数据源表上进行非常复杂的多表联合操作。
在性能的考量上。并不是说分区越多，性能就越好。这些分区都会影响到内存的消耗，CPU的繁忙程度。所以你在使用分区的时候，要找到你的平衡点。
具体的参数参考可以查找对应数据库的开发文档。
以上是我对这个话题一点心得看法。仅供参考，欢迎讨论, 欢迎拍砖。

之前做的查询功能今天被发现你速度缓慢 于是上网搜索解决的办法,但是网上的大佬们都是提到了优化数据库设计方面的办法,但是作为一个实习生怎么敢轻易改动数据库 于是就自己花了一个下午在改一条sql语句,最后总结出来了一点经验吧!

条件先行

在查询的时候大部分都是多表联查吧,单查一个表的情况很少 ,在俩个表以及俩个表以上联查的时候肯定会有一个主表(大部分条件都是在个表),其他的表基本就是一个条件等于一下 查一个字段 问题就在这里

谨记:查询是尽量嵌套select(最笨的办法),在最内层的select里面只查数据最多的那个表,而且该层select的from后尽量只出现一个表(主表),需要用到其他表的时候再去嵌套select(外层),把内层表查到的数据放在外层表的from关键子后 作为一个新表来查

我感觉自己没有说清楚 那就举个例子吧:
优化前:

SELECT a.a , a.b , a.c , b.a FROM biaoOne a , bioaTwo b WHERE a.d = ‘’ AND a.e LIKE ‘%%’ AND a.f = b.a AND a.g = b.b
优化后:

SELECT t.* , tt.a FROM

(SELECT a.a , a.b , a.c FROM biaoOne a WHERE a.d = ‘’ AND a.e LIKE ‘%%’) t  , bioatwo tt

WHERE t.f = tt.a AND t.g = tt.b
大致就是这个意思,优化后的速度比优化前快很多 尤其在数据量很大的情况下

工作中我们经常用到多个left join去关联其他表查询结果，但是随着数据量的增加，一个表的数据达到百万级别后，这种普通的left join查询将非常的耗时。
举个例子：
现在porder表有 1000W数据，其他关联的表数据都很少，因为条件的限制 必须要关联3个表，正常的逻辑就是这样写，但是我们在数据库执行的时候会发现这样的SQL 非常耗时，
而且此时才 limit 800  这样的SQL怎么能让用户受得了呢？
select p.*,b.supplier,t.type,c.org   from porder p
left JOIN brand b on p.supplier = b.supplier_id and b.mark = 0
left JOIN purchase c on p.org = c.id and c.mark = 0
left JOIN type t on c.category = t.type_id and t.mark = 0
WHERE p.nark = 0 ORDER BY p.id desc limit 800,500;
通过查询SQL优化方面的知识，发现一种比较好的优化方案:
select p.*,b.supplier,t.type,c.org from
(select po.id from porder po where po.mark = 0 order by po.id desc limit 800000,500) a
inner join porder p on a.id = p.id and p.mark = 0
left JOIN brand b on p.supplier = b.supplier_id and b.mark = 0
left JOIN purchase c on p.org = c.id and c.mark = 0
left JOIN type t on c.category = t.type_id and t.mark = 0;
我们可以先将数据量最大表的满足条件的ID查询出来，创建临时表，再用这个临时表去关联这个表本身以及其他表。limit80W 也就1S时间。
SQL分析：
我们可以使用 explain 查看上面2种SQL的执行计划。第一种SQL的执行计划中 通过 row 和extra 都可以看出 非常差，row几乎为全部扫描。
优化后的SQL通过 row 和extra 都可以看出都是很好的状态，row的数据是第一种的 1%。相当于提升了 100倍。
执行计划中的id列的数值越大，执行权就越高。id列的值相等的，就从上之下依次执行。明白了这一点，我们就可以再分析SQL了。
数据库先执行了 select po.id from porder po where po.mark = 0 order by po.id desc limit 800000,500 这段SQL，将查询出的有效id(满足条件的id)放在了临时表a中，
然后表a 再与其他的表匹配查询。
(注：优先执行的SQL 不参与 后面的表匹配。这里要理解，不然单独看执行计划，你会纳闷为何row列上 a表 中数值小，而 id列为2的表(po) row列的数值也很大。
你也可以拆分SQL。优先执行的SQL 单独拿出来执行，将查询到的结果当作查询条件，传给普通的 left join 中的where条件里面 即 in()， in的里面不要写SQL查询，必须是明确的数值！)
我只是提供方法，具体的原理，大家可以上网查一查。数据库有一种叫 驱动表的概念，大家可以了解下。或许对于理解这种方法更方便！
注：这个优化后的SQL在执行 limit1000000，**  的时候效率也就下降了，大概4S钟以上。所以这个SQL也是有极限的，对于分页查询等等，如果数据量超过100W 要注意！
希望有大神，能在SQL上能有更高的突破，有方法的，希望大家一起分享，一起学习。谢谢~
补：为了应对超过百万级别的查询，或者导出，SQL优化暂时没有好的办法，但是我们可以在传参上做文章。
比如分页查询时，每页展示20条数据，首页查询时，我们可以得到首页最后一条数据的ID (起名：lastId)(按ID排序，降序)，当点击第2页时，我们可以将 lastId 作为参数传入分页查询的SQL中。
这样分页时就加上了一个条件 就是ID
且扫描到满足条件的20条后，就不会再多扫描，大大减少了扫描的数据量，自然也就提升了效率。

工作中我们经常用到多个left join去关联其他表查询结果，但是随着数据量的增加，一个表的数据达到百万级别后，这种普通的left join查询将非常的耗时。
举个例子：
现在porder表有 1000W数据，其他关联的表数据都很少，因为条件的限制 必须要关联3个表，正常的逻辑就是这样写，但是我们在数据库执行的时候会发现这样的SQL 非常耗时，
而且此时才 limit 800  这样的SQL怎么能让用户受得了呢？
select p.*,b.supplier,t.type,c.org   from porder p
left JOIN brand b on p.supplier = b.supplier_id and b.mark = 0
left JOIN purchase c on p.org = c.id and c.mark = 0
left JOIN type t on c.category = t.type_id and t.mark = 0
WHERE p.nark = 0 ORDER BY p.id desc limit 800,500;
通过查询SQL优化方面的知识，发现一种比较好的优化方案:
select p.*,b.supplier,t.type,c.org from
(select po.id from porder po where po.mark = 0 order by po.id desc limit 800000,500) a
inner join porder p on a.id = p.id and p.mark = 0
left JOIN brand b on p.supplier = b.supplier_id and b.mark = 0
left JOIN purchase c on p.org = c.id and c.mark = 0
left JOIN type t on c.category = t.type_id and t.mark = 0;
我们可以先将数据量最大表的满足条件的ID查询出来，创建临时表，再用这个临时表去关联这个表本身以及其他表。limit80W 也就1S时间。
SQL分析：
我们可以使用 explain 查看上面2种SQL的执行计划。第一种SQL的执行计划中 通过 row 和extra 都可以看出 非常差，row几乎为全部扫描。
优化后的SQL通过 row 和extra 都可以看出都是很好的状态，row的数据是第一种的 1%。相当于提升了 100倍。
执行计划中的id列的数值越大，执行权就越高。id列的值相等的，就从上之下依次执行。明白了这一点，我们就可以再分析SQL了。
数据库先执行了 select po.id from porder po where po.mark = 0 order by po.id desc limit 800000,500 这段SQL，将查询出的有效id(满足条件的id)放在了临时表a中，
然后表a 再与其他的表匹配查询。
(注：优先执行的SQL 不参与 后面的表匹配。这里要理解，不然单独看执行计划，你会纳闷为何row列上 a表 中数值小，而 id列为2的表(po) row列的数值也很大。
你也可以拆分SQL。优先执行的SQL 单独拿出来执行，将查询到的结果当作查询条件，传给普通的 left join 中的where条件里面 即 in()， in的里面不要写SQL查询，必须是明确的数值！)
我只是提供方法，具体的原理，大家可以上网查一查。数据库有一种叫 驱动表的概念，大家可以了解下。或许对于理解这种方法更方便！
注：这个优化后的SQL在执行 limit1000000，**  的时候效率也就下降了，大概4S钟以上。所以这个SQL也是有极限的，对于分页查询等等，如果数据量超过100W 要注意！
希望有大神，能在SQL上能有更高的突破，有方法的，希望大家一起分享，一起学习。谢谢~
补：为了应对超过百万级别的查询，或者导出，SQL优化暂时没有好的办法，但是我们可以在传参上做文章。
比如分页查询时，每页展示20条数据，首页查询时，我们可以得到首页最后一条数据的ID (起名：lastId)(按ID排序，降序)，当点击第2页时，我们可以将 lastId 作为参数传入分页查询的SQL中。
这样分页时就加上了一个条件 就是ID
且扫描到满足条件的20条后，就不会再多扫描，大大减少了扫描的数据量，自然也就提升了效率。