业务开发中涉及的mysql索引问题

    近段时间发现很多周边开发人员都不懂索引…..   比如 单独索引和联合索引的本质区别, 联合索引要注意什么?   什么时候会命中order by \ group by 索引,什么场景下不需要建立索引,一个sql语句会调用几个索引?  等等….   因为没有到一个量级,所以很多的效率问题不去考虑….


    该文章写的有些乱,欢迎来喷 ! 另外文章后续不断更新中,请到原文地址查看更新.   http://xiaorui.cc/?p=3763


首先问一个比较经典的问题,  现在还有不少的傻逼公司会问这个休智商的问题   innodb vs myisam 区别?

如果他真的问了这么土鳖的问题,你可以这么回答,在mysql5.5之前的时候, myisam 引擎是适合读多写少的场景,写少是因为他是表锁,力度粗…     mysql 5.6 5.7出来以后,他们单纯的读写性能差距已经相当的微小了, 在功能上来说 innodb有外键和事物,备份数据可以借助引擎的崩溃处理方式不锁表实施。  


换个思维,如果你单纯的读写,为毛用关系型数据库呀,那么多选择。。。     另外大多数公司的dba已经不推荐你去使用 myisam引擎了。   这时候你可以烦着喷问你的面试官,你现在还在用myisam么?    


话说我曾经跟阿里、搜狐的dba吃饭聊天问他们,现在还有什么场景会推荐他们用myisam, 他们的回答都是,没有选择,要么innodb、要么tokudb. 


切记不要直接 select count(*) from xxx;   一定要记得count(索引) ,  mysql5.7之后才做了这类查询的编译优化。


innodb和myisam在数据和索引之间的区别? 

innodb  数据和索引是在一个文件里面 , myisam的索引和数据文件是分离的…..  另外b + tree的 key是有序的.  有点像跳跃表的结构. 

   

  


为什么不在性别上加索引?    

因为不是男,就是女,这时候你扫全表跟走索引的效率差不多….  

order by 命中索引的场景?

如果索引是 idx_abc(a,b,c), 哪么where a=xxx order by b,c的时候,这个order by可以使用到b,c的索引 !

如果索引是 idx_bc(b,c),那么 order by b,c      是可以使用到b,c的索引 !

如果索引是 idx(a,b,c) ,那么下面三种情况都可以命中索引 !

where  a =3  oder by a
where  a =3  oder by b
where  a = 3 and b > 3 order by b   

group by的命中索引场景? 

如果是idx(a b c),那么where  a   group by  b   不可以 !!!

WHERE a = xxx  GROUP BY a, b; 是可以走索引….
如果你的查询语句是 , where  a = 123  group by  b    那么建立索引最好的方式是那种?  


大多数情况下 a, b 的联合索引来说就可以了, 因为一般a=123只有部分纪录,一般最多不超过几千条,所以对这些条分组效率也不差

你就记住一点,要想group by使用到索引,那么他们后面的字断中一定要出现索引的第一个字断 ! 

另外复合索引中,如果想使用后面字段的索引,那么查询中where一定要出现复合索引前面的字段,并且该字段还只能是等值运算,一定是等值。

咱们拿 idx(a,b) 为例!下面的例子都是可以命中的.

where a=xxx and b=xxx
where a=xxx and b>xxx
where a=xxx
where a>xxx

这两种也可以使用索引,但是只能使用a字段的索引,b字段的索引使用不上

where a>xxx and b=xxx

where a>xxx and b>xxx



这两种是不会使用索引的,因为where中没有出现a !!!

where b=xxx
where b>xxx


这四种都是只能使用a字段的索引 !   后两种a字段走了区间,而不是相等的操作符。

where a=xxx
where a>xxx

where a>xxx and b=xxx

where a>xxx and b>xxx



这两种能使用这个复合索引 !
where a=xxx and b=xxx
where a=xxx and b>xxx

注意where中的字段没有顺序区分 !

where a>xxx and b>xxx与where b>xxx and a>xxx效果是一样的


一般两种情况下不建议加索引:

表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了;
至于多少条记录才算多,我个人的经验是以3000作为分界线,记录数不超过 3000可以考虑不建索引.  因为知道查询索引也是需要时间消耗的.

索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:

#xiaorui.cc
索引选择性比率 = 不重复的值 / 总数
Index Selectivity = distinct( field ) / count(1)


选择性取值范围是 ( 0, 1 ),选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的选择性不足0.0001,所以实在没有什么必要为其单独建索引。
MySQL只对一下操作符使用索引:<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。
其实没有严格指标说 ” 多少百分比没必要建立索引 “, 但小于0.1的肯定是不合理的.   
oracle 一般小于0.1就不走索引了, mysql还是会走索引,只是无意义罢了.     虽然选择性过低的字段不适合单独建立索引,联合索引还是可以的.   

我还没有讲完 !!!


大家觉得文章对你有些作用! 如果想赏钱,可以用微信扫描下面的二维码,感谢!
另外再次标注博客原地址  xiaorui.cc

1 Response

  1. oshadow 2016年9月14日 / 上午10:53

    这个想看

发表评论

邮箱地址不会被公开。 必填项已用*标注