02-mysql性能分析

一、性能下降、sql慢、执行时间长、等待时间长
1.1、查询数据过多-->拆分sql，条件过滤尽量少。
1.2、关联太多表，太多的jion。 -->jion原理。用A表中的每一条数据扫描B表中的所有数据。
1.3、没有利用到索引。--->单值/复合
　　多条件的时候，可以创建共同索引(混合索引)。有些情况下就算有索引，具体执行时也不会被使用。
1.4、服务器调优及各个参数设置（缓冲、线程数等）

二、常见的Jion查询
2.1、sql的执行顺序

select distinct 
    <select_list>
from
    <lect_table> <jion_type>
　　jion <right_table> on <jion_condition>
where 
    <where_conddition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit
    <limit_number>

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。

from <left_table> on <jion_condition>
<jiontype> jion <right_table>
where <where_condition>
group by <group_by_condition>
having <having condition>
select
distinct <select_list>
order by <order_by_condition>
limit <limit_number>

2.2、jion的七种情况

select <select_list> from tableA A inner jion tableB B on A.key=B.key;

select <select_list> from tableA A left jion tableB B on A.key=B.key;

select <select_list> from tableA A right jion tableB B on A.key=B.key;

select <select_list> from tableA A left jion tableB B on A.key=B.key where B.key is null;

select <select_list> from tableA A right jion tableB B on A.key=B.key where B.key is null;

select <select_list> from tableA A full outer jion tableB B on A.key=B.key;

select <select_list> from tableA A full outer jion tableB B on A.key=B.key where A.key is null or B.key is null;

三、索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，
复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

优势：
　　降低数据库的IO成本、降低CPU消耗
劣势：
　　实际上索引也是一张表，该表保存率了主键和索引字段，并指向实体表的记录，所以索引列也是要占空间的。
　　虽然索引可以提高查询速度，但是同时会降低更新修改表的速度。-->因为更新表的时候，mysql不仅要保存数据，还要保存每次更新添加的索引列的字段。
　　
mysql 索引结构
　　待研究

mysql 索引分类
主键索引：建立主键后数据库会自动建立索引，innoDB为聚簇索引。
单值索引：即一个索引值包含一个列。一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但是允许有空值。
复合索引：即一个索引中包含多个列。

在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建立索引）
当表的行数远大于索引列的数目时可以使用复合索引。

 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

什么情况下需要建立索引
1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该创建索引。
3、查询中与其他表关联的字段，外键关系建立索引。
4、单键/组合索引的选择问题。-->在高并发下倾向于创建组合索引。
5、查询中排序的字段。
6、查询中统计或分组字段。

什么情况下不要创建索引
1、表记录太少
2、经常增删改的表。-->提高了查询速度，同时降低了更新表的速度，因为更新表的时候mysql不仅要保存数据，还要维护索引文件。
3、where条件里用不到的字段不创建索引。
4、数据重复且分布平均的表字段。
　　因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

四、性能分析
mysql常见瓶颈
CPU：SQL中对大量数据进行比较、关联、排序、分组。最大压力在于比较。
IO：实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。
锁：不适宜的锁的设置，导致线程阻塞，性能下降。
　　死锁，线程之间交叉调用资源，导致死锁，程序卡住。
服务器硬件的性能瓶颈：top,free, iostat和vmstat来查看系统的性能状态

Explain
什么是(查看执行计划)
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

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

Explain + SQL语句
执行计划包含的信息


---各个字段的含义---
id：
select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下  
先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id 。 而  t2.id 的结果建立在 t2.id=t3.id 的基础之上。

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行
 
衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引）

select_type：
查询的类型，主要是用于区别：普通查询、联合查询、子查询等的复杂查询

1、SIMPLE：
　　简单的 select 查询,查询中不包含子查询或者UNION
2、PRIMARY：
　　查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
3、DERIVED：
　　在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里。
　　select * from (select t1.content from t1) a;
4、SUBQUERY：
　　在SELECT或WHERE列表中包含了子查询
5、DEPENDENT SUBQUERY：
　　在SELECT或WHERE列表中包含了子查询,子查询基于外层

6、UNCACHEABLE SUBQUREY：
　　无法被缓存的子查询
7、UNION：
　　若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

8、UNION RESULT：
　　从UNION表获取结果的SELECT


table：
显示这一行的数据是关于哪张表的

type：

访问类型排序：
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL

1、system：
　　表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，可以忽略不计。
2、constant：
　　表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快
　　如将主键置于where列表中，MySQL就能将该查询转换为一个常量

3、eq_ref：
　　唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

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


4、range：
　　只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
　　一般就是在你的where语句中出现了between、<、>、in等的查询
　　这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。


5、index：
　　Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
　　（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

6、all：
　　Full Table Scan，将遍历全表以找到匹配的行。

7、index_merge：
　　在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中。

8、ref_or_null：
　　对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

9、index_subquery：
　　利用索引来关联子查询，不再全表扫描。



10、unique_subquery ：
　　该联接类型类似于index_subquery。 子查询中的唯一索引。

　　一般来说，得保证查询至少达到range 级别，最好是能达到 ref。

posibble_keys:
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：
实际使用的索引。如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key_len:
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 

怎么计算？

总结一下：char(30) utf8 --> key_len = 30*3 +1  表示 utf8 格式需要  *3 (跟数据类型有关)   
                 允许为 NULL  +1  ，不允许 +0
                 动态类型 +2  (动态类型包括 : varchar , detail text() 截取字符串)

key_len字段能够帮你检查是否充分的利用上了索引。

第一组：key_len=deptno(int)+null + ename(varchar(20)*3+动态 =4+1+20*3+2= 67
第二组：key_len=deptno(int)+null=4+1=5 

ref：
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。


rows：
rows列显示MySQL认为它执行查询时必须检查的行数。越少越好。

Extra：
包含不适合在其他列中显示但十分重要的额外信息。
1、Using filesort ：
　　说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
　　MySQL中无法利用索引完成的排序操作称为“文件排序”

给ename 加上索引后：

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
分情况：当通过前面的查询语句 筛选大部分条件后，只剩下很少的数据。using filesort 性能影响不大。需要综合考虑

2、Using temporary 
　　使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

create index idx_deptno_ename on emp(deptno,ename) 后解决
优化前存在的 using temporary 和 using filesort 不在，性能发生明显变化：

3、USING index
　　表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
　　如果同时出现using where，表明索引被用来执行索引键值的查找;
　　如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
关于索引覆盖（Covering Index）

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。
①一个索引 ②包含了(或覆盖了)[select子句]与查询条件[Where子句]中 ③所有需要的字段就叫做覆盖索引。
 
上句红字理解：
         select id , name from t_xxx where age=18;
         有一个组合索引  idx_id_name_age_xxx 包含了(覆盖了)，id,name,age三个字段。查询时直接将建立了索引的列读取出来了，而不需要去查找所在行的其他数据。所以很高效。
         (个人认为：在数据量较大，固定字段查询情况多时可以使用这种方法。)
 
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

4、Using where

5、using join buffer
　　使用了连接缓存：

出现在当两个连接时
驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。
给驱动表建立索引可解决此问题。且 type 将改变成 ref。

6、impossible where
　　where子句的值总是false，不能用来获取任何元组。

7、select tables optimized away
　　在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
　　对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
　　查询执行计划生成的阶段即完成优化。
在innodb 中：

在myisam 中：

myisam 中会维护 总行数 (还有其他参数)这个参数，所以在执行查询时不会进行全表扫描。而是直接读取这个数。
但会对增删产生一定的影响。根据业务情况决定谁好谁坏
innodb 中没有这个机制。