POSTGRESQL / mysql 索引区别(where)
来源:互联网 发布:windows 7 系统还原 编辑:程序博客网 时间:2024/06/06 07:49
在如下的表中:
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.
- POSTGRESQL / mysql 索引区别(where)
- PostgreSQL mysql limit 区别
- mysql的order by与where加了索引与没有加索引的结果集区别
- mysql where和having区别
- MySQL 索引优化 Using where, Using filesort
- mysql索引区别
- Mysql各种索引区别
- mysql 索引区别
- mysql索引的区别
- Mysql各种索引区别
- mysql中having和where的区别
- mysql中having和where的区别
- MYSQL中having和where的区别
- Mysql中where与having的区别
- mysql中where和having的区别
- Mysql中 where和having的区别
- mysql中having和where的区别
- mysql中where与having的区别
- GCD的同步异步串行并行、NSOperation和NSOperationQueue一级用dispatch_once实现单例
- Redis教程(十):持久化详解
- Python-Argparse
- 【追求进步】用两个栈实现队列
- 关于httpurlconnection getcontentlength返回值为-1的问题
- POSTGRESQL / mysql 索引区别(where)
- 使用for循环输出字符串的子序列
- Redis教程(十一):虚拟内存介绍
- CentOS 6.5安装SVN
- chmod 更改权限和查看权限
- 获取文本的宽高(包含行间距)
- Redis教程(十二):服务器管理命令总结
- 关掉firefox(火狐)和palemoon地址栏自动加www.前缀功能
- Redis教程(十三):管线详解