我整理的一些关于【数据】的项目学习资料（附讲解～～）和大家一起分享、学习一下：

 https://d.51cto.com/eDOcp1

MySQL 查询不走索引的原因与解决方案

在使用 MySQL 数据库时，索引能够显著提升数据检索的速度。然而，有时尽管我们在表中创建了索引，查询仍然不使用索引。这种情况可能导致性能问题，尤其是在大数据量的情况下。本文将讨论 MySQL 查询不走索引的原因，并提供相应的解决方案。同时，我们将通过代码示例和图表帮助读者更好地理解这一问题。

一、什么是索引？

索引是数据库中用来快速查找数据的一种数据结构，通过索引可以避免全表扫描，提高查询效率。在 MySQL 中，索引可以是单列索引、复合索引等，常用的索引类型包括 B-tree 和 Hash。

二、查询不走索引的原因

1. 查询条件不合理

当查询条件不具备选择性时，MySQL 可能会选择不使用索引。例如：

SELECT * FROM users WHERE country = 'USA';
  • 1.

如果表中有大量记录来自美国，MySQL 可能认为扫描整个表会更高效，因此选择不使用索引。

2. 使用函数或计算

当查询条件中涉及函数或计算时，索引可能无法被利用：

SELECT * FROM users WHERE YEAR(created_at) = 2023;
  • 1.

在这个例子中，YEAR() 函数会使得索引失效，导致全表扫描。

3. NULL 值

在包含 NULL 值的列上创建索引时，查询条件为 NULL 的情况也可能导致索引不被使用：

SELECT * FROM users WHERE email IS NULL;
  • 1.

MySQL 对 NULL 值的处理逻辑可能使得索引无效。

4. 数据类型不匹配

在查询条件中，如果数据类型不匹配，也可能导致索引不被使用。比如：

SELECT * FROM users WHERE id = '123';
  • 1.

如果 id 列是整数类型，而传入的是字符串，MySQL 将进行隐式转换，从而使索引失效。

5. 小表与大表的比较

对于小表，MySQL 可能选择全表扫描而非使用索引，这是因为扫描小表的成本低于使用索引的成本。

三、通过代码示例了解如何检查索引使用情况

我们可以使用 EXPLAIN 语句来检查查询是否使用了索引：

EXPLAIN SELECT * FROM users WHERE country = 'USA';
  • 1.

通过 EXPLAIN 的输出，我们可以看到查询的执行计划，分析是否使用了索引。

示例代码

假设我们有一个 users 表，结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    country VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_country (country)
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
查询使用索引
-- 使用索引查询
SELECT * FROM users WHERE country = 'Canada';
  • 1.
  • 2.
查询不使用索引
-- 不使用索引的查询
SELECT * FROM users WHERE YEAR(created_at) = 2023;
  • 1.
  • 2.

四、如何解决查询不走索引的问题

  1. 优化查询条件：
    确保查询条件具有较好的选择性，避免使用全表扫描的条件。

  2. 避免使用函数或计算：
    如果有必要，考虑在应用层面进行时间的格式化，而不是在 SQL 查询中执行。

  3. 处理 NULL 值：
    对于 NULL 值的查询，考虑使用 IS NOT NULL 作为替代方案。

  4. 确保数据类型匹配：
    确保查询条件中的数据类型与表中的数据类型一致。

  5. 添加合适的索引：
    根据查询的使用频率和数据分布，新增合适的索引以支持常用查询。

五、关系图和序列图示例

关系图

下面是 users 表的简单 ER 图，展示了其字段与索引的关系。

erDiagram
    USERS {
        INT id PK
        VARCHAR username
        VARCHAR email UNIQUE
        VARCHAR country
        DATETIME created_at 
        INDEX idx_country (country)
    }
序列图

接下来是一个简单的查询序列图，展示执行过程。

Database User Database User SELECT * FROM users WHERE country='Canada' Check index on country Return results

六、结论

MySQL 查询不走索引的问题可能由多种因素造成，包括查询条件不合理、使用函数或计算、NULL 值影响等。通过合理设计查询和表结构，以及使用 EXPLAIN 工具，我们可以有效地识别并解决这些问题，从而提高数据库的查询性能。希望这篇文章能够帮助读者更好地理解 MySQL 索引的使用与优化。

整理的一些关于【数据】的项目学习资料（附讲解～～），需要自取：

 https://d.51cto.com/eDOcp1