【 TiDB 使用环境】测试环境
【 TiDB 版本】7.1.1
【复现路径】大sql查询
【遇到的问题：我在sql里加了hint，让表A走kv引擎。但是没生效果。是什么原因呢？
EXPLAIN
SELECT
/*+ read_from_storage(tikv[tb_uhome_pay_log]) */
CONCAT(IFNULL(DATE_FORMAT(B.bill_date_start, '%Y-%m-%d'), ''), '~', IFNULL(DATE_FORMAT(B.bill_date_end, '%Y-%m-%d'), '')) AS realCycleStartAndEnd,
A.COMMUNITY_ID AS communityId,
A.PAY_CYCLE AS payCycle,
DATE_FORMAT(A.PAY_DATE, '%Y-%m-%d') AS payDate,
DATE_FORMAT(A.CREATED_DATE, '%Y-%m-%d') AS createDate,
DATE_FORMAT(A.CREATED_DATE, '%Y-%m-%d %H:%m:%s') AS operateTime,
S.STAGE_ID AS stageId,
S.STAGE_NAME AS stageName,
H.NAME AS buildName,
H.BUILD_ID AS buildId,
B.HOUSE_ID AS houseId,
C.unit_id AS unitId,
C.UNIT AS unitName,
B.HOUSE_NAME AS houseName,
B.CUST_ID AS custId,
IFNULL(B.CUST_NAME, '业主') AS custName,
E.E_CUST_CODE AS custCode,
IF(B.receivable_date = 0 OR B.receivable_date IS NULL, '', B.receivable_date) AS receivableDate,
B.benefactor_id AS donorId,
IFNULL(df.donor_name, '') AS donorName,
A.PAY_METHOD AS payMethod,
D. `NAME` AS payMethodName,
B.BILLING_CYCLE AS billingCycle,
B.REAL_CYCLE AS realCycle,
B.FEE_ITEM_TYPE_ID AS feeItemTypeId,
B.FEE_ITEM_TYPE_NAME AS feeItemTypeName,
B.bill_rule_name AS billRuleName,
C.acct_house_code AS houseCode,
SUM(B.FEE) AS fee,
SUM(B.LFREE) AS lfree,
SUM(B.FEE) + SUM(B.LFREE) AS totalFee,
SUM(ROUND(B.TAX_FEE, 0)) + SUM(ROUND(B.LFREE_TAX_FEE, 0)) AS taxFee,
ROUND(SUM(B.inv_tax_fee) + SUM(B.inv_lfree_tax_fee), 0) AS invTaxFee,
ROUND(SUM(B.FACTORAGE), 0) AS factorage,
B.lfree_tax_rate AS lfreeTaxRate,
SUM(ROUND(B.lfree_tax_fee, 0)) AS lfreeTaxFee,
SUM(ROUND(B.inv_lfree_tax_fee, 0)) AS invLfreeTaxFee,
ROUND(SUM(B.LFREE) - SUM(B.lfree_tax_fee)) AS lfreeNotTaxFee,
ROUND(SUM(B.LFREE) - SUM(B.inv_lfree_tax_fee)) AS invLfreeNotTaxFee,
ROUND(SUM(B.FEE) - SUM(B.TAX_FEE)) AS totalNotTaxFee,
ROUND(SUM(B.FEE) - SUM(B.inv_tax_fee)) AS invTotalNotTaxFee,
SUM(ROUND(B.TAX_FEE, 0)) AS taxNotLfee,
SUM(ROUND(B.inv_tax_fee, 0)) AS invTaxNotLfee,
A.EMPEE_ID AS empeeId,
U. `NAME` AS empeeName,
A.payee_name AS payeeName,
A.TRANSACTION_NO AS transactionNo,
B.TICKET_CODE AS ticketCode,
A.PAY_SERIAL_NBR AS paySerialNbr,
A.REMARK AS remark,
A.PAYMENT_NAME AS paymentName,
if(B.balance_type = 3, '现金账本', '赠送账本') AS balanceType,
B.res_inst_name AS resInstName,
B.res_inst_code AS resInstCode,
B.tax_rate AS taxRate,
B.HOUSE_STATUS_TYPE AS houseStatusType,
B.HOUSE_STATUS AS houseStatus,
IF(pt1.category_code = 50, pt1.type_name, '') AS houseStatusName,
IF(pt2.category_code = 40, pt2.type_name, '') AS houseStatusTypeName,
X.invoice_number1 AS invoiceNumber,
CONCAT_WS(',', x.invoice_number2, x.src_invoice_number) AS originalInvoiceNumber,
B.BILL_AREA AS billArea,
B.obj_code AS contractNumber,
cst.first_name AS firstName,
cst.first_bus_name AS firstBusName,
CASE WHEN A.organ_bank_name = '' OR A.organ_bank_account = '' THEN
''
ELSE
CONCAT(A.organ_bank_name, '-', A.organ_bank_account)
END AS organBankAccount,
COUNT(DISTINCT (
CASE WHEN b.house_id IS NOT NULL
AND b.house_id > 0 THEN
b.house_Id
END)) houseCount,
C.inner_Area AS innerArea,
B.belong_res_type AS belongResType,
B.belong_res_id AS belongResId,
if(B.belong_res_type = 2, B.belong_res_name, '') AS parkingName
FROM
tb_uhome_pay_log A
INNER JOIN tb_uhome_pay_log_detail B ON A.PAY_SERIAL_NBR = B.PAY_SERIAL_NBR
LEFT JOIN bill_contract_inst cst ON B.obj_type = 8 AND B.obj_id = cst.contract_id AND cst.contract_id > 0
LEFT JOIN TB_UHOME_DICTIONARY D ON D. `CODE` = 'PAY_LOG_PAY_METHOD' AND D. `VALUE` = A.PAY_METHOD
LEFT JOIN TB_UHOME_HOUSE C ON B.HOUSE_ID = C.HOUSE_ID
LEFT JOIN TB_UHOME_BUILD H ON C.BUILD_ID = H.BUILD_ID
LEFT JOIN TB_UHOME_STAGE S ON S.STAGE_ID = H.STAGE_ID
LEFT JOIN CUSTOMER E ON E.CUST_ID = B.CUST_ID
LEFT JOIN bill_donor_file df ON df.donor_id = B.benefactor_id
LEFT JOIN TB_UHOME_FEE_ITEM_TYPE F ON F.FEE_ITEM_TYPE_ID = B.FEE_ITEM_TYPE_ID
LEFT JOIN TB_UHOME_ADMIN_USER U ON U.USER_ID = A.EMPEE_ID
LEFT JOIN (
SELECT
category_code,
type_code,
type_name
FROM
v_profession_type) pt1 ON pt1.type_code = B.house_status
LEFT JOIN (
SELECT
category_code,
type_code,
type_name
FROM
v_profession_type) pt2 ON pt2.type_code = B.house_status_type
LEFT JOIN (
SELECT
X1.pay_detail_id,
GROUP_CONCAT(DISTINCT IF(X2.invoice_status IN(10, 11), X2.invoice_number, NULL)) AS invoice_number1,
GROUP_CONCAT(DISTINCT IF(X2.invoice_status IN(20, 21, 22), X2.invoice_number, NULL)) AS invoice_number2,
GROUP_CONCAT(DISTINCT IF(X2.invoice_status IN(20, 21, 22), X2.src_invoice_number, NULL)) AS src_invoice_number
FROM
bill_invoice_log_detail X1,
bill_invoice_log X2
WHERE
X1.invoice_id = X2.invoice_id AND X2.ORGAN_ID = 13931131 AND X2.invoice_status IN(10, 11, 20, 21, 22)
GROUP BY
X1.pay_detail_id) X ON X.pay_detail_id = B.pay_detail_id
where A.COMMUNITY_ID = 13931131 AND A.PAY_DATE >= '2023-01-01 00:00:00' AND A.PAY_DATE <= '2023-09-04 23:59:59' AND A.pay_method NOT IN(14, 98) AND A.state NOT in('COR', 'AOK')
GROUP BY
communityId,
stageId,
buildName,
unitName,
houseName,
houseCode,
billArea,
innerArea,
custId,
donorId,
billingCycle,
feeItemTypeId,
billRuleName,
payMethod,
payCycle,
payDate,
createDate,
paymentName,
empeeId,
payeeName,
ticketCode,
transactionNo,
resInstName,
paySerialNbr,
invoiceNumber,
houseStatusType,
houseStatus,
realCycle,
realCycleStartAndEnd,
contractNumber,
firstName,
firstBusName,
organBankAccount,
originalInvoiceNumber,
custCode,
receivableDate,
parkingName,
taxRate
LIMIT 50;
分析结果：全是走了tiflash
【资源配置】3台8核，16G
