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的写法来达到选择合适索引的目的

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

3、索引优化

相关推荐