索引优化分析 2

1、性能下降SQL慢 执行时间长 等待时间长

　　1.查询语句写的烂

　　2.索引失效

　　　　单值索引

　　　　复合索引

　　3.关联查询太多 join （设计缺陷或不得已的需求）

　　4.服务器调优即各个参数的设置（缓冲、线程等）

2、常见通用的join查询

　　1.SQL执行顺序

　　　　手写

　　　　机读

　　　　总结

　　2.join 7种方法

1/inner join 
select <select_list>
from tablea a 
inner join tableb b 
on a.key = b.key;

2/left join 
select <select_list>
from tablea a 
left join tableb b 
on a.key = b.key; 

3/right join 
select <select_list>
from tablea a 
right join tableb b 
on a.key = b.key 

4/左外join
select <select_list>
from tablea a 
left join tableb b 
on a.key = b.key 
where b.key is null; 

5/右外join
select <select_list>
from tablea a 
right join tableb b 
on a.key = b.key 
where a.key is null; 

6/full outer join 
select <select_list>
from tablea a 
full outer join tableb b 
on a.key = b.key 

7/满外join
select <select_list>
from tablea a 
full outer join tableb b 
where a.key is null 
or b.key is null

3.索引 简介

　　1.是什么

　　　　mysql官方对索引的定义为：索引（index）是帮助mysql高效获取数据的数据结构。 可以得到索引的本质：索引是数据结构。

　　　　1、可以理解为 “排好序的快速查找数据结构”

　　　　　　1. 详解（重要）　　

　　　　　　2. 结论

 　　　　　　　　数据本身之外，数据库还维护着一个满足特定查找的数据结构，这些数据结构一某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法， 这种数据结构就是索引。

　　　2、一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上， 

　　2.优势

　　　　类似图书馆，提高数据检索效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本 和 CPU的消耗

　　3.劣势

　　4.mysql 索引分类

　　　　1、单值索引

　　　　　　即一个索引只包含单个列，一个表可以有多个单列索引　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　2、唯一索引

　　　　　　索引列的值必须唯一，但允许有空值 。

　　　　3、复合索引

　　　　　　即一个索引包含多个列

　　　　4、基本语法

　　5.mysql索引结构  

　　　　1、BTree索引

　　　　　　检索原理　

　　　　2、Hash索引 

　　　　3、full_text 全文索引

　　　　4、R_Tree索引

　　6那些情况需要创建索引

　　7，那些情况不需要创建索引

　　　　1、表太少

　　　　2、经常增删改的表：[ 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT, UPDATE, DELETE。 因为更新表时，mysql不仅要保存数据，还要保存一下索引文件。]

　　　　3、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。【注：如果某个列包含许多重复的内容，为它建立索引就没有太大的实际效果。】　

1.mysql query optimizer

2.Mysql 常见瓶颈

1. cpu：cpu在饱和的时候（一般发生在数据装入内存，或从内存中读取数据时）
2. IO：磁盘I\O瓶颈发生（装入数据大于内存容量时）
3. 服务器硬件的性能瓶颈：top,tree,iotat,vmstat 查看系统性能状态

3.性能分析

Explain：

1. 是什么（查看执行计划）：

1. 使用Explain关键字模拟优化器执行sql查询语句（分析查询语句，或是表结构的性能瓶颈），mysql是如何执行sql语句的顺序
2. 官网分析图： http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

2. 能干嘛（下面解答，重复阅读本段内容，会有不同体会）：

1. 表的读取顺序    ？
2. 数据读取操作的读取类型    ？
3. 某些索引可以使用    ？
4. 某些索引被实际使用    ？
5. 表之间的引用    ？
6. 每张表有多少行被优化器所查询    ？

3. 怎么玩：

1. Explain + sql语句
2. 执行计划包含的信息 -》 

    

    

4. 字段名解释：

• id：重点

• 描述：select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
• 三种情况：

• id相同，执行顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行。
• id相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。 衍生 =derived

• select_type：

• 描述：查询的类型，主要用于区别 普通查询、联合查询、子查询等  
• 值(作用)：

• simple：简单的select 查询，查询中不包含子查询或 UNION
• PRIMARY：查询中若包含任何复杂的子查询，最外层查询
• SUBQUERY：在select 或 where 包含子查询
• DERIVED：

• 在 Form 列表中包含的子查询被标记为DERIVED（衍生）
• mysql会递归执行这些子查询，结果放置到临时表中。

• UNION ：

• 若第二个Select 出现在UNION 之后，则被标记为：UNION
• 若 UNION 包含在FROM 子句的子查询中，则被标记：DERIVED

• UNION RESULT：从UNION表中获取结果的select

• table ：显示这一行是某一张表的。
• type:重点

• 描述：访问类型
• 常用显示排序(最好->最坏)：system->const->ref->range->index->All
• 值：

• system:    

• 表只用一行记录(等于系统表)，这是const类型的特例，平时不会出现（可以忽略）

• const:  

• 表示通过索引一次找到，const用于比较primary key 或者unique索引。如果将主键至于where列表中，mysql就能将该查询转换成为一个常量。

• eq_ref:  

• 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键，或唯一索引的扫描。

• ref：

• 唯一性索引扫描，返回匹配某个单独只的所有行。
• 本质上也是一种索引访问，它返回所有匹配某个单独值的行，它可能会找到多个条件的行，所以他应该属于查找和扫描的混合体。

• range：

• 只检索给定范围的行，使用一个索引选择行。
• key列显示使用的索引列，一般就是where中出现between、< 、>、in等查询条件。
• 这种范围索引扫描比全表扫描好，因为他只需要开始与索引的某一个点，而到结束的另一个点，不需要全表扫描。

• index：

• Full Index Scan，index与All的区别为index只遍历索引树，通常比All快，因为索引文件通常比数据文件小。（虽然index和all都是读全表，index是从索引中获取，但是All是从磁盘中获取）。

• All:

• Full Table Scan,将全表以找到匹配的行（全表磁盘扫描，多一些IO的操作）。

• 全显示排序（最好->最坏）：system->const->eq_ref->ref-f>fulltext->ref_or_null->index_merge->unique_subquery->range->index->All
• 优化：一般来说，得保证查询达到range级别，最好达到ref

• key_len:

• 描述： 

• 表示索引中使用的字节数，可通过该列计算查询中的索引长度，长度越短越好。
• key_len 显示的值为索引字段最大的可能长度，并非实际使用长度，即key_len是根据表定义计算面得，不通过表内检索出的。

• 案例：

• 结论：条件越细，key_len越长。

• ref

• 描述：显示索引的某一列被使用。如果可能是一个常数，某些列或常量被用于查找索引列上的值。
• 案例：
• 

   

   

• rows 重点

• 描述：根据表统计信息及索引选用情况，大致估算出找到的记录所需要读取的行数。
• 案例：

• Extra 重点

• 描述：包含不适合在其他列中显示，但十分重要的其他信息。
• 值：

• Using fileshort :  重点

• mysql 会对数据库使用一个外部的索引排序，而不是按照表内的索引进行读取。
• mysql 中无法利用索引完成排序的操作成为"文件排序"。
• 案例：
• 

• 案例1：未正确使用索引排序（文件排序）
• 案例2：正确使用索引排序

• Using temporay  重点

• 描述：

• 用临时表保存临时结果，mysql 查询结果使用临时表。
• 常见使用 排序 order by 和 group by 分组查询。

• 案例：
• 

• 案例1：未正确使用索引排序，又使用 group by 或 order by 产生的结果，会严重拖慢数据库的效率。
• 案例2：正确使用索引排序

• Using index  重点

• 描述：

• 表示相应的操作中使用了覆盖索引（Covering index），避免访问，表的数据行，效率不错！
• 如果同时出现Using where 表明 索引备用类执行索引键值的查找
• 如果没有同事出现Using w here 表明 索引用来执行读取数据，而非查找动作。
• 覆盖索引（Covering index）又被称为：索引覆盖

• 描述：

• 理解方式 一：select 的数据链只用从索引中获取，不必读取数据行，mysql 利用索引返回 select 中的字段，而不必根据索引再次读取数据文件，换句话说 查询列 要被所创建的索引覆盖。
• 理解方式 二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一列的数据，因此它不必读取整行。毕竟索引的叶子节点，存储它们索引的数据，当能通过索引获取数据，就可以获取到所需要的数据。一个索引包含（或覆盖）满足查询结果的数据叫做覆盖索引。

• 注意：

• 使用覆盖索引，注意select 列表中只能取出所需要的列，不可使用select *
• 因为如果将所有的字段一起做索引，会导致索引文件过大，查询性能下降。

• 案例：
• 

• 案例 1：
• 案例 2：

• Using where : 表示使用 where 过滤
• Using join buffer : 表示 使用连接缓存  #如果join比较多，可以把join buffer 调大一点。
• impossible where :

• 描述：where 子句的值总是 false,不能用来获取任何元素。
• 案例：
• 

   

   

• 过滤字段重复

• select table optimized away： 了解

• 在没有Group By 子句的情况下，基于索引优化MIN/MAX的操作或者对于 MyISAM 存储引擎优化 count(*) 操作，不必等到执行间断进行计算，查询计算生成间断完成优化。

•  distinct：优化distinct操作，在找到第一匹配的元组即停止找同样值的操作。 了解

5. 热身case：

• 案例：

4.索引优化

• 1.  索引分析

• 单表：

• 建表SQL:
• 
• 案例(分析好后，才能有优化)：

• 俩表： index 时 

• 建表：

 　　　　　　　　　　。。。。。

• 案例：

　　　　　　　　索引优化(左连接索引添加到右表)：

　　　　　　　　索引优化（右连接索引添加到左表）

• 三表：

• 建表：

• 案例(和俩表连接的结论类似)：

结论：
join语句的优化：
尽可能减少 join 语句中的NestedLoop循环总数；"永远记住小结果集驱动大结果集"。
尽可能优化 NestedLoop 的内层循环；
保证Join语句中被驱动表驱动表上的 join 字段被索引
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬joinBuffer的设置。

• 2.  索引失效（避免）

• 建表 SQL：

• 案例（索引失效）
• 

• 全值匹配我最爱

• 案例：
• 

• 最佳左前缀法则

• 案例：
• 描述：索引多列，遵守最佳左前缀法则（查询从索引最左前列开始，并且不跳过中间的索引列）。
• 

• 不能在索引上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

• 案例：
•   

• 结论：使用计算会导致索引失效

• 存储索引不能使用索引中范围条件右边的列

• 案例：
• 

• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 操作

• 案例：

• 结论：尽量使用覆盖索引

• mysql 使用（!=或< >）的时候无法使用索引会导致全表扫描。

• 案例：
• 

• is null,is not null 无法使用索引。 

• 案例：

• like 以通配符开头（‘%abc……‘） mysql索引失效会变成全表扫描的操作。

• 案例：
• 案例 2 生产环境中(like 俩边+%) 

• 结论：

• 1. 使用like ‘aaa%‘ ,%加到右边
• 2.如果 like ‘%ff%‘,%加俩边，使用覆盖索引（分析覆盖索引的字段 ）

• 少用 or ,用or 连接时索引会失效
• 口诀:

• 1.全值匹配我最爱，最左前缀要遵守。
• 2.带头大哥不能死，中间兄弟不能段。
• 3.索引列上少计算，范围之后全失效。
• 4.like 百分写最右，覆盖索引不写*。
• 5.不等空值还有or,索引失效要少用。
• 6.字符串引号不可丢，SQL高级也不难。

　　　　　　　　小总结

• 面试题讲解：

• 题目SQL

• 定值、范围还是排序，一般 oder by 是给个范围
• group by 基本上都会进行排序，会有临时表的产生。[注：group by 表面是分组，但分组之前必排序]

• 3. 一般性建议：

• 对于单值索引，尽量选择针对当前 query 过滤性更好的索引。
• 在选择组合索引的时候，当前query中过滤最好的字段在索引字段顺序中，位置越靠前 [左]  越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
• 尽可能通过分析统计信息和调整query的写法来达到合适的索引。