面试中的SQL优化问题

1、sql优化

1.1 SQL语言

是结构化查询语言(Structured Query Language)的简称，

是一种数据库查询和程序设计语言，

用于存取数据以及查询、更新和管理关系数据库系统，也是数据库脚本文件的扩展名。

1.2 SQL优化

定位有问题的语句，检查执行计划，检查执行过程中优化器的统计信息，分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划。

为了获得稳定的执行性能，SQL语句越简单越好

但是最佳的执行计划不一定是最佳的执行情况，一切以实际执行的情况为准。

2、sql优化的几种途径

第一部分：数据表格创建时：

1、选择合适的存储引擎（MySQL）

1.0 什么是存储引擎

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。

存储引擎是基于表，而不是基于库的。

所以存储引擎也可被称为表类型。

存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，但Oracle有OLTP和OLAP模式的区分。

存储引擎决定MySQL数据库中的表用什么样的方式来存储。

我们可以根据数据的特点来选择不同的存储引擎。

引擎包括三种，innodb、myisam、memory。

1.1 innoDB(默认引擎)

优势在于提供了良好的事务处理、崩溃修复能力和并发控制，支持回滚。

缺点是读写效率较差，占用的数据空间相对较大(连表查询)。

MySQL支持外键的存储引擎只有InnoDB，

在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

1.2 myisam

提供了高速检索和存储的能力，支持全文索引

MyISAM不支持事务、也不支持外键，

其优势是访问速度快，

对事物的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个

① myisam的表存储成3个文件。

② 文件的名字与表名相同。

​ 拓展名为：

​ frm（frm文件存储表的结构）

​ MYD（MYD文件存储数据，MYData的缩写）

​ MYI（MYI文件存储索引，MYIndex的缩写）

③ myisam存储引擎的表支持以下3种不同的存储格式：

​ 静态型：

MyISAM的默认存储格式

它的字段是固定长度的,使表具有极高的访问速度

缺点就是占用空间比较大

​ 动态型：

动态型包含变长字段，记录的长度不是固定的,

优点是它存数据时,采用的是数据的实际长度,能节省不少空间,

缺点是由于采用了数据的实际长度，每当数据更新,长度发生变更时，就变换了位置，使得原来得变得位置空闲，并且关联的数据并不是存放在相邻的块中,而且产生大量的碎片,需要定期进行碎片整理

​ 压缩型：

压缩型要用到myisampack工具，

空间占用相当小,只有原大小的一半,

而且读取数据时,还会对数据进行加压缩,

这个类型的表是只读表,不能进行修改.

1.3 memory

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。

该文件的文件名与表名相同，类型为frm类型（frm文件存储表的结构）。

而它的数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。

值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。

如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。

MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。

如果重启或者关机，所有数据都会消失。

因此，基于MEMORY的表的生命周期很短，一般是一次性。

2、Oracle的OLTP和OLAP模式的区分

2.1 OLTP联机事务处理

（on-line transaction processing）：

属于高可用的在线系统，注重事务，高响应，高并发(多人同时使用)。

操作语句，尽量以最简单的增删查改为主，避免复杂SQL。

这个模式是Oracle数据库也是传统关系型数据库的主要应用模式。

OLTP模式的应用场景，在日常中很常见:比如在线订票，银行交易，电子商务等等。

2.2 OLAP联机分析处理

（On-Line Analytical Processing）：

用于做复杂的SQL关联和数据分析，向高层展示汇总数据(也就是各种报表)，最终用于支撑决策。

也类似与平常说的数据仓库。

Oracle的OLAP模式在以前应用更多一些，但是通过最近几年大数据的热潮，Oracle的这个模式逐渐会被淘汰掉，更多会使用大数据系列工具(Hadoop系列)。

Hadoop系列：

一是对于PB级别的数据，Oracle的OLAP模式几乎毫无应对之法，单机处理性能始终有限，而Hadoop系列采用分布式存储，分布式处理，能横向扩展对于TB和PB级别的数据量都能有良好的处理性能。

二是Hadoop系列开源，免费，强一点的公司还可以自己做定制化，而Oracle不开源，维护费用昂贵。

当然，Oracle的OLAP模式现在还没有被淘汰，毕竟Hadoop的稳定性还在日趋完善，产品的替换也是需要很多人力，财力的投入。

3、设置表格字段时

使用varchar代替char，

因为变长字段记录的长度不是固定的，存储时采用的是数据的实际长度，节省空间。

采用数字型字段，

若只含数值信息的字段尽量不要设计为字符型，因为这会降低查询和连接的性能，并会增加存储开销。

也是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，

而对于数字型而言只需要比较一次就够了

4、创建表格时

添加索引，可以增加查询的速度，

但太多的索引会降低insert和update的效率，

因为插入和更新时有可能会重建索引，

一个表的索引最好不要超出6个，

具体要根据需要确定是否有必要添加索引

第二部分sql语句部分：

《 数据库技术内幕 》

① 减少数据访问： 设置合理字段类型，启用压缩，通过索引访问等减少磁盘IO(读写操作)

② 返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘io及网络io

③ 减少交互次数： 批量DML操作(数据操纵语言)，函数存储等减少数据连接次数

④ 减少服务器CPU开销： 尽量减少数据库排序操作，和全表查询 减少cpu 内存占用

⑤ 利用更多资源： 使用表分区(记录硬盘文件的地址)，可以增加并行操作，更大限度利用cpu资源

1、尽量避免全表查询

用具体的字段列表代替“*”，不要返回用不到的任何字段，否则会占用内存

禁止使用select *，

只获取需要的字段–查询很多无用字段，

增加CPU/IO/NET消耗；

不能有效的利用覆盖索引；

增删字段易出bug

不要出现 select * from table 的情况，优先考虑 where 和 order by

2、避免使用太过复杂的sql语句查询

尤其是出现嵌套的时候，

最好不要出现三层以上的嵌套，

否则会影响查询效率。

3、考虑使用临时表暂存

简化SQL语句的重要方法就是采用临时表暂存中间结果，

将临时结果暂存在临时表，可以避免程序中多次扫描主表。

但也要避免频繁创建和删除临时表，以减少系统表资源的消耗。

在新建临时表时，如果一次性插入数据量很大，

那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；

如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，

先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

4.应尽量避免在 where 子句中对字段进行 null 值判断

否则导致引擎放弃使用索引而进行全表扫描，降低效率

如：
select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

​ select id from t where num=0

5.应尽量避免在 where 子句中使用!=或<>操作符

否则引擎放弃使用索引而进行全表扫描，降低效率

6.应尽量避免在 where 子句中使用 or 来连接条件

否则导致引擎放弃使用索引而进行全表扫描，降低效率

如：
select id from t where num=10 or num=20

可以这样查询：

​ select id from t where num=10
​ union all
​ select id from t where num=20

7.in 和 not in 也要慎用

**否则会导致全表扫描，**降低效率

如：
select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

​ select id from t where num between 1 and 3

8.模糊查询也将导致全表扫描

注意使用like的时候尽量不要使用“%a%”，这样的不走索引，可以使用“a%”，走索引

select id from t where name like ‘%abc%’

9.应尽量避免在 where 子句中对字段进行表达式操作

否则会导致引擎放弃使用索引而进行全表扫描，降低效率

如：
select id from t where num/2=100

应改为:

​ select id from t where num=100*2

10.应尽量避免在where子句中对字段进行函数操作

否则会导致引擎放弃使用索引而进行全表扫描，降低效率

如：
select id from t where substring(name,1,3)=‘abc’–name以abc开头的id

应改为:

​ select id from t where name like ‘abc%’

11.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，

否则系统将可能无法正确使用索引

12.不要写一些没有意义的查询

会消耗系统资源

如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0

​ 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

​ create table #t(…)

13.最好用 exists 代替 in

速度更快

​ select num from a where num in(select num from b)

​ 用下面的语句替换：

​ select num from a where exists(select 1 from b where num=a.num)

exists 方式查询 比 in 方式查询效率高，但in 可读性较好。

建议尽可能使用exists方式，避免使用子查询(将结果保存在临时表中)，除非in 的参数为数值列表。