详细的Sql索引知识总结

概念

是关系型数据库表中一列或多列的值排序后的存储结构
索引也是一张表，该表保存了主键与索引字段，并指向实体表记录
类比
- 汉语字典的目录页，我们可以通过拼音、笔画、部首等方式查询需要的字
- 表格中的索引相当于是目录，通过一定的条件来快速定位到我们想要的表记录

优缺点

优点
- 减小了服务器需要扫描的数据量
- 索引可以将随机 IO 变成顺序 IO
缺点
- 降低建表、改表的速度
- 占用磁盘空间
- 重复数据过多索引效果差
- 索引的选择性高则效率高

分类

逻辑划分
- 普通索引（index）对指定字段没有限制，主要是提高访问速度
```
CREATE INDEX idx_username ON user_tbl(username);
```
- 对指定字段在表中只能是唯一的（对已有数据不生效，在插入或修改表中数据时做校验）
```
CREATE UNIQUE INDEX idx_username ON user_tbl(username);
```
- 主键索引（primary key）指定字段不允许为空值，不能重复
```
CREATE TABLE user_tbl( 
  ID INT NOT NULL, 
  username VARCHAR(16) NOT NULL, 
  PRIMARY KEY(ID) 
);
```
- 全文索引(fulltext index) 使用模糊搜索，like '%hello%'时需要用到全局索引
```
CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name);
```

物理实现划分
- 聚集索引数据行的物理顺序与列值的逻辑顺序相同（一般使用id自动递增），一个表只有一个聚集索引聚集索引不是一种单独索引类，而是一种数据存储方式

-   非聚集索引

    -   索引是顺序存储的，但索引项对应的内容是随机存储的
    -   两次查找：先查找索引再搜索数据

字段个数划分

单一索引：在一个列上添加索引

联合索引（组合索引、复合索引）

在多个列上添加索引

最左匹配原则：

(x,y,z):
WHERE x = 1
WHERE x = 1 AND y = 1
WHERE x = 1 AND y = 1 AND z = 1

(z,y,x):
WHERE z = 1
WHERE z = 1 AND y = 1
WHERE z = 1 AND y = 1 AND x = 1

使用注意事项

推荐使用
- WHERE, GROUP BY, ORDER BY
- 多张表 JOIN 的时候，对表连接字段创建索引
- 多个单列索引在多条件查询是只会有一个最优的索引生效：WHERE > GROUP BY > ORDER BY
不推荐使用
- 数据量很小的表
- 有大量重复数据的字段
- 频繁更新的字段
- 索引字段使用了函数或者表达式计算
- 大于小于条件
  - 命中数量很多，索引生效
  - 命中数量很小，索引失效
- 不等于条件 != <>，索引失效
- LIKE 值以 % 开头，索引失效

索引和数据类型的关系

字段是varchar类型，参数是int类型，不走索引
字段是varchar类型，参数是字符串，走索引
字段是int类型，参数是int类型，走索引
字段是int类型，参数是字符串，走索引