性能优化之数据库优化

本文主要是介绍性能优化之数据库优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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执行计划 参考文章：https://www.cnblogs.com/xiaoboluo768/p/5400990.html 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.补充 参考文章：https://blog.csdn.net/weixin_38112233/article/details/79054661 （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    

这篇关于性能优化之数据库优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！