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%'; 

（编辑：湖南网）

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