我有一个名为“element”的主表:

CREATE TABLE `element` (
  `elements_id` int(11) NOT NULL AUTO_INCREMENT,
  `elements_code` varchar(32) DEFAULT NULL,
  `elements_name` varchar(128) DEFAULT NULL,
  `elements_description` text,
  `elements_image` varchar(64) DEFAULT NULL,
  `attribute_category_id` int(11) DEFAULT '0',
  `attribute_material_id` int(11) DEFAULT '0',
  `attribute_color_id` int(11) DEFAULT '0',
  `attribute_shape_id` int(11) DEFAULT '0',
  `attribute_surface_id` int(11) DEFAULT '0',
  `attribute_size_id` int(11) DEFAULT '0',
  `attribute_holesize_id` int(11) DEFAULT '0',
  `attribute_cut_id` int(11) DEFAULT '0',
  `attribute_height_id` int(11) NOT NULL DEFAULT '0',
  `attribute_width_id` int(11) NOT NULL DEFAULT '0',
  `attribute_thickness_id` int(11) NOT NULL DEFAULT '0',
  `attribute_clasp_id` int(11) NOT NULL DEFAULT '0',
  `attribute_setting_id` int(11) NOT NULL DEFAULT '0',
  `attribute_chain_id` int(11) NOT NULL DEFAULT '0',
  `elements_weight` decimal(5,3) DEFAULT NULL,
  `elements_weight_goldpure` decimal(5,3) NOT NULL DEFAULT '0.000',
  `elements_supplier` varchar(64) DEFAULT NULL,
  `elements_price` decimal(10,5) DEFAULT NULL,
  `add_date` datetime DEFAULT NULL,
  `add_by` varchar(30) DEFAULT NULL,
  `is_finalized` char(1) DEFAULT '0',
  `stars` tinyint(4) NOT NULL DEFAULT '0',
  `wax_complexity` char(1) DEFAULT NULL,
  `elements_dioh_target` varchar(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`elements_id`),
  KEY `attribute_category_id` (`attribute_category_id`),
  KEY `attribute_material_id` (`attribute_material_id`),
  KEY `attribute_color_id` (`attribute_color_id`),
  KEY `attribute_shape_id` (`attribute_shape_id`),
  KEY `attribute_surface_id` (`attribute_surface_id`),
  KEY `attribute_size_id` (`attribute_size_id`),
  KEY `attribute_holesize_id` (`attribute_holesize_id`),
  KEY `attribute_cut_id` (`attribute_cut_id`),
  KEY `attribute_height_id` (`attribute_height_id`),
  KEY `attribute_width_id` (`attribute_width_id`),
  KEY `attribute_thickness_id` (`attribute_thickness_id`),
  KEY `is_finalized` (`is_finalized`)
) ENGINE=MyISAM AUTO_INCREMENT=12687 DEFAULT CHARSET=latin1

然后我加入这个名为“products_material”的表:

CREATE TABLE `products_materials` (
  `products_materials_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `material_name` varchar(128) NOT NULL DEFAULT '',
  `active_status` char(1) DEFAULT '0',
  `sort_number` int(4) DEFAULT '0',
  `label_active_status` char(1) DEFAULT '0',
  PRIMARY KEY (`products_materials_id`)
) ENGINE=MyISAM AUTO_INCREMENT=120 DEFAULT CHARSET=latin1

像这样的查询:

SELECT e.*, pm.material_name AS mat_name
FROM element e
  LEFT JOIN products_materials pm ON pm.products_materials_id=e.attribute_material_id
WHERE e.is_finalized='1' AND 1 = 1  AND pm.products_materials_id  =  '1' GROUP BY e.elements_id HAVING 1 = 1 ORDER BY e.elements_id;

解释结果:

+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                              | key                   | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | pm    | NULL       | const | PRIMARY                                    | PRIMARY               | 4       | const |    1 |   100.00 | Using filesort                     |
|  1 | SIMPLE      | e     | NULL       | ref   | PRIMARY,attribute_material_id,is_finalized | attribute_material_id | 5       | const |   10 |    98.20 | Using index condition; Using where |
+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+

如您所见，表“元素”使用键 attribute_material_id 进行索引。 但是如果我离开加入这个名为“elements_attributes_description”的表:

CREATE TABLE `elements_attributes_description` (
  `elements_attributes_decription_id` int(11) NOT NULL AUTO_INCREMENT,
  `elements_attributes_id` int(11) DEFAULT NULL,
  `languages_id` int(11) DEFAULT NULL,
  `elements_attributes_groups` int(11) DEFAULT NULL,
  `name` varchar(64) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  PRIMARY KEY (`elements_attributes_decription_id`),
  UNIQUE KEY `Unique` (`elements_attributes_id`,`languages_id`,`elements_attributes_groups`),
  KEY `index3` (`elements_attributes_groups`),
  KEY `Index 1` (`elements_attributes_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1776 DEFAULT CHARSET=latin1

像这样的查询:

SELECT e.*, ead2.name AS mat_name
FROM element e
  LEFT JOIN elements_attributes_description ead2 ON ead2.elements_attributes_id = e.attribute_material_id AND ead2.elements_attributes_groups = 2
WHERE e.is_finalized='1' AND ead2.elements_attributes_id  =  '1' GROUP BY e.elements_id HAVING 1 = 1 ORDER BY e.elements_id;

解释结果:

+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                              | key     | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | ead2  | NULL       | ref  | Unique,index3,Index 1                      | Index 1 | 5       | const |   30 |    19.08 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY,attribute_material_id,is_finalized | NULL    | NULL    | NULL  | 5123 |    70.20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+

如您所见，表格“元素”或未使用任何可能的键。

第二个查询中的查询或表结构有什么问题？

提前感谢您查看我的案例。

感谢任何提示或批评!

最佳答案

表结构没有问题，索引可能会得到改进。索引的主要问题是它们都只索引一个字段。 MySQL 在一个查询中每个表只能使用 1 个索引。

MySQL 可能决定不使用任何索引的原因:

1. element 表很小。 5123 条记录对于 rdbms 来说毫无意义。如果表很小，MySQL 可以决定不使用索引，因为打开索引并根据索引进行搜索然后转到表中并获取匹配数据的效率可能较低，而不是简单地搜索表中的所有记录。

2. is_finalised 字段可能有 2 个可能的值(或最多 3 个)，因此它的选择性很低，因此 MySQL 无论如何都不太可能使用它。

3. 这两个查询都违反了 sql 标准，它们对单个字段进行分组，并在选择列表中包含多个既不在分组依据子句中的字段，也不受聚合约束，也不在功能上依赖于组按字段。幸运的是，较新版本的 mysql 默认会阻止此类查询。

我会做些什么来让 mysql 更有可能使用索引:

1. 将 left join 更改为内部联接(您正在对右侧表进行过滤，有效地将左侧联接变为内部联接)。

2. 为覆盖 attribute_material_id, elements_id 字段的元素表添加多列索引。

显然，您应该考虑重写您的查询以符合 sql 标准。

关于php - MYSQL 不使用可能的键，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46108632/