mysql降序索引和减轻索引扫描
2007-02-14 10:55:15 来源:WEB开发网 闂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾惧綊鏌熼梻瀵割槮缁炬儳缍婇弻鐔兼⒒鐎靛壊妲紒鎯у⒔閹虫捇鈥旈崘顏佸亾閿濆簼绨绘い鎺嬪灪閵囧嫰骞囬姣挎捇鏌熸笟鍨妞ゎ偅绮撳畷鍗炍旈埀顒勭嵁婵犲嫮纾介柛灞捐壘閳ь剛鎳撻~婵嬪Ω閳轰胶鐤呯紓浣割儐椤戞瑩宕ョ€n喗鐓曟い鎰靛亝缁舵氨绱撻崘鈺傜婵﹤顭峰畷鎺戔枎閹搭厽袦婵犵數濮崑鎾绘⒑椤掆偓缁夌敻骞嗛悙鍝勭婵烇綆鍓欐俊鑲╃磼閹邦収娈滈柡灞糕偓鎰佸悑閹肩补鈧尙鏁栧┑鐐村灦閹稿摜绮旈悽绋课﹂柛鏇ㄥ灠閸愨偓濡炪倖鍔﹀鈧繛宀婁邯濮婅櫣绱掑Ο璇茶敿闂佺ǹ娴烽弫璇差嚕婵犳碍鏅插璺猴工瀹撳棝姊虹紒妯哄缂佷焦鎸冲畷鎴﹀箻鐠囧弶宓嶅銈嗘尰缁嬫垶绂嶉悙顒佸弿婵☆垳鍘ф禍楣冩倵濮樼偓瀚�

Descending indexing and loose index scan
降序索引和减轻索引扫描
Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general - these are features MySQL should get for all storage engines at some point.
在我以前文章的评论中,尤其是 Gokhan 在这个中提到的,激发了我想写点关于降序索引和减轻索引扫描的东西,或者是 Gokhan 所谓的“改善的范围”支持。通常这些特性跟 Innodb 表都没有内在联系,它们将来在某些时候都能支持各种存储引擎。
Descending indexes - This is something MySQL does not have at this point, but it was not where for so long at large extent because it is less problem than many people think. First - if index is ascending it does not mean it can’t be scanned in reverse order and it will well be. This is how MySQL will optimize indexed ORDER BY col DESC queries for example. Reverse scan could be as fast as forward scan - this is however where storage engines and operating systems come in play. For example certain operation systems might not do backward read-ahead which may slow it down a bit. Or some storage engines, such as MyISAM (for packed indexes) may have reverse scan being much slower than forward scan.
降序索引 -- 现在 MySQL 还不支持这个功能,不过这比很多人想的那样,问题少多了。首先,如果索引是顺序的并不意味着它不能倒序扫描,实际上它表现得挺好的。这就是为什么 MySQL 能优化例如 ORDER BY col DESC 查询的索引。倒序扫描能和正序扫描一样快,不过这些是由存储引擎和操作系统来处理的。例如有些操作系统无法倒序读,这对速度有所降低。或者某些存储引擎,如 MyISAM (它压缩了索引) 倒序扫描时比正序扫描来的慢。
更多精彩
赞助商链接