PostgreSQL Partial Index - 通过查询条件索引选定的行

   <p>说起「 <a href="/misc/goto?guid=4959671794970607657" rel="nofollow,noindex">Partial Index</a> 」，估计很多人没听说过。在 <a href="/misc/goto?guid=4959671795065734576" rel="nofollow,noindex">PostgreSQL</a> 中，它的含义是指：通过查询条件索引选定的行，而不是所有的行。虽然 <a href="/misc/goto?guid=4959671795148213974" rel="nofollow,noindex">MySQL</a> 也有此概念，但是其更接近前缀索引的含义：比如你想索引一个 VARCHAR(255) 的字段，根据数据分布情况，你可以仅索引前面若干个字符，如此通过降低索引体积来达到提升性能的目的。</p>    <h2>例一：</h2>    <p>有一个 users 表，里面有一个 mobile 字段，缺省值为 null。用户可以不填写手机号，如果填写，每个手机号只能关联一个用户，怎么办？</p>    <p>听起来这就是一个唯一索引，但是直接创建的话肯定会失败，因为字段里有空值，这个问题放到 MySQL 里就无解了，只能通过新建一个 users_mobiles 表来约束唯一性。下面看看在 PostgreSQL 中如何利用 Partial Index 来搞定：</p>    <pre>  <code class="language-sql">sql> CREATE UNIQUE INDEX on users (mobile)       WHERE mobile IS NOT NULL;</code></pre>    <p>通过在创建索引的时候过滤掉无关的数据，从而实现创建唯一索引的目的。</p>    <h2>例二：</h2>    <p>有一个 questions 表，里面有一个 created_at 字段表示创建时间，一个 answer_count 字段表示答案数量，查询不同时间范围里答案数量大于 10 个的问题：</p>    <pre>  <code class="language-sql">sql> SELECT * FROM questions       WHERE answer_count > 10 AND created_at > 1455555555       LIMIT 100;</code></pre>    <p>插播知识广告：查询分为两种类型：其一是 point 查询，比如 foo = 123；其二是 range 查询，比如 foo > 123。通常，一个索引里只能用到一个 range 类型的查询字段。</p>    <p>如此说来，本例如何创建索引呢？因为两个查询条件都是 range 类型的，所以一般情况下创建索引时必须二选一。下面看看在 PostgreSQL 中如何利用 Partial Index 来搞定：</p>    <p>先看看没有使用 Partial Index 时查询的效果：</p>    <pre>  <code class="language-sql">sql> CREATE INDEX created_at on questions (created_at);</code></pre>    <p><img src="https://simg.open-open.com/show/e42409663769f325c200a8310fb9363e.jpg"></p>    <h2>没有使用 Partial Index</h2>    <p>再看看使用 Partial Index 时查询的效果：</p>    <pre>  <code class="language-sql">sql> CREATE INDEX partial_index on questions (created_at)       WHERE answer_count > 10;</code></pre>    <p><img src="https://simg.open-open.com/show/7fbc71a2f927d1e38253859cdafd25af.jpg"></p>    <h2>使用 Partial Index</h2>    <p>对比前后两次查询可以发现，在没有使用 Partial Index 的时候，只能先通过索引拿到结果，然后再通过 Filter 过滤另一个条件；而在使用 Partial Index 的时候，可以直接通过 Partial Index 拿到结果，无需二次过滤，在本例中查询效率有百倍提升。</p>    <p>当然了，只有那些相对固定的条件适合用在 Partial Index 中，比如本例中，答案数量大于 10 的条件是作为热门问题的判断依据存在的，是明确的业务逻辑，如此则适合；而另一个创建时间的条件这是频繁变化的，如此则不适合。</p>    <p>推荐阅读： <a href="/misc/goto?guid=4959671795234023099" rel="nofollow,noindex">Handling Growth with Postgres: 5 Tips From Instagram</a> 。</p>    <p> </p>    <p>来自： <a href="/misc/goto?guid=4959671795314711679" rel="nofollow">http://huoding.com/2016/04/28/510</a></p>