注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

@fc_lamp

关注Web应用解决方案MySql/PHP/Python一盏名为"飞川"的灯~

 
 
 

日志

 
 

建立索引的原则 - 以innodb为例  

2016-03-02 00:02:21|  分类: Web技术-Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

一、写在前面

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

二、索引定义

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

三、Mysql Innodb B+Tree索引

  1. 一种是主键索引,主键索引即聚集索引(Cluster Index),它不仅有主键,而且有主键所属的全部数据,所以在Innodb中,主键索引即数据;
  2. 一种是列值为Key,主键位置为Value即 (列值, 主键位置) 的非主键索引(Secondary Index) 

四、建立索引的原则

        下面我们看一下建立一个好的索引需要遵循的原则,并结合具体的例子来做说明;
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(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录


4. 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ‘2015-08-14’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(‘2015-08-14’)。
5. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
6. 在order by或者group by子句中,如果想通过索引来进行排序,所建索引列的顺序必须与order by或者group by子句的顺序一致,并且所有列的排序方向(倒序或者正序)都一样;如果查询关联多张表,则只有order by子句引用的字段全部来自第一张表时,才能利用索引来排序;order by或者group by语句与查询型语句的限制是一样的:需要满足索引的最左前缀原则;否则mysql就要执行排序操作,无法利用索引来排序;(有一种情况order by或者group by子句可以不满足最左前缀原则,就是其前导为常量的时候,如果where或者join对这些列指定了常量,就可以弥补索引的不足)。


来源:https://yq.aliyun.com/articles/6901?hmsr=toutiao.io&utm_medium=toutiao.io&utm_source=toutiao.io

  评论这张
 
阅读(68)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017