【阅读】《MYSQL技术内幕：InnoDB》索引使用

B+树索引使用

不同应用下

1. OLTP 应用中，查询操作只从数据库中取得一小部分数据，如根据主键值取得用户信息，根据订单号取得订单的详细信息。通常会添加主键索引
2. OLAP 应用中，查询多是面向分析的查询，目的是为决策者提供支持，如这个月每个用户的消费情况，销售额同比、环比增长的情况。通常会添加时间索引

使用联合索引

1. 创建索引

--创建表并添加对应的数据
CREATE TABLE `buy_log` (
  `user_id` INT UNSIGNED NOT NULL,
  `buy_date` DATE NULL,
  `log` TEXT(4000) NULL
) ENGINE=InnoDB;

INSERT INTO `buy_log` (`user_id`, `buy_date`, `log`) VALUES ('1', '2023-01-01', 'buy some in 2023-01-01');
INSERT INTO `buy_log` (`user_id`, `buy_date`, `log`) VALUES ('2', '2023-01-01', 'buy some2 in 2023-01-01');
INSERT INTO `buy_log` (`user_id`, `buy_date`, `log`) VALUES ('3', '2023-02-01', 'buy some3 in 2023-02-01');
INSERT INTO `buy_log` (`user_id`, `buy_date`, `log`) VALUES ('1', '2023-01-02', 'buy some2 in 2023-01-02');
INSERT INTO `buy_log` (`user_id`, `buy_date`, `log`) VALUES ('3', '2023-01-03', 'buy some4 in 2023-01-03');
INSERT INTO `buy_log` (`user_id`, `buy_date`, `log`) VALUES ('1', '2023-01-04', 'buy some in 2023-01-04');
INSERT INTO `buy_log` (`user_id`, `buy_date`, `log`) VALUES ('1', '2023-01-01', 'buy some3 in 2023-01-01');
-- 创建索引
alter table buy_log add key (user_id);
alter table buy_log add key (user_id, buy_date); 

查看索引信息

show index from buy_log

总共创建了三个索引，其中 user_id_2 就是联合索引
2. 查看索引的使用

explain select user_id,buy_date from buy_log where user_id =2;

possible_keys 可以看出有两个索引选择，key 表示最终选择了 user_id 这个索引。
这时并没有选择联合索引。

explain select user_id,buy_date from buy_log where user_id =1 order by buy_date desc;

当添加上根据 buy_date 进行排序时，优化器就选择了 user_id_2 这个联合索引了，因为在这个联合索引中 buy_date 已经排序好了，根据该索引取出数据，无需再对 buy_date 做一次额外的排序操作。

alter table buy_log add key (user_id, buy_date, log(100));
drop index user_id_2 on buy_log;
explain select user_id,buy_date,log from buy_log where user_id=1 order by log(100) asc;

当我删除 user_id_2 索引，添加 user_id_3 （user_id, buy_date, log）索引时，再执行 上述查询语句，将不使用 user_id_3 索引。

索引（a,b,c）：

• 使用索引
  • where a=1 and b =2 order by c
  • where a=1 order by b
• 不使用索引 （a,b,c）
  - where a=1 order by c

explain select user_id,buy_date from buy_log where user_id=1 order by log(100) asc;

但是我不查询 log 值时就会使用user_id_3 的联合索引。

索引使用不仅是和 where 后面的语句相关，还和查询的字段相关

使用覆盖索引

概念：从辅助索引中可以查询到记录，而不需要查询聚集索引中的记录

explain select count(*) from buy_log;

这里的 possible_key 列为空，而 实际执行中优化器却选择user_id 的辅助索引，列 Extra 列的 Using index 就是代表了优化器进行了覆盖索引操作。

不使用索引

select 字段和索引没有关系

explain select user_id,buy_date,log from buy_log where user_id=1;

这里的 log 字段不在索引中，所以没有使用索引

提示使用索引

显示的告诉优化使用索引，但是不一定可以之间使用上

explain select * from buy_log use index(user_id) where user_id=1;

强制使用索引

explain select * from buy_log where user_id=1;

explain select * from buy_log force index(user_id) where user_id=1;

可以看出使用 force index({index_name}) 可以强制使用索引

hash 索引使用

只提供内部自适应 hash 索引，数据库自身创建并使用，DBA 本身并不能对其进行干预

全文索引使用

1. 创建索引

CREATE TABLE `fts_a` (
  `FTS_DOC_ID` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  `body` TEXT NULL,
  PRIMARY KEY (`FTS_DOC_ID`));
  
INSERT INTO `fts_a` (`body`) VALUES ('Pease porridge in the pot');
INSERT INTO `fts_a` (`body`) VALUES ('pease porridge hot, pease porridge cold');
INSERT INTO `fts_a` (`body`) VALUES ('Nine days old');
INSERT INTO `fts_a` (`body`) VALUES ('Some like it hot, some like it cold');
INSERT INTO `fts_a` (`body`) VALUES ('Some like it in the pot');
INSERT INTO `fts_a` (`body`) VALUES ('Nine dys old');
INSERT INTO `fts_a` (`body`) VALUES ('I like cold days');
-- 创建倒排索引
create fulltext index idx_fts on fts_a(body);

SELECT * FROM fts_a;

2. 查看分词对应的信息

set global innodb_ft_aux_table = 'test/fts_a';
select * from information_schema.INNODB_FT_INDEX_TABLE;

每个 word 对应一个文件id以及位置
3. 删除时

DELETE FROM `fts_a` WHERE `FTS_DOC_ID`='7';
select * from information_schema.INNODB_FT_DELETED;

虽然执行删除，但是在对于 INNODB_FT_INDEX_TABLE 表没有变化，而在 INNODB_FT_DELETED 中发现对应的记录。

-- 配置只对全文索引优化
set global innodb_optimize_fulltext_only=1;
-- 优化表
optimize table fts_a;
select * from information_schema.INNODB_FT_BEING_DELETED;
select * from information_schema.INNODB_FT_INDEX_TABLE;

当进行 optimize table后，INNODB_FT_INDEX_TABLE表才发生变化，没有了 doc_id=7 相关的记录。虽然表中没有 7 这条记录，但是不能往 fts_a 表中插入 fts_doc_id =7 的记录。
4. 查询

explain select * from fts_a where body like 'Pease%'

• 使用Natural Language 方法查询

explain select * from fts_a where match(body) against('Pease' in natural language mode);

select fts_doc_id,body,match(body)against('Pease') as relevent from fts_a;

获取相关性

模糊查询的不是一个单词，直接使用全文检索会得不到结果

• 使用 Boolean 方法查询
  eg. 查询不包含 Pease 但是有 hot 的内容

select * from fts_a where match(body) against('-Pease hot' in boolean mode);

Boolean 支持的操作符如下图所示：

-Query expansion
隐含单词的拓展查询，比如要查询 database ，客户希望查询的不只是 databse 还有 MYSQL,Oracle, 等等