mysql查询占用cpu

-- 原始复杂查询
SELECT *
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE table1.status = 'active';

-- 优化后的查询
CREATE TEMPORARY TABLE temp_table AS
SELECT id, status
FROM table1
WHERE status = 'active';

SELECT *
FROM temp_table
JOIN table2 ON temp_table.id = table2.id;

-- 添加单列索引
ALTER TABLE table1 ADD INDEX idx_status (status);

-- 添加复合索引
ALTER TABLE table1 ADD INDEX idx_id_status (id, status);

-- 分区表
ALTER TABLE table1 PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (100000),
    PARTITION p1 VALUES LESS THAN (200000),
    PARTITION p2 VALUES LESS THAN (300000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- 设置事务隔离级别为READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 优化事务处理逻辑
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
-- 执行其他操作
COMMIT;