POSTGRESQL / mysql 索引区别|where|postgresql mysql选哪个
在如下的表中:
create table aaa ( a int, b int, c int, d int);create index indexabc on aaa(a,b,c);
对比是否使用到了索引:
发现postgresql的索引更加智能一些.
它可以使用索引集合的任意子集 而 mysql不行.
版本:
mysql 5.6.22 postgresql: 9.5
官方文档参考:
mysql多列索引
If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).
postgresql多列索引
A multicolumn B-tree index can be used with query conditions that involve any subset of the index’s columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they’d still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.
相关内容
-
智能电视电脑显示器连接|智能电视与电脑连接
智能电视电脑显示器连接|智能电视与电脑连接,,1. 智能电视与电...
-
诺孚-诺孚厨堡抽油烟机能真正达到0油烟烹饪效果
诺孚-诺孚厨堡抽油烟机能真正达到0油烟烹饪效果吗?诺孚厨堡的...
-
什么样的过程中powerkey.exe是编辑集合查询
什么样的过程中powerkey.exe是编辑集合查询,,点评:powerkey.exe...
-
千元内老人用的智能机,你觉得有哪些好的推荐呢
千元内老人用的智能机,你觉得有哪些好的推荐呢,华为,像素,千元...
-
笔记本开机显示智能故障预测硬盘解决方案
笔记本开机显示智能故障预测硬盘解决方案,,笔记本开机显示智能...
-
华为b6手环蓝牙怎么开|华为b6智能手环怎么连蓝
华为b6手环蓝牙怎么开|华为b6智能手环怎么连蓝牙,,华为b6手环...
-
在四月大约40000000搜索引擎的邪恶使用
在四月大约40000000搜索引擎的邪恶使用,,PRNewswire弗吉尼亚Re...
-
请推荐几款T9键盘智能手机
请推荐几款T9键盘智能手机,诺基亚,键盘,请推荐几款T9键盘智能...
-
小米4智能电视音响怎么连接电脑连接电脑连接电
小米4智能电视音响怎么连接电脑连接电脑连接电脑|小米电视怎...
-
2、学习lucene之索引删除、恢复删除、强制删除
2、学习lucene之索引删除、恢复删除、强制删除,索引,位置, ...
-
金蝶供应链模板智能化|金蝶K3的商业智能如何?
金蝶供应链模板智能化|金蝶K3的商业智能如何?,,1.金蝶K3的商业...
-
华硕双核智能主板p7p55d-e强热售
华硕双核智能主板p7p55d-e强热售,,开学伊始,许多学生朋友开始为...
-
2011的首次全球智能手机出货量首次超过电脑。
2011的首次全球智能手机出货量首次超过电脑。,,美国市场研究公...
-
诺基亚滑盖智能手机|诺基亚电动滑盖手机
诺基亚滑盖智能手机|诺基亚电动滑盖手机,,诺基亚电动滑盖手机...
-
双网双待智能手机|双网双待智能手机型号
双网双待智能手机|双网双待智能手机型号,,1. 双网双待智能手机...