文字：1545 阅读：5分钟
很多中小型公司并没有专门DBA岗位，表和索引都是由后端开发创建，由于后端开发水平不一，所以也经常会因为索引问题引发一些线上BUG。
今天我们就说说索引优化，看你有没有踩坑。
InnoDB存储引擎会自动为主键创建主键索引，也叫聚簇索引，在聚簇索引的叶子节点上包含了完整的记录， 而我们自己建立的索引都叫二级索引，二级索引的叶子节点包含索引列和主键。如果为两列创建的索引叫联合索引，记录先按照前面的列排序，如果该列相同，再按后面的列排序。
回表
当查询 ”宝子“的欠款记录时，先走二级索引，然后在叶子节点上找到对应的主键值，再回到主键索引中去找到对应的值，这个过程叫回表，回表的成本很高，因为在二级索引上有三次I/O,然后主键索引中又产生三次I/O.
由于大量的回表会导致查询性能降低，所以建议不要使用*查找数据。
select * from table
只查询所需的列，还可以减少带宽。
注意：当使用二级索引进行范围查找时，如果所查记录占了整个表80%（概指），那MySQL会认为还不如直接在主键索引中查询。这个就是优化器的工作。
还有一种情况是为区分度不高的字段创建了索引，比如性别”男“，”女“，区分度低势必就会产生大量的回表,MySQL的优化器大概率走全表扫描。我们可以使用distinct方法来判断。
select count(distinct col)/count(*) from tabel
这个比值越接近于1，索引的效果越好。
而直接通过二级索引返回数据的，我们称作覆盖索引。
像上图中，如果村长直接查找宝子的欠款金额，这两个值直接命中覆盖索引，效率最高。
最左前缀原则
如果一个表拥有了联合索引，任何一个索引的最左前缀都会被优化器用于查找列，比如，你创建了（a,b,c)的三列索引，那索引生效于(a),(a,b)以及(a,b,c)的查询条件。
这也是面试中常被问到的问题。
我们还要考虑到空间的占用，比如创建（a,b)索引，需要支持(a),(a,b),(b)查询 ，由于(b)查询不命中联合索引，我们自然的单独为b创建索引。如：(a,b)和（b)，但换个角度，我们也可以创建（b,a) 和（a）索引。
在假设字段a占用10个字节，b占用20个字节，那第一个方案共占用空间10+20+20 = 50，而后一个方案共占用20+10+10 = 40， 这个答案会让你的面试加分
其它优化技巧
多用LIMIT
在实际开发中应该多使用LIMIT,尤其是你已经知道只有一条记录的场景 ，因为索引并不知道记录的唯一性，索引当查找到第1条数据时，仍会继续向下查找 。
注：在删除或修改数据时，为了防止误操作，最好也加上LIMIT 1
为order by 和group by 创建索引
将order by 后面的字段创建索引，这样在查询的时候就不需要再去做一次排序，因为索引树中的记录本身是排序好的，如果order by 没有创建索引的列，MySQL会先进行排序，这样就会产生临时表。group by同理。
更高效的分页
select * from table order by id desc limit 10,20
这个SQL非常常见，多数在写的时候也不会去思考，其实索引会从ID=1开始查找，一直查到第30个，然后把前10条记录去掉再返回。
如果变成limit 10000,20，那就非常浪费资源了，可以记录上次分页的最大ID,然后在SQL上增加id > 10000.
select * from table where id > 10000 limit 20
控制索引的数量
索引不是越多越好，一是因为索引需要额外的存储空间，二是在维护表时，同时也需要维护索引记录，索引过多，就会消耗性能，建议单表的索引个数不要超过5个，对于频繁增删改的字段不要建立索引。
对于创建索引的字段建议如下：
选择合适的索引字段
能用数字类型，就不用字符串
尽量使用小的类型，如tinyint
长度固定的字符串
金额字段使用decimal 避免精度丢失问题。
除此之外，还有很多原因会导致索引失效， 面试官经常会问这个问题，而很多同学只能说说出一两种，并不全面，这种情况怎么办，我们明天说说。
PS:
公司越大，对于产品经理的考验越不光是产品能力，还包括沟通能力和变通处理问题的能力。多数会停留在非零即一的情况下，有时候曲线完成任务也是一种途径。
关注我，带你一起进化！
更多面试资料
