MySQL Connection, Thread, Query Cache and Optimizer Deep Dive

Running Mechanism

Basic Flow

MySQL’s connection management is implemented through Connector and Connector Pool.

Connection Communication Mechanism Explained

MySQL client and server communication uses “half-duplex” mode:

  1. Half-duplex Characteristic:

    • At any given moment, the connection can only perform sending or receiving operations
    • Cannot perform bidirectional data transmission simultaneously like full-duplex communication

  2. Thread State Management:

    • Each MySQL connection corresponds to an independent thread

Connection Monitoring and Management

-- View active threads
show processlist;

-- View complete thread information
SHOW FULL PROCESSLIST;

-- Terminate specific connection
KILL [CONNECTION|QUERY] process_id;

Query Cache Explained

What is Query Cache

MySQL query cache is a query optimization mechanism built into the database. When query cache is enabled, the system stores SELECT query results and corresponding SQL statements in cache.

Query Cache Working Principle

  1. Check if query cache is enabled
  2. Find fully matched SQL in cache
  3. If hit, return result directly; if miss, continue parsing and execution process

Cache Exclusion Cases

  • Queries using SQL_NO_CACHE hint
  • Query results exceeding query_cache_limit setting
  • Queries containing non-deterministic functions like now(), rand()

Configuration Parameters

-- View basic cache configuration
SHOW VARIABLES LIKE '%query_cache%';

-- View detailed cache usage
SHOW STATUS LIKE '%Qcache%';

Query Optimizer

1. Static Optimization (Compile-time Optimization)

  • Equivalent Transformation: Simplify query conditions through logically equivalent transformations
  • Constant Expression Calculation: Calculate constant expressions at compile time

2. Dynamic Optimization (Runtime Optimization)

  • Aggregate Function Optimization:
    • count(*): InnoDB engine prioritizes using smallest index to count rows
    • min(): Only need to find the leftmost record in the index
    • max(): Only need to find the rightmost record in the index
  • LIMIT Optimization: When query contains LIMIT clause, optimizer terminates scanning early
  • Index Condition Pushdown (ICP): Push WHERE conditions directly to storage engine layer for filtering

Query Execution Process

  1. Storage Engine Interaction: Call corresponding API interface based on table’s storage engine type
  2. Result Return: For large result sets, use incremental return mode (streaming processing)
  3. Query Cache Processing: First check if completely matched SQL exists in cache

Note: MySQL 8.0 has removed query cache functionality; it is recommended to use other caching solutions.