MySql 的性能分析和索引建立与优化

1、索引简单语法

• 建立
  CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
  ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))；

• 删除
  DROP INDEX [indexName] ON mytable;

• 展示
  SHOW INDEX FROM table_name\G

2、MySql 的性能分析

• 查看执行计划
  Explain + SQL语句
• 怎么看/每个字段的意思
  

主要字段分析

• id

  select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
  相同：从上而下
  不同：从大到小
  相同不同：先大小后从上到小

• type

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

• key

  实际使用的字段，查询中若使用了覆盖索引，则该索引和查询的select字段重叠
  

• Extra

    包含不适合在其他列中显示但十分重要的额外信息
                Using filesort  查询排序中的字段 ，不推荐
                Using temporary  使用临时表是 ，索引必定失效
                USING index 效率最好的
     Using where 使用where 语句进行查询
  

• select_type

    查询的类型，主要是用于区别 普通查询、联合查询、子查询等的复杂查询
    simple primary subquery(子查询)、 derived（子查询的衍生）、UOION、UNION RESULT
  

• ref

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

3、索引优化

• Join语句的优化
  尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。
  
  优先优化NestedLoop的内层循环；
  保证Join语句中被驱动表上Join条件字段已经被索引；
  当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；
• 建立索引的原则和索引失效的
  1、全职匹配我最爱
  2、最佳左前缀法则
  3、不在索引列上做任何的操作（计算、函数、自动或手动类型转换 ），会造成索引失效导致全表扫描
  4、索引、存储引擎不能使用索引中范围条件右边的列
  5、尽量使用索引覆盖（只访问索引查询列），避免select *
  6、MySql在使用不等于时，会导致
  7、is null 、is not null 也无法使用索引
  8、like 以通配符（‘%acv’）会引起索引失效，% 要在最右边
  9、字符串不用’'引号处理会索引失效
  10、少用or,用它来连接时会引起索引失效
• 一般性的建议
  对于单键索引尽量选择针对当前query过滤性
  在选择组合索引的时候。当前Query中过滤性最好的字段在索引字段顺序中尽量靠前使用
  在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的