導讀
本文從遇到的問題出發，分析了tddl優化器、MySQL索引、分表拆分鍵的選擇相關知識。
背景
最近由于應用需要進行多租戶改造，對監控盯的較緊。發現了應用的一些問題，應用的hsf consumer成功率，不是一直都是100，偶爾出現99.99的情況，進一步查應用日志發現，是因為慢sql導緻服務逾時失敗。
定位到具體語句
(
select
`iop_xxx_msg`.`id`,
`iii_sss_msg`.`message_id`,
`iii_sss_msg`.`title`,
`iii_sss_msg`.`content`,
`iii_sss_msg`.`id_seller`,
`iii_sss_msg`.`id_user`,
`iii_sss_msg`.`gmt_create`,
`iii_sss_msg`.`gmt_modified`,
`iii_sss_msg`.`is_read`,
`iii_sss_msg`.`category`,
`iii_sss_msg`.`sub_category`,
`iii_sss_msg`.`description`,
`iii_sss_msg`.`need_side_notice`,
`iii_sss_msg`.`link_url`,
`iii_sss_msg`.`btn_name`,
`iii_sss_msg`.`gmt_create_l`,
`iii_sss_msg`.`mobile_content`,
`iii_sss_msg`.`tier`,
`iii_sss_msg`.`requirement_id`,
`iii_sss_msg`.`fk_template_id`,
`iii_sss_msg`.`business_part`,
`iii_sss_msg`.`business_id`
from
`iii_sss_msg_29` `iii_sss_msg`
WHERE
(
(
(
`iii_sss_msg`.`gmt_create` >= '2023-07-24 00:00:00'
)
AND (
`iii_sss_msg`.`gmt_create` < '2023-07-31 15:46:45.684'
)
AND (`iii_sss_msg`.`id_user` = 500173482096)
AND (`iii_sss_msg`.`tier` IN ('S', 'A'))
AND (
`iii_sss_msg`.`sub_category` IN (1000305, 1000306, 1000501, 1000502)
)
)
OR (
(`iii_sss_msg`.`category` IN (10003, 10005))
AND (
`iii_sss_msg`.`gmt_create` >= '2023-07-24 00:00:00'
)
AND (
`iii_sss_msg`.`gmt_create` < '2023-07-31 15:46:45.684'
)
AND (`iii_sss_msg`.`id_user` = ***)
AND (
`iii_sss_msg`.`sub_category` IN (1000305, 1000306, 1000501, 1000502)
)
)
)
order by
`iii_sss_msg`.`gmt_create` desc
limit
0, 5
)union all ... 語句較為複雜，概括來講sql語句的含義是找到七天内某個商家的某幾個類目或者tier='S' 的最近的五條消息。由iii_sss_msg表根據建立時間分了31張表，路由規則如下：
<property name="tbRuleArray">
<value>"iii_sss_msg_" + getCalendar(#gmt_create,1_date,31#).get(Calendar.DAY_OF_MONTH)</value>
</property> 是以該查詢連接配接了七張表iii_sss_msg_29、iii_sss_msg_30....(該分表規則極不合理，本文之後分析)
我們看看mybatis對應的xml檔案是什麼樣的。
<select id="selectByQuery" resultMap="webMsgResultMap" parameterType="map" >
select
<include refid="Base_Column_List" />
from iii_sss_msg
where
id_user = #{userId}
<if test="startTime != null">
and gmt_create <![CDATA[>=]]> #{startTime}
</if>
<if test="endTime != null">
and gmt_create <![CDATA[<]]> #{endTime}
</if>
... 發現mybatis對應的sql語句where條件的排序與我們最後看到的sql語句的并不一樣。是有什麼改變了這個語句？
TDDL優化器
我們的應用使用了tddl，在mybatis生成了sql後還會被tddl更改，tddl的工作流程如下，在Matrix層會對mysql進行解析與優化。（詳細的tddl知識請讀者自行查閱相關知識）
問題解答了，是tddl改變了這個語句，目的是優化查詢。那目的達到了嗎？
我們繼續進行分析，該表的索引資訊如下：
發現該表有個名為idx_user的索引，該索引是gmt_create,id_user,category,sub_category四個字段的聯合索引。這幾個字段剛好都在上面慢sql的where條件中。看起來tddl調整sql語句的順序是為了利用該索引。
我們用執行計劃分析下該條語句，結果如下：
發現用的索引并不是idx_user（gmt_create,id_user,category,sub_category），而是idx_uer_query（id_user,category）。看起來tddl自作多情了。那為什麼mysql選擇的是idx_user_query（id_user,category）索引呢？
為了驗證這個問題我們使用
force index（idx_user（gmt_create,id_user,
category,sub_category））強制使用idx_user索引。
對比兩個執行計劃發現，使用idx_uer_query（id_user,category）索引的掃描行數是13948，但是使用idx_user
（gmt_create,id_user,category,sub_category）
的索引的掃描行數是1552218，掃描行數是前者的100多倍！這個索引用了個寂寞，幸好mysql沒聽信tddl的讒言。那mysql怎麼知道使用idx_user索引會更好？
mysql如何選擇索引
選擇索引是優化器的工作。而優化器選擇索引的目的，是找到一個最優的執行方案，并用最小的代價去執行語句。優化器主要會根據以下條件考慮：
1.查詢語句中的條件：MySQL 會根據查詢語句中的條件選擇最合适的索引，以盡可能快地定位符合條件的行。如果查詢條件包括多個列，那麼可以考慮建立多列索引，以便在比對時更高效。
2.索引的選擇性（基數）：MySQL 會根據索引的選擇性來選擇最合适的索引。選擇性是指索引列中不同值的個數與表中記錄總數的比值。選擇性越高，索引的效率越高。
3.索引的大小和資料類型：索引的大小和資料類型也會影響索引的選擇。較小的索引通常比較大的索引效率更高，而資料類型的不同也會影響索引的效率。
4.資料塊的大小：MySQL 使用資料塊（或稱為頁）來存儲索引資料和表資料，資料塊的大小也會影響索引選擇。較小的資料塊能夠提高緩存的效率，并減少磁盤 I/O 操作的次數。
5.索引的覆寫度：如果一個查詢可以使用覆寫索引來滿足，則 MySQL 通常會優先選擇使用覆寫索引。覆寫索引是指查詢語句中需要的列都包含在索引中，不需要再到表中讀取資料。
如果按照1、5點當然選擇idx_user（gmt_create,id_user,category,sub_category）更好，但是第二點呢？
我們使用：
SHOW INDEX FROM `iii_sss_msg_29` 分析兩個索引的基數：
發現idx_user（gmt_create,id_user,category,sub_category）的gmt_create列的基數為99933，idx_uer_query（id_user,category）的id_user的基數為286528優于前者。
綜合考慮，是以優化器選擇idx_uer_query（id_user,category）索引。
MySQL會選錯索引嗎？
上面我們分析的兩個索引的基數差距并不是很大（優化器也可能預估錯誤）,然而idx_user（gmt_create,id_user,category,sub_category）索引在查詢語句中的條件中的比對度更高和使用索引idx_user（gmt_create,id_user,category,sub_category）可以避免排序（idx_user（gmt_create,id_user,category,sub_category）本身是索引，已經是有序的了，如果選擇索引idx_user（gmt_create,id_user,category,sub_category）的話，不需要再做排序，隻需要周遊），是以即使掃描行數多，也判定為代價更小。會有這樣的情況發生嗎？
多次執行，看看其他分表的執行計劃，發現優化器這次使用了idx_user索引！tddl優化器和索引idx_user（gmt_create,id_user,category,sub_category）裡應外合誤使優化器選擇了錯誤的索引。選擇了一百多萬的掃描行數的執行方案。
使用索引
idx_user（gmt_create,id_user,category,sub_category）需要執行995毫秒：
使用索引idx_uer_query（id_user,category）需要執行95毫秒：
這可能就是慢sql的原因了：tddl優化器選擇了錯誤的索引。
索引該怎麼建？
我們再來看看下面這個索引：
idx_user（gmt_create,id_user,category,sub_category）為什麼建立這個索引？大概是因為業務需求，有以上的條件查詢的語句較多。但是該索引合理嗎？
上圖是筆者畫的idx_user（gmt_create,id_user,category,sub_category）索引的B+樹結構圖。可以看到B+樹隻維護第一列gmt_create的有序性，其他字段的有序性沒有維護。由該表的分表規則可知iii_sss_msg_29的建立時間都在（2023-07-29 00:00:00-2023-07-29 23:59:59）之間。
結合之前的sql語句的where條件：
WHERE
(
(
(
`iii_sss_msg`.`gmt_create` >= '2023-07-24 00:00:00'
)
AND (
`iii_sss_msg`.`gmt_create` < '2023-07-31 15:46:45.684'
) 可知若使用該索引，idx_user所有的葉子結點都會掃描一遍！之前強制使用該索引查詢時掃描行數一百多萬，主鍵id索引的基數三百多萬，兩者是同一量級。
idx_user（gmt_create,id_user,category,sub_category）索引在上述sql的應用場景是不合理的，特别是在按建立時間的分表規則下。筆者将其改為idx_user（id_user,category,sub_category，gmt_create，is_read）(因為是否已讀也常見于業務條件中，是以加上)。更改之後，再次檢視sql語句的執行計劃，發現這次優化器堅定的選擇了idx_user（id_user,category,sub_category，gmt_create，is_read）索引。這次掃描行數隻有一萬多行。與idx_uer_query（id_user,category）索引相比，該索引還能減少回表次數。
mysql的索引不是剛建表的時候就能建立完全的。索引是為了加快查詢資料，與業務場景強相關。是以索引要根據業務查詢做相應的變化。不合理的索引反而會妨礙查詢，誤導優化器。
分表拆分鍵如何選擇
iii_sss_msg表根據gmt_create進行分表，該拆分鍵是合理的嗎？
拆分鍵的選擇重點應該考慮：
1.能夠最大限度的實作資料均勻分布到每個實體分表上去，即能夠實作負載均衡實作均勻拆分其實最好的就是自增的主鍵取模。但是可能主鍵不是自增，甚至不是數字就不一定能夠實作絕對的均勻分布了。
2.拆分鍵不可為空，必須要有索引。
3.盡可能的實作所有的查詢sql的where語句後都能夠帶上這個字段，如果做不到也要有方式能夠路由到具體的實體表。
4.要注意将相關聯的一系列資料拆分到同一個表中。
查七天内某個商家的一些類目的消息是我們應用最常見的場景，但是iii_sss_msg消息表卻根據gmt_create進行分表，頻繁觸發聯表查詢，每次查詢要從七張表裡面取資料，那如果以後要看一個月内的消息，那得從三十張表裡面取資料。如此看來該拆分鍵極不合理。根據業務場景來看，按照id_user作為拆分鍵是更佳選擇。（該改動較為費時，待後期排期解決QAQ）
總結
本文從遇到的問題出發，分析了tddl優化器、MySQL索引、分表拆分鍵的選擇相關知識，以下是知識總結：
1.tlld在Matrix層會對mysql進行解析與優化。
2.選擇索引是優化器的工作。而優化器選擇索引的目的，是找到一個最優的執行方案，并用最小的代價去執行語句。優化器主要會根據以下條件考慮：查詢語句中的條件、索引的選擇性（基數）、索引的大小和資料類型、資料塊的大小、索引的覆寫度。
3.mysql優化器存在選錯索引的可能性。對于優化器誤判的情況，可以在應用端用force index來強行指定索引，也可以通過修改語句來引導優化器，還可以通過增加或者删除索引來繞過這個問題。
4.拆分鍵的選擇重點應該考慮：能夠最大限度的實作資料均勻分布到每個實體分表上去、拆分鍵不可為空，必須要有索引、盡可能的實作所有的查詢sql的where語句後都能夠帶上這個字段、要注意将相關聯的一系列資料拆分到同一個表中。
以上知識如果想詳細了解，可以查閱相關資料。
作者:成誠
來源-微信公衆号:阿裡雲開發者
出處:https://mp.weixin.qq.com/s/bxqw0Arey3Qia4jjmgmCtQ