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 的无序性也许会引起数据位置频仍变换，严峻影响机能。

（编辑：湖南网）

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