MYSQL 索引

一、索引分类

        普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

        从功能逻辑上说，主要分为4种：普通索引、主键索引、唯一索引、全文索引

        从物理实现方式上，分为两种：聚簇索引、非聚簇索引。

        从列数上，分为两种：单列索引、组合索引。

二、索引的创建

1.在建表时指定

- 创建一般索引

CREATE TABLE IF NOT EXISTS book(
id INTEGER,
bookname VARCHAR(10),
INDEX idx_name(bookname)
);

INSERT INTO book
VALUES(1,'aa'),
(2,'bb'),
(3,'cc'),
(4,'dd'),
(5,'ee');

使用EXPLAIN 查看SQL查询是否走索引。EXPLAIN 字段详解

EXPLAIN SELECT * FROM book WHERE bookname=''; type 为ref,表示会进行索引查找。

EXPLAIN SELECT * FROM book WHERE id=1; type 为ALL也就是全表扫描。

- 创建唯一索引

CREATE TABLE IF NOT EXISTS book(
id INTEGER,
bookname VARCHAR(100),
UNIQUE INDEX idx_name(bookname)
);

- 创建主键索引

CREATE TABLE IF NOT EXISTS book(
id INTEGER PRIMARY KEY,
bookname VARCHAR(100)
);

- 创建联合索引

CREATE TABLE IF NOT EXISTS book(
id INTEGER,
bookname VARCHAR(100),

info varchar(100),
INDEX idx_mul(id,bookname,info)
);

联合(组合)索引需要满足最左前缀原则。

EXPLAIN SELECT * FROM book WHERE id=1; 索引查找

EXPLAIN 分析一波： 

EXPLAIN SELECT * FROM book WHERE bookname='aa'; 类似ALL,只不过遍历的时索引那颗B+树的所有叶子节点。性能低下。

 EXPLAIN SELECT * FROM book WHERE info='aaa'; 类似ALL,只不过遍历的时索引那颗B+树的所有叶子节点。性能低下。

 EXPLAIN SELECT * FROM book WHERE id=1 AND bookname='aa'; 索引查找

EXPLAIN SELECT * FROM book WHERE bookname='aa' AND info='aaa';类似ALL,只不过遍历的时索引那颗B+树的所有叶子节点。性能低下。

 

EXPLAIN SELECT * FROM book WHERE id=1 AND bookname='aa' AND info='aaa';索引查找

还有很多情形不会按索引搜索。如 OR 、非前缀LIKE（%xxx 、 %xx%） 等，这里不一一列举。

2.建表后使用ALTER TABLE创建索引

ALTER TABLE book ADD INDEX idx_name(bookname);

ALTER TABLE book ADD UNIQUE INDEX idx_name(bookname);

ALTER TABLE book ADD INDEX idx_mul(id,bookname,info);

3.使用CREATE INDEX 创建索引

格式：CREATE INDEX  index_name  ON table_name(column,...)

CREATE INDEX  idx_name ON book (bookname);

CREATE UNIQUE INDEX  idx_name ON book (bookname);

CREATE INDEX  idx_mul ON  book(id,bookname,info);

三、索引的删除

ALTER TABLE table_name DROP INDEX idx_name;

DROP INDEX idx_name ON table_name;

四、隐藏索引（mysql 8.x 才支持）

1.为啥需要隐藏索引：（来回创建索引开销大）

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能 通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

        只需要将待删除的索引设置为隐藏索引，优化器将不再使用该索引，即使force index 也没用。这种隐藏索引也称为索引的软删除。

        如果一个隐藏索引长期隐藏，建议删除，因为它还在随着表的更新而更新！    

2.创建：关键字 INVISIBLE 

CREATE TABLE IF NOT EXISTS book(
id INTEGER,
bookname VARCHAR(100),
INDEX idx_name(bookname) INVISIBLE 
);

或者

ALTER TABLE book ADD INDEX idx_name(bookname)  INVISIBLE;

或者：

CREATE INDEX  idx_name ON  book(bookname) INVISIBLE;

3.切换成可见，让优化器感知到。

切成不可见：

ALTER TABLE book ALTER INDEX idx_name INVISIBLE;

切成可见：

ALTER TABLE book ALTER INDEX idx_name VISIBLE;

        

在mysql 8.x中，可以设置变量use_invisible_indexes，让隐藏的索引也能被优化器感知到：当然