MySQL索引的学习

索引

为什么要有索引呢？其实是为了构建一种数据结构，可以在上面运用一种高效的数据结构，从而提高查询数据的速度；

索引（Index）：帮助MySQL高效获取数据的一种数据结构，所以，我们要想搞明白索引，也就是要搞明白他的数据结构是怎样的。

我们先来了解一下比较常见的的查询算法：

• 顺序查找：就是按照顺序对比每个元素的方法，很明显，效率是非常低的；

• 二分查找：首先保证数组是有序的，从数组的中间位置开始找，如果正好找到目标元素，则返回；否则就比较目标值与中间元素值得大小，然后再符合范围的数组中重复该查询操作；

• 二叉排序树查找：首先我们先来了解一下什么是二叉排序树：
  
  他的特点：
  1.它左子树的所有节点都要小于它的根节点；
  2.它右子树的所有节点都要大于它的根节点；
  3.它的左右子树都是有序的；
  二叉排序树查找的原理：
  如果它的根节点为空，则返回失败；否则判断根节点和目标值是否相等，相等则直接返回根节点；否则判断目标是是否小于根节点，如果小于，则去它的左子树中查找；否则就去它的右子树中查找；

• 哈希散列法（哈希表）：根据key值和哈希函数创建一个哈希表（散列表），通过键值，和散列函数计算数据元素的位置；它的时间复杂度几乎是O(1),主要取决于冲突的次数；

• 分块查找：又被称为索引顺序查找，它是顺序查找的一种改进方式，将n个数据划分为m块，块与块之间是有序的，每一块里面的数据也是有序的；这里的有序是针对某一关键字的有序；

但是分析来看每种查找算法都是针对于一定的数据结构上的，比如说二分查找，要求查找的数据本身是有序的；所以数据库必须维护和满足这些特定的数据结构，这些数据结构以某种方式指向数据，所以我们才能应用这些数据结构实现我们的查找算法，这些特定的数据结构，就是索引。

平衡多路查找树（B-Tree）

B-Tree是为磁盘等外存储设备设计的一种平衡查找树；
系统从磁盘读取数据到内存时，是以磁盘块为单位进行读取的，位于同一个磁盘块的数据会被一次性读取出来，并不是你要什么就取什么；
InnoDB存储引擎有页的概念，页是InnoDB存储引擎的基本单位，一般默认大小为16k,也就是说InnoDB是以页为单位和磁盘打交道的，每一次取数据是取一页大小的数据，能够大大降低内存与磁盘之间的I/O操作；
页的大小可以通过参数innodb_page_size来设定，通常设置为4k, 8k,16k;在MySQL中可以通过命令来查看 页的大小：

show variable like "innodb_page_size";

索引下推原理与执行流程

页的初始结构：

当我们向数据库中添加一条数据时，数据就会添加到用户数据区域，这里数据的第一列设定为我们的主键索引（int类型）；

当我们再添加一条数据（1,1,1,1,‘a’）时，那么问题来了，这条数据会添加在之前那条数据的什么位置呢？答案如图：

为什么在页的数据区域中，第二条数据会放在第一条数据的前面呢，这是由于B-tree的原因，InnoDB会根据主键索引进行排序，然后在插入，这样做虽然影响了插入的性能，但是却提高了查询的性能；为什么这么说呢？

我们又橡数据库中插入了一条数据，这时候执行一条SQL“select * from t where a = 3”(假设表明为t,a为主键列)；
这时候InnoDB从数据库中取出一页的数据放到内存，然后进行查询，第一条不是然后遍历第二条，明显第二条也不是，但是第二条的主键索引的值已经大于我们要查的数据了，这时候就不会继续向下找了，所以说提高了查询的效率；
但是有一个问题我们查的是“select * from t where a = 10000”呢，难道要一条一条去遍历吗？这时候便用到了目录（索引）了；

假设对我们的数据进行分组，分为两组，然后对应页目录里就要有两个目录项，每个目录项里面存储了每一个分组的起始的主键值和一个指向每一组起始数据的指针；
这时候我们在执行“select * from t where a = 3”时就要先确定它在哪一个组里面，然后在这个组里面进行查询就OK了；
但是一个新的问题是，如果我们插入的数据主键一直是无序的，而且当我们一页数据满了，就要不停地去调整数据的位置，把它按照顺序放到对应的页中，这样也是很费时间的，所以从原理的角度上讲，最好将主键索引设成自增；

这时候我们在执行sql"select * from t where a=6"的时候，它又是怎么查找的呢？其实它并不能直接定位到第二页，他是先从第一页开始找，第一页没有才会去第二页找；但是随着数据的增多，是不是又和最初的问题一样了，那怎么办呢？因为这是个链式结构，查询速度肯定是很慢的，所以以空间换时间，用同样的思想在加一页；

所以整个结构差不多就是这样一个B+Tree的结构
从上图我们可以总结出B+Tree的特点：
1.B+Tree是有序的；
2.B+Tree的一个节点可以包含多个数据；
3.非叶子节点在其它层是冗余的；
4.非叶子节点的指针是双向的；这一点是MySQL在B+Tree的结构上的改进；
在遍历时，如果查询条件是主键时，就会根据整个B+Tree的主键索引来查询；但是如果查询条件不是主键时，这个时候就只能进行全表扫描了；

联合索引

根据联合索引也会对应一套B+Tree的数据存储，这样对于数据库来说冗余的问题还是很大的，所以一般会根据联合索引生成一套只含有索引字段的B+Tree结构，然后在这个结构中另外存储每条记录对应的主键，这样再遍历时根据联合索引找到对应记录的主键，再根据主键去主键索引的结构中取出整条数据；这样就避免了很大部分的的数据冗余；

联合索引遵循一个原则：最左匹配原则（最左前缀原则）；
就是说当我们查询的条件中不含有创建索引的第一个字段时，是不能使用联合索引的结构来查询的；

索引优化策略

• 最左前缀匹配原则
• 主键外检一定要建立索引
• 对经常使用where，on,group by, order by 的列添加索引
• 尽量选择区分度高的列，区分度的公式：count(distinct col) /count(*);表示字段不重复的比例，比例越大说明我们扫描的记录数越少；比如说主键的区分度为1，但像性别这种区分度接近0；
• 对较小的数据建立索引，这样会使索引文件更小，同时内存中可以装载更多的索引键；
• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 这一条我还不太明白
• 为较长的字符串使用前缀索引
• 尽量扩展索引，不要新建索引；比如表中有一个（a）索引，现在加（a,b）的索引，只要修改原来的索引就好了
• 不要过多地创建索引，权衡索引的个数与DML之间的关系，DML也就是插入，删除的操作；这里需要权衡一个问题，建立索引的目的是为了提高查询效率，但建立的索引过多就会影响插入，删除的速度，因为我们修改的表数据，索引也需要调整重建；
• 对于like查询，”%”不要放在前面。
  SELECT * FROMhoudunwangWHEREunameLIKE’后盾%’ – 走索引
  SELECT * FROMhoudunwangWHEREunameLIKE “%后盾%” – 不走索引
• 查询where条件数据类型不匹配也无法使用索引
  字符串与数字比较不使用索引;
  CREATE TABLEa(achar(10));
  EXPLAIN SELECT * FROMaWHEREa=“1” – 走索引
  EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引
  正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因