如何利用MySQL索引提升查询性能技巧 MySQL索引优化详细教程助你快速上手-mysql教程-PHP中文网

mysql索引通过b-tree结构加速数据检索，减少全表扫描和io开销；2. 索引类型包括主键、唯一、普通、复合、全文和空间索引，应根据数据特性和查询需求选择；3. 使用explain分析执行计划，关注type、key、rows和extra等字段判断索引使用情况；4. 复合索引遵循最左前缀原则，查询条件需从索引最左列开始连续匹配；5. 避免对索引列使用函数、类型转换、前导通配符like或负向查询以防止索引失效；6. 优先为高选择性列和常用查询条件创建复合索引，并考虑覆盖索引提升性能；7. 定期监控索引使用情况，删除冗余索引，避免过度索引影响写性能和存储效率；8. 在低峰期使用alter table force或optimize table重建索引以减少碎片，保持查询效率。

如何利用MySQL索引提升查询性能技巧 MySQL索引优化详细教程助你快速上手

MySQL索引是提升查询性能的关键，说白了，它就像一本书的目录，能让你快速定位到需要的信息，而不用一页一页地翻。通过合理创建和使用索引，能够显著减少数据库的IO操作，加速数据检索，让你的系统跑得更快，用户体验也更好。

解决方案

要真正利用MySQL索引提升查询性能，我们得从它的本质和使用策略入手。在我看来，这不仅仅是“建个索引”那么简单，它更像是一门艺术，需要你对数据、查询模式以及MySQL内部机制有深入的理解。

首先，核心在于理解索引的工作原理。大多数MySQL索引（比如InnoDB的B-Tree索引）都是以B-Tree（或B+Tree）结构存储的。这种树形结构能让数据库系统在查找数据时，通过几次树的遍历就能找到目标数据行，而不是扫描整个表。这大大减少了磁盘I/O，因为磁盘I/O是数据库性能瓶颈的常见元凶。

创建索引时，你需要考虑几个关键点：

选择性（Cardinality）：索引列的唯一值越多，选择性越高，索引效果越好。比如，性别字段（男/女）选择性就很低，不适合单独建索引。
查询模式：你的SQL语句中，哪些列经常出现在
```
WHERE
```
登录后复制
子句、
```
JOIN
```
登录后复制
条件、
```
ORDER BY
```
登录后复制
或
```
GROUP BY
```
登录后复制
中？这些是建立索引的重点。
复合索引（联合索引）：当你的查询条件涉及多个列时，考虑创建复合索引。但要记住“最左前缀原则”，这个我们后面会详细聊。
索引维护成本：索引虽然能加速读操作，但会增加写操作（INSERT/UPDATE/DELETE）的开销，因为每次数据变动，索引也需要更新。所以，不要盲目地给所有列都建索引。
覆盖索引（Covering Index）：如果一个索引包含了查询所需的所有列，那么MySQL可以直接从索引中获取数据，而无需回表查询，效率极高。这是优化查询的一个高级技巧。

分析工具方面，

EXPLAIN

登录后复制

命令是你的得力助手。它能展示SQL语句的执行计划，告诉你查询是如何使用索引的，或者为什么没有使用索引。通过分析

EXPLAIN

登录后复制

的输出，你可以清晰地看到查询的瓶颈在哪里，从而有针对性地进行优化。

MySQL索引的类型有哪些，各自适用于什么场景？

说起MySQL的索引类型，其实远不止一种，每种都有它存在的价值和最佳应用场景。搞清楚这些，是咱们能灵活运用索引的前提。

主键索引（Primary Key Index）：这个是最常见的了，每个表通常都有一个主键，它自带唯一性和非空属性。在InnoDB存储引擎中，主键索引是聚簇索引。这意味着数据行是按照主键的顺序物理存储的。所以，通过主键查询效率极高，因为它直接定位到数据行本身。适用于所有需要唯一标识和高频查询的表。
唯一索引（Unique Index）：顾名思义，它保证了索引列中的所有值都是唯一的，但允许有NULL值（可以有多个NULL）。它和主键索引的区别在于，唯一索引不一定是聚簇索引，而且一个表可以有多个唯一索引。当你需要确保某一列（或多列组合）的值不重复，但又不想将其设为主键时，唯一索引就派上用场了。比如，用户表的email字段，通常会设为唯一索引。
普通索引（Normal/Non-Unique Index）：这是最基本的索引类型，没有唯一性的限制。它就是为了加速查询而生，可以创建在任何需要快速检索的列上。绝大多数的查询优化，都是围绕普通索引展开的。
复合索引（Composite Index/联合索引）：这个就有点意思了，它是指在一个索引上包含多个列。比如，
```
INDEX(last_name, first_name)
```
登录后复制
。它的优势在于，当你的查询条件涉及到多个列时，一个复合索引可能比多个单列索引更有效。但它有一个非常重要的特性，就是“最左前缀原则”，这个我们后面会单独拎出来说。适用于多条件联合查询。
全文索引（Full-text Index）：这个比较特殊，它主要用于对文本内容进行关键词搜索，比如文章标题、博客内容等。它不是基于B-Tree结构，而是使用倒排索引。当你需要实现类似搜索引擎的文本检索功能时，全文索引是首选。但要注意，它对中文支持可能需要额外配置（比如使用ngram解析器）。
空间索引（Spatial Index）：主要用于存储和查询地理空间数据，比如经纬度信息。如果你在做地图应用或者需要处理地理位置相关的查询，这个就用得上了。

选择哪种索引，核心思路就是：看你的数据特点和查询需求。主键和唯一索引保障数据完整性并加速特定查询；普通索引是通用加速器；复合索引是多条件查询的利器；而全文和空间索引则是针对特定数据类型的专业工具。

如何判断哪些SQL查询语句可以利用到索引，并进行优化？

判断SQL查询是否利用了索引，以及如何优化，这事儿的核心工具就是MySQL的

EXPLAIN

登录后复制

命令。它能给你一份详细的“执行计划”，告诉你数据库在执行你的SQL时，到底做了些什么。

你只需要在你的

SELECT

登录后复制

语句前面加上

EXPLAIN

登录后复制

，比如：

EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'New York';

登录后复制

然后，你就会看到一个表格输出，里面有很多列，其中有几个是咱们重点关注的：

```
type
```
登录后复制
列：这是最重要的一个指标，它表示MySQL是如何查找表的。
- ```
ALL
```
  登录后复制
  ：全表扫描，性能最差。意味着你的查询没有用到索引，或者索引不合适。这是我们最想避免的。
- ```
index
```
  登录后复制
  ：索引全扫描。虽然比
```
ALL
```
  登录后复制
  好，因为它避免了回表操作（如果索引是覆盖索引的话），但仍然是扫描了整个索引。
- ```
range
```
  登录后复制
  ：范围扫描。通常是好的，表示索引用于范围查询（如
```
>
```
  登录后复制
  、
```
<
```
  登录后复制
  、
```
BETWEEN
```
  登录后复制
  ）。
- ```
ref
```
  登录后复制
  /
```
eq_ref
```
  登录后复制
  ：等值查找。非常高效，表示通过索引进行精确查找。
```
eq_ref
```
  登录后复制
  用于连接操作，通常是最佳的。
- ```
const
```
  登录后复制
  /
```
system
```
  登录后复制
  ：最佳类型，表示查询优化器直接将查询转换为一个常量，通常是主键或唯一索引的等值查询。
```
possible_keys
```
登录后复制
列：MySQL认为可能用于这个查询的索引列表。
```
key
```
登录后复制
列：MySQL实际决定使用的索引。如果这里是NULL，那说明没用上索引。

纳米搜索
纳米搜索：360推出的新一代AI搜索引擎

30

查看详情
```
key_len
```
登录后复制
列：MySQL使用的索引的长度。越短越好，说明匹配的越精确。
```
rows
```
登录后复制
列：MySQL预估需要扫描的行数。这个数字越小越好。
```
Extra
```
登录后复制
列：额外信息，这里面有很多宝贝，能告诉你很多优化方向。
- ```
Using filesort
```
  登录后复制
  ：表示MySQL需要对结果进行外部排序，这通常意味着
```
ORDER BY
```
  登录后复制
  子句没有用到索引，性能会比较差。
- ```
Using temporary
```
  登录后复制
  ：表示MySQL使用了临时表来处理查询，比如
```
GROUP BY
```
  登录后复制
  或
```
DISTINCT
```
  登录后复制
  操作，这也会影响性能。
- ```
Using index
```
  登录后复制
  ：覆盖索引！ 这是非常好的情况，表示查询所需的所有数据都可以从索引中获取，不需要回表查询数据行。
- ```
Using where
```
  登录后复制
  ：表示使用了
```
WHERE
```
  登录后复制
  子句进行过滤。

常见的无法利用索引的情况：

对索引列进行函数操作：比如
```
WHERE DATE(create_time) = CURDATE()
```
登录后复制
，即使
```
create_time
```
登录后复制
有索引，
```
DATE()
```
登录后复制
函数也会导致索引失效。应该改为
```
WHERE create_time >= CURDATE() AND create_time < CURDATE() + INTERVAL 1 DAY
```
登录后复制
。
LIKE '%keyword%'
登录后复制
模糊查询：如果通配符
```
%
```
登录后复制
在开头，索引会失效。
```
LIKE 'keyword%'
```
登录后复制
则可以利用索引。
数据类型不匹配：比如索引列是
```
VARCHAR
```
登录后复制
，但你用数字去查询，MySQL可能会进行隐式转换，导致索引失效。
OR
登录后复制
连接条件：除非
```
OR
```
登录后复制
连接的所有条件列都有索引，并且优化器能有效地使用它们，否则通常会导致索引失效。
负向查询：
```
!=
```
登录后复制
、
```
<>
```
登录后复制
、
```
NOT IN
```
登录后复制
、
```
NOT EXISTS
```
登录后复制
等，有时会导致索引失效。
索引列参与计算：
```
WHERE column + 1 = 10
```
登录后复制
这种也会导致索引失效。

优化思路嘛，就是根据

EXPLAIN

登录后复制

的输出，调整你的SQL语句，或者创建/调整索引。看到

ALL

登录后复制

或

Using filesort

登录后复制

、

Using temporary

登录后复制

，那肯定是要重点关注的。

复合索引（联合索引）的最佳实践与“最左前缀原则”解析

复合索引，或者叫联合索引，它允许你在一个索引上包含多个列。这在多条件查询中非常有用，能避免创建多个单列索引带来的开销和潜在的查询效率问题。但它有一个核心的概念，那就是“最左前缀原则”。

什么是“最左前缀原则”？ 说白了，就是MySQL在使用复合索引时，会从索引的最左边的列开始匹配。如果你的查询条件没有包含复合索引的第一个列，或者跳过了中间的列，那么这个复合索引就可能无法被完全利用，甚至完全失效。

举个例子，假设我们有一个用户表

users

登录后复制

，并且创建了一个复合索引

idx_name_age_city

登录后复制

，包含

(last_name, first_name, city)

登录后复制

三个列。

```
SELECT * FROM users WHERE last_name = 'Zhang';
```
登录后复制
- 这个查询可以完全利用到
```
idx_name_age_city
```
  登录后复制
  索引的
```
last_name
```
  登录后复制
  部分。

SELECT * FROM users WHERE last_name = 'Zhang' AND first_name = 'San';

登录后复制

这个查询可以利用到
```
last_name
```
登录后复制
和
```
first_name
```
登录后复制
两部分。

```
SELECT * FROM users WHERE last_name = 'Zhang' AND first_name = 'San' AND city = 'Beijing';
```
登录后复制
- 这个查询可以完全利用到
```
last_name
```
  登录后复制
  、
```
first_name
```
  登录后复制
  和
```
city
```
  登录后复制
  三个部分。
```
SELECT * FROM users WHERE first_name = 'San';
```
登录后复制
- 注意！ 这个查询就无法利用到
```
idx_name_age_city
```
  登录后复制
  索引，因为它没有包含最左边的
```
last_name
```
  登录后复制
  。MySQL会进行全表扫描。
```
SELECT * FROM users WHERE last_name = 'Zhang' AND city = 'Beijing';
```
登录后复制
- 这个查询只能利用到
```
last_name
```
  登录后复制
  部分，
```
city
```
  登录后复制
  部分就用不上了，因为中间的
```
first_name
```
  登录后复制
  被跳过了。

复合索引的设计策略：

将最常用的查询条件放在前面：根据你的业务查询模式，把那些最常出现在
```
WHERE
```
登录后复制
子句中的列放在复合索引的最左边。
选择性高的列放在前面：如果多个列都被频繁查询，那么将选择性（唯一值数量）最高的列放在前面，这样可以更快地缩小搜索范围。
避免冗余索引：如果你已经有
```
(A, B, C)
```
登录后复制
的复合索引，那么就没有必要再单独创建
```
(A)
```
登录后复制
或
```
(A, B)
```
登录后复制
的索引了，因为
```
(A, B, C)
```
登录后复制
已经包含了它们。但如果你经常只查询
```
(B)
```
登录后复制
或
```
(C)
```
登录后复制
，那可能需要单独的索引。
考虑覆盖索引：如果你的查询只需要索引中的列，而不需要回表查询数据行，那么这个索引就是覆盖索引。设计复合索引时，可以考虑把查询结果中需要的列也包含进去，以达到覆盖索引的效果，进一步提升性能。

总的来说，复合索引的设计是一个权衡的过程，既要考虑查询效率，又要考虑索引维护的成本和存储空间。多用

EXPLAIN

登录后复制

去验证你的索引设计是否有效，这比任何理论知识都来得实在。

索引维护与管理策略

索引不是建好就一劳永逸了，它也需要适当的维护和管理，才能持续发挥最佳效果。这就像我们日常生活中，光买了好工具还不够，还得定期保养不是？

索引的创建与删除 这是最基础的操作，咱们用SQL命令就能搞定：

创建索引：

CREATE INDEX idx_name ON table_name (column1, column2);
-- 或者在创建表时指定
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    INDEX idx_name_age (name, age)
);

登录后复制

删除索引：
```
DROP INDEX idx_name ON table_name;
```
登录后复制
删除索引通常发生在索引不再需要、或者需要重建以优化结构时。

索引的重建或优化 随着数据的不断插入、更新和删除，索引可能会出现碎片化。这会导致索引的物理存储不再连续，从而降低查询效率。虽然InnoDB引擎在处理碎片方面做得比较好，但有时我们仍然需要考虑优化。

ALTER TABLE ... FORCE
登录后复制
：这个命令可以强制重建表和索引，相当于把表的数据和索引都重新整理一遍。
```
ALTER TABLE my_table FORCE;
```
登录后复制
OPTIMIZE TABLE
登录后复制
：对于InnoDB表，
```
OPTIMIZE TABLE
```
登录后复制
实际上会复制表，然后删除旧表，这也能达到整理碎片、回收空间的效果。
```
OPTIMIZE TABLE my_table;
```
登录后复制
这些操作通常在业务低峰期进行，因为它们可能会锁定表，影响正常服务。

索引的监控 了解你的索引使用情况，是优化决策的重要依据。

SHOW INDEX FROM table_name;
登录后复制
：这个命令能列出表的所有索引信息，包括索引名、列名、是否唯一等。
information_schema.STATISTICS
登录后复制
：这是一个系统视图，包含了更详细的索引统计信息，比如基数（Cardinality）。
SHOW STATUS LIKE 'Handler%';
登录后复制
：可以查看各种处理器操作的次数，比如
```
Handler_read_key
```
登录后复制
（通过索引读取的行数），
```
Handler_read_rnd_next
```
登录后复制
（全表扫描的行数），这些数据能间接反映索引的使用效率。

避免过度索引 这是一个很常见的误区。很多人觉得索引越多越好，但事实并非如此。

增加写操作开销：每次
```
INSERT
```
登录后复制
、
```
UPDATE
```
登录后复制
、
```
DELETE
```
登录后复制
操作，数据库都需要同时更新所有相关的索引。索引越多，写操作的性能就越差。
占用存储空间：每个索引都需要占用磁盘空间，索引多了，数据库文件也会变得更大。
查询优化器选择困难：索引过多，查询优化器在选择最佳执行计划时，需要考虑的路径就越多，反而可能导致优化器做出次优选择。