精华内容
下载资源
问答
  • sql优化
    千次阅读
    2022-03-23 13:08:38

    一、前言

    在应用开发的早期,数据量少,开发人员开发功能时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。

    二、SQL优化一般步骤

    1、通过慢查日志等定位那些执行效率较低的SQL语句

    2、explain 分析SQL的执行计划

    需要重点关注type、rows、filtered、extra。

    type由上至下,效率越来越高。

    • ALL 全表扫描;
    • index 索引全扫描;
    • range 索引范围扫描,常用语<,<=,>=,between,in等操作;
    • ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中;
    • eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询;
    • const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询;
    • null MySQL不访问任何表或索引,直接返回结果;
    • 虽然上至下,效率越来越高,但是根据cost模型,假设有两个索引idx1(a, b, c),idx2(a, c),SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1,那么是type为range,如果走idx2,那么type是ref;当需要扫描的行数,使用idx2大约是idx1的5倍以上时,会用idx1,否则会用idx2。

    Extra

    • **Using filesort:**MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行;
    • **Using temporary:**使用了临时表保存中间结果,性能特别差,需要重点优化;
    • **Using index:**表示相应的 select 操作中使用了覆盖索引(Coveing Index),避免访问了表的数据行,效率不错!如果同时出现 using where,意味着无法直接通过索引查找来查询到符合条件的数据;
    • **Using index condition:**MySQL5.6之后新增的ICP,using index condtion就是使用了ICP(索引下推),在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据。

    3、show profile 分析

    了解SQL执行的线程的状态及消耗的时间。

    默认是关闭的,开启语句“set profiling = 1;”

    SHOW PROFILES ;SHOW PROFILE FOR QUERY  #{id};
    

    4、trace

    trace分析优化器如何选择执行计划,通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

    set optimizer_trace="enabled=on";set optimizer_trace_max_mem_size=1000000;select * from information_schema.optimizer_trace;
    

    5、确定问题并采用相应的措施

    • 优化索引;
    • 优化SQL语句:修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤;
    • 改用其他实现方式:ES、数仓等;
    • 数据碎片处理。

    三、场景分析

    1、最左匹配

    1)索引

    KEY `idx_shopid_orderno` (`shop_id`,`order_no`)
    

    2)SQL语句

    select * from _t where orderno=''
    

    查询匹配从左往右匹配,要使用order_no走索引,必须查询条件携带shop_id或者索引(shop_id,order_no)调换前后顺序。

    2、隐式转换

    1)索引

    KEY `idx_mobile` (`mobile`)
    

    2)SQL语句

    select * from _user where mobile=12345678901
    

    隐式转换相当于在索引上做运算,会让索引失效。mobile是字符类型,使用了数字,应该使用字符串匹配,否则MySQL会用到隐式替换,导致索引失效。

    3、大分页

    1)索引

    KEY `idx_a_b_c` (`a`, `b`, `c`)
    

    2)SQL语句

    select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;
    

    对于大分页的场景,可以优先让产品优化需求,如果没有优化的,有如下两种优化方式:

    • 一种是把上一次的最后一条数据,也即上面的c传过来,然后做“c < xxx”处理,但是这种一般需要改接口协议,并不一定可行;‘
    • 另一种是采用延迟关联的方式进行处理,减少SQL回表,但是要记得索引需要完全覆盖才有效果,SQL改动如下:
    select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;
    

    4、in + order by

    1)索引

    KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)
    

    2)SQL语句

    select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10
    

    in查询在MySQL底层是通过n*m的方式去搜索,类似union,但是效率比union高。

    in查询在进行cost代价计算时(代价 = 元组数 * IO平均值),是通过将in包含的数值,一条条去查询获取元组数的,因此这个计算过程会比较的慢,所以MySQL设置了个临界值(eq_range_index_dive_limit),5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200,即in条件超过了200个数据,会导致in的代价计算存在问题,可能会导致Mysql选择的索引不准确。

    3)处理方式

    可以(order_status, created_at)互换前后顺序,并且调整SQL为延迟关联。

    5、范围查询阻断,后续字段不能走索引

    1)索引

    KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)
    

    2)SQL语句

    select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10
    

    范围查询还有“IN、between”。

    6、不等于、不包含不能用到索引的快速搜索

    可以用到ICP

    select * from _order where shop_id=1 and order_status not in (1,2)select * from _order where shop_id=1 and order_status != 1
    

    在索引上,避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。

    7、优化器选择不使用索引的情况

    如果要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问的数据占整个表中数据的蛮大一部分时(一般是20%左右),优化器会选择通过聚集索引来查找数据。

    select * from _order where  order_status = 1
    

    查询出所有未支付的订单,一般这种订单是很少的,即使建了索引,也没法使用索引。

    8、复杂查询

    select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;
    

    如果是统计某些数据,可能改用数仓进行解决;

    如果是业务上就有那么复杂的查询,可能就不建议继续走SQL了,而是采用其他的方式进行解决,比如使用ES等进行解决。

    9、asc和desc混用

    select * from _t where a=1 order by b desc, c asc
    

    desc 和asc混用时会导致索引失效。

    10、大数据

    对于推送业务的数据存储,可能数据量会很大,如果在方案的选择上,最终选择存储在MySQL上,并且做7天等有效期的保存。

    那么需要注意,频繁的清理数据,会照成数据碎片,需要联系DBA进行数据碎片处理。

    参考资料

    • 深入浅出MySQL:数据库开发、优化与管理维护(唐汉明 / 翟振兴 / 关宝军 / 王洪权)
    • MySQL技术内幕——InnoDB存储引擎(姜承尧)
    • https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
    • https://dev.mysql.com/doc/refman/5.7/en/cost-model.html
    • https://www.yuque.com/docs/share/3463148b-05e9-40ce-a551-ce93a53a2c66
    • https://blog.csdn.net/agonie201218/article/details/107026646
    更多相关内容
  • 跟着乐于分享的数据库大师梁敬彬抓住表象背后的SQL本质 有人就有江湖,有江湖就有IT系统,有IT系统就有数据库,有数据库就有SQL,SQL..., 随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包
  • sql优化工具.zip

    2019-06-19 16:01:55
    SQL Optimizer for SQL Server 优化工具。可以吧你的SQL优化成最优。 解压密码DotNet
  • 基于案例的sql优化

    2018-10-16 23:41:02
    基于案例的sql优化
  • sql优化的几种方法

    2018-05-18 23:12:34
    sql优化的几种方法sql优化的几种方法sql优化的几种方法sql优化的几种方法sql优化的几种方法
  • 基于Oracle的SQL优化2

    2016-08-23 23:23:51
    基于Oracle的SQL优化
  • 收获,不止SQL优化 PDF 带书签 第一部分
  • SQL优化方案——性能优化,包含52条sql优化建议,让你sql飞一般的运行
  • 基于Oracle的SQL优化

    2016-01-29 21:36:32
    《基于Oracle的SQL优化》是一本与众不同的书,它的目的是使读者真正掌握如何在 Oracle数据库里写出高质量的 SQL语句,以及如何在 Oracle数据库里对有性能问题的 SQL做诊断和调整。, 《基于Oracle的SQL优化》从 ...
  • Oracle数据库SQL优化详解

    千次阅读 2022-01-07 16:53:15
    Oracle数据库SQL优化1. Oracle SQL优化概述2. Oracle SQL优化详解2.1 Oracle 查询阻塞2.2 Oracle 查询耗时 SQL2.3.Oracle 查看执行计划2.4.Oracle 查看收集统计信息2.5.Oracle 查询优化器 -- 改写查询语句2.6.Oracle...

    1. Oracle SQL优化概述

    我们所执行的sql语句Oracle并不会直接去执行,而是经过数据库优化器优化以后再去执行。但是毕竟Oracle优化器也不是万能的,也有Oracle自身无法实现的优化语句,这就需要我们在书写sql语句的时候需要注意。注意SQL规范有助于避免SQL性能问题,也能避免不必要的异常。

    对于Oracle的sql语句优化也是有序可循的,按照步骤依次分析梳理,找出根源所在,针对性优化才有效果,而不是盲目来一通,以下是简单梳理优化步骤。

    Oracle 查询阻塞,查询耗时 SQL,查看执行计划,查看收集统计信息,查询优化器 – 改写查询语句,查询优化器 – 访问路径,查询优化器 – 表连接方法,索引,视图,减少数据库访问次数,面向对象,分开执行耗时操作,子程序内联,动态 SQL,避免在查询中使用函数,指定子程序 OUT 或 IN OUT 参数为引用传递,尽量少用循环语句,数据类型使用注意事项,字符串处理,短路评估,并发更新大表。

    2. Oracle SQL优化详解

    2.1 Oracle 查询阻塞

    如果你的 SQL 或系统突然 hang 了,很有可能是因为一个 session 阻塞了另一个 session,如何查询是否发生阻塞了呢?看看下面的 SQL吧。

    -- 如果你的 SQL 或系统突然 hang 了,很有可能是因为一个 session 阻塞了另一个 session,如何查询是否发生阻塞了呢?看看下面的 SQL吧。
    select 
      blocksession.sid        as block_session_sid,
      blocksession.serial#    as block_session_serial#,
      blocksession.username   as block_session_username,
      blocksession.osuser     as block_session_osuser,
      blocksession.machine    as block_session_machine,
      blocksession.status     as block_session_status,
      blockobject.object_name as blocked_table,
      waitsession.sid         as wait_session_sid,
      waitsession.serial#     as wait_session_serial#,
      waitsession.username    as wait_session_username,
      waitsession.osuser      as wait_session_osuser,
      waitsession.machine     as wait_session_machine,
      waitsession.status      as wait_session_status
    from 
      v$lock          blocklock,
      v$lock          waitlock,
      v$session       blocksession,
      v$session       waitsession,
      v$locked_object lockedobject,
      dba_objects     blockobject
    where 
      blocklock.block    = 1
      and blocklock.sid != waitlock.sid
      and blocklock.id1 = waitlock.id1
      and blocklock.id2 = waitlock.id2
      and blocklock.sid = blocksession.sid
      and waitlock.sid  = waitsession.sid
      and lockedobject.session_id = blocksession.sid
      and lockedobject.object_id  = blockobject.object_id;
      
    -- 如果上面的语句返回了结果,表明发生了阻塞,这个时候你可以把使用 blocksession 的程序停掉。
    -- 如果还是不能解决问题,那只能让 DBA 帮你把 blocksession kill 掉,如何 kill 呢? 试一试下面的语句吧。
    ALTER SYSTEM KILL SESSION '<block_session_sid>,<block_session_serial#>';
    ALTER SYSTEM KILL SESSION '113,55609';
      
    

    如果没有发生阻塞,系统就是很慢,该怎么办呢?在" Oracle 查询耗时 SQL"找答案吧。

    2.2 Oracle 查询耗时 SQL

    当你的系统变慢时,如何查询系统中最耗时的 SQL 呢?试一试下面的 SQL 吧。

    select * from (
    	select * from V$SQLSTATS
    	
    	-- 最耗时的 SQL
    	-- ELAPSED_TIME 指的是总耗时(毫秒),平均耗时 = ELAPSED_TIME/EXECUTIONS
    	-- order by ELAPSED_TIME DESC
    	
    	-- 查询执行次数最多的 SQL
    	-- order by EXECUTIONS DESC
    	
    	-- 读硬盘最多的 SQL
    	-- order by DISK_READS DESC
    	
    	-- 最费 CPU 的 SQL
    	-- order by BUFFER_GETS DESC
    ) where rownum <=50;
    

    一旦查询到耗时 SQL,你需要查看一下它们的执行计划才能知道它们为什么慢,不知道如何查询执行计划?看看这里吧 <Oracle 查看执行计划>。

    2.3.Oracle 查看执行计划

    我们可以通过 EXPLAIN PLAN 语句生成执行计划,该语句把执行计划保存到一个叫做 PLAN_TABLE 的表中,我们可以通过查询这个表来查看执行计划。下面是一个简单例子。
    查看执行计划的两种方式:1.EXPLAIN PLAN。2.autotrace。3.plsql查看执行计划菜单或者F5查看。

    1.EXPLAIN PLAN

    -- 生成执行计划
    EXPLAIN PLAN 
    SET STATEMENT_ID = 'test'
    FOR
    select * from employees where employee_id < 10;
     
    -- 由于 PLAN_TABLE 表非常复杂,Oracle 提供下面的方式察看执行计划
    SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'test', 'ALL'));
     
    -- 如果你想察看更多细节,你也可以直接查询表
    select * from plan_table where statement_id = 'test'
    

    2.autotrace
    如果你使用的是 sqlplus 工具,你还可以通过它提供了 autotrace 功能来查看执行计划,你只需要下面的两步,非常简单,下面是一个简单的例子。

    -- 第一步: 打开 autotrace
    SQL> set autotrace on
     
    -- 第二步: 执行 SQL 语句
    SQL> select * from test;
    

    3.plsql查看执行计划菜单或者F5
    在这里插入图片描述
    4.V$SQL_PLAN

    上面查看执行计划的方式有一个缺陷,它们必须由人触发,随着执行环境,时间,统计信息等的不同,执行计划有可能不同,有没有办法查看已经执行过 SQL 的执行计划呢?答案是肯定的,下面是一个简单的例子。

    -- 第一步: 通过下面的语句找到 SQL_ID 
    select SQL_ID,SQL_TEXT from v$sql 
    where sql_text like '%KAFKA_MSG_QUEUE%';
     
    -- 第二步: 通过下面的方式查看执行计划
    select * from V$SQL_PLAN where SQL_ID='g7b1uz8n2mdvf' 
    order by CHILD_NUMBER, id;
     
    -- 注意,上面的语句可以查询出该语句多次的执行计划,你可以加上时间来过滤
    select * from V$SQL_PLAN where SQL_ID='g7b1uz8n2mdvf' 
    and "TIMESTAMP"=TIMESTAMP '2015-08-20 19:38:06.000'
    order by CHILD_NUMBER, id
     
    -- 上面语句的结果可读性差,试一试下面的语句吧
    select '| Operation                         |Object Name                    |  Rows | Bytes|   Cost |'
    	as "Explain Plan in library cache:" from dual
    union all
    select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
           decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
           rpad(decode(id, 0, '----------------------------',
           substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
           ||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,'  ',
           decode(sign(cardinality-1000), -1, cardinality||' ',
           decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
           decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
           trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
           lpad(decode(bytes,null,' ',
           decode(sign(bytes-1024), -1, bytes||' ',
           decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
           decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
           trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
           lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
           decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
           trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
    from v$sql_plan sp
    where sp.SQL_ID='g7b1uz8n2mdvf' 
    and "TIMESTAMP"=TIMESTAMP '2015-08-20 19:38:06.000';
    

    在这里插入图片描述

    2.4.Oracle 查看收集统计信息

    统计信息相当于情报,对 Oracle 至关重要,如果统计信息不准确,Oracle 就会做出错误的判断。那如何查看统计信息呢?试一试下面的 SQL 吧。

    -- 查看表统计信息
    select * from DBA_TABLES where OWNER = 'HR' and TABLE_NAME = 'TEST';
    select * from DBA_TAB_STATISTICS where OWNER = 'HR' and TABLE_NAME = 'TEST';
     
    -- 查看列统计信息
    select * from DBA_TAB_COL_STATISTICS where OWNER = 'HR' and TABLE_NAME = 'TEST';
     
    -- 查看索引统计信息
    select * from DBA_IND_STATISTICS where OWNER = 'HR' and TABLE_NAME = 'TEST';
    

    通常,Oracle 会在每天固定时间段自动维护统计信息。但是对于某些表,这是远远不够的,如:某些表每天都需要清空,然后重新导入。这个时候,我们需要手动收集统计信息。

    -- 方法1: 使用 DBMS_STATS.GATHER_TABLE_STATS 手动收集存储过程
    DBMS_STATS.GATHER_TABLE_STATS('HR', 'ORDERS');
     
    -- 方法2:删除并锁定统计信息,如果没有统计信息,Oracle 会动态收集统计信息
    BEGIN
    	DBMS_STATS.DELETE_TABLE_STATS('HR','ORDERS');
    	DBMS_STATS.LOCK_TABLE_STATS('HR','ORDERS');
    END;
    

    Oracle 推荐我们使用方法1。 除此之外,DBMS_STATS 包还提供了下面的存储过程用来收集统计信息。

    GATHER_INDEX_STATS       收集索引统计信息
    GATHER_TABLE_STATS       收集指定表,列,索引的统计信息
    GATHER_SCHEMA_STATS      收集指定模式下所有对象的统计信息
    GATHER_SYSTEM_STATS      收集系统统(I/O,CUP)计信息
    GATHER_DICTIONARY_STATS  收集 SYS, SYSTEM 模式下对象的统计信息
    GATHER_DATABASE_STATS    收集所有数据库对象的统计信息
    GATHER_FIXED_OBJECTS_STATS   收集 FIXED 对象的统计信息
    

    DBMS_STATS 包还提供许多子程序让我们可以对统计信息进行操作,如:查询,删除,锁定,恢复等,更多详情你可以参考 PL/SQL Packages and Types 手册。

    2.5.Oracle 查询优化器 – 改写查询语句

    当我们执行一条查询语句的时候,我们只告诉 Oracle 我们想要哪些数据,至于数据在哪里,怎么找,那是查询优化器的事情,优化器需要改写查询语句,决定访问路径(如:全表扫描,快速全索引扫描,索引扫描),决定表联接顺序等。至于选择哪种方式,优化器需要根据数据字典做出判断。

    那优化器如何改写查询语句呢?

    第一种方法叫合并视图,如果你的查询语句中引用了视图,那么优化器会把视图合并到查询中,下面是一个简单的例子,需要注意的是优化器也不是神,如果你的视图包含集合操作符,聚合函数,Group by 等,优化器也傻了,不知道如何合并了。

    -- 视图定义
    CREATE VIEW employees_50_vw AS
    	SELECT employee_id, last_name, job_id, salary, commission_pct, department_id
    	FROM employees
    	WHERE department_id = 50;
     
    -- 查询语句
    SELECT employee_id
    FROM employees_50_vw
    WHERE employee_id > 150;
     
    -- 合并视图后的查询语句
    SELECT employee_id
    FROM employees
    WHERE department_id = 50
    AND employee_id > 150;
    

    第二种方法叫谓词推进(Predicate Pushing),对于那些无法执行合并视图的查询语句,Oracle 会把查询语句中的条件挪到视图中,下面是一个简单的例子。

    -- 视图定义
    CREATE VIEW all_employees_vw AS
    	(SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees)
    	UNION
    	(SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers);
     
    -- 查询语句的查询语句
    SELECT last_name
    FROM all_employees_vw
    WHERE department_id = 50;
     
    -- 谓词推进
    SELECT last_name
    FROM ( 
    	SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees
    		WHERE department_id=50 -- 注意此处
    	UNION
    	SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers
    		WHERE department_id=50 -- 注意此处
    );
    

    第三种方法是将非嵌套子查询转化为表连接。下面是一个简单的例子。需要注意的是,并不是所有的非嵌套子查询都能转化为表连接,对于下面的例子而言,如果 customers.cust_id 不是主键,转化后会产生笛卡尔集。

    -- 非嵌套子查询
    SELECT *
    FROM sales
    WHERE cust_id IN (SELECT cust_id FROM customers);
     
    -- 表连接
    SELECT sales.*
    FROM sales, customers
    WHERE sales.cust_id = customers.cust_id;
    

    第四种方法是使用物化视图改写查询,物化视图是将一个查询的结果集保存在一个表中,如果你的查询语句和某个物化视图兼容,那么 Oracle 就可以直接从物化视图中取得数据。

    2.6.Oracle 查询优化器 – 访问路径

    全表扫描(full table scans)
    不要以为全表扫描就一定慢, 全表扫描时, 由于数据块在磁盘中是连续的,Oracle 可以一次读取多个块来提高查询效率,至于多少个块,是由 DB_FILE_MULTIBLOCK_READ_COUNT 决定的。所以,如果你需要返回一个表的大部分数据,全表扫描要比索引扫描快。除此之外,Oracle 会自动对小表进行全表扫描,那什么是小表呢?就是语句块小于 DB_FILE_MULTIBLOCK_READ_COUNT 定义的值。
    如果你想让 Oracle 使用全表扫描,你也可以通过下面的方式指示 Oracle 使用全表扫描。

    select /* FULL(t)*/ * from test t where col = 'test';
    

    索引扫描(index scans)
    你有没有想过,我们通过索引扫描时,如何通过索引定位到表中的位置呢?答案是通过 Rowid, Rowid 是 Oracle 内部使用的,用来标示行存储地址,所以通过 Rowid 定位行记录是最快的。有一点特别需要注意,Oracle 读写磁盘的最小单位是块,一个块可能包含多行,所以全表扫描还是索引扫描取决于访问块的百分比,而不是行的百分比。假设我们现在需要访问 3 行,这 3 行可能在一个块中,也可能在两个块中,还可能在三个块中,很明显,最理想的情况是在一个块中,Oracle 使用索引聚簇因子(index clustering factor)来衡量这种特性。索引聚簇因子越高,表明 Oracle 通过 Rowid 访问行的代价就越高。此外,Oracle 会根据索引类型的不同,是否排序,采用不同的索引扫描方式。

    唯一索引扫描(Index Unique Scans)
    如果你的查询条件有等价操作符(=),且恰好能用到唯一索引,那么 Oracle 会采用唯一索引扫描,当然你也可以通过下面的方式建议 Oracle 采用哪个索引。

    select /* INDEX(t test_id_pk)*/ * from test t where col = 'test';
    

    索引范围扫描(Index Range Scans)
    如果你的查询条件有范围操作符(>,>=,<,<=,like ‘abc%’),且恰好能用到索引,那么 Oracle 会采用索引范围扫描,当然你也可以通过下面的方式建议 Oracle 采用哪个索引。

    select /* INDEX(t test_id_pk)*/ * from test t where col = 'test';
    

    索引降序范围扫描(Index Range Scans Descending)
    如果你的查询条件有范围操作符且要求用索引列排序,那么 Oracle 会采用索引降序范围扫描,这样做的好处是 Oracle 可以省略排序这个非常耗时的步骤。当然你也可以通过下面的方式建议 Oracle 采用索引降序范围扫描。

    如果你的索引是升序的。
    select /* INDEX_DESC(t test_id_pk)*/ * from test t where col = 'test';
    
    如果你的索引是降序的。
    select /* INDEX_ASC(t test_id_pk)*/ * from test t where col = 'test';
    

    索引跳跃扫描(Index Skip Scans)
    如果你的索引是复合索引,也就是索引包含多个列,如下所示。

    CREATE INDEX cust_idx ON customers (gender, email);
    

    全索引扫描(Full Scans)
    如果你的查询需要排序或分组,且排序或分组用到的列恰好是索引列,那么 Oracle 会采用全索引扫描,由于索引列是有序的,这样 Oracle 可以省略排序这个非常耗时的步骤。

    快速全索引扫描(Fast Full Index Scans)
    如果你要查询的所有列都包含在索引中,Oracle 就不需要访问表了,这样 Oracle 就可能通过并行和一次读取多个块来提高查询索引的效率。你也可以通过下面的方式建议 Oracle 采用快速全索引扫描。

    select /* INDEX_FFS(t test_id_pk)*/ * from test t where col = 'test';
    

    索引连接扫描(Index Joins)
    如果你的表有多个索引,恰好你要查询的所有列包含在这些索引中,Oracle 就不需要访问表了,Oracle 只需要把这些索引连接起来。你也可以通过下面的方式建议 Oracle 采用索引连接。

    select /* INDEX_JOIN(t test_id_idx test_name_idx)*/ * from test t where col = 'test';
    

    位图索引扫描(Bitmap Indexes)
    索引聚簇扫描(Indexed Cluster Access)
    如果你的表包含在了某个索引聚簇中,Oracle 会使用索引聚簇来执行查询。

    select /* INDEX_COMBINE(t test_idx1 test_idx2)*/ * from test t where col = 'test';
    

    Hash 聚簇扫描(Hash Cluster Access)
    如果你的表包含在了某个Hash 聚簇中,Oracle 会使用 Hash 聚簇来执行查询。

    采样扫描(Sample Table Scans)
    如果你的查询语句包含 SAMPLE 子句,那么 Oracle 会使用 采样扫描。

    2.7.Oracle 查询优化器 – 表连接方法

    循环嵌套连接,小表驱动大表,避免笛卡尔积的出现。

    循环嵌套连接(Nested Loop Joins)。

    SELECT e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
    

    对于循环嵌套,你可以把表想象成数组,Oracle 会采用如下的方式执行查询。

    String[] departments = {};
    String[] employees = {};
     
    // 外层循环
    for(String dep: departments) {
    	// 内层循环
    	for(String emp: employees) {
    		
    	}
    }
    

    很明显,如果 employees 很大且没有索引,外层循环每执行一次都需要全表扫描 employees,这是不可接受的。所以循环嵌套表连接方式适合那些内层循环数据量少且有索引的情形。
    当然,你也可以通过下面的方式建议 Oracle 采用循环嵌套连接方式。

    -- USE_NL
    SELECT /*+ USE_NL(e d) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
     
    --USE_NL_WITH_INDEX,指定 e 为内层循环表
    SELECT /*+ USE_NL_WITH_INDEX(e) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
     
    --USE_NL_WITH_INDEX,指定 e 为内层循环表,同时指定索引
    SELECT /*+ USE_NL_WITH_INDEX(e emp_dep_id_idx) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
    

    你还可以通过下面的方式建议 Oracle 不要采用循环嵌套连接方式。

    SELECT /*+ NO_USE_NL(e d) */ e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
    AND e.department_id = d.department_id;
    

    2.8.Oracle 索引

    要正确使用索引,避免索引失效,创建适当的索引。

    不可否认,提高性能最直接有效的方式就是创建索引,正因为如此,好多人把它当做救命的良药,随意创建索引,殊不知维护索引的代价是非常大的。Oracle 官方给了一个大约的数字,维护一个索引所需要的代价大约是操作本身的 3 倍。另外,索引也有好多种类型,不同的索引适应的场景也不同。

    1.索引组织表(Index-Organized Tables)

    创建索引时,如何选择索引列呢?其实就是查询用到的列,包括 SELECT,WHERE,JOIN, ORDER 等用到的列,但是列的顺序是有讲究的,应该把那些重复值最少的列放在最前面。如果你创建一个索引包含一个表的所有列,那么你应该将该表创建为索引组织表(IOT),如果一个表的列比较少,这么做是可以的。对于列多的表,千万别这么干。因为索引组织表的记录存储在索引的叶子节点上,当我们向表中插入数据时,Oracle 为了维护索引需要移动数据,这会大大降低插入速度。下面是一个简单的例子。

    CREATE TABLE test
    (
      id    NUMBER(10),
      name  VARCHAR2(30),
      CONSTRAINT pk_test PRIMARY KEY (id)
    ) ORGANIZATION INDEX;
    

    2.B 树索引(B-Tree Indexes)

    B 树索引是默认的索引类型,特别适合主键,或重复值比较少的列或列的组合,如何判断重复值得多少呢?看看下面的公式吧,下面的值越高越好,主键是 1.0

    SELECT COUNT(DISTINCT COLUMN) / COUNT(*) FROM TEST;
    

    下面是一个简单的例子。

    --普通索引
    CREATE INDEX test_idx_name ON test (name);
     
    --唯一索引
    CREATE UNIQUE INDEX test_idx_id ON test (id);
    

    3.位图索引(Bitmap Indexes)

    位图索引和 B 树索引正好相反,非常适合重复值比较多的列,最好是只有几项,如:国籍,性别,省份等等,而且这些值基本上不会频繁更新。注意,频繁更新的列不适合位图索引,如,订单表有个列表示是否被处理,只有两个值,YES, NO。下面是一个简单的例子。

    --位图索引
    CREATE BITMAP INDEX test_idx_country ON test (country);
    CREATE BITMAP INDEX test_idx_gender ON test (gender);
    CREATE BITMAP INDEX test_idx_province ON test (province);
    

    4.基于函数的索引(Function-based Indexes)
    在索引字段上使用函数会使索引失效,有时候,我们可以通过把它转化为范围扫描来避免这个问题,但是,有时候,我们必须要使用函数,如:忽略大小写查询,这个时候,我们可以在创建基于函数的索引,如下是一个简单的例子。

    CREATE INDEX test_idx ON test(UPPER(NAME));
    

    5.分区索引(Partitioned Indexes)

    分区索引的思想是将大的索引分成多个小索引,这样索引扫描时就可以减少IO。对于普通表,我们可以创建下面两种类型的分区索引。

    -- 范围全局分区索引
    CREATE INDEX test_idx ON test (amount)
    GLOBAL PARTITION BY RANGE (amount)
    (
    	PARTITION p1 VALUES LESS THAN (1000),
    	PARTITION p2 VALUES LESS THAN (2500),
    	PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );
     
    -- 哈希(散列)全局分区索引
    CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
    GLOBAL PARTITION BY HASH (cust_last_name)
    PARTITIONS 4;
    

    对于分区表,除了可以创建上面两种类型的分区索引外,我们还可以给某个表分区或所有分区创建分区索引。

    -- 创建分区表
    CREATE TABLE test
    (
            id number,  
            year number,  
            month number  
    )
    PARTITION BY RANGE (year)    
    (
            PARTITION p1 VALUES LESS THAN (2013),   
            PARTITION p2 VALUES LESS THAN (2014),    
            PARTITION p3 VALUES LESS THAN (2015),    
            PARTITION p4 VALUES LESS THAN (3000)
    );
     
    -- 给所有表分区创建分区索引
    CREATE INDEX test_idx ON test (year, month) LOCAL;
     
    -- 给 p1分区创建分区索引
    CREATE INDEX test_idx ON test (year, month) LOCAL (PARTITION p1);
    

    6.反向索引(Reverse Key Indexes)

    反向索引就是将正常的键值头尾调换后再进行存储,比如原值是’abc’,将会以’cba’形式进行存储,为什么要这样做呢?原因是有些值是根据一定的规则生成的,如时间,序列等,当我们插入大量数据时,它们都需要同时插入到索引的某个区域,Oracle 称之为热点区域(hot spot),如果我们使用反向存储,可以有效的避免这个问题,但是有利就有弊,我们不能使用反向索引进行范围扫描,所以使用它要慎重。这里给我们一个非常重要的启示,在设计自动生成的值时,如果有可能,每两次生成的值范围要广。

    7.复合索引

    复合索引就是有多个列的索引,索引列的顺序很关键,如果索引包含 A,B,C 三列,而你的查询条件只包含 B,C 两列,Oracle 就无法使用索引。

    重建索引

    如果让你重建索引该怎么办呢?大多数人都会先删除索引,然后再创建新索引,其实通过下面的方式重建索引更快,因为 Oracle 可以利用现有的索引重建索引。

    ALTER INDEX ... REBUILD
    

    有关索引的视图
    如果让你查询一下某个表都定义了哪些索引该怎么办呢?呵呵,很简单,只需查询一下下面的视图即可。

    all_indexes
    all_ind_columns
    all_ind_expressions
    all_ind_partitions
    all_ind_subpartitions
    all_ind_statistics
    

    那如果让你查询一下某个索引是否被用到,该怎么办呢?首先,你需要让 Oracle 帮你监控一下索引,等过一段时间后,你可以查询下面的视图查看索引是否被用到。

    -- 让 Oracle 监控索引
    ALTER INDEX <indx name> MONITORING USAGE;
     
    -- 查询是否被用到
    select * from v$object_usage
    

    2.9.Oracle 视图

    视图有好多优点,如它可以简化开发。但是有一点特别需要注意,最好不要使用多个视图做联合查询,因为优化器将很难优化这样的查询。

    2.10.Oracle 减少数据库访问次数

    连接数据库是非常耗时的,虽然应用程序会采用连接池技术,但与数据库交互依然很耗时,这就要求我们尽量用一条语句干完所有的事,尤其要避免把 SQL 语句写在循环中,如果你遇到这样的人,应该毫不犹豫给他两个耳光

    2.11 Oracle 面向对象

    我们都知道,传统数据库都是关系型数据库,随着 Java 和 面向对象的流行,Oracle也与时俱进,加入了面向对象的特性,最典型的就是嵌套表,嵌套表使查询变得复杂,同时它的性能也不如传统表好。

    2.12 Oracle 分开执行耗时操作

    首先,我们看一个故事,联合利华引进了一条香皂包装生产线,结果发现这条生产线有个缺陷:常常会有盒子里没装入香皂。总不能把空盒子卖给顾客啊,他们只得请了一个学自动化的博士后设计一个方案来分拣空的香皂盒。博士后拉起了一个十几人的科研攻关小组,综合采用了机械、微电子、自动化、X射线探测等技术,花了几十万,成功解决了问题。每当生产线上有空香皂盒通过,两旁的探测器会检测到,并且驱动一只机械手把空皂盒推走。

    中国南方有个乡镇企业也买了同样的生产线,老板发现这个问题后大为发火,找了个小工来说:你他妈给老子把这个搞定,不然你给老子爬出去。小工很快想出了办法:他在生产线旁边放了台风扇猛吹,空皂盒自然会被吹走。

    还有一个故事,美国宇航局发现圆珠笔在失重环境下无法使用, 结果花了2千万美刀研制出了失重环境下可用的圆珠笔, 而苏联人一直用铅笔。

    这两个故事给我们一个很重要的启示,性能问题都是由于资源竞争导致的,所以,一个简单的想法就是尽量分开执行耗时的操作。这看似一个最简单不过的道理,但是随着软件变得越来越大,到最后可能没有人知道什么时候执行什么操作时合适的。

    2.13 Oracle 子程序内联

    如果子程序 A 调用 B,内联可以把 B 的代码合并到 A 中,从而减少子程序调用,提高性能,下面是一个简单的例子。

    -- 子程序 A
    PROCEDURE A 
    IS
    BEGIN
    	-- 指定下面的子程序 B 内联
    	PRAGMA INLINE (B, 'YES')
    	B(1);
    	
    	-- 注意此处的子程序不会内联
    	B(2);
    END A;  
    
    -- 子程序 B  
    PROCEDURE B (x PLS_INTEGER)  
    IS
    BEGIN
    	DBMS_OUTPUT.PUT_LINE(x);  
    END B;
    

    如果你觉得在每一个子程序调用前加上 PRAGMA INLINE 在麻烦,你可以将编译参数 PLSQL_OPTIMIZE_LEVEL 设置为 3 (默认值是2),这样 Oracle 会把每一个子程序调用都内联。

    2.14 Oracle 动态 SQL

    如果你还不知道什么是动态 SQL,请参考 PL/SQL 动态 SQL
    如果有可能,尽量不要使用动态 SQL,动态 SQL需要运行时编译,影响性能。如果一定要使用动态 SQL,Oracle 推荐我们优先使用 EXECUTE IMMEDIATE,它要比 DBMS_SQL 性能更好。

    2.15 Oracle 避免在查询中使用函数

    一个查询可能要搜索上百万行数据,在查询中使用函数就可能被调用上百万次,这会严重影响性能,下面是一个简单的例子。

    -- 创建表
    CREATE TABLE Department 
    (
    	Department_Id    NUMBER(9,0),
    	Department_Name  VARCHAR2(40)
    );
     
    CREATE TABLE Employee
    (
    	Employee_id    NUMBER(9,0),
    	Employee_Name  VARCHAR2(40),
    	Department_Id  NUMBER(9,0)
    );
     
     
    -- 定义函数
    CREATE OR REPLACE FUNCTION getDepartmentNameById(
    	DepartmentId    number   
    )  
    	RETURN varchar2  
    AS    
    	DepartmentName VARCHAR2(40);    
    BEGIN    
    	select Department_Name into DepartmentName from Department where Department_Id = DepartmentId;    
    	return DepartmentName;      
    END;
     
     
    -- 查询 SQL -- 使用函数
    SELECT getDepartmentNameById(Department_Id) DepartmentName, Employee_Name FROM Employee;
     
     
    -- 查询 SQL -- 使用表连接
    SELECT 
    	d.Department_Name, 
    	e.Employee_Name 
    FROM 
    	Department d,
    	Employee e
    WHERE
    	d.Department_Id = e.Department_Id;
    

    2.16 Oracle 指定子程序 OUT 或 IN OUT 参数为引用传递

    通常,子程序的 OUT 或 IN OUT 参数为值传递,为了防止程序可能发生的异常,Oracle 将它保存到临时变量中,当程序正常退出时,Oracle 把临时变量中值赋给实际参数,异常退出时,保持实际参数不变。当OUT 或 IN OUT 参数返回大批量数据时,由于使用了临时变量导致占用大量内存,这时我们可以在参数的后面加上 NOCOPY 来提示Oracle使用引用传递.

    PROCEDURE test (infor IN OUT NOCOPY Collection) IS
    BEGIN
    	NULL;
    END;
    

    2.17 Oracle 尽量少用循环语句

    下面是一个使用循环语句删除表记录的例子。

    DECLARE
      TYPE NumList IS TABLE OF NUMBER;
      emps NumList := NumList(10, 30, 70);
    BEGIN
      FOR i IN emps.FIRST..emps.LAST LOOP
        DELETE FROM employees WHERE employee_id = emps(i);
      END LOOP;
    END;
    

    在你的工作中,千万别写出上面的语句,否则应该毫不犹豫的给自己两个耳光,应该使用批处理的方式,如下:

    DECLARE
      TYPE NumList IS TABLE OF NUMBER;
      emps NumList := NumList(10, 30, 70);
    BEGIN
      -- FORALL 语句批量执行下面的语句
      FORALL i IN emps.FIRST..emps.LAST
        DELETE FROM employees WHERE employee_id = emps(i);
    END;
    

    或使用 TABLE 表达式,如下:

    CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER;
    
    DECLARE
      emps number_table := number_table(10, 30, 70);
    BEGIN
      DELETE FROM employees WHERE employee_id IN (SELECT COLUMN_VALUE FROM TABLE(emps));
    END;
    

    2.18 Oracle 数据类型使用注意事项

    Oracle 支持 NUMBER,BINARY_FLOAT, BINARY_DOUBLE 等数值数据类型,NUMBER 更精确,BINARY_FLOAT 或 BINARY_DOUBLE 更高效,所以如果有可能,尽量优先使用 BINARY_FLOAT 或 BINARY_DOUBLE

    此外,PL/SQL 还支持好多它们的子类型,有些子类型是由约束的,如不允许 NULL,尽量不要使用约束多的子类型,因为在运行时, Oracle 需要额外的检查,确保它们没有违反约束。

    在运行时,Oracle 会自动进行数据类型转换,如下面的语句把一个字符串赋值给一个数值类型的变量,Oracle 是不会抱错的.

    declare
      x PLS_INTEGER;
    begin
      x := '1';
    end;
    

    应该尽量避免这种情况,如果一个变量是从一个表中获取的,我们应该定义这个变量的类型为 TABLE_NAME.COLUMN_NAME%TYPE

    2.19 Oracle 字符串处理

    如果你需要处理复杂字符串,尽量不要自己编写函数,Oracle 提供了大量的字符串函数供我们使用。点击此处察看 Oracle 支持哪些函数

    如果还是不能满足你的要求,我们还可以使用正则表达式。可以说正则表达式几乎没有处理不了的字符串问题。如果你还不知道什么是正则表达式,点击此处(正则表达式精萃),如果你不知道如何在 Oracle 中使用正则表达式, 点击此处察看如何使用正则表达式

    2.20 Oracle 短路评估

    Oracle 按照从左到右的顺序评估条件表达式,一旦确定结果就停止后面的评估,所以我们应该尽量将轻量级的条件放在最左边。

    IF (x > 10) OR function(parameter) THEN
    

    2.21 Oracle 并发更新大表

    如果你有一个很大的表要更新,千万别想着一次搞定,如果你这么干了,你会发现需要很长时间,最后的结果也不一定成功,为什么呢? 第一,Oracle 需要锁定整个表,这个过程中极有可能发生死锁。第二,Oracle 需要更多的日志文件用于回滚。第三,一旦发生点小问题会导致一个老鼠害一锅汤。那该怎么办呢?答案是分段执行,少量多次并发执行,下面是一个简单的例子。

    DECLARE
      l_sql_stmt VARCHAR2(1000);
      
    BEGIN
      -- 第一步: 创建任务
      DBMS_PARALLEL_EXECUTE.CREATE_TASK ('task_test');
      
      -- 第二步: 根据 ROWID 切块, 每次 100 行
      DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('task_test', 'TRADE', 'EMPLOYEES', true, 100);
      
      -- 第三步: 并发执行下面的 SQL
      l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e SET e.salary = e.salary + 10 WHERE rowid BETWEEN :start_id AND :end_id';
      DBMS_PARALLEL_EXECUTE.RUN_TASK('task_test', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 10);
      
      -- 第四步: 删除任务
      DBMS_PARALLEL_EXECUTE.DROP_TASK('task_test');
    END;
    

    另外多提一句,关于性能优化涉及面非常广,不仅仅是数据库SQL上的优化,设计架构高性能架构的类似秒杀系统需要多方面考虑,从前端静态页面,DNS加速,带宽,服务器性能,中间件性能,数据库性能,SQL只是其中一环。最后,感谢shangboerds老师,转载于shangboerds文章后验证补充。
    SQL优化规范
    sql优化规范
    Oracle SQL 优化精萃
    MySQL执行计划Explain详解
    MySQL数据库索引及失效场景

    展开全文
  • sql优化书籍大全

    2018-03-21 10:57:07
    关于mysql的sql优化书籍,满满的干货,保证你不会后悔
  • 随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题,...
  • 深入揭示OracleSQL优化与调优的原理、核心技术与思想方法 盖国强鼎力推荐! Oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是SQL性能问题。本书是作者十年磨一剑的成果之一...
  • MySql基础知识总结(SQL优化篇)

    万次阅读 多人点赞 2021-07-10 12:26:26
    2、添加索引 3、更改索引顺序 4、去掉in 5、小结 四、双表sql优化 1、建表语句 2、左连接查询 3、小结 五、避免索引失效的一些原则 六、一些其他的优化方法 1、exist和in 2、order by 优化 七、sql顺序 -> 慢日志...

    🍅 作者简介:CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    目录

    一、explain返回列简介

    1、type常用关键字

    2、Extra常用关键字

    二、触发索引代码实例

    1、建表语句 + 联合索引

    2、使用主键查询

    3、使用联合索引查询

    4、联合索引,但与索引顺序不一致

    5、联合索引,但其中一个条件是 >

    6、联合索引,order by

    三、单表sql优化

    1、删除student表中的联合索引。

    2、添加索引

    3、更改索引顺序

    4、去掉in

    5、小结

    四、双表sql优化

    1、建表语句

    2、左连接查询 

    3、小结

    五、避免索引失效的一些原则

    六、一些其他的优化方法

    1、exist和in

    2、order by 优化

    七、sql顺序 -> 慢日志查询

    1、慢查询日志

    2、阈值

    八、慢查询日志 --> mysqldumpslow工具

    1、mysqldumpslow工具

    2、查询不同条件下的慢sql

    九、分析海量数据

    1、show profiles

    2、精确分析,sql诊断

    3、全局查询日志

    十、锁机制详解

    1、操作分类

    2、操作范围

    3、加读锁,代码实例

    4、加写锁

    5、MyISAM表级锁的锁模式

    6、MyISAM分析表锁定

    7、InnoDB分析表锁定

    8、加行锁代码实例

    9、行锁的注意事项


    本篇是MySQL知识体系总结系列的第二篇,该篇的主要内容是通过explain逐步分析sql,并通过修改sql语句与建立索引的方式对sql语句进行调优,也可以通过查看日志的方式,了解sql的执行情况,还介绍了MySQL数据库的行锁和表锁。

    一、explain返回列简介

    1、type常用关键字

    system > const > eq_ref > ref > range > index > all。

    1. system:表仅有一行,基本用不到;
    2. const:表最多一行数据配合,主键查询时触发较多;
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型;
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取;
    5. range:只检索给定范围的行,使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range;
    6. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小;
    7. all:全表扫描;

    实际sql优化中,最后达到ref或range级别。

    2、Extra常用关键字

    Using index:只从索引树中获取信息,而不需要回表查询;

    Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。需要回表查询。

    Using temporary:mysql常建一个临时表来容纳结果,典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时;

    索引原理及explain用法请参照前一篇:MySQL索引原理,explain详解

    二、触发索引代码实例

    1、建表语句 + 联合索引

    CREATE TABLE `student` (
      `id` int(10) NOT NULL,
      `name` varchar(20) NOT NULL,
      `age` int(10) NOT NULL,
      `sex` int(11) DEFAULT NULL,
      `address` varchar(100) DEFAULT NULL,
      `phone` varchar(100) DEFAULT NULL,
      `create_time` timestamp NULL DEFAULT NULL,
      `update_time` timestamp NULL DEFAULT NULL,
      `deleted` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `student_union_index` (`name`,`age`,`sex`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    2、使用主键查询

    3、使用联合索引查询

    4、联合索引,但与索引顺序不一致

    备注:因为mysql优化器的缘故,与索引顺序不一致,也会触发索引,但实际项目中尽量顺序一致。

    5、联合索引,但其中一个条件是 >

    6、联合索引,order by

    where和order by一起使用时,不要跨索引列使用。

    三、单表sql优化

    1、删除student表中的联合索引。

    2、添加索引

    alter table student add index student_union_index(name,age,sex);

    优化一点,但效果不是很好,因为type是index类型,extra中依然存在using where。

    3、更改索引顺序

    因为sql的编写过程

    select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

    解析过程

    from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

     因此我怀疑是联合索引建的顺序问题,导致触发索引的效果不好。are you sure?试一下就知道了。

    alter table student add index student_union_index2(age,sex,name);

    删除旧的不用的索引:

    drop index student_union_index on student

    索引改名

    ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index

    更改索引顺序之后,发现type级别发生了变化,由index变为了range。

    range:只检索给定范围的行,使用一个索引来选择行。

    备注:in会导致索引失效,所以触发using where,进而导致回表查询。

    4、去掉in

    ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取;

    index 提升为ref了,优化到此结束。

    5、小结

    1. 保持索引的定义和使用顺序一致性;
    2. 索引需要逐步优化,不要总想着一口吃成胖子;
    3. 将含in的范围查询,放到where条件的最后,防止索引失效;

    四、双表sql优化

    1、建表语句

    CREATE TABLE `student` (
      `id` int(10) NOT NULL,
      `name` varchar(20) NOT NULL,
      `age` int(10) NOT NULL,
      `sex` int(11) DEFAULT NULL,
      `address` varchar(100) DEFAULT NULL,
      `phone` varchar(100) DEFAULT NULL,
      `create_time` timestamp NULL DEFAULT NULL,
      `update_time` timestamp NULL DEFAULT NULL,
      `deleted` int(11) DEFAULT NULL,
      `teacher_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    CREATE TABLE `teacher` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(100) DEFAULT NULL,
      `course` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    2、左连接查询 

    explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = '数学'

    上一篇介绍过,联合查询时,小表驱动大表。小表也称为驱动表。其实就相当于双重for循环,小表就是外循环,第二张表(大表)就是内循环。

    虽然最终的循环结果都是一样的,都是循环一样的次数,但是对于双重循环来说,一般建议将数据量小的循环放外层,数据量大的放内层,这是编程语言的优化原则。

    再次代码测试:

    student数据:四条

    teacher数据:三条

    按照理论分析,teacher应该为驱动表。

    sql语句应该改为:

    explain select teacher.name,student.name from teacher left join student on teacher.id = student.id  where teacher.course = '数学'

    优化一般是需要索引的,那么此时,索引应该怎么加呢?往哪个表上加索引?

    索引的基本理念是:索引要建在经常使用的字段上。

    on teacher.id = student.id可知,teacher表的id字段使用较为频繁。

    left join on,一般给左表加索引;因为是驱动表嘛。

    alter table teacher add index teacher_index(id);
    alter table teacher add index teacher_course(course);

    备注:如果extra中出现using join buffer,表明mysql底层觉得sql写的太差了,mysql加了个缓存,进行优化了。

    3、小结

    1. 小表驱动大表
    2. 索引建立在经常查询的字段上
    3. sql优化,是一种概率层面的优化,是否实际使用了我们的优化,需要通过explain推测。

    五、避免索引失效的一些原则

    1、复合索引,不要跨列或无序使用(最佳左前缀);

    2、符合索引,尽量使用全索引匹配;

    3、不要在索引上进行任何操作,例如对索引进行(计算、函数、类型转换),索引失效;

    4、复合索引不能使用不等于(!=或<>)或 is null(is not null),否则索引失效;

    5、尽量使用覆盖索引(using index);

    6、like尽量以常量开头,不要以%开头,否则索引失效;如果必须使用%name%进行查询,可以使用覆盖索引挽救,不用回表查询时可以触发索引;

    7、尽量不要使用类型转换,否则索引失效;

    8、尽量不要使用or,否则索引失效;

    六、一些其他的优化方法

    1、exist和in

    select name,age from student exist/in (子查询);

    如果主查询的数据集大,则使用in;

    如果子查询的数据集大,则使用exist;

    2、order by 优化

    using filesort有两种算法:双路排序、双路排序(根据IO的次数)

    MySQL4.1之前,默认使用双路排序;双路:扫描两次磁盘(①从磁盘读取排序字段,对排序字段进行排序;②获取其它字段)。

    MySQL4.1之后,默认使用单路排序;单路:只读取一次(全部字段),在buffer中进行排序。但单路排序会有一定的隐患(不一定真的是只有一次IO,有可能多次IO)。

    注意:单路排序会比双路排序占用更多的buffer。

    单路排序时,如果数据量较大,可以调大buffer的容量大小。

    set max_length_for_sort_data = 1024;单位是字节byte。

    如果max_length_for_sort_data值太低,MySQL底层会自动将单路切换到双路。

    太低指的是列的总大小超过了max_length_for_sort_data定义的字节数。

    提高order by查询的策略:

    1. 选择使用单路或双路,调整buffer的容量大小;
    2. 避免select * from student;(① MySQL底层需要对*进行翻译,消耗性能;② *永远不会触发索引覆盖 using index);
    3. 符合索引不要跨列使用,避免using filesort;
    4. 保证全部的排序字段,排序的一致性(都是升序或降序);

    七、sql顺序 -> 慢日志查询

    慢查询日志就是MySQL提供的一种日志记录,用于记录MySQL响应时间超过阈值的SQL语句(long_query_time,默认10秒) ;

    慢日志默认是关闭的,开发调优时打开,最终部署时关闭。

    1、慢查询日志

    (1)检查是否开启了慢查询日志:

    show variables like '%slow_query_log%'

    (2)临时开启:

    set global slow_query_log = 1;

    (3)重启MySQL: 

    service mysql restart;

    (4)永久开启:

    /etc/my.cnf中追加配置:

    放到[mysqld]下:

    slow_query_log=1
    
    slow_query_log_file=/var/lib/mysql/localhost-slow.log

    2、阈值

    (1)查看默认阈值:

    show variables like '%long_query_time%'

    (2)临时修改默认阈值:

    set global long_query_time = 5;

    (3)永久修改默认阈值:

    /etc/my.cnf中追加配置:

    放到[mysqld]下:

    long_query_time = 5;

    (4)MySQL中的sleep:

    select sleep(5);

    (5)查看执行时间超过阈值的sql:

    show global status like '%slow_queries%';

    八、慢查询日志 --> mysqldumpslow工具

    1、mysqldumpslow工具

    慢查询的sql被记录在日志中,可以通过日志查看具体的慢sql。

    cat /var/lib/mysql/localhost-slow.log

    通过mysqldumpslow工具查看慢sql,可以通过一些过滤条件,快速查出需要定位的慢sql。

    mysqldumpslow --help

    参数简要介绍:

    s:排序方式

    r:逆序

    l:锁定时间

    g:正则匹配模式

    2、查询不同条件下的慢sql

    (1)返回记录最多的3个SQL

    mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

    (2)获取访问次数最多的3个SQL

    mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

    (3)按时间排序,前10条包含left join查询语句的SQL

    mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

    九、分析海量数据

    1、show profiles

    打开此功能:set profiling = on;

    show profiles会记录所有profileing打来之后,全部SQL查询语句所花费的时间。

    缺点是不够精确,确定不了是执行哪部分所消耗的时间,比如CPU、IO。

    2、精确分析,sql诊断

    show profile all for query  上一步查询到的query_id。

    3、全局查询日志

    show variables like '%general_log%'

    开启全局日志:

    set global general_log = 1;

    set global log_output = table;

    十、锁机制详解

    1、操作分类

    读写:对同一个数据,多个读操作可以同时进行,互不干扰。

    写锁:如果当前写操作没有完毕,则无法进行其它的读写操作。

    2、操作范围

    表锁:一次性对一张表整体加锁。

    如MyISAM存储引擎使用表锁,开销小、加锁快、无死锁;但锁的范围大,容易发生冲突、并发度低。

    行锁:一次性对一条数据加锁。

    如InnoDB存储引擎使用的就是行锁,开销大、加锁慢、容易出现死锁;锁的范围较小,不易发生锁冲突,并发度高(很小概率发生高并发问题:脏读、幻读、不可重复读)

    lock table 表1 read/write,表2 read/write,...

    查看加锁的表:

    show open tables;

    3、加读锁,代码实例

    会话0:
    lock table student read;
    select * from student; --查,可以
    delete from student where id = 1;--增删改,不可以
    
    select * from user; --查,不可以
    delete from user where id = 1;--增删改,不可以
    

    如果某一个会话对A表加了read锁,则该会话可以对A表进行读操作、不能进行写操作。即如果给A表加了读锁,则当前会话只能对A表进行读操作,其它表都不能操作

    会话1:
    select * from student; --查,可以
    delete from student where id = 1;--增删改,会“等待”会话0将锁释放
    
    会话1:
    select * from user; --查,可以
    delete from user where id = 1;--增删改,可以
    
    

    会话0给A表加了锁,其它会话的操作①可以对其它表进行读写操作②对A表:读可以,写需要等待释放锁。

    4、加写锁

    会话0:
    lock table student write;

    当前会话可以对加了写锁的表,可以进行任何增删改查操作;但是不能操作其它表;

    其它会话:

    对会话0中对加写锁的表,可以进行增删改查的前提是:等待会话0释放写锁。

    5、MyISAM表级锁的锁模式

    MyISAM在执行查询语句前,会自动给涉及的所有表加读锁,在执行增删改前,会自动给涉及的表加写锁。

    所以对MyISAM表进行操作,会有如下情况发生:

    (1)对MyISAM表的读操作(加读锁),不会阻塞其它会话(进程)对同一表的读请求。但会阻塞对同一表的写操作。只有当读锁释放后,才会执行其它进程的写操作。

    (2)对MyISAM表的写操作(加写锁),会阻塞其它会话(进程)对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

    6、MyISAM分析表锁定

    查看哪些表加了锁:

    show open tables;1代表被加了锁

    分析表锁定的严重程度:

    show status like 'table%';

    Table_locks_immediate:可能获取到的锁数

    Table_locks_waited:需要等待的表锁数(该值越大,说明存在越大的锁竞争)

    一般建议:Table_locks_immediate/Table_locks_waited > 5000,建议采用InnoDB引擎,否则采用MyISAM引擎。

    7、InnoDB分析表锁定

    为了研究行锁,暂时将自动commit关闭,set autocommit = 0;

    show status like '%innodb_row_lock%';

    Innodb_row_lock_current_waits:当前正在等待锁的数量
    Innodb_row_lock_time:等待总时长。从系统启动到现在一共等待的时间
    Innodb_row_lock_time_avg:平均等待时长。从系统启动到现在一共等待的时间
    Innodb_row_lock_time_max:最大等待时长。从系统启动到现在一共等待的时间
    Innodb_row_lock_waits:等待次数。从系统启动到现在一共等待的时间

    8、加行锁代码实例

    (1)查询student

    select id,name,age from student

    (2)更新student 

    update student set age = 18 where id = 1

    (3)加行锁 

    通过select id,name,age from student for update;给查询加行锁。

    依旧修改成功,原因是MySQL默认是自动提交的,因此需要暂时将自动commit关闭

    set autocommit = 0;

    9、行锁的注意事项

    (1)如果没有索引,行锁自动转为表锁。

    (2)行锁只能通过事务解锁。

    (3)InnoDB默认采用行锁

    优点:并发能力强,性能高,效率高

    缺点:比表锁性能损耗大

    高并发用InnoDb,否则用MyISAM。

    🍅 作者简介:CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    关注公众号,回复1024,获取Java学习路线思维导图、加入万粉计划交流群

    展开全文
  • Spark SQL之SQL优化

    千次阅读 2022-02-22 14:43:25
    Spark SQL之SQL优化 主要关注于执行性能问题 1、避免使用不必要的UDF函数 UDF:用户定义函数,可以直接在SQL语句中计算的函数,如:count、sum、avg、max、min等 2、没有指定数据分区 SQL-1: SELECT date FROM test_...

    Spark SQL之SQL优化

    主要关注于执行性能问题

    1、避免使用不必要的UDF函数

    UDF:用户定义函数,可以直接在SQL语句中计算的函数,如:count、sum、avg、max、min等

    2、没有指定数据分区

    SQL-1: SELECT date FROM test_table WHERE date = '20170829' and value = 1;
    SQL-2: SELECT date FROM test_table PARTITION(p_20170829) p WHERE value = 1
    

    SQL-1虽然通过where表达式能够筛选到并执行所需的分区,但是在分析的时候会先获取所有的分区列表再进行筛选,会导致更多的内存占用,显然SQL-2更优

    3、避免使用笛卡尔积

    简单了解什么是sql里的笛卡尔积

    SQL-1: select * from gbk, utf8 where gbk.key= utf8.key
    SQL-2: select * from gbk join utf8 where gbk.key= utf8.key
    

    上面是先对tabel进行笛卡尔积再where筛选,应当按如下方式书写

    select * from gbk join utf8 on gbk.key= utf8.key
    

    4、show Rowcount

    读取一个表有多少行记录,虽然count也可以,但是速度非常慢!

    show rowcount extended test;
    

    按分区来显示每个分区有多少行记录

    5、show Tablesize

    读取一个表有多大,单位是字节

    show tablesize extended test;
    

    6、避免使用delete和update

    7、当存在小表join的时候,查看是否可以使用mapjoin

    MAPJION会把小表全部读入内存中,在map阶段直接拿另外一个表的数据和内存中表数据做匹配,由于在map是进行了join操作,省去了reduce运行的效率也会高很多。
    简单说就是在Map阶段将小表读入内存,顺序扫描大表完成Join。这样就不会由于数据倾斜导致某个reduce上落数据太多而失败。

    8、数据倾斜

    Join的时候,相同的key会hash到同一task中进行。如果某个key值太多,这个task就会成为瓶颈。

    一种是唯一值非常少,极少数值有非常多的记录值(唯一值少于几千)
    一种是唯一值比较多,这个字段的某些值有远远多于其他值的记录数,但是它的占比也小于百分之一或千分之一

    简单来说就是大量的相同key被partition分配到一个分区里,造成了’一个人累死,其他人闲死’的情况,这种情况是我们不能接受的,这也违背了并行计算的初衷,首先一个节点要承受着巨大的压力,而其他节点计算完毕后要一直等待这个忙碌的节点,也拖累了整体的计算时间,可以说效率是十分低下的.

    判断数据倾斜的方法:

    任务中单个或者少量的task拖慢整个任务速度。
    通过查看id的分布,返回表A的id分布情况,通过返回结果可以看出是否某些id的数量远大于其他。

    select id,count(id) as id_nums group by id order by num desc
    

    处理数据倾斜的方法

    1、过滤或者去重:如果导致倾斜的id 在最后的结果没有作用,可以在Join 之前将其过滤掉。例如,id=NULL 的数据往往是多余的。
    2、Map Join:如果join的表不大,使用Map Join。
    3、数据分离:可以将表A 的数据分为两部分A1和A2,A1中不包含数据倾斜的数据(id=b),A2中只包含数据倾斜的数据(id=a)。A1和A2 分别与B 进行Join,然后将结果Union。SQL 如下所示。A1与A2 的Join 没有数据倾斜的问题。由于A2通常不会很大,A2与B的Join 采用Map Join。但是如果A2很大则不适用
    4、数据打散:将有数据倾斜的表(A)中的id加上后缀,起到“打散”的作用,即"id_0"-“id_2”。为了结果正确性,小表B中的id需要将每条数据都“复制”多份。如下图所示。

    数据分离:

    select * from (
    select * from A1 join B on A1.id = B.id
    union all
    select /*+ MAPJOIN (A2) */ * from A2 join B on A2.id = B.id
    )
    

    数据打散:
    处理之前需要先查看一下id的分布情况

    表A:
    SELECT id, value, concat(id, string1(cast(rand() * 10000 as int)%3)) as new_id 
    FROM A;
    表B:
    SELECT id, value, concat(id, suffix) as new_id
    FROM (
       SELECT id, value, suffix
       FROM B Lateral View explode(array(0,1,2)) tmp as suffix
    )
    
    

    9、join与groupby的顺序

    如果Join 后有聚合函数操作,尽可能把聚合操作放到Join 之前,这样在Join 时 key 是单一的,避免数据倾斜。例如,

    select t1.key, t1.total from
    (select key ,sum(value) as total from t1 group by key
    ) t1
    join t2 on t1.key = t2.key
    

    前提是t2的key没有重复

    展开全文
  • PostgreSQL常用SQL优化技巧

    千次阅读 2022-01-07 16:10:06
    PostgreSQL的SQL优化技巧其实和大多数使用CBO优化器的数据库类似,因此一些常用的SQL优化改写技巧在PostgreSQL也是能够使用的。当然也会有一些不同的地方,今天我们来看看一些在PostgreSQL常用的SQL优化改写技巧。 1...
  • Sql优化总结!详细!(2021最新面试必问)

    万次阅读 多人点赞 2021-04-09 16:20:03
    Sql优化基础Sql优化查询SQL尽量不要使用select *,而是具体字段避免在where子句中使用or来连接条件使用varchar代替char尽量使用数值替代字符串类型查询尽量避免返回大量数据使用explain分析你SQL执行计划是否使用了...
  • 大聪明教你学Java | 谈谈SQL优化

    万次阅读 2021-11-25 09:25:59
    SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至某些公司招聘时,在岗位职能上都有明码标注,这也就更证明了掌握SQL优化技能的重要性,借此机会就和大家分享一下我在优化SQL时的一些经验心得。 小弟献丑啦,嘿嘿...
  • sql优化常用的几种方法

    千次阅读 2022-03-16 13:26:26
    1.EXPLAIN type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用到的索引名。...MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好
  • 基于案例学SQL优化

    2014-09-02 23:03:08
    \基于案例学SQL优化
  • MySQL性能优化 SQL优化方法技巧
  • 本书是作者十年磨一剑的成果之一,深入分析与解剖Oracle SQL优化与调优技术,主要内容包括: 第一篇“执行计划”详细介绍各种执行计划的含义与操作,为后面的深入分析打下基础。重点讲解执行计划在SQL语句执行的生命...
  • 基于Oracle的SQL优化脚本

    热门讨论 2014-07-17 17:30:14
    崔华的《基于Oracle的SQL优化》一书配套脚本,纯手打
  • sql优化的几种方式

    万次阅读 多人点赞 2018-11-05 10:20:46
    一、为什么要对SQL进行优化 我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也...二、SQL优化的一些方法 1.对查询进行优化,应尽量避免全表扫描...
  • 本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:1、EXPLAIN做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:type列,连接类型。一个...
  • 收获,不止SQL优化 PDF 带书签 第二部分
  • MySQL高级(SQL性能分析,SQL优化

    千次阅读 多人点赞 2022-02-27 20:47:01
    SQL性能分析,SQL优化
  • 大数据量+高并发的数据库SQL优化

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 626,465
精华内容 250,586
关键字:

sql优化