转:建立索引的原则以innodb为例

一、写在前面

        随着开发、测试任务进入尾声&＃xff0c;大家都在整理一些项目发布前的一些准备工作&＃xff0c;其中一个重要的工作就是为之前写的一些sql语句建立索引&＃xff0c;这高并发、高访问量的环境下是非常有必要的&＃xff0c;建立一个好的索引能够极大地提高sql语句的查询效率&＃xff0c;那么问题来了&＃xff0c;到底什么是索引&＃xff0c;怎样才能建立一个好的索引呢&＃xff1f;本文以mysql Innodb存储引擎为例&＃xff0c;结合实际的项目来看一下&＃xff0c;如何建立一个好的而索引。

二、索引定义

        MySQL官方对索引的定义为&＃xff1a;索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干&＃xff0c;就可以得到索引的本质&＃xff1a;索引是数据结构。
        我们知道&＃xff0c;数据库查询是数据库的最主要功能之一&＃xff0c;例如下面的SQL语句&＃xff1a;SELECT * FROM test_table WHERE id &＃61; 99 &＃xff1b;可以从表test_table中获得id为99的数据记录。
        我们都希望查询数据的速度能尽可能的快&＃xff0c;因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search)&＃xff0c;遍历test_table然后逐行匹配id的值是否是99&＃xff0c;这种复杂度为O(n)的算法在数据量很大时显然是糟糕的&＃xff0c;好在计算机科学的发展提供了很多更优秀的查找算法&＃xff0c;例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现&＃xff0c;每种查找算法都只能应用于特定的数据结构之上&＃xff0c;例如二分查找要求被检索数据有序&＃xff0c;而二叉树查找只能应用于二叉查找树上&＃xff0c;但是数据本身的组织结构不可能完全满足各种数据结构(例如&＃xff0c;理论上不可能同时将两列都按顺序进行组织)&＃xff0c;所以&＃xff0c;在数据之外&＃xff0c;数据库系统还维护着满足特定查找算法的数据结构&＃xff0c;这些数据结构以某种方式引用(指向)数据&＃xff0c;这样就可以在这些数据结构上实现高级查找算法。这种数据结构&＃xff0c;就是索引。
        举上面的例子主要是为了简单说明地说明索引的作用&＃xff0c;包括mysql Innodb在内的大部分数据库系统及文件系统并没有选择二叉树结构作为索引&＃xff0c;而是采用了B-Tree或其变种B&＃43;Tree作为索引结构&＃xff0c;这种索引结构可以最大限度地减少查找过程中磁盘I/O的存取次数&＃xff0c;关于什么是B-Tree或B&＃43;Tree以及选择它们做数据库索引结构的原因&＃xff0c;大家可以自行去学习。下面我们首先介绍下mysql Innodb引擎的两种B&＃43;Tree索引。

三、Mysql Innodb B&＃43;Tree索引

1. 一种是主键索引&＃xff0c;主键索引即聚集索引&＃xff08;Cluster Index&＃xff09;&＃xff0c;它不仅有主键&＃xff0c;而且有主键所属的全部数据&＃xff0c;所以在Innodb中&＃xff0c;主键索引即数据&＃xff1b;
2. 一种是列值为Key&＃xff0c;主键位置为Value即 (列值, 主键位置) 的非主键索引&＃xff08;Secondary Index&＃xff09;           Innodb属于索引组织表&＃xff0c;所有的数据全部挂在主键叶子节点下。所以如果不能保证主键的插入顺序&＃xff0c;那么会发生大量的主键节点分裂&＃xff0c;产生大量的I/O操作。另外Innodb规定单个索引字段的长度不得超过768字节&＃xff0c;否则截断超出长度不放入索引。         Innodb的非主键索引全部都指向主键索引&＃xff0c;查找非主键索引无法获得整行数据&＃xff0c;需要通过叶子节点的指针查到其主键索引的位置才能获得整行数据&＃xff0c;所以主键索引必须设计得尽可能小&＃xff0c;否则非主键索引将会非常的大。

四、建立索引的原则

        下面我们看一下建立一个好的索引需要遵循的原则&＃xff0c;并结合具体的例子来做说明&＃xff1b;
1. 最左前缀匹配原则&＃xff0c;非常重要的原则&＃xff0c;mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配&＃xff0c;比如a &＃61; 1 and b &＃61; 2 and c > 3 and d &＃61; 4 如果建立(a,b,c,d)顺序的索引&＃xff0c;d是用不到索引的&＃xff0c;如果建立(a,b,d,c)的索引则都可以用到&＃xff0c;a,b,d的顺序可以任意调整。
2. &＃61;和in可以乱序&＃xff0c;比如a &＃61; 1 and b &＃61; 2 and c &＃61; 3 建立(a,b,c)索引可以任意顺序&＃xff0c;mysql的查询优化器会帮你优化成索引可以识别的形式。
3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)&＃xff0c;表示字段不重复的比例&＃xff0c;比例越大我们扫描的记录数越少&＃xff0c;唯一键的区分度是1&＃xff0c;而一些状态、性别字段可能在大数据面前区分度就是0&＃xff0c;那可能有人会问&＃xff0c;这个比例有什么经验值吗&＃xff1f;使用场景不同&＃xff0c;这个值也很难确定&＃xff0c;一般需要join的字段我们都要求是0.1以上&＃xff0c;即平均1条扫描10条记录
4. 索引列不能参与计算&＃xff0c;保持列“干净”&＃xff0c;比如from_unixtime(create_time) &＃61; ‘2015-08-14’就不能使用到索引&＃xff0c;原因很简单&＃xff0c;b&＃43;树中存的都是数据表中的字段值&＃xff0c;但进行检索时&＃xff0c;需要把所有元素都应用函数才能比较&＃xff0c;显然成本太大。所以语句应该写成create_time &＃61; unix_timestamp(‘2015-08-14’)。
5. 尽量的扩展索引&＃xff0c;不要新建索引。比如表中已经有a的索引&＃xff0c;现在要加(a,b)的索引&＃xff0c;那么只需要修改原来的索引即可。
6. 在order by或者group by子句中&＃xff0c;如果想通过索引来进行排序&＃xff0c;所建索引列的顺序必须与order by或者group by子句的顺序一致&＃xff0c;并且所有列的排序方向&＃xff08;倒序或者正序&＃xff09;都一样&＃xff1b;如果查询关联多张表&＃xff0c;则只有order by子句引用的字段全部来自第一张表时&＃xff0c;才能利用索引来排序&＃xff1b;order by或者group by语句与查询型语句的限制是一样的:需要满足索引的最左前缀原则&＃xff1b;否则mysql就要执行排序操作&＃xff0c;无法利用索引来排序&＃xff1b;&＃xff08;有一种情况order by或者group by子句可以不满足最左前缀原则&＃xff0c;就是其前导为常量的时候&＃xff0c;如果where或者join对这些列指定了常量&＃xff0c;就可以弥补索引的不足&＃xff09;。

五、举例

        语句1&＃xff1a;

        语句2&＃xff1a;

        对于这两条语句&＃xff0c;如果单独进行考虑的话&＃xff0c;大家可能会建立两个索引&＃xff1b;
针对语句1建立(status,netting_batch_no,debtor_agent_member_id)&＃xff1b;
针对语句2建立(netting_batch_no,debtor_agent_member_id,transaction_currency)&＃xff1b;
如果综合考虑来看的话&＃xff0c;其实一个索引就够了&＃xff0c;即(netting_batch_no,debtor_agent_member_id)&＃xff0c;这里没必要将status或者transaction_currency字段放到索引中&＃xff0c;因为这两个字段的区分度太差&＃xff1b;
根据建立索引的原则2&＃xff0c;语句1是可以走到这个索引的&＃xff1b;
根据建立索引的原则1&＃xff0c;语句2也是可以走到这个索引的&＃xff1b;
索引不是越多越好&＃xff0c;建立过多的索引会增加数据库内存或者磁盘的消耗&＃xff0c;并且会影响到得插入、删除等操作的性能&＃xff0c;索引在建立索引时要遵循索引建立的原则&＃xff0c;通盘考虑&＃xff1b;