MySQL索引查询条件使用函数导致索引失效

 

索引失效

EXPLAIN
SELECT
    dt.name AS deviceName,
    su.`name` AS userName,
    date_format(co.upload_time, '%Y%m%d') as time
FROM
    tb_cust_order co
INNER JOIN sys_user su ON co.work_id = su.id
INNER JOIN tb_device d ON d.id=JSON_EXTRACT(co.use_devices -> '$[0].tbDeviceId','$[0]')
INNER JOIN tb_device_type dt ON dt.id=d.device_type_id
WHERE
    co.unit_id = 1498576617260576769
AND co.`status` = 'SUCCESS'
AND co.upload_time >= '2022-08-01 00:00:00'
AND co.upload_time <= '2022-08-31 59:59:59'
AND JSON_EXTRACT(co.use_devices -> '$[0].tbDeviceId','$[0]') is NOT NULL

 

 

索引生效：加上concat()函数

EXPLAIN
SELECT
    dt.name AS deviceName,
    su.`name` AS userName,
    date_format(co.upload_time, '%Y%m%d') as time
FROM
    tb_cust_order co
INNER JOIN sys_user su ON co.work_id = su.id
INNER JOIN tb_device d ON d.id=CONCAT(JSON_EXTRACT(co.use_devices -> '$[0].tbDeviceId','$[0]'))
INNER JOIN tb_device_type dt ON dt.id=d.device_type_id
WHERE
    co.unit_id = 1498576617260576769
AND co.`status` = 'SUCCESS'
AND co.upload_time >= '2022-08-01 00:00:00'
AND co.upload_time <= '2022-08-31 59:59:59'
AND JSON_EXTRACT(co.use_devices -> '$[0].tbDeviceId','$[0]') is NOT NULL

 

 

 

解决办法：

id=JSON_EXTRACT(co.use_devices -> '$[0].tbDeviceId','$[0]')
慢查询原因：函数会导致索引失效
解决方案：包一层concat()  
id=CONCAT(JSON_EXTRACT(co.use_devices -> '$[0].tbDeviceId','$[0]'))