MySQL索引相关

目录

1.索引是什么

2.索引的优点

3.索引的缺点

4.索引的分类

5.什么样的字段适合创建索引

5.1 表中某些在业务上具有唯一性的字段

5.2 频繁作为where查询条件的字段

5.3 经常group by和order by的列

5.4 update、delete语句中where的条件列

5.5 distince字段需要创建索引

5.6 多表join连接操作时，连接的字段需要加索引

5.7 使用类型小的数据列创建索引

5.8 使用字符串前缀创建索引

5.9 区分度高的列适合创建索引

5.10 使用最频繁的列放到联合索引的左侧

5.11 在多个字段都需要建立索引的情况下，联合索引优于单值索

6.什么样的字段不适合创建索引

6.1 在where中使用不到的字段

6.2 数据量小的表最好不要使用索引

6.3 有大量重复数据的列不要建立索引

6.4 避免对经常更新的表/字段创建索引

6.5 不建议用无序的值作为索引

6.6 不要定义冗余或重复的索引

---

1.索引是什么

        索引是一种帮助MySQL高效获取数据的数据结构。

2.索引的优点

• 提高数据检索效率，降低数据库IO成本
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
• 对于有依赖关系的字表和父表联合查询时，可以提高查询速度
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU消耗

3.索引的缺点

• 创建和维护索引需要耗费时间，且随着数据量的增加，耗费的时间也会增加
• 索引需要占用磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸
• 索引虽然提高了查询速度，同时却会降低更新表的速度。因为当对表中的数据进行增加、删除和修改时，需要动态的维护索引

4.索引的分类

按照功能划分

        普通索引：不附加任何限制，只用于提高查询效率。其值是否唯一和非空，受字段本身的完整性约束条件决定。

        唯一索引：使用UNIQUE创建的索引为唯一索引，允许空值，在一张表里可以有多个唯一索引

        主键索引：是一种特殊的唯一索引，在唯一索引的基础上加了非空约束，一张表里只能有1个唯一索引

        全文索引：使用FULL TEXT设置索引为全文索引，在定义所以的列上支持值的全文查找

按照物理实现划分

        聚簇索引：数据和索引在一个文件中的索引

        非聚簇索引：数据和索引不在一个文件中的索引

按照作用字段个数划分

        单列索引：在表中单个字段上创建的索引。

        联合（多列、组合）索引：在表中多个字段上创建的索引，遵循最左匹配原则。

5.什么样的字段适合创建索引

        5.1 表中某些在业务上具有唯一性的字段

        5.2 频繁作为where查询条件的字段

        5.3 经常group by和order by的列

        5.4 update、delete语句中where的条件列

        原理：update和delete之前，需要先根据where条件列检索出来这条记录，再对这条记录进行修改、删除

        5.5 distince字段需要创建索引

        5.6 多表join连接操作时，连接的字段需要加索引

        注：join的表尽量不要超过3张；对于用于双表连接的字段数据类型必须一致；

        5.7 使用类型小的数据列创建索引

        类型大小：该类型表示的数据范围的大小

        在定义数据列类型的时候，能用INT就不要使用BIGINT，因为：

• 数据类型越小，查询时进行的比较操作越快
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放下更多的记录，从而减少磁盘IO，加快读写效率

        特别是表的主键，因为聚簇索引和二级索引中都会存储主键值，如果主键使用更小的数据类型，意味着节省更多的存储空间和更高效的IO

        5.8 使用字符串前缀创建索引

        在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

        说明：索引长度和区分度是互斥的，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可使用count(distince left(列名,索引长度))/count(*)的区分度来确定。

        注：如果使用了字符串前缀来创建索引是无法支持索引排序的

        5.9 区分度高的列适合创建索引

        5.10 使用最频繁的列放到联合索引的左侧

        原理：最左匹配原则

        5.11 在多个字段都需要建立索引的情况下，联合索引优于单值索

6.什么样的字段不适合创建索引

        6.1 在where中使用不到的字段

        6.2 数据量小的表最好不要使用索引

        6.3 有大量重复数据的列不要建立索引

        当数据的重复度比较大高于10%时，就不需要对该字段添加索引

        6.4 避免对经常更新的表/字段创建索引

        6.5 不建议用无序的值作为索引

        6.6 不要定义冗余或重复的索引