MySQL知识体系——索引

规范与建议

1.  命名规则：表名_字段名
2.  需要加索引的字段，要在where条件中
3.  如果where条件中是OR关系，加索引不起作用
4.  能用小类型别用大类型字段
5.  索引 key_len 长度过大，也会影响 SQL 性能。所以尽量不默认 null，会占用字节、索引长度。
6.  常用的字段放在前面；选择性高的字段放在前面
7.  对较长的字符数据类型的字段建索引，优先考虑前缀索引，如 index(url(64))
8.  只创建需要的索引，避免冗余索引，如：index(a,b)，index(a)
9.  使用联合索引，以避免回表，达到覆盖索引
10.  联合索引遵循最左原则
11.  索引不可滥用，索引会占用存储空间并且增加数据更新操作的复杂度，降低CUD(create/update/delate)效率

回表

先了解一个概念，MySQL对 WHERE 中条件的处理，根据索引使用情况分成三种：index key, index filter, table filter

1. index key

用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于一个范围，至少包含一个起始与一个终止，因此Index Key也被拆分为Index First Key和Index Last Key，分别用于定位索引查找的起始，以及索引查询的终止条件。

2. index filter

在使用 index key 确定了起始范围和介绍范围之后，在此范围之内，，还有一些记录不符合 WHERE 条件，如果这些条件可以使用索引进行过滤，那么就是 index filter。

3. table filter

WHERE 中的条件不能使用索引进行处理的，只能访问table，进行条件过滤了。

从普通索引查出主键索引，然后查询出数据的过程叫做回表。回表一次就会执行一次查询，所以避免回表是减少数据库压力、提高效率的有效手段。在InnoDB中，使用联合索引配合主键索引可以直接返回结果而不需要回表查询。

联合索引（复合索引）与前缀索引（最左原则）

Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是(a,b,c)，可以支持 a | a,b | a,b,c 3种组合进行查找，但不支持 b,c 进行查找。这是最左原则的第一层意思：联合索引的多个字段中，只有当查询条件为联合索引的第一个字段时，索引才会有效。

条件 WHERE a LIKE 'perfix%'; 索引也会有效。这是最左原则的第二层意思：根据字段值最左若干个字符进行的模糊查询，索引有效。

覆盖索引

覆盖索引是对联合索引的合理利用。

比如 SELECT a, b FROM table WHERE a = 'wangnima'; ，如果我们已经创建了(a)或(a,b)的联合索引，那么这条语句会直接从索引返回而不会发生回表。即创建索引的字段覆盖了查询字段。

如果执行 SELECT c FROM table WHERE a = 'wangnima';  ，就会发生回表，因为我们的辅助索引树中，没有字段 c 的数据，需要拿到主键索引的关键字，去主键索引中回表查询。

但是需要注意的是，索引虽好不可滥用。

索引下推（Index Condition Pushdown (ICP)）

结合在 回表 概念中引出的三种索引使用情况（index key, index filter, table filter），ICP 技术，就是 index filter 技术。MySQL的架构分为服务器层和引擎层。

官方解释（https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html）

索引条件下推（ICP）是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP，存储引擎将遍历索引以定位基表中的行，并将它们返回到MySQL服务器，该服务器将计算基表行的where条件。在启用ICP的情况下，如果部分where条件可以通过只使用索引中的列来计算，MySQL服务器会把where条件的这部分 推入 存储引擎。然后，存储引擎通过使用索引条目来评估所推送的索引条件，并且只有在满足该条件时才从表中读取行。ICP可以减少存储引擎必须访问基本表的次数和MySQL服务器必须访问存储引擎的次数。

根据官方的指导，我们来做个验证：   

EXPLAIN   
     SELECT * FROM people   
     WHERE zipcode='95054'   
     AND lastname LIKE '%lao%'   
     AND address LIKE '%Main Street%'; 

官方解释：

EXPLAIN使用“索引条件下推”时，输出显示 Using index condition在 Extra列中。

假设一个表包含有关人员及其地址的信息，并且该表的索引定义为 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的zipcode价值但不确定姓氏，我们可以这样搜索：   

SELECT * FROM people  
      WHERE zipcode='95054'  
      AND lastname LIKE '%etrunia%'  
      AND address LIKE '%Main Street%'; 

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!