如何通过索引说数据库优化能力

索引是数据库优化所必需的工具&＃xff0c;在面试的时候一般不会问概念性的问题&＃xff0c;会问什么问题又如何回答呢&＃xff1f;请看下文。

　　如果我们需要招个Java方面的高级程序员&＃xff0c;一方面看年限&＃xff08;本科3年&＃xff09;&＃xff0c;具体到数据库方面的技能要求&＃xff0c;包括如下三个方面&＃xff1a;

　　第一&＃xff0c;是否会基本的增删改查&＃xff0c;存储过程等技能&＃xff0c;是否会用些groupby,having,distinct,exist,in,with等高级点的语句。这点一般都没问题&＃xff0c;甚至一个刚毕业的大学生或工作经验2年之内的初级程序员也没问题&＃xff0c;也就是说&＃xff0c;这个是高级程序员必备的&＃xff0c;你会了是应该&＃xff0c;不会甚至不及毕业生。

　　第二&＃xff0c;有没有设计表的经验&＃xff0c;这方面的面试技能也已经在数据库方面的面试技巧&＃xff0c;如何从建表方面展示自己能力里说了&＃xff0c;关键一点&＃xff0c;你得结合实际需求来说。

　　第三也是关键一点&＃xff0c;在数据库优化方面&＃xff0c;你是否有相关经验。

　　这是个开放性的问题&＃xff0c;大神们知道&＃xff0c;可以从分区&＃xff0c;根据执行计划优化等很多方面来考虑&＃xff0c;对高级程序员&＃xff0c;我的期望是你至少能说点索引相关的。但根据我的面试经验&＃xff0c;很少有候选人&＃xff08;特别是高级程序员候选人&＃xff09;能比较深入地叙述。

　　开场白说了不少&＃xff0c;下面进入正文。

　　索引是数据库优化所必需的工具&＃xff0c;在面试的时候一般不会问概念性的问题&＃xff0c;因为大家都能从教科书上找到答案&＃xff0c;所以一般会问以下两方面的问题&＃xff1a;

　　①索引有什么代价&＃xff1f;哪些场景下你需要建索引&＃xff1f;或者有时候反过来问&＃xff0c;哪些场景下不推荐建索引。

　　②建好索引之后&＃xff0c;怎么才能最高效地利用索引&＃xff1f;或者反过来问&＃xff0c;请说出一个无法有效利用已建索引的案例。

　　从结构上来看&＃xff0c;索引好比是一棵B树&＃xff08;也叫B*或者B&＃43;&＃xff09;&＃xff0c;假设学生表里只有学生ID和姓名两列&＃xff0c;该学生表里有1000个学生&＃xff0c;学号分别从1到1000&＃xff0c;如果针对ID建立索引&＃xff0c;大致的结构如下图所示。 

　　当然&＃xff0c;在实际的数据库系统中&＃xff0c;索引要比这个复杂得多&＃xff0c;但从这个图里&＃xff0c;我们能大致看出索引的工作原理。 
索引建好后&＃xff0c;如果我们要查找ID为111的学生&＃xff0c;则数据库系统就会走索引&＃xff0c;从图2.1中我们可以看到&＃xff0c;根据根节点的指引&＃xff0c;会找到第二层从左往右第二个数据块&＃xff0c;以此类推&＃xff0c;会在第四层里得到ID为111的物理地址&＃xff0c;然后直接从硬盘里找数据。

　　反过来&＃xff0c;如果没有建索引&＃xff0c;数据库系统可能就要从一个大的范围里逐一定位查找&＃xff0c;效率就没这么高了。

　　索引的好处大家已经看到了&＃xff0c;那么为了得到这个“查询效率高”的好处&＃xff0c;我们要付出了什么样的代价呢&＃xff1f;

　　1索引需要占硬盘空间&＃xff0c;这是空间方面的代价。

　　2一旦插入新的数据&＃xff0c;就需要重新建索引&＃xff0c;这是时间上的代价。

　　关于索引性能问题&＃xff0c;我会细问&＃xff0c;你建索引的表规模多少&＃xff1f;不少人直接告诉我表就几千条&＃xff0c;我或者问&＃xff0c;索引有什么代价&＃xff1f;不少回答是索引是只有好处没坏处的&＃xff0c;也就是说&＃xff0c;可以随便建。

　　对此我们来详细分析下&＃xff08;也就是大家在面试时需要说的&＃xff09;&＃xff1a;

　　场景一&＃xff0c;数据表规模不大&＃xff0c;就几千行&＃xff0c;即使不建索引&＃xff0c;查询语句的返回时间也不长&＃xff0c;这时建索引的意义就不大。当然&＃xff0c;若就几千行&＃xff0c;索引所占的空间也不多&＃xff0c;所以这种情况下&＃xff0c;顶多属于“性价比”不高。

　　场景二&＃xff0c;某个商品表里有几百万条商品信息&＃xff0c;同时每天会在一个时间点&＃xff0c;往其中更新大概十万条左右的商品信息&＃xff0c;现在用where语句查询特定商品时&＃xff08;比如wherename&＃61;‘XXX’&＃xff09;速度很慢。为了提升查询效率可以建索引&＃xff0c;但当每天更新数据时&＃xff0c;又会重建索引&＃xff0c;这是要耗费时间的。这时就需要综合考虑&＃xff0c;甚至可以在更新前删除索引&＃xff0c;更新后再重建。

　　场景三&＃xff0c;从上图中可以看到&＃xff0c;因为在数据表里ID值都不相同&＃xff0c;所以索引能发挥出比较大的作用。相反&＃xff0c;如果某个字段重复率很高&＃xff0c;如性别字段&＃xff0c;或者某个字段大多数值是空&＃xff08;null&＃xff09;&＃xff0c;那么不建议对该字段建索引。

　　请大家记住&＃xff0c;一定是有业务需求了才会建索引。比如在一个商品表里&＃xff0c;我们经常要根据name做查询&＃xff0c;如果没有索引&＃xff0c;查询速度会很慢&＃xff0c;这时就需要建索引。但在项目开发中&＃xff0c;如果不经常根据商品编号查询&＃xff0c;那么就没必要对编号建索引。

　　最后再强调一次&＃xff0c;建索引是要付出代价的&＃xff0c;没事别乱建着玩&＃xff0c;同时在一个表上也不能建太多的索引。

　　下面说下索引建好了该怎么用&＃xff1f;毕竟大家花了不少时间和空间代价建了索引&＃xff0c;至少得回本吧&＃xff1f;

　　如果出现一些不好的SQL语句&＃xff0c;那么索引就白建了。下面通过一些具体的例子来看索引的正确用法。

　　①语句一&＃xff1a;selectnamefrom商品表。不会用到索引&＃xff0c;因为没有where语句。

　　②语句二&＃xff1a;select*from商品表wherename&＃61;‘Java书’&＃xff0c;会用到索引&＃xff0c;如果项目里经常用到name来查询&＃xff0c;且商品表的数据量很大&＃xff0c;而name值的重复率又不高&＃xff0c;那么建议建索引。

　　③语句三&＃xff1a;select*from商品表wherenamelike‘Java%’这是个模糊查询&＃xff0c;会用到索引&＃xff0c;请大家记住&＃xff0c;用like进行模糊查询时&＃xff0c;如果第一个就是模糊的匹配符&＃xff0c;比如wherenamelike‘%java’&＃xff0c;那么在查询时不会走索引。在其他情况下&＃xff0c;不论用了多少个%&＃xff0c;也不论%的位置&＃xff0c;只要不出现在第一个位置&＃xff0c;那么都能用到索引。

　　学生成绩表里有两个字段&＃xff1a;姓名和成绩。现在对成绩这个整数类型的字段建索引。

　　①第一种情况&＃xff0c;当数字型字段遇到非等值操作符时&＃xff0c;无法用到索引。比如&＃xff1a; 
selectnamefrom学生成绩表where成绩>95,一旦出现大于符号&＃xff0c;就不能用到索引&＃xff0c;为了用到索引&＃xff0c;我们应该改一下SQL语句里的where从句&＃xff1a;where成绩in(96,97,98,99,100)

　　②第二种情况&＃xff0c;如果对索引字段进行了某种左值操作&＃xff0c;那么无法用到索引。

　　能用到索引的写法&＃xff1a;selectnamefrom学生成绩表where成绩&＃61;60

　　不能用到索引的写法&＃xff1a;selectnamefrom学生成绩表where成绩&＃43;40&＃61;100

　　③第三种情况&＃xff0c;如果对索引字段进行了函数操作&＃xff0c;那么无法用到索引。

　　比如SQL语句&＃xff1a;select*from商品表wheresubstr(name)&＃61;‘J’&＃xff0c;我们希望查询商品名首字母是J的记录&＃xff0c;可一旦针对name使用函数&＃xff0c;即使name字段上有索引&＃xff0c;也无法用到。

　　关于索引&＃xff0c;当然还有位图索引和复合索引等&＃xff0c;如果大家要应聘更高级的岗位&＃xff08;比如有5年经验了&＃xff09;&＃xff0c;那么就不能止步于此了&＃xff0c;但根据我的面试经验&＃xff0c;上述关于索引的说辞对工作经验3年以下的候选人是有帮助的。

　　其实我知道&＃xff0c;不少程序员平时用过索引&＃xff0c;但不知道怎么说&＃xff0c;这很吃亏。对于高级程序员而言&＃xff0c;如果你这都说不好&＃xff0c;那么你的能力比初级的要高多少&＃xff1f;对于初级程序员而言&＃xff0c;如果你掌握了&＃xff0c;而且能在面试中很好地说&＃xff0c;那么你和同等能力的人相比&＃xff0c;就很占优势。