索引的最左前缀原则
最左前缀原则就是最左优先，
在创建多列索引时，要根据业务需求，
where子句中使用最频繁的一列放在最左边。
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4
如果建立(a,b,c,d)顺序的索引，d是用不到索引的，
如果建立(a,b,d,c)的索引则都可以用到，
a,b,d的顺序可以任意调整。
=和in可以乱序，
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，
mysql的查询优化器会帮你优化成索引可以识别的形式。
最左前缀原则可以是联合索引的的最左N个字段，也可以是字符串索引的最左的M个字符。举个例子，假如现在有一个表的原始数据如下所示：
并根据col3 ，col2的顺序建立联合索引，此时联合索引树结构如图下所示：
叶子结点中首先会根据col3的字符进行排序，若是col3相等，在col3相等的值里面再对col2进行排序，假如我们要查询where col3 like 'Eri%'，就可以快速的定位查询到Eric。
若是查询条件为where col3 like ‘%se’，前面的字符不确定，表示任意字符都可以，这样就可以导致全表扫描进行字符的比较，就会使索引失效。
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Michael's Blog！
相关推荐
2017-03-20
索引的概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)， 它们包含着对数据表里所有记录的引用指针。 索引是一种数据结构。 数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。 索引的实现通常使用B树及其变种B+树。 更通俗的说，索引就相当于目录。 为了方便查找书中的内容，通过对内容建立索引形成目录。 而且索引是一个文件，它是要占据物理空间的。 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等， 然后找到对应字典页码， 这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。
2017-03-20
索引回表
回表 查询时一些字段值拿不到，需要到主键索引B+树再查一次。 根据索引进行条件查询，回到主键索引树进行搜索的过程 因为查询还要回表一次，再次查询主键索引树，所以实际中应该尽量避免回表的产生。 解决回表问题可以建立联合索引进行索引覆盖，如图所示根据name字段查询用户的name和sex属性出现了回表问题： 那么我们可以建立下面这个联合索引来解决： 123456create table user ( id int primary key, name varchar(20), sex varchar(5), index(name, sex)) engine = innodb; 建立了如上所示的index(name,sex)联合索引，在二级索引的叶子结点的位置就会同时也出现sex字段的值，因为能够获取到要查询的所有字段，因为就不用再回表查询一次。 强制索引 force index
2015-03-20
索引底层实现
Hash索引 基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。 B-Tree索引（MySQL使用B+Tree） B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。 B+Tree索引 是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。 B+tree性质： n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。 B+ 树中，...
2016-03-13
索引的类型
从存储结构上来划分： BTree索引（B-Tree或B+Tree索引）； Hash索引； full-index全文索引； R-Tree索引 这里所描述的是索引存储时保存的形式 从应用层次来分： 主键索引； 普通索引； 唯一索引； 复合索引(联合索引)； 空间索引； 根据中数据的物理顺序与键值的逻辑（索引）顺序关系： 聚集索引(聚族索引)； 非聚集索引(非聚族索引)； 总结 索引类型 概念 普通索引 一个索引只包含一个列，一个表可以有多个单列索引 唯一索引 索引列的值必须唯一，但允许有空值 复合索引 多列值组成一个索引，专门用于组合搜索，其效率大于索引合并 聚簇索引 也称为主键索引，是一种数据存储方式。B+Tree结构，非叶子节点包含健值和指针，叶子节点包含索引列和行数据。一张表只能有一个聚簇索引。 非聚簇索引 不是聚簇索引，就是非聚簇索引。叶子节点只是存索引列和主键id。如果sql还要返回除了索引列的其他字段信息，需要回表，第一次索引一般是顺序IO，回表的操作属于随机IO。回表的次数越多，性能越差
2015-10-03
聚簇索引与非聚簇索引
在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。 而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。 聚簇索引与非聚簇索引的区别： 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号） 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可 注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。 非聚簇索引一定会回表查询吗？不一定，这涉及到查询语句所要求的字段是否全部命中了索...
2015-03-20
前缀索引
因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。 流程是： 先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1 再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1 找到最优长度之后，创建前缀索引 :create index idx_front on table_1 (col_1(4))
评论
