mysql索引在存储引擎中的实现及索引的使用原则

索引在存储引擎中的实现

主要就看MYISAM和InnoDB，这两个是最常见的。

命令： show variables like 'datadir';可以查看数据和索引存放的目录

在目录下我们看到有“.frm”文件，这是表结构定义的文件，任何存储引擎都有的。

MYISAM存储引擎

MYISAM存储引擎的表有两个文件：“.MYD”和“.MYI”。
D就代表data，用来存放数据的文件。
I代表Index，用来存放索引，叶子节点存放数据的地址值
检索方式：在MYISAM中，主键索引和辅助索引的检索方式是一样的。都是先根据索引在“.MYI”文件中找到数据存放的地址值，再去“.MYD”文件中拿到数据返回，如图：

InnoDB存储引擎

InnoDB存储引擎的表只有一个“.ibd”文件，索引和数据放在一起，叶子节点存放数据
InnoDB中，以主键索引来组织数据存储，叶子节点按主键索引排序，索引的顺序和数据存放的顺序一致，这就叫做聚集索引。
那么主键索引存放的就是索引和数据，而辅助索引存储的就是索引和主键的值。也就是说，如果使用辅助索引，那么检索方式就是，先在辅助索引的B+树上找出主键值，再去主键索引的B+树上拿到具体的数据。（也就是遍历两个B+树）

问：既然数据是在主键索引的B+树上存放的，那如果没有主键索引怎么办？数据放在哪？
答:mysql官网上说：
1.如果你创建了Pirmary Key，那么这个key就是聚集索引，即存放数据。
2.如果没有主键索引，但是有个唯一索引（unique Key）而且它是NOT NULL，那么就会把这个唯一索引当做存放数据的聚集索引。
3.第三种情况就是这个表中什么索引都没有，怎么办？其实在InnoDB中它会有一个隐藏的列rowId，它会把这个rowId作为聚集索引，并存放数据。要记住一张表不可能没有索引和聚集索引的。

索引的使用

建立索引的基本规则

误区：既然索引那么好，那干脆在所有的字段上都创建索引。

我们来看看建立索引的基本规则：
（1）列的离散度：
离散度公式：count(distinct(字段名)) / count(*) （这个列去重后的行数除以总行数）
比如，性别和手机号这两个字段哪个离散度更高？
假设有10行数据，性别去重后就是非男即女，离散度=2/10=0.2。而手机号肯定不会重复即10/10=1。很显然，手机号的离散度更高，相比较下更适合建立索引。
（2）联合索引最左匹配原则：
在我们使用联合索引时，必须要遵循它创建的顺序，必须从左边开始，中间不能中断。
比如，如果我们建立了一个2列的联合索引(col1,col2),实际上已经建立了两个索引(col1)、(col1,col2）;
如果有一个3列索引(col1,col2,col3)，实际上已经建立了三个索引(col1)、(col1,col2)、(col1,col2,col3)。

举例：

1、b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

2、比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

覆盖索引

注意：覆盖索引不是一种索引的类型，而是一种情况。
说覆盖索引前，要先知道什么是回表：
在InnoDB中,上文说过，使用辅助索引时，会检索两个B+树，即从辅助索引中拿到对应的主键值时，再去主键索引的树上取出所需数据，这个过程就叫做回表。
覆盖索引就是要找的数据刚好就在辅助索引上，比如，select name from user where name = 'zs'; 如果用name这个字段建立辅助索引时，执行这条语句就不需要回表拿数据了，因为你要查的name字段就在辅助索引上，索引不需要回表拿数据了。

创建索引

1.在用于where判断order排序和join on的字段上创建索引。
2.索引个数不要过多（索引占用磁盘空间，而且增删改的时候需要维护索引的B+树）。
3.离散度低的字段，比如性别，不要建立索引。
4.频繁更新的值，不要作为主键或索引。
5.联合索引把离散度高的值放在前面
6.不建议用无序的字段作为索引，如UUID，身份证号等。因为你插入数据的时候是乱序的，不知道分配在哪个页上，如果插入到的是写满的页，就会导致页的分裂。最好使用自增或者递增的值作为索引。
7.过长的字段建立前缀索引（短索引），不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

什么时候用不到索引

1.在索引列上使用函数replace、substr、concat、sum、count、avg。。。等表达式。
2.字符串类型不加引号，出现隐式转换，导致用不上索引。
3.like查询以%开头。
4.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
5.如果mysql的优化器感觉全表扫描要比使用索引快,则不使用索引。

归根到底，到底用不用索引，咱说了不算，咱们能做的就是尽可能的让它能使用上索引。mysql优化器（optimizer）说了才算，它会计算使用索引和不使用索引的成本，择优选择生成执行计划。

<<上一篇：mysql索引及索引的存储结构
>>下一篇：浅析mysql事务与锁机制