MySQL&Oracle数据库进阶学习【未完】
语句/结构等优化
1.1结构优化
优化数据大小
使表占用尽量少的磁盘空间。减少磁盘I/O次数及读取数据量是提升性能的基础原则。表越小，数据读写处理时则需要更少的内存，同时，小表的索引占用也相对小，索引处理也更加快速。
MySQL支持不同类型的存储引擎和行格式，针对不同类型，业务需求的表应该设置合适的存储引擎及索引方法。表设置建议如下：
如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话，可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表，这个表主要字段分别是：
TABLE_SCHEMA : 数据库名TABLE_NAME：表名ENGINE：所使用的存储引擎TABLES_ROWS：记录数DATA_LENGTH：数据大小INDEX_LENGTH：索引大小
其中TABLES：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
[root@iZbp1hwh629hd4xz80i1z0Z ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| COLUMNS |
| COLUMNS_EXTENSIONS |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEY_COLUMN_USAGE |
| KEYWORDS |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMA_PRIVILEGES |
| SCHEMATA |
| SCHEMATA_EXTENSIONS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| STATISTICS |
| TABLE_CONSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
| TABLE_PRIVILEGES |
| TABLES |
| TABLES_EXTENSIONS |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
| VIEWS |
+---------------------------------------+
79 rows in set (0.00 sec)
mysql> SELECT * FROM TABLES;
| |
| def | alice | t_user_memory | BASE TABLE | MEMORY | 10 | Fixed | 0 | 251 | 0 | 221635761 | 0 | 0 | 1 | 2022-02-17 23:39:47 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| def | alice | t_user | BASE TABLE | InnoDB | 10 | Dynamic | 951027 | 102 | 97107968 | 0 | 45760512 | 0 | 956841 | 2022-02-18 17:31:37 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| def | alice | user | BASE TABLE | InnoDB | 10 | Dynamic | 30 | 546 | 16384 | 0 | 16384 | 0 | NULL | 2022-02-18 18:09:43 | NULL
以最后一行为例：
其中def对应的是TABLE_CATALOG ，即表目录；alice对应为表所属数据库，user为表名，BASE TABLE为表类型； InnoDB为引擎类型， 10 为 版本，默认值10；Dynamic 为 Row_format （行格式），30为 Table_rows（表内数据行数）等等，其余字段含义如下：
1.查看user表所占空间：
mysql> select table_name, concat(avg_row_length*table_rows+index_length,'字节') as '占用空间大小' from `information_schema`.`tables` where table_schema='alice' and table_name='user';
+------------+--------------------+
| TABLE_NAME | 占用空间大小 |
+------------+--------------------+
| user | 32764字节 |
+------------+--------------------+
1 row in set (0.00 sec)
得到的结果是以字节为单位，除1024为K，除1048576为M。
2.查看alice库所占空间大小：
mysql> select table_schema as 数据库名称, concat(sum(avg_row_length*table_rows+index_length),'字节') as 占用空间大小 from `information_schema`.`tables` where table_schema='alice';
+-----------------+--------------------+
| 数据库名称 | 占用空间大小 |
+-----------------+--------------------+
| alice | 142814414字节 |
+-----------------+--------------------+
1 row in set (0.00 sec)
mysql>
3.统计每个库的占用空间大小，并从大到小排序
提示，information_schema是内存表，不占用内存空间。
mysql> select table_schema as 数据库名, concat(sum(avg_row_length*table_rows+index_length),'字节') as '占用空间大小' from `information_schema`.`tables` group by `table_schema` order by 占用空间大小 DESC;
+--------------------+--------------------+
| 数据库名 | 占用空间大小 |
+--------------------+--------------------+
| library | 65524字节 |
| mysql | 2718240字节 |
| hzero_admin | 196579字节 |
| sys | 16380字节 |
| clouddb02 | 16380字节 |
| clouddb01 | 16380字节 |
| clouddb03 | 16380字节 |
| alice | 142814414字节 |
| hand2 | 131050字节 |
| hzero_platform | 10606405字节 |
| information_schema | 0字节 |
| performance_schema | 0字节 |
| todo | 0字节 |
+--------------------+--------------------+
13 rows in set (0.02 sec)
mysql>
https://baijiahao.baidu.com/s?id=1659704248394073140&wfr=spider&for=pc
1.2配置优化
1.3 SQL优化
1.3.1 Oracle 基础学习
项目上第一次接触到oracle（1521），在这里总结一下：
1.关于事务提交：
和mysql不同，oracle是需要手动提交的，所以在我们用PLSQL或者Navicate这种 远程软件时注意需要提交时要 手动提交，不然可能会造成锁表。
涉及手动提交的操作：inert，update，delete等（我测试create 和 drop时也显示有提交按钮，也需要提交后才能看到这期间所操作的数据，DDL语句应该是隐含 提交操作的），至于truncate 和 drop 等操作不需要提交
关于锁表和解决办法：
【锁表】
常见锁表原因：程序A操作表未提交时，B程序也操作此表，会造成锁表（锁表级别三），或者说同一事务内操作某些行数据但是提交前，另外的方法也对这些未提交数据进操作，会因为类似资源互斥抢占 从而锁表。
--查询oracle数据库中表是否被锁
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;
--解锁--
alter system kill session 'XXX,xxxxx';
2.关于数据类型：
varchar相关：
varchar(50) 表示该字段类型为varchar2类型，长度为50，可以存单字节字符50个，如字母、数字等，可以存储双字节字符25个，如汉字。不同的是，若输入的不足，则以空格填充。varchar2(50) 表示该字段类型为varchar2类型，长度为50，可以存单字节字符50个，如字母、数字等，可以存储双字节字符25个，如汉字。nvarchar2(50)表示该字段类型为nvarchar2,长度为50,不论英文，数字，中文都能存50个。
至于 编码，则影响的是 varchar的总长度，比如最常见的utf8，使得varchar2最多存储1333（4000的三分之一）个字符，最多1333个汉字
比如表设计时 name 类型为varchar2(10)，那最大容纳汉字只能是3个，多了报错，比如输入“测试最大长度”时：
ORA-12899: 列 "FIPT_DEV"."TEST"."S_NAME" 的值太大 (实际值: 18, 最大值: 10)
输入非汉字字符时也同理：数字和英文字符都是占一个长度，即默认varchar2(10)是varchar(10 byte),10个字节长度
输入“测试abcdefg3” 时：
报错：ORA-12899: 列 "FIPT_DEV"."TEST"."S_NAME" 的值太大 (实际值: 14, 最大值: 10)
--占用字符数
select length('测试占用字符2') from dual;
-- 返回7
--占用字节数 （即varchar2 对应）
select lengthb('测试占用字节数2') from dual;
-- 返回22
-- ps:utf8 一个汉字占3 字节
number相关：
相比较mysql的float，double，其实对于数值类型，Oracle里都可以用number存储，后端传值存表时不会报错，Bigdecimal，int，float等都可以映射为number类型。
Number(p,s)：
p和s都是可选的。
p指精度(precision)，即总位数。默认情况下精度为38。精度的取值范围为1~38。
s指小数位(scale)，小数点右边的位数。小数点位数的合法值为-84~12
没有指定精度p 时，总位数默认最大，如果指定了精度p，则会默认s为0，即视为整数存储。
3.关于 自增：
--Oracle 中是通过序列实现自增的，效果和mysql的自增差不多
Create Sequence CUX_TRA_ACC_PAYABLE_LINE_s Start With 10001 INCREMENT BY 1;
--这里h0默认设置10001开始自增，前面10000的空间是留给业务测试等等用途
4.关于分页：
Oracle用过 rownum实现分页
可以通过start和end限制开始结束页数：
select *
from (
select *
from (select ROWNUM rn,
CTEAA.COMPANY_CODE COMPANY_CODE,
CTEAA.COMPANY_NAME,
CTEAA.SEGMENT3
from CUX_TRA_ESTIMATE_AP_AGE CTEAA
LEFT JOIN CUX_PROV_ORDER_INFO info on info.ORDER_CODE = CTEAA.ORDER_NUM
order by CTEAA.SEGMENT3 asc
)
<where>
<if test="end != null">
and rn <= #{end}
</if>
</where>
)
<where>
<if test="start != null">
and rn > #{start}
</if>
</where>
注意一点，当rownum 只设置大于 时，是无法成立的，因为rownum范围是从1开始的，如果查询乃至子查询是大于的话，是无法找到包含1 的区间的，所以只设置大于条件查询 返回结果肯定是空，这一点和mysql的limit区别很大，另外，查询时根据需求进行升序排序会提高一定的效率。
5.Oracle常用函数
to_date与to_char
--to_date与to_char：
select c1.GL_DATE as yyyy_mm_str,
to_char((ADD_MONTHS(to_date(c1.GL_DATE, 'yyyy-mm')
, -1)), 'yyyy-mm') as yyyy_mm_str_add,
to_char(c1.LAST_UPDATE_DATE, 'YYYY-MM-DD HH:MI:SS') as date_1,
to_char(c1.LAST_UPDATE_DATE) as date_2
from CUX_TRA_ACCOUNT_DETAILS c1;
-- ADD_MONTHS函数 是对日期进行操作，-1 表示上个月，+1 表示下个月
-- 注意，oracle的时间格式化时 推荐用hh24这种
-- update CUX_TRA_ESTIMATE_AP_AGE set REV_DATE = to_date('2022-04-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
where PERIOD = '2022-03';
输出如下图：
总结：在sql中可以利用TO_CHAR 和 TO_DATE 设置参数格式来灵活转换
listagg列转行函数：
测试数据如上，测试sql如下：
-- 列转行函数 listagg()
select S_CODE,listagg(DISTINCT S_NAME, ';') as list_test from test
where CREATE_DATE >= to_date('2022-05-09', 'yyyy-mm-dd')
group by S_CODE;
-- 注意： || 和conact(a, b)虽然 也可以实现字符串拼接，却只限于行维度，而且conact一次只能拼接两个参数，如果拼接多个参数还得函数嵌套，||则没有拼接数量限制
输出如下图：
可以看到，根据s_code分组并对s_name进行了拼接，其使用规则类似sum()函数，需要对查询字段中非聚合字段进行分组才可使用
NVL函数
可以对查询为空的字段结果进行替换，金额计算时建议使用NVL函数先判空，如果为空给0即可。
select nvl(S_CODE, '空值') from TEST;
--查询时对于S_CODE 为空的就会用‘空值’ 代替
HAVING子句
在Oracle中 having必须结合group by分组函数使用，作用是可以对分组后的数据进行筛选展示
select S_CODE, S_NAME, AMOUNT from TEST
group by S_CODE, S_NAME, AMOUNT
having MAX(AMOUNT) < 0;
分组函数（分析函数）
sql中经常出现的问题就是数据重复或者数据异常问题，所以正确分组很重要
-- 分组函数 partition by
select row_number()
over(partition by S_CODE order by s_id desc) pb,s_id,s_code,s_name,AMOUNT
from TEST order by s_id;
-- 表示 以 s_code分组，组内以s_id 降序排列，row_number()表示其在组内的序号
可以看到 partition by 和group by不同，他是类似于局部分组 ，而非 对整体进行分组，是不会影响原查询的条数的，具体区别我sum（金额字段） 组合来演示：
-- 分组函数 partition by
-- 相当于只针对这个字段 以特定条件分组汇总，就很舒服
select SUM(nvl(AMOUNT, 0)) over (PARTITION BY s_code) total_amount, AMOUNT,
S_CODE,
S_NAME
from TEST
order by s_id;
-- group by 对比
select SUM(nvl(AMOUNT, 0)) total_amount, S_CODE
from TEST
group by S_CODE
因为Oracle分组的特性，使用group by时 需要遵守：
如果查询中字段不属于聚合函数（例如sum, min, max，avg，count等）那就必须出现在group by中
我们可以在子查询里把金额字段 用聚合函数 等分析汇总好，然后再进行条件分组，即子查询处理少量问题字段，其余的放在父查询即可。至于分组的维度，其实应该在存表时就考虑好，该汇总的汇总。
其他函数
--replace函数： replace(segment, param1, param2)
replace(LINE.SETTLEMENT_AMOUNT, '0', null)
-- replace可以放在查询和更新等语句中，总之就是对 字段进行整容， 如上语句是对字段SETTLEMENT_AMOUNT 中的 0换成空，其实就是删除var中的0，更多的可能是简单处理一些格式，比如 replace(CTEAA.RECEIVE_EMPLOYEE,',','')， 这个就是去掉var中的逗号
1.3.2 SQL优化之 SQL执行计划（Explain Plan(Raw)）
SQL执行计划简介
先拿个SQL当例子：
SELECT '暂估应付账龄表' AS ERP_TABLENAME,
CTEAA.OU_NAME AS OU_NAME,
CTEAA.COMPANY_CODE AS COMPANY_CODE,
CTEAA.COMPANY_NAME,
CTEAA.SEGMENT3 AS ACCOUNT_CODE,
CTEAA.SEGMENT3_DESC AS ACCOUNT_NAME,
CTEAA.VENDOR_NUM AS VENDOR_NUMBER,
CTEAA.VENDOR_NAME,
CTEAA.AFFILIATED_PARTY,
CTEAA.PROJECT_NAME AS project_Num,
CTEAA.EMPLYEE,
CTEAA.EMPLYEE_NUM,
CTEAA.REV_DATE,
(case
when exists(select t.unit_code from hpfm_unit t where t.unit_name = CTEAA.RECEIVE_DEPT and rownum = 1)
then (select t.unit_code from hpfm_unit t where t.unit_name = CTEAA.RECEIVE_DEPT and rownum = 1)
else
CTEAA.RECEIVE_DEPT
end) AS RECEIVE_DEPT_CODE,
(case
when exists(select he.EMPLOYEE_NUM from HPFM_EMPLOYEE he where he.NAME = replace(CTEAA.RECEIVE_EMPLOYEE,',','') and rownum = 1)
then (select he.EMPLOYEE_NUM from HPFM_EMPLOYEE he where he.NAME = replace(CTEAA.RECEIVE_EMPLOYEE,',','') and rownum = 1)
else
replace(CTEAA.RECEIVE_EMPLOYEE,',','')
end) AS RECEIVE_EMPLOYEE_NUM,
replace(CTEAA.RECEIVE_EMPLOYEE,',','') AS RECEIVE_EMPLOYEE,
CTEAA.RECEIVE_DEPT ,
CTEAA.CREATOR AS CREATOR_NUM,
CTEAA.CREATOR_DEPT AS CREATOR_DEPT_CODE,
ACCOUNT_RECEIVABLE_AGE,
info.REFERENCE3 AS PO_PROJECT,
info.PURCHARSE_TYPE AS PO_TYPE,
0 AS TENANT_ID,
CTEAA.LAST_UPDATE_DATE,
CTEAA.PERIOD
FROM CUX_TRA_ESTIMATE_AP_AGE CTEAA
LEFT JOIN CUX_PROV_ORDER_INFO info ON info.ORDER_CODE = CTEAA.ORDER_NUM
WHERE CTEAA.PERIOD = '2022-03'
--AND CTEAA.SEGMENT3 = #{accountCode}
ORDER BY CTEAA.SEGMENT3 ASC
IDEA右键 可以查看Explain Plan Raw:
-
表的加载顺序:ID：执行序列，但不是执行的先后顺序。执行的先后根据Operation缩进来判断
-
Operation： 当前操作的内容:
-
Name：操作对象
-
Rows：也就是10g版本以前的Cardinality(基数)，Oracle估计当前操作的返回结果集行数。
-
Bytes：表示执行该步骤后返回的字节数。
-
Cost（CPU）：表示执行到该步骤的一个执行成本，用于说明SQL执行的代价。
-
Time：Oracle 估计当前操作的时间。
执行计划中，表的访问方式有两种：
第一种：全表扫描
第二种：索引扫描：
index unique scan --索引唯一扫描
index range scan --索引局部扫描
index full scan --索引全局扫描
index fast full scan --索引快速全局扫描，不带order by情况下常发生
index skip scan --索引跳跃扫描，where条件列是非索引的前导列情况下常发生运算符：
1.sort --排序，很消耗资源
(1)order by clauses (2)group by (3)sort merge join –-这三个会产生排序运算
2.filter --过滤，如not in、min函数等容易产生
3.view --视图，大都由内联视图产生(可能深入到视图基表)
4.partition view --分区视图
COUNT STOPKEY : 表示代码中用到rownum，并且扫描到指定行数就终止了
1.3.3 开发中遇到的SQL问题
Q1：关联字段异常导致汇总金额异常
SELECT
- sum(nvl(CTAID.DIST_ACCOUNTED_AMOUNT, 0)) - sum(nvl(CTAAR.ENTERED_AMOUNT, 0)) - sum(nvl(ctp.ENTERED_AMOUNT, 0))
AS apBalance
FROM CUX_TRA_AP_INV_DETAILS CTAID
inner JOIN CUX_TRA_AP_AR_RELATION CTAAR ON CTAAR.TRX_NUM = CTAID.INVOICE_NUM
LEFT JOIN CUX_TRA_PAYMENT ctp ON ctp.CHECK_NUMBER = CTAAR.APPLIED_TRX_NUM
WHERE CTAID.INVOICE_TYPE_DESC != '预付款发票'
-- AND CTAID.SEGMENT3 = #{accountCode}
-- AND CTAID.SEGMENT4 = #{subAccountCode}
-- and to_char(CTAID.GL_DATE, 'yyyy-mm') = #{period}
GROUP BY CTAID.SEGMENT3,
CTAID.SEGMENT4
这个需求其实可以分为三部分，根据查询条件，
先求 - sum(nvl(CTAID.DIST_ACCOUNTED_AMOUNT, 0)) 设为a
再求- sum(nvl(CTAAR.ENTERED_AMOUNT, 0)) 设为b
最后 - sum(nvl(ctp.ENTERED_AMOUNT, 0)) 设为c
可以发现，a，b，c其实分别来自三个表A,B,C，这里如果拆成3个sql，那逻辑不会有什么问题，至于关联表一起计算...要注意关联字段在表中的数据是否存在，以及求值的先后顺序：
所以，假如A,B,C三表数据的存储逻辑没问题，我们现在有两个解决方案：
方案一：直接拆成3个小sql，根据查询条件进行汇总，然后计算。
方案二：拆分成利用多个子查询，其实也类似方案一，只不过整合在一起了，通过EXISTS来限制查询where条件，比如求字段c 的时候就需要先判断【AB对应字段是否有匹配】，成立后再拿B中的字段匹配C的字段，成立即可汇总对应数据
(SELECT nvl(SUM (nvl(ctp.entered_amount
, 0))
, 0)
FROM cux_tra_payment ctp
WHERE EXISTS (SELECT 1
FROM (SELECT tt.applied_trx_num
FROM cux_tra_ap_ar_relation tt
WHERE EXISTS
(SELECT 1
FROM cux_tra_ap_inv_details ctaid
WHERE ctaid.invoice_type_desc != '预付款发票'
-- AND ctaid.segment3 = #{accountCode}
-- AND ctaid.segment4 = #{subAccountCode}
-- AND to_char(ctaid.gl_date, 'yyyy-mm') <=#{period}
AND tt.trx_num = ctaid.invoice_num)) x
WHERE x.applied_trx_num = ctp.check_number))
To be continued....