900字范文 > where 1=1会影响索引吗_MySQL之InnoDB存储引擎：索引的使用

where 1=1会影响索引吗_MySQL之InnoDB存储引擎：索引的使用

时间：2020-07-17 06:39:47

索引一直是MySQL的重点内容，这里主要介绍下InnoDB引擎下索引的使用

abstract.png

查找

为了后续行文方便，这里先给出一张表的SQL创建语句。可以看到在建立该表的同时还建立了一个联合索引——index1。即，在该联合索引的B+树中，同一层次的各数据页、数据页内的各记录的顺序规则如下

首先使用name字段进行排序当name字段相同时，则使用birthday字段进行排序当birthday字段相同时，则使用phone_number字段进行排序

CREATETABLEperson_info(

idINTNOTNULLauto_increment,

nameVARCHAR(100)NOTNULL,

birthdayDATENOTNULL,

phone_numberCHAR(11)NOTNULL,

countryvarchar(100)NOTNULL,

PRIMARYKEY(id),

KEYindex1(name,birthday,phone_number)

);

全值匹配

所谓全值匹配指的是查找条件中的字段与索引中包含的字段一致，此时可充分利用该索引来大大提高效率了。如下面的SQL查询语句，其where子句中的字段(name、birthday、phone_number)与索引index1中包含的字段(birthday, phone_number)一致。故在该索引中依次使用name、birthday、phone_number字段来进行查找即可快速找到所需的记录主键

SELECT*FROMperson_info

WHEREname='aaron'ANDbirthday='1995-08-11'ANDphone_number='12345678900';

值得一提的是，如果我们将上述where子句中各字段的顺序调整下，如下所示。其同样也是全值匹配，原因在于通过MySQL的查询优化器可以实现根据索引中字段的顺序来适时调整各查找条件的使用顺序

SELECT*FROMperson_info

WHEREphone_number='12345678900'ANDbirthday='1995-08-11'ANDname='aaron';

最左匹配原则

最左匹配原则：对于联合索引而言，索引中包含的各字段会从左向右依次匹配给定的查询条件，直到遇到范围查询(、between、like)停止。这个原则比较绕口，也不好理解。下面我们通过一些具体的实例来进行解释

先看下面这个SQL查询语句，查询条件中只有name字段。而索引index1则会根据其定义的字段顺序(name、birthday、phone_number)依次开始匹配。显然这里，索引index1中的name字段匹配成功。此时即可利用该索引来加快查找速度了。原因很简单，因为前面说了该索引的B+树是先使用name字段进行排序的

SELECT*FROMperson_info

WHEREname='aaron';

现在，我们来看另外一个SQL查询语句。同样索引index1依然是先从name字段开始匹配的，显然这里查询条件中没有name字段。则匹配结束，即索引index1中定义的字段没有一个匹配上。故这个查询就无法利用该索引的B+来提高查询效率了。原因也很好理解，因为在该索引的B+树中，是先使用name字段进行排序的，只有在name字段相同的情况下，才使用birthday字段进行排序的。即birthday字段的有序性是相对的，是基于name相同的条件下才成立的。由于此查询语句没有name字段的查询条件来缩小范围，故birthday字段是无法通过该索引的B+树来提高查询效率的

SELECT*FROMperson_info

WHEREbirthday='1995-08-11';

同样的道理，如果SQL语句的查询条件中只给定了phone_number字段，也是无法通过该索引提高效率。所以说，在建立联合索引时，字段的定义顺序非常重要！如果你索引中定义的第一个字段是在实际查询时很少使用的查询条件的话，那么这个索引基本就等于白建了

现在继续看下面的SQL查询语句，根据我们的上面说的，索引index1从name字段开始匹配，然后发现索引中的第二个字段birthday是是范围查询，则停止匹配。显然这个查询语句是可以有效利用到索引index1的。原因其实上面也说了，先通过查询条件中的name值快速在该索引的B+树中进行查找。由于对于相同的name值是采用birthday字段进行排序，然后只需在上一次B+树利用name值找到的结果中，继续利用B+树找到birthday查询条件所限定的两个边界记录即可，而这两条边界记录之间的记录则可通过记录之间的单向链表、数据页之间的双向链表快速取出。即使这里我们将where子句中查询条件name值放在查询条件birthday的后面，由于查询优化器存在，故实际上并不会造成什么影响。这也很好的体现了最左匹配原则中强调的是索引中各字段的定义顺序

SELECT*FROMperson_info

WHEREname='aaron'ANDbirthday>'1995-08-01'ANDbirthday<'1995-08-11';

对于下面这个SQL查询而言，可以利用索引的B+树快速找到name查询条件限定的两个边界记录。但是根据最左匹配原则，我们知道该索引B+树的作用也就仅仅到此为止了。因为birthday字段在不同name值下显然是无序的，故无法通过B+树来快速找到，而只能从上一步中通过name值所确定的两个边界记录来取出的所有中间记录，最后依次遍历检查是否满足birthday值的查询条件

SELECT*FROMperson_info

WHEREname>'aaron'ANDname<'bob'ANDbirthday='1995-08-01';

对于like子句而言，需具体情况具体分析。具体地，下面的两个SQL语句，前者的like子句是一个范围查询，当然该SQL语句是可以通过索引index1来加快查找速度的，因为B+树是先使用name字段进行排序的；但对于后者而言，该SQL语句显然无法通过B+树实现快速查找

--该SQL可利用索引index提高查找速度

SELECT*FROMperson_infoWHEREnamelike'aa%';

--该SQL无法利用索引index来提高查找速度

SELECT*FROMperson_infoWHEREnamelike'%ron'

总而言之、言而总之，讲了这么多具体地例子大家可以看到所谓的最左匹配原则。理解的关键就在于联合索引B+树所使用的排序规则上，即是按照索引中指定的各字段依次排序

where子句的字段需保证单独字段的形式

如果对where子句中的字段使用表达式、函数来修饰的话，此时也是无法利用到索引的。故如果期望有效利用索引，必须保证索引中各字段是以单独字段的形式出现。这里我们假设user表有一个INT类型的age字段，并同时为该字段建立了索引。显然对于下面的两个SQL语句而言，第一个SQL语句where子句中的age字段不是以单独字段的形式，而是以 age*5 表达式的形式出现，故此时无法利用该索引，只能通过遍历所有记录、依次计算该表达式的值是否小于10的方式来查找符合的记录；而我们看第二个SQL的话，虽然实际语义与第一个SQL语句是一毛一样的，但是由于这里where子句的age字段是以单独字段的形式出现，故此时即可通过索引来提高查找效率的

--该SQL无法利用索引

SELECT*FROMuserWHEREage*510;

--该SQL可利用索引

SELECT*FROMuserWHEREage10/5;

排序

在MySQL中，可通过order by子句实现对结果进行按需排序。这里简要说说什么情况下可以直接通过索引实现排序。而什么时候又无法通过索引实现排序，只能通过对结果的排序实现SQL语句的排序要求

通过索引实现排序

对于索引index1而言，如果order by子句中各字段的顺序与该索引中各字段出现的顺序完全一致，则可直接利用索引实现排序。例如下面的SQL语句，其要求的排序结果与该索引的B+树中排序规则完全一致。故只需利用索引获取到主键，然后通过回表获取到索引中没有的字段数据(此处为country字段)即可

SELECT*FROMperson_info

ORDERBYname,birthday,phone_numberlimit5;

同理，即使order by子句只给出索引中的部分字段，只要其顺序符合索引中各字段的顺序。同样也是可以利用索引实现排序的。如下所示。至于原因相信大家应该都清楚了，前面在最左匹配原则一节中已经多次重复讲解了

--可以使用索引的SQL语句

SELECT*FROMperson_infoORDERBYnamelimit5;

SELECT*FROMperson_infoORDERBYname,birthdaylimit5;

SELECT*FROMperson_infoORDERBYname,birthday,phone_numberlimit5;

--无法使用索引的SQL语句

SELECT*FROMperson_infoORDERBYbirthdaylimit5;

SELECT*FROMperson_infoORDERBYphone_numberlimit5;

SELECT*FROMperson_infoORDERBYbirthday,phone_numberlimit5;

SELECT*FROMperson_infoORDERBYbirthday,name,phone_numberlimit5;

同理，索引中的左边字段如果在SQL语句的查询条件中为定值，则在order by子句中则可以依次使用后面的字段进行排序。此时依然可以利用到索引。例如下面的SQL语句，因为在该索引的B+树中即是当name相同时使用birthday进行排序，所以此时通过索引获取到主键然后回表得到的结果，即直接符合该SQL语句的排序要求

SELECT*FROMperson_info

WHEREname='aaron'

ORDERBYbirthdaylimit5;

不可通过索引实现排序

1. ASC、DESC同时出现

前面我们介绍了可通过索引来直接保证查询结果满足SQL语句的排序需求，但是其有一个大前提，即order by子句中的各字段，要么均是按升序排序，要么均是按降序排序。如果期望对结果按一个字段升序、另一个字段降序的进行排序的话，即同时出现了ASC、DESC关键字，示例如下所示。则此时MySQL将不会通过索引来实现SQL语句期望的排序效果，而是对查询结果进行再排序来实现

SELECT*FROMperson_info

ORDERBYname,birthdayDESClimit5;

Note

MySQL下order by子句中默认为ASC，即升序排序

2. order by子句中存在非同一个索引的字段

当order by子句中存在非来自同一个索引的字段，此时也无法通过索引实现排序。例如下面的SQL语句，显然order by子句中的country字段并不在index1索引中

SELECT*FROMperson_info

ORDERBYname,countryDESClimit5;

3. 对order by子句的字段使用表达式

如果对order by子句中的字段使用函数进行修饰，此时也是无法利用索引实现排序的，例如下面的SQL语句使用了UPPER函数进行修饰。故如果期望通过索引实现排序，必须保证索引中各字段是以单独字段的形式出现

SELECT*FROMperson_info

ORDERBYUPPER(name)limit5;

分组

对于SQL语句中的group by分组子句而言，如果group by子句中各字段的顺序与该索引中各字段出现的顺序一致，则同样可通过索引B+树进行分组。示例如下所示

--可以使用索引的SQL语句

SELECTname,count(*)FROMperson_infoGROUPBYname;

SELECTname,birthday,count(*)FROMperson_infoORDERBYname,birthday;

SELECTname,birthday,phone_number,count(*)FROMperson_infoORDERBYname,birthday,phone_number;

覆盖索引

我们知道在使用联合索引时，如果需要获取索引中不存在的字段信息时，就需要通过回表操作来实现。例如下面的SQL语句

SELECT*FROMperson_infoORDERBYname;

而如果一个索引能够覆盖所有需要查询的字段信息，则称之为覆盖索引。此时只需通过该索引的B+树即可直接获取所需查询的各字段结果，不再需要进行回表操作，显然这样大大提高了性能。这种查询方式即被之称为索引覆盖。例如下面的SQL语句所需查询的字段信息均可在索引index1中获取到，而不需要再进行回表了

SELECTname,birthdayFROMperson_infoORDERBYname;

冗余和重复索引

重复索引

所谓重复索引，是指在相同的字段上按相同的顺序创建相同类型的索引。比如下面的SQL语句中，可以看到这里针对主键id字段建立了一个二级索引index1，而我们知道InnoDB引擎本身就会自动为主键字段建立一个索引——即聚簇索引。即针对下面的SQL语句而言，我们实际上为了主键创建两个重复的主键。实际开发中，这是应该避免出现的情况，因为重复索引只会增加MySQL维护的成本，并不会提升我们查询的速度

CREATETABLEanimal_info(

idINTNOTNULLauto_increment,

nameVARCHAR(255)NOTNULL,

ageINTNOTNULL,

sexVARCHAR(255)NOTNULL,

typevarchar(100)NOTNULL,

PRIMARYKEY(id),

KEYindex1(id)

);

冗余索引

如果我们已经有了索引(A,B)，此时再创建一个索引(A)就是所谓的冗余索引了。因为索引(A)是索引(A,B)的前缀索引，即索引(A,B)本身就具有索引(A)的作用。例如对于下面的SQL语句而言，index3即是所谓的冗余索引了。大多数情况下都不需要冗余索引，而应该尽量拓展已有的索引，而不是创建新索引以导致产生了冗余索引

CREATETABLEanimal_info(

idINTNOTNULLauto_increment,

nameVARCHAR(255)NOTNULL,

ageINTNOTNULL,

sexVARCHAR(255)NOTNULL,

typevarchar(100)NOTNULL,

PRIMARYKEY(id),

KEYindex2(name,age,type),

KEYindex3(name)

);