MySQL索引道理与应用：索引范例，存储布局与锁

主键就是独一索引，可是独一索引不必然是主键，独一索引可觉得空，可是空值只能有一个，主键不能为空。对付单列索引，要求该列全部数据都不沟通，但应承有 NULL 值；对付多列的连系索引，要求这些列的组合是独一的。独一索引其自己既可以作为索引，现实中也可以用以发生数据束缚，防备增进可能修改后发生沟通数据，从而担保数据的完备性。

对付字符串范例，可以指定索引前缀长度(且对付 BLOB/TEXT 前缀长度参数是必需的)，在 InnoDB 表中其前缀长度最长是 767 bytes，且参数 M 是用 bytes 计量的。以是太长的字符串，成立 B+Tree 索引挥霍较量大，这时辰用手动模仿 HASH 索引是个要领，不外这种方法对字符串无法机动的行使前缀方法查询(譬喻 LIKE 这类的操纵)。

连系索引

单列索引指的是在表上为某一个字段成立的索引，一样平常索引的建设选择整型可能较小的定长字符串将更有利于服从的晋升。连系索引指的是多个字段凭证必然次序组织的索引。以索引 (name, city, gender) 为例，其起首是凭证 name 字段次序组织的，当 name 字段的值沟通时（如 Bush），其凭证 city 字段次序组织，当 city 字段值沟通时，其凭证 gender 字段组织。因为连系索引上通过多个列构建索引，偶然辰我们可以将必要频仍查询的字段加到连系索引内里，譬如常常必要按照 name 查找 age 我们可以建一个 name 和 age 的连系索引。

常见的前提连系包罗了 WHERE 前提连系与 ORDER BY 前提连系；所谓 WHERE 前提连系指的是，对付 WHERE 前提中的等值前提，其字段行使与连系索引的字段同等（次序可以纷歧致）。

ORDER BY 连系指的是假如 ORDER BY 后头的字段是连系索引包围 where 前提之后的一个字段，因为索引已经处于有序状态，MySQL 就会直接从索引上读取有序的数据，然后在磁盘上读取数据之后凭证该次序组织数据，从而镌汰了对磁盘数据举办排序的操纵。即对付未包围 ORDER BY 的查询，其有一项 Creating sort index，即为磁盘数据举办排序的耗时最高；对付包围 ORDER BY 的查询，其就不必要举办排序，而其耗时首要表此刻从磁盘上拉取数据的进程。

前缀索引

MySQL 的前缀索引可以分为三类：连系索引前缀，like 前缀和字符串前缀。

连系索引前缀与最左匹配（Leftmost Prefix）

连系索引前缀指的是在成立多列索引的时辰，必需凭证从左到右的次序行使所有或部门的索引列，才气充实的行使连系索引，好比：(col1, col2, col3) 行使 (col1)、(col1, col2)、(col1, col2, col3) 有用。在查询语句中会一向向右匹配直到碰着范畴查询 (>,<,BETWEEN,LIKE) 就遏制匹配，厥后的索引列将不会行使索引来优化查找了。

以 (name, city, interest) 三个字段连系的索引为例，假如查询前提为 where name='Bush'; 那么就只必要按照 B+树定位到 name 字段第一个 Bush 地址的值，然后次序扫描后续数据，直到找到第一个不为 Bush 的数据即可，扫描进程中将该索引片的数据 id 记录下来，最后按照 id 查询聚簇索引获取功效集。同理对付查询前提为 where name='Bush' and city='Chicago'; 的查询，MySQL 可以按照连系索引直接定位到中间灰色部门的索引片，然后获取该索引片的数据 id，最后按照 id 查询聚簇索引获取功效集。

由此我们可以得出连系索引前缀的留意点：

• 无法超过字段行使连系索引，如 where name='Bush' and interest='baseball';，对付该查询，name 字段是可以行使连系索引的第一个字段过滤大部门数据的，可是对付 interest 字段，其无法通过 B+ 树的特征直接定位第三个字段的索引片数据，好比这里的 baseball 也许分手在了第二条和第七条数据之中。最终，interest 字段着实举办的是包围索引扫描。
• 对付非等值前提，如 >、<、!= 等，连系索引前缀对付索引片的过滤只能到第一个行使非等值前提的字段为止，后续字段固然在连系索引上也无法参加索引片的过滤。这里好比 where name='Bush' and city>'Chicago' and interest='baseball';，对付该查询前提，起首可以按照 name 字段过滤索引片中第一个字段的非 Bush 的数据，然后按照连系索引的第二个字段定位到索引片的 Chicago 位置，因为其长短等值前提，这里 MySQL 就会从定位的 Chicago 往下次序扫描，因为 interest 字段是也许分手在索引第三个字段的任何位置的，因而第三个字段无法参加索引片的过滤。

因此 B-Tree 的列次序很是重要，上述行使法则都和列次序有关。对付现实的应用，一样平常要按照详细的需求，建设差异列和差异列次序的索引。假设有索引 Index(A,B,C):

# 行使索引  
A>5 AND A<10 - 最左前缀匹配  
A=5 AND B>6 - 最左前缀匹配  
A=5 AND B=6 AND C=7 - 全列匹配  
A=5 AND B IN (2,3) AND C>5 - 最左前缀匹配，填坑  
# 不能行使索引  
B>5 - 没有包括最左前缀  
B=6 AND C=7 - 没有包括最左前缀  
# 行使部门索引  
A>5 AND B=2 - 行使索引 A 列  
A=5 AND B>6 AND C=2 - 行使索引的 A 和 B 列 

（编辑：湖南网）

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