MySQL查询未使用索引问题排查：inventoryLineItem表查询异常

表结构信息 #

mysql> describe inventoryLineItem;
+-------------------------+---------------+------+-----+---------+----------------+
| Field                   | Type          | Null | Key | Default | Extra          |
+-------------------------+---------------+------+-----+---------+----------------+
| id                      | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| createdBy               | varchar(255)  | NO   |     | NULL    |                |
| dateCreated             | bigint(20)    | NO   |     | NULL    |                |
| isDeleted               | bit(1)        | NO   | MUL | NULL    |                |
| lastModified            | bigint(20)    | NO   |     | NULL    |                |
| lastModifiedBy          | varchar(255)  | NO   |     | NULL    |                |
| baseRate                | decimal(19,2) | YES  |     | NULL    |                |
| categoryId              | char(20)      | NO   |     | NULL    |                |
| freightRate             | decimal(19,2) | YES  |     | NULL    |                |
| gstRate                 | decimal(19,2) | YES  |     | NULL    |                |
| inventoryLineItemId     | char(20)      | NO   | UNI | NULL    |                |
| inventoryLineItemStatus | varchar(255)  | NO   | MUL | NULL    |                |
| inventoryLotId          | char(20)      | NO   | MUL | NULL    |                |
| meta                    | text          | YES  |     | NULL    |                |
| namespace               | varchar(255)  | NO   | MUL | NULL    |                |
| loadUnloadCharges       | decimal(19,2) | YES  |     | NULL    |                |
| preTaxAmountPerUnit     | decimal(19,2) | YES  |     | NULL    |                |
| productId               | char(20)      | NO   | MUL | NULL    |                |
| quantity                | decimal(19,3) | YES  |     | NULL    |                |
| taxAmountPerUnit        | decimal(19,2) | YES  |     | NULL    |                |
| totalGrossValue         | decimal(19,2) | YES  |     | NULL    |                |
| unit                    | text          | NO   |     | NULL    |                |
| unitRate                | decimal(19,2) | YES  |     | NULL    |                |
| otherCharges            | decimal(19,2) | YES  |     | NULL    |                |
| normalisedProductName   | varchar(255)  | YES  |     | NULL    |                |
| revisedItem             | bit(1)        | YES  |     | NULL    |                |
+-------------------------+---------------+------+-----+---------+----------------+

索引信息 #

mysql> show index from inventoryLineItem;
+-------------------+------------+-------------------------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                                        | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-------------------------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| inventorylineitem |          0 | PRIMARY                                         |            1 | id                      | A         |        1101 |     NULL | NULL   |      | BTREE      |         |               |
| inventorylineitem |          0 | UK_4inpuip29bneflv726ko6seix                    |            1 | inventoryLineItemId     | A         |        1090 |     NULL | NULL   |      | BTREE      |         |               |
| inventorylineitem |          0 | index_inventoryLineItem_inventoryLineItemId     |            1 | inventoryLineItemId     | A         |        1090 |     NULL | NULL   |      | BTREE      |         |               |
| inventorylineitem |          1 | index_inventoryLineItem_isDeleted               |            1 | isDeleted               | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| inventorylineitem |          1 | index_inventoryLineItem_inventoryLotId          |            1 | inventoryLotId          | A         |         596 |     NULL | NULL   |      | BTREE      |         |               |
| inventorylineitem |          1 | index_inventoryLineItem_productId               |            1 | productId               | A         |         488 |     NULL | NULL   |      | BTREE      |         |               |
| inventorylineitem |          1 | index_inventoryLineItem_inventoryLineItemStatus |            1 | inventoryLineItemStatus | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| inventorylineitem |          1 | index_inventoryLineItem_namespace               |            1 | namespace               | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+-------------------------------------------------+-----

查询及EXPLAIN结果 #

mysql> EXPLAIN  SELECT * FROM inventoryLineItem where inventoryLineItemId = 728362543914946859;
+----+-------------+-------------------+------------+------+--------------------------------------------------------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys                                                            | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+--------------------------------------------------------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | inventoryLineItem | NULL       | ALL  | UK_4inpuip29bneflv726ko6seix,index_inventoryLineItem_inventoryLineItemId | NULL | NULL    | NULL | 1101 |    10.00 | Using where |
+----+-------------+-------------------+------------+------+--------------------------------------------------------------------------+------+---------+------+------+----------+-------------+

未使用索引的原因分析 #

1. 数据类型不匹配 #

inventoryLineItemId字段定义为char(20)字符串类型，但查询条件中使用的是数值728362543914946859。MySQL会对字段进行隐式类型转换，将字符串转为数值后再匹配，这会导致索引无法被利用——因为索引是基于字符串排序存储的，类型转换破坏了索引的有序性，只能走全表扫描。

2. 表数据量过小 #

表中仅约1100条数据，MySQL优化器会计算查询成本：全表扫描只需遍历少量数据，而走索引需要先查找索引节点，再回表获取数据，额外的IO开销反而更高。因此优化器会选择更高效的全表扫描。

3. 重复索引干扰 #

inventoryLineItemId上存在两个完全重复的唯一索引（UK_4inpuip29bneflv726ko6seix和index_inventoryLineItem_inventoryLineItemId），虽然不会直接导致索引失效，但可能让优化器在选择索引时出现犹豫，不过这种情况影响概率较低。

验证与解决方法 #

• 修正类型匹配：将查询条件改为字符串形式，重新执行EXPLAIN：

  EXPLAIN SELECT * FROM inventoryLineItem where inventoryLineItemId = '728362543914946859';
  

  此时应能正常使用索引。

• 强制走索引：通过FORCE INDEX强制指定索引，验证优化器是否因成本选择全表扫描：

  EXPLAIN SELECT * FROM inventoryLineItem FORCE INDEX(index_inventoryLineItem_inventoryLineItemId) where inventoryLineItemId = 728362543914946859;
  

  如果此时走索引，说明之前是优化器成本计算的结果。

• 清理重复索引：删除inventoryLineItemId上的重复索引，保留一个唯一索引即可，减少优化器的选择负担：

  DROP INDEX index_inventoryLineItem_inventoryLineItemId ON inventoryLineItem;
  

内容的提问来源于stack exchange，提问作者Umesh Patidar