使用MariaDB 10.3.28。我要加入4张桌子。
- 查询优化器正确地为3个表使用索引，并扫描主表表#1。查询周期为6秒。
- 我在表#1的新版本中添加了一些列，其中没有一个列在查询中使用，并且重命名了一些列。现在我的查询只使用了第4个表上的索引，导致了糟糕的性能。查询需要63分钟!
- 我比较了这些表，注意到旧的表使用
latin1character_set和latin1_swedish_ci排序。 - 新表使用
utf8字符集和utf8_general_ci排序。
是否有可能从表#1连接的键字段，这是一个varchar(20)，没有得到优化，使用索引与新表#1由于不同的字符集和排序?其他3个表使用与旧表#1 (latin1,latin1_swedish_ci)相同的设置。
下面是2个已解释的查询。旧表#1:
EXPLAIN SELECT DISTINCT
h.dataareaid,
h.warehouseid,
h.itemno,
h.qoh,
p.eau,
h.fifo_cost,
(h.qoh * h.fifo_cost) AS value,
((p.cuft_inner / p.pcs_inner) * h.qoh) AS cuft,
p.status,
CASE WHEN (p.status = 'P'
OR p.isprivatelabel = 1
OR p.sales_category_id = 7
OR ph.smoothie_enabled != 1
OR pe.firstsold > CURDATE() - INTERVAL 1 YEAR
OR p.added > CURDATE() - INTERVAL 9 MONTH) THEN
0
ELSE
GREATEST(h.qty_avail - p.sold - p.allocated_qty - p.eau, 0)
END AS excess_qty,
CASE WHEN (p.status = 'P'
OR p.isprivatelabel = 1
OR p.sales_category_id = 7
OR ph.smoothie_enabled != 1
OR pe.firstsold > CURDATE() - INTERVAL 1 YEAR
OR p.added > CURDATE() - INTERVAL 9 MONTH) THEN
0
ELSE
GREATEST((h.qty_avail - p.sold - p.allocated_qty - p.eau) * h.fifo_cost, 0)
END AS excess_value
FROM
fiat.parts_warehouse_items h
LEFT JOIN fiat.parts p ON p.itemno = h.itemno
LEFT JOIN fiat.parts_ext pe ON pe.itemno = h.itemno
LEFT JOIN internal.parts_history ph ON ph.itemno = h.itemno
AND ph.date = CURRENT_DATE
WHERE
h.warehouseid != 'AllBlank'
GROUP BY
h.dataareaid,
h.warehouseid,
h.itemno;
+------+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | h | ALL | NULL | NULL | NULL | NULL | 59644 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY,itemno_category_class,itemstatus,itemstatuscolordescription,itemcolordescription,marketing,itemnodescription,itemnodescriptionupc,itemprices | PRIMARY | 32 | fiat.h.itemno | 1 | Using where |
| 1 | SIMPLE | pe | eq_ref | PRIMARY,itemno | PRIMARY | 20 | fiat.h.itemno | 1 | Using where |
| 1 | SIMPLE | ph | eq_ref | PRIMARY,date,itemno,itemno_2,date_2,eau_dollars | PRIMARY | 50 | const,fiat.h.itemno | 1 | Using where |
+------+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------+-------+----------------------------------------------+
新建表#1:
EXPLAIN SELECT DISTINCT
h.dataareaid,
h.warehouse_id AS warehouseid,
h.sku AS itemno,
h.qty_onhand AS qoh,
p.eau,
p.fifo_cost,
(h.qty_onhand * p.fifo_cost) AS value,
((p.cuft_inner / p.pcs_inner) * h.qty_onhand) AS cuft,
p.status,
CASE WHEN (p.status = 'P'
OR p.isprivatelabel = 1
OR p.sales_category_id = 7
OR ph.smoothie_enabled != 1
OR pe.firstsold > CURDATE() - INTERVAL 1 YEAR
OR p.added > CURDATE() - INTERVAL 9 MONTH) THEN
0
ELSE
GREATEST(h.qty_total_available - p.sold - p.allocated_qty - p.eau, 0)
END AS excess_qty,
CASE WHEN (p.status = 'P'
OR p.isprivatelabel = 1
OR p.sales_category_id = 7
OR ph.smoothie_enabled != 1
OR pe.firstsold > CURDATE() - INTERVAL 1 YEAR
OR p.added > CURDATE() - INTERVAL 9 MONTH) THEN
0
ELSE
GREATEST((h.qty_total_available - p.sold - p.allocated_qty - p.eau) * p.fifo_cost, 0)
END AS excess_value
FROM
lancia.warehouse_inventory AS h
LEFT JOIN fiat.parts p ON p.itemno = h.sku
LEFT JOIN fiat.parts_ext pe ON pe.itemno = h.sku
LEFT JOIN internal.parts_history ph ON ph.itemno = h.sku AND ph.date = CURRENT_DATE
WHERE
h.warehouse_id != 'AllBlank'
GROUP BY
h.dataareaid, h.warehouse_id, h.sku;
+------+-------------+-------+------+---------------------+---------+---------+-------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------------+---------+---------+-------+--------+--------------------------------------------------------+
| 1 | SIMPLE | h | ALL | NULL | NULL | NULL | NULL | 63068 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 115282 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | pe | ALL | NULL | NULL | NULL | NULL | 120843 | Using where; Using join buffer (incremental, BNL join) |
| 1 | SIMPLE | ph | ref | PRIMARY,date,date_2 | PRIMARY | 3 | const | 1 | Using where |
+------+-------------+-------+------+---------------------+---------+---------+-------+--------+--------------------------------------------------------+
旧表1 schema:
mysql> desc fiat.parts_warehouse_items;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| itemno | varchar(50) | NO | PRI | | |
| dataareaid | varchar(10) | NO | PRI | NULL | |
| warehouseid | varchar(20) | NO | PRI | NULL | |
| qoh | int(11) | YES | | NULL | |
| qty_avail | int(11) | YES | | NULL | |
| qty_posted | int(11) | YES | | NULL | |
| qty_received | int(11) | YES | | NULL | |
| qty_deducted | int(11) | YES | | NULL | |
| qty_picked | int(11) | YES | | NULL | |
| fifo_cost | double(10,5) | YES | | NULL | |
| calculation | text | YES | | NULL | |
| method | int(11) | YES | | NULL | |
| qty_res_oh | int(11) | YES | | 0 | |
| qty_avail_oh | int(11) | YES | | 0 | |
| qty_ordrd | int(11) | YES | | 0 | |
| qty_res_ordrd | int(11) | YES | | 0 | |
| qty_avail_ordrd | int(11) | YES | | 0 | |
| qty_on_order | int(11) | YES | | 0 | |
+-----------------+--------------+------+-----+---------+-------+
新建表#1 schema:
mysql> desc lancia.warehouse_inventory;
+---------------------------+------------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+------------------------+------+-----+---------------------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| sku | varchar(20) | NO | MUL | NULL | |
| warehouse_id | varchar(10) | NO | | NULL | |
| site_id | varchar(10) | NO | | NULL | |
| item_name | varchar(60) | NO | | NULL | |
| item_color | varchar(10) | YES | | NULL | |
| item_config | varchar(50) | YES | | NULL | |
| item_size | varchar(10) | YES | | NULL | |
| item_style | varchar(10) | YES | | NULL | |
| item_version | varchar(10) | YES | | NULL | |
| item_status | varchar(10) | NO | | NULL | |
| qty_onhand | decimal(32,6) unsigned | NO | | 0.000000 | |
| qty_onhand_reserved | decimal(32,6) unsigned | NO | | 0.000000 | |
| qty_onhand_available | decimal(32,6) unsigned | NO | | 0.000000 | |
| qty_ordered | decimal(32,6) unsigned | NO | | 0.000000 | |
| qty_ordered_reserved | decimal(32,6) unsigned | NO | | 0.000000 | |
| qty_ordered_available | decimal(32,6) unsigned | NO | | 0.000000 | |
| qty_on_order | decimal(32,6) unsigned | NO | | 0.000000 | |
| qty_total_available | decimal(32,6) | NO | | 0.000000 | |
| warehouse_management_used | tinyint(1) unsigned | NO | | 1 | |
| dataareaid | varchar(4) | NO | | NULL | |
| created_at | timestamp | NO | | current_timestamp() | |
+---------------------------+------------------------+------+-----+---------------------+----------------+
是。当以相等方式连接时，on子句中两个表中的两列应该具有恰好相同的数据类型，包括字符集和排序。排序被嵌入到索引中。