加入收藏 | 设为首页 | 会员中心 | 我要投稿 云计算网_泰州站长网 (http://www.0523zz.com/)- 视觉智能、AI应用、CDN、行业物联网、智能数字人!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

Schema的优化和索引 - 学习一个索引示例

发布时间:2016-01-18 18:48:53 所属栏目:MySql教程 来源:网络整理
导读:用例子来理解索引的概念是再简单不过的方法了。因此我们研究一个索引示例。 假使我们要做一个在线约会的网站。用户的资料就会有很多列,比如country, state/reg
用例子来理解索引的概念是再简单不过的方法了。因此我们研究一个索引示例。

假使我们要做一个在线约会的网站。用户的资料就会有很多列,比如country, state/region, city, sex, age, eye color等等。这个站点必须支持这些属性的组合来查询用户资料。也必须支持让用户通过用户最近的在线时间,其他会员的打分等等来排序和限制结果。对于这么个复杂的需求我们怎样来设计索引?

奇怪的是,第一个要决定的就是我们是否必须使用索引排序或者文件排序是否是可接受的。基于索引的排序限定了索引和语句创建的方式。比如,我们不能使用索引的情况是WHERE条件的age在18-25之间,不管语句是否使用了一个索引去排序。

如果MySQL使用了索引的范围查询,它也就不能使用另一个索引来排序了。假如这是个比较常见的查询,我们就得知有很多查询都要用到文件排序。

支持多种类的过滤

现在我们需要知道列具有唯一值的数量以及那些经常出现在WHERE条件后的列。在具有许多唯一值的列创建索引,这样做选择性很高。因为MySQL会高效的过滤掉不需要的值。

country不确定是否具有选择性,但是它可能会在大部分查询语句中出现。SEX列不具有选择性,但是它基本会在每个查询语句中出现。根据以上的想法,我们可以使用前缀为(sex,country)来创建一系列不同组合的索引。

按照传统的思路来说,在选择性低的字段加索引没有什么效果。但是我们为什么把一个选择性低的列放在每个索引的开始部分呢?思路错误了?

我们有两个理由要这么使用。第一个理由就是每个查询都会用到sex。我们甚至可以设计让用户仅能通过一个sex来进行搜索。重要的是,添加这一列也不会有什么弊端,因为我们还有一些技巧在里面。

技巧就是:即使一个查询没有对sex进行一些约束,我们也能通过AND sex IN('m', 'f')保证索引的使用。这么做不会过滤任意的行。因此功能上来说和不包含sex查询的语句一样。然而,我们需要包含这一列,因为这样做会使MySQL使用一个更大前缀的索引。这个技巧比较适用于这个需求,但是如果这个值有太多的不同,这样做就不会工作的很好,因为IN()列表太大了些。

这个例子揭示出一个一般准则:全面的考虑整张表。当你设计索引,不要仅仅思考你现有语句所需的索引类型,还要考虑优化语句。如果你需要一个索引,但是你想到一些语句可能会受到不好的影响,问问自己是否能改变这些语句。你应该把语句和索引一起优化来找到折中的一个方案;你没必要设计一个完美的索引schema.

接下来我们要考虑的是其他WHERE条件的组合并且考虑没有索引的情况下哪些组合会变慢。(sex,country,age)上索引是个显而易见的选择,并且我们也需要在(sex,country,region,age)和(sex,country,region,city,age)上创建索引。

这样会有很多索引的。如果我们想重用索引并且不生成过多的索引组合。我们可以使用IN()的技巧并且废弃(sex,country,age)和(sex,country,region,age)索引。如果它们并没有在搜索表单中,可以指定所有国家的列表或者国家的所有区域来确保符合和索引前缀相等的约束。

这些索引可以满足大部分常用查询的需要,但是我们怎样设计其他一些不常用的搜索条件呢。比如has_pictures, eye_color, hair_color和education?如果这些列选择性低并且不经常使用,我们可以不给它们创建索引并且让MySQL扫描一些额外的行。我们也可以这样做,我们可以把它们添加到age之前并且用到我们之前用到的IN()的方法。

现在你可能会发现,我们创建索引的时候总是把age列放到最后。这个列有啥不同么,为什么要放到最后?我们要尽可能使MySQL是用索引上所有的列,因为只能使用最左端的前缀,直到发现第一个条件为范围查询为止。我们已经提到的其他列在WHERE中都可以用相等的条件,但是age的条件是个范围(比如18-25之间)。

我们也可以转为IN()列表。如age IN(18, 19, 20, 21, 22, 23, 24, 25),但是这种方法并不适合这个类型的查询。我们举例要说明的一般准则就是把范围条件的列放到索引的最后,因此优化器就会尽可能使用索引。

我们已经说过了你可以添加更多的列到索引中并且使用IN()列表覆盖那些并不是WHERE条件一部分的那些列,但是你可能做的过了头就会导致问题的发生。在IN中使用了过多的值,是这些list的组合变得巨大,优化器就要评估这种情况了,并且,这样也会降低查询的速度。考虑下如下的语句:

WHERE eye_color IN('brown','blue','hazel')

AND hair_color IN('black','red','blonde','brown')

AND sex IN('M','F')

优化器会把这些转换为3*4*2组合,并且WHERE条件必须要对每一个进行校验。24个组合并不是很极端的数字。但是如果这个数字变为上千的话就要小心了。旧的MySQL版本在处理大量IN组合的时候会出现很多问题:查询优化器会用很多事前和消耗很多内存。如果组合变得过大,新的MySQL版本会停止评估这些组合 ,但是仍会限制MySQL使用索引。

(编辑:云计算网_泰州站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读