两道mysql面试题 4.23

如果在查询数据时，查询时间过长，如何优化？

1.分析问题根源

• 使用Explain分析执行计划
  • 运行EXPLAIN SELECT ...查看是否为全表扫描（type=ALL）、索引使用情况（key）、扫描行数（rows）等关键指标
  • 重点关注 Extra 列，避免出现 Using filesort（临时排序）或 Using temporary（临时表）
• 开启慢查询日志
  • 在配置文件中启用 slow_query_log，设置 long_query_time，记录慢查询语句
  • 通过 mysqldumpslow 或 pt-query-digest 工具分析日志，找出高频或高耗时的 SQL

2. 索引优化

• 添加索引：对 WHERE、JOIN、ORDER BY、GROUP BY 涉及的字段创建索引
• 优化索引使用
  • 不要在索引字段上使用函数或计算，否则索引失效
  • 联合索引遵循最左前缀原则，否则索引完全失效或部分失效
  • 选择高区分度字段作为索引
  • 选择占用存储空间较小的字段作为索引
  • 确保查询字段全部在索引中，避免回表查询

3. sql语句优化

• **避免 SELECT ***：仅选择必要字段，减少数据传输和内存开销
• 优化 join 操作
  • 确保关联字段有索引，小表作为驱动表（放在join左侧）
  • 避免多表 JOIN 导致笛卡尔积膨胀
• 分页优化：避免大偏移量 LIMIT 100000, 10，改用基于有序字段的游标分页，如WHERE id > 100000 LIMIT 10
• 减少子查询：将子查询改写为 JOIN

4.表结构优化

• 分表
  • 垂直分表：将大字段（如 TEXT）拆分到独立表，减少主表 I/O
  • 水平分表：按时间（如按月）或哈希分片（如 user_id % 10）分散数据
• 字段类型优化
  • 使用更小的数据类型（如 INT 替代 BIGINT，TIMESTAMP 替代 DATETIME）
  • 避免 NULL，用默认值（如 0 或空字符串）代替，减少索引复杂度
• 范式与反范式平衡：适当冗余高频查询字段，减少 JOIN 次数（如订单表冗余用户名称）

5.系统级优化

• 调整 MySQL 配置

  • 增加 innodb_buffer_pool_size（通常设为物理内存的 70%~80%），提升缓存命中率
  • 调整 max_connections 避免连接数不足

• 读写分离：主库处理写操作，从库处理读操作，分散压力

• 使用缓存：使用 Redis 缓存热点数据（如用户信息），减少直接查询Mysql数据库

什么情况会导致索引失效？

• 对索引列使用函数或表达式：索引存储的是列的原始值，对列进行运算或函数处理后，无法直接匹配索引结构

  -- 失效：对 date 列使用函数
  SELECT * FROM orders WHERE YEAR(create_time) = 2023;
  
  -- 优化：改为范围查询
  SELECT * FROM orders 
  WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

  • 注意：如果一开始索引就是YEAR(create_time)，则第一个sql语句可以索引，而第二个不行

• 索引隐式类型转换：查询条件与索引列类型不一致时，MySQL 会进行隐式类型转换，导致索引失效

  -- 失效：phone 是 VARCHAR，但用数字查询
  SELECT * FROM users WHERE phone = 13812345678;
  
  -- 优化：保持类型一致
  SELECT * FROM users WHERE phone = '13812345678';

• 前导模糊查询（LIKE '%value'）：B+树索引按值的前缀排序，前导通配符（%）使索引无法定位起始位置

  -- 失效：以 % 开头
  SELECT * FROM products WHERE name LIKE '%手机';
  
  -- 优化：尽量避免前导模糊，或使用全文索引
  SELECT * FROM products WHERE name LIKE '旗舰%';

• or 使用不当：OR 连接的多个条件中，若至少一个列无索引，则整个查询可能无法使用索引

  -- 失效：age 有索引，但 email 无索引
  SELECT * FROM users WHERE age = 25 OR email = 'user@example.com';
  
  -- 优化：为 email 添加索引，或拆分为 UNION 查询
  SELECT * FROM users WHERE age = 25
  UNION
  SELECT * FROM users WHERE email = 'user@example.com';

• 联合索引未遵循最左前缀法则：联合索引 (a, b, c) 仅支持 a、a,b 或 a,b,c 查询，跳过最左列会导致失效

• **使用 !=、<> 或 NOT IN**：非等值查询需要扫描大部分数据，优化器可能放弃索引

  -- 失效：不等于操作
  SELECT * FROM orders WHERE status != 'completed';
  
  -- 优化：若过滤范围小，可强制使用索引（需谨慎）
  SELECT * FROM orders FORCE INDEX(idx_status) WHERE status != 'completed';

• 范围查询后的索引列失效：联合索引中，若某一列使用范围查询（>、<、BETWEEN），后续列的索引可能失效

  -- 索引 (age, salary)
  -- age 使用范围查询，salary 无法走索引
  SELECT * FROM employees WHERE age > 30 AND salary = 10000;

• 数据重复率高（低区分度）：若某列的值重复率极高（如性别），使用索引可能不如全表扫描高效

  -- 索引 (gender)
  -- 若 gender 只有 'M'/'F'，可能直接全表扫描
  SELECT * FROM users WHERE gender = 'M';

• 查询优化器的选择：即使索引存在，优化器可能根据统计信息（如表小、索引区分度低）选择全表扫描

  -- 表仅有 100 行时，可能忽略索引
  SELECT * FROM small_table WHERE id > 50;

• 索引列参与计算：索引存储原始值，计算后的值无法直接匹配索引

  -- 失效：price 列参与计算
  SELECT * FROM products WHERE price * 0.9 = 100;
  
  -- 优化：重写条件
  SELECT * FROM products WHERE price = 100 / 0.9;