mysql进阶-索引进阶

目录

1. 索引的相关语法

1.1 创建索引：

1.2 删除索引：

1.3 其他修改或创建方法：

2. 索引创建分类

2.1 索引类型

2.2 索引方法

2.3 索引分类

3. 索引原则

3.1 覆盖索引

3.2 最左前缀原则

3.3 索引下推(index condition pushdown)

---

1. 索引的相关语法

假如我们存在订单表，表结构如下：

CREATE TABLE `t_order` (
  `order_id` int(12) NOT NULL AUTO_INCREMENT COMMENT '订单主键',
  `money` decimal(10,2) DEFAULT NULL COMMENT '金额',
  `good_id` int(12) DEFAULT NULL COMMENT '商品ID',
  `good_name` varchar(20) DEFAULT NULL COMMENT '商品名称',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

1.1 创建索引：

CREATE INDEX 索引名 ON <表名> (字段1，字段2，...);

示例：给商品id添加索引

create index index_good_id on t_order (good_id);

1.2 删除索引：

DROP INDEX 索引名 ON <表名>;

示例：删除商品id字段

drop index index_good_id on t_order;

1.3 其他修改或创建方法：

-- 创建表时创建索引
CREATE TABLE 表名
(
字段 数据类型,
….
{ INDEX | KEY } 索引名(字段1，字段2，...)
)
​
-- 修改表时修改索引
ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段1，字段2，...);
​
-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY；

本人推荐使用最直接的创建索引和删除索引的语法，不建议和表的操作结合。

2. 索引创建分类

2.1 索引类型

索引类型分为：Normal 普通索引、Unique 唯一索引、Full Text 全文索引、SPATIAL 空间索引。

普通的开发一般只会用到普通索引和唯一索引。普通索引就是：可以重复。唯一索引就是不可以重复。这两个索引的含义也比较好理解。

我们使用上边的语法创建的索引默认是：普通索引。

2.2 索引方法

索引方法分为：BTREE、HASH两种方式。

BTREE索引的优点上篇文章已总结。

HASH索引的检索效率非常高，但是对于范围查询性能非常低。

我们使用上边的语法创建的索引默认是：BTREE。

2.3 索引分类

根据创建索引时的字段的多少可以分为单字段索引和组合索引。单字段索引就是创建索引时只包含一个字段，例如上边的示例。多字段索引就是创建索引时包含多个字段，如下：

create index index_good_id on t_order (good_id,good_name);

当然还有其他分类，例如聚簇索引和非聚簇索引。

3. 索引原则

3.1 覆盖索引

假如我们在t_order表中创建了good_id字段的索引，

create index index_good_id on t_order (good_id);

此时有如下查询语句：

select order_id from t_order where good_id

此语句查询时会用到用到good_id索引，并且order_id作为主键也在索引上，因此可以直接提供查询结果，不需要回表(回表的概念可以查看上篇文章：mysql进阶-索引基础-CSDN博客)。在这个查询里，索引index_good_id已经“覆盖”了我们的查询需求，我们称为覆盖左右。

常见的场景：当我们需要根据身份证查询姓名时，那么我们可以在居民表中建立身份证和姓名的联合索引。

3.2 最左前缀原则

该原则只使用于组合索引。组合索引的多个字段是有序的，遵循左对齐的原则。

假设在订单表中，我们查询商品名称首个字是香的商品的订单ID：

select order_id from t_order where good_name like '香%'

如果我们在表中有如下索引：

create index index_good_name on t_order (good_name);

那么我们也可以用到索引。

由上可知：不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

3.3 索引下推(index condition pushdown)

索引下推是5.6版本之后引入的。

假如我们在表中有如下索引：

create index index_good_money on t_order (good_name,money);

如果我们有如下查询：

select * from t_order where good_name = '香蕉' and money > 10.0

此时数据库会使用索引下推，索引下推就是在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。