为什么要使用索引
当表中的数据量到达几十万甚至上百万的时候,SQL查询所花费的时间会很长,导致业务超时出错,此时就需要用索引来加速SQL查询。但是索引也是需要存储成索引文件的,因此对索引的使用也会涉及磁盘I/0操作。如果索引创建过多,使用不当,会造成SQL查询时,进行大量无用的磁盘I/O操作,降低了SQL的查询效率,适得其反,所以要掌握良好的索引创建原则。
索引对于良好的性能非常关键。数据量越大时,索引对性能的影响也越重要,好的索引可以将查询性能提高几个数量级。在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但是在数据量逐渐增大时,糟糕的索引会使MySQL的性能急剧的下降。
索引分类
索引是创建在表上的,是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度!
从物理上(聚集&非聚集)
从逻辑上
普通索引
没有任何限制条件,可以给任何类型的字段创建普通索引,可以给(创建新表&已创建表),数量是不限的(但是不同的存储引擎对索引的数量也有限制),一张表的一次查询只能使用一个索引,具体指的是where后面的字段;
唯一性索引
使用UNIQUE修饰的字段,值不能够重复,主键索引就隶属于唯一性索引;
CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
- CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
- index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
- table_name: 指定要在哪个表上创建唯一索引。
- (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
- ASC和DESC(可选): 用于指定索引的排序顺序。默认情况下,索引以升序(ASC)排序。
主键索引:使用Primary Key修饰的字段会自动创建索引(MyISAM不会自动创建, InnoDB会自动创建);
ALTER TABLE table_name ADD PRIMARY KEY (column);
单列索引:在一个字段上创建索引;
多列索引:在表的多个字段上创建索引(uid+cid,多列索引必须使用到第一个列,才能用到多列索引,否则索引用不上);
全文索引:使用FULLTEXT参数可以设置全文索引,只支持CHAR,VARCHAR和TEXT类型的字段上,常用于数据量较大的字符串类型上,可以提高查询速度(线上项目支持专门的搜索功能,给后台服务器增加专门的搜索引擎支持快速高校的搜索 elasticsearch 简称es)。
ALTER TABLE table_name ADD FULLTEXT (column);
索引的优缺点
优点: 提高查询效率
缺点: 索引并非越多越好,使用索引需要使用磁盘I/O,并且索引也是一种数据结构,对某一列建立索引,mysql会对这一列进行排序的,当索引过多时,我们在增加或者删除元素时,不仅仅会涉及到数据的改动还会造成索引文件的改动。所以过多的索引会导致CPU使用率居高不下,由于数据的改变,会造成索引文件的改动,过多的磁盘I/0造成CPU负荷太重。
创建索引时,需要注意的点
尽量确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
索引虽然能够提高查询性能,索引需要占用额外的存储空间。对表进行插入、更新和删除操作时,索引需要维护,可能会影响性能。过多或不合理的索引可能会导致性能下降,因此需要谨慎选择和规划索引。