mysql索引小结

在日常工作中，我们经常会涉及到数据库的操作，而且经常性的，数据库的查询效率直接决定了我们代码的执行效率。我们以mysql的InnoDB引擎为例。讲一下如何使我们的数据库的查询的效率更高。

什么是索引

mysql的官方文档对其的定义是。索引是帮助MySQL高效获取数据的数据结构。数据结构有很多种，比如Hash，二叉树，B-Tree，B+Tree等。mysql使用的索引结构是B+Tree，为什么不使用其他数据结构呢，以Hash为例，Hash可以很快速的定位到数据的具体位置，但是无法使用于范围查找，所以不太合适。

B+Tree

那为什么MySQL要使用B+Tree呢。因为索引以文件的形式存储在磁盘上，每次查找索引文件，都会产生磁盘的I/O，而磁盘的I/O相比于内存的读取，高了好几个数量级，所以在进行索引查找的时候，尽可能的减少磁盘的I/O，会极大的提高效率。并且磁盘还有预读的原理，就是在磁盘读区的当前位置，然后顺序读取一定长度的内容放入内存，预读的长度称为页，一个页4k的大小，所以尽量的使一个节点的大小保持在4k。不会造成浪费。因为逻辑相邻的节点，物理上不一定相邻。
而B+Tree的这种数据结构，数据在各种情况下，比二叉树跟红黑树的深度少，也就是磁盘I/O的次数少，而相对于B-Tree，因为B+Tree只有叶子结点才存储数据，所以一次磁盘I/O所获取的数据范围特别大，正常稳定在3次I/O就可以获取数据。
而对于InnoDB而言数据文件本身就是索引文件，那索引文件必须要有主索引，所以InnoDB的表必须要有主键索引，如果你不设置主键索引， 那InnoDB表生成一个隐含字段作为主键，字段长度为6个字节，类型为长整形。所以所有的辅助索引，都会引用主索引，所以避免使用过长的字段作为主键索引。

mysql的优化。

1.首先，建表以及新增字段的时候，需要判断一下这个字段是不是必须加到这个表里，是否可以有一个扩展表，存储一些不太常用的字段，以及经常更新的字段，比如用户表User，上一次购买登录时间字段等，经常会更新，所以可以不跟User表放在一起，因为经常更新会使得mysql的查询缓存失效。而一些不是经常用的字段，比如用户的爱好，地址什么的。其实只有展示的时候会用，很多业务上的操作，并不会用到这些字段，可以考虑把这些字段放到其他表里面，减少User的大小。小点的表，那它的索引文件即数据文件就小，就会比大表有更好的性能，
2.对于2个表要join的字段，需要建立索引，并且他们的类型与字符集也需要保持相同
3.设置字段的时候，尽量是not null，如果该字段的值有null，而且需要建立索引，那就跟其他非null字段建立联合索引。否则该字段的索引不生效。
4.mysql的索引，有一个最左匹配原则，即如果你一个表中建立了索引(a,b,c)，那么只有where语句中存在a的情况下，才能使用的索引，但是如果a,b可以全部使用到索引，如果是a,c则只能使用到a这个索引。
那么如果判断使用到多少个索引呢，可以使用explain，然后查看key_len的值，是否跟使用全部索引的值一致，如果一致则使用全部的索引，如果不一致，就需要计算索引字段的长度，然后自己计算key_len的值了。
5.范围列也可以用到索引，但必须是最左匹配的字段，但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则后一个范围列无法用刀索引。
6.查询的列不能使用函数或者表达式
虽然使用索引能大大加快查询的效率，但是索引也不是加的越多越好的，因为增加索引会增加内存的使用，而且删除，修改，新增都会维护这个索引结构，影响效率。所以索引的选择就至关重要了，我们需将选择索引选择性高的数据，所谓索引的选择性Selectivity是指不重复的索引值Cardinality与表记录数#T的比值：
Index Selectivity = Cardinality / #T
选择性越高的，索引创建的价值越大。
比如user表有2个字段，一个是是否离职，一个是员工编码，这2个字段为联合索引，那么这个索引的选择性为
SELECT count(DISTINCT(concat(是否离职, 员工编码)))/count(*) AS Selectivity FROM user；