MySQL索引

1.索引

1.1 索引的基本概念

索引的特点：MySQL中索引的存储类型有两种，即 BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持 BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和 BTREE索引。
索引的优点主要有以下几条：

• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的查询速度。
• 在实现数据的参考完整性方面，可以加速表和表之间的连接。
• 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点为：

• 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
• 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引的分类：

• 普通索引和唯一索引
  普通索引是 MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯主键索引是一种特殊的唯一索引，不允许有空值。

• 单列索引和组合索引
  单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
  组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

• 全文索引

  全文索引类型为 FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有 MyISAM存储引擎支持全文索引。

• 空间索引

  空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和 POLYGON。MySQL使用 SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NUL，空间索引只能在存储引擎为 MyISAM的表中创建。

索引的设计原则：

• 索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响 INSERT、DELETE、UPDATE等语句的性能。
• 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引。
• 数据量小的表最好不要使用索引。
• 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。
• 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高査询速度。
• 在频繁进行排序或分组（即进行 group by或 order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

1.2 创建表时创建索引

创建表时创建索引的语法：

CREATE TABLE 表名 [col name data type]
[UNIQUE I FULLTEXT I SPATIAL] [INDEX I KEY] [index_name] (col_name [length]) [ASC | DESC]

UNIQUE、FULLTEXT和 SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；

INDEX与KEY为同义词，两者作用相同，用来指定创建索引；

col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；

index_name指定索引的名称，为可选参数，如果不指定，MySQL默认 col_name为索引值；length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

ASC或DESC指定升序或者降序的索引值存储。

# 创建普通索引
CREATE TABLE book
(
    bookid            	INT NOT NULL,
    bookname          	VARCHAR(255) NOT NULL,
    authors            	VARCHAR(255) NOT NULL,
    info               	VARCHAR(255) NULL,
    comment           	VARCHAR(255) NULL,
    year_publication   	YEAR NOT NULL,
	INDEX(year_publication) # 在字段year_publication上建立普通索引
);

# 查看索引是否创建成功
explain select * from book where year_publication=1990 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE				# SELECT查询类型
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication		# 可用的索引
          key: year_publication		# 实际使用的索引
      key_len: 1					# 索引按字节计算的长度，key len数值越小，表示越快。
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

# 创建唯一索引
CREATE TABLE t1
(
    id    INT NOT NULL,
    name CHAR(30) NOT NULL,
    UNIQUE INDEX UniqIdx(id) # 在字段id上创建名为UniqIdx的唯一索引
);

# 创建单列索引
CREATE TABLE t2
(
    id   INT NOT NULL,
    name CHAR(50) NULL,
    INDEX SingleIdx(name(20))
);

# 建立组合索引
CREATE TABLE t3
(
    id    INT NOT NULL,
    name CHAR(30) 　NOT NULL,
    age  INT NOT　 NULL,
    info VARCHAR(255),
    INDEX MultiIdx(id, name, age(100)) # 组合索引在使用时必须遵循 最左前缀
);
		# 就是
		 explain select * from t3 where id=1 AND name='joe' \G # 查询 id 和 name 会使用到MultiIdx
		 # 以下两种查询没有id 不遵循左前缀查询，因此不会使用到MultiIdx索引
		 explain select * from t3 where name='joe' \G 
		 explain select * from t3 where name='joe' AND age=1 \G 

# 创建全文索引
CREATE TABLE t4
(
    id    INT NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;	# 仅MyISAM存储引擎支持全文索引

# 创建空间索引
CREATE TABLE t5
( 
    g GEOMETRY NOT NULL, # 仅GEOMETRY空间类型可以创建空间索引
    SPATIAL INDEX spatIdx(g) 
)ENGINE=MyISAM;

1.3 在已有表中创建索引

1.3.1 ALTER TABLE语句创建索引

# 查看表的索引
SHOW INDEX FROM book \G
*************************** 1. row ***************************
        Table: book					# 创建索引的表
   Non_unique: 1					# 索引非唯一：0表示唯一索引，1表示非唯一
     Key_name: year_publication		# 索引名称
 Seq_in_index: 1					# 该字段在索引中的位置：主要针对组合索引
  Column_name: year_publication		# 定义索引的列字段
    Collation: A			
  Cardinality: 0
     Sub_part: NULL					# 索引长度
       Packed: NULL
         Null: 						# 表示索引是否能为空
   Index_type: BTREE				# 索引类型
      Comment: 
Index_comment: 	
      Visible: YES
1 row in set (0.06 sec)

在已存在的表中添加索引的基本语法结构：

ALTER TABLE 表名 ADD [ UNIQUE I FULLTEXT I SPATIAL [ INDEX I KEY ]
[ index name] (col name [ length],……) [ ASC DESC]

# 添加普通索引
ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );

# 查看索引
SHOW INDEX FROM book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BkNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
2 rows in set (0.11 sec)

# 在bookId字段上添加唯一索引 UniqidIdx
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

# 在comment字段添加单列索引，其长度为50
ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );

# 添加组合索引
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );

# 添加全文索引：其表的存储引擎必须为MyISAM
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );

# 添加空间索引：其字段必须为GEOMETRY类型
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

1.3.2 使用CREATE INDEX创建索引

基本语法为：CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name [length],……) [ASC | DESC]

# 在已存在的表中创建普通索引
CREATE INDEX BkNameIdx ON book(bookname);

# 创建唯一索引
CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );

# 创建单列索引
CREATE INDEX BkcmtIdx ON book(comment(50) );

# 创建组合索引
CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );

# 创建全文索引：其表的存储引擎必须为MyISAM
CREATE FULLTEXT INDEX ON t6(info);

# 创建空间索引：其字段必须为GEOMETRY类型
CREATE SPATIAL INDEX spatIdx ON t7 (g);

1.4 删除索引

使用ALTER TABLE删除索引

ALTER TABLE 表名 DROP INDEX index_name;

使用DROP INDEX删除索引：DROP INDEX操作最终是映射到 ALTER TABLE操作上进行索引删除的

DROP INDEX index_name ON 表名;

单列索引、普通索引或者组合索引等，当包含的字段被完全删除时，索引也会自动删除