2023-08-19 06:37

关注

【MySQL】MySQL索引及调优

MySQL索引及调优

常见索引概念

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子结点），也就是所谓的 索引即数据，数据即索引。

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
- 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
- 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。
B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个**聚簇索引**的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX 语句去创建， InnDB 存储引擎会 自动 的为我们创建聚簇索引。

优点：

数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。

缺点：

插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个**自增的ID列为主键**
更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

非聚簇索引

特点

非聚簇索引相对于聚簇索引，B+树的叶子节点不是存储的不是完整的用户记录，而只是索引列+主键
目录项记录中也不是主键+页号，而是索引列+页号的搭配。

💡 **概念：回表**

我们根据这个以索引列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就是根据索引列的值查询一条完整的用户记录需要使用到 2 棵B+树！

小结：聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别：

聚簇索引的**叶子节点存储的就是我们的数据记录 , 非聚簇索引的叶子节点存储的是数据位置**。非聚簇索引不会影响数据表的物理存储顺序。
一个表**只能有一个聚簇索引 ，因为只能有一种排序存储的方式，但可以有多个非聚簇索引**，也就是多个索引目录提供数据检索。
使用聚簇索引的时候，数据的**查询效率高**，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

索引的分类

💡 MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。

按照作用字段个数进行划分，分成单列索引和联合索引。

索引的设计原则

💡 索引不是越多越好。

索引需要占用磁盘空间，索引越多，占用空间越大。
索引会影响 INSERT 、DELETE、UPDATE等语句的性能，当表中的数据发生改变时，索引也会进行相应的调整和更新。
优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySOL优化器生成执行计划时间，降低查询性能。

适合创建索引的场景

1.字段的数值具有唯一性的限制

💡 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2.频繁作为 WHERE 查询条件的字段

3.经常 GROUP BY 和 ORDER BY 的列

4.UPDATE、DELETE 的 WHERE 条件列

5.DISTINCT 字段需要创建索引

6.多表 JOIN 连接操作时，创建索引注意事项

7.使用列的类型小的创建索引

💡 我们这里所说的类型大小指的就是该类型表示的数据范围的大小。我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT `BIGINT` 等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要用`BIGINT`，能使用 `MEDIUMINT` 就不要使用 `INT` 。这是因为:

数据类型越小，在查询时进行的比较操作越快;
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/0。

8.使用字符串前缀创建索引

💡 假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题: B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，`在索引中占用的存储空间越大。` 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间我们可以通过截取字段的前面一部分内容建立索引，这个就叫 `前缀索引`。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既`节约空间` ，又减`少了字符串的比较时间` ，还大体能解决排序的问题。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

9.区分度高(散列性高)的列适合作为索引

💡 **`列的基数`** 指的是某一列中不重复数据的个数，比方说某个列包含值 **`2, 5, 8, 2, 5, 8, 2, 5, 8`**，虽然有**`9`**条记录，但该列的基数却是3。也就是说**在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。**这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列简历索引，为基数太小的列的简历索引效果可能不好。

可以使用公式**select count(distinct a) / count(*) from t1** 计算区分度，越接近1越好，一般超过33%就算比较高效的索引了。

扩展：联合索引把区分度搞(散列性高)的列放在前面。

10. 使用最频繁的列放到联合索引的左侧

💡 这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

不适合创建索引的场景

1.在where中使用不到的字段，不要设置索引

2.数据量小的表最好不要使用索引

💡 如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引 **`对查询效率的影响并不大`**。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

3.有大量重复数据的列上不要建立索引

💡 在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的"性别"字段上只有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会**`严重降低数据更新速度`**。

4.避免对经常更新的表创建过多的索引

💡 第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却降低更新表的速度。

5.不建议用无序的值作为索引

6.删除不再使用或者很少使用的索引

7.不要定义夯余或重复的索引

性能分析工具

查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Slow_queries：慢查询的次数。
Innodb_rows_read：Select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数
Com_select：查询操作的次数。
Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
Com_update：更新操作的次数。
Com_delete：删除操作的次数。

若查询MySQL服务器的连接次数，则可以执行如下语句:

SHOW STATUS LIKE 'Connections';

若查询服务器工作时间，则可以执行如下语句:

SHOW STATUS LIKE 'Uptime';

若查询MySQL服务器的慢查询次数，则可以执行如下语句:

SHOW STATUS LIKE 'Slow_queries';

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

再比如，如下的指令可以查看相关的指令情况：

SHOW STATUS LIKE 'Innodb_rows_%';

统计SQL的查询成本: `last_query_cost`

一条SQL查询语句在执行前需要查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL 语句所需要读取的读页的数量。

定位执行慢的 SQL：慢查询日志

💡 MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 `long-query_time` 值的SQL，则会被记录到慢查询日志中。 long_query_time的默认值为 10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。它的主要作用是，帮助我们发现那些执行时间特别长的 SOL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进全面分析，默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。**如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。** 慢查询日志支持将日志记录写入文件。

查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

分析查询语句 EXPLANIN

💡 定位了查询慢的 SQL 之后，我们就可以使用 `EXPLAIN` 或 `DESCRIBE` 做针对性的分析查询语句。 `DESCRIBE` 同 `EXPLAIN`。

EXPLAIN

索引优化与查询优化

💡 概览：

索引失效、没有充分利用到索引——建立索引
关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化
服务器调优及各个参数设置（缓冲、线程数等）——调整my.cnf
数据过多——分库分表

避免索引失效情况

1.计算、函数、类型转换（显式/隐式）

2.范围条件右边的索引列失效

3.不等于条件的索引失效

4.is null可以使用索引，is not null索引失效

5.like以通配符%开头索引失效

6.OR 前后存在非索引的列，索引失效

💡 **OR前后的两个条件中的列都是索引时，查询中才使用索引。**

7.编码字符集不同，在转换后会造成索引失效

💡 **数据库和表的字符集统一使用utf8mb4**

SQL优化

Join连接时被驱动表添加索引。
排序字段加索引，避免FileSort排序，多使用Index排序。
分组优化：
- where效率高于having，能写在where限定的条件就不要写在having中了。
- group by 先排序再分组，遵照索引建的最佳左前缀法则
- 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
分页优化：尽量在主键自增的表上进行分页。
优先考虑覆盖索引
可以考虑个长字段添加前缀索引。
合理选用exist与in。
- 小大exist
- 大小in
避免* 通配符
Limit 1

范式

第一范式(1st NF)：第一范式主要确保数据库中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为**不可再次拆分**的最小数据单元。
第二范式(2st NF)：第二范式要求，在满足第一范式的基础上，还要满足数据库里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。
第三范式(1st NF)：要求数据表中的所有非主键字段不能依赖于其他非主键字段，非主属性必须相互独立。
**BCNF（巴斯范式）：**若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BC范式。