如何优化SQL
⼀、SQL简介
1.1.什么是SQL?
SQL(结构化查询语⾔)是⼀种⽤于访问和处理数据库的标准计算机语⾔。
1.2.SQL能做什么？
SQL ⾯向数据库执⾏查询
SQL 可从数据库取回数据
SQL 可在数据库中插⼊新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限
⼆、如何优化SQL?
在应⽤系统开发初期，由于开发数据库数据⽐较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应⽤系统提交实际应⽤后，随着数据库中数据的增加，系统的响应速度就成为⽬前系统需要解决的最主要的问题之⼀。系统优化中⼀个很重要的⽅⾯就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于⼀个系统不是简单地能实现其功能就可，⽽是要写出⾼质量的SQL语句，提⾼系统的可⽤性。
由此可见，SQL的优化⾮常重要，那么优化SQL的⽅法有哪些呢？
2.1.使⽤索引
2.1.1索引简介
索引是⼀种单独的、物理的对数据库表中⼀列或多列的值进⾏排序的⼀种存储结构，它是某个表中⼀列或若⼲列值的集合和相应的指向表中物理标识这些值的数据页的逻辑清单。索引的作⽤相当于图书的⽬录，可以根据⽬录中的快速到所需的内容。
2.1.2索引分类以及原理
按照索引数据的存储⽅式可以将索引分为B树索引、位图索引、反向键索引和基于函数的索引等，其中B树索引是Oralce数据库中最常⽤的索引类型（也是默认的），原理如下图，其中Root为根节点，branch 为分⽀节点，leaf 到最下⾯⼀层称为叶⼦节点。每个节点表⽰⼀层，当查某⼀数据时先读根节点，再读⽀节点，最后到叶⼦节点。叶⼦节点会存放index entry （索引⼊⼝），每个索引⼊⼝对应⼀条记录。
Index entry 的组成部分：
Indexentry entry  header    存放⼀些控制信息。
Key column length    　某⼀key的长度
Key column value    　某⼀个key 的值
ROWID    指针，具体指向于某⼀个数据
下⾯这张图能更加清晰的描述索引的结构。
跟节点记录0⾄50条数据的位置，分⽀节点进⾏拆分记录0⾄10.......42⾄50，叶⼦节点记录每条数据的长度和值，并由指针指向具体的数据。
最后⼀层的叶⼦节是双向链接，它们是被有序的链接起来，这样才能快速锁定⼀个数据范围。
2.1.3索引的应⽤
了解完索引的原理之后，来看看索引的基本应⽤。创建⼀张User表和⼀张Department表，两者多对⼀关系，并往User表插⼊10万左右的数据，往Department表插⼊21条数据，DDL语句如下：
create table T_USER
(
id          NUMBER not null,
id_dept    NUMBER,
user_name VARCHAR2(100) not null,
employer_no VARCHAR2(100),
ad_location VARCHAR2(100),
status      NUMBER not null
)
alter table T_USER add constraint PK_USER_ID primary key (ID) using index
tablespace jay;
create table T_DEPARTMENT
(
id            NUMBER not null,
title        VARCHAR2(100CHAR) not null,
description  VARCHAR2(200CHAR),
status        NUMBER not null
);
alter table T_DEPARTMENT add constraint PK_DEPARTMENT_ID primary key (ID) using index tablespace jay;
需求1：查询AD_LOCATION为Beijing的⽤户的USER_NAME，SQL语句如下：
select t.user_name from t_user t where t.ad_location ='Beijing';
执⾏计划如下：
由此可见，该查询语句⽬前是⾛的全表扫描，当表的数据量很⼤时，查询效率会⽐较低下，可以通过建⽴索引进⾏优化：create index idx_location on t_user (ad_location);
再执⾏SQL，执⾏计划如下：
可以看到，创建索引idx_location之后，查询⾛了索引，Cost和Time都变⼩了，查询效率得到了提升。
需求2：查询AD_LOCATION为Beijing，且ID_DEPT为48的⽤户的USER_NAME，SQL语句如下：
select t.user_name from t_user t where t.ad_location ='Beijing' and t.id_dept= 48;
执⾏计划如下：
可以看到查询⾛了刚刚创建的索引IDX_LOCATION，但是⼜没有更快的⽅法呢？
由于where语句后⾯有两列查询条件，可以基于这两列建⽴复合索引：
create index idx_location_dept on t_user (ad_location,id_dept);
再看执⾏计划：
可以看到创建了复合索引后，查询⾛了复合索引idx_location_dept，Cost更⼩了。
那还能不能更好呢？
把select后⾯的user_name字段加到复合索引上⾯来：
create index idx_location_dept_username on t_user (ad_location,id_dept,user_name);
执⾏计划如下：
可以看到查询⾛了复合索引idx_location_dept_username，Cost更⼩了，⽽TABLE ACCESS BY INDEX ROWID这⼀⾏也消失了，这是因为通过索引到记录时，select 后⾯的user_name也在索引⾥⾯，不需要再通过关联rowid回表查询user_name，所以效率更快。
2.1.4如何避免索引失效sql语句优化方式
上⾯说完了索引的基本使⽤，然⽽在实际的开发⼯作中，有很多的不当操作都会使得索引失效，下⾯列举⼀些常⽤的使得索引失效的操作，并学习如何避免索引失效。1.不要在索引列上做计算或者函数操作。
由上图可知⽬前T_USER表⾥有两个索引，以IDX_DEPT为例，执⾏以下SQL：
select*from t_user t where t.id_dept =48；
可以看到正常⾛索引了。
如果对索引列进⾏计算：
select*from t_user t where t.id_dept*2=96
可以看到对索引列进⾏计算，索引失效了。
2.尽量避免like查询以%开头，以%开头会使索引失效
例如查询T_USER表中user_name以jay开头的⼈的AD_LOCATION：
select t.ad_location from t_user t where t.user_name like'jay%'
正常⾛了索引，如果是查询以jay结尾的⼈的AD_LOCATION呢select t.ad_location from t_user t where t.user_name like'%jay'
证实了索引列查询如果以%开头会使该索引失效。
3.字符型字段为数字时在where条件⾥需要添加引号
例如查询T_USER表中user_name为001的⼈的AD_LOCATION：select t.ad_location from t_user t where t.user_name='001'
正常做索引，如果不加引号：
select t.ad_location from t_user t where t.user_name=001
就变成了全表扫描，放弃了⾛索引。
4.where条件后⾯加is null会使索引失效
select t.ad_location from t_user t where t.user_name is null

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。