技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> MySQL --> InnODB和MyISAM索引统计集合

InnODB和MyISAM索引统计集合

浏览:5296次  出处信息

   最近做一个mysql专题学习。在了解到mysql变量时myisam_stats_method引导出MyISAM索引统计集合。然后了解InnODB和MyISAM索引统计集合,以下是对官网的翻译以及自己附加些少理解。

   官网地址:http://dev.mysql.com/doc/refman/5.6/en/myisam-index-statistics.html

   储存引擎收集优化器使用的统计信息,表统计信息来自数值组,数值组是指具有相同关键字前缀的行记录。为了优化,一个重要的统计就是平均数值组。ps:下面会提到平均数值组的来源根据

   MYSQL用在以下两种情况用到平均数值组:

   1、估计每个索引值访问需要读多少行数据

   2、估计一些联接产生多少行,如下面的联结:

   JOIN tbl_name ON tbl_name.key = expr

   随着平均数值组的增加,则每个查找平均行数也增加,那索引就更加没用。所以达到优化的目的,最好的是每个索引值(ref)指向少数行。当指向行数越多,读的次数也多,mysql更不会使用索引。

    ps 上面说的索引值指向函数,根据不同引擎存数的数据库而不同,这里最要讲的是MyISAM的,MyISAM的索引是保存每个索引值以及行数据的地址,而InnODB则是索引值和索引列数据包含在一起。

   平均数值组和什么有关系?

   平均数值组大小与表的势值(cardinality),即数值组的个数。用show index显示势值(N/S),N是表内记录数,S是平均数值组的大小。

   -----------------------------------------------------

   为了便于理解,以下是笔者的测试:

   在表中:

  • mysql> describe t_limit;  

  • +---------+-----------+------+-----+---------+----------------+  

  • | Field   | Type      | Null | Key | Default | Extra       |  

  • +---------+-----------+------+-----+---------+----------------+  

  • | id      | int(6)    | NO   | PRI | NULL    | auto_increment |  

  • | content | char(125) | NO   |     | NULL    |                |  

  • +---------+-----------+------+-----+---------+----------------+  

   表t_limit中id是主键

  • show index from t_limit;  

  • +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  

  • | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  

  • +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  

  • | t_limit |          0  | PRIMARY|    1 | id        | A         | 966 |     NULL | NULL   |      | BTREE      |         |  

  • +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  

   可以看到势值是966,表数据有1008  按照公式C=N/S =>966=1008/S=>S=1.043 也就是说MYSQL估计在t_limit中每个索引值需要读1.04行表数据,这个值越小索引被mysql利用的几率就越大,也就是说Cardinality 的值越大越好(这个也是网上流传很多的说法,但很少有说缘由)。还有的是势值是个估算值,每次用Analyze后值会变动。

   --------------------------------------------------------------

对于基于<=>(<=、=>)比较操作符的联接,NULL被mysql视为和其他值相同:NULL <=> NULL,正如对于其它N,N <=> N。

但是对于基于=操作符的联接,NULL与非NULL值不同:当expr1或expr2(或两者)为NULL时,expr1 = expr2不为真。这样影响比较形式tbl_name.key = expr的ref访问:如果expr当前的值为null,MySQL不会访问表,因为比较不能为真。

对于=比较,表内有多少NULL值并不重要。为了优化目的,相关值为非NULL数值组的平均大小。然而,MySQL目前不允许搜集或使用该平均大小

ps这是mysql针对所有存储引擎对where join 中有NULL表达式的处理方式,只要心中有这个概念,先不要纠结原因,下面有方法修改 myisam_stats_method和  InnoDB_stats_method 系统变量让mysql怎么对待null值

对于myisam和innodb表,可以通过设置myisam_stats_method和  InnoDB_stats_method 系统变量告诉mysql怎么收集统计信息,也就是如何去处理NUll值,Mysql现在有三个值:nulls_equal、nulls_unequal、nulls_ignored

1、nulls_equal,则对于数据库里面所有Null都是相等的(mysql看待null是一样的)

当数据表中null值远远大于非null值,那么平均数值组增大,这样使索引对于优化器来说比它实际为查找非NULL值的联接更加没有用。结果是,nulls_equal方法会使优化器进行索引值(ref)访问时本应使用索引而没有使用。

PS:对待null相等,这势值很小,C=N/S 则平均数组值S则很大,前面说了平均数组值越大,则用的索引的机会就越少。

2、nulls_unequal,null被认为是不一样(尽管我们眼中是一样),每一个null形成单独的数组值。

如果null数量很多,则该方法向下倾斜平均数数值组大小(就是偏小),这是我们想要的。

如果非null数量很多,null小,统计大小为1的每个组的NULL值会使优化器过高估计查找非NULL值的联接的索引值,按照笔者的理解就是这平均数数值组偏大,即时用其他方法(如遍历表)更好,mysql还是会用索引。但是这不是我们想要的。

所以用nulls_unequal是适合用null很多的情况下。PS:这段不完全按照官网英文翻译,加上笔者的些理解。

3、nulls_ignored,则null被忽略。

PS:myisam_stats_method默认是nulls_unequal

如果你要使用许多使用“<=>”操作符号而不是=的联接,在比较过程中null值并不特殊,一个null等于另一个null。在这种情况下,nulls_equal是合适的统计方法。但是若是=号的话则用nulls_unequal

myisam_stats_method系统变量有全局和会话值。设置全局值会影响MyISAM 为所有MyISAM表的统计的搜集。设置会话值只影响当前客户连接的统计的搜集。这说明你可以强制用给定的方法重新生成表的统计的搜集,而不需要因为设置myisam_stats_method的会话值而影响其它客户。

重新生成表的统计信息:

1、设置myisam_stats_method,然后执行CHECK TABLE语句

2、执行myisamchk --stats_method=method_name --analyze

3、更改表,使其统计信息不为最新,然后设置myisam_stats_method并执行ANALYZE TABLE语句

官网地址:http://dev.mysql.com/doc/refman/5.6/en/myisam-index-statistics.html

建议继续学习:

  1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读:15124)
  2. 浅谈MySQL索引背后的数据结构及算法    (阅读:9961)
  3. 由浅入深理解索引的实现(2)    (阅读:6457)
  4. HBase二级索引与Join    (阅读:5846)
  5. 如何建立合适的索引?    (阅读:5457)
  6. Innodb 表和索引结构    (阅读:4861)
  7. mysql查询中利用索引的机制    (阅读:4818)
  8. MySQL索引背后的数据结构及算法原理    (阅读:4485)
  9. 多维度分类排行榜应用:用位图索引    (阅读:4029)
  10. mysql索引浅析    (阅读:4137)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1