解决MySQL CPU使用率过高的问题需要从多个方面进行排查和优化，包括查询优化、索引优化、配置优化和硬件资源的合理使用等。以下是详细的解决方案和相应的代码示例。

一、查询优化

1. 检查慢查询

使用MySQL的慢查询日志来找到执行时间长的查询。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;  -- 将慢查询的阈值设置为1秒

2. 优化慢查询

利用 EXPLAIN 查看查询的执行计划，并对查询进行优化。

EXPLAIN SELECT * FROM my_table WHERE column1 = 'value';

分析 EXPLAIN 的输出，确保使用了合适的索引，并且没有不必要的全表扫描。

二、索引优化

1. 确保适当的索引

确保查询的列上有适当的索引，以提升查询性能。

CREATE INDEX idx_column1 ON my_table (column1);

2. 删除不必要的索引

不必要的索引会增加写操作的开销，可以通过以下查询找到不常使用的索引：

SELECT table_name, index_name, index_type, non_unique, seq_in_index, column_name, cardinality, sub_part, packed, null as nullable, index_comment 
FROM information_schema.statistics 
WHERE table_schema = 'your_database_name' AND table_name = 'your_table_name';

然后删除不必要的索引：

DROP INDEX idx_column1 ON my_table;

三、配置优化

1. 调整MySQL配置文件

根据服务器硬件配置优化 MySQL 配置文件 my.cnf 或 my.ini。

[mysqld]
max_connections = 500
query_cache_size = 128M
query_cache_type = 1
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2

2. 动态调整配置

使用 SET GLOBAL 命令动态调整某些参数。

SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024;  -- 1GB
SET GLOBAL max_connections = 1000;

四、硬件资源优化

1. 增加硬件资源

考虑增加服务器的CPU、内存和存储性能，以支持更高的并发和更大的数据量。

2. 使用SSD

使用SSD代替传统硬盘，可以大幅提升I/O性能，从而降低CPU等待I/O的时间。

五、分库分表

将数据分布到多个数据库和表中，减轻单个数据库和表的压力。

1. 水平分表

将大表拆分成多个小表。

CREATE TABLE my_table_0 LIKE my_table;
CREATE TABLE my_table_1 LIKE my_table;

2. 分库分表策略

在应用层实现分库分表策略，通过如Sharding-JDBC等中间件实现自动化的分库分表。

六、缓存机制

1. 引入缓存

在应用层引入缓存机制，如使用Redis或Memcached缓存热点数据，减少对MySQL的直接访问。

import redis.clients.jedis.Jedis;public class RedisCacheExample {private static final String REDIS_HOST = "localhost";private static final int REDIS_PORT = 6379;public static void main(String[] args) {try (Jedis jedis = new Jedis(REDIS_HOST, REDIS_PORT)) {String key = "user:1000";String value = jedis.get(key);if (value == null) {value = getUserFromDatabase(1000);jedis.set(key, value);}System.out.println("User data: " + value);}}private static String getUserFromDatabase(int userId) {// 模拟从数据库获取数据return "User Data from DB";}
}

七、异步处理

1. 使用消息队列

将写操作异步化，通过消息队列（如Kafka、RabbitMQ）进行异步写入，削峰填谷。

import org.apache.kafka.clients.producer.KafkaProducer;
import org.apache.kafka.clients.producer.ProducerRecord;
import org.apache.kafka.clients.producer.ProducerConfig;
import org.apache.kafka.common.serialization.StringSerializer;import java.util.Properties;public class KafkaProducerExample {public static void main(String[] args) {Properties props = new Properties();props.put(ProducerConfig.BOOTSTRAP_SERVERS_CONFIG, "localhost:9092");props.put(ProducerConfig.KEY_SERIALIZER_CLASS_CONFIG, StringSerializer.class.getName());props.put(ProducerConfig.VALUE_SERIALIZER_CLASS_CONFIG, StringSerializer.class.getName());KafkaProducer<String, String> producer = new KafkaProducer<>(props);for (int i = 0; i < 1000; i++) {String key = "key" + i;String value = "value" + i;producer.send(new ProducerRecord<>("my_topic", key, value));}producer.close();System.out.println("Data sent to Kafka successfully.");}
}

八、监控和分析

1. 使用性能监控工具

使用MySQL Performance Schema、监控工具（如Prometheus、Grafana）来监控和分析数据库性能，找到瓶颈所在。

-- 开启Performance Schema
UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE NAME LIKE '%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE NAME LIKE '%';