索引的设计

如何为表设计索引?一般设计索引时会遵循以下几个原则

针对于SQL中的where、order by、group by的字段设计索引

根据最左侧列匹配原则,索引的使用应该跟索引字段的顺序一致,从左开始匹配不可以跳过左边的字段直接使用右边的字段来做查询、分组、排序条件,因为这样不会使用到索引

针对于字段基数较大的字段设计索引

字段基数:某一个字段不同值的数量,比如说某个字段只有0和1两种值,那么它的字段基数就是2,对于这种字段做索引,其实也是不能提高效率的,因为无法对其进行高效的二分查找

尽量针对类型较小的字段来设计索引

类型小,如tinyint,意味着字段所占用的磁盘空间就小,那么它的查询效率就高,如果必须要对类型较大的字段,如varchar(255)这种字段来设计索引,那么可以使用前缀索引,就是只将该字段的前n个字符作为索引(使用形式:key(name(20), course, score)),但是这样会导致该索引字段只能被where语句所使用,而不能被order by,group by使用

不要对索引列嵌套函数

这样会导致无法使用索引

索引不要过多

在增删改数据的时候会同时对索引树进行操作,可能会进行页分裂,会不停的更新索引树,而索引树是存在放索引页中,索引页跟数据页一样也是存放在磁盘中的数据,这样一旦增删改的操作多了,就会导致性能下降

不要使用UUID作为主键,最好让主键是自增的

主键自增能保证插入的数据基本都是有序的,不会导致聚簇索引频繁的进行页分裂,但如果是UUID则不能保证

将经常需要范围查询的字段放在索引的最后一位

因为一旦一个字段用作范围查询用到了索引,那么这个字段接下来的查询条件都无法用到索引了,只有将这个字段放在最后,才能够保证查询条件中的每个字段都能用到索引