CQL 参考

键空间与表创建

-- Create keyspace
CREATE KEYSPACE IF NOT EXISTS shop
  WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'datacenter1': 3
  }
  AND durable_writes = true;

USE shop;

-- Create table with composite primary key
-- (partition_key) = single partition key
-- (partition_key, ...) = composite partition key
CREATE TABLE orders_by_user (
  user_id    UUID,
  order_id   TIMEUUID,
  status     TEXT,
  total      DECIMAL,
  created_at TIMESTAMP,
  PRIMARY KEY (user_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);

-- Composite partition key (distributes across nodes)
CREATE TABLE events_by_day (
  sensor_id TEXT,
  day       DATE,
  event_id  UUID,
  value     DOUBLE,
  PRIMARY KEY ((sensor_id, day), event_id)
);

SELECT 与分区查询

Cassandra 要求每次查询都包含分区键以实现高效路由，聚类列可使用 = 或范围过滤。

-- Query by full partition key (required for performance)
SELECT * FROM orders_by_user WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- Filter on clustering column (range allowed)
SELECT * FROM orders_by_user
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
  AND order_id > maxTimeuuid('2024-01-01')
  AND order_id < minTimeuuid('2025-01-01');

-- LIMIT results
SELECT * FROM orders_by_user
WHERE user_id = ?
LIMIT 10;

-- Token-based scan (full table scan across partitions)
SELECT * FROM orders_by_user WHERE token(user_id) > token(?);

-- ALLOW FILTERING (use with caution: full cluster scan)
SELECT * FROM orders_by_user WHERE status = 'pending' ALLOW FILTERING;

INSERT、UPDATE、DELETE 与 TTL

-- INSERT (upsert semantics: no duplicate check)
INSERT INTO orders_by_user (user_id, order_id, status, total)
VALUES (uuid(), now(), 'pending', 99.99)
USING TTL 2592000;    -- expire after 30 days

-- INSERT IF NOT EXISTS (lightweight transaction, avoid for high throughput)
INSERT INTO users (id, email) VALUES (uuid(), 'alice@example.com')
IF NOT EXISTS;

-- UPDATE (upsert: creates row if not exists)
UPDATE orders_by_user
USING TTL 86400
SET status = 'shipped'
WHERE user_id = ? AND order_id = ?;

-- Conditional update (LWT)
UPDATE users SET email = 'new@example.com'
WHERE id = ?
IF email = 'old@example.com';

-- DELETE specific columns
DELETE status FROM orders_by_user WHERE user_id = ? AND order_id = ?;

-- DELETE entire row
DELETE FROM orders_by_user WHERE user_id = ? AND order_id = ?;

集合类型

-- List, Set, Map columns
CREATE TABLE user_profiles (
  user_id UUID PRIMARY KEY,
  tags    SET<TEXT>,
  history LIST<TEXT>,
  metadata MAP<TEXT, TEXT>
);

-- Append to list
UPDATE user_profiles SET history = history + ['login'] WHERE user_id = ?;

-- Add to set
UPDATE user_profiles SET tags = tags + {'premium', 'verified'} WHERE user_id = ?;

-- Remove from set
UPDATE user_profiles SET tags = tags - {'unverified'} WHERE user_id = ?;

-- Update map entry
UPDATE user_profiles SET metadata['theme'] = 'dark' WHERE user_id = ?;

-- Frozen collections (can be used in primary keys)
CREATE TABLE articles (
  id      UUID PRIMARY KEY,
  authors FROZEN<LIST<TEXT>>
);

二级索引与 SASI

-- Regular secondary index (on low-cardinality columns)
CREATE INDEX idx_orders_status ON orders_by_user (status);

-- SASI (SSTable Attached Secondary Index): supports LIKE and range queries
CREATE CUSTOM INDEX sasi_orders_status ON orders_by_user (status)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {'mode': 'CONTAINS', 'case_sensitive': 'false'};

-- Query using SASI index
SELECT * FROM orders_by_user WHERE status LIKE '%pend%' ALLOW FILTERING;

-- Drop index
DROP INDEX IF EXISTS shop.idx_orders_status;

-- Materialized views (maintained by Cassandra, limited flexibility)
CREATE MATERIALIZED VIEW orders_by_status AS
  SELECT * FROM orders_by_user
  WHERE user_id IS NOT NULL AND order_id IS NOT NULL AND status IS NOT NULL
  PRIMARY KEY (status, user_id, order_id);