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. 慢查询日志
   开启慢查询日志，通过配置