SQL优化

---

1 全表扫描情况

2 如何定位低效 SQL？

3 优化工具——SHOW PROFILE、trace、EXPLAIN

4 【SQL优化——尽量避免】——全表扫描、无意义查询、区分度低、索引太多、重复操作、游标、大事务、返回大数据

5 【SQL优化——尽量使用】——小类型字段、varchar、关联查询

6 【SQL优化——其他策略】——临时表、字段写全、GROUP BY / ORDER BY、LIMIT 分页、自定义变量

-----------------------------------------------------------------------------------

---

1 全表扫描

用SQL语句对数据库进行操作时，如果引起全表扫描会对数据库的性能形成影响，会引起全表扫描的情况如下：

---

1.1 like ‘%…%’

• 原因：like本身效率就比较低，对于like ‘%...%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。

• 解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，

  • 右模糊like ‘…%’：会使用索引的
  • 左模糊like‘%...’：无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’；
  • 全模糊：是无法优化的，一定要的话考虑用搜索引擎。
  • 出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。

---

1.2 is null

• 原因：Oracle 9i中，查询字段is null时单索引失效，引起全表扫描。

• 解决方法：SQL语法中使用NULL会有很多麻烦，最好索引列都是NOT NULL的；对于is null，可以建立组合索引，nvl(字段,0)，对表和索引analyse后，is null查询时可以重新启用索引查找,但是效率还不是值得肯定；is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。

---

1.3 不等于操作符（<>、!=）

• 原因：SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引

• 解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描。
  例如，把column<>’aaa’，改成column<’aaa’ or column>’aaa’，就可以使用索引了。

---

1.4 or

• 原因：where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。
  例如：where A1 or B2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描。

---

2 如何定位低效 SQL？

• ① 慢查询日志定位（执行结束后查看）
  设置超时时间查询慢SQL，explain查看执行计划，针对性优化，可以通过慢查询日志定位那些已经执行完毕的 SQL 语句，慢查询日志在查询结束以后才记录。

  1.查看数据库服务慢查询日志是否开启
  sql show variables like 'slow_query_log'; on:开启 off:关闭
  2. 开启慢查询

  sql set global slow_query_log=on;
  3. 设置没有开启索引的SQL语句记录到慢查询日志
  sql set global log_queries_not_using_indexes=on;
  4. 设置超过多久时间的sql记录到慢查询日志
  sql show variables like 'long_query_time'; set long_query_time=0.5;//设置慢查询阈值
  5. 查看慢查询日志存储位置
  sql show variables like 'slow_query_log_file'; set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log';//设置路径
  6. 定位慢SQL之后，常常用到explain命令查看这些SQL语句的执行计划，是否使用了索引、有没有做全表扫描等

• ② SHOW PROCESSLIST 查询（执行时查看）
  所以在应用反应执行效率出现问题的时候查询慢查询日志不能定位问题，此时可以使用 SHOW PROCESSLIST 命令查看当前 MySQL 正在进行的线程，包括线程的状态、是否锁表等，可以实时查看 SQL 的执行情况，同时对一些锁表操作进行优化。找到执行效率低的 SQL 语句后，就可以通过 SHOW PROFILE、EXPLAIN 或 trace 等丰富来继续优化语句。

---

3 优化工具

---

3.1 SHOW PROFILE——分析 SQL 语句性能消耗

通过 SHOW PROFILE 可以分析 SQL 语句性能消耗，例如查询到 SQL 会执行多少时间，并显示 CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY N 分别查询 id 为 N 的 SQL 语句的 CPU、内存以及 IO 的消耗情况。

---

3.2 trace——进一步获取优化器是是如何选择执行计划的

从 MySQL5.6 开始，可以通过 trace 文件进一步获取优化器是是如何选择执行计划的，在使用时需要先打开设置，然后执行一次 SQL，最后查看 information_schema.optimizer_trace 表而都内容，该表为联合i表，只能在当前会话进行查询，每次查询后返回的都是最近一次执行的 SQL 语句。

---

3.3 EXPLAIN 的字段、含义——查看 SQL 语句的执行计划

执行计划是 SQL 调优的一个重要依据，可以通过 EXPLAIN 命令查看 SQL 语句的执行计划，如果作用在表上，那么该命令相当于 DESC。EXPLAIN 的指标及含义如下：

指标名	含义
id	表示 SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id 一样时，执行顺序从上往下。
select_type	表示查询中每个 SELECT 子句的类型，例如 SIMPLE 表示不包含子查询、表连接或其他复杂语法的简单查询，PRIMARY 表示复杂查询的最外层查询，SUBQUERY 表示在 SELECT 或 WHERE 列表中包含了子查询。
type	表示访问类型，性能由差到好为：ALL 全表扫描、index 索引全扫描、range 索引范围扫描、ref 返回匹配某个单独值得所有行，常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找，也经常出现在 join 操作中、eq_ref 唯一性索引扫描，对于每个索引键只有一条记录与之匹配、const 当 MySQL 对查询某部分进行优化，并转为一个常量时，使用这些访问类型，例如将主键或唯一索引置于 WHERE 列表就能将该查询转为一个 const、system 表中只有一行数据或空表，只能用于 MyISAM 和 Memory 表、NULL 执行时不用访问表或索引就能得到结果。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是consts 最好。
possible_keys	表示查询时可能用到的索引，但不一定使用。列出大量可能索引时意味着备选索引数量太多了。
key	显示 MySQL 在查询时实际使用的索引，如果没有使用则显示为 NULL。
key_len	表示使用到索引字段的长度，可通过该列计算查询中使用的索引的长度，对于确认索引有效性以及多列索引中用到的列数目很重要。
ref	表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows	表示 MySQL 根据表统计信息及索引选用情况，估算找到所需记录所需要读取的行数。
Extra	表示额外信息，例如 Using temporary 表示需要使用临时表存储结果集，常见于排序和分组查询。Using filesort 表示无法利用索引完成的文件排序，这是 ORDER BY 的结果，可以通过合适的索引改进性能。Using index 表示只需要使用索引就可以满足查询表得要求，说明表正在使用覆盖索引。

---

4 【SQL优化——尽量避免】

---

4.1 尽量避免全表扫描

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

---

4.1.1 对字段进行 null 值判断（ where 子句）

select id from t where num is null

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

select id from t where num=0    

---

4.1.2 使用 != 或 <> 操作符（ where 子句中）

否则将引擎放弃使用索引而进行全表扫描

---

4.1.3 使用 or 操作符（ where 子句中）

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

可以这样查询：

select id from t where num=10    
union all --UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
select id from t where num=20    

可以使用索引的情况必须所有的or条件都必须是独立索引。

---

4.1.4 使用 in / not in 操作符（ where 子句中）

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

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

  改为

  select id from t where num between 1 and 3    
  

• 例2：很多时候用 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
  )    
  

---

4.1.5 使用 like ‘%…%’ 全模糊查询（ where 子句中）

select id from t where name like '%abc%'    

---

4.1.6 对字段进行运算操作（ where 子句中）

这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100    

应改为:

select id from t where num=100*2    

---

4.1.7 对字段进行函数操作（ where 子句中）

这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id    

应改为:

select id from t where name like 'abc%'    

---

4.2 尽量避免无意义查询

• 例1：如需要生成一个空表结构：

  select col1,col2 into #t from t where 1=0  
  

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

  create table #t(...)    
  

• 例2：不带任何条件的count

  select count(*) from table；
  

  这样会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

---

4.3 尽量避免使用区分度低（含大量重复值）的索引

并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

---

4.4 尽量避免索引数量太多

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率

因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

---

4.5 尽量避免多次类似操作，尽量一次完成

例如，需要对一张表插入很多行数据时，应该尽量使用一次性插入多个值的 INSERT 语句，这种方式将缩减客户端与数据库之间的连接、关闭等消耗，效率比多条插入单个值的 INSERT 语句高。

也可以关闭事务的自动提交，在插入完数据后提交。当插入的数据是按主键的顺序插入时，效率更高。

---

4.6 尽量避免使用游标

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

---

4.7 尽量避免大事务操作

提高系统并发能力

---

4.8 尽量避免向客户端返回大数据量

若数据量过大，应该考虑相应需求是否合理

---

5 【SQL优化——尽量使用】

---

5.1 建表时，尽量使用小的数字型字段（减少字符型）

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

更小的数据类型通常也更快，因为它们占用更少的磁盘、内存和 CPU 缓存。

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

---

5.2 建表时，尽可能的使用 varchar 代替 char

因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

---

5.3 查询时，尽量使用关联查询提高效率（避免子查询）

在 MySQL 5.5 及以下版本尽量避免子查询，可以用关联查询代替，因为执行器会先执行外部的 SQL 再执行内部的 SQL。

---

子查询：即查询中又嵌套的查询

• 优点：比较灵活，不需要两个表有关联字段
• 缺点：效率低，会多次遍历所有的数据（视你的子查询的层次而定）

---

关联查询：即表连接

• 优点：效率高（只会遍历一次）
• 缺点：要求两个表必须有字段关联

---

6 【SQL优化——其他策略】

---

6.1 临时表相关

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

---

6.1.1 避免频繁创建和删除临时表

以减少系统表资源的消耗。

---

6.1.2 慎用临时表

临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

---

6.1.3 用 select into 代替 create table

在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

---

6.1.4 存储过程的最后务必将所有的临时表显式删除

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

---

6.1.5 优化 UNION 查询

MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要服务器消除重复的行，否则一定要使用 UNION ALL。

如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。

---

6.2 “字段写全写具体”原则

---

6.2.1 🚩组合索引的order排序中，涉及字段写全

组合索引的排序应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。
例如：

create index skip1 on emp5(job,empno，date); 
select job，empno from emp5 
where job=’manager’and empno=’10’ 
order by job,empno,date desc;

实际上只是查询出符合job=’manager’and empno=’10’条件的记录并按date降序排列，但是如果写成order by date desc性能较差。

---

6.2.2 Update 字段写具体

如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

---

6.2.3 sel 返回字段写具体

任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

---

6.3 多张大表的连接查询 先分页再JOIN

对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

---

6.4 复合索引的第一个字段需作为条件

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引， 否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。这也叫索引的最左原则。

---

6.5 优化 GROUP BY / ORDER BY

如果没有通过 ORDER BY 子句显式指定要排序的列，当查询使用 GROUP BY 时，结果会自动按照分组的字段进行排序，如果不关心结果集的顺序，可以使用 ORDER BY NULL 禁止排序

确保任何 GROUP BY 和 ORDER BY 的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程。

---

6.6 优化 LIMIT 分页

在偏移量非常大的时候，需要查询很多条数据再舍弃，这样的代价非常高。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

• 最简单的办法：是尽可能地使用覆盖索引扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。

索引覆盖扫描：当索引中的列包含所有查询中要使用的列的时候，就会用到覆盖索引，效率比较高。因为尽量使select后面的字段是where中的索引字段

• 还有一种方法：是从上一次取数据的位置开始扫描，这样就可以避免使用 OFFSET。其他优化方法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。

---

6.7 善用自定义变量

在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用。用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量。

例如可以使用变量来避免重复查询刚刚更新过的数据、统计更新和插入的数量等。

---