学习目标：

根据题目，进行 单表优化 操作

---

学习内容：

1、 第一题与第二题的优化 2、 第二题or的优化 3、 第四题

---

学习产出：

1、 第一题与第二题的优化

题目一：女性客户数量和平均月薪；

sql语句

select count(*),avg(monthsalary) from customers1 where gender = 0;

如何优化？？

第一步：查看表结构是否合理，字段数据类型等 是否还有优化的地方

desc table_name;

例：id 身份证 varchar(20) 可修改为 char(18)

第二步：查看表的索引使用情况

show index from customersl;

分析上面的sql语句后,可加一个 年龄与月薪 的联合索引

alter table customers1 add index index_gender_monthsalary(gender,monthsalary);

题目二：女性客户数量和平均月薪；

第一步：explain 分析sql语句

explain select count(*),avg(monthsalary) from customers1 group by city;

查询时间用了 3秒 Extra - Using temporary; 生成了临时表 属于慢查询 需要优化

第二步：发现与第一的的索引重叠了，可进行索引合并 （在使用索引优化的时候，可以考虑与其他查询的索引合并）

alter table customers1 add index index_city_gender_monthsalary(city,gender,monthsalary);

优化后只需 2 秒，达到了覆盖索引

2、 第三题or的优化

题目三：列出没有手机号，或者没有照片，或者没有年奖金的客户姓名；

select `name` from customers1 where mobile = '0' or photo is NULL or yearbonus = '0';

对于数据库调整，把一些允许为空的字段修改为0或其他特殊字符代替，这里我们把某一些字段改为默认值为0 表示null；

因为范围查询 三个联合索引会失效 添加组合索引即可

alter table customers1 add index idx_mobile_name(mobile,`name`); alter table customers1 add index idx_photo_name(photo,`name`); alter table customers1 add index idx_yearbonus_name(yearbonus,`name`);

优化后 都使用到可 覆盖索引

select `name` from customers1 where mobile = '0' union all select `name` from customers1 where photo is null union all select `name` from customers1 where yearbonus = '0';

查询的数据类型也很重要，比如 mobile varchar(10), where mobile = 138… 的效率会比 where mobile = ‘138…’ 慢很多

3、 第四题

题目三：不同年龄段（0-100岁之间，每10岁为一个年龄段）的平局年收入

查询会很复杂

select elt(interval(TIMESTAMPDIFF(YEAR, birthdate, CURDATE()), 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100), '0-10', '10-20', '20-30', '30-40', '40-50', '50-60', '60-70', '70-80', '80-90', '90-100', '100>' ) as age_level, avg((monthsalary * 12 + yearbonus)) as income from customers1 group by elt(interval(TIMESTAMPDIFF(YEAR, birthdate, CURDATE()), 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100), '0-10', '10-20', '20-30', '30-40', '40-50', '50-60', '60-70', '70-80', '80-90', '90-100', '100>' );

总结：

用到 elt 函数 区间分组

TIMESTAMPDIFF 函数根据生日或日期计算年龄

函数索引

创建函数索引 1、添加一个虚拟的字段 alter table class add column scorce_generated int generated always as (ROUND(score)); 2、添加索引 alter table class add key idx_scorce_generated(scorce_generated);

年龄可业务上计算出 再去做 查询