最正规的赌场网站还有这些MySQL高性能索引优化策

  关于MySQL的优化,相信很多人都听过这一条:避免使用select*来查找字段,而是要在select后面写上具体的字段。

  但我要讲的是另外一个原因:使用select*,就基本不可能再使用到覆盖索引(什么是覆盖索引,后面会讲),反而是将一个本该可以用覆盖索引的查询变成了不能使用覆盖索引的查询,就会导致随机I/O或回表查询(回表查询在下文介绍聚簇索引的时候会讲)。

  大部分的MySQL引擎都支持这种索引,它是使用B-TREE的数据结构来存储数据(InnoDB使用的B+TREE)。B+TREE是B-TREE的一个变种,区别是B+TREE为所有叶子结点增加了一个存储指向下个叶子结点的链指针,并且所有关键字都在叶子结点中出现。

  B-TREE通常就意味着里面存储的所有值都是有序的,并且查询的时候,不用全表扫描,而是按照索引结构查找,所以会更快。

  就是多列索引的最左前缀原则。例如一个多列索引为(A,B,C),当你的查询中包括A或A,B或A,B,C都可以用到索引,如果只有B,则无法用到该索引。

  举个例子就是,像like abc%可以用到索引,而like %abc%就无法用到该类索引。

  其实就是范围查询。但注意,当多列索引中有一列用到范围查询时,那么该列后面的索引都没法被用到。例如还是又一个索引为(A,B,C),又一个查询为where A=1,B1,C=1,那么这个查询只会用到(A,B,C)中的A,B列,C是不会被用到。

  哈希索引是基于哈希表实现的,只支持精确索引查询。在MySQL中,目前只有Memory引擎支持哈希索引,但我们可以自定义哈希索引。具体思路是这样的:

  例如一个表中需要存储大量的URL,如果正常使用B-TREE来存储URL,存储的内容就会很大,也导致索引很大。如果我们增加一列(url_hash)存储URL的哈希值,然后在这列上建立B-TREE索引,这样做的性能会高很多。因为当数据量非常大的时候,哈希会存在哈希冲突,所以在查询的时候要用到url和url_hash两列进行筛选。如:

  至于哈希算法,可以考虑自己实现一个简单的64位哈希函数。注意这个自定义函数一定要返回整数,而不是字符串。

  那么索引就一定是最好的解决方案吗?我们都知道维护索引需要做一些额外的工作,所以简单来说,当使用索引利大于弊时,索引就是有效的。

  所以我们应该养成简化WHERE条件的习惯,始终将索引列单独放在比较符号的一侧。

  有时候我们需要索引很长的字符串列,这时候我们就需要使用前缀索引,在MySQL中,对于TEXT、BLOB和很长的字符列,必须使用前缀索引,因为MySQL不允许索引这些列的所有长度。那么相应地,前缀索引必然会降低索引的选择性。索引的选择性是指,不重复的索引列与数据表的总记录数的比值。

  那么怎么才能找到前缀索引和索引选择性间的一个平衡呢?套用《高性能MySQL》中的一个例子:

  一张表中的一个字段存储的各个城市的名字。首先,我们找到最常见的城市列表:

  可以看出这个与原来的差距还是挺大的。经过尝试后,我们发现,当前缀索引长度为7时,比较合适:

  这是完整列的选择性。然后我们看下当前缀索引分别为3,4,5,6,7时的选择性为多少:

  这里可能有一个误区,会让我们感觉在索引前缀长度为4或5的时候,就已经足够了。那么我们再用之前的方法验证一下:

  可以看到最常出现的前缀次数要比最常出现的城市次数大很多。即使它们的选择性比较低。找到前缀索引长度后,我们就可以创建前缀索引了:

  前缀索引是一种能使索引更小、更快的有效办法。它的缺点是:MySQL无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。

  看到这里,你可以打开自己的数据库表,看看结构,是不是为每个列建立的单独的索引,我们公司现在就是这么做的。

  这是一个常见的错误。在MySQL5.0之前,下面的查询将无法用到索引,需要全表扫描:

  在MySQL5.0之后,引入了“索引合并”的概念。这种算法包括:OR条件的联合(union),AND条件的相交(intersect),组合前两种情况的联合和相交。

  这种索引合并策略是一种优化结果,但也间接说明了你的表上的索引建的很糟糕:

  当服务器对多个索引做相交操作时(通常是多个AND条件),通常意味着需要一个包含相关列的多列索引,而不是多个独立的单独索引。

  当服务器对多个索引做联合操作时(通常是多个OR条件),通常需要消耗大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是其中有些索引选择性不高,需要合并扫描返回的大量数据。

  更重要的是,这种索引合并策略不会被优化器计算到“查询成本”(cost)中去,优化器只关心随机页面的读取。

  既然要建立多列索引,那么选择合适的顺序就相当重要了。选择合适的索引顺序有一个经验法则:将选择性最高的列放到索引最前列。

  最开始,你可以按照这个法则建立多列索引,因为这可以在使用第一个索引列的时候就筛选出最少的数据量。随着经验的积累,你会有自己的索引列排序的经验。

  聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇索引总是把数据行存储在叶子页中,因此一个表中只能有一个聚簇索引。

  并不是所有的存储引擎都支持聚簇索引,这里我们主要讨论InnoDB。在InnoDB中,聚簇索引其实就是主键索引。如果表中没有定义主键,InnoDB会选择一个唯一的非空索引作为主键;如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚簇索引。聚簇索引的优点如下:

  如果表在设计和查询的时候能充分利用以上特点,将会极大提高性能。当然,聚簇索引也有它的缺点:

  聚簇索引最大限度提高了I/O密集型应用的性能,但如果所有的数据都存放在内存中,聚簇索引就没有优势了。

  插入速度严重依赖插入顺序。这也是为什么InnoDB一般都会设置一个自增的int列作为主键。

  更新聚簇索引的代价很高,因为会强制InnoDB将每个被更新的行移到新的位置。

  二级索引可能会比想象的更大。因为在二级索引的叶子节点中包含了引用行的主键列。

  答案在二级索引中保存的“行指针”的实质。因为二级索引在叶子节点中保存的并不是指向行的物理位置的指针,而是行的主键值。

  那么,如果此次查询不是覆盖查询,就会利用二级索引叶子节点中保存的行主键值再去表里进行二次查询。这时才会得到我们真正想要的数据,这样就是导致使用两次B-TREE查询,而不是一次。这也是文章开头所提到的,避免使用select*的另一个原因。

  如果一个索引包含或覆盖所有需要查询的字段值,我们就称之为“覆盖索引”。所以可能一个索引对于某些查询是覆盖索引,而对于其他的查询则不是。其实就是一个二级索引,只不过满足了一个特定条件。

  覆盖索引是一个非常有用的工具,可以极大提升性能。试想一下,如果一个查询只需要扫描索引而无需二次回表查询,会带来什么好处:

  当发起一个覆盖查询的时候,在Explain中的Extra列中可以看到“Using index”的信息。让我们回到select*这个问题上:没有任何一个索引能够覆盖所有列,所以select*可能会导致原本可以用到覆盖索引的查询而无法使用覆盖索引。

  ORDER BY和查找型查询的限制是一样的:需要满足索引的最左前缀原则,否则,MySQL无法使用索引排序。但有一个特殊情况,最正规的赌场网站就是前导列为常量。

  例如,有一个索引为(A,B,C),那么这样的SQL语句也会用索引排序,如下:

  理解索引最好的办法就是结合实例,那么这里我就结合我们公司的一个订单表来讲一下。现在的索引就是在每个需要的列上建立单独的索引。

  首先company_id(企业ID)列的选择性肯定很低,但基本上每个查询都会用到。然后status(订单状态)和type(订单类型)列的选择性通常也都比较低,但也会在很多查询中用到。所以这时候我们可以考虑在创建不同组合的索引时以(company_id,status,type)作为前缀。

  这时候肯定很多人会有疑问:我们之前不是提到过一个经验法则,尽可能将选择性高的列放在多列索引的前面,那么这里为什么在选择性很低的列上创建索引,而且还把它作为索引前缀列?

  这个“诀窍”就是,假如我们有一个查询需要查找所有状态的订单,那么我们可以在WHERE条件中添加AND status IN(1,2,3,4)来让MySQL选择该索引。这么用的话,需要一点,在IN()条件中,优化器需要做的组合是以指数增加的。

  比如我们之前提到的三个列,假设company_id有5个值,status有4个值,type有3个值,那么优化器就会转化成5x4x3=60种组合。60种组合对于MySQL来说,并不是很夸张,但当这个组合数达到上千个的时候就需要特别小心了。

  我们这边最常用的是按照店铺、客户手机号、客户姓名、时间段来查询订单。这里面数值最少的店铺,也有大几十个。所以显然不适合上面所提到的那个“诀窍”。

  在创建多列索引的时候,一定要避免多个范围条件,比如上面3个索引,如果你在click_at后面在加上任何列,是不会被索引命中的。除非有特殊的原因,比如你需要添加一个字段来让某个查询变成覆盖查询,否则最好不要在一个范围列后面在加上其他列,只会浪费空间。

  这个是题外线版本中引入了这个优化——Index Condition Pushdown。MySQL官网手册是这样描述的:

  在下粗略的翻译了下,意思是:ICP的目的是通过减少完整记录读取的数量来减少IO操作。对于InnoDB的聚簇索引,完整记录已经被读取到InnoDB缓冲里,在这种情况下使用ICP不能减少IO。

  在没有ICP的时候,WHERE条件中没有被索引用到的列的过滤是在MySQL服务层中;而有了ICP之后,这种过滤就直接在储存引擎层中完成了,而且是在二级索引回表查询前就完成了过滤,这就避免了大量的数据传输,从而降低了IO。返回搜狐,查看更多

分享到
扫描二维码分享到微信
确 认

Copyright © 2002-2018 最正规的赌场网站 版权所有 备案号:苏ICP备16035770号