精华内容
下载资源
问答
  • 建立组合索引的字段顺序优化

    千次阅读 2019-01-14 21:39:25
    建立组合索引的字段顺序优化 简介 组合索引我们经常用到,建立组合索引大家也都会,但是如何考虑建立组合索引的顺序是一个值得推敲的事情。 正文 1. 尽量把最常用的字段放在最前面 对于我们需要创建的组合索引,如果...

    建立组合索引的字段顺序优化

    简介

    组合索引我们经常用到,建立组合索引大家也都会,但是如何考虑建立组合索引的顺序是一个值得推敲的事情。

    正文

    1. 尽量把最常用的字段放在最前面

    对于我们需要创建的组合索引,如果同时又经常单独使用其中某个字段作为查询条件,这样的字段是要求放在组合索引前面的。

    因为这种场景下,能直接使用组合索引做范围扫描,否则,如果该字段放在后面,可能走索引跳跃扫描,全索引扫描,甚至全表扫描。

    举例:
    1. 首先创建表

      create table t_userserviceinfo_test as select * from T_USERSERVICEINFO nologging;
      
    2. 创建索引,把常用字段 phonenumber 作为组合索引的前导列

      create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(phonenumber,servstaus) tablespace ringidx; 
      
    3. 按号码查询,查看执行计划,走了该索引的范围扫描,很快就查到了结果。

    反例:
    1. 删除上面的索引

      drop index ix_userserviceinfo_test_1;
      
    2. 创建新的索引,把 phonenumber 不作为前导列

      create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(servstaus,phonenumber) tablespace ringidx; 
      
    3. 同样按号码查询,查看执行计划,走了该索引的跳跃扫描,效果不好。

    当然 Oracle考虑执行COST,可能就不会走这个索引了,导致全表扫描。

    2. 尽量把离散值较高的字段往前放

       	1. 条件中有单独使用这个字段,那么使用该索引有很好的效果
       	2. 放置误用索引,如果离骚之较少的字段放前面,同时条件中仅包含该字段,那么 Oracle 可能会选择该索引,但是其实选择该索引,选择率很低。
    

    3. 查询时,有的列是非等值条件,有点是等值条件,则等值条件字段放在前面

       	1. 等值条件字段放在前面,在查找的时候,找到的索引块都是有效数据。
       2. 如果非等值字段放在前面,那么需要进行索引跳跃扫描,或者范围扫描,这是就扫描了很多无效的索引。
    
    举例:
    1. 现需要根据状态和时间查找数据

       select * from t_userserviceinfo_test t where servstatus = 1 and t.upstatusstime > sysdate -100;
      
    2. 简历两个索引,分别把状态和时间字段顺序颠倒:

      create index ix_userserv_test_1 on t_userserviceinfo_test(servstaus,upstatustime);
      
      create index ix_userserv_test_2 on t_userserviceinfo_test(upstatustime,servstaus);
      
    3. 使用第一个索引查找

      select /* +index(ix_userserv_test_1) */  *
      from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
      

      得到执行分析如下,看到一致读数量为 334

      数据的查找过程是:首先从 servstaus = 1,upstatustime = sysdate -100 开始,找到第一条满足 servstaus = 1,upstatustime > sysdate -100 的数据,然后在索引树叶子节点顺序查找,直到找到第一条不满足条件的数据(servstaus = 2),退出查找,这个过程中查找到的索引都是有效索引。

      1. 使用第二个索引查找:
      select /* +index(ix_userserv_test_2) */  *
      from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
      

      看到一致读是前面的十倍,效果很不好。

      数据超找过程是:根据 upstatustime > sysdate -100 走的范围索引扫描,同时通过 servstaus = 1 过滤数据,存在大量的无用查找。

    总结:

    建立组合索引要考虑自身以及其他场景的使用情况,不要随意指定顺序。

    展开全文
  • mysql建立组合索引原则

    千次阅读 2015-02-27 09:42:10
    现实中,mysql可以根据业务需要建立组合索引,由于mysql使用B-Tree格式索引,可以直接定位记录,无需扫描。mysql建立多列索引有最左前缀的原则,即最左优先,如: 如果有一个2列的索引(col1,col2),则已经对(col1...
    现实中,mysql可以根据业务需要建立组合索引,由于mysql使用B-Tree格式索引,可以直接定位记录,无需扫描。mysql建立多列索引有最左前缀的原则,即最左优先,如:

    如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引;
    如果有一个3列索引(col1,col2,col3),则已经对(col1)、(col1,col2)和(col1,col2,col3)上建立了索引;

    如何建立组合索引?

    最频繁使用的列放在左边;
    查看列的选择性(即该列的索引值数量与记录数量的比值),比值越高,效果越好;

    例如用户表,如果按照用户姓名查询比较多,可以考虑在根据姓名建立索引。这里有两种形式:1在用户的名字字段(name)在做索引。2,在用户的名字和姓氏字段建立索引(name+family_name)。我们查看了下name字段的选择性,执行如下语句。

    SELECT count(DISTINCT(name))/count(*) AS Selectivity FROM user; 
    +-------------+


    | Selectivity |
    +-------------+
    | 0.0052 |
    +-------------+
    发现值非常小,因为name相同的用户实在太多。 再查看下name+family_name的选择性。
    SELECT count(DISTINCT(concat(name, family_name)))/count(*) AS Selectivity FROM user; 
    +-------------+


    | Selectivity |
    +-------------+
    | 0.9563 |
    +-------------+
    发现名字完全相同的员工基本没有。但是索引key如果太长会使得索引文件变大并且维护开销增大,name+family name的长度等于30,还是希望有一个艰巨选择性和长度的方案。 因此可以考虑在name和family前自己字符上面建立索引,例如 name + left(family_name,5):

    SELECT count(DISTINCT(concat(name, left(family_name,5))))/count(*) AS Selectivity FROM user;
    +-------------+


    | Selectivity |
    +-------------+
    | 0.9012 |
    +-------------+

    虽然不如以name+family_name的选择性高,但是已经基本满足要求。


    参考: http://www.lai18.com/content/312860.html

    展开全文
  • 转载自随心2017的博客 ...你创建一个 组合索引 ( 班级, 姓名) 那么 SELECT * FROM 学生表 WHERE 班级='2010级3班' AND 姓名='张三' 将使用索引. SELECT * FROM 学生表 WHERE 班级='2010级3班' 将使用索引...

    转载自随心2017的博客

    聚合索引

    例如你有一个 学生表。

    字段包含 学号, 班级, 姓名,性别, 出生年月日。

    你创建一个 组合索引 ( 班级, 姓名)

    那么

    SELECT * FROM  学生表  WHERE  班级='2010级3班'  AND  姓名='张三' 
    

    将使用索引.

    SELECT * FROM  学生表  WHERE  班级='2010级3班'      
    

    将使用索引 .

    SELECT * FROM  学生表  WHERE  姓名='张三'     
    

    将不使用索引。

    单独索引

    删除掉上面的索引

    再创建两个 独立索引

    索引1 ( 班级)

    索引2 ( 姓名)

    那么

    SELECT * FROM  学生表  WHERE  班级='2010级3班'  AND  姓名='张三'  
    

    将根据数据库的分析信息, 自动选择使用索引1或者索引2中的一个 (理论上会使用 索引2, 因为 姓名=张三的人少, 优先找到所有 姓名为 张三的人以后, 然后再从这些数据中, 找班级 = ‘2010级3班’ 的

    SELECT * FROM  学生表  WHERE  班级='2010级3班'      
    

    将使用索引1 .

    SELECT * FROM  学生表  WHERE  姓名='张三'     
    

    将使用索引2。

    总结

    组合索引是组合条件查询时有条件查询的顺序很重要,

    展开全文
  • mysql多个字段建立组合索引时候,字段顺序可以随意,但最好是遵循一定顺序的,如索引(a,b,c)与(b,a,c)肯定不一样的,顺序不一样索引的效果也不一样,所以要计算其先后顺序。 如表mc_k12_wechat_user_info有...

    mysql多个字段建立组合索引时候,字段顺序可以随意,但最好是遵循一定顺序的,如索引(a,b,c)与(b,a,c)肯定不一样的,顺序不一样索引的效果也不一样,所以要计算其先后顺序。

    如表mc_k12_wechat_user_info有如下字段

    700

     

    1、计算组合索引建立的顺序

    1、常用的字段放在最前面

    现在要建立组合索引(phone_number,provice),phone_number肯定是经常用的,要放在前边,provice不经常查,放在后边

    2、等值条件尽量在前边

    等值条件尽量在前边,在扫描的时候,找到的索引块都是有效数据。若非等值条件放在前边,那么需要进行索引跳跃扫描,或者范围扫描,这是就扫描了很多无效的索引,非等值条件放在后边可以,减少无效的索引扫描,提高查找效率。

    3、离散值较高的字段往前放

    现在要建立组合索引(phone_number,union_id,open_id)

    SELECT 
    COUNT(DISTINCT phone_number)/COUNT(*) phone_number, 
    COUNT(DISTINCT union_id)/COUNT(*) union_id ,
    COUNT(DISTINCT open_id)/COUNT(*) open_id 
    from mc_k12_wechat_user_info

    结果如下,按照ji计算值由大到小的顺序建立组合索引,值越大表明该字段不为空索引命中的几率越大。

    由计算结果可以看到 open_id > phone_number > union_id,组合索引建立的顺序就是(open_id,phone_number,union_id)

    ALTER TABLE mc_k12_wechat_user_info INDEX idx_wechat_user_info (open_id,phone_number,union_id)

    2、EXPLAIN 之key_len计算规则

    各种数据类型计算规则如下:

     

    char和varchar类型key_len计算公式:

    varchr(N)变长字段且允许NULL = N * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

    varchr(N)变长字段且不允许NULL = N * ( character set:utf8=3,gbk=2,latin1=1)+2(变长字段)

    char(N)固定字段且允许NULL = N * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)

    char(N)固定字段且允许NULL = N * ( character set:utf8=3,gbk=2,latin1=1)

    数值数据的key_len计算公式:

    TINYINT允许NULL = 1 + 1(NULL)

    TINYINT不允许NULL = 1

    SMALLINT允许为NULL = 2+1(NULL)

    SMALLINT不允许为NULL = 2

    INT允许为NULL = 4+1(NULL)

    INT不允许为NULL = 4

    日期时间型的key_len计算:(针对mysql5.5及之前版本)

     

    DATE 允许为NULL=3  +1(NULL)

    DATE 不允许为NULL=3

    DATETIME允许为NULL = 8 + 1(NULL)  (DATETIME从mysql5.6开始 长度5

    DATETIME不允许为NULL = 8

    TIMESTAMP允许为NULL = 4 + 1(NULL)

    TIMESTAMP不允许为NULL = 4

    组合索引遵循索引最左原则,key_len以最左边的索引字段长度为计算准则

     

    3、举例说明索引idx_union_id的计算规则

     EXPLAIN 查看执行计划

    EXPLAIN SELECT * from mc_k12_wechat_user_info where union_id = 'oNC5b6PvqwyLCpbc8b1aPMLNrtgk'

    看到key_len=194


     查看表结构

    desc mc_k12_wechat_user_info

    可以看到union_id的字段类型为varchar,长度为64且不允许为空,表字符编码为utf8

    所以key_len= varchar(64) * utf8(3) + 2 = 64 * 3 + 2 = 194

    组合索引遵循索引最左原则,组合索引的key_len计算亦遵循此原则,以最左边的索引字段长度为计算准则

    展开全文
  • 1.为所有的表建立了唯一索引,索引字段是主键id。 2.对Data表建立组合索引。 建立索引之前,需要花费2.796秒。 建立索引之后,只需要0.136秒。
  • Mysql之如何建立索引以及组合索引

    千次阅读 2015-12-25 20:56:24
    数据库中为何要建立索引? 这个问题对于做做简单实验的学生来说似乎并不需要过于了解,但是,如果处理的数据达到百万以及以上的时候,合适的索引就能够体现出很强大的优势 全文索引 B+树索引 Hash索引 创建索引的三...
  • MongoDB组合索引

    千次阅读 2018-08-17 11:00:15
    MongoDB支持组合索引,就是一个索引结构里面包括一个集合文档的多个字段。下图展示了一个拥有两个字段的组合索引组合索引的字段不能超过31个 组合索引查询的时候支持多个字段的匹配   一、创建一个组合...
  • 例如你有一个 学生表。...你创建一个 组合索引 ( 班级, 姓名) 那么 SELECT * FROM 学生表 WHERE 班级='2010级3班' AND 姓名='张三' 将使用索引. SELECT * FROM 学生表 WHERE 班级='2010级3班'
  • 普通索引:最基本的索引,没有任何限制 唯一索引:与"...组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。     Mysql常见索引有:主键索引、唯一索引、普通索引、全文...
  • 数据库组合索引

    千次阅读 2019-07-07 17:23:41
    组合索引建立的原则。b树和b+树的区别。 一次查询只能用到一个索引,所以 首先枪毙 a,b各建索引方案 a还是b? 谁的区分度更高(同值的最少),建谁! 当然,联合索引也是个不错的方案,ab,还是ba,则...
  • 组合索引

    2014-06-11 12:04:22
    1、组合列返回的数据越少越高效 2、组合索引有时可以避免
  • 复合索引(组合索引)

    2019-03-19 13:05:57
    用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引...
  • MySQL索引之组合索引

    2015-01-05 15:41:11
    单列索引:即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引; 组合索引:即一个索引包含多个列; 1.当查询where条件只有一个时,完全可以利用单列索引,因为这样速度可能会提高; 2.当涉及到的业务...
  • 题目:给定一张mytable表,其中有4个字段(name,sex,birth,birthaddr),分别建立组合索引(name,sex,birth,birthaddr)where条件后边使用where name=xx and sex=xx或者name=xx and sex=xx,或者name=xx or sex=xx 或者...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 141,765
精华内容 56,706
关键字:

怎么建立组合索引