Mysql: 之前看過(guò)一個(gè)說(shuō)法，說(shuō)order by后的各項(xiàng)如果排序不一致會(huì)導(dǎo)致聯(lián)合索引失效，譬如 order by a ASC, b DESC, c DESCa升序，b降序，排序不一致，索引（a,b,c）失效但是建索引的時(shí)候也可以指定排序如果建立一個(gè)索引(a ASC, b DESC, c DESC)那 order by a ASC, b DESC, c DESC 語(yǔ)句索引還失效嗎？
2 回答
MYYA
TA貢獻(xiàn)1868條經(jīng)驗(yàn) 獲得超4個(gè)贊
mysql 文檔中對(duì)于索引定義中，ASC 和 DESC 的描述
MySQL < 8.0
A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage.
Currently, they are parsed but ignored; index values are always stored in ascending order.
MySQL >= 8.0
A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.
所以，在8.0之前的版本中, DESC 是無(wú)效的，索引 (a ASC, b DESC, c DESC) 等于 (a ASC, b ASC, c ASC)，故而無(wú)法使用整個(gè)聯(lián)合索引進(jìn)行排序。
30秒到達(dá)戰(zhàn)場(chǎng)
TA貢獻(xiàn)1828條經(jīng)驗(yàn) 獲得超6個(gè)贊
8.0之后允許索引降序，拋開 sql 優(yōu)化等細(xì)節(jié)，只要 order by 順序和索引順序一致，那么還是可以用到索引排序的。
(8.0)CREATE INDEX Syntax
(5.7)CREATE INDEX Syntax
添加回答
舉報(bào)
0/150
提交
取消