MySQL 11 怎么给字符串字段加索引？
<h3 id="场景引入">场景引入</h3><p>假设现在维护一个支持邮箱登录的系统，用户表定义如下：</p>
<pre><code class="language-sql">create table SUser(ID bigint unsigned primary key,email varchar(64), ... )engine=innodb;
</code></pre>
<p>由于登录方式为邮箱，那么一定会有下面这样的业务：</p>
<pre><code class="language-sql">select f1,f2 from SUser where email='xxx';
</code></pre>
<p>对于上述语句，可以不对email字段加索引，也可以对email建立普通索引，还可以建立前缀索引。其建立索引的语句为：</p>
<pre><code class="language-sql">alter table SUser add index index1(email);
alter table SUser add index index2(email(6));
</code></pre>
<p>这两种不同建立方式在数据结构和存储上的区别如下：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250711204814121-1017888259.png" width="50%"></div>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250711204843636-1766235973.png" width="30%"></div>
<p>由于前缀索引只取规定字节数，所以占用空间更小。</p>
<p>但使用前缀索引可能会增加额外的记录扫描次数。比如要执行下面这个查询：</p>
<pre><code class="language-sql">select id,name,email from SUser where email='zhangssxyz@xxx.com'
</code></pre>
<p>如果使用普通索引，执行顺序为：</p>
<ul>
<li>
<p>从index1索引树找到满足条件的记录，取得主键为ID2；</p>
</li>
<li>
<p>在主键索引树上找到对应的行，如果email值正确，将这行记录加入结果集；</p>
</li>
<li>
<p>从index1索引树上查找下一条记录，发现不满足条件，结束。</p>
</li>
</ul>
<p>由于只回主键索引取一次数据，所以系统认为只扫描一行。</p>
<p>如果使用前缀索引，执行顺序为：</p>
<ul>
<li>
<p>从index2索引树找到满足条件的记录，第一个是ID1；</p>
</li>
<li>
<p>在主键索引树上找到对应的行，发现email值不正确，丢弃这行记录；</p>
</li>
<li>
<p>从index2索引树找到满足条件的下一条记录，取得主键为ID2；</p>
</li>
<li>
<p>在主键索引树上找到对应的行，如果email值正确，将这行记录加入结果集；</p>
</li>
<li>
<p>重复上述步骤，直到index2上匹配不到正确的前缀。</p>
</li>
</ul>
<p>这个过程需要回主键索引取4次数据，即扫描了4行。</p>
<p>但是，在该场景下，如果定义的index2为<code>email(7)</code>，会发现在index2上能直接取到ID2，也就只需要扫描一行。因此，当使用前缀索引，<strong>定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本</strong>。</p>
<p>那么，你大概就会好奇了，前缀的长度如何确定呢？</p>
<p>在建立索引时，我们需要关注字段的区分度，区分度越高，重复的键值越少。</p>
<p>可以使用下面语句计算列上有多少不同的值：</p>
<pre><code class="language-sql">select count(distinct email) as L from SUser;
</code></pre>
<p>然后，可以依次选取不同前缀长度来看：</p>
<pre><code class="language-sql">mysql> select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;
</code></pre>
<p>前缀损失一般会损失区分度，因此在选取长度时心里需要对可以接受的损失比做一个预估，比如想要控制在5%之内，那么L4-L7里需要选取$\ge$95% L的值。</p>
<h3 id="前缀索引对覆盖索引的影响">前缀索引对覆盖索引的影响</h3>
<p>前缀索引除了可能增加扫描行数，还有其他影响。比如下面这两个语句：</p>
<pre><code class="language-sql">select id,email from SUser where email='zhangssxyz@xxx.com';
</code></pre>
<p>如果使用index1（整个字符串的索引结构），可以利用覆盖索引，在index1查到结果就直接返回，不需要回表；而如果使用前缀索引，就必须回表，即使使用整个字符串的长度<code>email(18)</code>，依然需要回表，因为系统不确定前缀索引的定义是否截断了完整信息。</p>
<p>在该例子中，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是在选择是否使用前缀索引时需要考虑的一个因素。</p>
<h3 id="其他方式">其他方式</h3>
<p>对于邮箱这样的字段，由于用户名差异较大，使用前缀索引的效果可能不错。但有些字段前缀区分度不好，比如身份证号，这时候如果使用前缀索引，可能需要创建长度较长的前缀索引，才能满足区分度要求。但是这也意味着索引占用磁盘空间越大，搜索效率会越低。</p>
<p>假如，能够确定业务需求里只有按照身份证进行<strong>等值查询</strong>的需求，那么有更好的处理方式，既可以占用更小空间，又能达到相同查询效率：</p>
<p>（1）使用倒序存储</p>
<p>将身份证号倒过来存储，那么可能取6位就有足够的区分度。每次查询时，使用下面的方式：</p>
<pre><code class="language-sql">select field_list from t where id_card = reverse('input_id_card_string');
</code></pre>
<p>（2）使用hash字段</p>
<p>可以在表上再创建一个整数字段，比如每次插入新纪录时用<code>crc32()</code>函数得到一个校验码字段。由于校验码可能冲突，在查询时候需要判断<code>id_card</code>字段是否相同。</p>
<p>来看上面两种方法的异同：相同点是都不支持范围查询，不同点有：</p>
<ul>
<li>
<p>占用额外空间不同。倒序存储不消耗额外存储空间，而Hash字段需要增加一个字段。</p>
</li>
<li>
<p>CPU消耗不同。倒序存储方式每次读写都需要额外调用一次reverse函数，而Hash方式需要额外调用一次<code>crc32()</code>函数，从两个函数的计算复杂度看的话前者更小。</p>
</li>
<li>
<p>查询效率不同。使用Hash方式的查询性能相对更稳定，因为<code>crc32()</code>冲突概率小，可以认为每次查询的平均扫描行数接近1，而倒序存储方式毕竟还是使用前缀索引方式，会增加扫描行数。</p>
</li>
</ul><br><br>
来源：https://www.cnblogs.com/san-mu/p/18979644
頁:
[1]