Cassandra查询操作趟坑记录
例子表
CREATE TABLE employee
(
name TEXT,
age SMALLINT,
phone TEXT,
bornDate DATE,
createDate timestamp,
PRIMARY KEY ((bornDate),name, age,phone, createDate)
)
WITH compression = {
'chunk_length_in_kb' : 256,
'class' : 'LZ4Compressor',
'enabled' : true
} AND CLUSTERING ORDER BY (age asc, bornDate asc, createDate desc )
1.主键顺序查询限制
cassandra主键是一个partition key主键和多个clustering key复合主键,而主键的查询顺序必须与定义表结构时一致.
也就是说下面这种查询错的
select * from employee where age = 1 and name = '张三' and bornDate='1999-01-01'
而正确写法应该是这样
select * from employee where bornDate = '1999-01-01' and name ='张三' and age=1
2. 分区主键查询限制
cassandra中分区主键只能以 等号或in查询,不能使用范围查询
也就是不能以出生日期进行范围查询
select * from employee where bornDate >='1999-01-01' and name='张三';
必须以出生日期in查询,由于in查询其实效率并不是太好,所以在表设计时应当注意
select * from employee where bornDate in ('1999-01-01','1999-01-02') and name = '张三'
3.范围主键查询限制
cassandra中范围查询只能放在条件查询的最后一个位置,例如,如果范围查询age,则就不能添加phone查询条件
也就是这么写法是错的
select * from employee where bornDate = '2019-01-01' and name ='张三' and age >18 and phone = '123456'
当然也并不是不能这么做,不过那样必须加上ALLOW FILTERING,但并不建议这么做
也就是下面这种写法是没问题的
select * from employee where bornDate = '2019-01-01' and name ='张三' and age >18 and phone = '123456' allow filtering;
4.排序规则
cassandra在创建表时设置一个排序规则,默认以此进行规则排序,如当前表,默认以正序age,正序bornDate和倒序createDate, 手动设置倒序只有一种方式,即将所有排序字段全部颠倒,也就是必须像这样
select * from employee where bornDate in ('1999-01-01') and name = '张三' order by age desc, bornDate desc, createDate asc
5.排序对分区主键条件的限制
cassandra中只要使用排序,无论是使用默认排序规则还是相反排序规则,分区主键只能使用等于查询,(可以使用in,但是只能IN一个数据),
所以这样写就是错误
select * from employee where bornDate in ('1999-01-01','1999-01-02') and name = '张三' order by age desc, bornDate desc, createDate asc
应该
select * from employee where bornDate in ('1999-01-01') and name = '张三' order by age desc, bornDate desc, createDate asc
或
select * from employee where bornDate = '1999-01-01' and name = '张三' order by age desc, bornDate desc, createDate asc
6.使用In和Order by 时需要全局关闭分页,
Cluster.Builder()
.AddContactPoints(cassandraUrls)
// 设置pageSize为最大值,这样代表为关闭分页,可以使用in 和order by
.WithQueryOptions(new QueryOptions().SetPageSize(int.MaxValue))
.Build();