MYSQL未使用可能的索引键

Question

我有一个名为“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 |
+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+

正如您所见，表“element”使用关键属性_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;

EXPLAIN的结果：

+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
| 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) |
+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+

正如您所看到的，表"element"没有使用任何可能的键。

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

提前感谢您对我的案例进行审查。

欢迎提供任何建议或批评！

- Erick

---

嗨，请参考此链接 https://dev59.com/N2025IYBdhLWcg3w7qfL ，希望能对您有所帮助。 - KMS

你的表中有多少条记录？ - Shadow

嗨@Shadow，表元素=5123行，表元素属性描述=1766行，表产品材料=116行。 - Erick

Answer 1

表结构没有问题，索引有待改进。索引的主要问题在于所有索引都只索引一个字段。MySQL在查询中每个表只能使用一个索引。

以下是MySQL可能决定不使用任何索引的原因：

1. element表很小。对于关系数据库管理系统来说，5123条记录并不多。如果表很小，MySQL可能会决定不使用索引，因为打开索引并基于此进行搜索，然后访问表并获取匹配数据可能比仅在表中搜索所有记录要不太有效率。

2. is_finalised字段可能只有2个可能的值（或最多3个），因此它的选择性很低，因此MySQL不太可能使用它。

3. 两个查询都按照sql标准进行分组，只针对单个字段，并在select列表中包含几个既不在group by子句中，也不受聚合影响，也不依赖于group by字段的字段。幸运的是，较新版本的mysql默认阻止这些查询。

我会怎样使MySQL更有可能使用索引：

1. 将left join改为inner join（您正在过滤右侧表，将left join有效地转换为inner join）。

2. 在元素表中添加覆盖attribute_material_id、elements_id字段的多列索引。

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

- Shadow