mysql性能分析与查询优化

explain的使用（性能分析）

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

2、使用

Explain + SQL语句

作用：

表的读取顺序

哪些索引可以使用

数据读取操作的操作类型

哪些索引被实际使用

表之间的引用

每张表有多少行被物理查询

3、名词字段解释

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

三种情况：id相同，执行顺序由上至下；

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

                  id相同不同，同时存在

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

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

type：显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

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

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

key：实际使用的索引。如果为NULL，则没有使用索引

key_len：where后面的字段命中的索引长度

ref：显示索引的哪一列被使用了

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

Extra：包含不适合在其他列中显示但十分重要的额外信息

查询优化

1、批量数据脚本

1）创建函数

CREATE FUNCTION 函数名...

2) 创建存储过程

CREATE PROCEDURE  存储过程名...

3)调用存储过程

CALL 存储过程名...

注意：批量删除某个表上的所有索引，也要建立存储过程，并使用游标。

2、单表操作

索引失效：

1）全值匹配我最爱

2）最佳左前缀法则：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

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

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

5）mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

6）is not null 也无法使用索引,但是is null是可以使用索引的

7）like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

8）字符串不加单引号索引失效

3、关联查询操作（至少两个表）

1）关联查询的时候，前面的表是驱动表，后面的表是被驱动表，给被驱动表建立索引有效果。所以left join 时，选择小表作为驱动表，大表作为被驱动表。

2）保证被驱动表的join字段已经被索引

3）inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

4）子查询尽量不要放在被驱动表，有可能使用不到索引。

5）能够直接多表关联的尽量直接关联，不用子查询。

4、子查询优化

尽量不要使用not in  或者 not exists。

用left outer join  on  xxx is null 替代

5、排序分组优化

注意三点：

  #无过滤 不索引 

  #顺序错，必排序 

  #方向反 必排序 

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序，单路排序优于双路排序。

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。