MySQL索引设计原则

MySQL索引的设计原则：

索引设计原则一：

针对sql语句中的where,order by,group by条件设计索引。并且注意where,order by,group by后面跟的字段的顺序，是不是某个联合索引的最左侧字段开始的部分字段

索引设计原则二：

需要考虑字段基数的问题,一般建立索引尽量使用那些基数较大的字段，就是值比较多的字段,那么才能发挥出B 树快速二分查找的优势

PS：

• 什么时字段基数？

举个例子，有一个字段它一共在10万行数据里有10万个值对吧？结果呢？这个10万值，要不然就是0，要不然就是1，那么他的基数就是2，为什么？因为这个字段的值就俩选择，0和1。假设你要是针对上面说的这种字段建立索引的话，那就还不如全表扫描了，因为你的索引树里就仅仅包含0和1两种值，根本没法进行快速的二分查找，也根本就没有太大的意义了，所以这种时候，选用这种基数很低的字段放索引里意义就不大了。

索引设计原则三

尽量对那些字段类型较小的字段来设计索引。比如tinyint,因为它的字段类型较小,说明这个字段的值占用磁盘空间小,这样在搜索的时候性能较好一点

PS：

万一要是真的有varchar(255)的字段，觉得放在索引树中占用磁盘空间过大了；此时可以考虑一下换一种策略，也就是对该字段的前20个字符建立索引，通俗来讲就是将该字段的前20个字符值放在索引树里。也就是前缀索引

建立方式：KEY my_index(name(20),age,course)

这时通过该策略建立的索引，当你在where条件搜索时，如果是根据name来搜索，此时就会先到索引树中name字段的前20个字符去进行搜索，定位到数据后再到聚簇索引提取出完整的name值

注意注意注意！！！！！

• 对于前缀索引，仅仅包含部分字符到索引树中，where查询是可以使用的，但是order by和group by就用不上了

索引设计原则四

设计索引需要考虑到数据插入更新时索引树也会进而更新，以及主键一定要是自增的。避免数据稀疏造成频繁的页分裂，其次就是索引不要设计的太多，毕竟维护成本较高，也占用磁盘，还有就是插入的数据不是按照顺序来的，从而会导致某个索引树里的索引页自动分裂这就会很浪费时间

总结：

简单来说建立索引需要考虑的点主要一下几个方面：

• 需要针对where group by order by创建索引或联合索引。
• 索引字段的基数
• 索引字段值尽量不要过于庞大，可以考虑前缀索引
• 索引数量不要过多