概述
今天開發反饋同樣的sql且數據量一致,但在綜測環境需要140多秒,而在開發環境只需要1秒多,這是什麼原因呢?下面一起來看看吧~
問題sql
<code>SELECT ty.IS_FIXATION AS isFixation,CASE WHEN ty.INSPECTION_RULES IS NOT NULL AND ty.IS_INSPECTION = 'Y' THEN '未打卡' ELSE NULL END AS inspectionStatus,
FROM t_ams_asset_card t LEFT JOIN T_AMS_ASSET_TYPE ty ON ty.TYPE_NO = t.ASSET_TYPE AND t.FINANCE_CODE = ty.ORG_CODE
LEFT JOIN hr_employee e ON t.USE_MAN = e.EMPLOYEE_CODE
WHERE t.ASSET_MODULE = 'OWN' AND t.IS_DELETE = 'N'/<code>
對比:
思路:這種情況一般是沒有索引或者索引失效導致。
1、對比執行計劃
這裡通過explain + sql查看執行計劃
2、檢查兩邊環境索引情況
可以發現兩邊環境都有索引,那應該就是某個索引失效了..
3、強制使用索引
這裡試一下 force index(HR_EMPLOYEE_U1) ,發現強制使用索引也是失效的,在這裡索引為什麼會失效呢?
4、檢查表存儲引擎、表字段數據類型、表字段字符集
一般索引失效都是表的存儲引擎、字段數據類型或者字符集不同導致走的隱性轉換
<code>show full columns from hr_employee;
show full columns from t_ams_asset_card;/<code>
5、修改字符集編碼後測試
<code>SELECT
\tc.TABLE_SCHEMA '數據庫',
\tc.TABLE_NAME '表',
\tc.COLUMN_NAME '字段',
\tc.COLUMN_DEFAULT '默認值',
\tc.IS_NULLABLE '是否為空',
\tc.DATA_TYPE '字段類型',
\tc.character_set_name '原字符集',
\tc.collation_name '原排序規則',
\tCONCAT(
\t\t'ALTER TABLE ',
\t\tTABLE_SCHEMA,
\t\t'.',
\t\tTABLE_NAME,
\t\t' MODIFY COLUMN ',
\t\tCOLUMN_NAME,
\t\t' ',
\t\tCOLUMN_TYPE,
\t\t' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',
\tCASE
\t\t\t
\t\t\tWHEN c.is_nullable = 'NO' THEN
\t\t\t'NOT NULL' ELSE 'NULL'
\t\tEND,
\tCASE
\t\t\t
\t\t\tWHEN c.COLUMN_DEFAULT = '' THEN
\t\t\t' DEFAULT '''''
\t\t\tWHEN c.COLUMN_DEFAULT IS NULL THEN
\t\t\t' DEFAULT NULL' ELSE concat( ' DEFAULT ', '''', c.COLUMN_DEFAULT, '''' )
\t\tEND,
\t\t' comment ',
\t\t'''',
\t\tc.COLUMN_COMMENT,
\t\t'''',
\t\t';'
) '修正SQL'
FROM
\tinformation_schema.`COLUMNS` c
WHERE
\ttable_name = 'hr_employee' and table_schema='lcp_db'
\tAND CHARACTER_SET_NAME = 'utf8';/<code>
6、再次查看執行計劃
調整後發現已使用索引,且sql執行也在1秒內,滿足需求。
覺得有用的朋友多幫忙轉發哦!後面會分享更多devops和DBA方面的內容,感興趣的朋友可以關注下~
閱讀更多 波波說運維 的文章