1 You have a table in memory that needs an index for what you are asking of it, so you're doing full table scans in memory. Fix that with a covering index.
2 You're GROUPing or ORDERing in memory, and the sort is small enough to avoid spillover to disk. Avoid that by doing it in the client or by widening the scope of the covering index.
3 Code in stored procedures, stored functions, stacked views. You'll need to re-jigger that approach.
More general issues to check ...
CPU usage spikes, or is continuously high?
What does top say? An i/o bottleneck will show higher cpu%; high mem usage will leave free mem low.
Run perf.
If show processlist shows many locked queries, consider increasing max_connections but watch out for mem load.
Check thread configuration starting with ...
show variables like '%threads%';
If global status var ratios ...
Select_range_check / Uptime_since_flush_status Select_scan / Uptime_since_flush_status... > 0, optimise those queries.
Suss out abusive processes and remove them.
If none of the above leads to a solution, consider installing the Percona Monitoring and Management Tool; see here for an illustrative case study.
Other useful links ...
https://stackoverflow.com/questions/1282232/mysql-high-cpu-usage helpful
https://dba.stackexchange.com/questions/131477/innodb-intermittent-cpu-spikes-on-a-large-database
https://dba.stackexchange.com/questions/46752/mysql-5-5-high-cpu-usage
Query CPU time
Version 8.0.28 adds optional performance_schema tracking of CPU time spent on a query, see "Performance Schema Notes" at https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.htmlLast updated 18 Jan 2025
