组合列mysql下的索引使用原则

每日一贴,今天的内容关键字为组合列

为表建立索引，无疑是对数据库比较好的优化方法之一。以下是自己对索引的总结。

MYSQL QUERY Optimizer对索引的选择

1.即使一个SQL可以选择多个索引，但是大多数情况下它都市选择一个索引，而废弃其它的索引。

应用索引的前提：

1.索引的存是在where条件之后的。

2.在MYSQL中不同的存储引擎对索引的看待也是有点不一样的。

经常使用的索引类型以及情况

前缀索引(又叫短索引)

对串列停止索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是唯一的，那么就不要对整个列停止索引。短索引不仅可以提高查询速度而且可以节俭磁盘空间和I/O操作。

示例代码:

    //创建短索引
  

    
      create
    
  

     index

    ix_test

     on

    t(col(200))

  
    //适用的情况
  
  
    
      SELECT
    
  
  

        userName,income
  
  

        
    
      FROM
    
  
  

        t
  
  

        
    
      WHERE
    
  
  

        col
  
  

        
    
      like
    
  
  

        '
  
  

        
    
      john%
    
  
  

        ';
  
  
  

        
  
  

    也就是说： 短索引的适用范围主要是在like之后第一个非’%’的实例。
  
  

        
  
  

     
  
  

        
  
  

     
  
  

        
  
  

    
      组合索引
    
  
  

        
  
  

  

        
  
  

     比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
    

     如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能应用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(
    
      area
    
    ,age,salary)、(
    
      area
    
    ,age)、(
    
      area
    
    )三个索引，这被称为最好左前缀特性。
  
  

        
  
  

     对于组合索引，MYSQL数据库所停止查询的道理：
  
  

    
        每日一道理 
   
    

     即使青春是一枝娇艳的花，但我明白，一枝独放永远不是春天，春天该是万紫千红的世界。 即使青春是一株大地伟岸的树，但我明白，一株独秀永远不是挺拔，成行成排的林木，才是遮风挡沙的绿色长城。即使青春是一叶大海孤高的帆，但我明白，一叶孤帆很难远航，千帆竞发才是大海的壮观。 
   
    

  
  
    
      select
    
  
  

        *
  
  

        
    
      from
    
  
  

        users
  
  

        
    
      where
    
  
  

        area=’beijing’
  
  

        
    
      and
    
  
  

        age=22;
  
  
  
    
      select
    
  
  

        *
  
  

        
    
      from
    
  
  

        users
  
  

        
    
      where
    
  
  

        area=’beijing’;
  
  
  
    如果area有索引,则以上的SQL语句都市应用索引，因为组合索引有最好左前缀的特性，而select *
  
  

        
    
      from
    
  
  

        users
  
  

        
    
      where
    
  
  

        age=22; 则不会应用索引。
  
  
  

        
  
  

     
  
  

        
  
  

    
      因此我们在创建复合索引时应该将最经常使用作限制条件的列放在最左边，依次递减。
    
  
  

        
  
  

     
  
  

        
  
  

     
  
  

        
  
  

    
      单键索引还是组合索引
    
  
  

        
  
  

  

        
  
  

    在一般的应用场景中，
    
      只要不是其中某个过滤字段在大多数场景下能过滤90%以上的数据（
      
        这类可能性很小
      
      ），而其他的过滤字段会频繁的更新，一般更倾向于创建组合索引，尤其是在并发量较高的场景下
    
    。因为当并发量较高的时候，即使只为每一个Query节俭了很少的 IO 消费，但因为执行量非常大，所节俭的资源总量仍然是非常可观的。
  
  

        
  
  

     
  
  

        
  
  

    当然，
    
      创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量
    
    ，减少因为数据更新带来的索引更新本钱，同时还可以减少因为索引所消费的存储空间。
  
  

        
  
  

     
  
  

        
  
  

    
      有OR在SQL语句中来应用索引的情况
    
  
  

        
  
  

  

        
  
  

     如果有or，where前面就必须全面条件有索引，否则索引将不会起作用,所以必定要为or的全部字段创建索引
  
  

        
  
  

     SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';
  
  

        
  
  

      
  
  

        
  
  

      
  
  

        
  
  

    
      ordery by以及索引排序
    
  
  

        
  
  

  

        
  
  

     mysql查询只应用一个索引，因此如果where子句中已经应用了索引的话，那么order by中的列是不会应用索引的。
  
  

        
  
  

    
      因此数据库默认排序可以符合要求的情况下不要应用排序操作；(排序功能由索引来实现)
    
    尽量不要包括多个列的排序，如果需要最好给这些列创建复合索引。
  
  

    文章结束给大家分享下程序员的一些笑话语录： 程序员的愿望
    

     　　有一天一个程序员见到了上帝.上帝: 小伙子,我可以满足你一个愿望.程序员: 我希望中国国家队能再次打进世界杯.
    

     　　上帝: 这个啊!这个不好办啊,你还说下一个吧!
    

     　　程序员: 那好!我的下一个愿望是每天都能休息6个小时以上.
    

     　　上帝: 还是让中国国家打进世界杯.
    

  
  

    
      组合列mysql下的索引使用原则