提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
索引是数据库中很重要的一个知识点,涉及数据库的设计规范,优化。抽象来理解就是目录的意思。
提示:以下是本篇文章正文内容,下面案例可供参考
一、什么是索引?有什么用?
-
什么是索引?
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源。
-
索引为什么可以提高检索效率呢?
在数据库方面,查询一张表的时候有两种检索方式:
第一种方式:全表扫描
第二种方式:根据索引检索(效率很高)
其实最根本的原理是缩小了扫描的范围。
例:
select name,age from user where name = '张三';
当name字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描name字段中所有的值。
当name字段上添加索引的时候,以上sql语句会根据索引扫描,快速定位。
通过 explain 能查询sql语句执行的执行计划
添加索引前 rows:扫描行数25
mysql> explain select name,age from user where age = 18;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
添加索引后 rows:扫描行数1
mysql> explain select name,age from user where age = 18;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | u_name_index | u_name_index | 9 | const | 1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
-
能不能随意添加索引?
索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护。是有维护成本的。比如,表中的数据经常被修改
这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护。
添加索引是给某一个字段,或者说某些字段添加索引。 -
索引的作用范围?
添加索引是给某一个字段,或者说某些字段添加索引。
二、什么情况下添加索引?
当表数据量庞大之后数据查询速度肯定会变慢,这时候就可以考虑数据库索引优化来提升查询速度。
- 该字段很少的DML(增删改 )操作
因为字段经常进行增删改 数据库需要不停的维护索引,会增加维护成本,所以不适合添加索引。 - 该字段经常出现在where子句中
经常出现在where查询条件中的字段适合创建索引
在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的 - 在经常用在连接的列上
这些列主要是一些外键,可以加快连接的速度 - 排序的列上
因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间
三、什么情况下不适合添加索引?
-
第一,对于那些在查询中很少使用或者参考的列不应该创建索引。
这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 -
第二,对于那些只有很少数据值的列也不应该增加索引。
这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。 -
第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。
这是因为,这些列的数据量要么相当大,要么取值很少。 -
第四,当修改性能远远大于检索性能时,不应该创建索引。
这是因为,修改性能和检索性能是互相矛盾的。
四、索引的其他知识
1.索引的分类
- 单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。 这里不要搞混淆了
1:普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一 点。
2:唯一索引:索引列中的值必须是唯一的,但是允许为空值。
3:主键索引:是一种特殊的唯一索引,不允许有空值。(主键约束,就是一个主键索引)。
主键索引与唯一索引的区别:
- 主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。
- 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。
- 唯一性索引列允许空值,而主键列不允许为空值。
- 主键索引在创建时,已经默认为非空值+ 唯一索引了。
- 一个表最多只能创建一个主键索引,但可以创建多个唯一索引。
- 主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。
- 主键可以被其他表引用为外键,而唯一索引不能。
- 组合索引:在表中的多个字段组合上创建的索引
只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。
例如,这里由id、name和age 3个字段构成的索引,索引行中就按id/name/age的顺序存放,索引可以索引下面字段组合(id,name,age)、(id,name)或者(id)。如果要查询的字段不构成索引最左面的前缀,那么就不会是用索引,比如,age或者(name,age)组合就不会使用索引查询。
- 全文索引:只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引
介绍了要求,说说什么是全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行,比如有"你是个大煞笔,二货 …"
通过大煞笔,可能就可以找到该条记录。这里说的是可能,因为全文索引的使用涉及了很多细节,我们只需要知道这个大概意思。一般开发中,不会用到全文索引,因为其占用很大的物理空间和降低了记录修改性,故较为少用。
2.索引的实现原理
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,
最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率
是最高的。
3.索引失效
select name from user where name like '%张%';
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的
总结
当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。