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

在数据结构与算法--索引 https://url.wx-coder.cn/O07eI 一节中，我们讨论了 B+Tree, LSM-Tree 这样的文件索引以及全文索引的基础算法，本文则会针对文件索引在关系型数据库中的实际应用进行探讨。

索引（Index）是帮助数据库系统高效获取数据的数据结构，而数据库索引本质上是以增加额外的写操作，与用于维护索引数据结构的存储空间为代价的，用于提升数据库中数据检索效率的数据结构。索引可以帮助我们快速地定位到数据而不需要每次搜索的时候都遍历数据库中的每一行。当然，索引不是建立的越多、越长越好，因为索引除了占用空间之外，对后续数据库的增加、删除、修改都有额外的操作来更新索引。一般来说，小表使用全表扫描更快，中大表才使用索引，而超级大表索引基本无效，我们可能需要借助独立的全文索引系统；MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用 ES，Solr 这样的全文索引引擎。

索引类型

从索引的实现上，我们可以将其分为聚集索引与非聚集索引，或称辅助索引或二级索引，这两大类；从索引的实际应用中，又可以细分为普通索引、唯一索引、主键索引、联合索引、外键索引、全文索引这几种。

InnoDB 可以看做是聚集索引，因为它的 B+ 树的叶结点包含了完整的数据记录。InnoDB 的数据文件本身就是索引文件，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。

而 MyISAM 方式 B+ 树的叶结点只是存储了数据的地址，故称为非聚集索引。MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址；在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。

在 InnoDB 中，又有聚簇索引和普通索引之分，聚簇索引根据主键来构建，叶子节点存放的是该主键对应的这一行记录，根据主键查询可以直接利用聚簇索引定位到所在记录。而普通索引根据申明这个索引时候的列来构建，叶子节点存放的是这一行记录对应的主键的值，根据普通索引查询需要先在普通索引上找到对应的主键的值，然后根据主键值去聚簇索引上查找记录，俗称回表。如果我们查询一整行记录的话，一定要去聚簇索引上查找，而如果我们只需要根据普通索引查询主键的值，由于这些值在普通索引上已经存在，所以并不需要回表，这个称为索引覆盖，在一定程度上可以提高查询效率。

普通索引中还有唯一索引和联合索引两个特例，唯一索引在插入和修改的时候会校验该索引对应的列的值是否已经存在，联合索引将两个列的值按照申明时候的顺序进行拼接后在构建索引。

数据行并不是存储引擎管理的最小存储单位，索引只能够帮助我们定位到某个数据页，每一次磁盘读写的最小单位为也是数据页，而一个数据页内存储了多个数据行，我们需要了解数据页的内部结构才能知道存储引擎怎么定位到某一个数据行，可以参考 MySQL 存储管理 https://url.wx-coder.cn/IF5HH 系列。

索引选择性

对索引列和字符串前缀长度，都参考选择性（Selectivity）这个指标来确定：选择性定义为不重复的索引值和数据总记录条数的比值，其选择性越高，那么索引的查询效率也越高，譬如对于性别这种参数，建立索引根本没有意义。

Index Selectivity = Cardinality / #T 

显然选择性的取值范围为 (0, 1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。在实际的数据库中，我们可以通过以下语句来计算某列的选择性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM titles; 

主键

在 InnoDB 内部，表数据是优化主键快速查询而排列分布的，其查找速度是最快的，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。即使表中没有适合做主键的列，也推荐采用一个自动增长的整数主键(代理键)，那么这个表在增加数据的时候是顺序存放的，而且后续在别的表参考该外键查询的时候也会得到优化。

如果在创建表时没有显式地定义主键（Primary Key），则 InnoDB 存储引擎会按如下方式选择或创建主键：

• 首先表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。
• 不符合上述条件，InnoDB 存储引擎自动创建一个 6 个字节大小的指针，用户不能查看或访问。

主键的选择

在分布式 ID https://url.wx-coder.cn/tQ5eH 一文中我们讨论过分布式场景下的分布式 ID 的选择策略，而在数据库中，我们同样会有这样的考量。首先，MySQL 官方有明确的建议主键要尽量越短越好，36 个字符长度的 UUID 不符合要求；如果主键是一个很长的字符串并且建了很多普通索引，将造成普通索引占有很大的物理空间。并且主键最好是顺序递增的，否则在 InnoDB 引擎下，UUID 的无序性可能会引起数据位置频繁变动，严重影响性能。

（编辑：衡阳站长网）

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