本文介绍了数据库查询的索引优化方法，依次介绍了慢查询语句定位方法、索引设计与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
| 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秒
