一、索引的概念
1.索引是什么
在百度百科上是这样介绍的:
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。简而言之,数据库索引是排好序的数据结构。
简单地说,索引是一种特殊的数据,保存了其他数据的位置,查找数据时是先去查找索引再通过索引保存的位置来查找到数据,这样就提升了查找的效率。
2.索引的优缺点
由上述,可以了解到索引的作用是加快查找的速度,提升查询的效率。
索引的优点:
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接
- 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
- 创建唯一性索引,保证数据库表中每一行数据的唯一性;
索引的缺点:
- 索引需要占物理空间
- 创建索引时需要较长时间
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度
二、索引的类型
1.按照功能分:
1)普通索引
最基本的索引类型,没有唯一性之类的限制。
create index 索引名 on 表(列);
alter table 表 add index 索引名(列);
2)唯一索引
唯一索引是不允许其中任何两行具有相同索引值的索引。
alter table 表 add unique index 索引名(列);
3)主键索引
数据库表中一列或列组合(字段)的值唯一标识表中的每一行。该列称为表的主键。
alter table 表 add primary key 索引名(列);
4)全文索引
对数据库中长文本进行匹配,text类型
alter table 表 add fulltext index 索引名(列);
5)组合索引
将数据库中多个列组合到一起进行查询
alter table 表 add index 索引名(列,列,列,列);
删除索引
drop index 索引名 on 表;
查询索引
show index from 表;
2.按照结构分:
1)聚簇索引
索引键的顺序和实际数据的顺序相同
2)非聚簇索引
索引键的顺序和实际数据的顺序不同
三、索引的使用
1..索引的使用场景
- 在经常需要搜索的列上,可以加快搜索的速度;
- 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
- 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
- 在经常需要排序的列上创建索引
- 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
2.索引失效场景
- 索引列上使用了函数
- 对所有列进行了运算
- 在索引列上使用了模糊查询。需要在关键字前加'%'
- 使用or关键字。需要or两侧的字段都有索引,否则会失效
- 查询条件中使用了>,<,>=,<=,且查询区间没有闭合,例如0<price<100则不会失效
- 查询条件中使用了组合索引,但没有使用最左侧的字段
3.索引的使用情况
使用explain关键字来查询索引使用情况
explain 查询语句
1)id
SQL执行的顺序的标识,SQL从大到小的执行
2)select_type
表示查询类型,分为简单查询(SIMPLE)和复杂查询(PRIMARY),复杂查询又分为三类:简单子查询、派生表(from语句中的子查询)、union 查询。
3)table
显示当前行的数据是属于哪张表的,有事看到的不是真实的表名,看到的时derivenN,N表示第N步执行的结果。
4)type
表示MySQL在表中找到所需行的方式,又称“访问类型”
依次从最优到最差分别为:system > const > eq_ref > ref> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery> range > index > ALL
一般来说,需要保证查询至少达到range级别,最好能达到ref
type值 | 含义 |
---|---|
system | 表中只有一条记录 ( 等于系统表 ) ,这是const类型的特例,平时不会出现,这个可以忽略不计。 |
const | 表示通过索引一次找到了,const用于primary key 或者 unique 索引。因为只匹配一行数据,所以很快,将主键置于where列表中,mysql 就能将该查询转换成一个常量。 |
eq_ref | 唯一性索引扫描,对于每个索引键,表中只有一条记录于之匹配。常见于主键和唯一索引扫描。 |
ref | 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。 |
range | 只检索给定范围的行,使用一个索引来选择行。keyl列显示使用了哪个索引,一般就是在你的where 语句中出现了between、< 、> 、in 等的查询。这种范围扫描比全表扫描要好,因为它只需要开始于索引的某一点,二结束于另一点,不用扫描全部的索引。 |
index | Full index scan , index 与All 区别为index类型只遍历索引树。这通常比All块,因为索引文件通常比数据文件小。(也就是说虽然All 和 index 都是读全表,但是index 是从索引中读取的,而all 是从硬盘中读取的)。 |
ALL | Full table Scan,将遍历全表以找到匹配的行。 |
index_merge | 在查询过程中需要多个索引组合使用,通常出现在有or的关键字的sql中 |
ref_or_null | 对于某个字段既需要关联条件,也需要null值的情况下,查询优化器会选择用ref_or_null连接查询。 |
index_subquery | 利用索引来关联子查询,不再全表扫描。 |
snique_subquery | 该类型类似于index_subquery。子查询中的唯一索引。 |
5)possible_keys
指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
6)key
显示MySQL实际决定使用的键(索引)
7)key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
8)ref
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。常见的有:const(常量),字段名(例:film.id)
9)rows
MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,注意这个不是结果集里的行数。
10)Extra
展示额外信息