精华内容
下载资源
问答
  • 数据库索引最左前缀原则

    千次阅读 2018-11-30 12:53:53
    数据库最左前缀原则 最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用...

    数据库最左前缀原则

    最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上,

    注:如果第一个字段是范围查询需要单独建一个索引

    注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

    当创建(a,b,c)复合索引时,想要索引生效的话,只能使用 a和a,b和a,b,c三种组合

    实例:以下是常见的几个查询:

    mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' ;

    mysql>SELECT `a`,`b`,`c` FROM A WHERE `b`='bb' AND `c`='cc';

    mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' AND `c`='cc';

    请问:想要索引最大化的使用需要至少建几个索引

    答:需要建立两个复合索引:a,c、b,c

     

     

    原理:

    采用B+树结构,如果是复合索引,比如 a,b,c

    是先对a进行建立索引的结果下,再对b进行建立索引, 然后在以上的结果下,对c进行建立索引

     

    因此,只有a, ab, abc ,这三种情况下是有序的,会走索引,

    如果光看b, 是无序的。

    https://blog.csdn.net/qq_19557947/article/details/76951912

    https://www.cnblogs.com/weizhixiang/p/5914120.html 推荐

    展开全文
  • 转自:...一、最左前缀索引这里先说一下联合索引的概念。MySQL中的索引可以以一定顺序引用多个列,这种索引叫做复合(联合)索引,一般的,一个联合索引是一个有序元组<a1, a2...

    转自:https://www.cnblogs.com/duanxz/p/5244736.html

    高效使用索引的首要条件是知道什么样的查询会使用到索引,这个问题和B+Tree中的“最左前缀原理”有关,下面通过例子说明最左前缀原理。

    一、最左前缀索引

    这里先说一下联合索引的概念。MySQL中的索引可以以一定顺序引用多个列,这种索引叫做复合(联合)索引,一般的,一个联合索引是一个有序元组<a1, a2, …, an>,其中各个元素均为数据表的一列,实际上要严格定义索引需要用到关系代数,但是这里我不想讨论太多关系代数的话题,因为那样会显得很枯燥,所以这里就不再做严格定义。另外,单列索引可以看成联合索引元素数为1的特例。

    以employees.titles表为例,下面先查看其上都有哪些索引:

    复制代码
    SHOW INDEX FROM employees.titles;
    +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
    +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
    | titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE |
    | titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE |
    | titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE |
    | titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |
    +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
    复制代码

    从结果中可以到titles表的主索引为<emp_no, title, from_date>,还有一个辅助索引<emp_no>。为了避免多个索引使事情变复杂(MySQL的SQL优化器在多索引时行为比较复杂),这里我们将辅助索引drop掉:

    ALTER TABLE employees.titles DROP INDEX emp_no;

    这样就可以专心分析索引PRIMARY的行为了。

    规则1:全列匹配(此时若没有按照索引顺序时,mysql查询优化器会自动的调整顺序来使用定义好的索引)

    示例说明如下:

    复制代码
    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    复制代码

    很明显,当按照索引中所有列进行精确匹配(这里精确匹配指“=”或“IN”匹配)时,索引可以被用到。这里有一点需要注意,理论上索引对顺序是敏感的,但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引,例如我们将where中的条件顺序颠倒:

    复制代码
    EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    复制代码

    效果是一样的。

    规则2:最左前缀匹配

    示例说明如下:

    复制代码
    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
    | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
    复制代码

    当查询条件精确匹配索引的左边连续一个或几个列时,如<emp_no>或<emp_no, title>,所以可以被用到,但是只能用到一部分,即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引,但是key_len为4,说明只用到了索引的第一列前缀

    规则3:查询条件用到了索引中列的精确匹配,但是中间某个条件未提供

    示例说明如下:

    复制代码
    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    复制代码

    此时索引使用情况和情况二相同,因为title未提供,所以查询只用到了索引的第一列,而后面的from_date虽然也在索引中,但是由于title不存在而无法和左前缀连接,因此需要对结果进行扫描过滤from_date(这里由于emp_no唯一,所以不存在扫描)。如果想让from_date也使用索引而不是where过滤,可以增加一个辅助索引<emp_no, from_date>,此时上面的查询会使用这个索引。除此之外,还可以使用一种称之为“隔离列”的优化方法,将emp_no与from_date之间的“坑”填上。

    首先我们看下title一共有几种不同的值:

    复制代码
    SELECT DISTINCT(title) FROM employees.titles;
    +--------------------+
    | title |
    +--------------------+
    | Senior Engineer |
    | Staff |
    | Engineer |
    | Senior Staff |
    | Assistant Engineer |
    | Technique Leader |
    | Manager |
    +--------------------+
    复制代码

    只有7种。在这种成为“坑”的列值比较少的情况下,可以考虑用“IN”来填补这个“坑”从而形成最左前缀:

    复制代码
    EXPLAIN SELECT * FROM employees.titles
    WHERE emp_no='10001'
    AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
    AND from_date='1986-06-26';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 7 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    复制代码

    这次key_len为59,说明索引被用全了,但是从type和rows看出IN实际上执行了一个range查询,这里检查了7个key。看下两种查询的性能比较:

    复制代码
    SHOW PROFILES;
    +----------+------------+-------------------------------------------------------------------------------+
    | Query_ID | Duration | Query |
    +----------+------------+-------------------------------------------------------------------------------+
    | 10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'|
    | 11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ... |
    +----------+------------+-------------------------------------------------------------------------------+
    复制代码

    “填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据,则后者性能优势会更加明显。当然,如果title的值很多,用填坑就不合适了,必须建立辅助索引。

    规则4:查询条件没有指定索引第一列

    示例说明如下:

    复制代码
    EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    复制代码

     

    由于不是最左前缀,索引这样的查询显然用不到索引。

    规则5:匹配某列的前缀字符串。

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 56 | NULL | 1 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

    此时可以用到索引,但是如果通配符不是只出现在末尾,则无法使用索引。(原文表述有误,如果通配符%不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀)

    规则6:范围查询(范围查询后面的列将无法使用索引)

    对于范围条件查询,MYSQL无法再使用范围后面的其他索引列了。但对多个等值条件查询则没有这样的限制。

    复制代码
    EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    复制代码

    范围列可以用到索引(必须是最左前缀),但是范围列后面的列无法用到索引。同时,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引。

    复制代码
    EXPLAIN SELECT * FROM employees.titles
    WHERE emp_no < '10010'
    AND title='Senior Engineer'
    AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    复制代码

    可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方,那就是仅用explain可能无法区分范围索引和多值匹配,因为在type中这两者都显示为range。同时,用了“between”并不意味着就是范围查询,例如下面的查询:

    复制代码
    EXPLAIN SELECT * FROM employees.titles
    WHERE emp_no BETWEEN '10001' AND '10010'
    AND title='Senior Engineer'
    AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 16 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    复制代码

    看起来是用了两个范围查询,但作用于emp_no上的“BETWEEN”实际上相当于“IN”,也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配,否则会对MySQL的行为产生困惑。

    规则7:查询条件中含有函数或表达式

    很不幸,如果查询条件中含有函数或表达式,则MySQL不会为这列使用索引(虽然某些在数学意义上可以使用)。例如:

    复制代码
    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    复制代码

    虽然这个查询和情况五中功能相同,但是由于使用了函数left,则无法为title列应用索引,而情况五中用LIKE则可以。再如:

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

    显然这个查询等价于查询emp_no为10001的函数,但是由于查询条件是一个表达式,MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度,因此在写查询语句时尽量避免表达式出现在查询中,而是先手工私下代数运算,转换为无表达式的查询语句。

    二、索引选择性

      既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。

    第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过 2000可以考虑不建索引,超过2000条可以酌情考虑索引。

    另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:

    Index Selectivity = Cardinality / #T

    显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,上文用到的employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

    复制代码
    SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.0000 |
    +-------------+
    复制代码

    title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。

    三、索引优化策略之前缀索引

      有一种与索引选择性有关的索引优化策略叫做前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

    从图12可以看到employees表只有一个索引<emp_no>,那么如果我们想按名字搜索一个人,就只能全表扫描了:

    复制代码
    EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
    | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300024 | Using where |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
    复制代码

    如果频繁按名字搜索员工,这样显然效率很低,因此我们可以考虑建索引。有两种选择,建<first_name>或<first_name, last_name>,看下两个索引的选择性:

    复制代码
    SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.0042 |
    +-------------+
    SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.9313 |
    +-------------+
    复制代码

    <first_name>显然选择性太低,<first_name, last_name>选择性很好,但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法?可以考虑用first_name和last_name的前几个字符建立索引,例如<first_name, left(last_name, 3)>,看看其选择性:

    复制代码
    SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.7879 |
    +-------------+
    复制代码

    选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:

    SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.9007 |
    +-------------+

    这时选择性已经很理想了,而这个索引的长度只有18,比<first_name, last_name>短了接近一半,我们把这个前缀索引 建上:

    ALTER TABLE employees.employees
    ADD INDEX `first_name_last_name4` (first_name, last_name(4));

    此时再执行一遍按名字查询,比较分析一下与建索引前的结果:

    复制代码
    SHOW PROFILES;
    +----------+------------+---------------------------------------------------------------------------------+
    | Query_ID | Duration | Query |
    +----------+------------+---------------------------------------------------------------------------------+
    | 87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    | 90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    +----------+------------+---------------------------------------------------------------------------------+
    复制代码

    性能的提升是显著的,查询速度提高了120多倍。

    前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)。

     

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

    补充该节中的"范围查询"说明:

      Mysql对于范围查询range分的优化为单字段优化和多元素优化:

    单元素索引范围条件的定义如下:
     
    ·         对于BTREE和HASH索引,当使用=、<=>、INIS NULL或者IS NOT NULL操作符时,关键元素与常量值的比较关系对应一个范围条件,即const范围。
     
    ·         对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件。
     
    ·         对于所有类型的索引,多个范围条件结合ORAND则产生一个范围条件。
     
    前面描述的“常量值”系指:
     
    ·         查询字符串中的常量
     
    ·         同一联接中的const或system表中的列
     
    ·         无关联子查询的结果
     
    ·         完全从前面类型的子表达式组成的表达式

     

    多元素索引的范围条件:
     
         1.----------------
         对于BTREE索引,区间可以对结合AND的条件有用,其中每个条件用一个常量值通过=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE 'pattern' (其中'pattern'不以通配符开头)比较一个关键元素。区间可以足够长以确定一个包含所有匹配条件(或如果使用<>或!=,为两个区间)的记录的单一的关键元组。例如,对于条件:
         key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
     
        2.-----------
        对于HASH索引,可以使用包含相同值的每个区间。
        key_part1 cmp const1 AND key_part2 cmp const2
        AND ... AND key_partN cmp constN;
     
        这里,const1,const2,...为常量,cmp是=、<=>或者IS NULL比较操作符之一,条件包括所有索引部分。(也就是说,有N 个条件,每一个对应N-元素索引的每个部分)。   
     
        3.  ----------
        如果包含区间内的一系列记录的条件结合使用OR,则形成包括一系列包含在区间并集的记录的一个条件。如果条件结合使用了AND,则形成包括一系列包含在区间交集内的记录的一个条件。例如,对于两部分索引的条件:
     (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
     
        区间为:
        (1, -inf) < (key_part1, key_part2) < (1, 2)
        (5, -inf) < (key_part1, key_part2)   

     


    Mysql检索时间查询 (版本要求: 5.0.37或以上),开启profile:
    复制代码
    mysql> set profiling=1;
    2    Query OK, 0 rows affected (0.00 sec)
    eg:
        mysql> select * from test_1;
        mysql> show profiles;
        +----------+------------+----------------------+
        | Query_ID | Duration   | Query                |
        +----------+------------+----------------------+
        |        1 | 0.84718100 | select * from test_1 |
        +----------+------------+----------------------+
        1 row in set (0.00 sec)

    展开全文
  • :sql查询用到索引的条件是必须要遵守最左前缀原则,为什么上面两个查询还能用到索引? ----------------------------------------------------------------------------------------------------------------------...
    这是你的表结构,有三个字段,分别是id,name,cid
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `cid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_cid_INX` (`name`,`cid`),
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    

    索引方面:id是主键,(name,cid)是一个多列索引。
    -----------------------------------------------------------------------------
    下面是你有疑问的两个查询:

    EXPLAIN SELECT * FROM student WHERE   cid=1;
    
    EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';
    

    你的疑问是:sql查询用到索引的条件是必须要遵守最左前缀原则,为什么上面两个查询还能用到索引?
    ---------------------------------------------------------------------------------------------------------------------------

    讲上面问题之前,我先补充一些知识,因为我觉得你对索引理解是狭隘的:
    上述你的两个查询的explain结果中显示用到索引的情况类型是不一样的。,可观察explain结果中的type字段。你的查询中分别是:
    1. type: index
    2. type: ref

    解释:
    index:这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个复合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。

    所以:对于你的第一条语句:
    EXPLAIN SELECT * FROM student WHERE   cid=1;
    

    判断条件是cid=1,而cid是(name,cid)复合索引的一部分,没有问题,可以进行index类型的索引扫描方式。explain显示结果使用到了索引,是index类型的方式。

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

    ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,而不是会对索引中每一个数据都进行一 一的扫描判断,也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找,索引却是有要求的,要实现这种能快速查找的算法,索引就要满足特定的数据结构。简单说,也就是索引字段的数据必须是有序的,才能实现这种类型的查找,才能利用到索引。


    有些了解的人可能会问,索引不都是一个有序排列的数据结构么。不过答案说的还不够完善,那只是针对单个索引,而复合索引的情况有些同学可能就不太了解了。

    下面就说下复合索引:
    以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的:

                                                                          

    mysql创建复合索引的规则是首先会对复合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

    所以:第一个name字段是绝对有序的,而第二字段就是无序的了。所以通常情况下,直接使用第二个cid字段进行条件判断是用不到索引的,当然,可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

    那么什么时候才能用到呢?
    当然是cid字段的索引数据也是有序的情况下才能使用咯,什么时候才是有序的呢?观察可知,当然是在name字段是等值匹配的情况下,cid才是有序的。发现没有,观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。
    这也就是mysql索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引的原因。(而且第一个索引必须是等值匹配)。
    ---------------------------------------------------------------------------------------------------------------------------
    所以对于你的这条sql查询:
    EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';
    

    没有错,而且复合索引中的两个索引字段都能很好的利用到了!因为语句中最左面的name字段进行了等值匹配,所以cid是有序的,也可以利用到索引了。

    你可能会问:我建的索引是(name,cid)。而我查询的语句是cid=1 AND name='小红'; 我是先查询cid,再查询name的,不是先从最左面查的呀?

    好吧,我再解释一下这个问题:首先可以肯定的是把条件判断反过来变成这样 name='小红' and cid=1; 最后所查询的结果是一样的。
    那么问题产生了?既然结果是一样的,到底以何种顺序的查询方式最好呢?

    所以,而此时那就是我们的mysql查询优化器该登场了,mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。


    链接:https://www.zhihu.com/question/36996520/answer/93256153
    展开全文
  • 高效使用索引的首要条件是知道什么样的查询会使用到索引,这个问题和B+Tree中的“最左前缀原理”有关,下面通过例子说明最左前缀原理。 一、最左前缀索引 这里先说一下联合索引的概念。MySQL中的索引可以以一定...

    高效使用索引的首要条件是知道什么样的查询会使用到索引,这个问题和B+Tree中的“最左前缀原理”有关,下面通过例子说明最左前缀原理。

    一、最左前缀索引

    这里先说一下联合索引的概念。MySQL中的索引可以以一定顺序引用多个列,这种索引叫做复合(联合)索引,一般的,一个联合索引是一个有序元组<a1, a2, …, an>,其中各个元素均为数据表的一列,实际上要严格定义索引需要用到关系代数,但是这里我不想讨论太多关系代数的话题,因为那样会显得很枯燥,所以这里就不再做严格定义。另外,单列索引可以看成联合索引元素数为1的特例。

    以employees.titles表为例,下面先查看其上都有哪些索引:

    SHOW INDEX FROM employees.titles;
    +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
    +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
    | titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE |
    | titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE |
    | titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE |
    | titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |
    +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

    从结果中可以到titles表的主索引为<emp_no, title, from_date>,还有一个辅助索引<emp_no>。为了避免多个索引使事情变复杂(MySQL的SQL优化器在多索引时行为比较复杂),这里我们将辅助索引drop掉:

    ALTER TABLE employees.titles DROP INDEX emp_no;

    这样就可以专心分析索引PRIMARY的行为了。

    规则1:全列匹配(此时若没有按照索引顺序时,mysql查询优化器会自动的调整顺序来使用定义好的索引)

    示例说明如下:

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

    很明显,当按照索引中所有列进行精确匹配(这里精确匹配指“=”或“IN”匹配)时,索引可以被用到。这里有一点需要注意,理论上索引对顺序是敏感的,但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引,例如我们将where中的条件顺序颠倒:

    EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    | 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

    效果是一样的。

    规则2:最左前缀匹配

    示例说明如下:

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
    | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

    当查询条件精确匹配索引的左边连续一个或几个列时,如<emp_no>或<emp_no, title>,所以可以被用到,但是只能用到一部分,即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引,但是key_len为4,说明只用到了索引的第一列前缀

    规则3:查询条件用到了索引中列的精确匹配,但是中间某个条件未提供

    示例说明如下:

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

    此时索引使用情况和情况二相同,因为title未提供,所以查询只用到了索引的第一列,而后面的from_date虽然也在索引中,但是由于title不存在而无法和左前缀连接,因此需要对结果进行扫描过滤from_date(这里由于emp_no唯一,所以不存在扫描)。如果想让from_date也使用索引而不是where过滤,可以增加一个辅助索引<emp_no, from_date>,此时上面的查询会使用这个索引。除此之外,还可以使用一种称之为“隔离列”的优化方法,将emp_no与from_date之间的“坑”填上。

    首先我们看下title一共有几种不同的值:

    SELECT DISTINCT(title) FROM employees.titles;
    +--------------------+
    | title |
    +--------------------+
    | Senior Engineer |
    | Staff |
    | Engineer |
    | Senior Staff |
    | Assistant Engineer |
    | Technique Leader |
    | Manager |
    +--------------------+

    只有7种。在这种成为“坑”的列值比较少的情况下,可以考虑用“IN”来填补这个“坑”从而形成最左前缀:

    EXPLAIN SELECT * FROM employees.titles
    WHERE emp_no='10001'
    AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
    AND from_date='1986-06-26';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 7 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

    这次key_len为59,说明索引被用全了,但是从type和rows看出IN实际上执行了一个range查询,这里检查了7个key。看下两种查询的性能比较:

    SHOW PROFILES;
    +----------+------------+-------------------------------------------------------------------------------+
    | Query_ID | Duration | Query |
    +----------+------------+-------------------------------------------------------------------------------+
    | 10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'|
    | 11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ... |
    +----------+------------+-------------------------------------------------------------------------------+

    “填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据,则后者性能优势会更加明显。当然,如果title的值很多,用填坑就不合适了,必须建立辅助索引。

    规则4:查询条件没有指定索引第一列

    示例说明如下:

    EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

     

    由于不是最左前缀,索引这样的查询显然用不到索引。

    规则5:匹配某列的前缀字符串。

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 56 | NULL | 1 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

    此时可以用到索引,但是如果通配符不是只出现在末尾,则无法使用索引。(原文表述有误,如果通配符%不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀)

    规则6:范围查询(范围查询后面的列将无法使用索引)

    对于范围条件查询,MYSQL无法再使用范围后面的其他索引列了。但对多个等值条件查询则没有这样的限制。

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

    范围列可以用到索引(必须是最左前缀),但是范围列后面的列无法用到索引。同时,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引。

    EXPLAIN SELECT * FROM employees.titles
    WHERE emp_no < '10010'
    AND title='Senior Engineer'
    AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

    可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方,那就是仅用explain可能无法区分范围索引和多值匹配,因为在type中这两者都显示为range。同时,用了“between”并不意味着就是范围查询,例如下面的查询:

    EXPLAIN SELECT * FROM employees.titles
    WHERE emp_no BETWEEN '10001' AND '10010'
    AND title='Senior Engineer'
    AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 16 | Using where |
    +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

    看起来是用了两个范围查询,但作用于emp_no上的“BETWEEN”实际上相当于“IN”,也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配,否则会对MySQL的行为产生困惑。

    规则7:查询条件中含有函数或表达式

    很不幸,如果查询条件中含有函数或表达式,则MySQL不会为这列使用索引(虽然某些在数学意义上可以使用)。例如:

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
    | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
    +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

    虽然这个查询和情况五中功能相同,但是由于使用了函数left,则无法为title列应用索引,而情况五中用LIKE则可以。再如:

    EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
    | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |
    +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

    显然这个查询等价于查询emp_no为10001的函数,但是由于查询条件是一个表达式,MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度,因此在写查询语句时尽量避免表达式出现在查询中,而是先手工私下代数运算,转换为无表达式的查询语句。

    二、索引选择性

      既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。

    第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过 2000可以考虑不建索引,超过2000条可以酌情考虑索引。

    另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:

    Index Selectivity = Cardinality / #T

    显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,上文用到的employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

    SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.0000 |
    +-------------+

    title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。

    三、索引优化策略之前缀索引

      有一种与索引选择性有关的索引优化策略叫做前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

    从图12可以看到employees表只有一个索引<emp_no>,那么如果我们想按名字搜索一个人,就只能全表扫描了:

    EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
    | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300024 | Using where |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

    如果频繁按名字搜索员工,这样显然效率很低,因此我们可以考虑建索引。有两种选择,建<first_name>或<first_name, last_name>,看下两个索引的选择性:

    SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.0042 |
    +-------------+
    SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.9313 |
    +-------------+

    <first_name>显然选择性太低,<first_name, last_name>选择性很好,但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法?可以考虑用first_name和last_name的前几个字符建立索引,例如<first_name, left(last_name, 3)>,看看其选择性:

    SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.7879 |
    +-------------+

    选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:

    SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    | 0.9007 |
    +-------------+

    这时选择性已经很理想了,而这个索引的长度只有18,比<first_name, last_name>短了接近一半,我们把这个前缀索引 建上:

    ALTER TABLE employees.employees
    ADD INDEX `first_name_last_name4` (first_name, last_name(4));

    此时再执行一遍按名字查询,比较分析一下与建索引前的结果:

    SHOW PROFILES;
    +----------+------------+---------------------------------------------------------------------------------+
    | Query_ID | Duration | Query |
    +----------+------------+---------------------------------------------------------------------------------+
    | 87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    | 90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    +----------+------------+---------------------------------------------------------------------------------+

    性能的提升是显著的,查询速度提高了120多倍。

    前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)。

     

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

    补充该节中的"范围查询"说明:

      Mysql对于范围查询range分的优化为单字段优化和多元素优化:

    单元素索引范围条件的定义如下:
     
    ·         对于BTREE和HASH索引,当使用=、<=>、INIS NULL或者IS NOT NULL操作符时,关键元素与常量值的比较关系对应一个范围条件,即const范围。
     
    ·         对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件。
     
    ·         对于所有类型的索引,多个范围条件结合ORAND则产生一个范围条件。
     
    前面描述的“常量值”系指:
     
    ·         查询字符串中的常量
     
    ·         同一联接中的const或system表中的列
     
    ·         无关联子查询的结果
     
    ·         完全从前面类型的子表达式组成的表达式

     

    多元素索引的范围条件:
     
         1.----------------
         对于BTREE索引,区间可以对结合AND的条件有用,其中每个条件用一个常量值通过=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE 'pattern' (其中'pattern'不以通配符开头)比较一个关键元素。区间可以足够长以确定一个包含所有匹配条件(或如果使用<>或!=,为两个区间)的记录的单一的关键元组。例如,对于条件:
         key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
     
        2.-----------
        对于HASH索引,可以使用包含相同值的每个区间。
        key_part1 cmp const1 AND key_part2 cmp const2
        AND ... AND key_partN cmp constN;
     
        这里,const1,const2,...为常量,cmp是=、<=>或者IS NULL比较操作符之一,条件包括所有索引部分。(也就是说,有N 个条件,每一个对应N-元素索引的每个部分)。   
     
        3.  ----------
        如果包含区间内的一系列记录的条件结合使用OR,则形成包括一系列包含在区间并集的记录的一个条件。如果条件结合使用了AND,则形成包括一系列包含在区间交集内的记录的一个条件。例如,对于两部分索引的条件:
     (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
     
        区间为:
        (1, -inf) < (key_part1, key_part2) < (1, 2)
        (5, -inf) < (key_part1, key_part2)   

     


    Mysql检索时间查询 (版本要求: 5.0.37或以上),开启profile:
    mysql> set profiling=1;
    2    Query OK, 0 rows affected (0.00 sec)
    eg:
        mysql> select * from test_1;
        mysql> show profiles;
        +----------+------------+----------------------+
        | Query_ID | Duration   | Query                |
        +----------+------------+----------------------+
        |        1 | 0.84718100 | select * from test_1 |
        +----------+------------+----------------------+
        1 row in set (0.00 sec)

     

    转载于:https://www.cnblogs.com/duanxz/p/5244736.html

    展开全文
  • 如果在数据列a,b,c上建立了复合索引,索引中的数据行按照a/b/c次序排列,因此数据表会自动的按照a/b和a排列,即使指定了a,或者a和b的值,mysql也可以使用这个索引。 这个索引可以用于如下数据列的组合: a, b...
  • Mysql组合索引最左前缀原则

    千次阅读 2018-09-12 15:24:10
    建了一个(a,b,c)的复合索引,那么相当于建了(a),(a,b),(a,b,c)三个索引 结果: 只有查询(b)、(c)、(b,c)、(c,b)没有使用索引 并不是网上说的(b,a)会使索引失效,mysql优化器会发现执行SQL查询的...
  • 联合索引:联合索引又叫复合索引,是指由多个字段(字段是有顺序的)组成的索引,比如: CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, nick varchar(30) DEFAULT NULL, phone varchar(25) NOT NULL, ...
  • 1.为什么要使用索引索引是什么? 这里很官方的回答是:索引是帮助 MySQL 高效获取数据的数据结构。 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。 数据库...
  • 联合索引: (1)也称多列索引、复合索引。 (2)联合索引就是把多个列按照顺序放到一个索引树上。... 最左前缀原则:按照最左边的列开始的顺序并且不能跳过其中的列。 如:(name, age,posi...
  • 资料来源于网络,仅供参考学习。 CREATE TABLE test(a INT,b INT,c INT,KEY idx(a,b,c)); 优: SELECT * FROM test WHERE a=10 AND b>50;差: SELECT * FROM test WHERE b>50; 优: SELECT * FROM test ORDER BY ...
  • MySQL的最左前缀原则和联合索引失效的情况从索引的底层结构看待最左前缀原则一、多列索引在and查询中应用二、多列索引在范围查询中应用三、多列索引在排序中应用四,总结索引失效的情况 复合(联合)索引失效解析 从...
  • 文章目录概述普通索引唯一索引主键索引组合索引最左前缀 概述 索引分单列索引和组合索引: 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引; 组合索引(也叫复合索引),即一个...
  • 首先必须了解几个概念:单列索引,复合索引最左前缀原则(我自己起名为:通关有序原则) 1. 索引建立的原则 用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。 仅仅出现...
  • 当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次...
  • 数据库最左前缀原则

    2019-10-05 22:08:53
    最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最...
  • MySql最左前缀原则

    2018-03-02 15:57:00
    最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最...
  • 最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最...
  • 单个索引与复合索引

    2018-12-17 13:14:00
    在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。 如果我们创建了(username,sex,age)的复合索引,那么其实相当于创建了: ...
  • 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到,示例: 对列col1、列col2和列c...
  • 1、最佳左前缀原则——如果索引了多列,要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。前提条件:表中已添加复合索引(username,password,age)分析:该查询缺少username,查询条件复合...
  • 最左前缀法则 在复合索引中,如果查询了多列,索引需要从最左列开始,且不跳过中间的列。 最左前缀索引反例 跳过name,status去查adress,同样显示用到的索引(key)为空 在查询中调换顺序,那么显示用到了索引...
  • 为什么要强调最左前缀原则 那么什么时候才能用到呢? 为什么要强调最左前缀原则 以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的: mysql创建复合索引的规则是首先会对复合索引的最左边的...
  • 在Mysql建立多列索引(联合索引)有最左前缀的原则,即最左优先。 如果我们建立了一个2列的联合索引(col1,col2),实际上已经建立了两个联合索引(col1)、(col1,col2); 如果有一个3列索引(col1,col2,col3),实际上已经...
  • mysql建立多列索引(联合索引)有最左前缀的原则,即最左优先,如: 如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引;如果有一个3列索引(col1,col2,col3),则已经对(col1)、(col1,col2)...
  • 有关于mysql复合索引

    2018-06-25 15:46:32
    复合索引原则:最左前缀原则: index(a,b,c) 1、每次查询必须有a,如ab,ac,abc,如果没有a,只有bc查询将失效2、查询中带or,查询失效3、查询中带%*,查询失效。4、where a=* and b > and c=* ,c将不走索引 ...

空空如也

空空如也

1 2 3 4 5 ... 8
收藏数 149
精华内容 59
关键字:

复合索引最左前缀