Sql查询优化--索引设计与sql优化（包含慢查询定位+explain解释计划+左匹配原则+索引失效）

        本文介绍了数据库查询的索引优化方法，依次介绍了慢查询语句定位方法、索引设计与sql语句优化方法，并介绍了左匹配原则和索引失效的场景，最后介绍了explain执行计划要怎么看以调整检验索引设计是否生效和效率情况，创新介绍了如何以业务板块及大表为切入点，系统性设计索引，用最少的索引覆盖最多的查询语句。在实践中将整个业务板块的多个大表查询和复杂查询sql优化从40s以上优化到3s以内，保障系统正常运行。

优化前：43秒

优化后：4秒

一、慢查询定位

在排除前端请求超时时长设置和nginx负载以后，可以慢查询定位找到查询耗时长的sql语句。

1、Oracle 慢查询耗时定位

select *from (select sa.SQL_TEXT "执行 SQL",sa.EXECUTIONS "执行次数",round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",sa.COMMAND_TYPE,sa.PARSING_USER_ID "用户 ID",u.username "用户名",sa.HASH_VALUEfrom v$sqlarea saleft join all_users uon sa.PARSING_USER_ID = u.user_idwhere sa.EXECUTIONS > 0order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)where rownum <= 50;

2、mysql 慢查询耗时定位

查询是否开启慢查询日志：show variables like ‘slow_query_log’;

• 开启慢查询sql：set global slow_query_log = 1/on;
• 关闭慢查询sql：set global slow_query_log = 0/off;

二、索引设计方法

1、单索引和联合索引

      联合索引的优先级大于普通索引。索引相当于路线，即使一张表有多个索引，一个查询（子查询）只能走一个索引，走了其中一个索引就相当于走了一条路，不会同时走多个索引。

2、最大区分度

根据业务，where、group by、order等最常用到的关键词，能最大程度区分数据，记录中最具代表性质的字段。如status之类的字段则不便于建立索引。

3、遵循左匹配原则

索引index(a,b,c);   

查询语句where b=1 and c =3【不走索引】

查询语句where a=1 and c =3【走部分索引，索引存在中断】

查询语句where b=1 and a=3【走部分索引，左匹配原则和索引列的顺序有关，和查询列的顺序无关，因sql有查询优化器，可以调整sql执行方法】

查询语句where b=1 and a=3 and c=9 【走索引】

4、根据业务设计（**实战重点**）

（1）一张表的索引并非越多越好，索引过多对空间造成浪费，在新增和删除、修改时浪费效率。一张表建议不超过5个索引。

（2）首先根据一块业务找到涉及到的表，找到关联left join的关键字。

（3）一块业务流程中的用到的所有复杂查询语句、常用查询语句、大表查询语句、慢查询语句，提取出来分析（也可用慢查询分析）。

（4）关注where、order、group by、select、left join、实际数据记录重要性质区分的字段。各个sql语句的字段按字典序进行排序，关注每个sql语句涉及字段的交集。

（5）以交集字段建立联合索引，实现尽可能少的建立索引，尽可能多的sql语句能走索引查询。

Eg:

Sql_1 语句：select a from table_1 where a=1 and b=1 and c=2 and d=3;

Sql_2 语句：select a from table_1 where b=1 and c=1;

Sql_3 语句：select a from table_1 group by c,d;

此时3个语句的关键字中此时可以依据顺序建立索引index(c,a,b),注意索引顺序，三个语句都可以走联合索引。

其他复杂子查询或连接查询也依次法分析。

（6）根据Sql优化方法和explain分析，调整索引（见目录三、六）

三、Sql优化方法

1、小表驱动大表

含有子查询的语句，in 用于子查询范围小于外查询，exist则反过来

2、union all 代替 union

减少过滤

3、Join代替子查询

减少回表

4、Where条件代替having

5、Select 字段值 代替 select *

四、左匹配原则

创建联合索引时，首先会对最左边字段排序，也就是第一个字段，然后再在保证第一个字段有序的情况下，再排序第二个字段，以此类推。

索引index(a,b,c);   

查询语句where b=1 and c =3【不走索引】

查询语句where a=1 and c =3【走部分索引，索引存在中断】

查询语句where b=1 and a=3【走部分索引，左匹配原则和索引列的顺序有关，和查询列的顺序无关，因sql有查询优化器，可以调整sql执行方法】

查询语句where b=1 and a=3 and c=9 【走索引】

五、索引失效

1、索引列存在函数运算或类型转换

>、<、between中断索引，只能匹配部分

2、like ‘%dkla0’        %出现在最左端会失效

3、Or两边有其中一边没有走索引l

4、不满足左匹配原则

六、EXPLAIN解释计划执行含义

1、Mysql中

关注type列:

system > const > eq_ref > ref > range > index > all

2、Oracle中

关注operation列:

index unique scan>index range scan>index skip scan>index fast full scan>index full scan>table access ful

索引跳跃扫描：不满足左前缀匹配原则时，sql优化器

索引快速扫描：无序

索引全扫：有序（order by索引）

七、索引操作语句

1、新增

alter table table_name ADD INDEX [index_name] (index_col_name,...)

2、删除

DROP INDEX index_name ON tbl_name;

八、效果

其他同业务查询的相关语句也缩减到3s以内

优化前：43秒

优化后：4秒