本博客主要内容有：BTree与B+Tree 对比，mysql聚簇索引与非聚簇索引 ，索引的离散性，联合索引，常用 Mysql 语句案例，mysql 存储过程案例
B-TREE
一个B-tree 是一棵多路绝对平衡查找树。所谓绝对平衡是指所有叶子节点都在同一层级。一颗B-TREE，相对于AVL树而言，减少了树高，减少了 IO次数。但是在mysql 中使用的是优化过的BTree,即B+TREE。
B+TREE
1.在B+tree 中，每个索引节点只存储关键字和指向子节点的索引，并没有存储数据。而B-tree，每个索引节点存储关键字，数据和指向子节点的索引。B+Tree 相比B-Tree而言，B+Tree中每个节点的关键字和子节点引用的比值为1：1，而B-Tree是2：3，B+TREE 每个节点可以存储更多的关键字
2.对于B+Tree 而言，只在叶子节点中存储所有记录，想要查找一个记录，必须要进行三次IO操作，而B-Tree的IO操作可能是一次，二次，三次。相对于B-Tree而言，B+Tree 更加稳定，方便后续调优
3.B+Tree中，叶子节点天然有序，当进行基于索引的排序会更加方便和基于索引的扫表能力会更强
mysql 索引
两种索引： InnoDB （聚簇索引）和MyISAM（非聚簇索引）
InnoDB 聚簇索引：与数据存放的位置一致，一张表只能存在一个聚簇索引。InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
一般来说不建议使用隐含字段作为主键.，当我们自定义主键时，字段长度只有4个字节，隐含主键不仅占据更多空间，而且会使行锁升级为表锁，降低 了并发性。
Innodb 创建的索引会生成两个文件，一个是以.frm结尾的表结构文件，另一个是.ibd结尾的索引树
主键索引：
** 辅助索引（二级索引，非聚簇索引）：**
辅助索引（Secondary Index， 也就是非主键索引）：
在叶子节点中会存储辅助索引列值+主键，并指向对应的行。
好处：1.innodb在移动行时无须更新行指针。
缺点：使用主键值会让辅助索引占据更多的空间。
所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。同时InnoDB 不会压缩索引。
因此，在使用Innodb引擎时如果索引和sql编写不当往往会触发回表操作。
MyISAM：
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
辅助索引
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。创建MyIsam索引会生成三个数据文件，.frm的表结构文件，.myi的索引树文件，.myd中存储的实际数据
索引的离散性
无论是创建Innodb索引还是MyIsam索引，都会要求索引列的离散性要尽可能的好。其中离散性比值=count(distinct(col))：count(col)
如果离散性比值大于1：10,说明这个索引的离散性不错。
为什么要求索引的离散性尽可能的好呢？
因为索引离散性的好坏会影响优化器是否走索引。如果离散性太差，优化器会认为走索引的效果和全表扫描差不多，那么就不会走索引而去进行全表扫描了。
这边来记录一个问题，面试的时候也常考。
为啥Innodb的主键要设置为自增主键？
1.不设置主键采用默认主键时占据更多内存，降低并发度
2.在Innodb的叶子结点中是有序的，如果不采用自增主键的时候，会导致页的分离与合并，产生IO，降低性能。（页是Innodb的磁盘存储单位）这条是主要原因
3.离散性
讲解的很不错的博客：
mysql 聚簇索引原理
innodb 与MyISAM索引实现原理
回看：https://www.cnblogs.com/shijingxiang/articles/4743324.html
联合索引
联合索引：用多个列共同创建索引
联合索引与单列索引相比较，相当于把多棵单列索引数变成一棵，节省空间和提升效率。
在联合索引中，假设对（a,b,c）三列建立联合索引，利用索引查找时会先比较a，如果a相等然后再去比较b。
所以在联合索引中会有最左匹配原则。即如果where条件中没有a列，只有b,c两列的判断条件，那么联合索引会失效。
除此之外，在联合索引中如果出现了范围查询，那么后续索引也会失效。
但是在单列索引中，如果出现范围查询，是可以使用索引的。
举个例子吧，看下图：
在第一个中只会使用name索引，其他索引失效。
在第二个中也会使用name索引，其他索引失效。
在第三个中不使用索引。
在第四个中会使用两个索引。原因：mysql中的优化器会把数据进行调换，将离散性好的数据放在最左边。所以我们在写sql的时候，应该尽量按照索引顺序来，避免优化器的调换。
覆盖索引
所谓覆盖索引是指当我们执行一个sql的时候可以不用去主键索引树中查询行 （不执行回表操作）即能拿到所需要的数据。
使用覆盖索引可以加快查询速度。
1.用到了，没有去主键索引树中 查询
2.没有，因为select* 是查找所有列。所以为了执行效果，尽量不要写select *。
3.用到了。在username的索引树中会存储列信息和主键索引值
4，没用到
5.用到了。
mysql易错点
when 与group by,having
1 when,group by ,having 可以一起使用,when作用于group by 之前,having作用group by 之后,且必须和group by 同时出现。此时group by 会对when 中经过限制条件选择之后结果进行分组，而having 则是对分组之后的结果添加限制条件，从分组之后的结果中选择出满足条件的记录。
2 having其后所接一般为聚集函数，除having后聚集函数中出现的属性字段外，select语句后的属性字段必须全部出现在group by 子句中。而when后面不能跟 聚集函数作为条件。
**case when **
update empc set sal=case when sal>2000 and sal<=4600 then sal*1.15
when sal>4600 then sal*0.9
else sal end;
此种用法类似 if ...else 当第一个条件满足时便不会执行 第二个,如果前面条件都不满足时执行else条件
select cname,case when cource='数学' then score else 0 end 数学,
case when cource='语文' then score else 0 end 语文,
case when cource='英语' then score else 0 end 英语
from tb ;
行变列
select cname,case when cource='数学' then score else 0 end 数学,
case when cource='语文' then score else 0 end 语文,
case when cource='英语' then score else 0 end 英语
from tb ;
``
对每个记录都进行判断，满足任何一个casewhen的条件的都会作为结果放在名为该casewhen 属性字段下。
**top n**
关键词 limit，limit 开始位，选取个数。最好用于无并列情况。当选取最大者（最小者）时，limit 0，1。可以放在子查询中，主查询中。
select deptno,dname from dept where deptno =(select deptno from emp group by deptno order by count(deptno) desc limit 0,1);
子查询几乎可以出现在查询语句的任何位置，但是，当子查询作为查询源和属性字段出现时必须起别名。
mysql 在子查询方面性能比较糟糕.
例如 select * from a where a.id in(select id from b where b.id in (1,2,3))
mysql 会将外层的结果先查询出来然后逐条执行子查询，如果外层结果集很大会导致很慢。
存储过程案例及使用及调试：
CREATE OR REPLACE PROCEDURE P_ETL_F_DFNSBQ_TOP1000 (V_MONTH IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) IS
/**********************************************************
*名 称 --%@NAME:P_ETL_F_DFZFPM_TOP1000
*功能描述 --%@COMMENT:抽取地方级一般公共预算排名前一千的企业
*执行周期 --%@PERIOD:全量
*参 数 --%@PARAM:V_BBQ 帐期 YYYYMMDD
*参 数 --%@PARAM:V_RETCODE 过程执行结果代码(成功：SUCCESS/失败：FAIL/等待：WAIT),输出参数
*参 数 --%@PARAM:V_RETINFO 过程执行结果描述,输出参数
*创 建 人 --%@CREATOR: WKS
*创建时间 --%@CREATED_TIME: 2020-04-20
*备 注 --%@REMARK:
*修改记录 --%@MODIFY: 20200505 名称取时间段内最新的 WKS
*来 源 表 --%@FROM:
*目 标 表 --%@TO:
*--字段映射
***********************************************************/
V_USER VARCHAR2(50); --过程当前用户
V_BBQ VARCHAR2(100);
V_ROWCOUNT NUMBER; --用于保存记录数
V_PROCNAME VARCHAR2(1000); --过程名
V_TABLE_NAME VARCHAR2(1000); --表名
V_START DATE;
--V_YEAR VARCHAR2(8);
S_DAY VARCHAR2(10):=TO_CHAR(TRUNC(TO_DATE(V_MONTH,'YYYY/MM'),'YY'),'YYYYMMDD');
S_LAST_DAY VARCHAR2(10):=TO_CHAR(LAST_DAY(TO_DATE(V_MONTH,'YYYY/MM')),'YYYYMMDD');
S_DAY1 VARCHAR2(10):=TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE(V_MONTH,'YYYY/MM'),'YY'),-12),'YYYYMMDD');
S_LAST_DAY1 VARCHAR2(10):=TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(TO_DATE(V_MONTH,'YYYY/MM'),'MM'),-12)),'YYYYMMDD');--20200507 wks 先加月再求月底日期，防止2月28 29日
BEGIN
V_USER := 'DC_MID';
V_PROCNAME := 'P_ETL_F_DFNSBQ_TOP1000';
V_TABLE_NAME := 'F_BI_IN_DFNSBQ_TOP1000';
V_START := SYSDATE;
--V_YEAR := SUBSTR(V_MONTH, 1, 4);
V_BBQ :=V_MONTH;
P_INSERT_ETL_LOG(V_BBQ,
V_USER,
V_PROCNAME,
V_TABLE_NAME,
V_START,
'',
V_ROWCOUNT,
V_RETCODE,
V_RETINFO,
'');
IF 1 = 1 THEN
DELETE FROM M_BI_IN_DFNSBQ_TOP1000 WHERE ACCT_YEAR=V_BBQ;
COMMIT;
INSERT INTO M_BI_IN_DFNSBQ_TOP1000
SELECT V_BBQ ACCT_YEAR, PRIMARY_ID, XZQH, HY_DM, RN
FROM (SELECT PRIMARY_ID,
XZQH,
HY_DM,
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY AMT DESC) RN
FROM (SELECT PRIMARY_ID,
--MAX(DECODE(XZQH_RN, 1, XZQH, NULL)) XZQH,
MAX(CASE
WHEN SDS_AMT = 0 THEN
DECODE(XZQH_RN, 1, XZQH, NULL)
ELSE
DECODE(SDS_RN, 1, XZQH, NULL)
END) XZQH,
MAX(DECODE(HY_RN, 1, HY_DM, NULL)) HY_DM,
MAX(AMT) AMT
FROM (SELECT PRIMARY_ID,
XZQH,
HY_DM,
ROW_NUMBER() OVER(PARTITION BY PRIMARY_ID ORDER BY AMT_XZQH DESC) XZQH_RN,
ROW_NUMBER() OVER(PARTITION BY PRIMARY_ID ORDER BY AMT_HY DESC) HY_RN,
ROW_NUMBER() OVER(PARTITION BY PRIMARY_ID ORDER BY SDS_XZQH DESC) SDS_RN,
SDS_AMT,
AMT
FROM (SELECT /*+ PARALLEL(A,20)*/
A.PRIMARY_ID,
A.DIVISIONS XZQH,
A.IND_PHY HY_DM,
SUM(NVL(CITY_DIV_TRAAMT, 0) +
NVL(COUNTY_DIV_TRAAMT, 0)) OVER(PARTITION BY A.PRIMARY_ID, A.DIVISIONS) AMT_XZQH,
SUM(NVL(CITY_DIV_TRAAMT, 0) +
NVL(COUNTY_DIV_TRAAMT, 0)) OVER(PARTITION BY A.PRIMARY_ID, A.IND_PHY) AMT_HY,
SUM(DECODE(SUBSTR(BUDGETSUBJECTCODE, 1, 7),
'AA10104',
NVL(CITY_DIV_TRAAMT, 0) +
NVL(COUNTY_DIV_TRAAMT, 0),
0)) OVER(PARTITION BY A.PRIMARY_ID, A.DIVISIONS) SDS_XZQH,
SUM(NVL(CITY_DIV_TRAAMT, 0) +
NVL(COUNTY_DIV_TRAAMT, 0)) OVER(PARTITION BY A.PRIMARY_ID) AMT,
SUM(DECODE(SUBSTR(BUDGETSUBJECTCODE, 1, 7),
'AA10104',
NVL(CITY_DIV_TRAAMT, 0) +
NVL(COUNTY_DIV_TRAAMT, 0),
0)) OVER(PARTITION BY A.PRIMARY_ID) SDS_AMT
FROM F_BI_IN_RHDZSP_CX_DAY_HZ A
WHERE A.APPLYDATE BETWEEN S_DAY AND S_LAST_DAY
AND A.PRIMARY_ID NOT LIKE '%11111111'
/*AND ASCIISTR(A.TAXPAYNAME) LIKE '%\%'*/
AND NOT REGEXP_LIKE(A.TAXPAYNAME, '(^[A-Za-z]+$)')--20200506 wks 修改为只剔除全英文企业
AND A.BUDGETSUBJECTCODE LIKE 'A%'))
GROUP BY PRIMARY_ID))
WHERE RN <= 1000;
--名称取时间段内最新的--WKS-20200505
DELETE FROM M_BI_IN_DFNSBQ_TOP1000_QYXX;
COMMIT;
INSERT INTO M_BI_IN_DFNSBQ_TOP1000_QYXX
SELECT TTT.PRIMARY_ID, TTT.TAXPAYNAME, TTT.TAXPAYCODE
FROM (SELECT DISTINCT HZ.PRIMARY_ID,
HZ.TAXPAYNAME,
HZ.TAXPAYCODE,
HZ.APPLYDATE,
ROW_NUMBER() OVER(PARTITION BY HZ.PRIMARY_ID ORDER BY HZ.APPLYDATE DESC) RN
FROM M_BI_IN_DFNSBQ_TOP1000 TT, F_BI_IN_RHDZSP_CX_DAY_HZ HZ
WHERE TT.PRIMARY_ID = HZ.PRIMARY_ID
AND HZ.APPLYDATE BETWEEN S_DAY AND S_LAST_DAY
AND HZ.BUDGETSUBJECTCODE LIKE 'A%'
AND TT.ACCT_YEAR = V_MONTH) TTT
WHERE RN = 1;
COMMIT;
DELETE FROM DC_APP.F_BI_IN_DFNSBQ_TOP1000@BJCZ161 A WHERE A.ACCT_MONTH=V_BBQ;
COMMIT;
INSERT INTO DC_APP.F_BI_IN_DFNSBQ_TOP1000@BJCZ161
SELECT V_MONTH ACCT_MONTH,
A1.RN,
A1.PRIMARY_ID,
QYBC.TAXPAYNAME NSRMC,--名称取时间段内最新的--WKS-20200505
B1.SK_GKCODE XZQH,
B1.ORGNAME XZQH_NAME,
NVL(C1.CODE1, 'VVVV') HY_DM,
NVL(C1.NAME1, '第三产业其他') HY_NAME,
A1.B_AMT,
A1.S_AMT,
A1.CENT_AMT,
A1.CITY_AMT,
A1.COUNTY_AMT,
A1.S_CENT_AMT,
A1.S_CITY_AMT,
A1.S_COUNTY_AMT
FROM (SELECT /*+ PARALLEL(A,20) USE_HASH(A,B)*/
RN,
PRIMARY_ID,
MAX(NSRMC) NSRMC,
XZQH,
HY_DM,
SUM(B_AMT) B_AMT,
SUM(S_AMT) S_AMT,
SUM(CENT_AMT) CENT_AMT,
SUM(CITY_AMT) CITY_AMT,
SUM(COUNTY_AMT) COUNTY_AMT,
SUM(S_CENT_AMT) S_CENT_AMT,
SUM(S_CITY_AMT) S_CITY_AMT,
SUM(S_COUNTY_AMT) S_COUNTY_AMT
FROM (SELECT B.RN,
A.PRIMARY_ID,
MAX(A.TAXPAYNAME) NSRMC,
B.XZQH,
B.HY_DM,
SUM(TRAAMT) B_AMT,
0 S_AMT,
SUM(A.CENT_DIV_TRAAMT) CENT_AMT,
SUM(A.CITY_DIV_TRAAMT) CITY_AMT,
SUM(A.COUNTY_DIV_TRAAMT) COUNTY_AMT,
0 S_CENT_AMT,
0 S_CITY_AMT,
0 S_COUNTY_AMT
FROM F_BI_IN_RHDZSP_CX_DAY_HZ A,
M_BI_IN_DFNSBQ_TOP1000 B
WHERE A.APPLYDATE BETWEEN S_DAY AND S_LAST_DAY
AND A.PRIMARY_ID = B.PRIMARY_ID
AND B.ACCT_YEAR = V_MONTH
AND A.BUDGETSUBJECTCODE LIKE 'A%'
GROUP BY B.RN, A.PRIMARY_ID, B.XZQH, B.HY_DM
UNION ALL
SELECT /*+ PARALLEL(A,20) USE_HASH(A,B)*/
B.RN,
A.PRIMARY_ID,
MAX(A.TAXPAYNAME),
B.XZQH,
B.HY_DM,
0 B_AMT,
SUM(TRAAMT) S_AMT,
0,
0,
0,
SUM(A.CENT_DIV_TRAAMT) S_CENT_AMT,
SUM(A.CITY_DIV_TRAAMT) S_CITY_AMT,
SUM(A.COUNTY_DIV_TRAAMT) S_COUNTY_AMT
FROM F_BI_IN_RHDZSP_CX_DAY_HZ A,
M_BI_IN_DFNSBQ_TOP1000 B
WHERE A.APPLYDATE BETWEEN S_DAY1 AND S_LAST_DAY1
AND A.PRIMARY_ID = B.PRIMARY_ID
AND B.ACCT_YEAR = V_MONTH
AND A.BUDGETSUBJECTCODE LIKE 'A%'
GROUP BY B.RN, A.PRIMARY_ID, B.XZQH, B.HY_DM)
GROUP BY RN, PRIMARY_ID, XZQH, HY_DM) A1,
D_IN_ORGCODE_17 B1,
(SELECT * FROM D_HY_QM T WHERE T.DHY_HYZT = '01') C1,
M_BI_IN_DFNSBQ_TOP1000_QYXX QYBC--名称取时间段内最新的--WKS-20200505
WHERE A1.XZQH = B1.SK_GKCODE(+)
AND A1.HY_DM = C1.CODE1(+)
AND A1.PRIMARY_ID=QYBC.PRIMARY_ID;
COMMIT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '执行成功！';
P_UPDATE_ETL_LOG(V_BBQ,
V_USER,
V_PROCNAME,
V_TABLE_NAME,
V_START,
SYSDATE,
V_ROWCOUNT,
V_RETCODE,
V_RETINFO,
'');
END IF;
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
P_UPDATE_ETL_LOG(V_BBQ,
V_USER,
V_PROCNAME,
V_TABLE_NAME,
V_START,
SYSDATE,
V_ROWCOUNT,
V_RETCODE,
V_RETINFO,
'');
END;
7367
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
