Java-常见面试题收集(十九)

二十八 MySQL 优化

1 什么是最左匹配原则

  在MySQL查询优化中，最左匹配原则（Most Left Prefix Rule）是一个重要的概念，特别是在使用复合索引（即包含多个列的索引）时。它指的是在MySQL利用复合索引进行查询时，会按照索引中列的顺序从左到右进行匹配，直到遇到范围查询（如>、<、BETWEEN、LIKE 'pattern%'等）为止，之后的列将不会被索引继续利用。

  复合索引示例
  假设有一个表users，包含列id、first_name、last_name和email，并且在这个表上创建了一个复合索引(first_name, last_name, email)。

  遵循最左匹配原则的查询
  完全匹配：查询条件完全按照索引列的顺序，如WHERE first_name = ‘John’ AND last_name = ‘Doe’ AND email = ‘john.doe@example.com’。这种情况下，MySQL可以充分利用索引中的所有列。
  部分匹配：查询条件只包含索引最左边的几列，如WHERE first_name = 'John’或WHERE first_name = ‘John’ AND last_name = ‘Doe’。在这些情况下，MySQL仍然可以利用索引来加速查询，但只能利用到索引中匹配到的列。

  不遵循最左匹配原则的查询
  跳列查询：如果查询条件跳过了索引中的某些列，如WHERE last_name = ‘Doe’ AND email = ‘john.doe@example.com’（跳过了first_name），则MySQL无法利用这个复合索引来加速查询，因为它不是从最左边的列开始的。
  范围查询后的列：如果查询中在索引的某列上使用了范围查询，那么该列之后的所有列都无法被索引利用。例如，WHERE first_name = ‘John’ AND last_name LIKE ‘D%’，虽然first_name上的等值查询可以利用索引，但last_name上的范围查询导致email列无法被索引利用。

  注意事项
  选择索引列的顺序：在设计复合索引时，应该根据查询条件的常见性和选择性（即列中唯一值的数量）来决定索引列的顺序。
  避免使用函数或表达式：在索引列上使用函数或表达式会阻止MySQL利用索引。
  理解查询执行计划：使用EXPLAIN语句查看MySQL是如何执行你的查询的，这有助于理解索引是如何被利用的。

2 MySQL 问题排查都有哪些手段？

1. 查看错误日志
   检查 MySQL 错误日志文件，可以帮助识别数据库启动失败、权限问题等常见错误。
2. 使用 SHOW PROCESSLIST
   该命令可以查看当前所有连接信息，包括线程、命令、状态、耗时和执行的 SQL 语句。对于 root 用户，可以看到所有用户的连接状态。
3. 查看事务和锁信息
   使用 SELECT * FROM information_schema.INNODB_TRX 查看当前运行的所有事务。
   使用 SELECT * FROM information_schema.INNODB_LOCKS 查看当前出现的锁。
   使用 SELECT * FROM information_schema.INNODB_LOCK_WAITS 查看锁等待的对应关系。
4. 死锁排查
   使用 SHOW ENGINE INNODB STATUS 命令获取最近一次的死锁信息。
   开启 InnoDB 的监控机制，可以周期性打印 InnoDB 的运行状态到错误日志文件中。
5. 慢查询日志
   开启慢查询日志，通过配置 slow_query_log 和 long_query_time 参数，记录执行时间超过指定阈值的 SQL 语句。
   使用 mysqldumpslow 工具分析慢查询日志。
6. SQL 执行计划
   使用 EXPLAIN 命令查询 SQL 语句的执行计划，帮助优化查询性能。
7. 常见故障排查
   数据库未启动或端口被防火墙拦截：启动数据库或开放防火墙端口。
   密码不正确或没有权限访问：修改配置文件，重置密码。
   远程连接数据库慢：禁用 DNS 解析。
   数据表损坏：使用 myisamchk 工具或 phpMyAdmin 修复表。
   超出最大连接错误数量限制：使用 mysqladmin flush-hosts 命令清除缓存。
   连接数超出限制：增大最大连接数配置。
   innodb 数据文件损坏：修改配置文件，启动数据库后备份数据文件。
8. 磁盘和 CPU 使用率高
   使用 iotop 工具找到磁盘 I/O 开销最大的线程，利用 pstack 工具查看堆栈信息。
   优化 SQL 语句，减少多张大表联查，避免频繁创建和销毁临时表。

  通过以上手段，可以有效地排查和解决 MySQL 的常见问题，提高数据库的稳定性和性能。

3 sql 优化可以从哪些方面考虑？

1. 索引优化
   创建索引：为经常查询的字段创建索引，特别是 WHERE 子句、JOIN 操作和 ORDER BY 子句中使用的字段。
   使用合适的索引类型：选择合适的索引类型（如 B-Tree 索引、哈希索引、全文索引等）以适应不同的查询需求。
   避免过多索引：虽然索引可以加快查询速度，但过多的索引会影响插入、更新和删除操作的性能。
2. 查询优化

• 避免使用 SELECT *：明确列出需要查询的字段，减少不必要的数据传输。
• 减少子查询：尽量使用 JOIN 替代子查询，以提高查询效率。
• 使用 LIMIT 限制结果集：对于只需要部分结果的查询，使用 LIMIT 限制返回的行数。
• 优化 JOIN 操作：确保 JOIN 操作使用索引，并尽量减少 JOIN 的表数量。

3. 表设计优化
   规范化设计：通过规范化减少数据冗余，但要注意过度规范化可能导致查询复杂度增加。
   分区表：对于大表，可以使用分区表将数据分割成更小的部分，提高查询性能。
   垂直分割和水平分割：根据业务需求，将表进行垂直分割（按列分割）或水平分割（按行分割）。
4. 缓存和存储优化
   使用缓存：利用缓存机制（如 Redis、Memcached）减少对数据库的直接访问，提升查询性能。
   优化存储引擎：选择合适的存储引擎（如 InnoDB、MyISAM）以适应不同的读写需求。
5. 配置优化
   调整 MySQL 配置参数：如调整 innodb_buffer_pool_size、query_cache_size 等参数，以优化数据库性能。
   使用连接池：通过连接池管理数据库连接，减少连接建立和释放的开销。
6. 分析和监控
   使用 EXPLAIN 分析查询：通过 EXPLAIN 命令查看查询的执行计划，识别性能瓶颈。
   监控慢查询日志：开启慢查询日志，分析执行时间较长的查询，进行针对性优化。
   性能监控工具：使用 MySQL 自带的性能监控工具或第三方工具（如 Percona Toolkit）监控数据库性能。
7. 事务和锁优化
   减少锁的粒度：尽量使用行锁而不是表锁，减少锁冲突。
   控制事务大小：避免长事务，减少锁的持有时间。
   使用合适的隔离级别：根据业务需求选择合适的事务隔离级别，平衡性能和一致性。

4 大表数据查询如何进行优化？

1. 索引优化
   创建合适的索引：为经常查询的字段创建索引，特别是 WHERE 子句、JOIN 操作和 ORDER BY 子句中使用的字段。
   复合索引：对于多条件查询，创建复合索引（多列索引）可以显著提高查询性能。
   覆盖索引：选择性地创建覆盖索引，使查询只需要访问索引而不需要访问表数据。
2. 查询优化
   避免使用 SELECT *：明确列出需要查询的字段，减少不必要的数据传输。
   使用 LIMIT 限制结果集：对于只需要部分结果的查询，使用 LIMIT 限制返回的行数。
   优化 JOIN 操作：确保 JOIN 操作使用索引，并尽量减少 JOIN 的表数量。
   避免复杂的子查询：尽量使用 JOIN 替代子查询，以提高查询效率。
   使用适当的 WHERE 条件：确保 WHERE 条件能够有效利用索引，避免全表扫描。
3. 表设计优化
   分区表：将大表按某个字段（如日期、ID）进行分区，可以将查询限定在特定的分区内，减少扫描的数据量。
   垂直分割：将表按列进行分割，将经常查询的列和不常查询的列分开，减少查询的列数。
   水平分割：将表按行进行分割，将数据分布到多个表或多个数据库中，减少单表的数据量。
4. 缓存和存储优化
   使用缓存：利用缓存机制（如 Redis、Memcached）缓存热点数据，减少对数据库的直接访问。
   优化存储引擎：选择合适的存储引擎（如 InnoDB），并调整相关配置参数（如 innodb_buffer_pool_size）以优化性能。
5. 配置优化
   调整 MySQL 配置参数：如调整 innodb_buffer_pool_size、query_cache_size 等参数，以优化数据库性能。
   使用连接池：通过连接池管理数据库连接，减少连接建立和释放的开销。
6. 分析和监控
   使用 EXPLAIN 分析查询：通过 EXPLAIN 命令查看查询的执行计划，识别性能瓶颈。
   监控慢查询日志：开启慢查询日志，分析执行时间较长的查询，进行针对性优化。
   性能监控工具：使用 MySQL 自带的性能监控工具或第三方工具（如 Percona Toolkit）监控数据库性能。
7. 事务和锁优化
   减少锁的粒度：尽量使用行锁而不是表锁，减少锁冲突。
   控制事务大小：避免长事务，减少锁的持有时间。
   使用合适的隔离级别：根据业务需求选择合适的事务隔离级别，平衡性能和一致性。
8. 数据归档和清理
   定期归档历史数据：将不常用的历史数据归档到其他存储中，减少主表的数据量。
   清理无用数据：定期清理无用数据，保持表的数据量在可控范围内。

5 慢日志查询？统计慢查询？对慢查询如何优化？

1. 慢查询日志的开启和配置
   开启慢查询日志
   在 MySQL 中，可以通过配置文件或命令行开启慢查询日志。
   在配置文件中开启（my.cnf 或 my.ini）：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
slow_query_log：启用慢查询日志。
slow_query_log_file：指定慢查询日志文件的路径。
long_query_time：设置查询时间阈值（单位：秒），超过该时间的查询将被记录。

通过命令行开启：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2;

2. 统计慢查询
   使用 mysqldumpslow 工具
   MySQL 提供了 mysqldumpslow 工具，用于分析慢查询日志。
   常用命令：

# 统计返回记录集最多的前10条SQL
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

# 统计访问次数最多的前10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# 统计按照时间排序的前10条包含左连接的查询
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/mysql-slow.log
-s：排序方式（c：访问次数，r：返回记录数，t：查询时间）。
-t：返回前N条记录。
-g：正则匹配模式。

3. 对慢查询的优化
   分析语句的执行计划，查看 SQL 语句的索引是否命中
   优化数据库的结构，将字段很多的表分解成多个表，或者考虑建立中间表
   优化 LIMIT 分页