第 10 卷

中主键索引默认是聚簇索引。查询时找到索引即可直接获取数据，效率高。

1. 非聚簇索引（Secondary Index）：索引与数据分离，索引的叶子节点存储的是主键值，查询时需

先通过非聚簇索引找到主键，再通过主键索引获取数据（此过程称为“回表查询”）。普通索引、唯一索引、复合索引均为非聚簇索引。

十五、MySQL如何执行查询优化？

查询优化的核心目标是让MySQL避免全表扫描，尽量使用索引快速定位数据，减少查询过程中的资源消耗（CPU、内存、IO）。优化手段围绕“索引优化”“SQL语句优化”“表结构优化”“配置优

化”展开，具体如下：

1. 索引优化（最核心的优化手段）
2. 为频繁查询的字段创建索引：优先为WHERE条件、JOIN关联字段、ORDER BY排序字段、GROUP

BY分组字段创建索引。

1. 合理使用复合索引：多字段联合查询时，创建复合索引并遵循“最左前缀匹配原则”，避免重复创

建单一字段索引（如已创建idx_age_gender，无需再创建idx_age）。

1. 避免索引失效：以下情况会导致索引失效，需规避：

• 索引字段使用函数或运算： SELECT * FROM user WHERE SUBSTR(username,1,3)='张

三';（应改为 WHERE username LIKE '张三%'）

• 索引字段使用!=、<>、NOT IN、IS NOT NULL：可能导致全表扫描，尽量用等值查询替代。
• 模糊查询以%开头： SELECT * FROM user WHERE username LIKE '%张三';（索引失

效，改为 '张三%'）

• 复合索引不满足最左前缀：如复合索引idx_age_gender，查询 WHERE gender='男'（索引失

效）。

• 隐式类型转换：字段为VARCHAR类型，查询时用数字： WHERE username=123;（应改为

username='123'）

1. 定期维护索引：删除冗余索引（重复、无用的索引）和碎片化索引，通过 EXPLAIN 分析索引使用

情况。

1. SQL语句优化
2. 避免SELECT *：仅查询需要的字段，减少数据传输量和IO消耗，同时避免回表查询（非聚簇索引场

景）。

优化前： SELECT * FROM user WHERE age=25;

优化后： SELECT id, username FROM user WHERE age=25;

• 小表驱动大表：让小表作为驱动表（INNER JOIN会自动优化，LEFT JOIN需将小表放在左表）。

1. 优化子查询：将子查询改为JOIN查询，避免MySQL多次执行子查询（子查询效率低，JOIN更高

效）。

优化前： SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE status=1);

优化后： SELECT u.* FROM user u INNER JOIN order o ON u.id = o.user_id WHERE o.status=1;

1. 避免在WHERE子句中使用OR：OR会导致索引失效，可用UNION替代。

优化前： SELECT * FROM user WHERE age=25 OR username='张三';

优化后： SELECT * FROM user WHERE age=25 UNION SELECT * FROM user WHERE username='张三';（需确保两个查询字段一致）

1. 表结构优化
2. 选择合适的数据类型：遵循“最小够用”原则，减少存储空间和IO消耗：

• 整数类型：优先用TINYINT（1字节）、SMALLINT（2字节）、INT（4字节），避免用BIGINT

（8字节）；

• 字符串类型：短文本用VARCHAR（可变长度，节省空间），长文本用TEXT（避免VARCHAR过

长）；

• 日期类型：用DATE（日期）、TIME（时间）、DATETIME（日期时间），避免用字符串存储日期

（无法排序、查询效率低）。

1. 避免使用NULL值：NULL值会增加索引和查询的复杂度，可用默认值替代（如用0替代NULL的年

龄，用空字符串替代NULL的备注）。

1. 分表分库：当单表数据量过大（超过1000万行）时，拆分表或库提升性能：

• 水平分表：按数据范围（如时间）或哈希值拆分（如用户ID取模），每张表结构相同；
• 垂直分表：将表中不常用的大字段（如TEXT）拆分到单独表，减少主表数据量；
• 分库：按业务模块拆分数据库（如用户库、订单库），减轻单库压力。

1. 使用合适的存储引擎：核心业务表用InnoDB（支持事务、高并发），只读表用MyISAM（查询

快）。

1. 配置优化（MySQL参数调整）
2. 调整缓存参数：

• innodb_buffer_pool_size：InnoDB数据缓存池大小，建议设置为服务器内存的

50%-70%（提升数据缓存命中率，减少磁盘IO）；

• innodb_thread_concurrency：InnoDB并发线程数，建议设置为CPU核心数的2-4倍。

1. 调整日志参数：

• innodb_log_file_size：InnoDB重做日志文件大小，建议设置为512MB-2GB（提升事务

日志写入效率）；

• slow_query_log：开启慢查询日志（设置为ON），记录执行时间超过

long_query_time（默认10秒）的SQL，用于定位慢查询。

1. 工具辅助优化：EXPLAIN分析查询计划

EXPLAIN是MySQL的核心优化工具，通过 EXPLAIN + SQL语句可查看SQL的执行计划（如是否使用索引、扫描行数、连接方式等），从而定位优化点。

示例： EXPLAIN SELECT * FROM user WHERE age=25;

关键字段说明：

• type：查询类型，取值从好到坏为：system > const > eq_ref > ref > range > index > ALL（ALL

表示全表扫描，需优化）；

• key：实际使用的索引（NULL表示未使用索引）；
• rows：MySQL预估的扫描行数（行数越少，效率越高）；
• Extra：额外信息（如Using index表示使用覆盖索引，无需回表；Using filesort表示排序未使用

索引，需优化）。

十六、解释SQL注入攻击以及如何防止？

1. SQL注入攻击的核心定义

SQL注入（SQL Injection）是一种常见的Web安全漏洞，攻击者通过在用户输入的参数中插入恶意 SQL语句，欺骗数据库执行非预期的操作（如查询敏感数据、修改数据、删除表、甚至获取数据库权

限）。其本质是开发者未对用户输入进行严格过滤，导致用户输入直接拼接到SQL语句中执行。

1. SQL注入攻击的示例

以用户登录功能为例，正常的SQL逻辑如下（假设用户输入用户名和密码，后端拼接到SQL中查询）：

Code block

password='${password}'`;