我们先来看一个重要的属性列的 离散度，
count(distinct(column_name)) : count(*) -- 列的全部不同值个数：所有数据行行数
数据行数相同的情况下，分子越大，列的离散度就越高。简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。
当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
create table shop(address varchar(120) not null)
alter table shop add key(address(12)) // 截取12个字符作为前缀索引是最优的吗？
问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度计算公式：
select count(distinct address) / count(*) from shop
select count(distinct left(address, n)) / count(*) as subn from shop
count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大。举个例子，现在有两个address（东大街长兴小区，东大街福乐小区），那么 distinct(address，2) <distinct(address，3）
==>所以，截取的长度越长就会越接近字段在全部数据中的选择度
==>所以，我们要权衡索引大小和查询速度。
举个例子，通过不同长度去计算，与全表的选择性对比：
SELECT COUNT(DISTINCT(address))/COUNT(*) sub, -- 字段在全部数据中的选择度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5, -- 截取前5个字符的选择度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10, -- 截取前10个字符的选择度
COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,
COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,
COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,
COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15
FROM shop
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| sub | sub5 | sub7 | sub9 | sub10 | sub11 | sub12 | sub13 | sub15 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了，而且长度也相较后面更短一些， 综合考虑比较合适。
ALTER TABLE shop ADD KEY (address(11))
1.索引的个数不要过多（浪费空间，更新变慢）
2.在用于 where 判断 order 排序和 join 的（on）字段上创建索引
3.区分度低的字段，例如性别，不要建索引（离散度太低，导致扫描行数过多）
4.更新频繁的值，不要作为主键或者索引（页分裂）
5.不建议用无序的值作为索引，例如身份z、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）
6.若在多个字段都要创建索引的情况下，联合索引优于单值索引
7.联合索引把散列性高（区分度高）的值放在前面
在 Web 应用程序中跨大型数据集分页记录似乎是一个简单的问题，但实际上很难扩展。两种主要的分页策略是偏移/限制和游标。
我们将首先看一下这两种方法，然后稍作修改，可以使偏移/限制非常高效。
偏移/限制分页
偏移/限制方法是迄今为止最常见的方法，它通过跳过一定数量的记录（页）并将结果限制为一页来工作。
例如，假设您的应用程序配置为每页显示 15 条记录。您的 SQL 将如下所示：
这是最常见的，因为它非常简单，易于推理，并且几乎每个框架都支持它。
除了易于实现之外，它还具有页面可直接寻址的优点。例如，如果您想直接导航到第 20 页，您可以这样做，因为该偏移量很容易计算。
但是有一个主要的缺点，它潜伏在数据库处理偏移量的方式中。偏移量告诉数据库放弃从查询中返回的前N个结果。不过数据库仍然要从磁盘上获取这些行。
如果你丢弃的是100条记录，这并不重要，但如果你丢弃的是100,000条记录，数据库就会为了丢弃这些结果而做大量的工作。
在实践中，这意味着第一个页面会快速加载，之后的每一个页面都会变得越来越慢，直到你达到一个点，网络请求可能会直接超时。
基于游标的分页
基于游标的分页弥补了偏移/限制的一些不足，同时引入了一些自己的不足。
基于游标的分页是通过存储一些关于最后呈现给用户的记录的状态，然后根据这个状态来进行下一次查询。
因此，它不是按顺序获取所有的记录并丢弃前N条，而是只获取最后一个位置N之后的记录。
如果按ID排序，SQL可能看起来像这样。
你可能已经看到了其中的好处。因为我们知道上次向用户展示的ID，我们知道下一个页面将以一个更高的ID开始。我们甚至不需要检查ID较低的行，因为我们百分之百肯定地知道那些行不需要被显示。
在上面的例子中，我特别说明了ID可能不是连续的，也就是说，可能有缺失的记录。这使得我们无法计算出哪些记录会出现在某一页面上，你必须跟踪之前那一页面上的最后一条记录是什么。
与偏移/限制分页不同，使用游标分页时，页面不能直接寻址，你只能导航到 "下一页 "或 "上一页"。
不过光标分页的好处是在任何数量的页面上都很迅速。它也很适合无限滚动，在这种情况下，页面首先不需要可以直接寻址。
Laravel文档中有一些关于偏移量和游标之间的权衡的好的背景。
https://laravel.com/docs/pagination cursor -vs-offset-pagination
考虑到所有这些，让我们来看看一个偏移/限制优化，可以使它的性能足以在成千上万的页面上使用。
使用递延join的Offset/Limit
递延连接（deferred join ）是一种技术，它将对要求的列的访问推迟到应用了偏移量和限制之后。
使用这种技术，我们创建一个内部查询，可以用特定的索引进行优化，以获得最大的速度，然后将结果连接到同一个表，以获取完整的行。
它看起来像这样：
这种方法的好处可以根据你的数据集有很大的不同，但是这种方法允许数据库尽可能少地检查数据，以满足用户的意图。
查询中 "昂贵的 "select *部分只在与内部查询相匹配的15条记录上运行。所有数据的Select都被推迟了，因此被称为推迟join。
这种方法不太可能比传统的偏移/限制性能差，尽管它是可能的，所以一定要在你的数据上进行测试!
Laravel实现
我们如何把这一点带到我们最喜欢的网络框架，如Laravel和Rails？
让我们具体看看Laravel，因为我不知道Rails。
感谢Laravel的macroable特性，我们可以扩展Eloquent Query Builder来添加一个新的方法，叫做deferredPaginate。为了保持一致性，我们将模仿常规分页的签名。
我们将尝试做尽可能少的自定义工作，并将大部分工作留给 Laravel。
这是我们要做的：
这应该为我们提供 LaravelLengthAwarePaginator 和延迟连接的所有好处！
一个Github仓库
递延Join和覆盖索引
还没有完成...
使用递延Join的主要好处是减少了数据库必须检索然后丢弃的数据量。我们可以通过帮助数据库获得它需要的数据而更进一步，而无需获取底层行。
这样做的方法称为“覆盖索引covering index”，它是确保快速偏移/限制分页的最终解决方案。
覆盖索引是一个索引，在这个索引中，查询的所有需要的字段都包含在索引本身中。当一个查询的所有部分都能被一个索引 "覆盖 "时，数据库根本不需要读取该行，它可以从索引中获得它需要的一切。
请注意，覆盖索引并不是以任何特殊方式创建的。它只是指一个索引满足了一个查询所需要的一切的情况。一个查询上的覆盖索引很可能不是另一个查询上的覆盖索引。
在接下来的几个例子中，我们将使用这个基本的表，我把它填满了~1000万条记录。
让我们看一个仅select索引列的简单查询。在这种情况下，我们将从email表中进行select contacts。
在这种情况下，数据库根本不需要读取基础行。在MySQL中，我们可以通过运行一个解释并查看额外的列来验证这一点：
extra: using index告诉我们，MySQL能够只使用索引来满足整个查询，而不看基础行。
如果尝试select name from contacts limit 10， 我们将期望MySQL必须到该行去获取数据，因为名字name没有被索引。这正是发生的情况，由下面的解释显示。
extra不再显示 using index，所以我们没有使用覆盖索引。
假设你每页有15条记录，你的用户想查看第1001页，你的内部查询最终会是这样的。
select id from contacts order by id limit 15 OFFSET 150000
explain结果显示：
MySQL能够单看索引来执行这个查询。它不会简单地跳过前15万行，在使用offset是没有办法的，但它不需要读取15万行。(只有游标分页可以让你跳过所有的行）。
即使使用覆盖索引和延迟连接，当你到达后面的页面时，结果也会变慢，尽管与传统的偏移/限制相比，它应该是最小的。使用这些方法，你可以轻易地深入到数千页。
更好的覆盖索引
这里的很多好处取决于拥有良好的覆盖索引，所以让我们稍微讨论一下。一切都取决于您的数据和用户的使用模式，但是您可以采取一些措施来确保查询的最高命中率。
这将主要与 MySQL 对话，因为那是我有经验的地方。其他数据库中的情况可能会有所不同。
大多数开发人员习惯于为单列添加索引，但没有什么能阻止您向多列添加索引。事实上，如果您的目标是为昂贵的分页查询创建覆盖索引，您几乎肯定需要一个多列索引。
当你试图为分页优化一个索引时，一定要把按列排序放在最后。如果你的用户要按update_at排序，这应该是你复合索引中的最后一列。
看看下面这个包括三列的索引。
在MySQL中，复合索引是从左到右访问的，如果一个列缺失，或者在第一个范围条件之后，MySQL会停止使用一个索引。
MySQL 将能够在以下场景中使用该索引：
如果你跳过is_archived，MySQL将无法访问update_at，将不得不诉诸于没有该索引的排序，或者根本不使用该索引，所以要确保你有相应的计划。
主键始终存在
在MySQL的InnoDB中，所有的索引都附加了主键。这意味着（email）的索引实际上是（email，id）的索引，当涉及到覆盖索引和延迟连接时，这是相当重要的。
查询select email from contacts order by id完全被email上的一个索引所覆盖，因为InnoDB将id附加到了该索引上。
使用我们上面的综合例子，你可以看到这有什么好处。
因为复合索引涵盖了is_deleted, is_archived, updated_at, 和（通过InnoDB的功能）id，整个查询可以仅由索引来满足。
降序索引
大多数时候，用户都在寻找 "最新的 "项目，即最近更新或创建的项目，这可以通过按update_at DESC排序来满足。
如果你知道你的用户主要是以降序的方式对他们的结果进行排序，那么特别将你的索引设为降序索引可能是有意义的。
MySQL 8是第一个支持降序索引的MySQL版本。
如果你在explain的Extra部分看到向后索引扫描，你也许可以配置一个更好的索引。
前向索引扫描比后向扫描快~15%，所以你要按照你认为你的用户最常使用的顺序添加索引，并为少数使用情况承担惩罚。
太阳底下无新事
这种使用偏移/限制分页与延迟连接和覆盖索引的方法并不是银d。
仅仅是递迟连接就可以让你的速度得到很好的提升，但是需要花一些额外的心思来设计正确的索引以获得最大的好处。
有一种观点认为，递延连接应该是框架中默认的偏移offset/限制limit方法，而任何时候覆盖索引的出现都只是一种奖励。我还没有在足够多的生产环境中测试过，所以还没有强烈主张这样做。
使用MySQL的递延Join连接实现高效分页 - Aaron
常听说MySQL中3表 join 的执行流程并不是前两张表 join 得出结果，再与第三张表进行 join；而是3表嵌套的循环连接。那这个3表嵌套的循环连接具体又是个什么流程呢？与前两张表 join 得出结果再与第三张表进行 join 的执行效率相比如何呢？下面通过一个例子来分析分析。
set optimizer_switch='block_nested_loop=off'
关联字段无索引的情况下强制使用索引嵌套循环连接算法，目的是更好的观察扫描行数。
表结构和数据如下：
示例SQL：
通过 slow log 得知一共扫描 24100 行：
执行计划显示用的索引嵌套循环连接算法：
扫描行数构成：
总行数=100+4000+20000=24100。
从这个结果来看，join 过程像是先 t1 和 t3 join 得出 20 行中间结果，再与 t2 进行 join 得出结果。这结论与我们通常认为的 3表 join 实际上是3表嵌套的循环连接不一样，接着往下看。
查看执行计划成本：
mysql>explain format=json select * from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.b where t1.a<21\G
其他信息：
IO成本= 1*1.0 =1
CPU成本= 100*0.2 =20
t1总成本=21
IO成本= 1*1.0 =1
CPU成本= 200*0.2 =40
t3表总成本= 驱动表扇出*(IO成本+CPU成本) = 20*(1+40) =820
阶段性总成本= 21+820 =841
此处 eval_cost=80，实则为 驱动表扇出*被驱动每次扫描行数*filtered*成本常数 ，即 20*200*10%*0.2 。
简化公式为： eval_cost=rows_produced_per_json*成本常数
IO成本= 4*1.0 =4
CPU成本= 1000*0.2 =200
t2表总成本= 前2表join的扇出*(IO成本+CPU成本) = 400*(4+200) =81600
阶段性总成本= 841+81600 =82441
此处 eval_cost=8000，即 rows_produced_per_json*成本常数 ，即 40000*0.2
根据执行计划成本分析：
这样看，3表 join 流程是：
注意，由于造的数据比较特殊，所以第 3 步得出的中间结果集实际上只有 1行，所以最终 t2 表的查找次数是 20*1=20 ，所以扫描总行数是 20*1000 。所以单看 slow log 中显示的 24100 行，会误认为是先得出 t1 和 t3 join 的结果，再去和 t2 进行 join。
当我调整 t3 的数据，删除20行，再插入20行，使满足 b<21 的数据翻倍，这样“第 3 步得出的中间结果集”变成 2 行：
再来看slow log 中扫描的总行数为44100，t1、t3的扫描行数不变，t2 的扫描行数变为 20*2*1000=40000 ：
为什么执行计划中分析得到的是 t2 表查找 400 次呢？
因为执行计划对t1 join t3 的扇出是个估算值，不准确。而 slow log 是真实执行后统计的，是个准确值。
为什么执行计划中，t2表的执行次数是用“t1 join t3 的扇出”表示的？这不是说明 t1 先和 t3 join，结果再和 t2 join？
其实拆解来看，“3表嵌套循环” 和 “前2表 join 的结果和第3张表 join” 两种算法，成本是一样的，而且如果要按3表嵌套循环的方式展示每张表的成本将非常复杂，可读性不强。所以执行计划中这么表示没有问题。
总的来说，对于3表join或者多表join 来说，“3表嵌套循环” 和 “先2表 join，结果和第3张表join” 两种算法，成本是一样的。要注意的一点是3表嵌套循环成本并非如下图写的：n m x，而是 n (m+a x)，其中 a 为 t2 满足单个等值条件的平均值。
当被驱动表的关联字段不是唯一索引，或者没有索引，每次扫描行数会大于1时，其扇出误差会非常大。比如在上面的示例中：
t3 实际的扇出只有 20，但优化器估算值是 总扫描行数的 10%，由于t3表的关联字段没有索引，所以每次都要全表扫描200行，总的扫描行数= 20*200 =4000，扇出= 4000*10% =400，比实际的20大了20倍。尤其对于后续表的 join 来说，成本估算会产生更严重的偏差。
如果是 left join，每个被驱动表的 filtered 都会被优化器认定为 100%，误差更大！
通常建议join不超过2表，就是因为优化器估算成本误差大导致选择不好的执行计划，如果要用，一定要记住：关联字段必须要有索引，最好有唯一性或者基数大。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）