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....
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤