性能优化之数据库优化

1、何为性能优化
1.1.用户请求到响应,网络+服务+数据库+前端页面渲染,缺一不可
1.2.二八原则,80%的性能问题出现在20%的代码,找到关键点进行优化,0.01秒的查询再优化也提升不了体验
 
2、数据库优化
 
2.1.索引
2.1.1.分类
功能性:唯一索引,其他为辅助查询
        唯一索引Unique,比如role_info的role_id
        普通索引Normal
2.1.2.三星索引
一星:创建索引把数据都放在一起,
        反例:单列索引,会索引合并,效率不高
        最好用联合索引
二星:排序走索引(group by本质也是排序)
        把排序字段一起放到联合索引中
三星:只查索引不回表
        查询的那几个字段正好都是有索引的
2.1.3.多列索引
a) 多列索引效率要比单列索引高
         联合索引
b) 列顺序非常重要,index(del_flag, form_status, customer_org_id, supplier_org_id)
         注意:最左原则
c) 将经常查询的、枚举的列放在最前,比如del_flag,当查询全部时可以用del_flag in (1,0)来走索引
2.1.4.强制索引
当mysql没有按照预想的索引解析,且效率较慢,可以使用force index(idx_test)来强制指定索引
from role_info force index(idx_test)
一般来说possible keys其中数据库会择优选择一个好的索引key
2.1.5.其他
a) Null值不会被包含在索引中
b)唯一索引对null值不生效,例如:一个多列唯一索引unindex(A, B),当A为null时,B值相同仍能入库;
索引的那个字段不要为NULL,故字段最好设个默认值0或””,不要为NULL
 
2.2.查询
a) InnoDB中的or语句(or查询可以考虑用union all替换)、
where age + 1 = 12、where fun(age) = 12、<>、not in、!=不走索引,
查询类型不一致也会导致不走索引(where a.meun_id = b.menu_id a表的是int而b表的是varchar)
注意:索引走的值是字段age,如果对其修改了,比如计算+x,函数(age),这时都不会走索引
比如:select * from role_info where age = 18 or age = 28改成
select * from role_info where age = 18
union all
select * from role_info where age = 28
b) 查询要根据实际业务场景,预估每个表内的大致数据量
c) 复杂查询拆分为简单查询
d) 当发现索引和查询已经无法继续优化时,让java做或者换种方式实现
注意:最好是单表查询,每个表可以添加冗余字段(org_code和org_name),避免多表查询(除了主表和明细表)
 
2.3.合格标准
a) 每个请求必须在0.5秒内执行完成
b) MySQL获取到符合要求的数据就会停止查询,所以当查询语句有limit时,要测试最后一页的查询速度
     尽量不要select *
     分页查询,第一页响应时间100ms,但最后一页需400ms
 
2.4执行计划
sql语句前,写explain
explain
select *
from role_info  可以查看执行计划
 
id:多个查询,数字越大越先执行
rows:扫描的行数,数量尽量减少
possible_keys:查询可能使用到的索引都会在这里列出来
key:查询用到的索引
ref:表示走了几个索引字段(const, const)
type:ref、range、index_merge、index、all
  最好可以到ref,不要all,除了all,其他都用到了索引 
 
2.5.补充
(1)索引就像书本的目录,目录可以快速找到所在页数,数据库中索引可以帮助快速找到数据,而不用全表扫描
(2)数据库的优化:sql语句优化,索引优化等(一般只掌握这两个基础优化)
(3)sql语句的优化:
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
最好有where条件限制,where和order by的字段做一个联合索引。
2. MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE(abc%)。
3.in 和 not in 也要慎用,否则会导致全表扫描,对于连续的数值,能用 between 就不要用 in 了:Select id from t where num between 1 and 3
4. 索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引。一个表的索引数最好不要超过6个
5. 将需要查询的结果预先计算好放在表中,,查询的时候再Select
表中添加需要的冗余字段
6. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率
7. 当有一批处理的插入或更新时,用批量插入或批量更新,绝不会一条条记录的去更新!
不要在for循环中,一条条去插入或更新,for循环前新建List<SysRoleInfo> insertList
8. 在适当的情形下使用GROUP BY而不是DISTINCT
9. 索引创建规则:
表的主键、外键必须有索引(业务主键uuid建唯一索引);
经常与其他表进行连接的表,在连接字段上应该建立索引;
经常出现在Where子句中的字段,应该建立索引;
索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
频繁进行数据操作的表,不要建立太多的索引;
删除无用的索引,避免对执行计划造成负面影响;
尽量不要对数据库中某个含有大量重复的值的字段建立索引。
 
2.6.实例
(1)
SELECT count(*) AS value, date_format(add_time, '%Y-%m-%d') AS name
FROM order_purchase_info
WHERE del_flag = 0 AND form_type = 1 AND customer_org_id = '9395C427D83D4986AB0932A33D1C75BB' AND date_format(add_time, '%Y-%m-%d') BETWEEN "2019-03-18" AND "2019-03-25"
GROUP BY date_format(add_time, '%Y-%m-%d');
 
1.创建一个索引idx_dfca(del_flag, form_type, customer_org_id, add_time)
查询列与索引列次序可以不一致,最好一致
2.AND date_format(add_time, '%Y-%m-%d') BETWEEN "2019-03-18" AND "2019-03-25"
改成AND add_time BETWEEN "2019-03-18" AND "2019-03-25 23:59:59"
原先的条件里字段add_time被修改了,故不会走索引
3.排序的add_time被改动了,故排序这的add_time不会走索引
4.最左原则(索引次序):
比如有1000条数据,del_flag=0有700条,则接下去就会在这700条数据中,
继续查询form_type=1的,如果有400条,则继续在这400条中查询
执行计划中,ref有const,const,const,说明就走了三个索引,dfc
如果索引idx_dfac(del_flag, form_type, add_time, customer_org_id)
因为add_time这个索引没有走,即条件add_time = 无,
所以就不会走下一个所以c,故ref就两个const
索引次序从左往右走,有一个索引,该索引不在条件中,则停止走了
5.查询指定日期的,用mysql函数:
今天TO_DAYS(add_time) = TO_DAYS(NOW())
昨天DATEDIFF(NOW(), add_time) = 1,上周同期DATEDIFF(NOW(), add_time) = 7
都改成add_time BETWEEN "2019-03-21" AND "2019-03-21 23:59:59",才会走索引
 
(2)
SELECT rm.menu_id
FROM role_menu_relation_info rm
WHERE rm.del_flag = 0
AND EXISTS (
  SELECT role_id
  FROM user_role_relation_info ri
  WHERE rm.role_id = ri.role_id AND ri.del_flag = 0 AND ri.user_id = 'E45EA2B1599D5A5CE040007F010020E1' AND ri.org_id = '9395C427D83D4986AB0932A33D1C75BB'
);
改成
SELECT rm.menu_id
FROM role_menu_relation_info rm
INNER JOIN user_role_relation_info ri ON rm.role_id = ri.role_id AND ri.del_flag = 0 AND ri.user_id = 'E45EA2B1599D5A5CE040007F010020E1' AND ri.org_id = '9395C427D83D4986AB0932A33D1C75BB'
WHERE rm.del_flag = 0;

 

1. 用EXISTS子查询,外表rm:all,并没有走索引,内表ri走索引了
select * from a where a_name in (select b_name from b)
select * from a where exists (select b_id from b where b.b_name=a.a_name)
exists子句返回的结果并不是从数据库中取出的结果集,而是一个布尔值,如果子句查询到数据,那么返回true,反之返回false。
所以子句中选择的列根本就不重要,而重要的是where 后的条件。如果返回了true,那么相当于直接执行了子句 where 后的部分,即把a_name 和 b_name 作比较,如果相等则返回这条数据
2. 用多表连接代替EXISTS子句
INNER JOIN   ON 两个表都走索引了
3.尽量用EXISTS代替IN
   一般INNER JOIN > EXISTS >IN,可以比较所用时间和查询执行计划,用效率高的
 
(3)
SELECT count(*) AS countNumber, 1 AS type
FROM order_deliver_info
WHERE del_flag =0 AND customer_org_id = '9395C427D83D4986AB0932A33D1C75BB' AND add_time BETWEEN "2019-03-21" AND "2019-03-21 23:59:59"
UNION ALL
SELECT count(*) AS countNumber, 2 AS type
FROM order_deliver_info
WHERE del_flag =0 AND customer_org_id = '9395C427D83D4986AB0932A33D1C75BB' AND add_time BETWEEN "2019-03-20" AND "2019-03-20 23:59:59"
UNION ALL
SELECT count(*) AS countNumber, 3 AS type
FROM order_deliver_info
WHERE del_flag =0 AND customer_org_id = '9395C427D83D4986AB0932A33D1C75BB' AND DATEDIFF(NOW(), add_time) = 7;

 

union all的使用,结果type 1 2 3 对应countNumber 10 20 30
 
 

posted @ 2021-05-17 15:44  沐兮sc  阅读(380)  评论(0编辑  收藏  举报