面试题：MySQL多CPU与多核心环境下的查询优化

简化查询逻辑：
- 检查是否存在冗余的子查询或连接条件，能合并的尽量合并。例如，如果有多个子查询为了获取不同列但数据源相同，可尝试将这些子查询合并为一个。
- 去除不必要的排序和分组，仅在确实需要结果集按特定顺序或分组展示时才使用。
合理使用JOIN类型：
- 对于大多数情况，INNER JOIN 效率较高。如果能确定连接条件的结果都存在于两个表中，应优先使用 INNER JOIN。
- 若存在一个表可能有缺失记录但仍需保留时，根据需求使用 LEFT JOIN 或 RIGHT JOIN。注意，LEFT JOIN 和 RIGHT JOIN 性能通常比 INNER JOIN 低，尤其是在大数据量下。
使用覆盖索引：
- 分析查询中涉及的列，确保索引包含查询所需的所有列。这样，MySQL可以直接从索引中获取数据，而无需回表操作，大大提高查询速度。例如，查询语句为 SELECT column1, column2 FROM table1 WHERE column3 = 'value'，创建索引 CREATE INDEX idx_column3_1_2 ON table1 (column3, column1, column2) 可以实现覆盖索引。

创建复合索引：
- 根据查询条件和连接条件创建复合索引。复合索引的顺序很关键，最常使用的过滤条件列应放在最前面。例如，查询 SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023 - 01 - 01'，可创建复合索引 CREATE INDEX idx_customer_date ON orders (customer_id, order_date)。
避免索引失效：
- 避免在索引列上使用函数或表达式。例如，SELECT * FROM users WHERE UPPER(username) = 'ADMIN' 会导致索引失效，应改为 SELECT * FROM users WHERE username = 'admin' 并在应用层进行大小写转换。
- 注意使用 LIKE 时，LIKE '%value' 会导致索引失效，而 LIKE 'value%' 可以利用索引。

调整缓冲区大小：
- InnoDB缓冲池大小：增大 innodb_buffer_pool_size，让更多的数据和索引能缓存到内存中，减少磁盘I/O。一般可设置为物理内存的 60% - 80%，但要注意预留足够内存给操作系统和其他进程。
- 查询缓存：如果查询重复性较高，可适当调整 query_cache_type 和 query_cache_size。不过在MySQL 8.0 及以后版本，查询缓存已被弃用，因为其维护成本较高，在高并发写入场景下可能会成为性能瓶颈。
多线程相关配置：
- 线程池：合理配置线程池参数，如 thread_handling 设置为 pool-of-threads，通过调整 thread_pool_size 来控制线程池中线程数量，以适应多CPU多核心环境，提高并发处理能力。
- innodb_thread_concurrency：对于InnoDB存储引擎，可适当调整此参数来限制并发线程数，避免过多线程竞争资源导致性能下降。通常设置为CPU核心数的2倍左右。

星途面试题库