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_VALUE

     from v$sqlarea sa

     left join all_users u

      on sa.PARSING_USER_ID = u.user_id

     where sa.EXECUTIONS > 0

     order 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

mysql索引【type列】		oracle索引【operation列】
system	只有一条记录	index unique scan	主键扫描
const	主键+唯一【返回一行】	index range scan	索引范围扫描
eq ref	唯一索引	index skip scan	索引跳跃扫描
ref	联合索引	index fast full scan	索引快速扫描
range	主键或者索引，进行范围查询	index full scan	索引全扫
index	遍历索引树，查索引全部数据	table access ful	全表查询
all	全表查询

索引跳跃扫描：不满足左前缀匹配原则时，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秒