有哪些商业网站,wordpress注册网址,旅游网站建设规模,wordpress建立栏目Mysql高级篇#xff08;中#xff09;—— 索引优化 一、索引分析案例案例 1#xff1a;单表查询案例 2#xff1a;两表连接查询案例 3#xff1a;三表连接查询 二、避免索引失效常见索引失效场景简述场景 1场景 2场景 3场景 4场景 5场景 6 三、索引优化文字版示例版 一、… Mysql高级篇中—— 索引优化 一、索引分析案例案例 1单表查询案例 2两表连接查询案例 3三表连接查询 二、避免索引失效常见索引失效场景简述场景 1场景 2场景 3场景 4场景 5场景 6 三、索引优化文字版示例版 一、索引分析案例 使用 EXPLAIN 分析 SQL 查询性能是数据库优化的重要环节。EXPLAIN 能展示查询的执行计划帮助我们找出潜在的性能瓶颈。我们可以从 EXPLAIN 输出中的多个列如 type、rows、Extra、key 等来分析查询的执行情况 常见的优化手段包括 创建单列或复合索引、避免全表扫描、减少排序操作以及充分利用数据库索引覆盖 等。以下我将详细分析单表、两表、三表查询案例给出性能分析和优化建议。 案例 1单表查询
场景描述 我们有一个 employees 表包含员工的姓名、年龄、部门等信息结构如下 CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),age INT,department_id INT,hire_date DATE,salary DECIMAL(10, 2),INDEX (department_id),INDEX (age)
);
查询要求查找年龄在 30 岁以上的某部门的所有员工。
SELECT * FROM employees WHERE department_id 1 AND age 30;使用 EXPLAIN 分析该查询
EXPLAIN SELECT * FROM employees WHERE department_id 1 AND age 30;案例 2两表连接查询
场景描述 我们有两个表employees 和 departments。查询要求是找到所有在 HR 部门的员工并按他们的薪水降序排列。 SELECT e.name, e.salary FROM employees e
JOIN departments d ON e.department_id d.id
WHERE d.name HR ORDER BY e.salary DESC;使用 EXPLAIN 分析该查询
EXPLAIN SELECT e.name, e.salary FROM employees e
JOIN departments d ON e.department_id d.id
WHERE d.name HR ORDER BY e.salary DESC;案例 3三表连接查询
场景描述 假设我们有三个表employees、department 和 projects查询要求是查找所有参与某个项目的员工姓名、薪水和所属部门。 SELECT e.name, e.salary, d.name FROM employees e
JOIN departments d ON e.department_id d.id
JOIN projects p ON e.id p.employee_id
WHERE p.project_name Project A;使用 EXPLAIN 分析该查询
EXPLAIN SELECT e.name, e.salary, d.name FROM employees e
JOIN departments d ON e.department_id d.id
JOIN projects p ON e.id p.employee_id
WHERE p.project_name Project A;二、避免索引失效 索引失效 问题是指数据库中的查询操作无法正确使用已有的索引导致查询效率大幅下降。这通常发生在 SQL 语句的编写方式、表结构的设计或数据的变化未能与索引策略相匹配时。以下是几种常见的索引失效原因及避免方法结合案例来说明。 常见索引失效场景简述
序号索引失效场景1索引列上进行计算、函数、类型转换等操作2使用 !、 或者 NOT IN等操作3is nullis not null也无法使用索引4OR 条件5like以通配符开头(‘%abc…’)的操作6使用复合索引时 没有遵循最佳左前缀法则 上述场景示例解析:
场景 1
当查询条件 使用了函数或表达式 时索引会失效。 例如对于表 users如果在 name 字段上有索引但执行以下查询
SELECT * FROM users WHERE UPPER(name) JOHN;由于 UPPER(name) 使用了函数MySQL 无法利用 name 字段上的索引。要避免这种情况可以在插入或更新数据时将数据规范化为大写或小写或者改用不依赖函数的查询方式
SELECT * FROM users WHERE name john; -- 假设所有 name 都存储为小写当 字段和查询条件的类型不一致 时数据库会进行 隐式类型转换 这也会导致索引失效。例如id 字段是整数类型但查询时传入的是字符串
SELECT * FROM users WHERE id 123; -- id 为 INT因为 id 字段是整数类型数据库会尝试将 123 转换为数字导致索引无法使用。正确做法是确保数据类型一致
SELECT * FROM users WHERE id 123;场景 2
在使用 不等操作符 (!、) 或者 NOT IN 时索引通常不会生效。例如以下查询可能无法使用索引
SELECT * FROM users WHERE age ! 30;要避免这种情况可以改用其他逻辑如将查询拆分为多个条件在特定情况下适用
SELECT * FROM users WHERE age 30 OR age 30;场景 3 场景 4 场景 5 场景 6 最左前缀法则 是数据库中针对 复合索引 使用的一个原则。它指的是在使用复合索引时查询条件必须按照 索引中从左到右的顺序并且不跳过索引中间的列 使用从第一个开始逐步向右才能有效利用索引。 三、索引优化
文字版 示例版
