SQL必知必会跟练 3-5
2026/11/6 5:13:26
1. 索引语法与基础操作
索引是MySQL中用于加速数据检索的关键机制，合理使用索引可以显著提升查询性能。我们先从最基础的索引操作语法开始，这是每位数据库开发者必须掌握的技能。
1.1 索引的创建语法
创建索引的基本语法如下：
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (column1, column2,...);这里有几个关键点需要注意：
UNIQUE：创建唯一索引，确保索引列的值唯一FULLTEXT：创建全文索引，适用于文本内容的搜索- 索引名应具有描述性，通常采用
idx_表名_字段名的命名规范
实际案例：为用户表的手机号字段创建唯一索引
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);1.2 索引查看与删除
查看表的所有索引信息：
SHOW INDEX FROM table_name;这个命令会返回包括索引名、索引类型、索引字段等详细信息，对于分析现有索引结构非常有用。
删除索引的语法：
DROP INDEX index_name ON table_name;注意：删除索引是一个危险操作，特别是在生产环境中。建议先通过
EXPLAIN确认该索引确实不再使用后再执行删除。
1.3 索引类型选择建议
MySQL支持多种索引类型，选择适合的索引类型对性能至关重要：
| 索引类型 | 适用场景 | 注意事项 |
|---|---|---|
| 普通索引 | 大多数查询场景 | 最基本的索引类型 |
| 唯一索引 | 需要保证数据唯一性的列 | 会阻止重复值插入 |
| 主键索引 | 表的主键 | 自动创建，不能为NULL |
| 复合索引 | 多列组合查询 | 遵循最左前缀原则 |
| 全文索引 | 文本内容搜索 | 仅适用于MyISAM和InnoDB |
2. SQL性能分析工具详解
要优化索引使用，首先需要准确分析SQL语句的性能表现。MySQL提供了一系列强大的性能分析工具。
2.1 SQL执行频率分析
通过以下命令可以统计各类SQL操作的执行频率：
SHOW GLOBAL STATUS LIKE 'Com_______';这个命令会返回以Com开头的各种操作计数器，重点关注：
Com_select：SELECT查询次数Com_insert：INSERT操作次数Com_update：UPDATE操作次数Com_delete：DELETE操作次数
通过分析这些数据，可以确定哪些类型的查询最频繁，从而有针对性地优化。
2.2 慢查询日志配置与使用
慢查询日志是识别性能问题的利器，配置方法如下：
- 修改MySQL配置文件（通常为
/etc/my.cnf或/etc/mysql/my.cnf）：
slow_query_log = 1 long_query_time = 2 slow_query_log_file = /var/log/mysql/mysql-slow.log重启MySQL服务使配置生效
使用以下工具分析慢查询日志：
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log这个命令会按查询时间排序，显示最慢的10个查询。
2.3 PROFILING深度分析
PROFILING功能可以详细展示SQL语句执行过程中的资源消耗情况：
- 开启profiling：
SET profiling = 1;执行需要分析的SQL语句
查看分析结果：
-- 查看所有查询的概要信息 SHOW PROFILES; -- 查看特定查询的详细执行信息 SHOW PROFILE FOR QUERY 1; -- 查看CPU使用情况 SHOW PROFILE CPU FOR QUERY 1;2.4 EXPLAIN执行计划解析
EXPLAIN是分析查询性能最常用的工具，使用方法：
EXPLAIN SELECT * FROM users WHERE age > 30;执行计划中的关键字段解读：
type：访问类型，从最优到最差依次为：- system > const > eq_ref > ref > range > index > ALL
key：实际使用的索引rows：预估需要检查的行数Extra：额外信息，如Using index表示使用了覆盖索引
3. 索引使用规则与最佳实践
理解了索引基础后，我们需要掌握索引使用的核心规则，避免常见的性能陷阱。
3.1 最左前缀法则详解
最左前缀法则是复合索引使用的黄金规则。假设有索引(A,B,C)：
有效使用场景：
WHERE A = 1 WHERE A = 1 AND B = 2 WHERE A = 1 AND B = 2 AND C = 3无效使用场景：
WHERE B = 2 WHERE C = 3 WHERE B = 2 AND C = 3原理说明：复合索引的存储结构是按照索引定义的字段顺序组织的。就像电话簿先按姓氏排序，再按名字排序。如果不知道姓氏，就无法利用这个排序。
3.2 范围查询对索引的影响
范围查询（>、<、BETWEEN等）会使复合索引中该列右侧的所有列索引失效：
-- 只有name和age会使用索引，status索引失效 WHERE name = '张三' AND age > 20 AND status = 1解决方案：
- 调整索引顺序，将范围查询列放在最后
- 使用等值查询替代范围查询
3.3 索引失效的常见场景
以下是导致索引失效的典型情况：
- 在索引列上使用函数或运算
-- 索引失效 WHERE YEAR(create_time) = 2023- 隐式类型转换
-- phone是varchar类型，这里数字比较导致索引失效 WHERE phone = 13800138000- 前导模糊查询
-- 前导%导致索引失效 WHERE name LIKE '%张'- OR条件使用不当
-- 如果age列没有索引，整个查询都不会使用索引 WHERE name = '张三' OR age = 20- 数据分布影响当MySQL优化器判断使用索引比全表扫描更慢时，会放弃使用索引。
4. 高级索引优化技巧
掌握了基础规则后，我们来看一些高级的索引优化技术。
4.1 SQL提示的使用技巧
SQL提示可以影响优化器的索引选择：
USE INDEX：建议使用特定索引
SELECT * FROM users USE INDEX(idx_age) WHERE age > 20IGNORE INDEX：忽略特定索引
SELECT * FROM users IGNORE INDEX(idx_name) WHERE age > 20FORCE INDEX：强制使用特定索引
SELECT * FROM users FORCE INDEX(idx_age) WHERE age > 20使用建议：SQL提示应谨慎使用，通常只在优化器明显选择错误时才考虑。
4.2 覆盖索引优化
覆盖索引是指查询所需的所有列都包含在索引中，无需回表查询数据行。
优势：
- 减少IO操作
- 提高查询效率
创建覆盖索引示例：
-- 假设经常查询name和age CREATE INDEX idx_name_age ON users(name, age); -- 以下查询可以使用覆盖索引 SELECT name, age FROM users WHERE name = '张三';4.3 前缀索引设计
对于长字符串列，可以使用前缀索引节省空间：
-- 选择合适的前缀长度 CREATE INDEX idx_email_prefix ON users(email(5));确定最佳前缀长度的方法：
SELECT COUNT(DISTINCT LEFT(email, 5))/COUNT(*) AS selectivity5, COUNT(DISTINCT LEFT(email, 6))/COUNT(*) AS selectivity6 FROM users;选择选择性接近完整列值的前缀长度。
4.4 联合索引设计原则
联合索引比多个单列索引更高效：
设计原则：
- 将选择性高的列放在前面
- 经常一起查询的列组合在一起
- 考虑查询的排序和分组需求
示例：
-- 好的设计 CREATE INDEX idx_status_create_time ON orders(status, create_time); -- 常用查询 SELECT * FROM orders WHERE status = 'PAID' ORDER BY create_time DESC;5. 索引设计的最佳实践
最后，我们总结索引设计的系统性原则。
5.1 索引创建的基本原则
选择性原则：选择区分度高的列建立索引
- 计算方法：
COUNT(DISTINCT col)/COUNT(*) - 高于0.1通常适合建索引
- 计算方法：
适度原则：避免过度索引
- 每个索引都会增加写操作开销
- 建议单表索引不超过5-6个
避免NULL原则：尽可能使用NOT NULL约束
- NULL值会使索引更复杂
- 影响优化器的判断
5.2 索引维护策略
- 定期分析表：
ANALYZE TABLE users;- 检查未使用的索引：
SELECT * FROM sys.schema_unused_indexes;- 重建碎片化严重的索引：
ALTER TABLE users REBUILD INDEX idx_name;5.3 索引优化检查清单
在实际项目中，可以使用以下检查清单评估索引设计：
- 所有重要查询是否都有合适的索引支持？
- 复合索引的列顺序是否合理？
- 是否存在冗余或未使用的索引？
- 索引的选择性是否足够高？
- 是否考虑了覆盖索引的可能性？
- 长字符串字段是否考虑使用前缀索引？
- 索引命名是否符合团队规范？
通过系统性地应用这些索引优化技术，可以显著提升数据库查询性能，为应用提供更好的响应速度。在实际工作中，建议结合具体的业务查询模式和数据特点来设计索引，并持续监控和优化。