Mysql 索引及优化查询

Mysql 索引

1.说明

索引是一种特殊的文件(在InnoDB 数据表上的索引是表空间的一个组成部分)，它包含对数据表里所有记录的引用指针

 

2,.建立原则

   (1)最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，

如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

 

   (2)=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，

mysql的查询优化器会帮你优化成索引可以识别的形式

 

   (3)尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，

比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，

那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，

一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

 

   (4)索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，

原因很简单，b+树中存的都是数据表中的字段值，

但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

   (5)尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

 

 

3.使用索引注意事项

   (1)索引不会包含有NULL值的列 (有NULL则使索引无效)

   (2)使用短索引

   (3)索引列排序，Mysql查询只使用一个索引，如果where子句中已经使用索引，那么order by中的列将不会再使用。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引

   (4)like语句尽量不要使用，如果非要使用一个 like xxx% 形式

   (5)不要在列上计算

   (6)不使用!=,Not IN 和<>操作 (<>是一个符号)

 

4.索引分类

  普通索引

  唯一索引(允许有空值)

  主键索引(特殊的唯一索引，不允许有空值)

  全文索引(MyISAM)

  组合索引(最左原则eg建立索引(a,b,c) 则为 a,b,c  a,b  及a 三种)

      最左边的效率最快(字段长度最小的放在左边)

      建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一 个大表上创建了多种组合索引，索引文件的会膨胀很快。

5.索引创建，删除，查看

   查看

   SHOW INDEX FROM table

  创建

   普通：ALTER TABLE tableName ADD INDEX indexName field(length)

   组合：ALTER TABLE tableName ADD INDEX indexName (aField,bFiled,...)

   唯一：ALERT TABLE tableName ADD UNIQUE INDEX indexName filed

 

6.EXPLAIN(SQL分析效率分析)

  (1)id:越大执行的优先级越高,如果同级，由上向下执行

  (2)select_type:查询类型

    1>simple: 简单的 select （没有使用 union或子查询

    2>primary: 最外层的 select。

    3>union: 第二层，在select 之后使用了 union

    4>dependent union: union 语句中的第二个select，依赖于外部子查询

    5>subquery: 子查询中的第一个 select

    6>dependent subquery: 子查询中的第一个 subquery依赖于外部的子查询

    7>derived: 派生表 select（from子句中的子查询）

 (3)table:输出行所用的表

 (4)type(重要):优到劣排序

    1>system 表仅有一行

    2>const 表最多有一个匹配行

    3>eq_ref 对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型

     4>ref 对于每个来自于前面的表的行组合 如果联接不能基于关键字选择单个行的话(><)

    5>ref_or_null  这种连接类型类似 ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录

    6>index_merge

    7>uinque_subquery 这种类型用例如一下形式的 in 子查询来替换 ref (key in(select...))

    8>index_subquery 种连接类型类似 unique_subquery。它用子查询来代替in，不过它用于在子查询中没有唯一索引的情况下

    9>range 只有在给定范围的记录才会被取出来，利用索引来取得一条记录。key字段表示使用了哪个索引

    10>index

    11>all

  (5)possible_keys:可能用到哪些索引有助于查询

  (6)key:Mysql实际从possible_key 选择使用的索引

         使用USE INDEX(indexname) 强制使用索引

         使用IGNORE INDEX(indexname)强制忽略索引

  (7)key_len:使用索引的长度。

  (8)ref:显示索引的那一列被使用

  (9)rows:Mysql 认为必须检查的用来返回请求数据的行数,中药指标

 

  (10)extra:

    下列2项意味着Mysql不会使用索引，要优化

     Using filesort

    Using temporary

 

其他

   inner join 和 left join 差不多,都需要优化右表。而 right join 需要优化左表