Oracle难点总结

Hello ！大家好，今天我来给大家简单总结一下Oracle的一些知识难点，喜欢的朋友请您动动小手指三联支持一下哦.

目录

1. PLSQL

2.游标

3. 存储过程

into 关键字用法

函数

触发器

DML（数据库数据操纵语言）触发器

替代触发器

DDL触发器

系统事件触发器

包 PACKAGE

SQL优化

SELECT语句 - 语法顺序：

SELECT语句 - 执行顺序：

SQL优化策略

一、避免不走索引的场景

二、SELECT语句其他优化

三、增删改 DML 语句优化

四、查询条件优化

五、建表优化

---

1. PLSQL

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。

-- PLSQL语句块 一连串SQL逻辑代码
declare
    -- 定义变量
begin
  -- 逻辑业务代码
end;

2.游标

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

3. 存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

declare
  --定义游标，将查询到的数据集合给到游标
  cursor cur_emp is
    select * from emp e;
  v_sal_level emp.sal_level%type;
begin
​
  --循环遍历游标
  for v_emp in cur_emp loop
    --薪资800 - 1500 等级为低，1500 - 3000 等级为中，3001 - 8000 等级为高， 其他就是超级高
    if v_emp.sal between 800 and 1500 then
      v_sal_level := '低';
    elsif v_emp.sal between 1501 and 3000 then
      v_sal_level := '中';
    elsif v_emp.sal between 3001 and 8000 then
      v_sal_level := '高';
    elsif v_emp.sal is not null then
      v_sal_level := '超级高';
    end if;
    update emp e set e.sal_level = v_sal_level where e.empno = v_emp.empno;
  end loop;
  
  commit;
end;

--2.存储过程  42s

alter table person_info drop column education;
create or replace procedure proc_test_person is
  cursor cur_person is
    select * from person_info where rownum < 1000000;
  v_education person_info.education%type;
begin
  for v_person in cur_person loop
    if v_person.age = 18 then
      v_education := '大一';
    elsif v_person.age between 19 and 20 then
      v_education := '大二';
    elsif v_person.age between 21 and 23 then
      v_education := '大三';
    elsif v_person.age > 23 then
      v_education := '大四';
    else
      v_education := '高中';
    end if;
    update person_info pi
       set pi.education = v_education
     where pi.p_id = v_person.p_id;
  end loop;
  commit;
end;
call proc_test_person();

into 关键字用法

declare
  v_ename emp.ename%type;
  v_job emp.job%type;
  v_sal emp.sal%type;
begin 
  -- into 关键字 ，将查询字段值赋值给属性，注意的是，查询必须是单条数据
  select e.ename,e.job,e.sal into v_ename, v_job, v_sal from emp e where e.empno = 7369;  
  dbms_output.put_line(v_ename || ',' || v_job || ',' || v_sal);
end;

函数

函数用于计算和返回一个结果值，把经常需要进行的计算写成函数,函数的调用是表达式的一部分。 函数与过程在创建的形式上有些相似，也是编译后放在内存中供用户使用。 函数必须有一个返回值，而过程没有做强制的规定。 RETURN在声明部分需要定义一个返回参数的类型，而在函数体中必须有一个RETURN语句。如果函数结束时还没有遇到返回语句，就会发生错误。

• 大大减少代码的冗余

• 节省资源开销

• 。。。

MYSQL版：

#将所有女学生按年龄从大到小排序，从第2条记录开始显示6名女学生的姓名、年龄、出生日期、手机号信息 
# 根据出生日期计算年龄
​
#没运用函数之前的写法
select t.studentName, ceil(year(now()) - year(bornDate)) 年龄, t.bornDate, t.phone from student t where t.sex = '女' order by t.bornDate limit 1,6;
​
#运用了函数的写法
select t.studentName, func_computer_age(t.bornDate) 年龄, t.bornDate, t.phone from student t where t.sex = '女' order by t.bornDate limit 1,6;
​
#差别1、sql可读性更好，2、sql查询性能更佳
​
-- mysql中创建一个自定义函数,用来根据出生日期计算年龄
delimiter $$
create or replace function func_computer_age(bornDate datetime)
-- 注意是returns而不是return
returns int
NO SQL
begin 
 set @age= ceil(year(now()) - year(bornDate));
​
return @age;
end $$
delimiter ;

触发器

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。

触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。

此外触发器是逻辑电路的基本单元电路，具有记忆功能，可用于二进制数据储存，记忆信息等。

DML（数据库数据操纵语言）触发器

DML触发器，就是在新增、删除、更新时触发（具体事件根据需求自定义）

-- 在周六日的时候不能对员工表进行DML操作
create or replace trigger tri_emp_dml_checked
before insert or delete or update on emp
--行级触发器：操作每一条的数据的时候都会触发这个触发器 
--块级触发器：只要做了这个操作就会触发一次这个触发器, :new 、：old不能在块级触发器中使用
--for each row
begin
  --to_char(sysdate, 'day') : 当前日期是周几
  if to_char(sysdate, 'day') in ('星期六', '星期日') then
     raise_application_error(-20006, '在周六日不能对 EMP 表进行DML操作');
   end if;
  --dbms_output.put_line('更新之前的值：' || :old.sal || ',更新之后的值：' || :new.sal);
end;
--:OLD获取之前的值  :NEW获取最新的值 实例： :OLD.sal = 880   :NEW.sal = 3000
​
​
--测试DML触发器
update emp e set e.sal = 5000 where e.deptno = 10;

示例二：

--删除获取更新部门表数据时，员工表相关联的数据也要跟着删除或更新
create or replace trigger tri_insteadof_dept_emp
--在对dept表进行 update、delete操作时触发这个触发器！
  after update or delete on dept
  for each row
begin
  dbms_output.put_line(:old.deptno || ',' || :new.deptno);
  if deleting then
    --删除员工表
    delete from emp e where e.deptno = :old.deptno;
  elsif updating then
    --更新员工表数据
    update emp e set e.deptno = :new.deptno where e.deptno = :old.deptno;
  end if;
end;

替代触发器

比如说一个视图，这个视图数据来源于两张表的并集，删除这个视图数据时，数据库不知道你到底要删哪张表？因此会导致报错！比如需求要删除某张表的，这种情况可以通过替代触发器来做！

create or replace view view_emp as (select * from emp union all select * from emp_temp);
​
-- 创建一个替代触发器，来实现类似需求
create or replace trigger tri_instead_of_view_emp
  -- instead of 代表是一个替代触发器
  instead of delete or update on view_emp
  for each row
begin
  dbms_output.put_line('deptno: ' || :old.deptno);
  if deleting then
    delete emp_temp et where et.deptno = :old.deptno;
  elsif updating then
    update emp_temp et set et.sal = :new.sal where et.deptno = :old.deptno;
  end if;
end;
​
-- 测试替代触发器
delete from view_emp where deptno = 20;
​
update view_emp set sal = 8888 where deptno = 20;

DDL触发器

DDL 是指 数据库对象操作语，例如create、drop、alter;

主要用于审核与规范对数据库中表，触发器，视图等结构上的操作。比如在修改表，修改列，新增表，新增列等。它在数据库结构发生变化时执行，我们主要用它来记录数据库的修改过程，以及限制程序员对数据库的修改，比如不允许删除某些指定表等。

--使用DDL触发器限制删表
create or replace trigger tri_ddl_emp_drop
--在删除 emp 表之前触发这个触发器
  before drop on schema
begin
  dbms_output.put_line(ora_dict_obj_name);
  
  dbms_output.put_line(ora_dict_obj_type);
  --根据系统函数来获取操作的对象名称和对象类型
  if ora_dict_obj_name = 'EMP' and ora_dict_obj_type = 'TABLE' then
    raise_application_error(-20007, '不能删除 emp 表！');
  end if;
end;

​

系统事件触发器

服务启动、关闭，用户登录、注销

--记录服务启动日志
create or replace trigger tri_sys_database
-- 在服务启动之后触发这个触发器
after startup on database
-- 在关闭服务器之前触发这个触发器
-- bebofore shutdown on database
begin
  dbms_output.put_line('数据库名称：' || ora_database_name || ',服务启动了！');
  insert into event_table values(ora_sysevent, sysdate, ora_client_ip_address);
end;
​

包 PACKAGE

oracle package是oracle包，是一组相关过程、函数、变量、常量#SinaEditor_Temp_FontName、类型和游标等PL/SQL程序设计元素的组合。包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。

作用：

(1)包package声明或定义：包定义部分是创建包的规范说明，声明包内数据类型、变量、常量、游标等元素。

这部分也是为使用者提供了透明的接口。

(2)包体packpage body:包体是包定义部分的具体实现。

(3)将有联系的对象打成包,方便使用

(4)包中对象包括储存过程,函数,游标,自定义类型和变量,可以在PL_SQL块中应用这些对象.

-- 创建一个包的定义
​
create or replace package pkg_emp is
  
  -- 定义变量
  v_dept emp.deptno%type;
  -- 定义常量 1、必须初始化时赋予初始值，2、过程中不能修改其值
  v_sal constant emp.sal%type := 8888;
​
  -- 维护薪资等级字段
  procedure proc_emp_maintian_sal_level;
  
  -- 根据部门编号更新员工薪资
  procedure proc_update_emp_sal(in_deptno in emp.deptno%type);
  
  -- 根据部门编号获取该部门的平均薪资
  function func_get_emp_avg(in_deptno in emp.deptno%type) return emp.sal%type;
  
  
  -- 根据部门编号获取该部门最大薪资
  function func_get_emp_max_sal(in_deptno in emp.deptno%type)  return emp.sal%type;
​
end pkg_emp;
​
​
-- 创建包体
create or replace package body pkg_emp is
​
  -- 维护薪资等级字段
  procedure proc_emp_maintian_sal_levelxxx is
    v_sal emp.sal%type;
  begin
    select e.sal into v_sal from emp e where rownum = 1;
  end;
​
  -- 维护薪资等级字段
  procedure proc_emp_maintian_sal_level is
    v_sal emp.sal%type;
  begin
    select e.sal into v_sal from emp e where rownum = 1;
  end;
​
  -- 根据部门编号更新员工薪资
  procedure proc_update_emp_sal(in_deptno in emp.deptno%type) is
    v_sal emp.sal%type;
  begin
    select e.sal into v_sal from emp e where rownum = 1;
  end;
​
  -- 根据部门编号获取该部门的平均薪资
  function func_get_emp_avg(in_deptno in emp.deptno%type) return emp.sal%type is
    v_avg_sal emp.sal%type;
  begin
    select avg(e.sal)
      into v_avg_sal
      from emp e where e.deptno = in_deptno
     group by e.deptno;
     
  
    return v_avg_sal;
  end;
​
  -- 根据部门编号获取该部门最大薪资
  function func_get_emp_max_sal(in_deptno in emp.deptno%type)
    return emp.sal%type is
    v_max_sal emp.sal%type;
  begin
    select max(e.sal)
      into v_max_sal
      from emp e where e.deptno = in_deptno
     group by e.deptno;
     
    return v_max_sal;
  end;
end pkg_emp;
​

SQL优化

BATJTMD等大厂的面试难度越来越高，但无论从大厂还是到小公司，一直未变的一个重点就是对SQL优化经验的考察。一提到数据库，先“说一说你对SQL优化的见解吧？”。

SQL优化已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，如果是你，在这个问题上能吊打面试官还是会被吊打呢？

面试官问你，多表连接查询SQL应该怎么优化

1. 多表关联字段类型必须一致

2. 在条件查询时，避免类型隐式转换

3. 关联字段是主外键关系

4. 如果关联字段是多个，那么应该考虑创建一个组合索引

SELECT语句 - 语法顺序：

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

SELECT语句 - 执行顺序：

FROM <表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。 ON <筛选条件> # 对笛卡尔积的虚表进行筛选 JOIN <join, left join, right join...> <join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中 WHERE <where条件> # 对上述虚表进行筛选 GROUP BY <分组条件> # 分组 <SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的 HAVING <分组筛选> # 对分组后的结果进行聚合筛选 SELECT <返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外 DISTINCT # 数据除重 ORDER BY <排序条件> # 排序 LIMIT <行数限制>

SQL优化策略

声明：以下SQL优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。

一、避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%陈%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'

如果需求是要在前面使用模糊查询，

• 使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置

• 使用FullText全文索引，用match against 检索

• 数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级

• 当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like '%xx%'。

2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1

优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

7. 查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8. where条件仅包含复合索引非前置列

如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。

select col1 from table where key_part2=1 and key_part3=2

9. 隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;

10. order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;
 
-- 走age索引
SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：

• 第一步：根据where条件和统计信息生成执行计划，得到数据。

• 第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。

• 第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

二、SELECT语句其他优化

1. 避免出现select *

首先，select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数，将指定列名以取代select *。

2. 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3.多表关联查询时，小表在前，大表在后。

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，你品，那就先去吃个饭再说吧是吧。

4. 使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

5. 用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

6.调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

三、增删改 DML 语句优化

1. 大批量插入数据

如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别。

方法一：

insert into T values(1,2); 
 
insert into T values(1,3); 
 
insert into T values(1,4);

方法二：

Insert into T values(1,2),(1,3),(1,4);

选择后一种方法的原因有三。

• 减少SQL语句解析的操作，MySQL没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；

• 在特定场景可以减少对DB连接次数

• SQL语句较短，可以减少网络传输的IO。

2. 适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

• 事务占用的undo数据块；

• 事务在redo log中记录的数据块；

• 释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

四、查询条件优化

1. 对于复杂的查询，可以使用中间临时表 暂存数据

2. 优化group by语句

默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

3. 优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo 
   LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
      WHERE salesinfo.CustomerID IS NULL 

连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4. 优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
 
UNION ALL 
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

低效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
 
UNION 
 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

5.拆分复杂SQL为多个小SQL，避免大事务

• 简单的SQL容易使用到MySQL的QUERY CACHE；

• 减少锁表时间特别是使用MyISAM存储引擎的表；

• 可以使用多核CPU。

6. 使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

7. 使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率。

案例1：

select * from t where thread_id = 10000 and deleted = 0 
   order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0
   order by gmt_create asc limit 0, 15) a, t 
      where a.id = t.id; 

上述例子必须满足t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

五、建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段。

2. 尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3. 查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4. 用varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。