为什么要使用索引
当表中的数据量到达几十万甚至上百万的时候,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 子句的条件)。
-
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
-
索引虽然能够提高查询性能,索引需要占用额外的存储空间。对表进行插入、更新和删除操作时,索引需要维护,可能会影响性能。过多或不合理的索引可能会导致性能下降,因此需要谨慎选择和规划索引。
什么时候要使用索引?
- 主键自动建立唯一索引;
- 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引;
- 作为排序的列要建立索引;
- 查询中与其他表关联的字段,外键关系建立索引
- 高并发条件下倾向组合索引;
- 用于聚合函数的列可以建立索引,例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
什么时候不要使用索引?
- 经常增删改的列不要建立索引;
- 有大量重复的列不建立索引;
- 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。
索引失效的情况:
- 在组合索引中不能有列的值为NULL,如果有,那么这一列对组合索引就是无效的。
- 在一个SELECT语句中,索引只能使用一次,如果在WHERE中使用了,那么在ORDER BY中就不要用了。
- LIKE操作中,'%aaa%'不会使用索引,也就是索引会失效,但是‘aaa%’可以使用索引。
- 在索引的列上使用表达式或者函数会使索引失效,例如:
select * from users where YEAR(adddate)<2007,
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。
其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。 - 在查询条件中使用不等于,包括<符号、>符号和!=会导致索引失效。特别的是如果对主键索引使用!=则不会使索引失效,如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。(经erwkjrfhjwkdb同学提醒,不等于,包括<符号、>符号和!,如果占总记录的比例很小的话,也不会失效)
- 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
- 字符串不加单引号会导致索引失效。即类型涉及到类型强转的时候。
- 在查询条件中使用OR连接多个条件会导致索引失效,除非OR链接的每个条件都加上索引,这时应该改为两次查询,然后用UNION ALL连接起来。
- 如果排序的字段使用了索引,那么select的字段也要是索引字段,否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
- 尽量不要包括多列排序,如果一定要,最好为这队列构建组合索引;