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

rfyiamcool 1 Comment

    近段时间发现很多周边开发人员都不懂索引…..   比如 单独索引和联合索引的本质区别， 联合索引要注意什么?   什么时候会命中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

    这个想看

    回复

发表评论

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