多列数据库索引和查询速度 [英] Multi-column database indexes and query speed
问题描述
由于其中一些第三方提供商的工作相当不好保持他们的数据排序，并且因为我不想要重复的优惠券进入我的数据库，我已经在这四列中实现了一个独特的复合索引。这样可以防止相同的优惠券被多次插入到我的数据库中。
鉴于我正在搜索这些列（通过简单的 WHERE列无论什么％等于任何％的比例），我希望这些列可以分别受益于通过索引它们的速度增长。
所以这里是我的问题：整个列之间的复合索引是否提供了与每个列应用单个索引相同的搜索速度增益？或者它只会保证行之间的唯一性？
有些问题有点是我在开发Rails，所以我的问题与SQLite3和MySQL我们可能会在未来迁移），而不是一个特定的RDBMS。
我的猜测是，索引将加速搜索个人列，但我真的没有足够的引擎盖数据库专业知识对这种判断感到自信。
感谢您提供专业知识。
将所有
列的复合索引提供与
相同的搜索速度，我已经为每列应用了
个别索引？
没有。索引中的列的顺序非常重要。假设你有一个这样的索引：在table_name（标题，coupon_code，description，expiration_date）上创建唯一索引index_name
在这种情况下，这些查询将使用索引
select * from table_name where headline = 1
select * from table_name where headline = 1 and cupon_code = 2
这些查询不会使用唯一索引：
select * from table_name where coupon_code = 1
select * from table_name where description = 1 and cupon_code = 2
所以规则是这样的。当您将多个字段编入索引时，您必须指定可以使用索引的第一个k字段。
所以如果你想要能够要搜索任何这些字段之一，那么您应该分别在每个索引上创建 （除了组合的唯一索引）
另外，请注意LIKE操作符。
这将使用索引 SELECT * FROM tbl_name WHERE key_col LIKE'Patrick％';
不会 SELECT * FROM tbl_name WHERE key_col LIKE'％Patrick％';
索引使用 http://dev.mysql.com/doc/refman/5.0/en/mysql- indexes.html
多列索引 http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
I'm deploying a Rails application that aggregates coupon data from various third-party providers into a searchable database. Searches are conducted across four fields for each coupon: headline, coupon code, description, and expiration date.
Because some of these third-party providers do a rather bad job of keeping their data sorted, and because I don't want duplicate coupons to creep into my database, I've implemented a unique compound index across those four columns. That prevents the same coupon from being inserted into my database more than once.
Given that I'm searching against these columns (via simple WHERE column LIKE %whatever% matching for the time being), I want these columns to each individually benefit from the speed gains to be had by indexing them.
So here's my question: will the compound index across all columns provide the same searching speed gains as if I had applied an individual index to each column? Or will it only guarantee uniqueness among the rows?
Complicating the matter somewhat is that I'm developing in Rails, so my question pertains both to SQLite3 and MySQL (and whatever we might port over to in the future), rather than one specific RDBMS.
My guess is that the indexes will speed up searching across individual columns, but I really don't have enough "under the hood" database expertise to feel confident in that judgement.
Thanks for lending your expertise.
will the compound index across all columns provide the same searching speed gains as if I had applied an individual index to each column?
Nope. The order of the columns in the index is very important. Lets suppose you have an index like this: create unique index index_name on table_name (headline, coupon_code, description,expiration_date)
In this case these queries will use the index
select * from table_name where headline = 1
select * from table_name where headline = 1 and cupon_code = 2
and these queries wont use the unique index:
select * from table_name where coupon_code = 1
select * from table_name where description = 1 and cupon_code = 2
So the rule is something like this. When you have multiple fields indexed together, then you have to specify the first k field to be able to use the index.
So if you want to be able to search for any one of these fields then you should create on index on each of them separately (besides the combined unique index)
Also, be careful with the LIKE operator.
this will use index SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
and this will not SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
index usage http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html multiple column index http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
这篇关于多列数据库索引和查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
