MySQL（十一）索引的分类和创建原则

索引的创建与设计原则

1 索引的声明与使用

1.1 索引的分类

​ MySQL索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引

• 按照逻辑结构划分，主要有四种：普通索引、唯一性索引、主键索引和全文索引
• 按照物理实现方式划分，主要有两种：聚簇索引和非聚簇索引
• 按照作用字段：单列索引和多列索引（联合索引）

多列索引即联合索引，当多个字段构成联合主键的时候则为聚簇索引，否则为非聚簇索引

普通索引

• 在创建普通索引的时候，不需要任何的限制条件，只是用于提高查询速度
• 这类索引可以创建在任何数据类型中，其值是否非空和唯一，取决于字段本身的完整性约束
• 创建索引之后，可以直接通过索引进行查询。

唯一性索引

• 使用UNIQUE参数可以设置索引为唯一索引
• 在创建唯一索引的时候，会限制索引的值必须是唯一的，但是允许有空值
• 在一张表中可以有多个唯一索引。

UNIQUE会自动创建唯一索引，并且可以通过删除字段的唯一索引来删除唯一性

主键索引

• 主键索引是一种特殊的唯一索引，也就是NOT NULL + UNIQUE，也即是聚簇索引
• 一张表中只能有一个主键索引

这是由主键索引的物理实现方式（聚簇索引）决定的：数据存储在文件中只能按照一种顺序（主键大小）进行存储

单列索引

• 即在单个字段上创建的索引
• 单列索引可以是普通索引，也可以是唯一性索引还可以是全文索引，只需要保证对应一个字段即可
• 一张表可以有多个单列索引

多列索引

• 即在多个字段组合上创建一个索引，该索引指向创建时对应的多个字段，可以通过这几个字段进行查询
• 但是在查询的时候，只有使用了第一个字段时索引才会被使用（和上面一样，这是由主键索引的物理实现方式决定的：数据存储在文件中只能按照一种顺序（一个列的大小）进行存储），即最左前缀原则

全文索引

空间索引

1.2 创建索引

​ 首先创建数据库和表

CREATE DATABASE dbtest2 CHARACTER SET 'utf8'

CREATE TABLE dept (
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(20)
);

CREATE TABLE emp (
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
	emp_name VARCHAR(20) UNIQUE,
	dept_id INT,
	CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
)

① 创建表的时候添加索引

• 隐式创建索引：在create table的时候，会对主键约束、唯一性约束和外键约束的字段上，自动地添加相关的索引

  

• 显示创建普通索引：语法如下

  CREATE TABLE tableName [colName dataType ...]
  [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [indexName] (colName [length]) [ASC | DESC]
  

  • UNIQUE | FULLTEXT | SPATIAL：可选参数，即唯一索引、全文索引和空间索引
  • index和key作用相同，用来指定创建索引，一般情况下使用key
  • indexName：索引名，不指定的话默认为列名
  • colName：列名
  • length：可选参数，表示索引的长度，只有列为字符类型字段才需要指定
  • ASC、DESC：指定索引的排序顺序

  CREATE TABLE book (
  	book_id INT,
  	book_name VARCHAR(100),
  	AUTHORS VARCHAR(100),
  	info varchar(100),
  	COMMENT VARCHAR(100),
  	year_publication YEAR,
  	INDEX idx_bname(book_name)
  )
  

  

• 通过命令查看索引：

  方式一：show create table tableName

  mysql> show create table book\G
  *************************** 1. row ***************************
         Table: book
  Create Table: CREATE TABLE `book` (
    `book_id` int DEFAULT NULL,
    `book_name` varchar(100) DEFAULT NULL,
    `AUTHORS` varchar(100) DEFAULT NULL,
    `info` varchar(100) DEFAULT NULL,
    `COMMENT` varchar(100) DEFAULT NULL,
    `year_publication` year DEFAULT NULL,
    KEY `idx_bname` (`book_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
  1 row in set (0.01 sec)
  
  

  方式二：show index from tableName;

  mysql> show index from book\G
  *************************** 1. row ***************************
          Table: book
     Non_unique: 1 // 表示不是唯一
       Key_name: idx_bname
   Seq_in_index: 1
    Column_name: book_name
      Collation: A
    Cardinality: 0
       Sub_part: NULL
         Packed: NULL
           Null: YES
     Index_type: BTREE
        Comment: 
  Index_comment: 
        Visible: YES
     Expression: NULL
  1 row in set (0.09 sec)
  
  

• EXPLAIN性能分析工具

  EXPLAIN SELECT * FROM book WHERE book_name = 'mysql'
  

  

  可以看到查询语句possible_keys可能使用到的索引包括了idx_bname

• 创建表的时候添加唯一索引

  CREATE TABLE book1 (
  	book_id INT,
  	book_name VARCHAR(100),
  	AUTHORS VARCHAR(100),
  	info varchar(100),
  	COMMENT VARCHAR(100),
  	year_publication YEAR,
  	UNIQUE INDEX uk_idx_cmt(COMMENT)
  );
  
  
  INSERT INTO book1 VALUES (1, 'mysql', NULL, NULL, 'mysql高级', NULL)
  INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, 'mysql高级', NULL)
  -- [Err] 1062 - Duplicate entry 'mysql高级' for key 'book1.uk_idx_cmt'
  INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, NULL, NULL)
  INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, NULL, NULL)
  

  声明有唯一索引的字段，在添加数据的时候要保证数据的唯一性，但是可以多次添加NULL值，等效于添加唯一性约束

• 主键索引：通过主键约束的方式定义，即隐式的方式

  INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, NULL, NULL)
  
  mysql> show index from book2\G
  *************************** 1. row ***************************
          Table: book2
     Non_unique: 0
       Key_name: PRIMARY
   Seq_in_index: 1
    Column_name: book_id
      Collation: A
    Cardinality: 0
       Sub_part: NULL
         Packed: NULL
           Null: 
     Index_type: BTREE
        Comment: 
  Index_comment: 
        Visible: YES
     Expression: NULL
  1 row in set (0.04 sec)
  

  同样删除索引也只能通过删除主键约束的方式：

  ALTER TABLE book2
  DROP PRIMARY KEY

  添加自增的主键不能删除

• 创建单列索引

  前面的都是，这里就不再写了

• ⭐ 创建联合索引

  CREATE TABLE book3 (
  	book_id INT,
  	book_name VARCHAR(100),
  	AUTHORS VARCHAR(100),
  	info varchar(100),
  	COMMENT VARCHAR(100),
  	year_publication YEAR,
  	INDEX multi_bid_bname_binf(book_id, book_name, info)
  )
  SHOW INDEX FROM book3;
  

  

  ​ Seq_in_index表示索引的排序顺序，该联合索引会按照id、name、info的顺序进行排序，并且查询的时候要求根据最左前缀原则先查询id才能使用索引

  EXPLAIN SELECT * FROM book3 WHERE book_id = 1 AND book_name = 'mysql'
  -- possible keys : multi_bid_bname_binf
  
  EXPLAIN SELECT * FROM book3 WHERE book_name = 'mysql' = 1 AND book_id
  -- possible keys : multi_bid_bname_binf
  
  EXPLAIN SELECT * FROM book3 WHERE book_name = 'mysql'
  -- possible keys : NULL
  

  最左前缀原则是由数据库表真实的物理存储结构决定的，底层使用的B+树按照联合主键的字段顺序大小进行排序，因此使用别的字段值无法进行检索

  • 全文索引

    

    全文检索使用match + against的方式查询：

    select * from test4 where Match(name, info) against('检索字符串')
    

    使用全文检索性能相较于like快很多，但是精度差（可能会少数据）

• 空间索引

  

② 创建表的时候添加索引

• alter table

  ALTER TABLE tableName ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX indexName(colName ...)
  

• create index on

  CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX indexName ON tableName(colName ...)
  

1.3 删除索引

​ 使用场景：当表中存在较多索引并且进行大量增删改操作的时候，可以先进行删除索引的操作，以减少增删改过程对于索引的维护。

• ALTER TABLE DROP INDEX对应ALTER TABLE ADD INDEX

ALTER TABLE book3
DROP INDEX multi_bid_bname_binf

• DROP INDEX ON 对应 CREATE INDEX ON

DROP INDEX multi_bid_bname_binf ON book3

对于使用AUTO_INCREMENT约束的字段不能删除其主键或者索引，前面页提到过，因为AUTO_INCREMENT约束要求字段为主键或者UNIQUE

​ 当删除、修改字段的时候，会自动对索引进行维护：

SHOW INDEX FROM book3;

book3	1	multi_bid_bname_binf	1	book_id	A	0			YES	BTREE			YES	
book3	1	multi_bid_bname_binf	2	book_name	A	0			YES	BTREE			YES	
book3	1	multi_bid_bname_binf	3	info	A	0			YES	BTREE			YES	

ALTER TABLE book3
DROP COLUMN book_name;

SHOW INDEX FROM book3;

book3	1	multi_bid_bname_binf	1	book_id	A	0			YES	BTREE			YES	
book3	1	multi_bid_bname_binf	2	info	A	0			YES	BTREE			YES	

2 MySQL 8.0 索引新特性

2.1 支持降序索引

​ 降序索引会以降序存储键值，虽然在语法上mysql4.0就支持索引降序了，但是实际上DESC是被忽略的（使用反向扫描来实现降序），直到mysql 8.0 才开始真正支持降序索引（仅限于innoDB存储引擎）

​ 使用降序索引意义重大，如一个查询，需要对多个列进行排序，而且顺序要求不一致，那么降序索引就能够避免数据库使用额外的文件排序来实现反向扫描，从而提高性能。

​ 举个栗子，首先在5.0 和 8.0 都创建一张表：

CREATE TABLE ts1 (
	a int,
	b int,
	INDEX idx_a_b(a, b DESC)
)

​ 然后编写一个存储过程添加一些数据：

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i < 800
	DO
		INSERT INTO ts1 SELECT RAND() * 80000, RAND() * 80000;
		SET i = i + 1;
	END WHILE;
	COMMIT;
END //
DELIMITER;

​ 此时按照a升序、b升序检索，查看执行计划：

EXPLAIN SELECT * FROM ts1 ORDER BY a, b

1	SIMPLE	ts1		index		idx_a_b	10		799	100	Using index; Using filesort

可以看到使用了索引和文件排序

​ 此时按照a升序、b降序检索，查看执行计划：

EXPLAIN SELECT * FROM ts1 ORDER BY a, b DESC

1	SIMPLE	ts1		index		idx_a_b	10		799	100	Using index

可以看到只使用了索引

而5.0版本的mysql则是也会使用文件排序，因为实际存储时仍按照b升序排列的

这里就不演示了

2.2 支持隐藏索引

​ 使用场景：在MySQL5.7及之前，只能通过显示的方式删除索引，此时如果发现索引删除后出现错误，则又需要再添加创建索引，如果此时表中已经存储了大量的数据或者表本身比较大，则就会消耗过多的资源，操作成本非常高。或者想要验证索引删除后的性能影响。

​ 功能：Mysql 8.0后支持的隐藏索引，只需要将待删除的索引设置为隐藏索引，查询优化器就不会使用这个索引（哪怕使用force index也不会），确认删除索引不会出错后，再真正删除索引。这种通过设置隐藏索引，再删除索引的方式就是软删除。

主键不能设置为隐藏索引，当表中没有显示的主键，表中的第一个非空索引就会成为隐式主键，也不能设置隐藏索引

• 创建表的时候直接创建隐藏索引

  CREATE TABLE book4 (
  	book_id INT,
  	book_name VARCHAR(100),
  	AUTHORS VARCHAR(100),
  	info varchar(100),
  	COMMENT VARCHAR(100),
  	year_publication YEAR,
  	INDEX multi_bid_bname_binf(book_id, book_name, info) INVISIBLE
  )
  
  SHOW INDEX FROM book4
  

  

  这时候尝试使用索引进行查询：

  EXPLAIN SELECT * FROM book4 WHERE book_id = 1
  

  

• 创建表以后隐藏索引

  ALTER TABLE book4
  ADD UNIQUE INDEX uq_idx_bid(book_id)
  
  ALTER TABLE book4
  ADD UNIQUE INDEX uq_idx_bname(book_name) INVISIBLE
  

• 修改索引可见性

  ALTER TABLE book4
  ALTER INDEX uq_idx_bname VISIBLE;
  

注意

索引被隐藏只是查询优化器不可见，但是仍然和正常索引一样是实时更新的，因此索引长时间被隐藏应该删除，避免影像增删改的性能