===== setup.sql ===== -------------- -- Isolated experiment for the next 深度解析 article. MySQL 9.6.0, InnoDB, 16 KiB pages. -------------- -------------- -- Run only against the temporary local socket documented in the editorial plan. -------------- -------------- CREATE DATABASE IF NOT EXISTS index_lab -------------- -------------- USE index_lab -------------- -------------- DROP TABLE IF EXISTS orders -------------- -------------- DROP TABLE IF EXISTS digits -------------- -------------- CREATE TABLE digits (d TINYINT PRIMARY KEY) -------------- -------------- INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9) -------------- -------------- CREATE TABLE orders ( id BIGINT NOT NULL PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL, total INT NOT NULL, padding VARCHAR(100) NOT NULL, KEY idx_user_created (user_id, created_at) ) ENGINE=InnoDB -------------- -------------- INSERT INTO orders (id, user_id, status, created_at, total, padding) SELECT n + 1, n % 10, IF(n % 97 = 0, 1, 0), TIMESTAMP('2026-01-01') + INTERVAL n SECOND, n % 10000, RPAD('x', 100, 'x') FROM ( SELECT d0.d + 10*d1.d + 100*d2.d + 1000*d3.d + 10000*d4.d AS n FROM digits AS d0 CROSS JOIN digits AS d1 CROSS JOIN digits AS d2 CROSS JOIN digits AS d3 CROSS JOIN digits AS d4 ) AS numbers ORDER BY n -------------- -------------- ANALYZE TABLE orders -------------- +------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+---------+----------+----------+ | index_lab.orders | analyze | status | OK | +------------------+---------+----------+----------+ -------------- SELECT COUNT(*) AS rows_total, SUM(user_id = 2) AS rows_user_2, SUM(user_id = 2 AND status = 1) AS rows_user_2_status_1 FROM orders -------------- +------------+-------------+----------------------+ | rows_total | rows_user_2 | rows_user_2_status_1 | +------------+-------------+----------------------+ | 100000 | 10000 | 103 | +------------+-------------+----------------------+ -------------- EXPLAIN ANALYZE SELECT id, created_at, total FROM orders FORCE INDEX (idx_user_created) WHERE user_id = 2 AND status = 1 ORDER BY created_at DESC LIMIT 20 -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 20 row(s) (cost=856 rows=20) (actual time=0.955..2.05 rows=20 loops=1) -> Filter: (orders.`status` = 1) (cost=856 rows=1102) (actual time=0.954..2.05 rows=20 loops=1) -> Index lookup on orders using idx_user_created (user_id = 2) (reverse) (cost=856 rows=11024) (actual time=0.949..2.01 rows=1891 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ -------------- EXPLAIN ANALYZE SELECT id, created_at FROM orders FORCE INDEX (idx_user_created) WHERE user_id = 2 ORDER BY created_at DESC LIMIT 20 -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 20 row(s) (cost=1108 rows=20) (actual time=0.179..0.181 rows=20 loops=1) -> Covering index lookup on orders using idx_user_created (user_id = 2) (reverse) (cost=1108 rows=11024) (actual time=0.179..0.18 rows=20 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ===== compare.sql ===== -------------- USE index_lab -------------- -------------- ALTER TABLE orders ADD KEY idx_user_status_created (user_id, status, created_at) -------------- -------------- ANALYZE TABLE orders -------------- +------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+---------+----------+----------+ | index_lab.orders | analyze | status | OK | +------------------+---------+----------+----------+ -------------- EXPLAIN ANALYZE SELECT id, created_at, total FROM orders FORCE INDEX (idx_user_status_created) WHERE user_id = 2 AND status = 1 ORDER BY created_at DESC LIMIT 20 -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 20 row(s) (cost=1268 rows=20) (actual time=0.181..0.183 rows=20 loops=1) -> Index lookup on orders using idx_user_status_created (user_id = 2, status = 1) (reverse) (cost=1268 rows=5221) (actual time=0.18..0.182 rows=20 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ -------------- EXPLAIN ANALYZE SELECT id, created_at, total FROM orders FORCE INDEX (idx_user_created) WHERE user_id = 2 AND status = 1 ORDER BY created_at DESC LIMIT 20 -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 20 row(s) (cost=856 rows=20) (actual time=0.976..2.07 rows=20 loops=1) -> Filter: (orders.`status` = 1) (cost=856 rows=1102) (actual time=0.976..2.07 rows=20 loops=1) -> Index lookup on orders using idx_user_created (user_id = 2) (reverse) (cost=856 rows=11024) (actual time=0.971..2.03 rows=1891 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ -------------- SELECT index_name, stat_name, stat_value FROM mysql.innodb_index_stats WHERE database_name = 'index_lab' AND table_name = 'orders' AND index_name IN ('idx_user_created', 'idx_user_status_created') AND stat_name IN ('size', 'n_leaf_pages') ORDER BY index_name, stat_name -------------- +-------------------------+--------------+------------+ | index_name | stat_name | stat_value | +-------------------------+--------------+------------+ | idx_user_created | n_leaf_pages | 149 | | idx_user_created | size | 225 | | idx_user_status_created | n_leaf_pages | 145 | | idx_user_status_created | size | 225 | +-------------------------+--------------+------------+ ===== icp.sql ===== -------------- USE index_lab -------------- -------------- -- status is available in the index, but sits after created_at and is not -------------- -------------- -- a selective leading range key in this query. -------------- -------------- ALTER TABLE orders ADD KEY idx_user_created_status (user_id, created_at, status) -------------- -------------- ANALYZE TABLE orders -------------- +------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+---------+----------+----------+ | index_lab.orders | analyze | status | OK | +------------------+---------+----------+----------+ -------------- SET optimizer_switch='index_condition_pushdown=on' -------------- -------------- EXPLAIN FORMAT=TRADITIONAL SELECT id,total FROM orders FORCE INDEX (idx_user_created_status) WHERE user_id=2 AND created_at>='2026-01-01 10:00:00' AND status=1 -------------- +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | orders | NULL | range | idx_user_created_status | idx_user_created_status | 10 | NULL | 1 | 10.00 | Using index condition | +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+ -------------- EXPLAIN ANALYZE SELECT id,total FROM orders FORCE INDEX (idx_user_created_status) WHERE user_id=2 AND created_at>='2026-01-01 10:00:00' AND status=1 -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Index range scan on orders using idx_user_created_status over (user_id = 2 AND '2026-01-01 10:00:00' <= created_at AND 1 <= status), with index condition: ((orders.`status` = 1) and (orders.user_id = 2) and (orders.created_at >= TIMESTAMP'2026-01-01 10:00:00')) (cost=0.71 rows=1) (actual time=0.0147..0.732 rows=66 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ -------------- SET optimizer_switch='index_condition_pushdown=off' -------------- -------------- EXPLAIN FORMAT=TRADITIONAL SELECT id,total FROM orders FORCE INDEX (idx_user_created_status) WHERE user_id=2 AND created_at>='2026-01-01 10:00:00' AND status=1 -------------- +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | orders | NULL | range | idx_user_created_status | idx_user_created_status | 10 | NULL | 1 | 10.00 | Using where | +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-------------+ -------------- EXPLAIN ANALYZE SELECT id,total FROM orders FORCE INDEX (idx_user_created_status) WHERE user_id=2 AND created_at>='2026-01-01 10:00:00' AND status=1 -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: ((orders.`status` = 1) and (orders.user_id = 2) and (orders.created_at >= TIMESTAMP'2026-01-01 10:00:00')) (cost=0.71 rows=0.1) (actual time=0.0484..5.47 rows=66 loops=1) -> Index range scan on orders using idx_user_created_status over (user_id = 2 AND '2026-01-01 10:00:00' <= created_at AND 1 <= status) (cost=0.71 rows=1) (actual time=0.00558..5.3 rows=6400 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ===== covering.sql ===== -------------- -- Run after setup.sql. If the index already exists, skip the ALTER statement. -------------- -------------- USE index_lab -------------- -------------- ALTER TABLE orders ADD KEY idx_user_status_created_total (user_id, status, created_at, total) -------------- -------------- ANALYZE TABLE orders -------------- +------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+---------+----------+----------+ | index_lab.orders | analyze | status | OK | +------------------+---------+----------+----------+ -------------- SELECT index_name, stat_name, stat_value FROM mysql.innodb_index_stats WHERE database_name = 'index_lab' AND table_name = 'orders' AND index_name IN ('idx_user_status_created', 'idx_user_status_created_total') AND stat_name IN ('size', 'n_leaf_pages') ORDER BY index_name, stat_name -------------- +-------------------------------+--------------+------------+ | index_name | stat_name | stat_value | +-------------------------------+--------------+------------+ | idx_user_status_created | n_leaf_pages | 145 | | idx_user_status_created | size | 225 | | idx_user_status_created_total | n_leaf_pages | 170 | | idx_user_status_created_total | size | 225 | +-------------------------------+--------------+------------+ -------------- EXPLAIN FORMAT=TRADITIONAL SELECT id, created_at, total FROM orders FORCE INDEX (idx_user_status_created_total) WHERE user_id = 2 AND status = 1 ORDER BY created_at DESC LIMIT 20 -------------- +----+-------------+--------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+----------------------------------+ | 1 | SIMPLE | orders | NULL | ref | idx_user_status_created_total | idx_user_status_created_total | 5 | const,const | 5221 | 100.00 | Backward index scan; Using index | +----+-------------+--------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+----------------------------------+ -------------- EXPLAIN ANALYZE SELECT id, created_at, total FROM orders FORCE INDEX (idx_user_status_created_total) WHERE user_id = 2 AND status = 1 ORDER BY created_at DESC LIMIT 20 -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 20 row(s) (cost=537 rows=20) (actual time=0.0238..0.0262 rows=20 loops=1) -> Covering index lookup on orders using idx_user_status_created_total (user_id = 2, status = 1) (reverse) (cost=537 rows=5221) (actual time=0.0236..0.0252 rows=20 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+