SQL高阶技巧 - 性能优化和查询过程

变量和二次查询

@用户变量名是mysql的一种语法，MySQL中的用户变量以一个“@”开头。根据作用范围不同，又分为会话用户变量和局部变量。在进行二次查询或者在处理过程中涉及到变量的时候需要，大家基本不会用到。下面是个例子可以考验下自己是想表达什么样的查询。

            select * from (
  SELECT *,  (case
                  when @field_value = brand
                      then @row_num := @row_num + 1
                  else @row_num := 1
                 end) as number,
           ##重新赋值
          @field_value:= brand FROM ( 
 SELECT brand,cat ,title, SUM(saleNum) AS num, SUM(saleNum*price) AS gmv FROM out_phone_mix_db WHERE    cat='pad' 
and  brand in ( select brand from ( select brand, count(*) as 商品数, sum(saleNum) as num, sum(saleNum*price) as gmv from out_phone_mix_db   WHERE
  cat='pad' group by brand order by  num desc limit 10) b)  
 
  GROUP BY brand,cat,title  ORDER BY brand ,num  DESC  ) c )d where  number<=10
          

存储过程

存储过程是预编译的SQL语句集合，可以接受参数、执行复杂逻辑，提高代码复用性和执行效率。

            CREATE PROCEDURE GetStudentByAge(IN min_age INT)
BEGIN
  SELECT * FROM student WHERE age >= min_age;
END;

          

视图查询

视图是基于SQL查询结果的虚拟表，可以简化复杂查询，提高数据安全性和查询效率。


			查询视图和查询表一样。

使用索引

索引是提高查询性能的关键

            -- 创建索引
			
CREATE INDEX idx_student_name ON student(name);

-- 创建复合索引

CREATE INDEX idx_grade_score ON student(grade, score);

-- 查看索引

SHOW INDEX FROM student;

💡

索引可以加快查询速度，但会降低插入、更新和删除的速度，需要权衡使用

避免SELECT *

只查询需要的列

            -- ❌ 不推荐
SELECT * FROM student WHERE id = 1;

-- ✅ 推荐
SELECT id, name, age, department FROM student WHERE id = 1;

明确指定列名可以减少数据传输量，提高查询效率

使用LIMIT限制结果

避免返回过多数据

            -- 限制返回结果数量
			
SELECT * FROM student
ORDER BY name DESC
LIMIT 100;

-- 分页查询

SELECT * FROM student
ORDER BY id
LIMIT 20 OFFSET 40;

优化WHERE条件

合理使用WHERE子句

            -- ❌ 避免在WHERE中使用函数
			
SELECT * FROM student WHERE YEAR(birth_date) = 2000;

-- ✅ 改为范围查询

SELECT * FROM student 
WHERE birth_date BETWEEN '2000-01-01' AND '2000-12-31';

-- ❌ 避免使用NOT IN

SELECT * FROM student WHERE department NOT IN ('计算机系', '数学系');

-- ✅ 使用NOT EXISTS或其他方式

SELECT * FROM student WHERE grade NOT IN ('计算机系', '数学系');

优化子查询

尽量使用JOIN代替子查询

            -- ❌ 使用子查询
			
SELECT * FROM student
WHERE grade IN (SELECT grade FROM classes WHERE teacher = '张老师');

-- ✅ 使用JOIN

SELECT s.* FROM student s
INNER JOIN classes c ON s.grade = c.grade
WHERE c.teacher = '张老师';

使用EXPLAIN分析查询

查看查询执行计划

            -- 分析查询性能
			
EXPLAIN SELECT * FROM student WHERE age > 20;

-- 分析JOIN查询

EXPLAIN SELECT s.*, c.class_name
FROM student s
INNER JOIN classes c ON s.class_id = c.id;

EXPLAIN可以显示查询的执行计划，帮助识别性能瓶颈

性能优化建议

常见的优化技巧

1. 合理使用索引 - 在经常查询的列上创建索引
2. 避免全表扫描 - 使用WHERE条件过滤数据
3. 优化JOIN操作 - 确保JOIN字段有索引
4. 减少数据传输 - 只查询需要的列和行
5. 使用批量操作 - 批量插入和更新数据
6. 定期维护 - 定期分析和优化表

变量和存储过程--在线练习暂无