.jpg)
多列排序在mysql中通過(guò)order by子句實(shí)現(xiàn)，可指定多個(gè)列及其排序方向（asc或desc），如select * from 表名 order by 列1 asc, 列2 desc; 聯(lián)合索引提升性能需滿足最左前綴原則，即索引順序與order by列順序一致或前綴匹配；優(yōu)化技巧包括：1. 索引順序應(yīng)與排序列順序一致；2. mysql 8.0+支持降序索引，可創(chuàng)建方向匹配的索引；3. 使用覆蓋索引避免回表查詢；4. 用explain檢查是否使用索引排序，避免文件排序；5. 區(qū)分度高的列優(yōu)先作為索引前列；6. 測(cè)試不同索引結(jié)構(gòu)并分析查詢計(jì)劃；處理排序方向不一致時(shí)，mysql 8.0+可用降序索引，舊版本可通過(guò)函數(shù)模擬但效率較低；聯(lián)合索引失效常見(jiàn)原因包括：未遵循最左前綴、范圍查詢后列無(wú)法使用索引、使用函數(shù)/表達(dá)式、數(shù)據(jù)類型不匹配、優(yōu)化器選擇全表掃描、使用or連接不同列條件。
多列排序在MySQL中非常實(shí)用，它允許你根據(jù)多個(gè)列的值對(duì)結(jié)果集進(jìn)行排序。這在需要更精細(xì)控制排序結(jié)果時(shí)非常有用，例如，先按日期排序，再按優(yōu)先級(jí)排序。聯(lián)合索引在這種情況下可以顯著提升性能，但前提是索引的順序和排序的規(guī)則需要仔細(xì)設(shè)計(jì)。
解決方案
MySQL中使用ORDER BY子句進(jìn)行多列排序。你可以指定多個(gè)列，并為每一列指定排序方向（ASC升序或DESC降序）。
SELECT * FROM 表名 ORDER BY 列1 ASC, 列2 DESC;
上面的sql語(yǔ)句會(huì)先按照列1升序排序，如果列1的值相同，則按照列2降序排序。
聯(lián)合索引的關(guān)鍵在于最左前綴原則。這意味著只有當(dāng)你查詢條件中使用了索引的最左邊的列或列的前綴時(shí)，索引才會(huì)被有效利用。對(duì)于多列排序，聯(lián)合索引的順序必須與ORDER BY子句中的列順序相匹配（或者至少是前綴匹配），才能避免額外的文件排序操作，從而提高查詢效率。
例如，如果你的查詢是ORDER BY 列1 ASC, 列2 DESC，那么理想的索引是INDEX(列1, 列2)。但是，如果查詢是ORDER BY 列2 ASC, 列1 DESC，那么這個(gè)索引就無(wú)法直接利用。
優(yōu)化技巧：
- 索引順序： 確保聯(lián)合索引的列順序與ORDER BY子句中的列順序一致。
- 排序方向： MySQL 8.0及以上版本支持降序索引，允許索引的列以不同的排序方向存儲(chǔ)。如果你的MySQL版本支持，可以創(chuàng)建與ORDER BY子句排序方向完全匹配的索引，例如INDEX(列1 ASC, 列2 DESC)。
- 覆蓋索引： 如果查詢只需要返回索引中的列，那么可以使用覆蓋索引，避免回表查詢，進(jìn)一步提高性能。例如，SELECT 列1, 列2 FROM 表名 ORDER BY 列1, 列2，可以創(chuàng)建一個(gè)包含列1和列2的聯(lián)合索引。
- 避免文件排序： 使用EXPLaiN命令檢查查詢計(jì)劃，確保查詢使用了索引進(jìn)行排序，而不是文件排序（using filesort）。文件排序通常意味著性能瓶頸。
- 考慮數(shù)據(jù)分布： 如果某個(gè)列的唯一值很少，即使它在ORDER BY子句中，也可能沒(méi)有必要將其包含在索引中。
多列排序時(shí)，如何選擇最佳的索引順序？
選擇最佳索引順序的關(guān)鍵在于了解查詢的模式和數(shù)據(jù)分布。通常，應(yīng)該將區(qū)分度最高的列放在索引的最前面。區(qū)分度越高，索引的選擇性就越好，查詢效率也就越高。
例如，如果列1的唯一值比列2多，那么INDEX(列1, 列2)可能比INDEX(列2, 列1)更有效。但是，如果你的查詢經(jīng)常只涉及到列2，那么INDEX(列2, 列1)可能更合適。
最好的方法是使用實(shí)際的查詢和數(shù)據(jù)進(jìn)行測(cè)試，并使用EXPLAIN命令分析查詢計(jì)劃，選擇最合適的索引。
如何處理排序方向不一致的情況？
在MySQL 8.0之前，索引只能以升序存儲(chǔ)。這意味著如果你需要按照降序排序，MySQL可能會(huì)被迫進(jìn)行文件排序。
從MySQL 8.0開(kāi)始，引入了降序索引，允許你創(chuàng)建以特定排序方向存儲(chǔ)數(shù)據(jù)的索引。例如：
CREATE INDEX idx_col1_asc_col2_desc ON 表名 (列1 ASC, 列2 DESC);
這樣，就可以直接使用索引進(jìn)行ORDER BY 列1 ASC, 列2 DESC排序，避免文件排序。
如果你的MySQL版本不支持降序索引，一種常見(jiàn)的解決方法是創(chuàng)建一個(gè)與排序方向相反的索引，然后使用函數(shù)來(lái)模擬降序排序。例如，如果你需要按照列2 DESC排序，你可以創(chuàng)建一個(gè)INDEX(列2 ASC)，然后在查詢中使用ORDER BY -列2 ASC。但這通常不如使用降序索引有效。
聯(lián)合索引在什么情況下會(huì)失效？
聯(lián)合索引失效的情況有很多，以下是一些常見(jiàn)的原因：
- 不滿足最左前綴原則： 查詢條件沒(méi)有使用索引的最左邊的列或列的前綴。例如，如果有一個(gè)索引INDEX(col1, col2, col3)，但是查詢條件只使用了col2和col3，那么索引將不會(huì)被使用。
- 使用范圍查詢后，后面的列無(wú)法使用索引： 例如，如果有一個(gè)索引INDEX(col1, col2, col3)，查詢條件是col1 = ‘value’ AND col2 > ‘value2’ AND col3 = ‘value3’，那么只有col1和col2會(huì)使用索引，col3不會(huì)使用索引。
- 使用了函數(shù)或表達(dá)式： 如果在查詢條件中對(duì)索引列使用了函數(shù)或表達(dá)式，那么索引將不會(huì)被使用。例如，WHERE YEAR(date_col) = 2023。
- 數(shù)據(jù)類型不匹配： 如果查詢條件中的數(shù)據(jù)類型與索引列的數(shù)據(jù)類型不匹配，MySQL可能會(huì)進(jìn)行隱式類型轉(zhuǎn)換，導(dǎo)致索引失效。
- 優(yōu)化器認(rèn)為全表掃描更快： 在某些情況下，即使有索引可用，Mysql優(yōu)化器也可能認(rèn)為全表掃描更快，從而不使用索引。這通常發(fā)生在表很小或者查詢條件的選擇性很低的情況下。
- 使用了OR： 在某些情況下，使用OR可能會(huì)導(dǎo)致索引失效，特別是當(dāng)OR連接的條件涉及到不同的索引列時(shí)。可以使用union代替OR來(lái)解決這個(gè)問(wèn)題。
理解這些失效原因，可以幫助你更好地設(shè)計(jì)索引和編寫查詢，從而提高查詢效率。
.png)
