mysql 数据库以及sql 的优化

时间:2022-08-14 23:25:03

使用案例

表结构说明

  • 用户账号表(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 优化 分析

mysql 数据库以及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`);

最后查询结果是非常快的了

 

mysql 数据库以及sql 的优化

 

那么为什么没有在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的规范有很多可以吸取的地方