SQL语句优化——结合书籍论坛小结

刚开始碰到一个旧系统里面的一条SQL语句，查询要200多秒，一看就是left join了很多表，并且索引使用有问题，条件连表之后再判断等问题。通过查看了mysql高性能和mysql语句优化书籍和论坛做一下小结并且记录一下这些知识点。

1.在SQL语句前面加上explain 运行就可以查看到相关属性参数

对于这些参数我把重要的提及一下，其他的可以百度一下explain对应的属性。

这是我下面没有优化的：

t

table里面有derived就是说明有派生表，尽量少使用派生表。

type 重要的项,显示连接使用的类型,按最 优到最差的类型排序    

 possible_keys:

possible_keys

指出 MySQL 能在该表中使用哪些索引有助于 查询。如果为空,说明没有可用的索引

key: 

key	MySQL 实际从 possible_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引

key_len: 

key_len

使用的索引的长度。在不损失精确性的情况 下,长度越短越好。

ref：

ref	显示索引的哪一列被使用了

rows:

rows	MYSQL 认为必须检查的用来返回请求数据的行数，如果值很大要考虑优化

extra重要两项说明记得优化（其他的项不做说明）
Using filesort    表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”
Using temporary    表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

 

查看了刚才的情况会发现有的有索引但是没有使用索引，为什么呢？下面几种情况一般不是使用索引的。

2、不使用索引情况（编写SQL语句时尽量避免使用一下情况）

1、查询谓词没有使用索引的主要边界,换句话说就是select *，可能会导致不走索引。

比如，你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引，但是优化器会认为需要一行行的扫描会更有效，这个时候，优化器可能会选择TABLE ACCESS FULL，但是如果换成了SELECT Y FROM T WHERE Y = XXX，优化器会直接去索引中找到Y的值，因为从B树中就可以找到相应的值。

2、单键值的b树索引列上存在null值，导致COUNT(*)不能走索引。

如果在B树索引中有一个空值，那么查询诸如SELECT COUNT(*) FROM T 的时候，因为HASHSET中不能存储空值的，所以优化器不会走索引，有两种方式可以让索引有效，一种是SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL或者把这个列的属性改为not null (不能为空)。

3、索引列上有函数运算，导致不走索引

如果在T表上有一个索引Y，但是你的查询语句是这样子SELECT * FROM T WHERE FUN(Y) = XXX。这个时候索引也不会被用到，因为你要查询的列中所有的行都需要被计算一遍，因此，如果要让这种sql语句的效率提高的话，在这个表上建立一个基于函数的索引，比如CREATE INDEX IDX FUNT ON T(FUN(Y));这种方式，等于Oracle会建立一个存储所有函数计算结果的值，再进行查询的时候就不需要进行计算了，因为很多函数存在不同返回值，因此必须标明这个函数是有固定返回值的。

4、隐式转换导致不走索引。

索引不适用于隐式转换的情况，比如你的SELECT * FROM T WHERE Y = 5 在Y上面有一个索引，但是Y列是VARCHAR2的，那么Oracle会将上面的5进行一个隐式的转换，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。

5、表的数据库小或者需要选择大部分数据，不走索引

在Oracle的初始化参数中，有一个参数是一次读取的数据块的数目，比如你的表只有几个数据块大小，而且可以被Oracle一次性抓取，那么就没有使用索引的必要了，因为抓取索引还需要去根据rowid从数据块中获取相应的元素值，因此在表特别小的情况下，索引没有用到是情理当中的事情。

6、cbo优化器下统计信息不准确，导致不走索引

很长时间没有做表分析，或者重新收集表状态信息了，在数据字典中，表的统计信息是不准确的，这个情况下，可能会使用错误的索引，这个效率可能也是比较低的。

7、！=或者<>(不等于），可能导致不走索引，也可能走 INDEX FAST FULL SCAN

例如select id  from test where id<>100

8、表字段的属性导致不走索引

字符型的索引列会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式，

由于字符型和数值型的在insert的时候排序不同，字符类型导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说，就是按照数字类型插入（1..3200000），按字符类型（'1'...'32000000'）t排序，在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。

9、避免在where子句中对字段进行null值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

有一个前模糊或者全模糊查询它一般不走普通索引，但是mysql里面有个全文索引，使用全文索引可以解决这个问题，

但是全文索引和普通索引使用是有区别的哦。

其实有时候联表你想强制使用索引，怎么做呢？其实可以使用强制使用索引字段，必要时可以使用force index来强制查询走某个索引

 

3、下面讲解一下要避免的坑：

1、尽量将or 转换为 union all

不使用索引：

select * from user where name=’a’ or age=’20’

使用索引：

select * from user where name=’a’ union all select * from user where age=’20’

2、SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。

3、SELECT语句务必指明字段名称

SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

4、当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型

5、如果排序字段没有用到索引，就尽量少排序

6、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

7、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

8、不使用ORDER BY RAND()

select id from `dynamic` order by rand() limit 1000;

上面的SQL语句，可优化为：

select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;

9、区分in和exists、not in和not exists

select * from 表A where id in (select id from 表B)

上面SQL语句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

原SQL语句：

select colname … from table1 a where a.id not in (select b.id from table2 b)

高效的SQL语句：

select colname … from table1  a Left join table2 b  on where a.id = b.id where b.id is null

取出的结果集如下图表示，A表不在B表中的数据：

10、使用合理的分页方式以提高分页的效

select id,name from product limit 866613, 20

使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：

select id,name from product where id> 866612 limit 20

11、分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。扫描的行数成百万级以上的时候就可以使用分段查询

12尽量避免使用is null或is not null

不使用索引：

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

使用索引：

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >0;

等等。。。。。。。。

其实多看书，当自己编写的时候要要看看是否使用到了索引，避免不必要的坑，刚开始数据量小可能没什么问题，但是数据量大的话有的就要执行很久。这样对数据库和系统的压力都大哦。

 

《………………………………………………菜鸟起飞中，请各位走过路过的多多指教……………………………………》