MySQL索引
|
276
|
0
|
未分类

3191 字
|
17 分钟

简介：

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

全表扫描性能极低。 建立索引，就是建立一个二叉树并维护。

有索引的位置，查询效率非常高效。

提高数据检索的效率，降低数据库IO压力，用空间换时间。 虽然查询效率很高，但如果进行更删改操作时，会产生额外维护树的成本。

索引结构：

在存储引擎层实现，不同存储引擎有不同的结构：

image-20240904172446832

B+树是最常见的索引类型，大部分引擎都支持B+树索引。 B+tree索引对于三个常用存储引擎都是支持的（InnoDB、MyISAM、Memory）

B树：

B树为存取数据设计的多路平衡搜索树。B树类似于红黑树，但B树在降低磁盘I/O方面更好一些。许多数据库都用B树或者B树变种来存储数据。

B树可以在O(lg(n))时间内完成一些动态集合的操作。

B树根据最小度数t≥2，对包含n个关键字，高度为h的B树有：

image-20240904191844336

层级相较于红黑树少，更适合于大数据量IO $$ h≤log _{t} \frac{n+1}{2} $$

B+树：

最常见的B树变种之一，它把所有卫星数据都存储在叶节点中，内部结点只存放关键字和孩子指针，因此最大化了内部结点的分支因子。

MySQL对B+树进行了优化，增加了一个指向相邻叶子节点的链表指针，形成了有序指针的B+Tree，提高了区间访问的性能。

image-20240904194346540

Hash：

哈希索引采用hash算法，将key-value换算成新的hash值，映射到对应的槽位上，存储在hash表。出现hash冲突时，将hash表的value改造成链表。

Hash索引查询效率高，理想情况的时间复杂度为O(1)。

Hash索引只能用于匹配（=），不支持范围查询（between, < , > ) 且Hash索引无法完成排序操作。

除了Memory外，InnoDB有自适应hash索引的功能。

索引分类：

聚集索引和二级索引：

InnoDB中： 聚集索引：将数据与索引共同存储，索引结构的叶子节点保存了行数据。 二级索引：数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

聚集索引有且仅有一个 二级索引可以存在多个

聚集索引选取规则： 如果存在主键，主键索引就是聚集索引。 如果不存在主键，将使用第一个唯一索引（UNIQUE）作为聚集索引。 如果没有合适的唯一索引，InnoDB会自动生成一个隐藏的rowid作为聚集索引

聚集索引：叶节点存放行数据。

image-20240904194346540

二级索引：叶节点存放主键

image-20240904194523710

用二级索引查到的主键值，在聚集索引中查询行数据值，这种操作叫回表查询。 大部分SQL优化，都依赖于聚集索引和二级索引结构和原理的理解。

索引操作语法：

创建索引：

// 不指定索引类型 默认创建常规索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON [table_name] (col_name...)

查看索引：

SHOW INDEX FROM table_name;

删除索引：

DROP INDEX [index_name] ON [table_name];

例：

name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

phone手机号字段的值，非空，唯一。为该段建立唯一索引。

job，age 设联合索引。

// 联合索引的字段顺序 
create index [index_name] on [table_name] (字段1，字段2...)

SQL性能分析：

做SQL分析，需要做SQL性能分析。

SQL执行频率：

查看当前服务器状态信息

show [session|global] status;

常用的命令

// 七个下划线
show [session|global] status like 'com_______';
image-20240904203907968

反悔了所有操作的访问频次，为SQL性能优化提供决策基础。

慢查询日志：

慢查询时间记录了所有执行时间超过指定参数long_query_time的所有SQL语句的日志（默认10秒）

数据库的慢查询日志，默认没有开启，需要在MySQL的配置文件(如：/etc/mysql/my.cnf)中添加以下配置信息：

#分配一个配置组
[mysqld]
#开启MySQL慢日志查询开关
slow_query_log = 1
#设置慢日志时间 SQL语句执行时间超过time，就会被记录到慢查询日志中
long_query_time = time
#设置慢日志存储位置和慢日志文件名
slow_query_log_file = /var/lib/mysql/xxx-slow.log

输入 show variables like ‘slow_query_log’ 查看当前慢日志开启情况

image-20240904212051965

日志文件默认生成在 /var/lib/mysql 目录下

用tail -f xxx-slow.log 来监视文件变动，可以测试慢查询日志，来定位执行效率比较低的SQL。

image-20240905162241638

profile详情：

如果我们想关注比慢查询日志略慢的SQL语句

show profiles能在做SQL优化前，让我们知道时间

// 查看是否支持profiles
SELECT @@have_profiling;
// 查看是否开启profiling
SELECT @@profiling;
// 开启profiling
set profiling = 1;
image-20240904212632560

说明支持profile，但profiling默认是关闭的，需要我们手动的打开。

image-20240904212752806

image-20240904212847209

通过show profiles命令可以查看所有命令的耗时情况。

// 查询profiles
show profiles;
// 查询一条命令的具体耗时 profile没有s
// 加入cpu后 可以查看每个阶段的cpu占用情况
show profile [cpu] for query [Query_ID];
image-20240904213152056

同时也可以查询一条命令的具体耗时：

image-20240904213402224

explain执行计划：

EXPLAIN 或者 DESC命令获取MySQL如何

EXPLAIN和DESC效果是一样的

image-20240904214316209

各字段含义：

Id： 表示查询的表顺序，表示了操作表的执行顺序。 （id相同，执行顺序从上到下。id不同，值越大越先执行。）

select_type： 表示SELECT的类型，常见的取值有 Simple （简单表，即不用表连接或子查询） Primary （主查询，即最外层的查询） Union （UNION中第一个之后的查询语句） Subquery （SELECT/WHERE 之后的子查询）

type： 表示连接类型，性能由高到低的连接类型为 NULL , system , const , eq_ref , ref , range , index , all NULL在一般业务操作中一般不太可能出现。 all代表全表扫描，是性能最差的查表方式。 其余各连接类型现查即可。

possible_key： 显示可能应用在这张表上的索引。

Key： 实际使用的索引，为NULL时没有使用索引

Key_len： 表示索引中使用的字节数，为索引字段最大可能长度，并非实际使用长度。 在不损失精确性的前提下，长度越短越好。

rows： MySQL预估必需要执行查询的行数，在InnoDB引擎的表中是一个估计值。

filtered： 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

Extra： 额外的值，前面没有展示出来的值会在此显示。

type，possible_keys，key，key_len，是最重要的返回值。

索引使用：

验证索引效率：

使用主键查询和非主键查询时，性能会有很大的差异。 主键默认有主键索引，其他key需要自行创建索引。

创建索引需要构建一个B+树的数据结构。需要较长的时间。

未创建索引：

在数据量为1e7时，无索引查询时间约为8.92s

image-20240905162434927

已创建索引：

创建索引后，查询时间为0.01s。验证了索引查询的效率。

image-20240905162549795

同时用explain语句查询SQL执行计划：

image-20240905162805558

使用的key为索引。

使用规则：

最左前缀法则：

最左前缀法则指的是，查询从创建索引时的最左列开始，并且不跳过索引中的列 如果索引了多列（联合索引），要遵循最左前缀法则。

在创建联合索引时，假设字段的顺序为 (i1,i2,i3)

// SQL会使用索引
select * from t1 where i1 = 'a' and i2 = 'a' and i3 = 'a';
select * from t1 where i1 = 'a' and i2 = 'a';
select * from t1 where i1 = 'a';

// 下面的SQL索引会失效
select * from t1 where i2 = 'a',i3 = 'a';
select * from t1 where i2 = 'a';
select * from t1 where i3 = 'a';

// 下面的SQL，索引会部分失效
select * from t1 where i1 = 'a' and i3 = 'a'; // i3字段的索引会失效

MySQL语法优化器会为我们优化。

如果使用范围查询（>,<），使用了范围查询右侧（后续列）的字段索引失效。

覆盖索引：

查询所有字段可以将所有索引都用上的情况。（？） 尽量使用覆盖索引，减少select * 的使用。

如果使用二级索引查找时，有二级索引中不存在的字段。 则还需要回表查询，在聚集索引中查找整列以获取字段的值。

联合索引 可以解决多字段查询时回表查询的问题。

前缀索引：

当字段类型为字符串(varchat , text等)时，有时候需要索引很长的字符串，会让索引变得很大。 查询时会造成大量磁盘I/O,影响查询效率。 使用模拟哈希索引，反而可能导致索引变得更长。

此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，提高索引效率（字典树？）

语法：

// 根据该字段的前缀长度n 设置索引，只记录该字段的前n位
CREATE index idx_xxx on table_name(字段(n))

前缀长度 可以根据索引的选择性来决定。 求取索引的选择性，需要知道字段不重复的值，和表中记录行的总数。

// 字段去重后不重复的总计数 C
select count(distinct 字段) from t1;
// 表中行总数 T
select count(*) from t1;

// 截取前n个值的索引的选择性为 C/T in n;
select count(distinct Left(X,n))/count(*) from T1;

可以得到不同前缀长度时的索引选择性。当性能提升不明显时，选择一个较短，选择性够高的截取度作为前缀长度即可。

image-20240905174228333

引入概念 索引的选择性
记c为字段不重复的值，T为数据表的记录总数
索引的选择性为 c/T （1/T ~1)
索引的选择性越高，查询效率越高，因为选择性高可以MySQL在查找时过滤掉更多的行。
唯一索引的选择性为1时，性能是最好的。

单列索引和联合索引：

单列索引：一个索引只包含单个列 容易涉及回表查询。

联合索引：一个索引包含多个列，叶节点存储卫星数据 如果存在多个查询条件，考虑针对查询字段建立联合索引。 来避免回表查询的额外性能开销。

索引失效规则：

字符串：

格式不规范，条件查询字符串类型字段时不加单引号。涉及类型转换，导致索引失效。 尾部模糊匹配，索引不会失效。头部模糊匹配，索引会失效。

// 索引生效
select * from t1 where phone = '123';
select * from t1 where phone like '12%';
// 索引失效
select * from t1 where phone = 123;
select * from t1 where phone = '%23';

or 连接条件：

用 | （or） 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，涉及的索引都不会被用到

select * from t1 where indexed = 1 or notindexed = 1;
image-20240905165247414

数据分布影响：

如果MySQL评估使用索引比全表扫描更慢，则不会使用索引。 在rows一栏里，如果返回的结果预估为一张表的大部分，索引扫描加上随机磁盘访问的代价比顺序读取整张表的代价要高，优化器就会执行全表扫描，就会出现索引失效的情况。

MySQL使用基于成本的优化器，它会预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O
MySQL依赖存储引擎提供的统计信息来评估成本，而InnoDB因为其MVCC的架构，并不能维护一个数据表行数的精确统计信息。
所以MySQL的最优不一定是最理性的最优情况。
---《高性能MySQL（第3版）》 6.4.3查询优化处理

SQL提示：

如果有多个索引，MySQL在默认情况下会用优化器选择一个索引使用。 通过在SQL语句中加入一些人为的提示，来指定索引的使用和忽视。

SQL提示，是优化数据库的一个重要手段。

分为使用索引，忽视索引和强制使用索引 在表名后面添加相关的语句。

use index: // 加入索引（MySQL可能不用）
select * from t1 use index(index) where ...;

ignore index: // 忽略索引
select * from t1 ignore index(index) where ...;

force index: // 强制使用索引
select * from t1 force index(index) where ...;

索引设计原则：

image-20240905174957302

总结：

索引是一种数据结构，用以高效获取数据 常用的索引结构是 B+树和Hash B+树适合数据的持久化，Hash适合快速匹配。

索引的类别： 主键索引、唯一索引、常规索引、全文索引 聚集索引、二级索引

索引语法：

​ create [unique|fulltext] INDEX index_name ON [table_name] (col_name…); ​ show index from [table_name]; ​ drop index [index_name] on [table_name];

SQL性能分析： 执行频次 慢查询日志 profile explain 其中最常用的是explain

索引使用： 联合索引 索引失效（函数运算、字符串转换、尾部模糊匹配、OR索引） SQL提示 覆盖索引（一种创建索引的思想，避免回表查询） 前缀索引 单列/联合索引

索引设计原则： 在哪建，建立什么索引。

上一篇
下一篇