查询优化经验分享
背景:前端做了限制,如果一个接口执行时间超过20秒,判定接口执行错误,并且查询条件的字段上面也建立了索引。
实际生产中经常会有一条查询语句执行20秒以上的情况,数据量大并且连表(子查询)以及一些函数计算,造成索引失效。
最近的一次解决思路:
①查询需要的数据,不要再sql中使用函数计算(加减乘除以及平均数,总和等)
②可拆解的sql,拆分查询。
③需要的数据在代码中Stream流过滤,逻辑计算
④如果生产的cpu核数是多核的话,可以用并行流计算提高代码中的效率
效率对比
之前的查询速度:22秒
修改之后的查询速度:7秒(由于生产服务器是单核,放弃了并行流)
可能有的人会执着于建索引,sql优化。由于时间范围的查询,在数据量达到一定级别的时候(大概2万左右),datetime类型的索引,数据库还是会走全表扫描,导致索引失效。
插入数据的优化
MyISAM数据表插入数据的优化
1.禁用索引
禁用索引:ALTER TABLE table_name DISABLE KEYS;
开启索引:ALTER TABLE table_name ENABLE KEYS;
2.禁用唯一性检查
禁用唯一性检查:SET UNIQUE_CHECKS = 0;
开启唯一性检查:SET UNIQUE_CHECKS = 1;
3.禁用外键检查
禁用外键检查:SET foreign_key_checks = 0;
开启外键检查:SET foreign_key_checks = 1;
4.批量导入数据
LOAD DATA INFILE ‘data_file_path’ INTO TABLE table_name;
InnoDB适用于上面4条
另外InnoDB是支持事务的
禁用事务 set autocommit = 0;
开启事务 set autocommit = 1;