SQL优化-综合举例

SQL优化有两大方向：
（基本上是我看《高性能SQL》第五章和第六章的内容结合生产上实际情况的理解）
一、建立合理的索引
首先明白我们常见的索引：
1、B-tree：
切记满足最左原则，只有从左边开始精确匹配，后面的索引才能继续使用，范围匹配后后续列将不在起作用，详见我之前写博客，讨论了很多关于B-tree,数据库与它的存储结构
适用：精确匹配
范围匹配
还可以排序，group by,distinct
2、哈希索引
只适用于精确匹配，
不能范围查询，就是说不能用>,<,<>等
不能排序
哈希索引主要是快，当冲突少的时候，然后单个精确查询的时候很快，但是不能排序，不能范围查询导致实际场景用的较少，
3、全文索引
主要是解决列字符串大时，建立的B-tree索引很大导致效果不理想，这个和solr的原理类似，只是分析数值内容，用字典的方式查询，用的较少
4、前缀索引
用列值的前几个值作为索引，所以选择的列值区分度很重要
select count( distinct(left(colum,n)) )/count(*) from xxx
所有非重复的索引列除以所有实际列的比值站到97即可
5、聚簇索引
innodb独有，一个表只能有一个：就是说这个索引不但含有本身的列值，还含有该行的内容
id列的索引既是如此
6、覆盖索引
如果查询的字段刚好全部在索引列上，减少二次回表

针对SQL优化，从索引的角度，我们怎么优化呢？
1、索引不能进行计算 select * from xxx where id + 1 > 5;这是不对的
2、建立合理的联合索引：
a.合理的列顺序：区分度高的在前面，范围查询的在最后，联合索引一般不进行两个列的范围查询
b.order by, group by, distinct最好走联合索引，记住最左匹配原则
3、不要建立重复索引，冗余索引
重复就是 unique(id),primaryKey(id)
冗余索引（A,id）(A),在我的生产实际中碰到了erp_taobao_so_header中，domain_id因为冗余了，一点也不起作用

二、语句优化
优化原则
1、高并发下的SQL优化价值远远大于低并发的SQL语句优化
2、看的懂explain，我之前写的文章，我看过很多博客，看过高性能ＳＱＬ，和ｍｙｓｑｌ性能优化两本书综合起来解读的，应该比较全面了http://blog.csdn.net/zhangyufeijiangxi/article/details/75098494

优化思想：
1、尽量走索引
2、小结果集驱动大结果集
3、索引要满足最左匹配原则
4、不要取不想要的数据：
含select * 和limit
limit在优化中，可以先limit行，再排序，最后过滤
还可以，先查id，再排序，最后取数据行内容
5、切分查询：
数据量多的时候，不要一股脑的想一次把数据全部取出来

优化的具体方向：
１、count(1)、count(*),count(id)
讨论的前提是无where查询条件
count(id),会过滤那些列值为NULL的行数，因此当列为非NULL的时候，就是行数，但是有NULL值的时候，就代表不是行数了
count(*),行数，会选取索引中非NULL值的索引，并且该索引的长度最短来计算行数（innodb）
但是针对(MyIAM)，infomation数据库会直接读取行数值
如果有查询条件where，则按照explain进行优化

2、order by, group by, distinct
排序尽量走索引
不能跨表排序，因为跨表肯定走不了索引
一定要符合最左匹配原则

3、join
a.没必要不要join，拆分查询数据
b.关联顺序直接影响性能，用小结果集驱动大结果集，看explain，如果和想象中的不一样，强制关联表顺序
stranght_join,这个 in的用法一致，看下面给的in,和exists优化
c.阿里开发手册建议不超过3个表join

4、union
a、union默认采用distinct，需要排序，浪费时间，如果确定取出所有数据，union all
b、如果数据不是要所有的，limit在这里很有用，如：
select* from a union select* from b limit 20;
改成：
（select* from a limit 20 ）union （select* from b limit 20） limit 20;
联合时消耗大量的内存，尽量不要取出不需要的数据

5、子查询优化：
A in B，A索引，B全表扫描，适合A大表，B小表的情况
A exists B，A全表扫描，B索引，适合A小表，B大表的情况
A not in B ,A,B 都走全表扫描，尽量不能这么写
A not exists B, AB 都走索引，因此not exists优于 not in

6、limit
a、数据量大时，先查id，再查数据内容
b、限制不要的数据，不管是中查询中排序，关联，或者查询出来的数据不需要那么多

实际生产过程中碰到的问题：

1、拆分查询
SELECT *
FROM XXX AS x
INNER JOIN(
SELECT id
FROM XXX
ORDER BY id DESC
LIMIT M,N
) AS t
USING(id)
用先查id。再查数据行。在生产中，我们做了一个job，需要导出100万条数据，我们做的优化，就是先查100万条id，然后分批次查询数据行

2、建立合理的索引
select
o.*
FROM
erp_taobao_so_header o
FORCE INDEX(i_tbsoh_domain_ct)
WHERE
1 = 1
AND o.domain_id = 2291
AND o.deleteVersion = 0
AND o.receiverProvince = ‘山西省’
AND (1 = 1)
ORDER BY
id DESC
LIMIT 0,
500;
该查询优化器会走id索引，但是我们没有where条件关于id，所以做了全索引扫描，我们发现domain区分度很高，但是却没有走这个索引
存在的问题：冗余索引，联合索引建立错误
改写成 order by createTime,建立（domain,createTime的联合索引）