04 - Index

04 - 索引

先准备 Linux 系统下的 MySQL...

1. 索引概述

1.1. 介绍

索引 index...帮助 MySQL高效获取数据的数据结构...有序...

1.2. 数据结构

常用树...二叉树，红黑树...B+树

1.3. 演示

比如在 select时...会遍历整个表来找出 查询

  • 称为全表扫描，性能极低...

如果有了索引，有序的索引就可以方便很多

  • 维护二叉树？

  • 可以瞬间降低复杂度...

1.4. 优缺点

优点

  1. 提高检索效率

  2. 提高 IO 效率

  3. SELECT 效率高...

缺点

  1. 维护占用成本

  2. 索引列占用空间

  3. INSERT UPDATE DELETE 效率低

2. 索引结构

回顾 MySQL 架构，索引在引擎层实现

2.1. 分类

B+Tree

  • 最常见的...InnoDB默认的...？

面试也会问

Hash 哈希表实现

R-tree 空间索引

Full-text 全文索引

2.2. 从二叉树到 B+ 树

二叉树

顺序插入的时候，会形成链表...

树就失去了性能？

红黑树

相对平衡的二叉树

按照节点大小来进行定位

B-Tree

多路平衡查找树

一个 n-1 key 的节点最多对应 n 个指针

根据区间来进行下层指针选择？

如果节点个数大于 度...就会向上分裂...

数据结构可视化网站：https://www.cs.usfca.edu/~galles/visualization/

B+Tree

和 B 树很像...

所有的元素都会出现在叶子节点

非叶子节点主要取到索引的作用

B+ 树的节点个数大于度的时候...叶子节点仍然保留元素，也向上分裂

  • 所有数据都存在叶子节点...

  • 叶子节点形成单向链表

B+Tree优化

在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，形成了带有顺序的链表（双向可遍历？）...

2.3. Hash

通过 Hash 算法：

  • 将键值换算成新的 Hash 值

  • 如果出现 hash 冲突，通过链表解决...

特点

  1. 只能用于对等比较，不能进行范围查询

  2. 无法利用索引完成排序操作

  3. 查询效率高，通常只需要一次检索就可以了

引擎支持

  • 只有 Memory 引擎支持

  • 但是 InnoDB 具有自适应 Hash 功能....

2.4. 为什么 InnoDB 存储引擎选择使用 B+tree 索引结构

相比 Binary-Tree 或者红黑树

  • B+ 在数据量一样的时候，层级更低

  • 利于遍历啊

相比 B-Tree

  • B+ 采用双向链表

  • 只有叶子存储数据

相比 Hash

  • B+ 支持范围查询

3. 索引分类

主键索引

3.1. 主键索引

针对表中主键建立索引

默认自动创建，只能有一个

PRIMARY 关键字

3.2. 唯一索引

避免同一个表中某数据列中的值重复

UNIQUE 关键字

3.3. 常规索引

常规索引

3.4. 全文索引

查找文本关键词

3.5. InnoDB 中

聚集索引

Clustered Index

数据存储和索引放到一块，索引结构的叶子节点保存了行数据

必须有，而且只能有一个

  • 如果有主键 PRIMARY KEY 主键索引就是聚集索引

  • 如果没有主键，使用第一个 UNIQUE 索引作为聚集索引

  • 如果都没有，InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

二级索引 - 辅助索引

Secondary Index

数据和索引分来存储

可以存在多个

SELECT * FROM USER WHERE ID = 10;
SELECT * FROM USER WHERE NAME = 'ARY';

ID 为主键，第一条性能更快...

4. 索引语法

4.1. 创建索引

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...

CREATE

4.2. 查看索引

SHOW INDEX FROM table_name;

SHOW

4.3. 删除索引

DROP INDEX index_name ON table_name;

DROP

4.4. 案例练习...

沟槽的每给建表代码...我虚空练习得了...

联合索引

顺序是一定的！！！

5. SQL 性能分析

5.1. SQL 执行频率

了解 SQL 的数据库的执行

CRUD 的频率？

只有知道了数据库是查询多还是插入删除多才有优化方向

5.2. 慢查询日志

记录所有执行时间超过指定参数（long_query_time）的所有 SQL 语句的日志。

默认是 off？

在 Linux 下的配置文件中自行配置...

考虑针对日志里的操作进行优化。

5.3. Profile 详情

SHOW PROFILES 能够在做优化时，帮助我们了解时间都耗费到哪里了

SELECT @@HAVE_PROFILING;

SELECT @@PROFILING;

SET PROFILING=1;

查看结果：

SHOW PROFILES;

查看详细结果：

SHOW PROFILE [CPU] FOR QUERY 1;

5.4. EXPLAIN 执行计划

任意 SELECT 语句之前加上 EXPLAIN 就可以查看本次执行的信息

id 代表的并不是主键，而是执行顺序！

如果 id 相同：从上到下

如果 id 不同：从大到小...

各字段

select_type

type 性能从好到差 NULL - syste - const - eq_ref - ref - range - index - all(全表扫描)...尽量往前优化

possible_keys可能用到的索引

key实际用到的索引

key_len索引中的长度

row执行查询的长度

filtered返回结果行数占用读取行数的百分比，这个百分比越高越好...

5.5. 例子

学生 和 课程

STUDENT 表和 COURSE 表 可以通过 一个中间表（选课表）来进行链接...

多对多，需要一个中间表来维护他们的关系

内连接？

内连接的语句结合

内查询 - 内查询先执行最内部的表

表的返回结果其实就是一些数据，把数据抽象成为语句就可以完成书写...

6. 索引使用

正确使用更重要

6.1. 验证索引效率

通过构建索引，对某些原本很慢的查询提效非常明显！！！

6.2. 最左前缀法则

如果索引了多列（联合查询）

比如有三个字段的联合索引

进行查询时

  • 首字段不能省略

  • 中间不能省略某一列...要么就只要首字段...

  • 这样都会走索引

不满足最左前缀法则就会走全表扫描！

从最左列开始，中间不得跳过某一列...

最左列必须存在！和放的位置没关系

6.3. 范围查询

联合索引中。出现范围查询，右边的索引会失效...

使用符合索引，尽量使用 >= 而不是 >

6.4. 索引列运算

不要进行列运算操作，否则索引会失效

譬如，进行函数运算的时候...

6.5. 字符串不加单引号

字符串不加单引号？查询不会出现一点问题，也能查询出结果...

索引会失效...

6.6. 模糊查询

加上 % 如果是头部进行模糊匹配，索引会失效。

如果是尾部，则不会失效

大数据量下尽量规避...

6.7. or 连接的条件

用 or 分割开的条件，如果 or 前有索引，后面没有索引，那么涉及到的索引都不会用到......

...这时候简单粗暴，没索引的 - 创建一个索引就好了...

如果更改 or 连接的关键字...

6.8. 数据分布影响

如果使用索引比全表扫描还慢...那么会进行全表扫描...只要走索引的话大于半张表，就会走 ALL

否则走 索引

6.9. SQL 提示

你不知道要不要用到索引...SQL 会自己帮你用上索引...

possible_keys 就是可以用的索引，SQL 内部会自己选择一个实际用到的 key

但是你也可以选择

  • use index

    • 建议要用哪个索引

    • SQL 会自己权衡，不一定会采纳

  • ignore index

    • 不要用哪个索引

  • force index

    • 强制要用哪个索引

6.10. 覆盖索引

一条语句查询返回的列，在索引中...

尽量减少 * 的使用，而是使用 实际的字段名...

using where using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

using index condition：查找使用了索引，但是需要回表查询数据

总的来说：你查的东西就是你要返回的东西...不需要再复盘？

索引分为聚集索引 id 和辅助索引 name

覆盖索引是最佳实践！

6.11. 前缀索引

如果字段类型是 char 或者 varchar 的时候，索引需要很长的字符串，让索引变得很大，查询时浪费大量的磁盘 IO，影响查询效率...

create index idx_xxxx on table_name(column(n));

可以选取字符串的一部分前缀建立索引，节约索引空间

前缀长度？

根据索引选择性来决定，索引选择性最好是不重复...也就是 all/distinct == 1

通过不断更换 substring 的参数来判断选择性！

不断平衡选择性和前缀长度...

辅助索引：

  • 辅助索引是表的额外索引

  • 它不改变表中数据的物理存储顺序

  • 辅助索引的叶子节点存储的是指向数据行的指针（或主键值）

聚集索引

  • 聚集索引决定了表中数据的物理存储顺序

  • 表中的数据行存储在索引的叶子节点上

  • 这意味着表数据和索引数据是存储在一起的

6.12. 单列索引 与 联合索引

单列索引：一个索引只包含一个列

联合索引：一个索引包含多个列

业务场景 推荐联合索引（如果存在多个查询条件）

如果单列和联合同时存在

SQL 会进行评估

联合索引

如果使用得当，完全可以避免回表查询

而且联合索引的字段顺序也是需要考虑的！！！

7. 索引设计原则

  1. 数据量大，且查询频繁的表

  2. 针对 where, order by, group by 操作的字段作为索引

  3. 选择区分度高的列作为索引，尽量建立唯一索引，越唯一，越高效...

  4. 如果是字符串类型，建立前缀索引

  5. 尽量使用联合前缀索引，减少单列索引，尽量使用覆盖索引

  6. 控制索引数量，这不是多多益善的！

  7. 索引不能存储 NULL 值，创建表的时候要使用 NOT NULL 来约束它，优化器会考虑选择哪些列来进行索引

Previous02. Functions, Constraints, Multi-table Queries and TransactionsNext05 - SQL Optimization

Last updated