精华内容
下载资源
问答
  • 复合索引(又称为联合索引),在多个列上创建的索引。创建复合索引最重要的列顺序的选择,这关系到索引能否使用上,或者影响多少个谓词条件能使用上索引。复合索引的使用遵循最左匹配原则,只有索引左边的列匹配到...

    复合索引(又称为联合索引),是在多个列上创建的索引。创建复合索引最重要的是列顺序的选择,这关系到索引能否使用上,或者影响多少个谓词条件能使用上索引。复合索引的使用遵循最左匹配原则,只有索引左边的列匹配到,后面的列才能继续匹配。本文主要探究复合索引的创建顺序与使用情况。

    (一)复合索引的概念

    在单个列上创建的索引我们称为单列索引,在2个以上的列上创建的索引称为复合索引。在单个列上创建索引相对简单,通常只需要考虑列的选择率即可,选择性越好,代表数据越分散,创建出来的索引性能也就更好。通常,某列选择率的计算公式为:

    selectivity = 施加谓词条件后返回的记录数 / 未施加谓词条件后返回的记录数

    可选择率的取值范围是(0,1],值越小,代表选择性越好。

    对于复合索引(又称为联合索引),是在多个列上创建的索引。创建复合索引最重要的是列顺序的选择,这关系到索引能否使用上,或者影响多少个谓词条件能使用上索引。复合索引的使用遵循最左匹配原则,只有索引左边的列匹配到,后面的列才能继续匹配。

    (二)什么情况下会使用复合索引的列

    复合索引遵循最左匹配原则,只有索引中最左列匹配到,下一列才有可能被匹配。如果左边列使用的是非等值查询,则索引右边的列将不会被查询使用,也不会被排序使用。

    实验:哪些情况下会使用到复合索引

    复合索引中的哪些字段被使用到了,是我们非常关心的问题。网络上一个经典的例子:

    --创建测试表

    CREATE TABLEt1(

    c1CHAR(1) not null,

    c2CHAR(1) not null,

    c3CHAR(1) not null,

    c4CHAR(1) not null,

    c5CHAR(1) not null)ENGINE innodb CHARSET UTF8;--添加索引

    alter table t1 add indexidx_c1234(c1,c2,c3,c4);--插入测试数据

    insert into t1 values('1','1','1','1','1'),('2','2','2','2','2'),

    ('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');

    需要探索下面哪些查询语句使用到了索引idx_c1234,以及使用到了索引的哪些字段?

    (A) where c1=? and c2=? and c4>? and c3=?

    (B) where c1=? and c2=? and c4=? order by c3

    (C) where c1=? and c4=? group by c3,c2

    (D) where c1=? and c5=? order by c2,c3

    (E) where c1=? and c2=? and c5=? order by c2,c3

    (F) where c1>? and c2=? and c4>? and c3=?

    A选项:

    mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4>'1' and c3='2';+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

    | 1 | SIMPLE | t1 | NULL | range | idx_c1234 | idx_c1234 | 12 | NULL | 1 | 100.00 | Using index condition |

    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

    使用的索引长度为12,代表4个字段都使用了索引。由于c1、c2、c3都是等值查询,所以后面的c4列也可以用上。

    注:utf8编码,一个索引长度为3,这里12代表4个字段都用到该索引。

    B选项:

    mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4='2' order byc3;+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 1 | 20.00 | Using index condition |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+

    使用的索引长度为6,代表2个字段使用了索引。根据最左使用原则,c1、c2使用了索引。因为查询中没有c3谓词条件,所以索引值使用到c2后就发生了中断,导致只使用了c1、c2列。这里SQL使用了order by排序,但是在执行计划Extra部分未有filesort关键字,说明在索引中按照c3字段顺序读取数据即可。

    这里特别留意,虽然索引中的c3字段没有放在索引的最后,但是确实使用到了索引中c2字段的有序特性,因为执行计划的Extra部分未出现"fileasort"关键字。这是为什么呢?这里用到了MySQL5.6版本引入的Index Condition Pushdown (ICP) 优化。其核心思想是使用索引中的字段做数据过滤。我们来整理一下不使用ICP和使用ICP的区别:

    如果没有使用ICP优化,其SQL执行步骤为:

    1.使用索引列c1,c2获取满足条件的行数据。where c1='2' and c2='2'

    2.回表查询数据,使用where c4='2'来过滤数据

    3.对数据排序输出

    如果使用了ICP优化,其SQL执行步骤为:

    1.使用索引列c1,c2获取满足条件的行数据。where c1='2' and c2='2'

    2.在索引中使用where c4='2'来过滤数据

    3.因为数据有序,直接按顺序取出满足条件的数据

    C选项:

    mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group byc3,c2;+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using where; Using index; Using temporary; Using filesort |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+

    使用的索引长度为3,代表1个字段使用了索引。根据最左使用原则,c1使用了索引。因为查询中没有c2谓词条件,所以索引值使用到c1后就发生了中断,导致只使用了c1列。该SQL执行过程为:

    1.在c1列使用索引找到c1='2'的所有行,然后回表使用c4='2'过滤掉不匹配的数据

    2.根据上一步的结果,对结果中的c3,c2联合排序,以便于得到连续变化的数据,同时在数据库内部创建临时表,用于存储group by的结果。

    C选项扩展:

    mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group byc2,c3;+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using where; Using index |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+

    使用的索引长度为3,代表1个字段使用了索引。根据最左使用原则,c1使用了索引。

    D选项:

    mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order byc2,c3;+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using index condition; Using where |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+

    使用的索引长度为3,代表1个字段都使用了索引。根据最左使用原则,c1使用了索引。因为查询中没有c2谓词条件,所以索引值使用到c1后就发生了中断,导致只使用了c1列。

    D选项扩展:

    mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order byc3,c2;+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using index condition; Using where; Using filesort |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+

    使用的索引长度为3,代表1个字段都使用了索引。根据最左使用原则,c1使用了索引。因为查询中没有c2谓词条件,所以索引值使用到c1后就发生了中断,导致只使用了c1列。

    E选项:

    mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c5='2' order byc2,c3;+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 2 | 14.29 | Using index condition; Using where |

    +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+

    使用的索引长度为6,代表2个字段都使用了索引。根据最左使用原则,c1、c2使用了索引。这里SQL使用了order by排序,但是在执行计划Extra部分未有filesort关键字,说明在索引中按照c3字段顺序读取数据即可(c2是常量)。

    F选项:

    mysql> explain select c1,c2,c3,c4,c5 from t1 where c1>'4' and c2='2' and c3='2' and c4='1';+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

    | 1 | SIMPLE | t1 | NULL | range | idx_c1234 | idx_c1234 | 3 | NULL | 1 | 20.00 | Using index condition |

    +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

    使用的索引长度为3,代表1个字段都使用了索引。根据最左使用原则,c1使用了索引。这里c1使用了不等值查询,导致后面的c2查询无法使用索引。该案例非常值得警惕,谓词条件中含有等值查询和范围查询时,如果范围查询在索引前面,则等值查询将无法使用索引;如果等值查询在前面,范围查询在后面,则都可以使用到索引。

    (三)如何创建复合索引

    复合索引创建的难点在于字段顺序选择,我的观点如下:

    如果存在等值查询和排序,则在创建复合索引时,将等值查询字段放在前面,排序放在最后面;

    如果存在多个等值查询,则选择性好的放在前面,选择性差的放在后面;

    如果存在等值查询、范围查询、排序。等值查询放在最前面,范围查询和排序需根据实际情况决定索引顺序;

    此外,《阿里巴巴Java开发手册-2020最新嵩山版》中有几个关于复合索引的规约,我们可以看一下:

    1.如果有order by的场景,请注意利用索引的有序性。order by后的字段是组合索引的一部分,并且放在组合索引的最后,避免出现filesort的情况,影响查询性能。

    正例:where a=? b=? order by c; 索引a_b_c

    反例:索引如果存在范围查询,那么索引有序性将无法使用。如:where a>10 order by b; 索引a_b无法排序。

    2.建复合索引的时候,区分度最高的在最左边,如果where a=? and b=?,a列的值几乎接近唯一值,那么只需建单列索引idx_a即可。

    说明:存在等号和非等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where c>? and d=?,那么即使c的区分度更高,也必须把d放在索引的最前列,即创建索引idx_d_c。

    实验:应该如何创建复合索引

    在有的文档里面讲到过复合索引的创建规则:ESR原则:精确(Equal)匹配的字段放在最前面,排序(Sort)条件放中间,范围(Range)匹配的字段放在最后面。接下来我们来探索一下该方法是否正确。

    例子:存在员工表employees

    mysql> show create tableemployees;+-----------+-------------------------------

    | Table | Create Table

    +-----------+-------------------------------------

    | employees | CREATE TABLE`employees` (

    `emp_no`int(11) NOT NULL,

    `birth_date` dateNOT NULL,

    `first_name`varchar(14) NOT NULL,

    `last_name`varchar(16) NOT NULL,

    `gender` enum('M','F') NOT NULL,

    `hire_date` dateNOT NULL,PRIMARY KEY(`emp_no`)

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

    +-----------+-------------------------------------

    --数据量约30万行

    mysql> select count(*) fromemployees;+----------+

    | count(*) |

    +----------+

    | 300024 |

    +----------+

    现在需要查询1998年后入职的first_name为"Ebbe"员工,并按照出生日期升序排序。

    其SQL语句如下:

    selectemp_no,birth_date,first_name,last_name,gender,hire_datefromemployeeswhere hire_date >= '1998-01-01'

    and first_name = 'Ebbe'

    order by birth_date;

    为了优化该SQL语句的性能,需要在表上创建索引,为了保证where与order by都使用到索引,决定创建复合索引,有如下创建顺序:

    (A)hire_date,first_name,birth_date

    (B)hire_date,birth_date,first_name

    (C)first_name,hire_date,birth_date

    (D)first_name,birth_date,hire_date

    (E)birth_date,first_name,hire_date

    (F)birth_date,hire_date,first_name

    确认哪种顺序创建索引是最优的。

    Note:

    1.date类型占3个字节的空间,hire_date和 birth_date都占用3个字节的空间。

    2.first_name是变长字段,多使用2个字节,如果允许为NULL值,还需多使用1个字节,占用16个字节

    A选项:hire_date,first_name,birth_date

    create index idx_a on employees(hire_date,first_name,birth_date);

    其执行计划如下:

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    | 1 | SIMPLE | employees | NULL | range | idx_a | idx_a | 19 | NULL | 5678 | 10.00 | Using index condition; Using filesort |

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    这里key_len长度为19,令人不解,hire_date是非等值查询,理论上key_len应该为3,通过使用MySQL workbench查看执行计划,也可以发现索引只使用了hire_date列(如下图)。为什么会是19而不是3呢?实在令人费解,思考了好久也没有想明白,如有知道,望各位大神不吝解答。

    25739c9012f8905c97472d3b53da01fa.png

    B选项:hire_date,birth_date,first_name

    为避免干扰,删除上面创建的索引idx_a,然后创建idx_b。

    create index idx_b on employees(hire_date,birth_date,first_name);

    其执行计划如下:

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    | 1 | SIMPLE | employees | NULL | range | idx_b | idx_b | 3 | NULL | 5682 | 10.00 | Using index condition; Using filesort |

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    这里key_len长度为3,hire_date是非等值查询,导致后面的索引列无法使用到。

    C选项:first_name,hire_date,birth_date

    为避免干扰,删除上面创建的索引idx_b,然后创建idx_c。

    create index idx_c on employees(first_name,hire_date,birth_date);

    其执行计划如下:

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    | 1 | SIMPLE | employees | NULL | range | idx_c | idx_c | 19 | NULL | 5 | 100.00 | Using index condition; Using filesort |

    +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+

    这里key_len长度为19,first_name是等值查询,可以继续使用hire_date列,因为hire_date列是非等值查询,导致索引无法继续使用birth_date。

    D选项:first_name,birth_date,hire_date

    为避免干扰,删除上面创建的索引idx_c,然后创建idx_d。

    create index idx_d on employees(first_name,birth_date,hire_date);

    其执行计划如下:

    +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

    | 1 | SIMPLE | employees | NULL | ref | idx_d | idx_d | 16 | const | 190 | 33.33 | Using index condition |

    +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

    这里key_len长度为16,first_name是等值查询,在谓词过滤中未使用birth_date,导致只有first_name列使用上索引,但是birth_date列用于排序,上面执行计划显示SQL最终并没有排序,说明数据是从索引按照birth_date有序取出的。

    E选项:birth_date,first_name,hire_date

    为避免干扰,删除上面创建的索引idx_d,然后创建idx_e。

    create index idx_e on employees(birth_date,first_name,hire_date);

    其执行计划如下:

    +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

    | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 3.33 | Using where; Using filesort |

    +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

    这里未使用到索引,说明排序列放在复合索引的最前面是无法被使用到的。

    F选项:birth_date,hire_date,first_name

    为避免干扰,删除上面创建的索引idx_e,然后创建idx_f。

    create index idx_f on employees(birth_date,hire_date,first_name);

    其执行计划如下:

    +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

    | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 3.33 | Using where; Using filesort |

    +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

    与E选项一样,这里未使用到索引,说明排序列放在复合索引的最前面是无法被使用到的。

    通过上面的6个索引测试,我们发现,等值查询列和范围查询列放在复合索引前面,复合索引都能被使用到,只是使用到的列可能不一样。哪种方式创建索引最好呢?MySQL的查询优化器是基于开销(cost)来选择最优的执行计划的,我们不妨来看看上面的6个索引的执行开销。

    索引 开销cost---------- ------------

    idx_a 8518idx_b8524idx_c13idx_d228idx_e78083idx_f78083

    通过上面的开销,可以看到:

    idx_a和idx_b:索引使用范围查询字段开头,导致索引只能使用到第一列,无法消除排序,导致开销较大;

    idx_c和idx_d:索引使用等值查询字段开头,范围查询和排序位于后面,开销是最小的;

    idx_e和idx_f :索引使用排序字段开头,导致索引无法被使用到,走的全表扫描,开销巨大。

    更进一步,idx_c和idx_d如何选择呢?idx_c使用索引进行等值查询+范围查询,然后对数据进行排序;idx_d使用索引进行等值查询+索引条件下推查询,然后按照顺序直接获取数据。两种方式各有优劣,我们不妨再来看一个例子:

    把上面6个索引都加到表上,看看如下SQL会选择哪个索引。

    mysql> show index fromemployees;+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

    +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

    | employees | 0 | PRIMARY | 1 | emp_no | A | 299468 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_a | 1 | hire_date | A | 5355 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_a | 2 | first_name | A | 290745 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_a | 3 | birth_date | A | 299468 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_b | 1 | hire_date | A | 6237 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_b | 2 | birth_date | A | 297591 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_b | 3 | first_name | A | 299468 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_c | 1 | first_name | A | 1260 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_c | 2 | hire_date | A | 293517 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_c | 3 | birth_date | A | 299468 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_d | 1 | first_name | A | 1218 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_d | 2 | birth_date | A | 294525 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_d | 3 | hire_date | A | 298095 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_e | 1 | birth_date | A | 4767 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_e | 2 | first_name | A | 292761 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_e | 3 | hire_date | A | 299468 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_f | 1 | birth_date | A | 4767 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_f | 2 | hire_date | A | 297864 | NULL | NULL | | BTREE | | |

    | employees | 1 | idx_f | 3 | first_name | A | 299468 | NULL | NULL | | BTREE | | |

    +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

    SQL1

    mysql> explain selectemp_no,birth_date,first_name,last_name,gender,hire_datefromemployeeswhere hire_date >= '1998-01-01'

    and first_name = 'Ebbe'

    order bybirth_date;+----+-------------+-----------+------------+-------+-------------------------+-------+---------+------+------+----------+---------------------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+-------+-------------------------+-------+---------+------+------+----------+---------------------------------------+

    | 1 | SIMPLE | employees | NULL | range | idx_a,idx_b,idx_c,idx_d | idx_c | 19 | NULL | 5 | 100.00 | Using index condition; Using filesort |

    +----+-------------+-----------+------------+-------+-------------------------+-------+---------+------+------+----------+---------------------------------------+

    这里MySQL自动选择了idx_c,是因为first_name+hire_date两个字段已经将数据过滤了只有5行,由于数据少,排序非常快。反之,如果选择idx_d,则需要先通过first_name字段过滤出符合条件的190行数据,然后再使用hire_date筛选数据,工作量较大。

    SQL2

    mysql> explain selectemp_no,birth_date,first_name,last_name,gender,hire_datefromemployeeswhere hire_date >= '1980-01-01'

    and first_name = 'Ebbe'

    order bybirth_date;+----+-------------+-----------+------------+------+-------------------------+-------+---------+-------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-----------+------------+------+-------------------------+-------+---------+-------+------+----------+-----------------------+

    | 1 | SIMPLE | employees | NULL | ref | idx_a,idx_b,idx_c,idx_d | idx_d | 16 | const | 190 | 50.00 | Using index condition |

    +----+-------------+-----------+------------+------+-------------------------+-------+---------+-------+------+----------+-----------------------+

    如果选择idx_c,first_name+hire_date两个字段通过索引过滤数据之后,数据量较大,导致排序非常慢。MySQL自动选择了idx_d,通过索引的first_name列过滤数据,并通过索引条件下推过滤hire_date字段,然后从索引中有序的取出数据,相对来说,由于使用idx_d无需排序,速度会更快。

    (四)复合索引总结

    1.复合索引的创建,如果存在多个等值查询,则将选择性好的列放在最前面,选择性差的列放在后面;

    2.复合索引的创建,如果涉及到等值查询和范围查询,不管非等值查询的列的选择性如何好,等值查询的字段要放在非等值查询的前面;

    3.复合索引的创建,如果涉及到等值查询和范围查询和排序(order by、group by),则等值查询放在索引最前面,范围查询和排序哪个在前,哪个在后,需要根据实际场景决定。如果范围查询在前,则无法使用到索引的有序性,需filesort,适用于返回结果较少的SQL,因为结果少则排序开销小;如果排序在前,则可以使用到索引的有序性,但是需要回表(或者索引条件下推)去查询数据,适用于返回结果较多的SQL,因为无需排序,直接取出数据。

    4.复合索引的创建,一定不能把order by、group by的列放在索引的最前面,因为查询中总是where先于order by执行;

    5.使用索引进行范围查询会导致后续索引字段无法被使用,如果有排序,无法消除filesort排序。例子:a_b_c索引,where a>? and b = ? order by c,则a可以被使用到,b无法被使用,c字段需filesort。

    【完】

    Note:作者是一枚MySQL菜鸟,文章仅代表个人观点,如有不对,敬请指出,谢谢。

    展开全文
  • 复合索引

    2018-05-27 23:31:23
    概要什么是单一索引,什么又是复合索引呢? 何时新建复合索引复合索引又需要注意些什么呢?本篇文章主要是对网上一些讨论的总结。一.概念单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。用户可以...

    概要


    什么是单一索引,什么又是复合索引呢? 何时新建复合索引,复合索引又需要注意些什么呢?本篇文章主要是对网上一些讨论的总结。

    一.概念

    单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。

    用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。但复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引。当表的行数远远大于索引键的数目时,使用这种方式可以明显加快表的查询速度。

    同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,如果不特殊说明的话一般是指单一索引。宽索引也就是索引列超过2列的索引。

    设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效。拥有更多的窄索引,将给优化程序提供更多的选择余地,这通常有助于提高性能。

    二.使用

    创建索引 

    create index idx1 on table1(col1,col2,col3)  

    查询

    select * from table1 where col1= A and col2= B and col3 = C

    这时候查询优化器,不在扫描表了,而是直接的从索引中拿数据,因为索引中有这些数据,这叫覆盖式查询,这样的查询速度非常快。   

    三.注意事项

    1.何时是用复合索引

    在where条件中字段用索引,如果用多字段就用复合索引。一般在select的字段不要建什么索引(如果是要查询select col1 ,col2, col3 from mytable,就不需要上面的索引了)。根据where条件建索引是极其重要的一个原则。注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中.

    2.对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高。如:  

    IDX1:create   index   idx1   on   table1(col2,col3,col5)  

    select   *   from   table1   where   col2=A   and   col3=B   and   col5=D  

    如果是"select   *   from   table1   where   col3=B   and   col2=A   and   col5=D"

    或者是"select   *   from   table1   where   col3=B"将不会使用索引,或者效果不明显

    3.复合索引会替代单一索引么?

    很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列)

    IDX1:create   index   idx1   on   Tgongwen(fariqi,neibuyonghu)  

    (1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

    查询速度:2513毫秒

    (2)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'

    查询速度:2516毫秒

    (3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'

    查询速度:60280毫秒

    从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

    [参考: 查询优化及分页算法方案 http://blog.csdn.net/chiefsailor/archive/2007/05/28/1628339.aspx]


    4.需要在同一列上同时建单一索引和复合索引么?

    试验: sysbase   5.0   表table1   字段:col1,col2,col3  

    试验步骤:  

    (1)建立索引idx1   on   col1  

      执行select   *   from   table1   where   col1=A     使用idx1  

      执行select   *   from   table1   where   col1=A   and   col2=B   也使用idx1  

    (2)删除索引idx1,然后建立idx2   on   (col1,col2)复合索引  执行以上两个查询,也都使用idx2  

    (3)如果两个索引idx1,idx2都存在  

      并不是   where   col1='A'用idx1;where   col1=A   and   col2=B  用idx2。  

      其查询优化器使用其中一个以前常用索引。要么都用idx1,要么都用idx2.  

    由此可见,

    (1)对一张表来说,如果有一个复合索引 on   (col1,col2),就没有必要同时建立一个单索引 on col1。

    (2)如果查询条件需要,可以在已有单索引 on col1的情况下,添加复合索引on   (col1,col2),对于效率有一定的提高。

    (3)同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处,相对而言,建立多个窄字段(仅包含一个,或顶多2个字段)的索引可以达到更好的效率和灵活性。

    5. 一定需要覆盖性查询么?

    覆盖性查询:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。

    通常最好不要采用一个强调完全覆盖查询的策略。如果Select子句中的所有列都被一个非群集索引覆盖,优化程序会识别出这一点,并提供很好的性能。不过,这通常会导致索引过宽,并会过度依赖于优化程序使用该策略的可能性。通常,是用数量更多的窄索引,这对于大量查询来说可以提供更好的性能。

    总结:

    1.尽量使用窄索引。即时是在使用覆盖性查询的时候

    2.索引过多反而会降低数据库的性能

    3.除非在使用or条件时,需要在or之间的每个条件上使用单一索引,否则窄索引的复合索引和单一索引是没有明显区别的

    4.注意索引的使用顺序和建立顺序。无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

    出处:http://www.cnblogs.com/wenly/articles/1240321.html




    展开全文
  • 什么要创建索引 mysql在默认的情况下,表中的数据记录没有顺序的,就是说,在数据检索的时候,符合条件的数据存在表中的什么位置不知道的,如果使用select 进行查询,数据库会从第一条数据进行检索,找到第一...

    为什么要创建索引

    mysql在默认的情况下,表中的数据记录是没有顺序的,就是说,在数据检索的时候,符合条件的数据存在表中的什么位置是不知道的,如果使用select 进行查询,数据库会从第一条数据进行检索,找到第一条符合条件的数据之后,数据库的搜索不会停止,因为符合条件的数据不止一条,也就是说,此时select要把数据库中所有的信息全部的检索一遍才结束,就是我们常说的全表扫描,如果数据表中有很多数据,数据的记录不知道在什么位置,满足条件的数据不止一条,需要进行全表扫描,这样检索的效率就会变的很低。
    为了解决这个问题对应的索引的问题就产生了,**索引就是为了进行检索的,通过创建某个字段或者多个字段的索引,在搜索字段的时候就可以根据对应的索引快速的减速处相应的数据,避免全表扫描。**索引就像书的目录一样,通过目录就可以找到对应的内容,不用每一页都进行查看。

    索引的类型

    单列索引、复合索引、唯一索引、主键索引

    单列索引

    单列索引就是普通索引,没有任何限制,创建一个单列索引有3种方式:

    1.create index index_name on tbl_name(index_col_name)
    

    这里,create index表示创建一个索引,index_name表示索引的名称,on tbl_name表示要创建索引的表, index_col_name表示表中要创建索引的列
    通过修改表结构的方式创建索引

    2.alter table 表名 add index index_name on(index_col_name)
    

    这两种方式都是在表已经创建完成的时候使用的。也可以在表创建的时候指定索引‘

    3.create table tbale(
    	id int(11) not null auto_increment,
    	name varchar(32) not null,
    	......
    	primary key(id)
    	
    	indexName(name(32))   表示创建了name字段的索引
    
    )engine=innodb default charset=utf8
    

    举例:
    在user表中为字段user_name创建单列索引

    create index index_name on user(username)
    

    查看user表对应的索引,\G代表优化显示方式
    show index from user \G;
    在这里插入图片描述
    从这个图片中可以看出,user表中有两个索引,一个是列id 对应的索引名称是primary,另一个是列username对应的索引名称是index_name,从索引的信息表中可以看出,primary索引不允许重复,index_name索引允许重复,对应的索引类型都是btree。

    注意:可以使用show index from
    user查看user表中哪些字段创建了索引,创建表的时候主键定义的字段会自动的创建索引,称为丛生索引,而通过上面的三种方式创建的索引叫做普通索引,单列索引

    复合索引

    在多个字段上创建的索引就是复合索引,遵守最左查询原则在查询中只有使用了索引中的第一个字段索引才会被使用,在复合索引中索引列的顺序很重要。创建复合索引的方式也有三种:

    1.create index index_name on tbl_name(index_col_name1,index_col_name2,index_col_name3)
    

    通过修改表结构的方式创建索引

    2.alter table 表名 add index  index_name on(index_col_name1,index_col_name2,index_col_name3)
    

    创建表的时候直接指定

    3.create table table(
    	id int(11) not null auto_increment,
    	name varchar(32)  not null,
    	pinyin varchar(32),
    	primary key(id),
    	indexName(name(32),pinyin(32))
    )
    

    删除索引的语法

    drop index index_name on 表名
    

    举例:
    1.删除user表中索引index_name
    drop index index_name on user
    2.查看user表中的索引情况
    show index from user \G
    在这里插入图片描述
    可以看到user表中只有一个创建表时候指定主键的丛生索引
    3.创建username pinyin两个字段的复合索引
    create index index_username_pin on user(username,pinyin)
    4.查看user表中的索引情况
    show index from user \G
    在这里插入图片描述

    可以看出,username和pinyin两个字段都是索引并且是一个索引,索引名称为index_pinyin,username在前面,在查询的时候只有使用了复合索引的第一个字段索引才会被使用,索引的顺序很重要。

    唯一索引

    创建唯一索引必须要指定关键字unique,唯一索引和单列索引类似,主要的区别是:唯一索引限制列的值必须唯一,但是允许出现空值。对于多个字段,对于多个字段而言,列值的组合必须是唯一的,创建唯一索引也有3种方式:

    1.create unique index index_name on tbl_name(index_col_name1,index_col_name2,index_col_name3);
    

    通过修改表结构创建唯一索引

    2.alter table 表名 add unique index  index_name on(index_col_name1,index_col_name2,index_col_name3)
    

    在创建表的时候指定唯一索引

    3.create table table(
    	id int(11) not null auto_increment,
    	name varchar(32) not null,
    	primary key(id),
    	unique indexName(name(32))
    );
    

    举例:
    在user表中的username字段上创建唯一索引

    create unique index idnex_name  on user(username);
    

    需要注意的是:设置为唯一索引的字段在使用的时候不能有重复的值

    主键索引

    主键索引也就是丛生索引,是一种特殊的唯一索引,不允许有空值。创建主键索引的语法是:

    1.alter table table_name add primary key(index_col_name);
    

    主键索引一般情况在创建表的时候指明了主键就会自动创建,无需手动创建

    使用索引的时候要注意的地方:

    • where子句中的列kennel最适合作为索引
    • 不要尝试为性别或者有无这类字段建立索引,因为性别由于这种字段作为索引进行查找的时候会查询处一半的数据
    • 如果创建复合索引,要遵守最左前缀法则查询从最左前缀开始,并且不跳过中间的列
    • 不能过度的使用索引,因为每一次更新,删除,插入的时候都会维护改表的索引,维护索引需要浪费时间,并且创建索引占用的空间更多
    • 使用innodb存储引擎的时候,行默认会按照一定的顺序怕许,如果已经定义主键,则会按照主键的顺序进行存储,因为普通索引中会保存主键的键值,因此主键应当尽可能的选择较短的数据类型,节省存储空间
    • 不要尝试在索引列上使用函数
    展开全文
  • 复合索引什么情况下使用

    千次阅读 2012-01-05 18:01:49
    1、复合索引使用的目的是什么? :能形成索引覆盖,提高where语句的查询效率 2、一个复合索引是否可以代替多个单一索引? :复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推,否则复合索引不会...

     http://topic.csdn.net/t/20060813/16/4946416.html

    1、复合索引使用的目的是什么?
    :能形成索引覆盖,提高where语句的查询效率

    2、一个复合索引是否可以代替多个单一索引?
    :复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推,否则复合索引不会被使用
    所以,正常情况下复合索引不能替代多个单一索引

    3、在进行哪些类型的查询时,使用复合索引会比较有效?
    :如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引,形成索引覆盖可以提高查询的效率

    4、符合索引中索引列的排序原则是什么?
    :复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推

    5、什么情况下不适合使用复合索引?
    :建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立,另外如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引,另外如果表是经常需要更新的也不适合做索引

     

    根据你的where条件选择符合索引.如果查询是根据多个列,如where   Name= 'aa '   and   class= 'bb ',那么在Name和Class上建立符合索引会比较好.

    create   index   idx_test   on   表(Name,Class)

    但是符合索引的顺序一定要和查询的顺序相同才有效,如果顺序不同,那就没有效果了.

    是否建立符合索引要根据查询的需要,如果很多查询都是where   Name= 'aa '   and   class= 'bb '这样的形式,那建立符合索引就非常好.如果有一部分查询是where   class= 'cc '   那么你就应该在class上边建立单索引.所以最重要的是根据查询的条件.

    展开全文
  • 1.什么是复合索引? 顾名思义,复合索引的意识就是,将数据库中的多个字段组合起来形成的一个索引就是复合索引。创建复合索引的方式如下图所示: (1)使用Navicat创建 (2)使用SQL语句创建 UNIQUE key 'idex_test'...
  • mysql 里创建‘联合索引’的意义...建了一个(a,b,c)的复合索引,那么实际等于建了(a),(a,b),(a,b,c)三个索引,因为每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,这可是不小的开销!覆盖...
  • mysql 里创建‘联合索引’的意义...建了一个(a,b,c)的复合索引,那么实际等于建了(a),(a,b),(a,b,c)三个索引,因为每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,这可是不小的开销!覆盖...
  • 什么使用数据索引能提高效率[/size]数据索引的存储有序的 在有序的情况下,通过索引查询一个数据无需遍历索引记录的 极端情况下,数据索引的查询效率为二分法查询效率,趋近于 log2(N)对于复合索引:Mysql...
  • 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 ...
  • 单一索引和复合索引区别及联系 - BABY的日志 - 网易博客 ... 何时新建复合索引复合索引又需要注意些什么呢?本篇文章主要是对网上一些讨论的总结。 一.概念 单一索引是指索引列为一列的情况,即新建索引的语句只实...
  • 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 ...
  • 复合索引生效

    2015-04-14 21:24:36
    何时新建复合索引复合索引又需要注意些什么呢?本篇文章主要是对网上一些讨论的总结。 一.概念 单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。 用户可以在多个列上建立索引,这种...
  • 数据库复合索引

    2017-01-12 10:25:00
    参照:http://blog.sina.com.cn/s/blog_5a8b8eb80100sg83.html博客 1.什么是复合索引 1.1复合索引定义 索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。 利用索引中的附加...
  • 假如建的非聚集索引是复合索引,第一个字段至为重要。为什么如此重要?因为统计信息统计信息只在非聚集索引上的第一个字段上进行,意味著选择索引与否,选择哪个索引,都与这个第一字段息息相关。这么说,建复合索引...
  • 复合索引和效率

    2015-11-02 19:24:29
    什么是单一索引,什么又是复合索引呢? 何时新建复合索引复合索引又需要注意些什么呢?本篇文章主要是对网上一些讨论的总结。 一.概念 单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。 ...
  • 单一索引和复合索引区别及联系

    千次阅读 2017-06-08 10:01:31
    单一索引和复合索引区别及联系 - BABY的日志 - 网易博客 ... 何时新建复合索引复合索引又需要注意些什么呢?本篇文章主要是对网上一些讨论的总结。 一.概念 单一索引是指索引列为一列的情况,即新建索引的
  • 复合索引介绍

    2014-05-27 14:42:00
    什么是复合索引 1.1复合索引定义 索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 555
精华内容 222
关键字:

复合索引是什么