MySQL 索引优化指南_mysql索引原理及慢查询优化

苍茫编程网编程文章MySQL 索引优化指南_mysql索引原理及慢查询优化

MySQL 索引优化指南_mysql索引原理及慢查询优化

编程文章jaq1232025-10-02 1:08:332A⁺A^-

SQL # MySQL 索引优化指南

索引使用建议：

索引应建立在 查询频繁、更新较少 的字段上。
避免对经常更新的表创建过多索引。
多列索引要注意 最左前缀原则 。
全文索引适用于文本搜索，但需要注意字符集和排序规则。
定期分析查询语句，使用 EXPLAIN 查看索引使用情况。

一、索引概述

索引是对数据库表中一列或多列的值进行排序的一种数据结构，使用索引可以 快速访问 数据库表中的特定信息，从而 加快对表中记录的查找或排序 。

数据结构可视化网站： www.cs.usfca.edu/~galles #技术分享/vis…

二、索引的优缺点

优点

加速查询 ：显著减少数据检索时间
保证唯一性 ：唯一索引确保数据唯一性
优化连接 ：加快表连接操作速度
提升排序分组 ：改善ORDER BY和GROUP BY性能

缺点

空间占用 ：索引需要额外的存储空间
维护成本 ：增删改操作需要更新索引，影响写入性能
设计复杂度 ：不合理的索引反而降低性能

三、索引分类

| 索引类型 | 说明 | | ---

四、查看索引

1. 查看所有索引（系统视图）

SELECT * FROM mysql.innodb_index_stats;

2. 查看指定数据库中的索引

SELECT *

WHERE database_name = 'test_auth'

3. 查看某表中所有索引

SELECT *

WHERE database_name = 'test_auth' AND table_name LIKE '%log%';

4. 查看表中索引（SHOW 语句）

SHOW INDEX FROM test_auth.sys_user;

5. 查看索引使用情况（性能分析）

EXPLAIN SELECT * FROM table_name WHERE condition;

五、创建索引

索引创建最佳实践：

设计阶段 ：根据业务查询模式设计合适的索引
开发阶段 ：使用 EXPLAIN 验证索引使用情况
测试阶段 ：通过性能测试验证索引效果
生产环境 ：定期监控和优化索引性能
维护阶段 ：定期清理无用索引，重建碎片化索引

推荐使用联合索引

1. 创建普通索引

CREATE INDEX index_name ON table_name(column_name);

2. 创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(column_name);

3. 创建全文索引

注意事项：

只有 CHAR 、 VARCHAR 、 TEXT 类型字段可以建立全文索引
在数据量较大时，先插入数据再创建全文索引比先创建索引再插入数据效率更高
使用前需确认 MySQL 版本、存储引擎和字符集支持全文索引

CREATE FULLTEXT INDEX index_name ON table_name(column_name);

查看全文索引配置：

SHOW VARIABLES LIKE '%ft%';

重点关注：

ft_min_word_len ：最小词元长度（默认4）
ft_max_word_len ：最大词元长度（默认取决于版本）

4. 创建联合索引

创建联合索引时一定要注意位置，遵循 最左前缀法则

CREATE INDEX index_name ON table_name(col1, col2, col3);

最左前缀原则示例：

CREATE INDEX idx_phone_name ON test_auth.sys_user (phone, username);


SELECT *

SELECT *

SELECT *



SELECT *

SELECT * FROM sys_user WHERE phone = 'xxx' OR username = 'xxx';

5. 创建空间索引（仅 MyISAM）

CREATE SPATIAL INDEX index_name ON table_name(column_name);

6. 修改表结构添加索引

ALTER TABLE table_name ADD INDEX index_name(column_name);
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name);
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name);

7. 创建前缀索引

当字段类型为字符串时(varchar、text)，有时需要索引很长的字符串，这会让索引变得很大。此时可以只将字符串的一部分前缀建立索引，节约索引空间。

前缀长度选择原则

选择性越高越好，最高为1（即100%），表示每个截取后的前缀都是唯一的，和完整的列一样好

但不代表就一定要选择第一个为1（即100%）

根据索引的选择性决定，选择性是不重复的索引值（基数）和数据表记录总数的比值。

计算方法：

SELECT COUNT(DISTINCT email) / COUNT(*) FROM sys_user;


SELECT COUNT(DISTINCT SUBSTRING(email, 1, 15)) / COUNT(*) FROM sys_user;

最佳实践

选择第一个达到或超过 0.95（或 0.9）的长度。

如果 selectivity_14 = 0.93 , selectivity_15 = 1.0 ，那么 选择15 是更稳妥的，因为它保证了唯一性。
如果 selectivity_14 = 0.98 , selectivity_15 = 1.0 ，那么需要权衡：
选14 ：索引更小，写入更快。牺牲了2%的选择性（即每100行有2个可能的前缀冲突），在数万行的表中可能影响不大。
选15 ：索引最大程度有效，但比14长1个字节。对于超大型表，这1个字节的累积开销也需要考虑。

选择性分析示例

| 前缀长度 | 选择性 | 性价比评价 | | ---

| 8 | 87.80% | 太低 | | 9 | 94.55% | 接近标准 | | 10 | 98.09% | 高性价比 | | 11 | 99.41% | 优秀 | | 12 | 99.82% | 很好 | | 13 | 99.93% | 最佳推荐 | | 14 | 99.98% | 近乎完美 | | 15 | 100% | 完美 |

创建前缀索引

--

CREATE INDEX idx_email_prefix ON table_name(email(13));

六、删除索引

方式一：DROP INDEX

DROP INDEX index_name ON table_name

方式二：ALTER TABLE

ALTER TABLE table_name DROP INDEX index_name;

七、索引优化建议

选择合适的数据类型 ：使用较小的数据类型，整数类型比字符类型索引效率更高
避免过度索引 ：每个额外的索引都会增加写操作的开销
使用前缀索引 ：对于长字符串字段，可以只索引前几个字符

CREATE INDEX index_name ON table_name(column_name(10));

定期分析索引使用情况 ：使用性能监控工具监控索引效果
考虑索引选择性 ：选择性高的字段（唯一值多的字段）更适合建索引

八、性能监控与分析

1. 查询操作统计

根据输出结果可以判断当前数据库是以查询还是增删改为主，方便我们知道是否要对其做优化。

SHOW GLOBAL STATUS LIKE 'Com_______';

返回结果说明：

Com_select ：SELECT查询执行次数
Com_insert ：INSERT语句执行次数
Com_update ：UPDATE语句执行次数
Com_delete ：DELETE语句执行次数
Com_begin ：事务开始次数
Com_commit ：事务提交次数
Com_rollback ：事务回滚次数
Com_change_db ：数据库切换次数

2. 慢查询日志配置

配置文件位置： /etc/my.cnf 或 /etc/mysql/my.cnf

slow_query_log = 1


long_query_time = 2 

slow_query_log_file = /var/log/mysql/slow.log

查看慢查询日志位置：

SHOW VARIABLES LIKE 'slow_query_log_file';

3. SQL性能分析（PROFILING）

检查是否支持 profiling：

SELECT @@have_profiling;

开启 profiling：

SET profiling = 1

查看所有查询的性能分析：

SHOW PROFILES

查看特定查询的详细分析：

SHOW PROFILE FOR QUERY [query_id]

查看 CPU 耗费情况：

SHOW PROFILE CPU FOR QUERY [query_id]

其他分析选项：

SHOW PROFILE ALL FOR QUERY [query_id]
SHOW PROFILE BLOCK IO FOR QUERY [query_id]
SHOW PROFILE CONTEXT SWITCHES FOR QUERY [query_id]
SHOW PROFILE IPC FOR QUERY [query_id]
SHOW PROFILE MEMORY FOR QUERY [query_id]
SHOW PROFILE PAGE FAULTS FOR QUERY [query_id]
SHOW PROFILE SOURCE FOR QUERY [query_id]
SHOW PROFILE SWAPS FOR QUERY [query_id]

4. EXPLAIN 执行计划分析

EXPLAIN 输出字段详解：

id

select 查询的序列号，表示查询中执行的 select 子句或者是操作表的顺序。

id相同：执行顺序从上到下
id不同：值越大，越先执行

select_type

表示 SELECT 的类型，常见取值：

SIMPLE ：简单查询，不使用表连接或者子查询
PRIMARY ：主查询，即外层的查询
UNION ：UNION中的第二个或者后面的查询语句
SUBQUERY ：SELECT/WHERE之后包含了子查询
DERIVED ：派生表的SELECT
UNION RESULT ：UNION的结果

type（连接类型性能排名）

最优级别

| type | 说明 | 性能 | | ---

优秀级别

| type | 说明 | 性能 | | ---

| eq_ref | 关联查询时使用主键或唯一索引 | 优秀 | | ref | 使用普通索引查询 | 良好 | | fulltext | 全文索引查询 | 良好 |

可接受级别

| type | 说明 | 性能 | | ---

需要优化级别

| type | 说明 | 性能 | | ---

最差级别

| type | 说明 | 性能 | | ---

| ALL | 全表扫描 | 急需优化 |

possible_keys

可能用到的索引，一个或多个。

key

实际使用的索引，如果为 NULL，则表示没有使用索引。

key_len

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows

MySQL 认为必要执行查询的行数，在 InnoDB 引擎中是一个预估值。

filtered

表示返回结果的行数占需要读取行数的百分比，filtered 的值越大越好。

extra

额外的执行信息，常见值：

NULL ：回表查询
Using index ：使用覆盖索引
Using where ：使用WHERE过滤
Using temporary ：使用临时表
Using filesort ：使用文件排序
Using join buffer ：使用连接缓冲

九、常见问题排查

1. 索引失效场景分析

最左前缀法则

如果索引引了多列（联合索引），要遵循最左法则。查询从索引最左列开始，并且不跳过索引中的列。

CREATE INDEX idx_profession_age_status ON tb_user(profession, age, status);


EXPLAIN SELECT *

EXPLAIN SELECT *

EXPLAIN SELECT *



EXPLAIN SELECT *

EXPLAIN SELECT *



EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND status = 'O';

范围查询影响

联合索引中，出现范围查询(<,>)，范围查询右侧的列索引失效

EXPLAIN SELECT *



EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age >= 30 AND status = 'O';

运算操作导致失效

不要在索引列上进行运算操作，索引将失效。

EXPLAIN SELECT *



EXPLAIN SELECT * FROM tb_user WHERE phone LIKE '%15';

字符串类型不加引号

字符串类型字段使用时，不加引号，索引失效。

EXPLAIN SELECT *



EXPLAIN SELECT * FROM tb_user WHERE phone = '17799990015';

模糊查询影响

尾部模糊匹配：索引不会失效
头部模糊匹配：索引失效

EXPLAIN SELECT *



EXPLAIN SELECT *



EXPLAIN SELECT * FROM tb_user WHERE phone LIKE '%9999%';

OR连接条件影响

如果使用 OR 进行分割条件，只有所有条件都有索引时才会使用索引。

EXPLAIN SELECT *



EXPLAIN SELECT * FROM tb_user WHERE phone = '17799990017' OR profession = '软件工程';

数据分布影响

如果 MySQL 评估使用索引比全表扫描慢，则不使用索引。

EXPLAIN SELECT *

EXPLAIN SELECT *

2. 索引提示（SQL Hints）

SQL 提示是优化数据库的一个重要手段，通过在 SQL 语句中加入人为提示来达到优化目的。

EXPLAIN SELECT *

WHERE is_deleted = 0 AND username = 'tsuism'; 

EXPLAIN SELECT *

WHERE is_deleted = 0 AND username = 'tsuism'; 

EXPLAIN SELECT *

WHERE is_deleted = 0 AND username = 'tsuism';

3. 覆盖索引与回表查询

回表查询

当查询的列不在索引中时，MySQL 需要根据索引找到主键，再通过主键回表查询完整数据行，这个过程称为回表查询。

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列在该索引中已经全部能够找到，不需要回表查询。

EXPLAIN 中的 Extra 字段说明：

每个数据库的版本不同所以看到的信息也可能不同，下面只是参考。

Using where; Using index 性能更优

Using index condition ：查找使用了索引，但需要回表查询
Using where; Using index ：查找使用了索引，且所有数据都在索引列中，不需要回表查询

示例优化

表结构：(id, username, password, status)

优化 SQL：SELECT id, username, password FROM tb_user WHERE username = 'xxx';

解决方案： 为 username 和 password 建立联合索引

CREATE INDEX idx_username_password ON tb_user(username, password);

这样查询时只需要使用索引就能获取所有需要的数据，避免回表查询。

4. 常规问题排查

索引未生效 ：使用 EXPLAIN 分析查询执行计划
索引选择不当 ：检查是否满足最左前缀原则
索引碎片化 ：定期优化表或重建索引

OPTIMIZE TABLE table_name;

统计信息过期 ：使用 ANALYZE TABLE 更新统计信息

ANALYZE TABLE table_name;

锁竞争问题 ：监控锁等待情况，优化事务设计

十、最佳实践总结

设计合理的索引策略 ：根据业务查询模式设计索引
定期监控性能 ：使用慢查询日志和EXPLAIN分析
避免索引过度 ：每个索引都有维护成本
使用覆盖索引 ：减少回表操作
定期维护 ：重建碎片化索引，更新统计信息
避免索引失效 ：注意查询语句写法，避免上述索引失效场景
合理使用索引提示 ：在必要时使用USE/IGNORE/FORCE INDEX
优化查询语句 ：尽量使用覆盖索引，减少SELECT * 的使用

设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件（where）、排序（order by)、分组（group by）操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。尽量不要使用 gender 、 status 这种因为区分度不高
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。比如当前表只涉及插入不涉及查询，那么就不要建立索引，相反如果建立越多的索引增删改效率反而降低，还会浪费磁盘空间
如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

点击这里复制本文地址以上内容由jaq123整理呈现，请务必在转载分享时注明本文地址！如对内容有疑问，请联系我们，谢谢！

mysql rank

上一篇：大厂面试-如何在2000万数据量的数据库中查询排名前20万的数据

下一篇：「MySQL调优」大厂MySQL性能优化实战讲解

MySQL 索引优化指南_mysql索引原理及慢查询优化

SQL # MySQL 索引优化指南

一、索引概述

二、索引的优缺点

优点

缺点

三、索引分类

四、查看索引

1. 查看所有索引（系统视图）

2. 查看指定数据库中的索引

3. 查看某表中所有索引

4. 查看表中索引（SHOW 语句）

5. 查看索引使用情况（性能分析）

五、创建索引

1. 创建普通索引

2. 创建唯一索引

3. 创建全文索引

4. 创建联合索引

5. 创建空间索引（仅 MyISAM）

6. 修改表结构添加索引

7. 创建前缀索引

前缀长度选择原则

最佳实践

选择性分析示例

创建前缀索引

六、删除索引

方式一：DROP INDEX

方式二：ALTER TABLE

七、索引优化建议

八、性能监控与分析

1. 查询操作统计

2. 慢查询日志配置

3. SQL性能分析（PROFILING）

4. EXPLAIN 执行计划分析

id

select_type

type（连接类型性能排名）

possible_keys

key

key_len

rows

filtered

extra

九、常见问题排查

1. 索引失效场景分析

最左前缀法则

范围查询影响

运算操作导致失效

字符串类型不加引号

模糊查询影响

OR连接条件影响

数据分布影响

2. 索引提示（SQL Hints）

3. 覆盖索引与回表查询

回表查询

覆盖索引

示例优化

4. 常规问题排查

十、最佳实践总结

设计原则

相关文章