Mysql设置索引的注意事项

深藏阁楼爱情的钟 2022-06-01 10:50 384阅读 0赞

在讲索引设置前先说说索引的原理。索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：**索引是一种数据结构**。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

MyisAM存储引擎的索引文件和数据文件是分开的，在通过索引查询得到的是数据记录在磁盘的物理地址，然后再根据这个地址去查询关联的数据信息。MyisAM不支持外键，不支持行级锁，不支持事务，所以不经常使用。

![这里写图片描述][SouthEast]

InnoDB存储引擎的索引和数据是存放在同一个文件内的，而且数据是按照主键的顺序排列的，所以主键索引也叫聚集索引或聚簇索引。普通字段上的索引是非聚集索引。主键索引上的value是当前记录信息(不包含主键)，非聚集索引上的value存储的是主键。当查询时使用的是主键索引，那么能直接返回需要的数据；如果查询时使用的是非聚集索引，第一步返回主键，第二步通过这个主键去查询相应的记录，额外多了一步。

![这里写图片描述][SouthEast 1]

![这里写图片描述][SouthEast 2]

相比于MyisAM存储，InnoDB引擎的索引结构有些不同，且分为聚集索引和非聚集索引，MyisAM没有这个之分，主键和非主键索引存的都是数据记录的磁盘地址。

1.**使用自增主键**  
因为InnoDB引擎的数据是按照主键的顺序排列的，所以如果主键是非自增的，那么当插入一条新的数据时，比如原先有主键为48，50的数据，他们是紧密排列的，当插入一条主键为49的数据，那么就需要将50的数据往下挪一位，然后将49的数据插入；如果当前数据页的容量已经满了，一般Mysql内的数据页大小是默认16K，如果每条数据记录的大小是500B，那么最多也只能存不到30条，因为数据页还需要存储其他类型的数据信息，比如上下页的引用地址，页类型等。当一页已经满了时，就需要分裂此页，那么久会导致一页左右的数据分散到两页中，两页上会有很多额外的空闲空间，也就是多了很多的磁盘碎片空间，导致资源的浪费。**主键非自增，插入数据时需要额外的移动数据行以及可能导致更多的碎片空间。**所以推荐使用不具有业务含义的自增字段来作为表的主键。

2.**改变频率太高的字段不适合加索引**  
索引是一种数据结构，频繁的改变索引字段的值，就会不断的改变当前记录对应的索引行在索引页的位置，类似主键非自增一样，会增加额外的磁盘IO消耗和额外的磁盘碎片。

3.**分布集中的字段不适合加索引**  
索引是按照字符的字典顺序排列的，如果加索引的字段重复性很高，那么通过索引页只能搜索到字段相同的区间，假设性别字段只有’男’和’女’两个选择，加了索引，也只能查询到字段值为’男’或’女’区间，效率很低下。虽然说相比原来也提高了50%的性能，但是索引也是需要占据磁盘空间的，而且一张表很多时候含有不止一个索引，导致索引占据的磁盘空不见得比实际数据小，为了空间和性能的平衡，不建议对值分布集中的字段添加索引。

4.**前缀索引**  
如果一个字段值的字符长度很长，而整个字段又是查询的常用字段，此时完全使用整个字段的值做索引是不合适的，因为这会导致索引结构变得很大，额外占据更多的磁盘空间，此时需要尝试添加前缀索引，也就是将这个字段的前多少个字符作为索引的key，在查询匹配时会将截取当前字段的前多少个字符和索引的key匹配。  
至于选择多少的长度为好，可以计算下：

select count( distinct ( left (column,length ) ) ) / count(column) from ....；

计算索引选择率，即字段前缀不同的记录行数除以记录总数，当有0.9以上时，可以视作是合适的前缀长度。

5.**复合索引的最左前缀原则**  
Mysql查询时，针对每张表只会使用当前表的一个索引，如果查询时很多时候会有几个字段作为查询条件，那么针对每个字段都建立一个索引是不合适的。此时可以尝试建立复合索引，也就是索引里包含了多个字段的值，复合索引的建立需要遵循最左前缀原则。所谓的最左前缀指的是**索引字段从左到右按照出现频率由高到低排列**，查询时出现频率最高的字段方最左侧，最低的放最右侧。对多个字段添加复合索引时，并非对每个字段都添加一个索引，而是对第一个字段值作为索引，以第一个字段值加上第二个字段值作为第二个索引，之后是第一，第二，第三个字段值合并做索引，假设复合字段为Index（A,B,C），则创建三个索引，A，A+B，A+B+C。复合索引对于多个字段查询的场景很适用，因为A+B+C这种索引形式使的key更加难以相同，也就是说多个索引字段查询时能够得到更小的结果范围。

[SouthEast]: /images/20220601/5237f97f56cc4930be7b9c62a630621e.png
[SouthEast 1]: /images/20220601/5c4c86883f14464b82a37c209381d2d3.png
[SouthEast 2]: /images/20220601/b4d2f3b1ee27424a9ecadff5eff9aec8.png