Mysql慢查询

1.索引的一些概念

1.索引

排好序的快速查找的数据结构（我们平时说的索引，如果没有特别指明，都是指B树，其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认使用的都是B+树索引，除B+树这种类型的索引外还有哈希索引等）

• 优缺点---->何种情况建索引

1. 优点
   查找 ：提高数据检索效率，降低IO成本。
   排序：通过索引对数据进行排序，降低排序成本，降低cpu消耗
2. 缺点
   实际上索引也是一张表，该表保存了主键与索引字段，并指向索引的记录，所以索引列也需要占空间。
   更新表时（insert、update、delete）不仅要保存数据还要更新保存索引文件新添加的索引列。

• 索引分类

1. 单值索引（单列索引）：一个索引只包含单个列，一个表中可以有多个单列索引。
2. 唯一索引：索引列必须唯一，但可以允许有空值
3. 复合索引：一个索引包含多个列

2.mysql索引结构

1. BTree索引
2. Hash索引
3. full-text全文检索
4. R-Tree索引

3.适合创建索引和不适合创建索引的情况

1. 哪些情况要建索引

1. 主键自动建主键索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 在高并发下倾向建立组合索引
5. 查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组的数据

2. 哪些情况不适合建索引

1. 频繁更新的字段
2. where条件用不到的字段不创建索引
3. 表记录太少
4. 经常增删改的表
5. 数据重复太多的字段，为它建索引意义不大（假如一个表有10万，有一个字段只有T和F两种值，每个值的分布概率大约只有50%，那么对这个字段的建索引一般不会提高查询效率，索引的选择性是指索引列的不同值数据与表中索引记录的比，，如果，一个表中有2000条记录，表中索引列的不同值记录有1980个，这个索引的选择性为1980/2000=0.99，如果索引项越接近1，这个索引效率越高）

4.执行计划

id：表示select子句或者操作的顺序

1. id相同：执行顺序自上而下
2. id不同：id值越大优先级越高，越先被执行
3. id相同不同：id越大越先执行，相同的自上而下执行

select_type：主要是区分普通查询、联合查询、子查询等。

SIMPLE：简单的select查询，不包含子查询与union
PRIMARY：查询中包含复杂的子部分，最外层会被标记为primary
SUBQUERY：在select或者where列表中包含了子查询
DERIVED：在from列表中包含的子查询衍生表
UNION：若第二个select出现在union之后，则被标记为union
UNION RESESULT：从union表获取结果的select

table：这一行数据是哪个表的数据
type：查询中使用了何种类型（优化程度参考）
  结果值从最好到最坏：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>rang>index>all

一般来说，得保证查询至少达到range级别，最好能到达ref
system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
const：表示通过索引一次就能够找到
eq_ref:唯一性索引扫描，对于每个索引键，表示只有一条记录与之匹配，常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
range：只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现了between、<、>、in等的查询
index：index比all快，因为index是从索引中读取，all是从硬盘中读取
all：遍历全表才能找到      

possible_key：显示可能应用在这张表中的索引，但实际上不一定用到
key：实际上使用的索引，如果没有则为null
key_len：表示索引中使用的字节数（可能使用的，不是实际的），可通过该列查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被用到，如果可能的话是一个常数，哪些常量被用于查找索引列上的值
rows：大致估算找出所需的记录要读取的行数
Extra：包含不适合在其他列中显示，但十分重要的的额外信息

1、Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序成为“文件排序”
2、Using temporary 使了用临时表保存中间结果，mysql在对查询结果排序时使用了临时表，常见于排序order by 和分组查询group by
3、Using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率高，如果同时出现了using where 表明索引被用来执行索引键值查找，如果没有出现    using where 表明索引用来读取而非执行查询动作。
4、Using where 表明使用了where进行过滤
5、Using join buffer 使用了连接缓存
6、impossible where where子句的值总是false，不能用来获取任何元组
7、select table optimized away 在没有group by子句的情况下，基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算
8、distinct:在优化distinct操作，在找到第一匹配的元组后即停止找到同样值的动作

5.索引失效

1. 应该尽量全值匹配
2. 复合最佳左前缀法则（第一个索引不能掉，中间不能断开）
3. 不在索引列上做任何操作（计算、函数、类型转换）会导致索引失效而转向全表扫描
4. 储存引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7. is null，is not null也会无法使用索引
8. like以统配符开头
9. 字符串不加单引号
10. 少用or

6.order by优化

1. 避免filesort

2. 尽量在索引上进行排序，遵照最佳左前缀原则

3. filesort有两种排序：

   1. 双路排序：两次磁盘扫描

   2. 单路排序：一次性读取保存在内存中，没拉完的数据再次拉

   3. 单路排序是后出的，总体好于双路排序

   4. 优化策略：

      1. 增大sort_buffer_size参数的设置
      2. 增大max_length_for_sort_data参数的设置
         原因：尽可能一次拿到内存