-
索引有哪些类型
普通索引:最基本的索引,没有任何约束限制。
唯一索引:和普通索引类似,但是具有唯一性约束,可以有 null
主键索引:特殊的唯一索引,不允许有 null,一张表最多一个主键索引
组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并
全文索引:对文本的内容进行分词、搜索
覆盖索引:查询列要被所建的索引覆盖,不必读取数据行
-
为什么要使用索引?它们对数据库性能有何影响?
-
加快数据检索速度:通过创建适当的索引,数据库可以更快地定位和访问所需的数据行,减少了全表扫描或索引范围扫描的需求,从而加快了数据检索速度。
-
减少磁盘IO操作:索引可以帮助数据库系统减少磁盘IO操作的次数。相比于全表扫描,使用索引可以只检索和加载部分数据页到内存中,减少了磁盘IO的开销。
-
优化查询性能:索引可以使查询语句更快速地执行,减少了查询的响应时间。通过使用索引,数据库可以快速定位到满足查询条件的数据行,避免了进行全表扫描的开销。
-
改善排序和聚合操作:索引可以显著提升排序和聚合操作的性能。例如,在执行ORDER BY子句时,如果有合适的索引可用,数据库可以直接利用索引中的排序顺序,而无需额外的排序操作。
-
对多表连接操作的优化:在涉及多个表的连接操作中,使用索引可以加速连接过程,减少连接的时间复杂度。
-
创建索引有哪些注意点
- 索引应该建在查询应用频繁的字段,比如where 判断、 order 排序和 join 的(on)字段上创建索引。
- 索引的个数应该适量,索引需要占用空间,更新时候也需要维护。
- 区分度低的字段,例如性别,不要建索引。
- 频繁更新的值,不要作为索引,维护索引文件需要成本;还会导致页分裂,IO次数增多。
- 联合索引把散列性高(区分度高)的值放在前面为了更好的满足最左前缀匹配原则。
- 尽可能用联合索引代替多个单列索引(对于单列索引,MySQL基本只能使用一个索引,所以经常使用多个条件查询时更适合使用联合索引)。
- 过长的字段,使用前缀索引。当字段值比较长的时候,建立索引会消耗很多的空间,搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。
- 不建议用无序的值(例如身份证、UUID )作为索引,在插入时会造成叶子节点频繁分裂,出现磁盘存储的碎片化。
-
如何选择合适的列来创建索引?
-
查询频率:选择经常被查询的列作为索引列。针对经常出现在WHERE子句或JOIN操作中的列进行索引可以显著提高查询性能。
-
列的选择性:选择具有高选择性的列作为索引列。选择性是指索引列中不同值的唯一性程度。如果列具有高选择性,即不同值较多,那么使用索引将更加有效。例如,一个性别列只有两个可能的值(男、女),则对该列创建索引的效果会比较有限。
-
列的大小和类型:通常情况下,较小的列更适合创建索引,因为它们占用较少的存储空间,并且在内存中加载更快。而过大的文本类型或二进制类型的列则不太适合创建索引。
-
数据的更新频率:如果某个列的数据频繁更新,那么对该列创建索引可能会导致索引维护的开销增加。因此,在选择索引列时,需要权衡查询性能的提升与更新开销之间的平衡。
-
索引哪些情况下会失效呢
1.查询条件包含or,可能导致索引失效
2.如果字段类型是字符串,where时一定用引号括起来,否则会因为隐式类型转换,索引失效
3.like通配符可能导致索引失效。
4.联合索引,查询时的条件列不是联合索引中的第一个列,索引失效。
5.在索引列上使用mysql的内置函数,索引失效。
6.对索引列运算(如,+、-、*、/),索引失效。
7.索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效。
8.索引字段上使用is null, is not null,可能导致索引失效。
9.左连接查询或者右连接查询查询关联的字段编码格式不一样,可能导致索引失效。
10.MySQL优化器估计使用全表扫描要比使用索引快,则不使用索引。