MYSQL 优化笔记

(1)id: select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序;
       id相同，执行顺序由上至下
       id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
       id相同不同，同时存在,id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
(2)select_type:查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
       SIMPLE:简单的 select 查询,查询中不包含子查询或者UNION
       PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
       DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生表)　　MySQL会递归执行这些子查询, 把结果放在临时表里。DERIVED 既查询通过子查询查出来的 临时表；
       SUBQUERY：在SELECT或WHERE列表中包含了子查询
       DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询,子查询基于外层，dependent subquery 与 subquery 的区别 依赖子查询 ： 子查询结果为 多值；子查询：查询结果为 单值
       UNCACHEABLE SUBQUREY：无法被缓存的子查询
       UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED；UNION RESULT 两个语句执行完后的结果
       UNION RESULT：从UNION表获取结果的SELECT
简单的 select 查询,查询中不包含子查询或者UNION
(3)type：索引类型，system>const>eq_ref>ref>range>index>all
要对type进行优化的前提：有索引
其中system、const只是理想情况，很难达到，实际能达到的ref/range、eq_ref偶尔能达到

system:只有一条数据的系统表，或者衍生表只有一条数据的朱查询
 const:仅仅能查到一条数据的SQL，用于Primary key 或unique索引，与索引类型有关
eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据(有且只有一个，不能多，不能0)，常见于唯一索引和主键索引
   ref:非唯一索引，对于每个索引建的查询，返回匹配的所有行(0，行或多行)
 range:检索指定范围的行，where后面是一个范围查询(between,>,<,>=,in,等，in有时会失效从而转为all查询)
 index:查询全部索引列数据
   all:查询全部表中的数据(较index数据量大）

(4)possible_keys:可能用到的索引，是一种预测，不一定准，值为null时没有用到索引
(5)key :实际用到的索引，值为null时没有用到索引
(6)key_len:索引的长度，经常用于判断符合索引是否完全被使用(a,b,c三个字段的索引有几个起到作用)
   如果索引字段可以为null，mysql会用一个字节去标识；
   mysql用两个字节标识可变长度 (如char不可变，varchar可变)
   utf8一个字符占三个字节，gbk一个字符占两个字节，latin一个字符占一个字节
   如，一个utf8编码值可为null的varchar(20)类型的字段，它的key_len计算方式：3x20+1+2=63
(7)ref:用于指明当前表所参照的字段
(8)rows:被索引优化查询的数据个数(实际通过索引查询到的数据个数)
(9)extra :using filesort(表示性能消耗大，需要额外的一次查找排序)，一般出现在order by 语句中
          避免：对于单索引，如果排序和查找是同一个字段则不会出现using filesort，否则会出现
                对于复合索引不能跨列(最佳左前缀，按复合索引创建顺序要先左后右使用，不能跨列)，where和order by按照复合索引的顺序使用，不能跨列或无序使用
          
          using temporary:性能损耗大，用到了临时表，已经有表了但不适用，要再来一张表。一般出现在group by语句中
          避免：查询列要出现在group by中 ，考虑sql的解析过程
          
          using index:性能提升(索引覆盖），说明此次查询不读取源文件，只从索引文件中获取数据，只要使用到的列全部都在索引中，就会出现index
          using where:虽然用到了索引，但是部分数据需要回原表查询；比如除了查询条件(查询条件为索引字段)以外查询的其他字段;（复合索引跨列使用，会造成后面的索引失效，会回表查询）
          imposiable where:查询条件永远为false
          using filesort:文件内排序，多了一次额外的查找/排序；不要跨列使用复合索引（where和order by连起来不跨列）；如果(a,b,c,d)复合索引 和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致，则使用部分索引；
          using join buffer:mysql底层使用的连接缓存，当写的sql性能差的时候，mysql底层帮助sql优化
--------------------------------------------
单表优化查询：
 sql需要逐步优化，不需要追求一步达到最优；
 sql优化是一种概率优化，至于是否使用了所做的优化，需要用explain进行推测
 服务层的优化器有可能会干扰所做的优化，导致跟预想的优化效果不一样
1.一但索引优化升级，需要将之前的索引删掉，防止干扰
2.查询条件 'in',between,>,等范围查询有可能会使索引失效，需要将其尽量放后面
3.索引不能跨列使用(最佳做前缀)，保持索引的定义和使用的一致性
4.using index(不回原表查询),using where(回原表查询)，查询字段在索引中时不回原表查询，否则会回原表查询，查询字段在索引中但索引失效的情况下(如范围条件查询)，会回原表查询  
-----------------------------------------------
双表优化查询：
1.小表驱动大表，如left join 小表放左边，大表放右边
2.索引需要建立在经常使用的字段上
3.一般情况下，左外连接给左表建立索引，右外链接给友表建立索引
避免索引失效原则：
  a.复合索引，不要跨列或无序使用(最佳做前缀）
  b.尽量使用全索引匹配，比如复合索引a,b,c三个字段尽量都是用
  c.不要在索引上进行任何操作，否则索引失效，如(在索引查询条件上进行计算，函数，类型转换，select a,b,c from atable,where a*1=2）
  d.复合索引，一旦左边的索引失效，则右边的全部失效（如复合索引a,b,c 若b失效，则b,c全部失效）
  e.复合索引不能使用不等于(!= <>)或is null(is not null)或>,<号(部分概率有效) 否则自身和右边索引全部失效
  f.索引优化是一个大部分情况适用的结论，但由于sql优化器等原因结论不是100%正确，尽量使用索引覆盖(using index)去补救
  g.like 尽量以"常量开头，不要以'%'开头，否则索引失效,如(select * from table where name like '%xx%'索引失效，select * from table where name like 'xx%'索引起作用)，如果不可避免的使用'%xx%'进行模糊查询，可以使用索引覆盖挽救，如查询的字段都是索引列
  h.尽量不要使用类型转换(显式，隐式)，即索引字段类型与查询条件类型保持一样
  i.尽量不要使用or否则索引失效，甚至会把or左边的索引也失效
其他优化方法：
  1.exist和in 
  如果主查询的数据集大，则使用in
  如果子查询的数据集大，则使用exist
  
  exist语法：将主查询的数据放到子查询中进行条件校验(看子查询是否有数据，如果有数据则符合校验，保留数据)
  例如：select tname from teacher where exists(select * from teacher where tid=99)
  2.order by 优化
  using filesort  有两种排序算法 单路排序、双路排序 (根据io的次数)
  mysql4.1之前默认使用双路排序，双路：扫描2次磁盘(1,从磁盘读取排序字段，对排序字段)进行排序(在buffer中进行)，2,再次读取磁盘读取其他字段）
  mysql4.1之后默认使用单路排序，理论上只读取一次磁盘(读取全部字段)，在buffer中进行排序，会有一定的隐患(不一定是一次io，数据量大时会出现多次io，无法将所有字段一次性读取时，会进行分片读取即多次io)
  单路排序会比多路排序占用更多的buffer，因此在单路排序时，如果数据量过大可调大buffer的大小：set max_length_for_sort_data = 1024 单位byte
  如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路 (太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数)
  提高order by 查询的策略：
  a.选择使用单路、双路：调整buffer的容量大小；
  b.避免select * ....
  c.复合索引不要跨列使用，避免using filesort;
  d.保证全部的排序字段顺序的一致性(都是升序或降序)
SQL排序-慢查询日志：
  mysql提供的一种日志记录，用于记录mysql中响应时间超过阈值的sql语句，慢查询日志默认是关闭的：建议：开发调优时打开，而最终部署的时候关闭。
  检查是否开启了慢查询日志：show variables like '%slow_query_log%';
  临时开启：set global slow_query_log =1;在内存中开启
            exit 
            service mysql restart
  永久开启：
            /etc/my.cnf 中追加配置:
            vi /etc/my.cnf
            [mysqld]
            slow_query_log=1
            slow_query_log_file=/var/lib/mysql/localhost-slow.log
 慢查询阈值：show variables like '%long_query_log%';
 临时设置阈值：set global long_query_time =5; --设置完毕后，需要重新登录后起效
 永久设置阈值: /etc/my.cnf 中追加配置；
               vi /etc/my.cnf
               [mysqld]
               long_query_time=3
 查询超过阈值的sql: show global status like '%slow_queries%';
 慢查询被记录在日志中可以通过打开日志进行查看具体的慢sql
 也可以通过mysqldumpslow工具查看慢sql    mysqldumpslow --help查看工具帮助
 
 
 分析海量数据:profiles
              show profiles ;--默认关闭
              show variables like '%profiling%';查看状态是否关闭
              set profiling =on ;设置状态为打开
              show profiles : 会记录所有profiling 打开之后的全部sql语句所花费的时间,缺点：不够精确，只能看到总共花费的时间，不能看到各个硬件如cpu等花费的时间
              --精确分析：sql诊断
              show profile all for query; 上一步查询的query_id,
              show profile cpu,block io for query;  例如show profle cpu,block io for query 2;
              
              全局日志查询：记录开启之后的全部sql语句(仅在调优开发过程中打开)
              show variables like '%general_log%';
              set global general_log =1;--开启全局日志
              set global log_out ='table';--把日志记录到表（mysql.general_log）中
              select * frin mysql.general_log；--查询记录的sql
              开启后会记录所有sql:会被记录到mysql.general_log表中
              
锁机制
     表锁：一次性对一张表加锁，如MyISAM存储引擎使用表锁，开销小、加锁快、无死锁
     行锁：一次对一条数据加锁，如InnoDB存储引擎使用行锁，锁的范围小，不易发生锁冲突，并发度高(很小概率发生高并发问题)
     页锁：
 

每个表最多支持64个索引。每个索引可以包含1到16列或部分列