MySQL怎样避免全表扫描 索引覆盖与查询重写方案

避免mysql全表扫描的核心方法包括：1.使用explain分析查询计划，关注type列是否为all以识别全表扫描；2.创建合适的索引，如针对高频查询字段建立单列或复合索引；3.优化where子句，避免使用函数或表达式导致索引失效；4.限制返回数据量，使用limit减少不必要的数据检索；5.定期维护索引，通过optimize table整理碎片；6.考虑分区表以缩小扫描范围；7.利用索引覆盖，创建包含所有查询字段的复合索引并验证extra列是否显示using index；8.重写查询语句，如用join替代子查询、union all替代or、避免select *等；9.启用慢查询日志并使用工具分析性能瓶颈；10.避免索引失效的常见问题，如隐式类型转换、like以%开头、不满足最左前缀原则等；11.评估索引选择性，选择唯一值比例高的列建立索引。

避免MySQL全表扫描，核心在于合理利用索引，并优化你的查询语句。索引就像一本书的目录，能帮你快速定位到所需内容，而不是一页一页地翻。

解决方案

1. 分析查询，识别瓶颈：使用 EXPLAIN 命令，查看你的SQL查询计划。关注 type 列，如果显示 ALL，那就意味着全表扫描。possible_keys 列会告诉你哪些索引可能被使用，key 列告诉你实际使用的索引。

   

   EXPLAIN SELECT * FROM users WHERE age > 30;

   登录后复制

2. 创建合适的索引：基于查询条件创建索引。记住，索引并非越多越好，过多的索引会降低写入性能，并占用额外的存储空间。针对高频查询的字段建立索引。

   

   CREATE INDEX idx_age ON users (age);

   登录后复制

3. 优化WHERE子句：避免在 WHERE 子句中使用函数或表达式，这会导致索引失效。比如 WHERE YEAR(birthday) = 1990 应该改为 WHERE birthday >= '1990-01-01' AND birthday

4. 限制返回的数据量：使用 LIMIT 子句限制返回的行数，特别是对于不需要所有数据的查询。

5. 定期维护索引：定期使用 OPTIMIZE TABLE 命令优化表，整理碎片，提高索引效率。

6. 考虑使用分区表：对于大型表，可以考虑使用分区表，将数据分割成更小的、易于管理的部分，从而减少全表扫描的范围。

如何利用索引覆盖优化查询？

索引覆盖是指查询所需的所有字段都包含在索引中，这样MySQL可以直接从索引中获取数据，而无需回表查询。回表查询是指在索引中找到符合条件的记录后，需要根据主键ID再次回到数据表中查询其他字段的值。

要实现索引覆盖，你需要创建一个包含所有查询字段的复合索引。例如，如果你的查询是 SELECT name, age FROM users WHERE city = 'Beijing'，那么你可以创建一个包含 city, name, age 的复合索引。

CREATE INDEX idx_city_name_age ON users (city, name, age);

使用 EXPLAIN 命令检查查询计划，如果 Extra 列显示 Using index，则表示使用了索引覆盖。

查询重写有哪些常见技巧？

查询重写是指修改SQL查询语句，使其更高效地执行。这通常涉及到以下几个方面：

• 子查询优化：尽量避免使用 IN 或 NOT IN 子查询，可以使用 JOIN 替代。

  -- 优化前
  SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'Beijing');
  
  -- 优化后
  SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'Beijing';

  登录后复制

• OR优化：使用 UNION ALL 替代 OR，特别是当 OR 连接的条件涉及不同的索引时。

  -- 优化前
  SELECT * FROM products WHERE price < 100 OR category = 'Electronics';
  
  -- 优化后
  SELECT * FROM products WHERE price < 100
  UNION ALL
  SELECT * FROM products WHERE category = 'Electronics' AND price >= 100;

  登录后复制

  注意：使用 UNION ALL 前，确保两个子查询没有重复数据。

• 使用连接代替子查询：子查询通常会创建临时表，影响性能。使用 JOIN 可以避免这种情况。

• 优化COUNT()查询：COUNT(*) 会统计所有行，即使某些列为 NULL。如果只需要统计非 NULL 列的数量，可以使用 COUNT(column_name)。

• *避免使用 `SELECT `**：只选择需要的列，减少数据传输量。

如何监控MySQL的慢查询？

慢查询日志是定位性能瓶颈的重要工具。启用慢查询日志，可以记录执行时间超过指定阈值的SQL语句。

1. 启用慢查询日志：在MySQL配置文件（my.cnf 或 my.ini）中设置以下参数：

   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log  # 日志文件路径
   long_query_time = 2  # 执行时间超过2秒的SQL语句会被记录
   log_output = FILE  # 日志输出到文件

   登录后复制

   重启MySQL服务使配置生效。

2. 分析慢查询日志：可以使用 mysqldumpslow 工具分析慢查询日志，找出执行频率高、执行时间长的SQL语句。

   mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log  # 找出执行时间最长的前10条SQL语句

   登录后复制

3. 使用性能监控工具：可以使用诸如 Percona Monitoring and Management (PMM)、Prometheus + Grafana 等工具，实时监控MySQL的性能指标，包括慢查询数量、CPU使用率、内存使用率等。

如何避免索引失效？

索引失效会导致MySQL放弃使用索引，从而进行全表扫描。以下是一些常见的导致索引失效的原因及避免方法：

• 使用函数或表达式：在 WHERE 子句中使用函数或表达式，会导致索引失效。

  -- 索引失效
  SELECT * FROM orders WHERE YEAR(order_date) = 2023;
  
  -- 优化
  SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

  登录后复制

• 隐式类型转换：当查询条件的数据类型与列的数据类型不匹配时，MySQL可能会进行隐式类型转换，导致索引失效。

  -- 假设 user_id 是 VARCHAR 类型
  SELECT * FROM users WHERE user_id = 123;  -- 索引可能失效
  
  -- 优化
  SELECT * FROM users WHERE user_id = '123';

  登录后复制

• 使用 != 或 ：尽量避免使用 != 或 操作符，这通常会导致全表扫描。可以使用其他方式替代，例如使用 IN 或 NOT IN。

• LIKE 查询以 % 开头：LIKE '%keyword' 会导致索引失效，因为索引无法从中间开始匹配。可以使用全文索引或搜索引擎解决这个问题。

• 组合索引不满足最左前缀原则：如果有一个组合索引 (a, b, c)，那么只有在查询条件中包含 a 或 a, b 或 a, b, c 时，索引才会被使用。如果查询条件只有 b 或 c，索引将失效。

• 数据分布不均匀：当某个索引列的值重复率很高时，MySQL可能会认为使用索引的效率低于全表扫描，从而放弃使用索引。

索引选择性是什么，如何评估？

索引选择性是指索引列中唯一值的比例。选择性越高，索引的效率越高。一个选择性很低的索引，比如性别（男/女），通常不如选择性高的索引，比如用户ID。

可以使用以下公式计算索引选择性：

索引选择性 = COUNT(DISTINCT column_name) / COUNT(*)

选择性的值越接近 1，索引的效率越高。

评估索引选择性可以帮助你判断是否需要创建索引，以及应该选择哪些列作为索引。对于选择性低的列，可以考虑与其他列组合成复合索引，或者不创建索引。

以上就是MySQL怎样避免全表扫描 索引覆盖与查询重写方案的详细内容，更多请关注php中文网其它相关文章！