所有分类
  • 所有分类
  • 未分类

MySQL–索引的优点/缺点/创建索引的原则

简介

本文介绍MySQL索引的优点、缺点、创建索引的原则。

索引优点

索引优点就是提高了查询性能,主要是以下几个方面

  1. 索引大大减少了服务器需要扫描的数据量。
  2. 索引可以帮助服务器避免排序和临时表。
  3. 索引可以将随机I/O 变为顺序I/O

索引缺点

  1. 降低了数据写入的效率。
    1. 原因:当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护。
  2. 索引增加了查询优化器的选择时间。
    1. 查询优化器在对一条sql语句进行分析时,会结合一系列的分析计算出一条最优的查询sql。
    2. 添加了索引之后,相当于是在原来的基础上,添加了对索引因素的分析,若在很多字段上创建了索引,会增加这个选择的时间。
  3. 索引占物理空间。
    1. 除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

创建索引的原则

索引原则说明/示例
对查询频率高的字段创建索引        查询频率高的字段有:作为查询条件的字段(where子句中的列)、连接子句中指定的列
为经常需要排序、分组和联合操作的字段建立索引        经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。         如果为其建立索引,可以有效地避免排序操作。
尽量使用唯一索引        唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。唯一值少的列上不适合建立索引或者建立索引效率低,因为创建索引会对那一列做自动的排序。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。若使用姓名的话,可能存在同名现象,从而降低查询速度。         主键索引和唯一键索引,在查询中使用是效率最高的。
使用短索引        如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
使用前缀来索引        如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。
索引的数目不要太多1. 每创建一个索引都会占用相应的物理空间; 2. 过多的索引会导致insert、update、delete语句的执行效率降低;         在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。若一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL 在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是,则就不要费力去增加这个索引了,因为已经有了。
避免索引失效

不推荐使用索引的情况

情况说明
查询不频繁的字段既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
数据唯一性差(一个字段的取值只有几种时)的字段不要使用索引比如性别,只有两种可能数据。意味着索引的二叉树级别少,多是平级。这样的二叉树查找无异于全表扫描。
修改频率远远大于查询频率比如login count(登录次数),频繁变化导致索引也频繁变化,增大数据库工作量,降低效率。         修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

1

评论2

请先

  1. 数据唯一性差的。是不是超过一定的比例。比如百分之75或者半数以上。这种是不是即使命中索引 也是不会走索引的。直接全表扫描
    花生 2024-04-12 0
    • 对,它自己有算法,会权衡走索引和不走索引哪个更快更好,然后自动选择。
      自学精灵 2024-04-12 0
显示验证码
没有账号?注册  忘记密码?

社交账号快速登录