【SQL】MySQL中的索引，索引优化

索引是存储引擎用来快速查询记录的一种数据结构，按实现方式主要分为Hash索引和B+树索引。
按功能划分，主要有以下几类

单列索引指的是对某一列单独建立索引，一张表中可以有多个单列索引
1. 单列索引 - 普通索引

• 创建索引（关键字index）：

create table student(
     id int primary key,
     card_id varchar(20),
     name varchar(20),
     index index_name(name) -- 1.创建表时，给name列创建普通索引
);
-- 2.创建表后，添加普通索引
create index index_name on student(name); 
-- 3.修改表结构，添加普通索引
alter table student add index index_name(name);
1
2
3
4
5
6
7
8
9
10

• 查看索引（通用操作）：

show index from student;
1

• 删除索引（通用操作，仅需修改索引名字）：

drop index index_name on student;
-- 或
alter table student drop index index_name;
1
2
3

2. 单列索引 - 唯一索引
与普通索引的区别在于唯一索引列的值必须唯一，但可以有空值

• 创建索引（关键字unique）：

create table student(
     id int primary key,
     card_id varchar(20),
     name varchar(20),
     unique index_card_id(card_id) -- 1.创建表时，给card_id列创建唯一索引
);
-- 2.创建表后，添加唯一索引
create unique index index_card_id on student(card_id); 
-- 3.修改表结构，添加唯一索引
alter table student add unique index_card_id(card_id);
1
2
3
4
5
6
7
8
9
10

3. 单列索引 - 主键索引
MySQL会在主键列上自动创建索引，就是主键索引。（联合主键也会自动创建对应的组合索引）
主键列唯一且不为空，所以主键索引是特殊的唯一索引。
4. 组合索引（复合索引）
组合索引指将多个列合在一起创建索引，可以创建组合普通索引，组合唯一索引（某一列值可以重复，但两列合起来不能重复）。

• 创建组合索引

-- 添加组合普通索引
create index index_card_id on student(id,card_id); 
-- 添加组合唯一索引
create unique index index_card_id on student(id,card_id); 
1
2
3
4

组合索引的使用符合最左原则，例如上面的索引，单独查id可以用到组合索引，单独查card_id用不到组合索引。
5. 全文索引（仅了解）

• 全文索引用来查找文本中的关键字，类似于like+%模糊匹配
• 关键字为fulltext，在大量文本数据查找时，速度比like+%快N倍
• 只有char、varchar、text及其系列才可以建全文索引
• 全文索引有两个变量，最小搜索长度和最大搜索长度，只有长度在最小搜索长度~最大搜索长度的文本，才能被索引查询。

-- 添加组合普通索引
alter table t_article add fulltext index_content(content);
-- 添加组合唯一索引
create fulltext index index_content on t_article(content); 
1
2
3
4

• 使用全文索引查询，使用match和against关键字，不能用like

-- match后跟字段，against后跟要模糊匹配的文本
select * from t_article where match(content) against('you');
-- 查询结果与下面语句相同
select * from t_article where content like '%you%';
1
2
3
4

6. 空间索引（使用很少，仅了解）
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有以下4种

• 添加空间索引（关键字spatial）

create table shop_info(
     id int primary key auto_increment comment 'id',
     shop_name varchar(64) not null comment '门店名称',
     geom_point geometry not null comment '经纬度',
     spatial key geom_index(geom_point) -- 添加空间索引
);
1
2
3
4
5
6

---

索引内部原理

• 索引通常以索引文件的形式存储在磁盘中
• 索引查找要产生磁盘I/O消耗，而磁盘I/O的消耗远高于内存I/O，所以索引的数据结构要尽量减少磁盘I/O的操作次数
• Hash索引
  通过字段值计算出hash值（可看作是存储地址，类似于书本中的页码），直接定位数据
  
• B+树索引
  二叉树 -> 平衡二叉树 -> B树 -> B+树
  二叉树
  
  平衡二叉树
  
  B树（B-Tree）
  PS：没有B减树，只有B树，英文就是B-Tree，-只是为了分隔B和Tree
  数据可视化网址https://www.cs.usfca.edu/~galles/visualization/BTree.html
  B+树
  数据可视化网址https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
  MyISAM存储引擎使用B+树作为索引结构，叶节点的data域存放的是数据的地址。
  InnoDB存储引擎使用B+树作为索引结构，叶节点的data域存放的是数据，比MyISAM效率高一点，但占磁盘空间。

---

索引失效的各种情况

-- 创建复合索引
-- 等价于三个索引name，name + status，name + status + address
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
-- 全值匹配（索引生效）
select * from tb_seller where name = '小米科技' and status = '1' and address = '北京市';
-- 最左前缀法则
select * from tb_seller where name = '小米科技'; -- 索引生效
select * from tb_seller where status = '1'; -- 索引不生效
select * from tb_seller where name = '小米科技' and address = '北京市'; -- 只有name列索引生效
-- 范围查询右边的列不能使用索引
select * from tb_seller where name = '小米科技' and status > '1' and address = '北京市'; -- 只有name和status使用索引，address没有使用索引
-- 索引列上运算则索引失效
select * from tb_seller where substring(name,3,2) = '科技'; -- 索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 查询列尽量包含仅包含索引列
select * from tb_seller where name = '小米科技' and address = '北京市';
-- or条件不会使用索引
select * from tb_seller where name = '小米科技' or status = '1'; -- 索引失效
-- %开头的模糊查询不会使用索引
select * from tb_seller where name like '%科技'; -- 索引失效
select * from tb_seller where name like '科技%'; -- 使用索引
-- %开头的模糊查询仅包含索引则可以使用索引
select name from tb_seller where name like '%科技%'; -- 使用索引
1
2
3
4
5
6
7
8
9

-- in使用索引，not in不使用索引（针对普通索引来说，若为主键索引则都可以使用索引）
select * from tb_seller where name in ('小米科技','阿里巴巴'); -- 使用索引
select * from tb_seller where name not in ('小米科技','阿里巴巴'); -- 不使用索引
-- 如果有多个单列索引，只会使用一个最优的索引
-- 如果不是组合索引，而是对name，status，address分别建立单列索引，则只会使用一列索引
select * from tb_seller where name = '小米科技' and status = '1' and address = '北京市';
1
2
3
4
5
6