浅谈MySQL索引 实用又方便的方法

索引基础

   索引的作用其实就是在MySQL中高效的获取数据。在数据库中，一般索引会很大，不可能全部储存在内存中，会是以文件形式存储在文件系统中。这样查询索引就会设计到磁盘I/O，而磁盘I/O相对于内存的读写速度差几个数量级，索引的目的即是减少一次查询中的磁盘I/O次数。而在计算机中，当做一次I/O操作时，在吧当前磁盘地址数据读取到内存中时，也会把相邻地址的数据读到内存中，这个是计算机中著名的局部性原理:当一个数据被用到时，其相邻地址的数据也通常会马上被使用。由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率。索引数据结构的设计即是基于这个原理。

B-/+Tree索引简单分析

维基百科对B树的定义为：B树（B-Tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同，B-Tree为系统最优化大块数据的读和写操作。B-Tree算法减少定位记录时所经历的中间过程，从而加快存取速度。

B-Tree有一个重要的特点是：所有叶节点具有相同的深度，等于树高h。这样索引检索一次最多需要访问h个节点。

B-Tree和B+Tree的区别在于，B+Tree只有叶节点才存储数据，另外在B+Tree的每个叶子节点包含一个指向相邻叶子节点的指针，这样它即带有顺序访问功能。目的是为了提高区间查找和遍历的性能。

B+Tree的优点在于：

由于B+树在内部节点上不好含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子几点上关联的数据也具有更好的缓存命中率。
B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B-Tree则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+Tree好。
B-Tree的优点在于，由于B-Tree的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。
最左前缀匹配

建索引时都知道会有这一个最左前缀匹配原则，这个通常是在复合索引中遇到的。查询条件中的所有字段需要从左边起按顺序出现在多列索引中，查询条件的字段数要小于等于多列索引的字段数，中间字段不能存在范围查询的字段(<,like等)，这样的sql可以使用该多列索引。

可以使用B+Tree索引的查询类型

1.全值匹配

2.匹配最左前缀

3.匹配列前缀(可用用like a%,但不能使用like %b)

4.匹配范围值

5.精确匹配某一列和和范围匹配另外一列

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作（按顺序查找）。

索引建立原则

1.最左前缀匹配原则，组合索引必用，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的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少。

4.索引列不要参与计算，比如date_format(create_time,’%Y-%m-%d’) = ’2015-12-31’就使用不到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = str_to_date(‘2014-05-29’,’%Y-%m-%d’);

Mysql 优化原则

简化SQL，快速执行，无阻塞，简单SQL比复杂SQL更高效；
仅仅使用最有效的过滤条件，索引字段不是越多越好；
只取出自己需要的 Columns，避免使用select *；
覆盖索引可以直接返回结果，无须扫描数据；
例如：select id,status from tab where id=2 ，建立组合索引(id,status)，这个索引包含（或者说覆盖）所有需要查询的字段的值，MySQL利用索引返回select列表中的字段，而不必根据索引再次回表读取数据页。
不仅仅是select，delete/update语句也需要建索引；
尽可能在索引中完成排序（order by， group by的优化）；
尽量少用子查询，改写成多表JOIN；
多表JOIN，永远用小结果集驱动大的结果集；
索引列不能是表达式的一部分，也不能是函数的参数。

下面两例中即使在 id, gmt_created 上建立索引，也会导致索引失效。

 
select id from tab where id+1 = 5;
select id,value from tab where to_days(now()) - to_days(gmt_created) <= 10;
 
应该养成简化 where 条件的习惯，始终将索引列单独放在比较符号的一侧。正确的写法是：

select id from tab where id = 5-1;
select id, value from tab where gmt_created >= DATA_SUB(now(),interval 10 day );