使用案例
表结构说明
- 用户账号表(account),主要存储用户账号、密码、注册时间等信息,1万条数据
- 用户基本信息表(userinfo),主要存储用户个人信息,包括年龄、性别等,关联 account 表,关联字段 account_id,1万条数据
- 订单表(orderinfo),主要存储用户订单信息,关联account 表,关联字段 account_id,10万条数据
业务需求说明
统计出年龄大于 30 岁,性别为女(0)的用户所下订单的总数量。 当然用其他方式可以实现,但这里不考虑非数据库处理的其他方式
分析:
数量需要查询order表,但是 年龄在userinfo 表,他们没有直接的关联关系,他们和account表有关联
所以这里一定是 from account 表 ,然后left join 其他的表
SELECT COUNT(*) FROM account a LEFT JOIN userinfo u ON u.account_id = a.id LEFT JOIN orderinfo o ON o.account_id = a.id WHERE u.age > 30 AND u.sex = 0 AND o.id IS NOT NULL;
没有添加索引的时候查询结果 需要25秒
使用sql 优化 分析
考虑添加索引,添加索引在 where 条件 以及order by 等字段上
userinfo 和 order 和 account 有关联,那么我们考虑先在 userinfo 上加
添加索引
ALTER TABLE userinfo ADD INDEX index_account_id (`account_id`);
注意:这里是` 不是单引号
查询索引
SHOW INDEX FROM userinfo;
再次查询 结果变成了5秒
然后再order 上加索引
ALTER TABLE orderinfo ADD INDEX index_account_id (`account_id`);
最后查询结果是非常快的了
那么为什么没有在age 和 sex 上加索引呢
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件,也就是区分度太低,
比如性别,比如查看性别的区分度可以用这个语句:
2、较频繁的作为查询条件的字段应该创建索引
SELECT count(*), sex FROM userinfo GROUP BY sex; +----------+------+ | count(*) | sex | +----------+------+ | 5000 | 0 | | 5000 | 1 | +----------+------+
可以看到,一共有两个性别,每个5000,即使加了索引,每次也需要扫描一半的数据。
3、更新非常频繁的字段不适合创建索引;
4、不会出现在 WHERE 子句中的字段不该创建索引
SQL 优化 explain
参考:https://www.cnblogs.com/fengzheng/p/8916125.html
尽量去避免聚合操作
聚合操作如count,group等,是数据库性能的大杀手,经常会出现大面积的表扫描和索表的情况,所以大家能看到很多平台都把数量的计算给隐藏了,商品查询不去实时显示count的结果。如淘宝,就不显示查询结果的数量,只是显示前100页。
避免聚合操作的方法就是将实时的count计算结果用字段去存储,去累加这个结果。当然,也可以考虑用spark等实时计算框架去处理,
程序的优化很多时候都是一些细节的问题,更应该注意平时的积累,阿里SQL的规范有很多可以吸取的地方