MySQL索引的优点和缺点
通俗的来说,索引就相当于一个字典的目录,为了方便查找书中的内容,通过对内容建立索引形成目录,索引是一个文件,它是要占据物理内存的。
官方的来说索引它是一种数据结构,数据库索引是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据表中的数据。
优点:
(1)通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
(2)可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
(3)可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
(4)在使用分组和排序子句进行数据检索时,同时可以显著减少查询中分组和排序的时间。
缺点:
(1)创建索引和维护索引需要时间成本,这个成本随着数据量的增加而加大。
(2)创建索引和维护索引需要空间成本,每一条索引都要占据数据库的物理存储空间,数据量越大,占用及空间也越大(数据表占据的是数据库的数据空间)
(3)会降低表的增删改的效率,因为每次增删改索引需要进行动态维护,导致时间边长。
索引的使用场景
(1)选择唯一性索引:
唯一性索引的值是唯一的,可以更加速的通过该索引来确定某条记录。
就比如学生表中学生的学号是具有唯一性的字段,为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话可能会存在同名现象,从而降低查询速度。
(2)经常需要排序,分组,联合操作的字段建立索引:
经常需要 order by,group by ,distinct , union 等操作的字段,排序操作会浪费很多时间,为其建立索引可以有效地避免排序操作。
(3)常作为查询条件的字段建立索引:
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度,为这样的字段建立索引,可以提高整个表的查询速度。
(4)限制索引的数目:
索引的数目不是越多越好,每个索引都需要占用磁盘空间,索引越多需要的磁盘空间就会越大。修改表的时候对索引的重构和更新都会很麻烦;索引越多更新表就会越浪费时间。
(5)尽量使用数据量少的索引:
索引的值越长查询的速度会越慢。例如一个char(100)类型的字段进行全文检索需要的时间比char(10)类型的字段需要的时间更多。
(6)删除不再使用或很少使用的索引:
表中的数据大量更新或者是数据的使用方式被改变后,原有的一些索引可能不在需要,数据库管理员应当定期找出这些索引,将他们删除,从而减少索引对更新操作的影响。
(7)尽量的扩展索引,不要新建索引:
比如表中已经有a 的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
索引的分类
常见的索引类型有主键索引,唯一索引,普通索引,全文索引,联合索引
(1)主键索引,即主索引,根据主键建立索引,不允许重复,空值
ALTER TABLE ‘mydb’ ADD PRIMARY KEY pk_index(‘a’);
(2)唯一索引,用来建立索引的值必须是唯一的,允许空值。
ALTER TABLE ‘mydb’ ADD UNIQUE index_name(‘a’);
(3)普通索引:用表中的普通列构建的索引,没有任何限制
ALTER TABLE ‘mydb’ ADD INDEX index_name(‘a’);
(4)组合索引:用多个列组合构建的索引,这多个列中的值不允许有空值
ALTER TABLE ‘mydb’ ADD INDEX index_name(‘a’,‘a2’,‘a3’…);