Mysql patterns

MySQL 8 Patterns

JSON Column

-- Define and query
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  attrs JSON NOT NULL
);

INSERT INTO products (attrs) VALUES ('{"color":"red","size":42}');

SELECT JSON_UNQUOTE(attrs->'$.color') AS color FROM products;
SELECT * FROM products WHERE attrs->>'$.size' > 40;

-- JSON_TABLE — shred JSON to rows
SELECT jt.*
FROM products,
  JSON_TABLE(attrs, '$' COLUMNS (
    color VARCHAR(50) PATH '$.color',
    size  INT         PATH '$.size'
  )) AS jt;

-- Index on generated column from JSON
ALTER TABLE products ADD COLUMN color VARCHAR(50)
  GENERATED ALWAYS AS (JSON_UNQUOTE(attrs->>'$.color')) VIRTUAL;
CREATE INDEX idx_color ON products (color);

Window Functions

SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
  NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders;

-- Latest row per group (using window)
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM sessions
)
SELECT * FROM ranked WHERE rn = 1;

EXPLAIN and Index Tuning

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1 AND status = 'active';

-- Composite index order matters — leading column equality first
CREATE INDEX idx_orders ON orders (status, user_id, created_at);

-- Covering index
CREATE INDEX idx_cover ON orders (user_id, status) INCLUDE (total);

-- Force index hint
SELECT * FROM orders FORCE INDEX (idx_orders) WHERE status = 'pending';

-- Check index usage
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

InnoDB Internals

-- Check engine status
SHOW ENGINE INNODB STATUS\G

-- Buffer pool hit rate (should be > 99%)
SELECT (1 - (
  SELECT variable_value FROM performance_schema.global_status
  WHERE variable_name = 'Innodb_buffer_pool_reads'
) / (
  SELECT variable_value FROM performance_schema.global_status
  WHERE variable_name = 'Innodb_buffer_pool_read_requests'
)) * 100 AS hit_rate_pct;

-- Deadlock detection
SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.INNODB_TRX;

Replication

-- Primary config (my.cnf)
-- server-id = 1
-- log_bin = /var/log/mysql/mysql-bin.log
-- binlog_format = ROW
-- gtid_mode = ON
-- enforce_gtid_consistency = ON

-- Replica setup
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary-host',
  SOURCE_USER='replication_user',
  SOURCE_PASSWORD='secret',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;

-- Check lag
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source should be near 0

-- Semi-sync for durability
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

ProxySQL Connection Pooling

-- Route reads to replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 2);  -- hostgroup 2 = replicas

-- Stats
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree
FROM stats.stats_mysql_connection_pool;

Performance Checklist

• innodb_buffer_pool_size = 70-80% of RAM
• max_connections tuned to actual concurrent load
• Avoid SELECT * — fetch only needed columns
• Use LIMIT with ORDER BY on indexed column for pagination
• Batch inserts with multi-row INSERT or LOAD DATA INFILE
• Use pt-query-digest from Percona Toolkit to analyze slow log