mysql索引的优化和原理_mysql索引原理及优化

采用B+树的原因

B+树的特点决定的。查询一般为log(n)。选择B+树而不是其他数据结构的原因主要是因为数据是保存在硬盘上而不是内存中，所以减少磁盘IO次数才是提升效率的关键。B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

B+树的查询效率更加稳定：由于非终结点并不是最终指向文档内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

总结：

Hash索引查询是O(1),应该是更快，但是为啥不用呢？因为大多数情况下并不是每次只查询一个，而是多个，比如前10条，b+树叶子节点有链接，所以能快速查询。另外，文档或数据库索引数据比较大，也不做不到一次加载到内存，但是B树可以一个一个节点的加载，进行查询。

索引实现

不同的存储引擎采用不同的实现方式。

MyISAM 非聚集索引

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

非聚集索引

我们看到，索引文档本身和数据表的文档是分离的，这也是非聚集索引的由来。叶子节点的data区域存放的是数据表每条记录的地址。

InnoDB聚集索引

InnoDB的主键索引也使用B+Tree作为索引结构，但这里表数据文档本身就是B+树的一个结构，也就是说叶子节点的data区域保存了完整的数据表的一条记录。索引的key就是表的主键，这就是聚集索引的由来。

Alt text

由此看来，InnoDB的数据表必须要有一个主键，如果没有指定，mysql就会自动选择一个唯一标识记录的作为主键。那这样的也不存在怎么办？mysql就会生成一个隐含的6字节长整型作为主键。

InnoDB的次级索引

InnoDB还有个地方与MyISAM不同，就是辅助索引data记录的是主键的值而不是数据表记录的地址。此时，索引文档和数据文档是分开的。

这样就要注意了，InnoDB主键不要太大，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

当通过辅助索引来寻找数据时，Innodb存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。这种在二级索引中不能找到所有需要的数据列的现象，被称为非覆盖索引，反之称为覆盖索引。

优化索引

最左前缀匹配原则

我们创建组合索引：a,b,c

其实是建了三个索引，a、ab、abc

where查询时请注意:

where a=3 and b=4 and c =5是有效的。

where b =4 and c=5直接跳过a条件是不能利用组合索引的，因为没有最左的。

where a =4 and c=5只利用了a这个一个索引条件。

where b=2 and a=1是可以的，顺序不重要。

不要在比较运算符左侧使用函数或进行计算

在SQL语句的比较运算符左侧使用函数或进行计算会使索引失效。

select * from employees where emp_no + 1 = 10005;索引无效。

explain select * from employees where emp_no = 10005-1;索引有效。