索引优化分析 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、基本语法

#创建
create [unique]index indexName on mytable(columnname(length)); #unique 表示唯一字段
alter mytable add [unique]index[indexName] on (columnname(length))
#删除
drop index [indexName] on mytable;
#查看
show index from table_name\G
#使用alter 命令

　　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 常见瓶颈

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

3.性能分析

Explain：

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

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

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

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

怎么玩：

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

字段名解释：

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操作，在找到第一匹配的元组即停止找同样值的操作。了解

热身case：

案例：

4.索引优化

1. 索引分析

单表：

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

俩表： index 时

建表：

　　　　　　　　　　。。。。。

案例：

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

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

三表：

建表：

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

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