MySQL性能优化(四):如何高效正确的使用索引

8 篇文章 5 订阅 ¥19.90 ¥99.00

在这里插入图片描述
前期回顾:
MySQL性能优化(一):MySQL架构与核心问题
MySQL性能优化(二):选择优化的数据类型
MySQL性能优化(三):深入理解索引的这点事

正确地创建和使用索引是实现高性能查询的基础。前面文章MySQL性能优化(三):深入理解索引的这点事已经介绍了各种类型的索引及其特点,而在实际使用索引中,如何真正有效地发挥索引的价值,还需要进一步持续不断地学习、磨练。接下来本篇文章将分享如何高效、正确的使用索引。

实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作的,就可以了。在后续使用索引,或者优化索引时࿰

1 性能优化 1.1 避免频繁 commit,尤其是把 commit 写在循环体中每次循环都进行commit。 1.2 使用绑定变量,避免常量直接引用。 示例以下书写不符合本规范。 INSERT INTO sm_users (user_id, user_name, created_by, creation_date) VALUES (1, 'Tang', -1, SYSDATE); 建议用如下方式操作 DECLARE v_user_id sm_users.user_id%TYPE; v_user_name sm_users_user_name%TYPE; v_created_by sm_users.created_by%TYPE; v_creation_date sm_users.creation_date%TYPE; BEGIN ... INSERT INTO sm_users (user_id, user_name, created_by, creation_date) VALUES (v_user_id, v_user_name, v_created_by, v_creation_date); END; 1.3 Operator 使用规范  IN  比较容易写及清晰易懂  但效能是比较低  ORACLE试图将其转换成多个表连接,如果转换不成功则先执行IN里面子查询,再查询外层表记录,如果转换成功则直接采用多个表连接方式查询。由此可见用INSQL至少多了一个转换过程。一般SQL都可以转换成功,但对于含有分组统计等方面SQL就不能转换了。  NOT IN  此操作是强列推荐不使用,因为不能应用表索引。  推荐方案用NOT EXISTS 或(Outer-Join+判断为空)方案代替 例如 SELECT deptno FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp) 建议写成 SELECT deptno FROM dept, emp WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL  <>  永远不会用到索引  推荐方案用其它相同功能操作运算代替,如: a<>0 改为 a>0 or a<0 a<>’’ 改为 a>’’  IS NULL 或IS NOT NULL  一般是不会应用索引,因为B-tree索引是不索引空值。  推荐方案用其它相同功能操作运算代替,如: a is not null 改为 a>0 或a>’’  不允许字段为空,而用一个default代替空值,如业扩申请中状态区位不允许为空, default为申请。  > 及 <  有索引就会采用索引查找  但有情况下可以对它进行优化  如一个表有100万记录,一个数值型字段A,30万记录A=0,30万记录A=1,39万记录A=2,1万记录A=3。那么执行A>2与A>=3效果就有很大区别了,因为A>2时ORACLE会先找出为2记录索引再进行比较,而A>=3时ORACLE则直接找到=3记录索引。  LIKE  LIKE可以应用通配符查询,里面通配符组合可能达到几乎是任意查询,但是如果用得不好则会产生性能问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引性能肯定大大提高。  UNION  SQL在运行时先取出数个查询结果,再用排序空间进行排序删除重复记录,最后返回结果集,如果表数据量大话可能会导致用磁盘进行排序。  实际大部分应用中是不会产生重复记录,推荐采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单将两个结果合并后就返回。  Exists 示例当有 A、B 两个结果集,当结果集 B 很大时,A 较小时,适用 exists,如 SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE a.COLUMN = b.COLUMN); 当结果集 A 很大时,B 很小时,适用 in,如 SELECT * FROM a WHERE a.COLUMN IN(SELECT b.COLUMN FROM b) 1.4 SQL书写影响  同一功能同一性能不同写法SQL影响  Select * from zl_yhjbqk  Select * from dlyx.zl_yhjbqk(带表所有者前缀)  Select * from DLYX.ZL_YHJBQK(大写表名)  Select * from DLYX.ZL_YHJBQK(中间多了空格)  以上个SQL在ORACLE分析整理之后产生结果及执行时间是一样,但是从ORACLE共享内存SGA原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次分析结果,这不仅可以减少分析SQL时间,而且可以减少共享内存重复信息,ORACLE也可以准确统计SQL执行频率。  WHERE后面条件顺序影响  Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1  Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'  以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行时候都是全表扫描,第一条SQLdy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1比率只为0.5%,在进行第一条SQL时候99%条记录都进行dy_dj及xh_bz比较,而在进行第二条SQL时候0.5%条记录都进行dy_dj及xh_bz比较,以此可以得出第二条SQLCPU占用率明显比第一条低。  查询表顺序影响  在FROM后面表中列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析情况下ORACLE会按表出现顺序进行链接,由此因为表顺序不对会产生十分耗服务器资源数据交叉。(注如果对表进行了统计分析,ORACLE会自动先进小表链接,再进行大表链接)  对条件字段一些优化  采用函数处理字段不能利用索引,如 substr(hbs_bh,1,4)=’5400’,优化处理hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate)优化处理 sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)  进行了显式或隐式运算字段不能进行索引,如 ss_df+20>50,优化处理ss_df>30 ‘X’||hbs_bh>’X5400021452’,优化处理hbs_bh>’5400021542’ sk_rq+5=sysdate,优化处理sk_rq=sysdate-5 hbs_bh=5401002554,优化处理hbs_bh=’ 5401002554’ 注此条件对hbs_bh 进行隐式to_number转换,因为hbs_bh字段是字符型  条件内包括了多个本表字段运算时不能进行索引,如 ys_df>cx_df,无法进行优化 qc_bh||kh_bh=’5400250000’,优化处理qc_bh=’5400’ and kh_bh=’250000’  HINT  是在ORACLE产生SQL分析执行路径不满意情况下要用到。它可以对SQL进行以下方面提示  目标方面提示  COST(按成本优化)  RULE(按规则优化)  CHOOSE(缺省)(ORACLE自动选择成本或规则进行优化)  ALL_ROWS(所有行尽快返回)  FIRST_ROWS(第一行资料尽快返回)  执行方法提示  USE_NL(使用NESTED LOOPS方式联合)  USE_MERGE(使用MERGE JOIN方式联合)  USE_HASH(使用HASH JOIN方式联合)  索引提示  INDEX(TABLE INDEX)(使用提示索引进行查询)  其它高级提示(如并行处理等等) 1.5 索引规则 建立索引常用原则如下 1. 表主键、外键必须有索引 2. 数据量超过 1000 行表应该有索引 3. 经常与其它表进行连接表,在边接字段上应建立索引 4. 经常出现在 where 子句中字段且过滤性极强,特别是大表字段,应该建立索引 5. 索引字段,尽量避免值为 null 6. 复合索引建立需要仔细分析;尽量考虑用单字段索引代替正确选择复合索引第一个字段,一般是选择性较好且在 where 子句中常字段上。  复合索引几个字段是否经常同时以and方式出现在where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引。  如果复合索引中包含字段经常单独出现在 where 子句中,则分解为多个单字段索引。  如果复合索引所包含字段超过 3 个,那么仔细考虑其必要性,考虑减少复合字段。  如果既有单字段索引,又有这几个字段上复合索引,一般可以删除复合索引; 7. 频繁 DDL 表,不要建立太多索引 8. 删除无用索引,避免对执行计划造成负面影响 9. 让 SQL 语句用上合理索引,合理让 SQL 语句使用索引原则如下  首先,看是否用上了索引,对于该使用索引而没有用上索引 SQL 语句,应该想办法用上索引。  其次,看是否用上正确索引了,特别复杂 SQL 语句,当其中 where 子句包含多个带有索引字段时,更应该注意索引选择是否合理。错误索引不仅不会带来性能提高,相反往往导致性能降低。  针对如何用上合理索引,以 Oracle 数据中例子进行说明  任何对列操作都可能导致全表扫描,这里所谓操作包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等式右边,甚至去掉函数。  避免不必要类型转换,要了解“隐藏”类型转换。  增加查询范围,限制全范围搜索。  索引选择性低,但资料分布差异很大时,仍然可以利用索引高效率。  Oracle 优化器无法用上合理索引情况下,利用 hint 强制指定索引。  使用复合索引且第一个索引字段没有出现在 where 中时,建议使用 hint 强制。 1.6 索引使用优化  建立Plan_Table CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), TIMESTAMP DATE, REMARKS VARCHAR2(80), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(30), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_INSTANCE NUMBER(38), OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER(38), ID NUMBER(38), PARENT_ID NUMBER(38), POSITION NUMBER(38), OTHER LONG )  Syntax 说明 explain plan set statement_id = user_define for select ... 将结果显示 SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name, POSITION FROM plan_table START WITH ID = 0 AND STATEMENT_ID = user_define CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = user_define  示例 如要测试下面SQL SELECT c.short, a.cday, a.card_no, a.qty FROM sales.stockiohis a, sales.product_info b, sales.vendor c WHERE a.card_no = b.card_no AND b.vendorid = c.vendorid AND a.produce_no = '2007090001' AND a.CATEGORY = '10' AND a.iotype = '1' 新增文件例 d:\mydoc\plan.sql '0001'为user_define为使用者自定义编号 EXPLAIN PLAN SET STATEMENT_ID = '0001' FOR SELECT 'X' FROM sales.stockiohis a ,sales.product_info b ,sales.vendor c WHERE a.card_no = b.card_no AND b.vendorid = c.vendorid AND a.produce_no = '2007090001' AND a.CATEGORY = '10' AND a.iotype = '1' / SET arraysize 1 SET line 100 COLUMN op format a40 COLUMN object_name format a20 COLUMN options format a20 SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name, POSITION FROM plan_table START WITH ID = 0 AND STATEMENT_ID = '0001' CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = '0001' / DELETE FROM plan_table WHERE STATEMENT_ID = '0001' / COMMIT / 结果 1.7 避免不必要排序 说明对查询结果进行排序会大大降低系统性能,group与union都会对数据作排序,要耗费较多内存,视状况用union all既可,不然有时数据太大又要进行union排序,会导致Oracle数据库SORT_AREA_SIZE不足发生系统错误。 1.8 对于数字型Primary Key,建议用序列 sequence 产生。 说明除非是单据单号,要求必须是唯一,并且依据流水号不可以跳号,不然在大量交易表格中,不在乎跳耗时,要取得唯一Primary Key 建议使用Oracle Sequence这样速度会较快,而且不会有锁定(Lock)问题。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:博客之星2020 设计师:CSDN官方博客 返回首页
评论 3

打赏作者

xcbeyond

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值