我真的需要一些关于如何正确优化mysql数据库查询的建议，我害怕一开始就把一切都搞砸了，这会让我以后很头疼。
有一个商品目录，商品有自己的属性-类别、性别、年龄、品牌、颜色、材料、季节、制造商。所有这些字段都将参与选择/搜索。但不总是同时参与。这些字段可以是以下类型的复合查询：
类别、性别、存在
类别、性别、品牌、颜色、季节、可用性
类别、制造商、可用性
等等。
将始终使用两个字段-类别和可用性（不需要显示目录中不可用的产品，但不会从数据库中删除）。
迁移如下所示：
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->string('name', 100);
$table->string('title', 100);
$table->string('category_title', 50)->index()->nullable();
$table->integer('old_price')->nullable();
$table->integer('price')->nullable();
$table->integer('sale')->nullable();
$table->string('description', 500)->nullable();
$table->string('short_description', 150)->nullable();
$table->string('img_small', 115)->nullable();
$table->string('age', 15)->index()->nullable();
$table->string('gender', 10)->index()->nullable();
$table->string('brand', 50)->index()->nullable();
$table->string('color', 50)->index()->nullable();
$table->string('material', 50)->index()->nullable();
$table->string('seazon', 50)->index()->nullable();
$table->string('country', 50)->index()->nullable();
$table->integer('stock')->default('0')->index();
$table->float('rating')->nullable()->index();
$table->integer('bought')->default('0')->index();
$table->timestamps();
}目前，所有参与索引的字段都被标记为索引。很明显，当请求多个where条件时-这并不是那么有用。我尝试使用复合索引：
$table->index(['category_title', 'stock', 'gender', 'brand', 'color']);而且它对这类请求有效。但我强烈怀疑我是否对所有可能的查询条件做笛卡尔交集并在那里添加索引是最好的解决方案。
一个问题-如何在这种情况下是正确的实现索引字段？我提前感谢您的帮助解决这个问题。
1条答案
按热度按时间qoefvg9y1#
你是对的，你不能创建足够多的索引来覆盖属性的每一个排列，这将需要N因子索引，但是在MySQL中，你不能为每个表创建超过64个索引。
如果需要支持这些属性的任意排列，就不能完全索引所有可能性。
您所能做的就是对您所说的在每个查询的条件中出现的列进行索引：这将至少缩小所检查的行的范围，然后其他条件将应用于所检查的行，即使它没有使用索引进行优化。
您还可以在三列上创建索引：category和availability，还有一个，所以搜索除了常用属性外，还会按一个属性过滤已检查的行。然后，任何其他条件都将应用于已检查的行。
因此，您可以创建以下索引：
优化器将选择这些索引中的一个，无论它认为哪一个在缩小所检查的行数方面最有效。
请在评论中回复您的问题。
当使用"类别，可用性，性别"索引时，是否会快速搜索"类别，可用性"？或者该索引仅适用于三元比较？
(category, availability, gender)上的索引将有助于从左到右搜索这些列的子集的查询。以电话簿为例，它就像一个按顺序排列的
(last name, first name, phone number)列的索引，如果只按姓氏搜索，它仍然会有帮助，如果按姓氏和名字这两列搜索，它也会有帮助。因此，您不必使用索引的 * 所有 * 列进行搜索即可使用索引。
但是如果你只搜索名字，那些名字是分开的，因为它们可能属于整本书中姓氏不同的任何人，所以索引是没有帮助的。
所以你必须在索引中从左到右使用连续的列进行搜索，你不必使用所有的列，但是你不能跳过列。
如果我做一个长索引"类别，可用性，性别，品牌，颜色，季节，制造商"-这将有助于查询速度
由于上述规则，每个列的单个索引可能没有帮助，它只对搜索该索引的前导连续列的查询有帮助。
在您的示例中，您对
(category, availability, gender, brand, color, season, manufacturer)有一个索引，并按category, availability, gender, manufacturer, brand进行搜索。它将按索引的最左侧列缩小搜索范围：category, availability, gender, brand.查询中
AND搜索项的顺序是可交换的，因此MySQL知道如何将它们与索引的列匹配。您的WHERE子句不需要与索引定义的顺序相同。您的搜索中没有
color或season的搜索项，因此最左边的列集合中有一个缺口。因此，索引的后续列（如manufacturer）不会用于优化该查询。很多人告诉我哪里做错了。我需要将表相互链接，并在其他表中存储属性。如果为每个属性创建表，查询会运行得更快吗？
听起来那些人在描述数据库规范化。
性能不是数据库规范化的目的。其目的是将属性放在正确的表中，这有助于避免数据异常。如果每个"事实"只存储一次，则事实不可能与存储在另一个表中的相同信息不一致。
这并不一定有助于提高性能，但有助于降低数据变得不合逻辑和损坏的可能性。如果您告诉客户他们的银行帐户余额已损坏，但查询速度非常快，这对他们来说不会有多大安慰。
数据库规范化并不是随机地分离属性。规范化数据库的设计有一些逻辑指导，你应该学习它们。你可以找到像wikipedia这样的免费资源：https://en.wikipedia.org/wiki/Database_normalization
我还在我的书SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming中写了一个描述范式的附录。