mysql 索引详解

最新推荐文章于 2023-06-13 20:34:45 发布

date-date

最新推荐文章于 2023-06-13 20:34:45 发布

阅读量318

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/learner_up/article/details/78507839

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本博客主要内容有：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;

date-date

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 索引详解

when 与group by,having 1 when,group by ,having 可以一起使用,when作用于group by 之前,having作用group by 之后,且必须和group by 同时出现。此时group by 会对when 中经过限制条件选择之后结果进行分组，而having 则是对分组之后的结果添加限制条件，从分组之后的结果中选择出满足条件的记录。 2 ha
复制链接

扫一扫