浅谈如何设计索引

前言

我们在设计索引时，通常应该考虑哪些因素，给哪些字段建立索引，如何建立索引，建立好索引后应该如何使用才是最合适的，下面篇幅会简单介绍下我的经验（文字比较多哈，但也会穿插一些栗子哦）

如何设计索引

在开发角度来说，接到一个需求，针对业务需求建立好一张表的结构之后，就知道这个表有哪些字段，每个字段是什么类型的，会包含哪些数据。我们要考虑第一点，就是未来我们对表进行查询的时候，大概会如何来进行查询？

提问：此时我们很难预测到未来会怎么查询，那我怎么可能知道呢？

完全没有问题，先别记着设计索引，咱们可以进入系统开发的环节，也就是说根据需求文档逐步逐步的把你的Java业务代码给写好，当你系统差不多开发完毕了，功能都跑通了，此时你就可以来考虑如何建立索引了，因为你的系统里所有的SQL语句都已经写完了，你完全知道对每一张表会发起些什么样的查询语句。

设计原则

保证sql中尽量多的字段能用上索引

观察SQL语句里的where条件、order by条件以及group by条件。 where条件里要根据哪些字段来筛选数据？order by要根据哪些字段来排序？group by要根据哪些字段来分组聚合？

设计一个联合索引，每一个联合索引都尽量去包含上你的where、order by、group by里的字段是不是每个where、order by、group by后面跟的字段顺序，都是某个联合索引的最左侧字段开始的部分字段。

举个栗子：有联合索引（a,b,c）， 有sql select * from xxx where a=? and b=?，order by a,b，group by a 。此时where、order by、group by后续跟的字段都是联合索引的最左侧开始的部分字段，这就可以了，说明你的每个SQL语句都会用上你的索引了

尽量使用那些基数比较大的字段

还得考虑一个问题，字段基数问题，就是区分度。举个栗子，“性别”这字段，基数是2，因为只有男和女，如针对这字段建索引的话，还不如全表扫描，索引树里就仅仅包含男和女两种值，根本没法进行快速的二分查找，没有多大的意义。

所以，一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。其次的话，你尽量是对那些字段的类型比较小的列来设计索引，比如说什么tinyint之类的，因为他的字段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时你在搜索的时候性能也会比较好一点。

使用前缀索引

尽量是对那些字段的类型比较小的列来设计索引，比如说什么tinyint之类的，因为字段本身的值占用磁盘空间比较少，搜索的性能也会比较高。但！不是绝对，有时候你就是要针对varchar(255)这种字段建立索引，有没有什么解决方案呢？

有！对这个varchar(255)字段的前20个字符建立索引，就是说，对这个字段里前20个字符放在索引树里，类似联合索引(name(20),sex,age)

索引字段不使用函数

尽量不要让你的查询语句里的字段搞什么函数，或者是搞个计算，这样会导致索引失效。同时也不太建议使用mysql实现一些逻辑运算，毕竟db主要还是提供存储功能

主键使用自增的

自增主键，聚簇索引不会频繁分裂，主键值都是有序的，就会自然的新增一个页而已