mysql索引优化

mysql索引优化补充

  

 

1.单列索引还是多列索引？

  由于mysql只能用到你所有建立的索引中的唯一一个最优索引，所以如果建立多个单列索引，其实效率并不会高（虽然mysql5.x以上的版本，会在查询时，做索引合并的优化，但仍不建议这么做）

 

2.索引可以优化查询，那么索引真的越多越好吗？

  由于mysql的索引是表的数据的一部分，就像你手中的汉语词典一样，目录越庞大，可读性越差（查询性能越差）。而且，在insert，update，delete时，都要额外维护索引的成本，建立过多的索引可能意味着更差的写性能。

 

3.查询条件的顺序对索引真的有影响吗？

  查询条件的顺序，并不会影响索引的使用，索引的使用只跟索引建立的顺序有关（多列索引的顺序）

例1：

首先建立一个3列的索引

alter table test

add key idx_col_1_col_2_col_3(col_1,col_2,col_3);

查询条件是这样的

where col_2='2' and col_3='3' and  col_1='1'

可以用到索引吗？

可以

例2：

首先建立一个3列的索引

alter table test

add key idx_col_1_col_2_col_3(col_1,col_2,col_3);

查询条件是这样的

where col_2='2' and col_3='3'

可以用到索引吗？

不可以，因为where条件缺少了索引的最左前缀col_1，即使有col_2和col_3，都不会走索引

例3：

首先建立一个3列的索引

alter table test

add key idx_col_1_col_2_col_3(col_1,col_2,col_3);

查询条件是这样的

where col_1='1'

可以用到索引吗？

可以，因为where条件包含了索引的最左前缀col_1

 

 

4.什么样的列适合建立索引？

  按道理说，选择性强的适合建立索引（说了跟没说有什么区别T_T），那么什么样的列选择性强呢？

• ID类型的列，这样的列关联方便，重复几率小，多为数字类型，mysql查询成本更低（因为排序比较简单，你大学课本学过的好多没有用过的算法他都能用，什么二分法，快排，只要是有序的，查询时间复杂度就大大降低，这也就是为什么主键都强烈建议你用数字类型）
• 需要做排序优化的列，有了索引，排序那是相当轻松的（索引存放时，本身是有序的，所以你想排序，那太容易了）
• 可以通过 between ... and ... 连接的，这样的列，可以走索引的range类型的匹配
• 可以通过 in(...)的列，这样的列，mysql内部会优化成等值连接，也可以走索引
• 要做模糊查询的列要注意了，因为like的通配符只能出现在最右边(如：col like 'tom%')的索引才能使用，如果你的查询没法保证这一点，其实加了也没用。。。
• 字段内容不要太长，索引只支持256个字符以内的类型（超过255的需自己截断）
• 像性别这样的列不适合做索引

 

 

5.好的索引可以让你的程序跑起来更优雅更流畅，那么怎么设计索引呢？

  这点我也一直在学习，实践和探索。虽然还没有炉火纯青，但是还是给大家一些我的经验之谈，希望可以减少大家的学习成本（各种google，各种百度来的资料，很多都未经证实，或者说的含糊不清，对学习造成很多困扰，我也差点吐血。。。）

• 找出你所有的查询，然后把他们的where条件都列出来
• 尽可能找出where条件中公共的部分，然后看这些字段是否适合建索引
• 将适合建索引的列按照辨识度从高到低排列
• 需要自己权衡，怎么样用最少的索引覆盖最多的查询，实在覆盖不到的，但是又调用特别频繁的查询，需要单独给他们加索引

 

6.如何优化查询？

• 尽可能的让查询走索引，查询条件尽量避免出现 or 
• 范围查询， col like '%xxx%'模糊查询，会导致范围|模糊查询 之后的条件索引失效
• 子查询使用关联查询替代
• 关联查询，用小表驱动大表
• 学会使用limit，limit会让你的查询提前返回需要的行，这点在排序的时候尤其有用（避免全部排序，只排序需要的列）
• 在咱们多租户的系统里，千万别漏掉tenant_id这个辨识度最高的列(tenant_id建议放在最左边)

 

7.有的查询比较复杂，想要建索引，又拿不准？

  可以找我帮忙一起分析，因为索引的建立，需要权衡很多内容，比如数据量，mysql的统计分析等因素。