1 回表

# 回表查询：先定位主键值，再定位行记录的查询
   性能 比 聚集索引(只扫一遍索引树) 更低
  
# eg: 辅助索引查询
  对于辅助索引查询方式而言，一共搜索了两棵 B+Tree，
  第一次搜索 B+Tree 拿到主键值后 再 去搜索主键索引的 B+Tree，这个过程就是所谓的回表。


# 回表优化： 慢查询优化的一种，减少回表的次数
  核心：sql 查询时，只select 索引包含的字段
    
  1.单个辅助索引，只查该索引的字段
    eg: name为辅助索引， 那么select时，只查询主键id 或 name
        
  2.若需要两个或多个字段，减少回表：可将该两个建联合索引     # 但谨慎使用，若索引过大，也会影响mysql性能
    eg: name、age为联合索引， 那么select时，只查询主键id 或 name 或 age

2 慢查询日志

# 慢查询日志
  设定一个最长查询时间，检测所有超出该时间的sql语句，并记录到慢查询日志文件中，然后针对性的进行优化！

  # 默认情况下，MySQL数据库并不启动慢查询日志，需要手动设置
    当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响。

2.1 开启慢查询日志

# 1.配置MySQL自动记录慢日志  
  vim /etc/my.cnf
    
[mysqld]

slow_query_log = off/on              # 是否开启慢日志记录
long_query_time = 2                  # 慢查询的阀值，超过此时间，则记录到慢查询日志  默认:10s
slow_query_log_file = /usr/slow.log  # 指定慢日志文件存放位置,可以为空  默认：host_name-slow.log
log_queries_not_using_indexes = OFF  # 未使用索引的搜索是否记录

min_examined_row_limit = 100         # SQL查询结果返回行数的阀值，小于该行数，SQL则不被记录
  # 该参数尽量不用  eg：如果查询半小时，返回少于100
    
    
# 注：
  # 1.查看当前配置信息：
  show variables like '%slow_query_log%'  # 查看慢日志是否开启

  # 2.修改当前配置：
  set global 变量名 = 值

# 2.查看MySQL慢日志
cat /usr/slow.log 

2.2 慢查询日志分析

# 在生产环境中，如果要手工分析慢查询日志，查找、分析SQL，显然是个体力活 
  MySQL提供了日志分析工具mysqldumpslow
    
  # 记录到慢日志中的日志是没有顺序的，是追加的模式

# mysqldumpslow命令
  mysqldumpslow -s c -t 10 /data/slow/slow.log  # 输出记录次数最多的10条SQL语句
  mysqldumpslow -s at -t 10 /data/slow/slow.log # 输出平均查询时间最高的10条SQL语句
    
# 参数：
  -s # 是表示按照何种方式排序   默认：at
　    c     #  记录次数
      t、at #  查询时间、平均查询时间
      l、al #  锁定时间、平均锁定时间
      r、ar #  返回记录数、平均返回记录数

  -t # 是top n的意思，即为返回前面多少条的数据；


# 扩展工具
  1.pt-query-diagest 自动做判断，先优化哪一个 # mysql运维工具:percona-toolkit里面的
  2.mysqlsla   # 专门用于处理分析Mysql的日志而存在

  # 重要的是先找到，哪条语句最慢，而且执行次数最多

3 explain-执行计划

https://cloud.tencent.com/developer/article/1093229

# 通过 explain 命令 可获取select语句的执行计划  而不是执行这条SQL
  explain + 查询SQL ---> 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化


# explain执行计划 返回的字段参数信息
  核心指标：
    1.type 找到结果的查询方式   
           # 达到range及以上
    
    2.rows 找到结果的 所读取的行数  # 就是缩小 条件查询的范围  eg: id=100 和 id>100
           # 绝大部分rows小的语句，执行一定很快 (有例外，下面会讲到)
            所以优化语句基本上都是在优化rows
            
	3.table # 显示改行数据是关于哪张表
    
    4.key   # 使用的索引
    

# 1.id   查询顺序标识
  id列是select的序列号，有几个select 就有几个id  并且id的顺序是按select出现的顺序增长的
  表示查询中执行select子句或操作表的顺序

  # 注：
  a.在嵌套查询中  id越大的语句 越先执行
  b.该值可能为NULL 是其他行select的联合(union)结果  # union的结果总是放在一个匿名临时表


# 2.select_type  子句类型
  表示查询中每个select子句的类型（简单OR复杂）
    
  # 值：
  a.SIMPLE   # 简单查询  查询不包含子查询和union
        
  b.PRIMARY  # 复杂查询中 最外层的select子查询
  c.SUBQUERY # 包含在select 或 where中的子查询
  d.DERIVED  # 包含在from子句中的select     derived  n.衍生
               mysql会递归执行并将结果放到一个临时表中，服务器内部称为"派生表"    

  e.UNION        # 位于union中第二个及其以后的select
  f.UNION RESULT # 从union临时表检索结果的select
  
  g.DEPENDENT SUBQUER # 包含在select或where中的子查询，并依赖于外层查询中发现的数据
  h.DEPENDENT UNION   # 位于union中第二个及其以后的select，并依赖于外层查询中发现的数据
  
  i.UNCACHEABLE SUBQUER # 一个子查询的结果不能被缓存，必须重新评估外链接的第一行

    
# 3.table  数据表  *****
  对应行的数据是关于哪张表的  表名或者别名
    
  # 注：
  1.当from子句中有子查询时，是 <derivenN> 格式 表示当前查询依赖id=N的查询，先执行id=N的查询
  2.当UNION RESULT时，是<union1,2>格式  1和2表示参与union的 select行id

    
# 4.type  关联类型或访问类型   *****
  表示MySQL在表中找到所需行的方式

  # 常见值有：
  ALL、index、range、ref、eq_ref、const、system、NULL # 从左到右 性能从差到好

  ALL   # 即全表扫描 对于数据表从头到尾找一遍 
        需优化，说明查询没有走索引：
   　　    1.语句本身的问题
   　　    2.索引的问题，没建立索引

  index # 即全表扫描索引树  与ALL区别为只遍历索引树

  range # 索引范围扫描   对索引列进行范围查找
        eg: >  < >= <=   between  and   in()   or

  ref   # 使用非唯一索引或者唯一索引的部分前缀  可能会找到多个符合条件的行
        简单说：用了索引，但不是唯一返回值，可能返回多条数据

  eq_ref # 类似ref，区别就在使用的索引是唯一索引或主键索引  对于每个索引键值，最多只有一条记录匹配
         简单说：
           就是多表连接中使用primary key或者 unique key作为关联条件    
　　        join条件使用的是primary key或者 unique key

  const、system  # mysql能对查询的某部分进行优化并将其转化成一个常量。
                 eg: 将主键置于where中，MySQL就能将该查询转换为一个常量
                 system是const类型的特例，当查询的表只有一行的情况下

  NULL  #  MySQL在优化过程中分解语句，执行时甚至不用访问表或索引
        eg：在索引列里选取最小值，可以通过单独索引查找完成，不需要在执行时访问表。
              或者走的缓存中已经存在的


# 5.possible_keys 
  显示查询可能使用的索引


# 6.Key  *****
  显示实际决定使用的索引


# 7.key_len
  表示索引中使用的字节数
    
  # 注：
  1.可通过key_len计算规则，计算查询中使用的索引长度，实现短索引原则 # 值为索引字段的最大可能长度，并非实际使用长度
  2.索引最大长度是768字节
 

# 8.ref
  表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  
  常见的有：const（常量）、func、NULL、字段名(例：film.id)


# 9.rows  *****
  表示MySQL根据表统计信息及索引选用情况，估算 找到查询结果 所需要读取的行数

     
# 10.Extra  *****
  该列包含MySQL解决查询的详细信息,有以下几种情况：

  # 值：
  distinct     # 表示mysql一旦找到了与行相联合匹配的行 就不再搜索了    性能高的表现
        
  Using index  # 表示mysql将使用覆盖索引，以避免回表     性能高的表现

  Using where  # 表示mysql服务器将在存储引擎检索行后，再进行过滤
                 就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃
        
  Select tables optimized away  # 表示仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

  -----------以下避免出现  需索引优化----------
  Using temporary # 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

  Using filesort  # 表示MySQL中无法利用索引完成排序操作，而使用一个外部索引排序

  Using join buffer # 表示在获取连接条件时没有使用索引，使用了连接缓存区来存储中间结果

  Impossible where  # 表示where子句的值总是false，没有符合条件的行   

4 慢查询优化

# 慢查询优化的基本思路步骤：
  1.先运行看看是否真的很慢，注意设置SQL_NO_CACHE  # SQL_NO_CACHE的作用是禁止缓存查询结果
    
  2.where条件单表查，锁定最小返回记录表。
    # 意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，
      单表每个字段分别查询，看哪个字段的区分度最高

  3.explain查看执行计划，是否与预期一致  # 从锁定记录较少的表开始查询
  4.order by limit 形式的sql语句，让排序的表优先查 
  5.了解业务方使用场景
  6.加索引时，参照建索引的几大原则  
  7.观察结果，不符合预期继续从1分析



# 企业SQL优化思路：
  1.把一个大的不使用索引的SQL语句按照功能进行拆分
  2.长的SQL语句无法使用索引，能不能变成2条短的SQL语句让它分别使用上索引。
  3.对SQL语句功能的拆分和修改
  4.减少“烂”SQL
    由运维（DBA）和开发交流（确认），共同确定如何改，最终由DBA执行
  5.制定开发流程    

# 不适合走索引的场景：
  1.唯一值少的列上不适合建立索引或者建立索引效率低。例如：性别列
  2.小表可以不建立索引，100条记录。
  3.对于数据仓库，大量全表扫描的情况，建索引反而会慢  # 读写数据时，需要额外的建索引操作，反而降低性能 


# 查看表的唯一值数量：
  select count(distinct user) from mysql.user;
  select count(distinct user,host) from mysql.user;     


# 建索引流程：
  1.找到慢SQL
     show processlist;
     记录慢查询日志

  2.explain select句,条件列多

  3.查看表的唯一值数量：
    select count(distinct user) from mysql.user;
    select count(distinct user,host) from mysql.user;
    条件列多  可以考虑建立联合索引

  4.建立索引(流量低谷)
  force index

  5.拆开语句（和开发）

  6.like '%%'不用mysql，使用全文搜索ES

原文地址：http://www.cnblogs.com/Edmondhui/p/16836092.html