验证行在客户端比次要索引与完整的主键？ [英] Validating row at client side better than secondary index with whole primary key?
本文介绍了验证行在客户端比次要索引与完整的主键？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在cassandra，众所周知，二级索引应该非常谨慎地使用。
如果我有一个表，例如：
用户（用户名，用户类型，电子邮件等）
b $ b
这里的用户名是分区键。现在我想支持返回特定用户（将会给出用户名）的操作，当且仅当usertype是一个特定的值X。
有两种方法可以做it：
一：
根据用户类型创建次要索引，可能的值（'A'，'B'，'C'）
用户名是分区键。
SELECT * FROM user WHERE username ='something'AND usertype ='A'
两个：
然后检查usertype是否为A。
哪种方法更好？大，10s）场景，其中并非分区的所有行都可能具有给定的值（这需要一些客户端过滤）。
我不清楚次要索引是如何在特定节点中查找数据。
例如： SELECT * FROM user WHERE username ='something'AND usertype ='A'
例如，用户类型隐藏的CF有数据'A' - >'jhon'，'miller'，'chris'，...等，100个用户名
使用分区键的查询和usertype一起扫描所有这100个用户名以匹配用户名'something'，或者它只是用户首先获取用户名，如果匹配与'A'？ 它是如何进行搜索？
我使用java作为客户端，如果这个查询费用是给定的索引是低基数数据和每个映射到许多行？
更新：我知道我可以使用集群（用户类型）键为这个特定的例子，但我想知道我的问题。我的原始表是非常复杂的。
解决方案
对于这个例子，让我们假设我创建一个表来跟踪船员ship和id：
CREATE TABLE crewByShip（
ship text，
id int，
firstname text，
lastname text，
gender text，
PRIMARY KEY（ship，id））;
我将在 gender ：
CREATE INDEX crewByShipG_idx ON crewByShip
插入一些数据后，表格如下：
船| id | firstname |性别| lastname
---------- + ---- + ----------- + -------- + -------- ---
Serenity | 1 | Hoban | M | Washburne
Serenity | 2 | Zoey | F | Washburne
Serenity | 3 | Malcolm | M | Reynolds
Serenity | 4 | Kaylee | F | Frye
Serenity | 5 | Sheppard | M |书
Serenity | 6 | Jayne | M | Cobb
Serenity | 7 |西蒙| M | Tam
Serenity | 8 |河| F | Tam
Serenity | 9 | Inara | F | Serra
现在我将打开跟踪，并使用PRIMARY KEY查询一个不同的行，受限于 gender 上的索引。
aploetz @ cqlsh：stackoverflow2>追踪;
aploetz @ cqlsh：stackoverflow2> SELECT * FROM crewByShip WHERE ship ='Serenity'AND id = 3 AND gender ='M';
ship | id | firstname |性别| lastname
---------- + ---- + ----------- + -------- + -------- -
Serenity | 3 | Malcolm | M | Reynolds
（1行）
追踪会话：34ea1840-e8e1-11e4-9cb7-21b264d4c94d
活动| timestamp |源| source_elapsed
--------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------- + ---------------------------- + ----------- ----- + ----------------
执行CQL3查询| 2015-04-22 06：17：48.102000 | 192.168.23.129 | 0
解析SELECT * FROM crewByShip WHERE ship ='Serenity'AND id = 3 AND gender ='M'; [SharedPool-Worker-1] | 2015-04-22 06：17：48.114000 | 192.168.23.129 | 3715
准备语句[SharedPool-Worker-1] | 2015-04-22 06：17：48.116000 | 192.168.23.129 | 4846
对用户执行单分区查询[SharedPool-Worker-2] | 2015-04-22 06：17：48.118000 | 192.168.23.129 | 5730
获取sstable引用[SharedPool-Worker-2] | 2015-04-22 06：17：48.118000 | 192.168.23.129 | 5757
合并memtable墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：48.119000 | 192.168.23.129 | 5793
sstable 1的密钥缓存命中[SharedPool-Worker-2] | 2015-04-22 06：17：48.119000 | 192.168.23.129 | 5848
要求在数据文件中开始分区[SharedPool-Worker-2] | 2015-04-22 06：17：48.120000 | 192.168.23.129 | 5856
跳过0/1无切片相交sstables，包括0由于墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：48.120000 | ：192.168.23.129 | 7056
合并来自memtables和1 sstables的数据[SharedPool-Worker-2] | 2015-04-22 06：17：48.121000 | 192.168.23.129 | 7080
读取1个live和0个tombstoned单元格[SharedPool-Worker-2] | 2015-04-22 06：17：48.122000 | 192.168.23.129 | 7143
查询的计算范围[SharedPool-Worker-1] | 2015-04-22 06：17：48.122000 | 192.168.23.129 | 7578
候选索引平均基数是CompositesIndexOnRegular {columnDeffinition = [ColumnDefinition {name = gender，type = org.apache.cassandra.db.marshal.UTF8Type，kind = REGULAR，componentIndex = 1，indexName = crewbyshipg_idx，indexType = COMPOSITES }]}：0。用crewbyship.crewbyshipg_idx扫描。 [SharedPool-Worker-1] | 2015-04-22 06：17：48.122000 | 192.168.23.129 | 7742
在1个范围上提交范围请求，并发数为1（每个范围预期为0.0行）[SharedPool-Worker-1] | 2015-04-22 06：17：48.122000 | 192.168.23.129 | 7807
提交1个覆盖1个范围的并发范围请求[SharedPool-Worker-1] | 2015-04-22 06：17：48.122000 | ：192.168.23.129 | 7851
执行[Serenity，Serenity] [SharedPool-Worker-2]的索引扫描| 2015-04-22 06：17：48.123000 | 192.168.23.129 | 10848
候选索引平均基数是CompositesIndexOnRegular {columnDeffinition = [ColumnDefinition {name = gender，type = org.apache.cassandra.db.marshal.UTF8Type，kind = REGULAR，componentIndex = 1，indexName = crewbyshipg_idx，indexType = COMPOSITES }]}：0。用crewbyship.crewbyshipg_idx扫描。 [SharedPool-Worker-2] | 2015-04-22 06：17：48.123000 | 192.168.23.129 | 10936
候选索引平均基数是CompositesIndexOnRegular {columnDeffinition = [ColumnDefinition {name = gender，type = org.apache.cassandra.db.marshal.UTF8Type，kind = REGULAR，componentIndex = 1，indexName = crewbyshipg_idx，indexType = COMPOSITES }]}：0。用crewbyship.crewbyshipg_idx扫描。 [SharedPool-Worker-2] | 2015-04-22 06：17：48.123000 | 192.168.23.129 | 11007
对crewbyship.crewbyshipg_idx执行单分区查询[SharedPool-Worker-2] | 2015-04-22 06:17:48.123000 | 192.168.23.129 | 11130
获取sstable引用[SharedPool-Worker-2] | 2015-04-22 06：17：48.123000 | 192.168.23.129 | 11139
合并memtable墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：48.124000 | 192.168.23.129 | 11155
跳过的0/0非切片相交的sstables，包括0由于墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：48.124000 | 192.168.23.129 | 11253
从memtables和0 sstables合并数据[SharedPool-Worker-2] | 2015-04-22 06：17：48.124000 | 192.168.23.129 | 11262
读取1个live和0个tombstoned单元格[SharedPool-Worker-2] | 2015-04-22 06：17：48.127000 | 192.168.23.129 | 11281
对机组人员执行单分区查询[SharedPool-Worker-2] | 2015-04-22 06：17：48.130000 | 192.168.23.129 | 11369
获取sstable引用[SharedPool-Worker-2] | 2015-04-22 06：17：48.131000 | 192.168.23.129 | 11375
合并memtable墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：48.131000 | 192.168.23.129 | 11383
跳过的0/0非切片相交的sstables，包括0由于墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：48.133000 | 192.168.23.129 | 11409
从memtables和0 sstables合并数据[SharedPool-Worker-2] | 2015-04-22 06：17：48.134000 | 192.168.23.129 | 11415
读取1个live和0个tombstoned单元格[SharedPool-Worker-2] | 2015-04-22 06：17：48.138000 | 192.168.23.129 | 11430
扫描1行并匹配1 [SharedPool-Worker-2] | 2015-04-22 06：17：48.138000 | 192.168.23.129 | 11490
请求完成| 2015-04-22 06：17：48.115679 | ：192.168.23.129 | 13679
现在，我将重新运行相同的查询，但没有多余的索引 gender 。
aploetz @ cqlsh：stackoverflow2> SELECT * FROM crewByShip WHERE ship ='Serenity'AND id = 3;
ship | id | firstname |性别| lastname
---------- + ---- + ----------- + -------- + -------- -
Serenity | 3 | Malcolm | M | Reynolds
（1排）
跟踪会话：38d7f440-e8e1-11e4-9cb7-21b264d4c94d
活动| timestamp |源| source_elapsed
--------------------------------------------- -------------------------------------------------- - + ---------------------------- + ---------------- + - ---------------
执行CQL3查询| 2015-04-22 06：17：54.692000 | 192.168.23.129 | 0
解析SELECT * FROM crewByShip WHERE ship ='Serenity'AND id = 3; [SharedPool-Worker-1] | 2015-04-22 06：17：54.695000 | 192.168.23.129 | 87
准备语句[SharedPool-Worker-1] | 2015-04-22 06：17：54.696000 | 192.168.23.129 | 246
对用户执行单分区查询[SharedPool-Worker-3] | 2015-04-22 06：17：54.697000 | 192.168.23.129 | 1185
获取sstable引用[SharedPool-Worker-3] | 2015-04-22 06：17：54.698000 | 192.168.23.129 | 1197
合并memtable墓碑[SharedPool-Worker-3] | 2015-04-22 06：17：54.698000 | 192.168.23.129 | 1215
sstable 1的密钥缓存命中[SharedPool-Worker-3] | 2015-04-22 06：17：54.700000 | 192.168.23.129 | 1249
寻求在数据文件中开始分区[SharedPool-Worker-3] | 2015-04-22 06：17：54.700000 | 192.168.23.129 | 1278
跳过0/1非切片相交sstables，包括0由于墓碑[SharedPool-Worker-3] | 2015-04-22 06：17：54.701000 | 192.168.23.129 | 3309
从memtables和1 sstables合并数据[SharedPool-Worker-3] | 2015-04-22 06：17：54.701000 | 192.168.23.129 | 3333
读取1个live和0个tombstoned单元格[SharedPool-Worker-3] | 2015-04-22 06：17：54.702000 | 192.168.23.129 | 3368
对船员执行单分区查询[SharedPool-Worker-2] | 2015-04-22 06：17：54.702000 | 192.168.23.129 | 4607
获取sstable引用[SharedPool-Worker-2] | 2015-04-22 06：17：54.704000 | 192.168.23.129 | 4633
合并memtable墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：54.704000 | 192.168.23.129 | 4643
跳过的0/0非切片相交的sstables，包括0由于墓碑[SharedPool-Worker-2] | 2015-04-22 06：17：54.705000 | 192.168.23.129 | 4678
从memtables和0 sstables合并数据[SharedPool-Worker-2] | 2015-04-22 06：17：54.705000 | 192.168.23.129 | 4683
读取1个live和0个tombstoned单元格[SharedPool-Worker-2] | 2015-04-22 06：17：54.706000 | 192.168.23.129 | 4697
请求完成| 2015-04-22 06：17：54.697676 | 192.168.23.129 | 5676
如您所见，次要索引查询的source_elapsed
我认为我们可以肯定地说，在低基数列上使用二级索引在宽行表中将不执行良好。现在我不会说过滤客户端是一个好主意，在这种情况下，有一个小的结果集，它可能是更好的选择。
In cassandra, it's well known that secondary indexes should be used very sparingly.
If I have a table for example:
User(username, usertype, email, etc..)
Here username is partition key. Now I want to support operation which returns a specific user(username will be given) if and only if usertype is a specific value X.
There are two ways I can do it:
One: Create a secondary index on usertype, possible values ('A', 'B', 'C') and username is partition key.
SELECT * FROM user WHERE username='something' AND usertype='A';
Two:
I can just fetch the row with username to client and then check if usertype is A.
Which approach is better? Please also consider a wide row(not so big, 10s) scenario where not all rows of a partition might have the given value (which requires some client side filtering).
What I'm not clear about secondary indexes is how data is looked up in a particular node.
Ex: SELECT * FROM user WHERE username='something' AND usertype='A'
For example usertype hidden CF has data 'A'-> 'jhon', 'miller', 'chris',...etc, 100 usernames
And the query with partition key is given along with usertype does it scan through all these 100 usernames to match with the username 'something' or does it just fetches by username first and sees the usertype column if it matches with 'A'? How exactly it does that searching? How does the query fares given the index is on low cardinality data and each one is mapped to many rows?
I'm using java as client if that matters.
Update: I understand that I can use clustering (usertype) key for this particular example but I wanted to know the trade off I've asked. My original tables are much more complex.
解决方案
For this example, let's say I create a table to keep track of crew members by ship and id:
CREATE TABLE crewByShip (
ship text,
id int,
firstname text,
lastname text,
gender text,
PRIMARY KEY(ship,id));
And I'll create an index on gender:
CREATE INDEX crewByShipG_idx ON crewByShip(gender);
After inserting some data, my table looks like this:
ship | id | firstname | gender | lastname
----------+----+-----------+--------+-----------
Serenity | 1 | Hoban | M | Washburne
Serenity | 2 | Zoey | F | Washburne
Serenity | 3 | Malcolm | M | Reynolds
Serenity | 4 | Kaylee | F | Frye
Serenity | 5 | Sheppard | M | Book
Serenity | 6 | Jayne | M | Cobb
Serenity | 7 | Simon | M | Tam
Serenity | 8 | River | F | Tam
Serenity | 9 | Inara | F | Serra
Now I'll turn tracing on, and query a distinct row with the PRIMARY KEY, but also restrict by our index on gender.
aploetz@cqlsh:stackoverflow2> tracing on;
aploetz@cqlsh:stackoverflow2> SELECT * FROM crewByShip WHERE ship='Serenity' AND id=3 AND gender='M';
ship | id | firstname | gender | lastname
----------+----+-----------+--------+----------
Serenity | 3 | Malcolm | M | Reynolds
(1 rows)
Tracing session: 34ea1840-e8e1-11e4-9cb7-21b264d4c94d
activity | timestamp | source | source_elapsed
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+----------------+----------------
Execute CQL3 query | 2015-04-22 06:17:48.102000 | 192.168.23.129 | 0
Parsing SELECT * FROM crewByShip WHERE ship='Serenity' AND id=3 AND gender='M'; [SharedPool-Worker-1] | 2015-04-22 06:17:48.114000 | 192.168.23.129 | 3715
Preparing statement [SharedPool-Worker-1] | 2015-04-22 06:17:48.116000 | 192.168.23.129 | 4846
Executing single-partition query on users [SharedPool-Worker-2] | 2015-04-22 06:17:48.118000 | 192.168.23.129 | 5730
Acquiring sstable references [SharedPool-Worker-2] | 2015-04-22 06:17:48.118000 | 192.168.23.129 | 5757
Merging memtable tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:48.119000 | 192.168.23.129 | 5793
Key cache hit for sstable 1 [SharedPool-Worker-2] | 2015-04-22 06:17:48.119000 | 192.168.23.129 | 5848
Seeking to partition beginning in data file [SharedPool-Worker-2] | 2015-04-22 06:17:48.120000 | 192.168.23.129 | 5856
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:48.120000 | 192.168.23.129 | 7056
Merging data from memtables and 1 sstables [SharedPool-Worker-2] | 2015-04-22 06:17:48.121000 | 192.168.23.129 | 7080
Read 1 live and 0 tombstoned cells [SharedPool-Worker-2] | 2015-04-22 06:17:48.122000 | 192.168.23.129 | 7143
Computing ranges to query [SharedPool-Worker-1] | 2015-04-22 06:17:48.122000 | 192.168.23.129 | 7578
Candidate index mean cardinalities are CompositesIndexOnRegular{columnDefs=[ColumnDefinition{name=gender, type=org.apache.cassandra.db.marshal.UTF8Type, kind=REGULAR, componentIndex=1, indexName=crewbyshipg_idx, indexType=COMPOSITES}]}:0. Scanning with crewbyship.crewbyshipg_idx. [SharedPool-Worker-1] | 2015-04-22 06:17:48.122000 | 192.168.23.129 | 7742
Submitting range requests on 1 ranges with a concurrency of 1 (0.0 rows per range expected) [SharedPool-Worker-1] | 2015-04-22 06:17:48.122000 | 192.168.23.129 | 7807
Submitted 1 concurrent range requests covering 1 ranges [SharedPool-Worker-1] | 2015-04-22 06:17:48.122000 | 192.168.23.129 | 7851
Executing indexed scan for [Serenity, Serenity] [SharedPool-Worker-2] | 2015-04-22 06:17:48.123000 | 192.168.23.129 | 10848
Candidate index mean cardinalities are CompositesIndexOnRegular{columnDefs=[ColumnDefinition{name=gender, type=org.apache.cassandra.db.marshal.UTF8Type, kind=REGULAR, componentIndex=1, indexName=crewbyshipg_idx, indexType=COMPOSITES}]}:0. Scanning with crewbyship.crewbyshipg_idx. [SharedPool-Worker-2] | 2015-04-22 06:17:48.123000 | 192.168.23.129 | 10936
Candidate index mean cardinalities are CompositesIndexOnRegular{columnDefs=[ColumnDefinition{name=gender, type=org.apache.cassandra.db.marshal.UTF8Type, kind=REGULAR, componentIndex=1, indexName=crewbyshipg_idx, indexType=COMPOSITES}]}:0. Scanning with crewbyship.crewbyshipg_idx. [SharedPool-Worker-2] | 2015-04-22 06:17:48.123000 | 192.168.23.129 | 11007
Executing single-partition query on crewbyship.crewbyshipg_idx [SharedPool-Worker-2] | 2015-04-22 06:17:48.123000 | 192.168.23.129 | 11130
Acquiring sstable references [SharedPool-Worker-2] | 2015-04-22 06:17:48.123000 | 192.168.23.129 | 11139
Merging memtable tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:48.124000 | 192.168.23.129 | 11155
Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:48.124000 | 192.168.23.129 | 11253
Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2015-04-22 06:17:48.124000 | 192.168.23.129 | 11262
Read 1 live and 0 tombstoned cells [SharedPool-Worker-2] | 2015-04-22 06:17:48.127000 | 192.168.23.129 | 11281
Executing single-partition query on crewbyship [SharedPool-Worker-2] | 2015-04-22 06:17:48.130000 | 192.168.23.129 | 11369
Acquiring sstable references [SharedPool-Worker-2] | 2015-04-22 06:17:48.131000 | 192.168.23.129 | 11375
Merging memtable tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:48.131000 | 192.168.23.129 | 11383
Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:48.133000 | 192.168.23.129 | 11409
Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2015-04-22 06:17:48.134000 | 192.168.23.129 | 11415
Read 1 live and 0 tombstoned cells [SharedPool-Worker-2] | 2015-04-22 06:17:48.138000 | 192.168.23.129 | 11430
Scanned 1 rows and matched 1 [SharedPool-Worker-2] | 2015-04-22 06:17:48.138000 | 192.168.23.129 | 11490
Request complete | 2015-04-22 06:17:48.115679 | 192.168.23.129 | 13679
Now, I'll re-run the same query, but without the superfluous index on gender.
aploetz@cqlsh:stackoverflow2> SELECT * FROM crewByShip WHERE ship='Serenity' AND id=3;
ship | id | firstname | gender | lastname
----------+----+-----------+--------+----------
Serenity | 3 | Malcolm | M | Reynolds
(1 rows)
Tracing session: 38d7f440-e8e1-11e4-9cb7-21b264d4c94d
activity | timestamp | source | source_elapsed
-------------------------------------------------------------------------------------------------+----------------------------+----------------+----------------
Execute CQL3 query | 2015-04-22 06:17:54.692000 | 192.168.23.129 | 0
Parsing SELECT * FROM crewByShip WHERE ship='Serenity' AND id=3; [SharedPool-Worker-1] | 2015-04-22 06:17:54.695000 | 192.168.23.129 | 87
Preparing statement [SharedPool-Worker-1] | 2015-04-22 06:17:54.696000 | 192.168.23.129 | 246
Executing single-partition query on users [SharedPool-Worker-3] | 2015-04-22 06:17:54.697000 | 192.168.23.129 | 1185
Acquiring sstable references [SharedPool-Worker-3] | 2015-04-22 06:17:54.698000 | 192.168.23.129 | 1197
Merging memtable tombstones [SharedPool-Worker-3] | 2015-04-22 06:17:54.698000 | 192.168.23.129 | 1215
Key cache hit for sstable 1 [SharedPool-Worker-3] | 2015-04-22 06:17:54.700000 | 192.168.23.129 | 1249
Seeking to partition beginning in data file [SharedPool-Worker-3] | 2015-04-22 06:17:54.700000 | 192.168.23.129 | 1278
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2015-04-22 06:17:54.701000 | 192.168.23.129 | 3309
Merging data from memtables and 1 sstables [SharedPool-Worker-3] | 2015-04-22 06:17:54.701000 | 192.168.23.129 | 3333
Read 1 live and 0 tombstoned cells [SharedPool-Worker-3] | 2015-04-22 06:17:54.702000 | 192.168.23.129 | 3368
Executing single-partition query on crewbyship [SharedPool-Worker-2] | 2015-04-22 06:17:54.702000 | 192.168.23.129 | 4607
Acquiring sstable references [SharedPool-Worker-2] | 2015-04-22 06:17:54.704000 | 192.168.23.129 | 4633
Merging memtable tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:54.704000 | 192.168.23.129 | 4643
Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2015-04-22 06:17:54.705000 | 192.168.23.129 | 4678
Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2015-04-22 06:17:54.705000 | 192.168.23.129 | 4683
Read 1 live and 0 tombstoned cells [SharedPool-Worker-2] | 2015-04-22 06:17:54.706000 | 192.168.23.129 | 4697
Request complete | 2015-04-22 06:17:54.697676 | 192.168.23.129 | 5676
As you can see, the "source_elapsed" for the query with the secondary index was more than twice what it was for the same query (which returned the same row) without the index.
I think we can definitely say that using a secondary index on a low-cardinality column in a wide-row table will not perform well. Now while I won't say that filtering client-side is a good idea, in this case, with a small result set, it probably would be the better option.
这篇关于验证行在客户端比次要索引与完整的主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
