MySQL Slow Query Optimization

1. Causes of MySQL Slow Queries

1.1 Core Reasons

• Index Issues: Queries do not utilize primary key indexes or other indexes, or indexes are improperly designed (unreasonable index creation leads to excessively deep B+Tree structures, making query performance comparable to full table scans).
• Excessive Data Volume: The table is too large, and a single query scans an excessive amount of data.

2. Optimization Strategies

① Optimize Indexes: For tables lacking primary key indexes or having improperly designed indexes, drop ineffective indexes and rebuild them. If a table does not have a primary key, add a primary key index.

② Partitioning and Sharding: When dealing with extremely large data volumes, MySQL queries resort to partial or full table scans, significantly increasing scan time. Splitting large tables into smaller partitioned tables (e.g., based on time or ID as the partition key) can greatly improve query speed and reduce unnecessary scanning.

3. Detailed Optimization Methods

3.1 Optimizing Indexes: Creating and Dropping Inappropriate Indexes

To address slow queries, adding indexes is often necessary—trading storage space for improved query performance.

(1) Regular Index

-- Creating during table creation
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name)  -- Regular index
);

-- Adding via ALTER
ALTER TABLE users ADD INDEX idx_age (age);

-- Creating separately with CREATE
CREATE INDEX idx_name ON users (name);

(2) Unique Index

-- Creating during table creation
CREATE TABLE users (
    id INT,
    email VARCHAR(100) UNIQUE,           -- Implicit unique index
    UNIQUE INDEX idx_email (email)       -- Explicit unique index
);

-- Adding via ALTER
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

(3) Primary Key Index

-- Creating during table creation
CREATE TABLE users (
    id INT PRIMARY KEY,   -- Primary key index (automatically created)
    name VARCHAR(50)
);

-- Adding primary key via ALTER
ALTER TABLE users ADD PRIMARY KEY (id);

-- Creating a unique index
CREATE UNIQUE INDEX idx_email ON users (email);

(4) Full-Text Index

-- Creating during table creation
CREATE TABLE articles (
    id INT,
    content TEXT,
    FULLTEXT INDEX idx_content (content)   -- Full-text index (only applicable to MyISAM/InnoDB)
);

-- Adding via ALTER
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

-- Creating separately with CREATE
CREATE FULLTEXT INDEX idx_content ON articles (content);

(5) Spatial Index

-- Creating during table creation
CREATE TABLE locations (
    id INT,
    point GEOMETRY NOT NULL,
    SPATIAL INDEX idx_point (point)   -- Spatial index (requires MyISAM or a spatial-index-supporting engine)
);

-- Adding via ALTER
ALTER TABLE locations ADD SPATIAL INDEX idx_point (point);

-- Creating separately with CREATE
CREATE SPATIAL INDEX idx_point ON locations (point);

(6) Composite Index

-- Creating during table creation
CREATE TABLE orders (
    user_id INT,
    order_date DATE,
    INDEX idx_user_date (user_id, order_date)   -- Composite index
);

-- Adding via ALTER
ALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);

-- Creating separately with CREATE
CREATE INDEX idx_user_date ON orders (user_id, order_date);

(7) Additional Special Scenarios

Prefix Index: Creates an index on the first N characters of a field (suitable for long text fields).

ALTER TABLE users ADD INDEX idx_name_prefix (name(10));   -- Indexes only the first 10 characters

Functional Index (MySQL 8.0+): Creates an index based on an expression or function.

ALTER TABLE users ADD INDEX idx_year (YEAR(create_time));   -- Creates an index by year

Invisible Index (MySQL 8.0+): Creates an invisible index (useful for testing index effectiveness).

ALTER TABLE users ADD INDEX idx_age (age) INVISIBLE;

(8) Dropping Indexes

ALTER TABLE users DROP INDEX idx_name;
-- or
DROP INDEX idx_name ON users;

3.2 Partitioning and Sharding for Massive Data Volumes

As data volume continues to grow, partitioning tables becomes essential for managing massive datasets, reducing database load, and improving query speed.

Common Partitioning Methods

(1) RANGE Partitioning (by Date Range)

-- Partition order data by year
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(20)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE   -- Handles future data
);

(2) RANGE COLUMNS Partitioning (Multi-Column Range)

-- Composite range partitioning by date and amount
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(20)
)
PARTITION BY RANGE COLUMNS(order_date, amount) (
    PARTITION p2023_low VALUES LESS THAN ('2024-01-01', 1000),   -- Year 2023 and amount < 1000
    PARTITION p2024_high VALUES LESS THAN ('2025-01-01', 5000),
    PARTITION pmax VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

(3) LIST Partitioning (by Predefined Values)

-- Partition by predefined region IDs
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    region_id INT
)
PARTITION BY LIST (region_id) (
    PARTITION p_east VALUES IN (1, 2, 3),    -- Eastern regions
    PARTITION p_west VALUES IN (4, 5, 6),    -- Western regions
    PARTITION p_other VALUES IN (DEFAULT)    -- Other regions
);

(4) HASH Partitioning (Even Data Distribution)

-- Distribute data evenly across 4 partitions using user ID hash
CREATE TABLE user_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    log_time DATETIME,
    content TEXT
)
PARTITION BY HASH(user_id)
PARTITIONS 4;   -- Specifies the number of partitions

(5) KEY Partitioning (Simplified Hashing)

-- Partition by hash of username
CREATE TABLE messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30),
    message TEXT
)
PARTITION BY KEY(username)
PARTITIONS 5;   -- Number of partitions

(6) Composite Partitioning (RANGE + HASH)

-- Range partition by year, then hash subpartition by user ID
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    user_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 4 (   -- 4 subpartitions per main partition
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Adding Partitions Example

-- Add a new partition for 2023 (ensure partition continuity)
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Dropping Partitions

-- Drop a specific partition (data will be lost!)
ALTER TABLE sales DROP PARTITION p2020;

Querying Partition Metadata

-- View partition information for a table
SELECT
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sales';

3.3 Automating Partition Creation with Stored Procedures

Since the open-source version of MySQL lacks Oracle's automatic partition creation feature, stored procedures and scheduled events are required to automate partition creation. Below is a reference script:

-- Create a stored procedure to automatically add partitions
DELIMITER $$
CREATE PROCEDURE AddNextPartition()
BEGIN
    DECLARE next_month VARCHAR(6);
    -- Calculate the next month; e.g., if data requires a new '202401' partition
    SET next_month = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m');

    SET @sql = CONCAT(
        'ALTER TABLE revenue_detail_table REORGANIZE PARTITION pmax INTO (',
        'PARTITION p', next_month, ' VALUES LESS THAN (\'',
        DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), '%Y%m'), '\'), ',
        'PARTITION pmax VALUES LESS THAN (MAXVALUE))'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- Create an event to check and automatically add partitions monthly
CREATE EVENT IF NOT EXISTS AutoAddPartition
ON SCHEDULE EVERY 1 MONTH
DO CALL AddNextPartition();