四、 SQL优化

一、 SQL优化:

详情参考:这才是SQL优化的正确姿势
在这里插入图片描述

1. 单条 SQL 运行慢

问题分析:
造成单条 SQL 运行比较慢的常见原因有以下两个:

  1. 未正常创建或使用索引;
  2. 表中数据量太大。

解决方案:

  1. 创建并正确使用索引;
  2. 数据拆分:①.垂直拆分、②.水平拆分。

1.创建并正确使用索引:

  • 使用 explain 命令来分析 SQL 的执行情况:执行结果中主要关注的是,type 和 key:
    type = ALL :表示全表扫描;
    type = const :表示通过索引一次就找到了。
    key = NULL:表示没有使用索引;
    key = primary :表示使用了主键;
    key一般=使用了主键/索引的名字
  • 避免不走索引的情况:
    ①尽量避免在where子句中使用or来连接条件;
    ②尽量避免在where子句中使用否定判断: !=(<>) not in not exists;
    ③尽量避免在where子句中进行null值判断:is null. is not null;
    ④尽量避免使用前导模糊查询:也就是 ‘%XX’ 或 ‘%XX%’,由于前导模糊不能利用索引的顺序,必须一个个去找,看是否满足条件,这样会导致全索引扫描或者全表扫描;
    ⑥用union all替换union,使用exists代替in;

2.数据拆分:

  • 垂直拆分:把一张列比较多的表拆分为多张表,插入数据时,使用事务确保两张表的数据一致性。垂直拆分的原则:①经常组合查询的列放在一张表中;②把不常用的字段单独放在一张表;③把 text,blob 等大字段拆分出来放在附表中;
  • 水平拆分:将数据表行进行拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。我们使用取模的方式来进行表的拆分,比如,一张有 400W 的用户表 users,为提高其查询效率我们把其分成 4 张表 users1,users2,users3,users4,然后通过用户 ID 取模的方法,同时查询、更新、删除也是通过取模的方法来操作。

2. 部分 SQL 运行慢

部分 SQL 运行比较慢,我们首先要做的就是先定位出这些 SQL,然后再看这些 SQL 是否正确创建并使用索引。也就是说,我们先要使用慢查询工具定位出具体的 SQL,然后再进行单条 SQL优化

MySQL 中自带了慢查询日志的功能,指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。long_query_time 的默认值为 10,意思是运行 10S 以上的语句。
使用 mysql> show variables like '%slow_query_log%'; 来查询慢查询日志是否开启,结果中slow_query_log 的值为 OFF 时,表示未开启慢查询日志。
使用如下mysql> set global slow_query_log=1开启慢查询日志(当前数据库生效,MySQL 重启也会失效),永久生效,就必须修改 MySQL 的配置文件 my.cnf,配置如下:slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log
当开启慢查询日志之后,所有的慢查询 SQL 都会被记录在 slow_query_log_file 参数配置的文件内,默认是 /tmp/mysql_slow.log 文件,此时我们就可以打开日志查询到所有慢 SQL 进行逐个优化。

3. 整个 SQL 运行慢

需要使用一些数据库的扩展手段来缓解 MySQL 服务器了。
解决方案:读写分离

  1. 应用层解决方案:通过应用层对数据源做路由来实现读写分离;
  2. 中间件解决方案:通过 MySQL 的中间件做主从集群。

二、 MySql和Oracle区别:

1. 选型:

单纯从表的数据量而言,MySQL的最佳实践建议是单表百万级,控制在千万级内;而Oracle单表可以千万级甚至亿级也没太大问题。而且,MySQL从一开始的设计目标便不是为了追求强一致性事务,这导致MySQL的可靠性和事务性方面就完全和Oracle不在一个可比较的级数。高并发压力情况下,MySQL丢数据的概率是远超Oracle的;然而Oracle的费用贵是无法回避的事实,Oracle数据库依然是昂贵的稀缺资源。应只把最核心,同时非常强调数据一致性的强事务类的业务放在Oracle上,用好NoSQL和分布式缓存,降低核心Oracle数据库的负载压力。

2. 区别:

1. 单引号的处理: MySQL字符串单引号、双引号都可以;Oracle双引号不可以;
2. 自动增长的数据类型处理: MySQL是一个自动增长的数据类型,插入数据的时候,不需要管理,它自己会自动增长;Oracle不支持自动增长的数据类型,通过建立一个自增的序列号来完成自动增长;
3. 事物提交方式: MySQL默认是自动提交。不支持事物;Oracle默认不自动提交,需要用户手动提交。
mysql默认自动提交,也就是你提交一个query,他就直接执行,我们可以通过
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
4. 分页的处理: MySQL处理翻页的SQL语句比较简单,用 Limit 开始位置,记录个数;Oracle处理翻页的SQL语句就比较繁琐了。每个结果集只有一个Rownum 字段标明它的位置,并且只能用 Rownum <100,不能用 Rownum >80;
不排序分页:(表需要起别名,不然会报错)

select * from (select a.*, rownum rn from 表名 a where rownum <= endPage) where rn >= startPage

排序之后分页:(表需要起别名,不然会报错)

SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM 表名 order by id) A WHERE ROWNUM <=endPage) WHERE RN >= startPage;

详细参考:Oracle数据库分页
5. 对应的日期与字符串转换函数:
1. Oracle:字符串转日期:to_date()、日期转字符串:to_char();
2. MySQL:字符串转日期:str_to_date()、日期格式化:date_format();

详细参考:Oracle/MySql下分别对应的日期与字符串转换函数

关于2中序列号sequence说明:
通过创建一个自增的序列号sequence来完成自动增长:
1.创建序列:
参考:实现ORACLE字段自增的方法

create sequence AutoID //(序列名常定义为‘seq_XXX’的形式,创建序列不能使用replace)
start with 1 //序列的初始值,缺省值为1;
increment by 1 //序列每次增加的值,负数表示递减,缺省值为1;
minvalue 1	//序列可生成的最小值,缺省值为nominvalue,即不设置最小值;
nomaxvalue	//maxvalue:序列可生成的最大值,缺省值为nomaxvalue,即不设置最大值;系统能产生的最大值为10的27次方;

//cycle:定义当序列达到最大/小值后是否循环,缺省值为不循环;nocycle:不循环;cycle:循环;如果不使用循环达到限制值后继续产生新值就会出错;使用循环达到最大值后的下一个值为1,和start with设置的值无关,递增还是increment by设置的值;
//cache:定义缓存序列的个数,缺省值为20,nocache表示不设置缓存;使用缓存可以提高序列的性能,但数据库出错时会造成数据丢失使序列不连续;

create sequence seq_logid1 maxvalue 999999999 increment by 1 start with 1 cycle nocache;
//序列的使用,结合字符用作含固定特征的唯一标识:
select ‘9|| substr(concat(00000000000,trim(to_char(SEQ_LOGID1.nextVal))),-10) from dual;
select seq_logid1.nextval from dual; –获取下一个数
select seq_logid1.currval from dual; –获取当前数

–查看序列:
select * from user_sequences; –当前用户下创建的序列
select * from all_sequences; –查看所有序列
–修改序列:
alter sequence seq_logid1 nomaxvalue nocycle;
–删除序列:
drop sequence seq_logid1;

2.添加:方法一:数据库中添加:

Insert into Test_Table(ID,Name,Value1)values(AutoID.nextval,'xiyang','blog')

3.方法二:代码中添加:
Service使用时:此"SEQ_BL_FILE_FIELD"即库中定义好的序列(相当于上方的AutoID):

public class StoreServiceImpl implements StoreService {
    @Autowired
    private SequenceDao sequenceDao;
    
    public boolean addStore(WgStorevo wgStorevo){
        Long id=sequenceDao.getSequence("SEQ_BL_FILE_FIELD");
        String name=wgStorevo.getName();
        Store store=new Store();
        store.setId(id);
        store.setName(name);
    }
}

SequenceDao :

@Mapper
public interface SequenceDao {
    //获取指定sequence
    @Select("select ${sequenceName}.nextVal from dual")
    Long getSequence(@param("sequenceName") String sequenceName);
}

三、Oracle中常见函数:

to_char()、trim()、concat()、substr()、|| 等等;
例:
select ‘9’ || substr(concat(‘00000000000’,trim(to_char(SEQ_LOGID1.nextVal))),-10) from dual;

select ‘9||substr(concat(00000000000,trim'(
						to_char(SEQ_LOGID1.nextVal)
											 )
				         ),-10
			       ) from dual;
//分解:			       
1.select SEQ_LOGID1.nextVal from dual; --42641
2.select concat(00000000000,trim'(to_char(SEQ_LOGID1.nextVa))) from dual; --0000000000042641
3.select substr(concat(00000000000,trim'(to_char(SEQ_LOGID1.nextVal))),-10) from dual; --0000042641
4.select ‘9||substr(concat(00000000000,trim'(to_char(SEQ_LOGID1.nextVal))),-10) from dual; --90000042641

1. to_char(): 功能是将数值型或者日期型转化为字符型;
2. trim(): trim()函数用于去除字符串的行首和行尾的空格;
3. concat(): concat()连接两个字符串;
4. || : 连接多个字符串;
5.substr(): 截取字符串;(a为正数表示从前开始数往后截取,为负数表示从后开始数往后截取)

  • 格式1(三个参数): substr(string string, int a, int b);
  • 格式2(两个参数):substr(string string, int a) ;

格式1: string 要截取的字符串,a 截取字符串的开始位置(注:当a等于0或1时,都是从第一位开始截取),b 要截取的字符串的长度;
格式2: string 要截取的字符串,a 可以理解为从第a个字符开始截取后面所有的字符串。

eg1:concat()与"||"区别:

.sql>select concat('aa','bb') from test;
 .sql>select 'aa'||'bb' from test;
结果:aabb
②.sql>select concat('aa','bb','cc') from test;
结果:ORA-00909: invalid number of arguments
 .sql>select 'aa'||'bb'||'cc' from test;
结果:aabbcc

eg2:substr()截取字符串:

//格式一:
1、select substr('HelloWorld',0,3) value from dual; //返回结果:Hel,截取从“H”开始3个字符
2、select substr('HelloWorld',1,3) value from dual; //返回结果:Hel,截取从“H”开始3个字符
3、select substr('HelloWorld',2,3) value from dual; //返回结果:ell,截取从“e”开始3个字符
5、select substr('HelloWorld',5,3) value from dual; //返回结果:oWo
6、select substr('Hello World',5,3) value from dual; //返回结果:o W (中间的空格也算一个字符串,结果是:o空格W)
7、select substr('HelloWorld',-1,3) value from dual; //返回结果:d (从后面倒数第一位开始往后取1个字符,而不是3个)
8、select substr('HelloWorld',-2,3) value from dual; //返回结果:ld (从后面倒数第二位开始往后取2个字符,而不是3个)
9、select substr('HelloWorld',-3,3) value from dual; //返回结果:rld (从后面倒数第三位开始往后取3个字符)
10、select substr('HelloWorld',-4,3) value from dual; //返回结果:orl (从后面倒数第四位开始往后取3个字符)
//格式二:
11、select substr('HelloWorld',0) value from dual;  //返回结果:HelloWorld,截取所有字符
12、select substr('HelloWorld',1) value from dual;  //返回结果:HelloWorld,截取所有字符
13、select substr('HelloWorld',2) value from dual;  //返回结果:elloWorld,截取从“e”开始之后所有字符
15、select substr('HelloWorld',-1) value from dual;  //返回结果:d,从最后一个“d”开始 往回截取1个字符
16、select substr('HelloWorld',-2) value from dual;  //返回结果:ld,从最后一个“d”开始 往回截取2个字符
17、select substr('HelloWorld',-3) value from dual;  //返回结果:rld,从最后一个“d”开始 往回截取3个字符 

说明:
1. 当a等于0或1时,都是从第一位开始截取(如:1和2);
2. 假如HelloWorld之间有空格,那么空格也将算在里面(如:5和6);
3. 虽然7,8,9,10截取的都是3个字符,结果却不是3 个字符; 只要 |a| ≤ b,取a的个数(如:7、8、9);当 |a| ≥ b时,才取b的个数,由a决定截取位置(如:9和10);
4. 当只有两个参数时;不管是负几,都是从最后一个开始 从后往前截取(如:15、16、17).

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值