Ordering Index

SELECT *
FROM t
ORDER BY a, b, c;

SELECT *
FROM t
WHERE a = 10
ORDER BY b, c;

SELECT *
FROM t
ORDER BY b, c;

SELECT *
FROM t
WHERE a = 10
ORDER BY b DESC
LIMIT 20;

CREATE INDEX idx_score_time
ON ranking(category ASC, score DESC, created_at ASC);

SELECT *
FROM ranking
WHERE category = 1
ORDER BY score DESC, created_at ASC
LIMIT 20;

-- 索引为 INDEX(a, b)
SELECT *
FROM t
WHERE a BETWEEN 1 AND 10
ORDER BY b;

索引范围扫描或索引扫描
  -> 条件过滤
  -> 返回结果

范围扫描或全表扫描
  -> 条件过滤
  -> 临时结果
  -> filesort
  -> 返回结果

CREATE TABLE events (
  id         BIGINT PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  payload    VARCHAR(200),
  KEY idx_user_time(user_id, created_at DESC)
);

EXPLAIN
SELECT id, created_at, payload
FROM events
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

CREATE TABLE metrics (
  id        BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  category  INT NOT NULL,
  value     DECIMAL(18,2) NOT NULL,
  KEY idx_tenant_category(tenant_id, category)
);

EXPLAIN
SELECT category, COUNT(*), SUM(value)
FROM metrics
WHERE tenant_id = 100
GROUP BY category;

SET SESSION optimizer_switch = 'prefer_ordering_index=on';

SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=on') */
       id, created_at, payload
FROM events
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

SET SESSION optimizer_switch = 'prefer_ordering_index=off';

SELECT ...
FROM t
WHERE filter_col = ?
ORDER BY order_col DESC
LIMIT 10;

SELECT *
FROM messages
WHERE receiver_id = 100
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX idx_receiver_status_time
ON messages(receiver_id, status, created_at DESC);

SHOW SESSION STATUS LIKE 'Adaptive_ordering_used';

SET SESSION optimizer_switch = 'limit_aware_join_order=on';

SET SESSION optimizer_switch =
  'prefer_ordering_index=on,limit_aware_join_order=on';

SELECT /*+ SET_VAR(
         optimizer_switch='prefer_ordering_index=on,limit_aware_join_order=on'
       ) */
       o.id, o.updated_at, d.tag
FROM order_detail AS d
JOIN orders AS o ON o.org_id = d.org_id
WHERE d.tag = 1
  AND o.region_id = 1
ORDER BY o.updated_at DESC
LIMIT 5;

CREATE INDEX idx_orders_region_time
ON orders(region_id, updated_at);

EXPLAIN
SELECT o.id, o.updated_at, d.tag
FROM order_detail AS d
JOIN orders AS o ON o.org_id = d.org_id
WHERE d.tag = 1
  AND o.region_id = 1
ORDER BY o.updated_at DESC
LIMIT 5;

等值过滤列 -> 排序列 -> 其他用于覆盖的列

SELECT id, created_at, title
FROM articles
WHERE tenant_id = 10
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX idx_article_tenant_status_time
ON articles(tenant_id, status, created_at DESC);

等值过滤列 -> 分组列 -> 其他用于过滤或覆盖的列

SELECT category, COUNT(*), SUM(amount)
FROM sales
WHERE tenant_id = 10
GROUP BY category;

CREATE INDEX idx_sales_tenant_category
ON sales(tenant_id, category);

ORDER BY created_at DESC
LIMIT 100000, 20;

SELECT id, created_at, payload
FROM events
WHERE tenant_id = 10
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX idx_events_tenant_time_id
ON events(tenant_id, created_at DESC, id DESC);

SET SESSION optimizer_trace = 'enabled=on';
SET SESSION optimizer_trace_max_mem_size = 1048576;

EXPLAIN
SELECT ...;

SELECT JSON_PRETTY(TRACE)
FROM information_schema.optimizer_trace;

SET SESSION optimizer_trace = 'enabled=off';