mysql - mysql索引未在查询中使用
问题描述
我有一个 MySQL 查询，
select u.*
from `usage` as u
use index (index4,index7)
where u.IsDeleted = 0
and (
(p_fromDate is null and p_toDate is null) or
(p_fromDate is not null and p_toDate is not null and
DATE(CONCAT(
convert(u.`Year`, char(4)),
'-',
convert(u.`Month`, char(2)), '-1'
)) between p_fromDate and p_toDate
) or
(
p_fromDate is not null and
p_toDate is not null and
u.`Month` is null and
u.`Year` between Year(p_fromDate) and Year(p_toDate)
)
)
我想添加索引以提高性能，我添加了两个索引。
usage在(IsDeleted)上创建索引 index4 ；
上面一个是 for u.IsDeleted = 0，我可以看到用在EXPLAIN.
我创建的以下一个是我希望Year可以在where子句的第二次评估中使用
create index index7 on用法(Year);
这是该where条款的第二次评估
(p_fromDate is null and p_toDate is null) or
(
p_fromDate is not null and p_toDate is not null
and DATE(CONCAT(
convert(u.`Year`, char(4)),
'-',
convert(u.`Month`, char(2)),
'-1'
)) between p_fromDate and p_toDate
) or
(
p_fromDate is not null and p_toDate is not null
and u.`Month` is null
and u.`Year` between Year(p_fromDate) and Year(p_toDate)
)
但在EXPLAIN
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, u, , ref, index4,index7, index4, 1, const, 37547, 100.00, Using where
可以看到只使用了 index4，year第二个条件没有使用 index7，我可以在 where 子句中让查询使用第二个条件的索引吗？
样本数据
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| id | LocationID | IndicatorID | Year | Month | HalfYear1 | HalfYear2 | FourMonths1 | FourMonths2 | FourMonths3 | Q1 | Q2 | Q3 | Q4 | BiMonthly1 | BiMonthly2 | BiMonthly3 | BiMonthly4 | BiMonthly5 | BiMonthly6 | DateOfUsage | Price | PriceUnit | ExchangeRate | Value | ValueUnit | Remarks | CreatedBy | CreatedDate | ModifiedBy | ModifiedDate | IsDeleted | IsHeatRecovery |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 1 | 33 | 17 | 2016 | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 2 | 33 | 15 | 2016 | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 3 | 33 | 653 | 2016 | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 4 | 34 | 17 | 2016 | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 5 | 34 | 15 | 2016 | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 6 | 34 | 653 | 2016 | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 7 | 33 | 17 | 2016 | 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 8 | 33 | 15 | 2016 | 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 9 | 33 | 653 | 2016 | 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
| 10 | 34 | 17 | 2016 | 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | kg | NULL | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | john.smith@hkg.fujixerox.com | 2017-07-11 17:23:28 | 0 | 0 |
+----+------------+-------------+------+-------+-----------+-----------+-------------+-------------+-------------+------+------+------+------+------------+------------+------------+------------+------------+------------+-------------+-------+-----------+--------------+-------+-----------+---------+------------------------------+---------------------+------------------------------+---------------------+-----------+----------------+
运行原始存储过程的结果
CREATE DEFINER=`root`@`%` PROCEDURE `SelectUsage4`(
IN p_ids MEDIUMTEXT
, IN p_locationIDs MEDIUMTEXT
, IN p_indicatorIDs MEDIUMTEXT
, IN p_fromDate date
, IN p_toDate date
, IN p_yearly tinyint(4)
, IN p_monthly tinyint(4)
, IN p_halfYear1 tinyint(4)
, IN p_halfYear2 tinyint(4)
, IN p_fourMonths1 tinyint(4)
, IN p_fourMonths2 tinyint(4)
, IN p_fourMonths3 tinyint(4)
, IN p_q1 tinyint(4)
, IN p_q2 tinyint(4)
, IN p_q3 tinyint(4)
, IN p_q4 tinyint(4)
, IN p_biMonthly1 tinyint(4)
, IN p_biMonthly2 tinyint(4)
, IN p_biMonthly3 tinyint(4)
, IN p_biMonthly4 tinyint(4)
, IN p_biMonthly5 tinyint(4)
, IN p_biMonthly6 tinyint(4)
, IN p_approvalStatus int(11)
, IN p_language nvarchar(10)
)
BEGIN
explain select
case when (select count(lbl.id) from `labels` as lbl where lbl.ObjectID = l.id and lbl.ObjectName = 'locations' and lbl.ColumnName = 'LocationName' and lbl.LanguageCode = p_language) > 0 then
(select content from `labels` as lbl where lbl.ObjectID = l.id and lbl.ObjectName = 'locations' and lbl.ColumnName = 'LocationName' and lbl.LanguageCode = p_language limit 1)
else
l.LocationName
end as LocationName
, l.ParentID as LocationParentID
, l.Active as LocationActive
, l.RegionID
, case when (select count(lbl.id) from `labels` as lbl where lbl.ObjectID = i.id and lbl.ObjectName = 'indicators' and lbl.ColumnName = 'IndicatorName' and lbl.LanguageCode = p_language) > 0 then
(select content from `labels` as lbl where lbl.ObjectID = i.id and lbl.ObjectName = 'indicators' and lbl.ColumnName = 'IndicatorName' and lbl.LanguageCode = p_language limit 1)
else
i.IndicatorName
end as IndicatorName
, i.ParentID as IndicatorParentID
, i.Unit
, i.DecimalPlaces
, i.Active as IndicatorActive
, i.IndicatorType
, u.*
from
`usage` as u
left join `locations` as l on u.LocationID = l.id
left join `Indicators` as i on u.IndicatorID = i.id
where
u.IsDeleted = 0
and (
(p_fromDate is null and p_toDate is null)
or
(
p_fromDate is not null and p_toDate is not null
and
DATE(CONCAT(convert(u.`Year`, char(4)), '-', convert(u.`Month`, char(2)), '-1')) between p_fromDate and p_toDate
)
or
(
p_fromDate is not null and p_toDate is not null
and
u.`Month` is null
and
u.`Year` between Year(p_fromDate) and Year(p_toDate)
)
)
and (p_yearly is null or (p_yearly is not null and p_yearly = 1 and u.`Month` is null) or (p_yearly is not null and p_yearly = 0 and u.`Month` is not null))
and (p_monthly is null or (p_monthly is not null and p_monthly = 1 and u.`Month` is not null))
and (p_ids is null or FIND_IN_SET(u.id, p_ids))
and (p_locationIDs is null or FIND_IN_SET(u.LocationID, p_locationIDs))
and (p_indicatorIDs is null or FIND_IN_SET(u.IndicatorID, p_indicatorIDs))
and
(
(p_halfYear1 is null or u.HalfYear1 = p_halfYear1)
or (p_halfYear2 is null or u.HalfYear2 = p_halfYear2)
)
and
(
(p_fourMonths1 is null or u.FourMonths1 = p_fourMonths1)
or (p_fourMonths2 is null or u.FourMonths2 = p_fourMonths2)
or (p_fourMonths3 is null or u.FourMonths3 = p_fourMonths3)
)
and
(
(p_q1 is null or u.Q1 = p_q1)
or (p_q2 is null or u.Q2 = p_q2)
or (p_q3 is null or u.Q3 = p_q3)
or (p_q4 is null or u.Q4 = p_q4)
)
and
(
(p_biMonthly1 is null or u.BiMonthly1 = p_biMonthly1)
or (p_biMonthly2 is null or u.BiMonthly2 = p_biMonthly2)
or (p_biMonthly3 is null or u.BiMonthly3 = p_biMonthly3)
or (p_biMonthly4 is null or u.BiMonthly4 = p_biMonthly4)
or (p_biMonthly5 is null or u.BiMonthly5 = p_biMonthly5)
or (p_biMonthly6 is null or u.BiMonthly6 = p_biMonthly6)
)
and (
p_approvalStatus is null
or
(
select ara.ApprovalStatus
from `tasks_details` as t
inner join `approval_request_tasks` as art on t.TaskID = art.TaskID
inner join `approval_request_approvers` as ara on art.ApprovalRequestID = ara.ApprovalRequestID
where
t.IsDeleted = 0
and
t.ObjectID = u.id
and t.ObjectType = 'Usage'
order by
ara.ModifiedDate desc limit 1
) = p_approvalStatus
)
order by
i.IndicatorName, l.LocationName
;
END
这是结果
解决方案
推荐阅读
- javascript - 如何使用 react.js 从 RESTAPI 获取数据
- c# - 如何在 AppCenter SDK for C# 中为 TrackError 注册事件处理程序？
- c# - 将表格元素加载到组合框中
- javascript - fs.opensync 不是函数
- codenameone - 代号一中的Android清单合并错误
- html - 将 Excel 文件内容显示为 HTML 表格 Angular
- c - GCC 中 strftime 的自定义语言环境字符串
- javascript - 是否保证在客户端智能支付按钮 .then() 之后会触发 webhook？
- mysql - MySQL 使用 leftjoin 和 select
- multicast - Wireshark 如何在不加入多播组的情况下捕获多播流量