四、 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).