作为 Percona 的首席架构师，我的职责之一是为我们的客户调整 MySQL 数据库服务器。调优工作着眼于数据库服务的各个方面，如操作系统、MySQL 配置、模式、查询等。我们有明确定义的流程来调优操作系统和 MySQL 配置。然而，调整模式和使用它的查询可能是微不足道的，也可能是极具挑战性的。
架构和查询的挑战主要在于索引。最常见的索引类型是基于 b 树或哈希列表。 InnoDB 不支持散列索引，这对于相等条件来说是个遗憾。 B-tree 索引更通用，它们适用于相等条件并且非常适用于范围条件。然而，它们非常重，并且非常依赖于定义的列顺序。它们对于双量程条件也很差。
GIS R-tree 类型索引可以处理双范围条件，这是我想在以后的文章中探讨的主题。这篇文章的重点是全文索引，而不是像文本文档这样的主要用例。我想向您展示如何使用这种类型的索引来有效地解决一种模式的性能问题，我将其称为“基于标签的模式”。令人惊讶的是，全文索引受到的关注如此之少，我在此博客上进行了快速搜索，我们的最新帖子来自 2018 年。
什么是基于标签的架构？
至少从高级视图来看，数据库表用于将对象或实体的属性映射到列。对象可以是社交网络数据库中的用户或关系或信用卡处理数据库中的交易。一些对象具有很少的、定义非常明确的属性，而另一些对象则具有数十个甚至数百个属性。包含一百列可能需要索引的表是不切实际的并且很难调整。让我们看几个例子。
示例：房屋租赁
假设您正在为房屋租赁应用程序设计数据库。您需要一张表格来描述您要出租的房屋。在这种情况下，房屋是具有许多描述其基本属性、便利设施和环境的属性的对象。让我们尝试列出主要的：
属性：房间数量、浴室数量、私人浴室、全套厨房、双人床、两张单人床、停车位、会议/工作空间、评级（星级）、价格范围、地区等。
配套设施：空调、游泳池、按摩浴缸、壁炉、台球桌、wifi、家庭影院、有线电视、美景、安静（或不）、禁烟（或不）等。
环境：海滩、徒步、步行、健身、滑雪、餐厅、酒吧、眼镜、剧院、公园、离市中心的距离、离机场的距离等。
示例：树分类
一个非常不同的例子是一个数据库，列出了树木的种类以及如何根据它们的一般特征、树皮、叶子、种子等来识别它们。
树皮：光滑、纸质、剥落、棱纹、鳞片等。
叶：SimpleSym、simpleAsym、多对生、多交替、多旋、针扁、针圆、裂片、齿等
种子：水果、坚果、翼果、橡子、球果、豆荚等。
这些类型的对象很难映射。除了我们上面讨论的宽方法（许多列）之外，另一种传统方法是实体值模式。实体值模式每个属性使用一行，而不是宽，我们可以说它很长。实体值模式很灵活，但也带来了许多调整挑战。让我们探索使用全文索引和标签的替代方案。在此替代方案中，您存储属性 word 元素（标签），使用分隔符将它们连接到 TEXT 列中。
带有全文索引的标签
首先，我们需要为属性/标签定义一个字典表：
CREATE TABLE `dictTags` (
`tag` varchar(10) NOT NULL,
`description` varchar(100) DEFAULT NULL,
`groupTags` int unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
该表包含所有定义的标签。 描述是标签的人类可读版本，GroupTags 允许设置独占组（只能使用一个值）。 上表是一个简单的示例，但可以轻松扩展。
这是房屋或公寓租赁网站的示例标签列表。 请记住，我根本不擅长这种类型的业务，我很容易错过关键方面。 更现实地，可能有数百个标签。
mysql> select * from dictTags;
+-------------+----------------------------------------+-----------+
| tag | description | groupTags |
+-------------+----------------------------------------+-----------+
| 1bath | One bathroom | 1 |
| 1bedr | One bedroom | 2 |
| 1kmCenter | Less than 1 kilometer from city center | 6 |
| 2bath | Two bathrooms | 1 |
| 2bedr | Two bedrooms | 2 |
| 2kmCenter | Less than 2 kilometer from city center | 6 |
| 3bath | Three bathrooms | 1 |
| 3bedr | Three bedrooms | 2 |
| 3kmCenter | Less than 3 kilometer from city center | 6 |
| 4bath | Four bathrooms | 1 |
| 4bedr | Four bedrooms | 2 |
| aircond | Air conditioning | 0 |
| beach | Direct beach access | 0 |
| City | Urban environment | 4 |
| ctv | Cable TV | 0 |
| Doublebed | Double beds | 5 |
| firep | Fireplace | 0 |
| hike | Hiking | 0 |
| hometh | Home Theater | 0 |
| Jacu | Jacuzzi | 0 |
| Nature | Nature environment | 4 |
| night | Nightlife options close by | 3 |
| poolt | Pool table | 0 |
| quiet | Quiet neighborhood | 3 |
| sky | Skying options | 0 |
| swim | Swimming pool | 0 |
| Twinbed | Twin beds | 5 |
| view | Nice view | 0 |
| wifi | Wifi Internet | 0 |
+-------------+----------------------------------------+-----------+
29 rows in set (0.00 sec)
我们将在此表中存储要出租的物业：
CREATE TABLE `PropertiesToRent` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`owner` int unsigned NOT NULL,
`address` varchar(100) DEFAULT NULL,
`tags` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `idxFtTags` (`tags`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
为了帮助以一致的方式操作标签，我们将创建四个方便的 SQL 函数，hasTag、AddTag、AddTags 和 removeTag。 这些函数的 SQL 代码可在我的 GitHub 上找到。 有了这些函数，让我们添加一些属性。
mysql>; insert into PropertiesToRent (id,owner,address,tags) values (1,1,'1 Québec street',addTags('4bedr,2bath,wifi,aircond,swim',''));
Query OK, 1 row affected (0.00 sec)
mysql>; insert into PropertiesToRent (id,owner,address,tags) values (2,1,'1 Ontario street',addTags('3bedr,2bath,wifi,Jacu,poolt',''));
Query OK, 1 row affected (0.00 sec)
mysql>; insert into PropertiesToRent (id,owner,address,tags) values (3,2,'1 Manitoba street',addTags('3bedr,3bath,sky,nature,ctv,Jacu',''));
Query OK, 1 row affected (0.00 sec)
mysql>; select * from PropertiesToRent;
+----+-------+-------------------+---------------------------------+
| id | owner | address | tags |
+----+-------+-------------------+---------------------------------+
| 1 | 1 | 1 Québec street | 4bedr,2bath,wifi,aircond,swim |
| 2 | 1 | 1 Ontario street | 3bedr,2bath,wifi,Jacu,poolt |
| 3 | 2 | 1 Manitoba street | 3bedr,3bath,sky,nature,ctv,Jacu |
+----+-------+-------------------+---------------------------------+
3 rows in set (0.00 sec)
现在，我们可以使用标签上的全文索引来搜索表格。 假设您想找出哪些房产有按摩浴缸：
mysql> select * from PropertiesToRent where match (tags) against ('+Jacu' in Boolean mode);
+----+-------+-------------------+---------------------------------+
| id | owner | address | tags |
+----+-------+-------------------+---------------------------------+
| 2 | 1 | 1 Ontario street | 3bedr,2bath,wifi,Jacu,poolt |
| 3 | 2 | 1 Manitoba street | 3bedr,3bath,sky,nature,ctv,Jacu |
+----+-------+-------------------+---------------------------------+
2 rows in set (0.00 sec)
你还需要有wifi连接吗？
mysql> select * from PropertiesToRent where match (tags) against ('+Jacu +wifi' in Boolean mode);
+----+-------+------------------+-----------------------------+
| id | owner | address | tags |
+----+-------+------------------+-----------------------------+
| 2 | 1 | 1 Ontario street | 3bedr,2bath,wifi,Jacu,poolt |
+----+-------+------------------+-----------------------------+
1 row in set (0.01 sec)
然后你意识到游泳池可以代替按摩浴缸：
mysql> select * from PropertiesToRent where match (tags) against ('+(Jacu swim) +wifi' in Boolean mode);
+----+-------+------------------+-------------------------------+
| id | owner | address | tags |
+----+-------+------------------+-------------------------------+
| 1 | 1 | 1 Québec street | 4bedr,2bath,wifi,aircond,swim |
| 2 | 1 | 1 Ontario street | 3bedr,2bath,wifi,Jacu,poolt |
+----+-------+------------------+-------------------------------+
2 rows in set (0.00 sec)
这种方法的缺点是某些属性包含其他属性。 例如，如果您想要一个至少有三间卧室的房产，您需要指定附加属性以包含更多卧室数量：
mysql> select * from PropertiesToRent where match (tags) against ('+(3bedr 4bedr) +(Jacu swim) +wifi' in Boolean mode);
+----+-------+------------------+-------------------------------+
| id | owner | address | tags |
+----+-------+------------------+-------------------------------+
| 1 | 1 | 1 Québec street | 4bedr,2bath,wifi,aircond,swim |
| 2 | 1 | 1 Ontario street | 3bedr,2bath,wifi,Jacu,poolt |
+----+-------+------------------+-------------------------------+
2 rows in set (0.00 sec)
其他履行
我上面的例子相当简单。 如前所述，我还可以想到另外两种实现方式：wide 和 entity。 让我们看看这些替代实现的等效查询是什么。
宽（多列）：
select * from PropertiesToRent where bedr >= 3 and (Jacuzzi = ‘y’ or swimingpool = ‘y’) and wifi = ‘y’;
实体（垂直）：
select * from PropertiesToRent
where id in (select propertyId from PropertyAttributes where AttributeName = ‘Bedroom’ and iValue > 3)
and id in (select propertyId from PropertyAttributes where AttributeName in (‘Jaccuzzi’,’Swimminpool’) and bValue = ‘y’)
and id in (select propertyId from PropertyAttributes where AttributeName in (’wifi’) and bValue = ‘y’);
这两种实现都有自己的调优挑战。广泛的实现易于部署，但难以维护，并且几乎不可能正确索引，因为大量列可能是 where 子句的一部分。您可以轻松地扩展实体实现，但随后，PropertyAttributes 表将变得非常大。对于每种支持的数据类型，您还必须有一列。
结论
在这篇文章中，我们使用全文索引来解决具有大量属性的模式映射对象的问题。这种使用文本标签来映射属性的方法非常有效和优雅。它比将属性映射到列的广泛方法更容易维护。它也比每个属性都是表中的一行的实体值模式更有效。
全文索引不如构建 InnoDB 的 b-tree 索引常见。使用 GIS R-tree 索引，MySQL 中使用较少的索引类型。然而，正如我们在这篇文章中所展示的那样，它们可能在其预期的初始范围之外很有用。
在以后的文章中，我想探讨使用 R-tree 索引来解决另一个常见的 SQL 问题，即双范围条件。如果您对数据库功能的非正统用法感兴趣，请保持关注。
原文标题：Tags and FullText indexes in MySQL
原文作者：Yves Trudeau
原文地址：https://www.percona.com/blog/tags-and-fulltext-indexes-in-mysql/
