MySQL 索引必知必会

Posted by Waynerv on

category: 数据库

Tags: MySQL 索引

内容基于InnoDB引擎,索引的存储类型为B+树。

索引类型

该分类方式并不严格,仅方便理解。

单列索引

一个索引只包含单个列,但一个表中可以有多个单列索引。

普通索引

MySQL中的基本索引类型,没有任何限制,允许在定义索引的列中插入重复值和空值,加快数据查询速度。

普通索引的叶子节点为主键值,通过普通索引查询记录需要回表(即通过主键在主键索引中再查一次)。

唯一索引

索引列中的值必须是唯一的,但是允许为空值。

主键索引

值必须是唯一的,也不允许有空值,用作不同记录的唯一标识。通常会使用自增ID,也可使用其他字段。

主键索引的叶子节点存储行数据的物理地址。而其他索引则是存储的该行的主键值。 所以使用次级(非主键)索引会包含两次查找。一次是查找次级索引自身,然后查找主键(聚集索引)。

组合索引

在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段或字段中左边字符串时,索引才会被使用,使用组合索引时遵循最左前缀集合。

多个组合值唯一的列可以作为主键,因此组合索引也可作为主键索引。

创建索引

创建表时创建索引

格式:

CREATE TABLE 表名[字段名 数据类型]  [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) [ASC|DESC]

创建普通索引

在创建表时直接指定:

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))   
);  

若创建索引时未指定索引名,MySQL会自动用字段名当作索引名。

创建唯一索引

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

创建主键索引

CREATE TABLE mytable(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(10),
PRIMARY KEY(id)
);  

定义主键的同时即创建索引。

创建组合索引

创建一个表mytable,在表中的id、name和age字段上建立组合索引

CREATE TABLE mytable(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdxname (id,name,age)
);

在已存在的表上创建索引

修改表结构

格式:

ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]
普通索引

添加普通索引,索引值可出现多次。

ALTER TABLE tbl_name ADD INDEX index_name (column_list);
唯一索引

创建索引的值必须是唯一的(但允许为空)。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
主键索引

添加一个主键,索引值必须是唯一的,且不能为NULL。

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);

创建索引

使用CREATE INDEX语句对表增加索引,能够增加普通索引和UNIQUE索引两种。

格式:

CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])[ASC|DESC]
普通索引
CREATE INDEX indexName ON mytable(username(length)); 
唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

不主动创建索引

当一个表没有主键,或者没有一个索引,innodb按如下规则处理:

  1. 如果定义了主键,那么这个主键将作为聚集索引。
  2. 如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引。
  3. 如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,该列的值会随着数据的插入自增。

查看索引

查看表的索引

示例

SHOW INDEX FROM table_name;\G

添加 \G 来格式化输出信息。

结果解释

  • Table:创建索引的表
  • Non_unique:表示索引非唯一,1代表非唯一索引, 0代表唯一索引
  • Key_name:索引名称
  • Seq_in_index 表示该字段在索引中的位置,单列索引的话该值为1,组合索引为每个字段在索引定义中的顺序
  • Column_name:表示定义索引的列字段
  • Sub_part:表示索引的长度
  • Null:表示该字段是否能为空值
  • Index_type:表示索引类型

EXPLAIN检索语句

示例

EXPLAIN SELECT * FROM book WHERE year_publication = 1990;\G

结果解释

  • id: SELECT识别符。
  • select_type:所使用的SELECT查询类型,SIMPLE表示为简单的SELECT,不使用UNION或子查询。该SELECT查询时会使用索引。 其他取值:PRIMARY、UNION、SUBQUERY。
  • table:数据表的名字。
  • type:指定本数据表和其他数据表之间的关联关系,该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。
  • possible_keys:MySQL在搜索数据记录时可以选用的各个索引,该表中只有一个可用索引,year_publication
  • key:实际选用的索引
  • key_len:mysql使用索引的个数,当 key 字段的值为 null时,表示没有使用索引。
  • rows:MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。  

删除索引

修改表的结构

普通索引

ALTER TABLE testalter_tbl DROP INDEX c;

主键索引

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

DROP 直接删除

普通索引

DROP INDEX [indexName] ON mytable; 

注:转载本文,请与作者联系




如果觉得文章对您有价值,请作者喝杯咖啡吧

|
donate qrcode

欢迎通过微信与我联系

wechat qrcode

0 Comments latest

No comments.