MySQL学习笔记九:索引

时间:2020-09-09 19:45:00 来源:互联网 作者: 神秘的大神 字体:

一、索引分类

1.1、索引的分类有以下几种:

1)单值索引:即一个索引只包含单个列,一个表可以有多个单列索引。

2)唯一索引:索引列的值必须唯一,但允许有空值。

3)复合索引:一个索引包含多个列,如INDEX MultiIdx(id,name,age)

4)全文索引:只有在MyISAM引擎上才能使用,只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。

5)空间索引:空间索引是对空间数据类型的字段建立的索引。

二、索引操作

2.1、创建索引

语法:CREATE INDEX 索引名称 ON table (column[,column]...);

CREATE INDEX ID_INDEX ON emp (ID);
CREATE INDEX NAME_INDEX ON emp (NAME);

2.2、删除索引

语法:DROP INDEX 索引名称 ON 表名;

DROP INDEX NAME_INDEX ON emp;

2.3、查看索引

语法:SHOW INDEX FROM 表名;

SHOW INDEX FROM emp;

2.4、自动创建索引

1)在表上定义了主键时,会自动创建一个对应的唯一索引。

2)在表上定义了一个外键时,会自动创建一个普通索引。

三、EXPLAIN

3.1、关于EXPLAIN

作用:用来查看索引是否正在被使用,并且输出其使用的索引的信息。

3.2、EXPLAIN使用示例

3.3、EXPLAIN输出信息

id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。

select_type:所使用的SELECT查询类型。

table:显示这一行的数据是关于哪张表的。

type:type显示的是访问类型,是较为重要的一个指标,结果值从最好到最差依次是:system>const>eq_ref>ref<range>index>all(倒序)一般来说,保证查询至少达到range级别,最好能达到ref。

key:实际使用的索引,若为null,则没有使用到索引。(两种可能,①没建立索引。②建立索引,但索引失效)。查询中若使用了覆盖索引,则该索引仅出现在key列表中。

possible_keys:显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。

key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确型的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据定义计算而得,不是通过表内检索出的。

ref:显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或者常量被用于查找索引列上的值。只有当type为ref的时候,ref这列才会有值。

rows:根据表统计信息以及索引选用情况,大致估算出找到所需的记录所需要读取的行数,所以越小越好。可以用来查询sql的读取行数。

extra:包含不适合在其它列中显示但十分重要的额外信息。

四、哪些情况适合/需要创建索引

4.1、以下情况适合/需要创建索引:

1)主键(自动建立唯一索引)

2)外键

3)查询中与其它表关联的字段

4)频繁作为查询条件的字段

5)查询中统计或者分组的字段

6)查询中排序的字段

五、哪些情况不适合创建索引

5.1、以下情况不适合创建索引:

1)频繁更新的字段,因为每次更新不单单更新了记录还会更新索引。

2)WHERE条件里用不到的字段

3)表记录太少

4)经常增删改的表

5)如果某个数据列包含太多重复的内容(如性别,为它建立索引就没有太大的实际效果。)