MySQL索引原理与应用：索引类型，存储结构与锁

使用索引对结果进行排序，需要索引的顺序和 ORDER BY 子句中的顺序一致，并且所有列的升降序一致(ASC/DESC)。如果查询连接了多个表，只有在 ORDER BY 的列引用的是第一个表才可以(需要按序 JOIN)。

# 使用索引排序  
ORDER BY A - 最左前缀匹配  
WHERE A=5 ORDER BY B,C - 最左前缀匹配  
WHERE A=5 ORDER BY B DESC - 最左前缀匹配  
WHERE A>5 ORDER BY A,B - 最左前缀匹配  
# 不能使用索引排序  
WHERE A=5 ORDER BY B DESC,C ASC - 升降序不一致  
WHERE A=5 ORDER BY B,D - D 不在索引中  
WHERE A=5 ORDER BY C - 没有包含最左前缀  
WHERE A>5 ORDER BY B,C - 第一列是范围条件，无法使用 BC 排序  
WHERE A=5 AND B IN(1, 2) ORDER BY C - B 也是范围条件，无法用 C 排序  

like 前缀

对于 like 前缀，其是指在使用 like 查询时，如果使用的表达式为 first_name like 'rMq%';那么其是可以用到 first_name 字段的索引的。但是对于 first_name like '%Chu%';，其就无法使用 first_name 的索引。对于 like 前缀，MySQL 底层实际上是使用了一个补全策略来使用索引的，比如这里 first_name like 'rMq%';，MySQL 会将其补全为两条数据：rMqAAAAA 和 rMqzzzzz，后面补全部分的长度为当前字段的最大长度。在使用索引查询时，MySQL 就使用这两条数据进行索引定位，最后需要的结果集就是这两个定位点的中间部分的数据。如下是使用 like 前缀的一个示意图：

字符串前缀

字符串前缀索引指的是只取字符串前几个字符建立的索引。在进行查询时，如果一个字段值较长，那么为其建立索引的成本将非常高，并且查询效率也比较低，字符串前缀索引就是为了解决这一问题而存在的。字符串前缀索引主要应用在两个方面：

• 字段前缀部分的选择性比较高；
• 字段整体的选择性不太大（如果字段整体选择性比较大则可以使用哈希索引）。

譬如为 first_name 字段建立了长度为 4 的前缀索引，可以看到，如果查询使用的是 where first_name='qWhNIZqxcbD';，那么 MySQL 首先会截取等值条件的前四个字符，然后将其与字符串前缀索引进行比较，从而定位到前缀为"qWhN"的索引片，然后获取该索引片对应的磁盘数据，最后将获取的磁盘数据的 first_name 字段与查询的等值条件的值进行比较，从而得到结果集。

字符串前缀索引最需要注意的一个问题是如何选择前缀的长度，长度选择合适时，前缀索引的过滤性将和对整个字段建立索引的选择性几乎相等。这里我们就需要用到前面讲解的关于字段选择性的概念，即字段选择性为对该字段分组之后，数据量最大的组的数据量占总数据量的比例。这里选择前缀长度时，可以理解为，前缀的选择性为按照前缀分组之后，数据量最大的组占总数据量的比例。如下表所示为计算前缀长度的 SQL 公式：

select count(*) as cnt, first_name as perf from actor group by perf ORDER BY cnt desc limit 10;    -- 0  
select count(*) as cnt, left(first_name, 2) as perf from actor group by perf ORDER BY cnt desc limit 10;    -- 2  
select count(*) as cnt, left(first_name, 3) as perf from actor group by perf ORDER BY cnt desc limit 10;    -- 3  
select count(*) as cnt, left(first_name, 4) as perf from actor group by perf ORDER BY cnt desc limit 10;    -- 4  

其他索引

覆盖索引

覆盖索引指的是对于查询中使用的除去参与索引过滤扫描的所有字段将其加入到该查询所使用的索引尾部的索引。覆盖索引扫描的优点在于由于查询中所使用的所有字段都在同一索引的字段，因而在进行查询时只需要在索引中获取相关数据即可，而不需要回磁盘扫描相应的数据，从而避免了查询中最耗时的磁盘 I/O 读取。对于如下查询：

select a, b, c from t where a='a' and b='b'; 

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!