Mysql 索引建立原则

﹏ヽ暗。殇╰゛Y 2023-09-25 00:03 261阅读 0赞

首先，在针对业务需求建立好一张表的结构之后，就知道这个表有哪些字段，每个字段是什么类型的，会包含哪些数据。

接着设计好表结构之后，接下来要做的，就是要设计表的[索引][Link 1]，这个设计索引的时候，要考虑第一点，就是未来对表进行查询的时候，大概会如何来进行查询？

其实很多时候很多人可能说，要让我刚设计完表结构就知道未来会怎么查询表，那我怎么可能知道呢，实在是想不出来！

好，那么没关系，此时完全可以在表结构设计完毕之后，先别急着设计索引，因为此时你根本不知道要怎么查询表。

接着就可以进入系统开发的环节，也就是说根据需求文档逐步逐步的把Java业务代码给写好，在写代码的过程中，现在一般都是用MyBatis作为数据持久层的框架的，肯定会写很多的MyBatis的DAO和Mapper以及SQL吧？

那么当系统差不多开发完毕了，功能都跑通了，此时就可以来考虑如何建立索引了，因为系统里所有的MyBatis的SQL语句都已经写完了，完全知道对每一张表会发起些什么样的查询语句，对吧？

那么这个时候，第一个索引设计原则就来了，针对SQL语句里的where条件、order by条件以及group by条件去设计索引。

也就是说，where条件里要根据哪些字段来筛选数据？order by要根据哪些字段来排序？group by要根据哪些字段来分组聚合？

此时就可以设计一个或者两三个联合索引，每一个联合索引都尽量去包含上where、order by、group by里的字段，接着就要仔细审查每个SQL语句，是不是每个where、order by、group by后面跟的字段顺序，都是某个联合索引的最左侧字段开始的部分字段？

比如有一个联合索引是INDEX(a,b,c)，此时一看发现有三个SQL，包含了where a=? and b=?，order by a,b，group by a这些部分，那么此时where、order by、group by后续跟的字段都是联合索引的最左侧开始的部分字段，这就可以了，说明每个SQL语句都会用上索引了。所以在设计索引的时候，首先第一条，就是要按照这个原则，去保证每个SQL语句的where、order by和group by都可以用上索引。

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

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

其次的话，尽量是对那些字段的类型比较小的列来设计索引，比如说什么tinyint之类的，因为它的字段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时在搜索的时候性能也会比较好一点。

不过当然了，这个所谓的字段类型小一点的列，也不是绝对的，很多时候就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间，那也得去设计这样的索引，比较关键的其实还是尽量别把基数太低的字段包含在索引里，因为意义不是太大。当然了，万一要是真的有那种varchar(255)的字段，可能里面的值太大了，觉得都放索引树里太占据磁盘空间了，此时仔细考虑了一下，发现完全可以换一种策略，也就是仅仅针对这个varchar(255)字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里而已。

此时建立出来的索引其实类似于KEY my\_index(name(20),age,course)，就这样的一个形式，假设name是varchar(255)类型的，但是在索引树里对name的值仅仅提取前20个字符而已。

此时在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对就可以了。

但是假如要是order by name，那么此时name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引了！group by也是同理的。所以这里要对前缀索引有一个了解。

设计好了一个索引，非常棒，接着在SQL里这么写：where function(a) = xx，给索引里的字段a套了一个函数，你觉得还能用上索引吗？明显是不行了。所以尽量不要让查询语句里的字段搞什么函数，或者是搞个计算。

设计索引的时候需要注意的点都已经讲完了，其实就是好好设计索引，让查询语句都能用上索引，同时注意一下字段基数、前缀索引和索引列套函数的问题，尽量让查询都能用索引，别因为一些原因用不上索引了。

其实查询基本都能走索引一般问题都不会太大的，但是插入就有点讲究了。插入数据肯定有主键吧，那有主键就得更新聚簇索引树，插入一条数据肯定会包含索引里各个字段的值吧，那联合索引的B+树是不是也要更新？

对了，不停的增删改数据，就会不停的更新索引树。

所以因为插入的数据值可能根本不是按照顺序来的，很可能会导致索引树里的某个页就会自动分裂，这个页分裂的过程就很耗费时间，因此一般设计索引别太多，建议两三个联合索引就应该覆盖掉这个表的全部查询了。否则索引太多必然导致增删改数据的时候性能很差，因为要更新多个索引树。

另外很关键一点，建议主键一定是自增的，别用UUID之类的，因为主键自增，那么起码聚簇索引不会频繁的分裂，主键值都是有序的，就会自然的新增一个页而已，但是如果用的是UUID，那么也会导致聚簇索引频繁的页分裂。

[Link 1]: https://so.csdn.net/so/search?q=%E7%B4%A2%E5%BC%95&spm=1001.2101.3001.7020