四、 SQL优化

绝地灬酷狼 2022-09-13 11:23 167阅读 0赞

## 一、 SQL优化： ##

**详情参考：[这才是SQL优化的正确姿势][SQL]**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAS2FyZW5fOTk5OQ_size_20_color_FFFFFF_t_70_g_se_x_16]

> **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数据库分页][Oracle]**  
**5. 对应的日期与字符串转换函数：**  
**1. Oracle**：字符串转日期：to\_date()、日期转字符串：to\_char()；  
**2. MySQL**：字符串转日期：str\_to\_date()、日期格式化：date\_format()；

**详细参考：[Oracle/MySql下分别对应的日期与字符串转换函数][Oracle_MySql]**

**关于2中序列号sequence说明：**  
通过创建一个自增的序列号sequence来完成自动增长：  
**1.创建序列：**  
参考：[实现ORACLE字段自增的方法][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).

[SQL]: https://mp.weixin.qq.com/s/ZosZWVeME3V652uO1yrdoA
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAS2FyZW5fOTk5OQ_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/d7e3469091b34c80a37af7f3dec6c5e8.png
[Oracle]: https://blog.csdn.net/it_you_know/article/details/83617761
[Oracle_MySql]: https://blog.csdn.net/J080624/article/details/52753726?utm_source=blogxgwz5
[ORACLE]: https://js.aizhan.com/data/oracle/7236.html