精华内容
下载资源
问答
  • 创建多列索引 在t_user表id,userName,email字段上创建多列索引(该表只有此索引): alter table t_user add index USER_INDEX(id, userName, email); 能够利用该索引的查询 符合leftmost index prefixes原则的查询 ...

    创建多列索引 在t_user表id,userName,email字段上创建多列索引(该表只有此索引): alter table t_user add index USER_INDEX(id, userName, email); 能够利用该索引的查询 符合leftmost index prefixes原则的查询 select * from t_user where id = 40;se

    创建多列索引

    在t_user表id,userName,email字段上创建多列索引(该表只有此索引):alter table t_user add index USER_INDEX(id, userName, email);

    能够利用该索引的查询

    符合leftmost index prefixes原则的查询select * from t_user where id = 40;

    select * from t_user where id between 10 and 50;

    select * from t_user where id in (30, 31, 32);

    select * from t_user where id = 40 and userName = '侯西阳';

    select * from t_user where id = 40 and userName = '侯西阳' and email = 'xiyang.hou@gmail.com';

    select * from t_user where id > 40 and userName > 't';

    不能利用以上索引的查询

    不符合leftmost index prefixes原则的查询select * from t_user where userName = '侯西阳';

    select * from t_user where userName = '侯西阳' and email = 'xiyang.hou@gmail.om';

    or查询select * from t_user where id = 40 or userName = '侯西阳';

    不能使用索引的解决方案在where语句后面的查询字段建立单个索引及多列索引,注意leftmost index prefixes原则,避免建立重复索引

    or查询使用union来连接查询结果,并在对应的字段上建立索引

    展开全文
  • 如何创建合适的索引-主要是创建多列索引还是单列索引?  [复制链接] w_z_y 论坛徽章: 0 电梯直达 1#  发表于 2004-12-8 15:21 | 只看该作者 
    查看: 13665|回复: 10
    收藏 打印 上一主题 下一主题

    如何创建合适的索引-主要是创建多列索引还是单列索引? 

    [复制链接]
    论坛徽章:
    0
    跳转到指定楼层
    1#
     发表于 2004-12-8 15:21 | 只看该作者 回帖奖励
    如我有一个表
    UserInfo
    {
       id number(10,0) not null,
       username varchar(100) not null,
       realname varchar(100) not null,
       birthday date not null
    }

    其中id列是PK,username 是唯一键(unique)
    以上四列经常被当作条件放到where 字句里面。
    我是分别在每一列上创建一个索引,还创建一个索引包含一列呢?
    请多指教!!
    如:
    1)select * from userInfo where id = 10;
    select * from userInfo where username = '1111';
    select * from userInfo where name = '1111';
    select * from userInfo where birthday= to_date('2003-10-10','yyyy-mm-dd');

    这个肯定创建单列索引了
    2)select * from userinfo where  name = '2222' and birthday= to_date('2003-10-10','yyyy-mm-dd');
    如何创建索引??
    3)select * from userinfo where  id = 1111 and name = '2222' and birthday= to_date('2003-10-10','yyyy-mm-dd');
     
       
    论坛徽章:
    0
    2#
     发表于 2004-12-8 15:32 | 只看该作者
    每一列创建索引
     
     
       
    论坛徽章:
    2
    授权会员日期:2005-10-30 17:05:33会员2006贡献徽章日期:2006-04-17 13:46:34
    3#
     发表于 2004-12-8 15:49 | 只看该作者
    下面的內容是使用composite indexes的好處和一些建議,你看一下看對你有沒有用:
    Choosing Composite Indexes
    A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes: 

    Improved selectivity 
    Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with more accurate selectivity. 

    Reduced I/O 
    If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table. 




    A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement: 

    CREATE INDEX comp_ind 
    ON tab1(x, y, z);


    These combinations of columns are leading portions of the index: x, xy, and xyz. These combinations of columns are not leading portions of the index: yz, y, and z. 

    Follow these guidelines for choosing keys for composite indexes:
    Consider creating a composite index on keys that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually. 

    If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys. 

    Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections. Follow these guidelines for ordering keys in composite indexes: 

    Create the index so the keys used in WHERE clauses make up a leading portion. 

    If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index. 

    If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance. 

    If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index. 

    有一點也注意的是:如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。
    所以,你要根據你的查詢需求來決定是否使用Composite Indexes
     
     
       
    招聘 : 数据库管理员
    论坛徽章:
    21
    授权会员日期:2005-10-30 17:05:332012新春纪念徽章日期:2012-02-13 15:11:362012新春纪念徽章日期:2012-02-13 15:11:362012新春纪念徽章日期:2012-02-13 15:11:362012新春纪念徽章日期:2012-02-13 15:11:36马上有车日期:2014-02-19 11:55:14马上有房日期:2014-02-19 11:55:14马上有钱日期:2014-02-19 11:55:14马上有对象日期:2014-02-19 11:55:142012新春纪念徽章日期:2012-02-13 15:11:36
    4#
     发表于 2004-12-26 17:27 | 只看该作者
    最初由 oracle-plus 发布
    [B]
    有一點也注意的是:如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。
    所以,你要根據你的查詢需求來決定是否使用Composite Indexes [/B]


    这个是8i的限制,9i不是这样了
     
     
       
    论坛徽章:
    31
    授权会员日期:2005-10-30 17:05:332012新春纪念徽章日期:2012-02-13 15:09:232012新春纪念徽章日期:2012-02-13 15:09:232012新春纪念徽章日期:2012-02-13 15:09:232012新春纪念徽章日期:2012-02-13 15:09:23马上有车日期:2014-02-19 11:55:14马上有房日期:2014-02-19 11:55:14马上有钱日期:2014-02-19 11:55:14马上有对象日期:2014-02-19 11:55:142012新春纪念徽章日期:2012-02-13 15:09:23
    5#
     发表于 2004-12-26 18:03 | 只看该作者
    创建索引不光要看查询
    还要注意对表进行delete insert是否很频繁
    综合考虑
    索引并不是越多越好
    索引多了自然系统对维护索引的代价也多了
     
     
       
    招聘 : 数据库管理员
    论坛徽章:
    66
    ITPUB元老日期:2005-07-16 18:49:11授权会员日期:2005-10-30 17:05:33ITPUB新首页上线纪念徽章日期:2007-10-20 08:38:44现任管理团队成员日期:2011-05-07 01:45:08版主3段日期:2012-05-15 15:24:11
    6#
     发表于 2004-12-26 18:05 | 只看该作者
    三个索引足了
    1 id上的主键
    2 username上的unique index
    3 name+birthday的组合索引
     
     
       
    论坛徽章:
    24
    生肖徽章:狗日期:2006-09-07 10:14:43数据库板块每日发贴之星日期:2008-07-26 01:02:20生肖徽章2007版:兔日期:2008-10-13 11:10:11奥运会纪念徽章:铁人三项日期:2008-10-24 13:27:21开发板块每日发贴之星日期:2008-12-27 01:01:09生肖徽章2007版:马日期:2009-11-18 10:45:032010新春纪念徽章日期:2010-03-01 11:21:02ITPUB9周年纪念徽章日期:2010-10-08 09:28:51ERP板块每日发贴之星日期:2011-05-18 01:01:012012新春纪念徽章日期:2012-01-04 11:51:22
    7#
     发表于 2007-11-15 18:15 | 只看该作者
    最初由 grassbell 发布
    [B]

    这个是8i的限制,9i不是这样了 [/B]


    这个要看sql怎么写的。

    SQL> desc user_info
    名称                                      是否为空? 类型
    ----------------------------------------- -------- ----------------------------

    ID                                                 NUMBER
    CODE                                               VARCHAR2(10)
    NAME                                               VARCHAR2(20)
    FDATE                                              DATE
    REMARK                                             VARCHAR2(50)

    在name,fdate上建立复合索引。

    select name,fdate from user_info where fdate=trunc(sysdate)

    这样可以利用到索引,如果查询中还有别的列,那么就不行了!

    一般我们认为like '%xx%'是不会走索引的,可是如果只查询了索引列或count等,还是可以利用索引的!
     
     
       
    jieyancai 该用户已被删除
    8#
     发表于 2007-11-15 20:08 | 只看该作者
    将这四列作一个复合索引
     
     
       
    论坛徽章:
    8
    会员2007贡献徽章日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章日期:2007-10-20 08:38:44生肖徽章2007版:鸡日期:2008-01-02 17:35:53生肖徽章2007版:猴日期:2008-01-02 17:35:53生肖徽章2007版:鼠日期:2008-01-02 17:35:532008新春纪念徽章日期:2008-02-13 12:43:032009新春纪念徽章日期:2009-01-04 14:52:28ITPUB十周年纪念徽章日期:2011-11-01 16:23:26
    9#
     发表于 2007-11-15 21:52 | 只看该作者
    最初由 hanjs 发布
    [B]

    这个要看sql怎么写的。

    SQL> desc user_info
    名称                                      是否为空? 类型
    ----------------------------------------- -------- ----------------------------

    ID                                                 NUMBER
    CODE                                               VARCHAR2(10)
    NAME                                               VARCHAR2(20)
    FDATE                                              DATE
    REMARK                                             VARCHAR2(50)

    在name,fdate上建立复合索引。

    select name,fdate from user_info where fdate=trunc(sysdate)

    这样可以利用到索引,如果查询中还有别的列,那么就不行了!

    一般我们认为like '%xx%'是不会走索引的,可是如果只查询了索引列或count等,还是可以利用索引的! [/B]


    SQL> create table test1(id int,code varchar2(20),zode varchar2(10));

    Table created.

    SQL> begin
      2     for i in 1..1000 loog
      3  /
            for i in 1..1000 loog
                             *
    ERROR at line 2:
    ORA-06550: line 2, column 19:
    PLS-00103: Encountered the symbol "LOOG" when expecting one of the following:
    * & - + / at loop mod remainder rem <an exponent (**)> ||
    multiset


    SQL> begin
      2     for i in 1..1000 loop
      3     insert into test1 values(i,'xxx'||i,'zode'||i);
      4     end loop;
      5  commit;
      6  end;
      7  /

    PL/SQL procedure successfully completed.

    SQL> create index ind_id_code on test1(id,code);

    Index created.

    SQL> set autotrace on
    SQL> set autotrace trace
    SQL> set autotrace
    Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
    SQL> set autotrace trace exp
    SQL> select id,code from test1 where code='xxx10';

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3343096337

    --------------------------------------------------------------------------------

    ----

    | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time
       |

    --------------------------------------------------------------------------------

    ----

    |   0 | SELECT STATEMENT     |             |     1 |    25 |     3   (0)| 00:00:

    01 |

    |*  1 |  INDEX FAST FULL SCAN| IND_ID_CODE |     1 |    25 |     3   (0)| 00:00:

    01 |

    --------------------------------------------------------------------------------

    ----


    Predicate Information (identified by operation id):
    ---------------------------------------------------

       1 - filter("CODE"='xxx10')

    Note
    -----
       - dynamic sampling used for this statement

    SQL>

    是index fast full scan啊,
     
     
       
    论坛徽章:
    8
    会员2007贡献徽章日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章日期:2007-10-20 08:38:44生肖徽章2007版:鸡日期:2008-01-02 17:35:53生肖徽章2007版:猴日期:2008-01-02 17:35:53生肖徽章2007版:鼠日期:2008-01-02 17:35:532008新春纪念徽章日期:2008-02-13 12:43:032009新春纪念徽章日期:2009-01-04 14:52:28ITPUB十周年纪念徽章日期:2011-11-01 16:23:26
    10#
     发表于 2007-11-15 21:59 | 只看该作者
    SQL> select id,code from test1 where id=10;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2428865550

    --------------------------------------------------------------------------------

    | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

    --------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT |             |     1 |    25 |     2   (0)| 00:00:01 |

    |*  1 |  INDEX RANGE SCAN| IND_ID_CODE |     1 |    25 |     2   (0)| 00:00:01 |

    --------------------------------------------------------------------------------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

       1 - access("ID"=10)

    Note
    -----
       - dynamic sampling used for this statement

    SQL>
    如果这么写就是INDEX RANGE SCAN了,
    估计oracle看到sql是只查询id,code这两个字段,index中正好已经包括这两个字段,因此不用scan table了。但是上面一个查询条件是code用不到index 的key因此使用了full index scan,而这个的查询条件是id用到了key,因此使用了index range scan,实际上这两个是不一样的

    个人认为,建index的目的是为了让oracle使用index range scan。个人愚见,望大佬指正。

    展开全文
  • 原文连接地址:http://leeyin.iteye.com/blog/441350什么是索引索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树...如果作为搜索条件的上已经创建索引,MySQL无需扫描任何记录即可迅速得到目标...

    原文连接地址:http://leeyin.iteye.com/blog/441350

    什么是索引?

    索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫

    描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描

    任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍,个人感觉快100倍有点夸张。

    假设我们创建了一个名为people的表:

    CODE:CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

    后,我们完全随机把1000个不同name值插入到people表。

    可以看到,在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引,MySQL将在索引中排序name列:

    对于索引中的每一项,MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此,如果我们要查找name等于“Mike”记录的

    peopleid(SQL命令为“SELECT peopleid FROM people WHERE

    name='Mike';”),MySQL能够在name的索引中查找“Mike”值,然后直接转到数据文件中相应的行,准确地返回该行的

    peopleid(999)。在这个过程中,MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引,MySQL要扫描数据文件中的所有

    记录,即1000个记录!显然,需要MySQL处理的记录数量越少,则它完成任务的速度就越快。

    索引的类型

    MySQL提供多种索引类型供选择:

    普通索引

    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:

    CODE:创建索引,例如CREATE INDEX ; ONtablename (列的列表);

    修改表,例如ALTERTABLE tablename ADD INDEX [索引的名字](列的列表);

    创建表的时候指定索引,例如CREATETABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

    唯一性索引

    这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:

    CODE:创建索引,例如CREATE UNIQUE INDEX ; ONtablename (列的列表);

    修改表,例如ALTERTABLE tablename ADD UNIQUE [索引的名字](列的列表);

    创建表的时候指定索引,例如CREATETABLE tablename ( [...], UNIQUE [索引的名字](列的列表)

    );

    主键

    主键是一种唯一性索引,但它必须指定为“PRIMARY

    KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATE

    TABLE tablename ( [...], PRIMARY KEY (列的列表) );

    ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表);

    ”。每个表只能有一个主键。

    全文索引

    MySQL从3.23.23版开始支持全文索引和全文检索。在

    MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE

    TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER

    TABLE(或者CREATE

    INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,要了解更多信息,请参见MySQL

    documentation。

    单列索引与多列索引

    索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表:

    CODE:CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50)NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT

    NULL, PRIMARY KEY (peopleid) );

    下面是我们插入到这个people表的数据:

    这个数据片段中有四个名字为“Mikes”的人(其中两个姓Sullivans,两个姓McConnells),有两个年龄为17岁的人,还有一个名字与众不同的Joe Smith。

    这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如,我们可能需要查找姓名为Mike

    Sullivan、年龄17岁用户的peopleid(SQL命令为SELECT peopleid FROM people WHERE

    firstname='Mike' AND lastname='Sullivan' AND

    age=17;)。由于我们不想让MySQL每次执行查询就去扫描整个表,这里需要考虑运用索引。

    首先,我们可以考虑在单个列上创建索引,比如firstname、lastname或者age列。如果我们创建firstname列的索引

    (ALTER TABLE people ADD INDEX firstname

    (firstname);),MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录,然后再在这个“中间结果集”上

    进行其他条件的搜索:它首先排除那些lastname不等于“Sullivan”的记录,然后排除那些age不等于17的记录。当记录满足所有搜索条件之

    后,MySQL就返回最终的搜索结果。

    由于建立了firstname列的索引,与执行表的完全扫描相比,MySQL的效率提高了很多,但我们要求MySQL扫描的记录数量仍旧远远超过

    了实际所需要的。虽然我们可以删除firstname列上的索引,再创建lastname或者age

    列的索引,但总地看来,不论在哪个列上创建索引搜索效率仍旧相似。

    为了提高搜索效率,我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引,MySQL只需一次检索就能够找出正确的结果!下面是创建这个多列索引的SQL命令:

    CODE:ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

    由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后再转到合适的lastname,最后转到合适的age。在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录!

    那么,如果在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、

    lastname、age

    的多列索引一样呢?答案是否定的,两者完全不同。当我们执行查询的时候,MySQL只能使用一个索引。如果你有三个单列的索引,MySQL会试图选择一个

    限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

    最左前缀

    多列索引还有另外一个优点,它通过称为最左前缀(Leftmost

    Prefixing)的概念体现出来。继续考虑前面的例子,现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引

    为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:

    CODE:firstname,lastname,age

    firstname,lastname

    firstname

    从另一方面理解,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引:

    CODE:SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' ANDage='17'; SELECT peopleid FROM people WHERE firstname='Mike' ANDlastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The

    following queries cannotuse the index at all: SELECT peopleid FROM people WHERElastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECTpeopleidFROM people WHERE lastname='Sullivan' AND age='17';

    选择索引列

    在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列。请看下面这个查询:

    CODE:SELECT age ## 不使用索引 FROM people WHERE firstname='Mike' ## 考虑使用索引 AND

    lastname='Sullivan' ## 考虑使用索引

    这个查询与前面的查询略有不同,但仍属于简单查询。由于age是在SELECT部分被引用,MySQL不会用它来限制列选择操作。因此,对于这个查询来说,创建age列的索引没有什么必要。下面是一个更复杂的例子:

    CODE:SELECT people.age, ##不使用索引 town.name ##不使用索引 FROM people LEFT JOIN town ONpeople.townid=town.townid ##考虑使用索引 WHERE firstname='Mike' ##考虑使用索引 ANDlastname='Sullivan' ##考虑使用索引

    与前面的例子一样,由于firstname和lastname出现在WHERE子句中,因此这两个列仍旧有创建索引的必要。除此之外,由于town表的townid列出现在join子句中,因此我们需要考虑创建该列的索引。

    那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此,但并不完全。我们还必须考虑到对列进行比较

    的操作符类型。MySQL只有对以下操作符才使用索引:;,>;=,BETWEEN,IN,以及某些时候的

    LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT peopleid FROM

    people WHERE firstname LIKE 'Mich%';”这个查询将使用索引,但“SELECT peopleid FROM

    people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

    分析索引效率

    现在我们已经知道了一些如何选择索引列的知识,但还无法判断哪一个最有效。MySQL提供了一个内建的SQL命令帮助我们完成这个任务,这就是

    EXPLAIN命令。EXPLAIN命令的一般语法是:EXPLAIN

    ;。你可以在MySQL文档找到有关该命令的更多说明。下面是一个例子:

    CODE:EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'

    AND age='17';

    这个命令将返回下面这种分析结果:

    table type possible_keys keykey_len ref rows Extra

    people ref fname_lname_age fname_lname_age102 const,const,const 1 Where used

    下面我们就来看看这个EXPLAIN分析结果的含义。

    table:这是表的名字。

    type:连接操作的类型。下面是MySQL文档关于ref连接类型的说明:

    “对于每一种与另一个表中记录的组合,MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀,或者如果键不是

    UNIQUE或 PRIMARY

    KEY类型(换句话说,如果连接操作不能根据键值选择出唯一行),则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录,则ref是一

    种好的连接类型。”

    在本例中,由于索引不是UNIQUE类型,ref是我们能够得到的最好连接类型。

    如果EXPLAIN显示连接类型是“ALL”,而且你并不想从表里面选择出大多数记录,那么MySQL的操作效率将非常低,因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息,请参见MySQL的手册说明。

    possible_keys:

    可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称;如果索引没有昵称,则默认显示的是索引中第一个列的名字(在本例中,它是“firstname”)。默认索引名字的含义往往不是很明显。

    Key:

    它显示了MySQL实际使用的索引的名字。如果它为空(或NULL),则MySQL不使用索引。

    key_len:

    索引中被使用部分的长度,以字节计。在本例中,key_len是102,其中firstname占50字节,lastname占50字节,age占2字节。如果MySQL只使用索引中的firstname部分,则key_len将是50。

    ref:

    它显示的是列的名字(或单词“const”),MySQL将根据这些列来选择行。在本例中,MySQL根据三个常量选择行。

    rows:

    MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然,这里最理想的数字就是1。

    Extra:

    这里可能出现许多不同的选项,其中大多数将对查询产生负面影响。在本例中,MySQL只是提醒我们它将用WHERE子句限制搜索结果集。

    索引的缺点

    到目前为止,我们讨论的都是索引的优点。事实上,索引也是有缺点的。

    首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能列组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表,索引文件的大小可能达到操作系统允许的最大文件限制。

    第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。

    【结束语】在大型数据库中,索引是提高速度的一个关键因素。不管表的结构是多么简单,一次500000行的表扫描操作无论如何不会快。如果你的网

    站上也有这种大规模的表,那么你确实应该花些时间去分析可以采用哪些索引,并考虑是否可以改写查询以优化应用。要了解更多信息,请参见MySQL

    manual。另外注意,本文假定你所使用的MySQL是3.23版,部分查询不能在3.22版MySQL上执行。

    展开全文
  • 原文连接地址:http://leeyin.iteye.com/blog/441350什么是索引索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树...如果作为搜索条件的上已经创建索引,MySQL无需扫描任何记录即可迅速得到目标...

    原文连接地址:http://leeyin.iteye.com/blog/441350

    什么是索引?

    索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫

    描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描

    任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍,个人感觉快100倍有点夸张。

    假设我们创建了一个名为people的表:

    CODE:CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

    后,我们完全随机把1000个不同name值插入到people表。

    可以看到,在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引,MySQL将在索引中排序name列:

    对于索引中的每一项,MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此,如果我们要查找name等于“Mike”记录的

    peopleid(SQL命令为“SELECT peopleid FROM people WHERE

    name='Mike';”),MySQL能够在name的索引中查找“Mike”值,然后直接转到数据文件中相应的行,准确地返回该行的

    peopleid(999)。在这个过程中,MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引,MySQL要扫描数据文件中的所有

    记录,即1000个记录!显然,需要MySQL处理的记录数量越少,则它完成任务的速度就越快。

    索引的类型

    MySQL提供多种索引类型供选择:

    普通索引

    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:

    CODE:创建索引,例如CREATE INDEX ; ONtablename (列的列表);

    修改表,例如ALTERTABLE tablename ADD INDEX [索引的名字](列的列表);

    创建表的时候指定索引,例如CREATETABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

    唯一性索引

    这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:

    CODE:创建索引,例如CREATE UNIQUE INDEX ; ONtablename (列的列表);

    修改表,例如ALTERTABLE tablename ADD UNIQUE [索引的名字](列的列表);

    创建表的时候指定索引,例如CREATETABLE tablename ( [...], UNIQUE [索引的名字](列的列表)

    );

    主键

    主键是一种唯一性索引,但它必须指定为“PRIMARY

    KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATE

    TABLE tablename ( [...], PRIMARY KEY (列的列表) );

    ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表);

    ”。每个表只能有一个主键。

    全文索引

    MySQL从3.23.23版开始支持全文索引和全文检索。在

    MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE

    TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER

    TABLE(或者CREATE

    INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,要了解更多信息,请参见MySQL

    documentation。

    单列索引与多列索引

    索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表:

    CODE:CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50)NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT

    NULL, PRIMARY KEY (peopleid) );

    下面是我们插入到这个people表的数据:

    这个数据片段中有四个名字为“Mikes”的人(其中两个姓Sullivans,两个姓McConnells),有两个年龄为17岁的人,还有一个名字与众不同的Joe Smith。

    这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如,我们可能需要查找姓名为Mike

    Sullivan、年龄17岁用户的peopleid(SQL命令为SELECT peopleid FROM people WHERE

    firstname='Mike' AND lastname='Sullivan' AND

    age=17;)。由于我们不想让MySQL每次执行查询就去扫描整个表,这里需要考虑运用索引。

    首先,我们可以考虑在单个列上创建索引,比如firstname、lastname或者age列。如果我们创建firstname列的索引

    (ALTER TABLE people ADD INDEX firstname

    (firstname);),MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录,然后再在这个“中间结果集”上

    进行其他条件的搜索:它首先排除那些lastname不等于“Sullivan”的记录,然后排除那些age不等于17的记录。当记录满足所有搜索条件之

    后,MySQL就返回最终的搜索结果。

    由于建立了firstname列的索引,与执行表的完全扫描相比,MySQL的效率提高了很多,但我们要求MySQL扫描的记录数量仍旧远远超过

    了实际所需要的。虽然我们可以删除firstname列上的索引,再创建lastname或者age

    列的索引,但总地看来,不论在哪个列上创建索引搜索效率仍旧相似。

    为了提高搜索效率,我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引,MySQL只需一次检索就能够找出正确的结果!下面是创建这个多列索引的SQL命令:

    CODE:ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

    由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后再转到合适的lastname,最后转到合适的age。在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录!

    那么,如果在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、

    lastname、age

    的多列索引一样呢?答案是否定的,两者完全不同。当我们执行查询的时候,MySQL只能使用一个索引。如果你有三个单列的索引,MySQL会试图选择一个

    限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

    最左前缀

    多列索引还有另外一个优点,它通过称为最左前缀(Leftmost

    Prefixing)的概念体现出来。继续考虑前面的例子,现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引

    为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:

    CODE:firstname,lastname,age

    firstname,lastname

    firstname

    从另一方面理解,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引:

    CODE:SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' ANDage='17'; SELECT peopleid FROM people WHERE firstname='Mike' ANDlastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The

    following queries cannotuse the index at all: SELECT peopleid FROM people WHERElastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECTpeopleidFROM people WHERE lastname='Sullivan' AND age='17';

    选择索引列

    在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列。请看下面这个查询:

    CODE:SELECT age ## 不使用索引 FROM people WHERE firstname='Mike' ## 考虑使用索引 AND

    lastname='Sullivan' ## 考虑使用索引

    这个查询与前面的查询略有不同,但仍属于简单查询。由于age是在SELECT部分被引用,MySQL不会用它来限制列选择操作。因此,对于这个查询来说,创建age列的索引没有什么必要。下面是一个更复杂的例子:

    CODE:SELECT people.age, ##不使用索引 town.name ##不使用索引 FROM people LEFT JOIN town ONpeople.townid=town.townid ##考虑使用索引 WHERE firstname='Mike' ##考虑使用索引 ANDlastname='Sullivan' ##考虑使用索引

    与前面的例子一样,由于firstname和lastname出现在WHERE子句中,因此这两个列仍旧有创建索引的必要。除此之外,由于town表的townid列出现在join子句中,因此我们需要考虑创建该列的索引。

    那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此,但并不完全。我们还必须考虑到对列进行比较

    的操作符类型。MySQL只有对以下操作符才使用索引:;,>;=,BETWEEN,IN,以及某些时候的

    LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT peopleid FROM

    people WHERE firstname LIKE 'Mich%';”这个查询将使用索引,但“SELECT peopleid FROM

    people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

    分析索引效率

    现在我们已经知道了一些如何选择索引列的知识,但还无法判断哪一个最有效。MySQL提供了一个内建的SQL命令帮助我们完成这个任务,这就是

    EXPLAIN命令。EXPLAIN命令的一般语法是:EXPLAIN

    ;。你可以在MySQL文档找到有关该命令的更多说明。下面是一个例子:

    CODE:EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'

    AND age='17';

    这个命令将返回下面这种分析结果:

    table type possible_keys keykey_len ref rows Extra

    people ref fname_lname_age fname_lname_age102 const,const,const 1 Where used

    下面我们就来看看这个EXPLAIN分析结果的含义。

    table:这是表的名字。

    type:连接操作的类型。下面是MySQL文档关于ref连接类型的说明:

    “对于每一种与另一个表中记录的组合,MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀,或者如果键不是

    UNIQUE或 PRIMARY

    KEY类型(换句话说,如果连接操作不能根据键值选择出唯一行),则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录,则ref是一

    种好的连接类型。”

    在本例中,由于索引不是UNIQUE类型,ref是我们能够得到的最好连接类型。

    如果EXPLAIN显示连接类型是“ALL”,而且你并不想从表里面选择出大多数记录,那么MySQL的操作效率将非常低,因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息,请参见MySQL的手册说明。

    possible_keys:

    可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称;如果索引没有昵称,则默认显示的是索引中第一个列的名字(在本例中,它是“firstname”)。默认索引名字的含义往往不是很明显。

    Key:

    它显示了MySQL实际使用的索引的名字。如果它为空(或NULL),则MySQL不使用索引。

    key_len:

    索引中被使用部分的长度,以字节计。在本例中,key_len是102,其中firstname占50字节,lastname占50字节,age占2字节。如果MySQL只使用索引中的firstname部分,则key_len将是50。

    ref:

    它显示的是列的名字(或单词“const”),MySQL将根据这些列来选择行。在本例中,MySQL根据三个常量选择行。

    rows:

    MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然,这里最理想的数字就是1。

    Extra:

    这里可能出现许多不同的选项,其中大多数将对查询产生负面影响。在本例中,MySQL只是提醒我们它将用WHERE子句限制搜索结果集。

    索引的缺点

    到目前为止,我们讨论的都是索引的优点。事实上,索引也是有缺点的。

    首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能列组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表,索引文件的大小可能达到操作系统允许的最大文件限制。

    第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。

    【结束语】在大型数据库中,索引是提高速度的一个关键因素。不管表的结构是多么简单,一次500000行的表扫描操作无论如何不会快。如果你的网

    站上也有这种大规模的表,那么你确实应该花些时间去分析可以采用哪些索引,并考虑是否可以改写查询以优化应用。要了解更多信息,请参见MySQL

    manual。另外注意,本文假定你所使用的MySQL是3.23版,部分查询不能在3.22版MySQL上执行。

    展开全文
  • CODE:CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );...如果我们创建了name索引,MySQL将在索引中排序name:对于索引中的每一项,MySQL在内部为它保存一个数据文件...
  • 什么是索引索引用来快速地寻找那些具有特定值的...如果作为搜索条件的上已经创建索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描...
  • 执行explain之后的截图如下:可以看到possible_keys中有很多是之前无用的index,并没有按预想的多列索引status,source_user_id,type来查询,于是果断去掉了多余的索引,执行explain后截图:可以看到虽然用...
  • 什么是索引索引用来快速地寻找那些具有特定值...如果作为搜索条件的上已经创建索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100...
  • 索引的三星原则1....一个常见的错误就是,为每个列建立独立的索引,或者按照错误的顺序创建多列索引。我们会在稍后的章节中单独讨论索引列的顺序问题。先来看第一个问题,为每个列创建独立的索引,从SHOW C...
  • 多列索引,是指在创建索引时所关联的字段不是一个字段,而是多个字段,虽然可以通过所关联的字段进行查询,但是只有查询条件中使用了所关联字段中的第一个字段,多列索引才会被使用。 创建表时定义索引 CREATE TABLE...
  • 查看索引 show index from 数据库表名 alter table 数据库add index 索引名称(数据库字段名称) PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ...
  • 创建单列索引,多列索引

    千次阅读 2018-11-14 19:17:48
    单列索引: CREATE TABLE t_user ( id INT, username VARCHAR(20), PASSWORD VARCHAR(20), ...多列索引: CREATE TABLE t_user1 ( id INT, username VARCHAR(20), PASSWORD VARCHAR(20), INDEX index...
  • 查看索引 show index from 数据库表名alter table 数据库add index 索引名称(数据库字段名称)PRIMARY KEY(主键索引)ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )UNIQUE(唯一索引)ALTER TABLE `table_name...
  • 1.创建多层行索引 (1) 隐式构造 最常见的方法是给DataFrame构造函数的index参数传递两个或更的数组 -- Series也可以创建多层索引 import numpy as np import matplotlib.pyplot as plt import pandas as ...
  • 创建和查看多列索引

    2016-07-31 16:37:56
    注释:所谓多列索引,是指在创建索引时,所关联的字段不是一个字段,而是多个字段。虽然可以通过所关联的字段进行查询,但是只要查询条件中使用了所关联字段中的第一个字段,多列索引才会被使用。1.create table ...
  • 索引的三星原则1....一个常见的错误就是,为每个列建立独立的索引,或者按照错误的顺序创建多列索引。我们会在稍后的章节中单独讨论索引列的顺序问题。先来看第一个问题,为每个列创建独立的索引,从SHOW C...
  • 多列索引不是为每个列创建独立的索引,或者按照错误的顺序创建多列索引;在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0及以上版本引入了一种叫“索引合并”(index merge)的策略...
  • 我有桌子create table1(column1 number(10,column2 number...column1是主键column2和column3是外键我在2创建了唯一约束alter table table1add constraint table1_contr1 unique(column1,column2)using index tab...
  • 讨论MySQL选择索引时单列单列索引和多列索引使用,以及多列索引的最左前缀原则。1. 单列索引在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在Where子句中...
  • 只有唯一索引才真正提高速度,一般的索引只能提高30%左右讨论MySQL选择索引时单列单列索引和多列索引使用,以及多列索引的最左前缀原则。1. 单列索引在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。...
  • 索引的三星原则 1.索引将相关的记录放到一起,则获得一星 ...一个常见的错误就是,为每个列建立独立的索引,或者按照错误的顺序创建多列索引。  我们会在稍后的章节中单独讨论索引列的顺序问题。先来看第一...
  • 创建一个多列索引:CREATE TABLE test (id INT NOT NULL,last_name CHAR(30) NOT NULL,first_name CHAR(30) NOT NULL,PRIMARY KEY (id),INDEX name (last_name,first_name));创建多个索引:CREATE TABLE test (id I....

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,974
精华内容 2,389
关键字:

创建多列索引