MySQL高级

MySQL高级

1. 存储引擎

MySQL存储引擎决定了数据在磁盘上的存储方式和访问方式。不同的存储引擎实现了不同的存储和检索算法，因此它们在处理和管理数据的方式上存在差异。也就是说，存储引擎决定了数据在磁盘上的存储方式和访问方式。

CREATE TABLE `t_user` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

当我们在创建表时，不指定存储引擎，mysql默认使用InnoDB存储引擎。

MySQL常见的存储引擎包括InnoDB、MyISAM、Memory、Archive等。每个存储引擎都有自己的特点和适用场景。

  • InnoDB引擎支持事务和行级锁定，适用于需要高并发读写的应用。

  • MyISAM引擎不支持事务，但适用于读操作较多的应用。

  • Memory引擎数据全部存储在内存中，适用于对读写速度要求很高的应用。

选择适合的存储引擎可以提高MySQL的性能和效率，并且根据应用需求来合理选择存储引擎可以提供更好的数据管理和查询功能。

使用如下命令，查看当前版本数据库支持哪些存储引擎。

show engines;

image-20240706164133299

在创建表时，我们可以指定想要使用的存储引擎，如

create table t_user2(
	id int
)engine=MyISAM;

修改存储引擎，使用如下语句：

alter table 表名 engine=MyISAM;

我们一般很少会去修改存储引擎。

在修改存储引擎之前，需要考虑以下几点：

  • 修改存储引擎可能需要执行复制表的操作，因此可能会造成数据的丢失或者不可用，确保在执行修改之前备份数据。
  • 不是所有的存储引擎都支持相同的功能，确保选择的新存储引擎支持应用程序所需的功能。
  • 修改表的存储引擎可能会影响到现有的应用程序和查询。确保修改之前评估和测试所有的影响。
  • ALTER TABLE语句可能需要适当的权限才能执行。确保拥有足够的权限来执行修改存储引擎的操作。

InnoDB

  • MySQL默认的事务型存储引擎
  • 支持ACID事务
  • 具有较好的并发性能和数据完整性
  • 支持行级锁定
  • 适用于大多数应用场景，尤其是需要事务支持的应用

MyISAM

  • MySQL早期版本中常用的存储引擎
  • 支持全文索引和表级锁定
  • 不支持事务
  • 由于其简单性和高性能，在某些特定的应用场景中会得到广泛应用，如读密集的应用。

MEMORY

  • 称为HEAP，是将表存储在内存中的存储引擎
  • 具有非常高的读写性能，但数据会在服务器重启时丢失
  • 适用于需要快速读写的临时数据集、缓存和临时表等场景

CSV

  • 将数据以纯文本格式存储的存储引擎
  • 适用于需要处理和导入/导出CSV格式数据的场景

ARCHIVE

  • 将数据高效的进行压缩和存储的存储引擎
  • 适用于需要长期存储大量历史数据且不经常查询的场景

2. 索引

2.1 什么是索引

索引是一种能够提高检索（查询）效率的提前排好序的数据结构。例如：书的目录就是一种索引机制。索引是解决SQL慢查询的一种方式。

比如我们有一个student表，student表中有一个name字段，name字段中有很多数据。为了name字段上的查找效率，我们就可以为name字段创建索引。相当于时name字段数据的一个目录。

MySQL中索引采用的数据结构为B+树。

2.2 索引的创建和删除

主键字段会自动添加索引，不需要程序员干涉，主键字段上的索引被称为逐渐索引。

unique约束的字段也会自动添加索引，不需要程序员干涉，这种字段上添加的索引称为唯一索引。

给指定的字段添加索引

建表时添加索引

create table 表名(
	...
  name varchar(10),
  ...
  index idx_name(name)  # 索引名称(字段名)
);

如果表已经存在了，后期给字段添加索引

alter table 表名 add index inx_name(name);  # 索引名称(字段名)

或者使用另外一种方式

create index 索引名 on 表名(字段名);

删除指定字段上的索引

alter table 表名 drop index 索引名;

查看某张表上添加了哪些索引

show index from 表名;

2.3 索引的分类

不同的存储引擎有不同的索引类型和实现：

  • 按照数据结构分类：

    B+树索引（MySQL的InnoDB存储引擎采用的就是这种索引）采用B+树的数据结构

    Hash索引（仅memory存储引擎支持）采用哈希表的数据结构

  • 按照物理存储分类：

    聚集索引：索引和表中数据在一起，数据存储的时候就是按照索引顺序存储的，一张表只能有一个聚集索引。

    非聚集索引：索引和表中数据是分开的，索引是独立于表空间的，一张表可以有多个非聚集索引。

  • 按字段特性分类：

    主键索引

    唯一索引

    普通索引

    全文索引（fulltext：仅InnoDB和MyISAM存储引擎支持），要求字段的类型都是文本内容才可以使用全文索引。

  • 按照字段个数分类：

    单列索引、联合索引（也叫复合索引、组合索引）

2.4 索引底层原理

MySQL索引底层使用的是B+树。

排序二叉树、红黑树、B树、B+树，树的高度不同，树的高度越低，性能越高，这是因为每一个节点都是一次I/O。

排序二叉树

左小右大。

中序遍历（左、根、右）则为从小到大的顺序。

image-20240706211253162

现在我们有如上一张表，假设使用二叉树的方式给id字段创建索引。

image-20240706211427854

这时，如果要找id=10的数据，需要的IO次数为4次，效率显著提升了。

但是二叉树存在极端的情况，如下，这种情况下，效率较低。这样就变成单向链表了，树的高度会非常高。所以MySQL没有使用这种数据结构作为索引。

image-20240706212038702

红黑树（自平衡二叉树）

为了解决二叉树这种极端的问题，所以引入了红黑树，也叫做自平衡二叉树。

通过自旋平衡规则进行旋转，子节点会自动分叉为2个分支，从而减少树的高度，当数据有序插入时比二叉树数据检索性能更好。

例如，有如下数据

image-20240706212908592

给id字段添加索引，假设使用了红黑树作为数据结构，那么结果为：

image-20240706213349925

如果查找id=10的数据，磁盘IO次数为5次，效率比普通的二叉树要高一些。

但是如果数据量庞大，例如500万条数据，也会导致树的高度很高，磁盘IO次数仍然很多，查询效率也会比较低。

因此MySQL并没有使用红黑树这种数据结构作为索引。

B Trees（B树）

B Trees就是平衡树。

B树首先是一个自平衡的。

B树每个节点下的子节点数量>2。

B树每个节点中也不是存储单个数据，可以存储多个数据。

B树又称为多路平衡查找树。

B树分支的数量不是2，是大于2，具体是多少分支，由阶决定。例如：

  • 3阶的B树，一个节点下最多有3个子节点，每个节点中最多有2个数据。
  • 4阶的B树，一个节点下最多有4个子节点，每个节点中最多有3个数据。
  • ……

MySQL采用了16阶。

采用B树时，我们会发现，相同的数据量，B树的高度更低，磁盘IO次数更少。

例如，id为1，2，3，4，5，6，7，8，9，10，11

image-20240706214905795

假设id字段添加了索引，并采用了B树作为数据结构，查找id=10的数据，只需要2次的IO。

image-20240706215241729

在B树中，每个节点不仅存储了索引值，还存储该索引值对应的数据行。

并且每个节点中的p1，p2，p3是指向下一个节点的指针。

B树数据结构存在的缺点是：不适合做区间查找，对于区间查找效率较低。假设要查id在[3~7]之间的，需要查找的是3，4，5，6，7。那么查这每个索引值都需要从头节点开始。

因此MySQL使用了B+ Trees解决了这个问题。

B+ Trees（B+树）

B+树将数据都存储在叶子节点中。并且叶子节点之间使用链表连接，这样很适合范围查询。

B+树非叶子节点上只有索引值，没有数据，所以非叶子节点可以存储更多的索引值，这样B+树更矮更胖，提高检索效率。

假设有这样一张表：

image-20240706220159777

B+树方式存储如下：

image-20240706220254816

经典面试题：mysql为什么选择B+树作为索引数据结构，而不是B树？

  • 非叶子节点上可以存储更多的键值，阶数可以更大，更矮更胖，磁盘IO次数更少，数据查询效率更高。
  • 所有数据都是有序存储在叶子节点上，让范围查找、分组查找效率更高。
  • 数据页之间、数据记录之间采用链表链接，让升序降序更加方便操作。

经典面试题：如果一张表没有主键索引，那还会创建B+树吗？

当一张表没有主键索引时，默认会使用一个隐藏的内置的聚集索引。这个聚集索引是基于表的物理存储顺序构建的，通常是使用B+树来实现的。

2.5 其他索引及相关调优

哈希索引

支持哈希索引的存储引擎有：

  • InnoDB

    不支持手动创建哈希索引，系统会自动维护一个自适应的哈希索引。

    对于InnoDB来说，即使手动置顶了某字段采用哈希索引，最终show index from 表名的时候，还是B Tree。

  • Memory

    仅支持哈希索引。

哈希索引底层的数据结构就是哈希表。一个数组，数组中每个元素是链表。和java和hashmap一样。哈希表中每个元素都是key value结构。key存储索引值，value存储行指针。

原理如下：

image-20240707215342063

如果name字段上添加了Hash索引idx_name。

Hash索引为：

image-20240707215432115

检索原理：假设name='孙行者'。通过哈希算法将孙行者转换为数组下标，通过下标找链表，在链表上遍历找到孙行者的行指针。

注意：

不同的字符串，经过哈希算法得到的数组下标可能相同，这叫哈希碰撞/哈希冲突。不过，好的哈希算法应该具有很低的碰撞概率。常用的哈希算法如MD5，SHA-1，SHA-256等都被设计为尽可能减少碰撞的发生。

Hash索引优点：只能用在等值比较中，效率很高。例如：name='孙悟空'。

Hash索引缺点：不支持排序，不支持范围查找。

聚集索引和非聚集索引

按照数据的物理存储方式不同，可以将索引分为聚集索引（聚簇索引）和非聚集索引（非聚簇索引）。

存储引擎是InnoDB的，主键上的索引属于聚集索引。

存储引擎是MyISAM的，任意字段上的索引都是非聚集索引。

InnoDB的物理存储方式：当一张表t_user，并使用InnoDB存储引擎时，会在硬盘上生产这样一个文件：

  • t_user.idb（InnoDB data表索引+数据）
  • t_user.frm（存储表结构信息）

MyISAM的物理存储方式：当创建一站表t_user，并使用MyiSAM存储引擎时，会在硬盘上生成这样一个文件：

  • t_user.MYD（表数据）
  • t_user.MYI（表索引）
  • t_user.frm（表结构）

注意：

从MySQL8.0开始，不再城市frm文件了，引入数据字典，用数据字典来统一存储表结构信息，例如

  • information_schema.TABLES（表包含了数据库中所有表的信息，例如表名、数据库名、引擎类型等）
  • information_schema.COLUMNS（表包含了数据库中所有表的列信息，例如列名、数据类型、默认值等）

聚簇索引的原理图（B+树，叶子节点上存储了索引值+数据）

image-20240706220254816

非聚集索引原理图（B+树，叶子节点上存储了索引值+行指针）

image-20240708094807826

聚簇索引的优点：聚集索引将数据存储在索引树的叶子节点上，可以减少一次查询，因为查询索引树的同时可以同时获取数据。

聚簇索引的缺点：对数据进行修改或删除时需要更新索引树，会增加系统的开销。

二级索引

二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。

有表t_user，id是主键，age是非主键，在age字段上添加的索引称为二级索引。（所有非主键索引都是二级索引）。

image-20240708095224421

二级索引的数据结构：

image-20240708095241671

二级索引的查询原理：

假设查询语句为

select * from t_user where age=30;

image-20240708095406808

为什么会“回表”？因为使用了select *

避免“回表【回到原数据表】”是提高SQL执行效率的手段。例如select id from t_user where age=30;，这样的SQL语句是不需要回表的。

覆盖索引

覆盖索引，是指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。

假设有一个用户表（user）包含以下列：id，username，email，age。

常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能，可以创建一个覆盖索引，包含（username，email）这两列。

创建覆盖索引的SQL语句可以如下：

create index idx_user_username_email on user(username,email);

当执行以下查询时：

select email from user where username='lucy';

MySQL可以直接使用覆盖索引（idx_user_username_email）来获取查询结果，而不必再去查找用户表中的数据。这样可以减少磁盘I/O并提高查询效率。而如果没有覆盖索引，MySQL会先使用（username）来找到匹配的行，然后再回表查询获取邮箱，这个过程会增加更多的磁盘I/O和查询时间。

值得注意的是，覆盖索引的创建需要考虑查询的字段选择。如果查询需要的字段较多，可能需要创建包含更多列的覆盖索引，以满足完全覆盖查询的需要。

索引下推

索到下推 (lndex Condition Pushdown)是一种 MySQL 中的优化方法，它可以将查询中的过滤条件下推到索引层级中处理，从而减少回表次数，优化查询性能。

具体来说，在使用索引下推时，MISQL 会在索引的叶节点层级执行查询的过滤条件，过滤掉无用的索引记录，仅返回符合条件的记录的主键，这样就可以游免查询时回表读取表格的数据行，从而缩短了整个查询过程的时间。

假设有以下表user：

image-20240708101936925

现在我们创建了一个多列索引：（索引下推通常是基于多列索引的）

alter table users add index idx_name_city_age(name,city,age);

假设我们要查询年龄大于30岁，并且所在城市是"London"的用户，假设只给age字段添加了索引，它就不会使用索引下推。传统的查询优化器会将所有满足年龄大于30岁的记录读入内存，然后再根据城市进行筛选。

使用索引下推优化后，在索引范围扫描的过程中，优化器会判断只有在城市列为"London"的情况下，才会将满足年龄大于30岁的记录加载到内存中，这样就可以避免不必要的I0和数据传输，提高查询性能。

单列索引（单一索引）

单列卖引是指对数据库表中的某一列或属性进行索引创建，对亥列进行快速查找和排序操作。单列索引可以加快查询速度，提高数据库的性能。

举个例子，假设我们有一个学生表 (student)，其中有以下几个列: 学生编号 (student.jid)、姓名 (name) 、年龄 (age) 和性别 (gender) 。

如果我们针对学生表的学生编号 (student id)列创建了单列卖引，那么可以快速地根据学生编号进行查询或排序操作，例如，我们可以使用以下SQL语句查询学生编号为123456的学生信息:

select * from student where student_id = 123456;

由于我们对学生编号列建立了单列索引，所以数据库可以直接通过索引快速定位到具有学生编号123456的那一行记录，从而加快查询速度。

复合索引（组合索引）

复合索引(Compound Index) 也称为多列索引 (Multi-Column Index)，是指对数据库表中多个列进行索引创建。

与单列索引不同，复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键，以提高多列条件查询的效率举个例子，假设我们有一个订单表 (Order)，其中包含以下几个列: 订单编号 (OrderlD)、客户编号 (CustomerlD)、订单日期 (OrderDate)和订单金额(OrderAmount).

如果我们为订单表的客户编号和订单日期这两列创建复合索引 (CustomerID,OrderDate)，那么可以在查询时同时根据客户编号和订单日期来快速定位到匹配的记录。

我们可以使用以下SQL语句查询客户编号为123456且订单日期为2021-01-01的订单信息：

select * from order where CustomerID = 123456 and OrderDate = '2021-01-01';

由于我们为客户编号和订单日期创建了复合索引，数据库可以使用这个索引来快速定位到符合条件的记录，从而加快查询速度。复合索引的使用能够提高多列条件查询的效率，但需要注意的是，复合索引的创建和维护可能会增加索引的存储空间和对于写操作的影响。

2.6 索引总结

索引的优点

  • 提高查询性能：通过创建索引，可以大大减少数据库查询的数据量，从而提升查询的速度。
  • 加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率。
  • 减少磁盘IO：索引可以减少磁盘IO的次数，这对于磁盘读写速度较低的场景，尤其重要。

索引的缺点

  • 占据额外的存储空间：索引需要占握额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。
  • 增删改操作的性能损耗：每次对数据表进行插入、更新、删除等操作时，需要更新索引，会导致操作的性能降低。
  • 资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。

何时使用索引？

在以下情况下建议使用索引：

  • 频繁执行查询操作的字段:如果这些字段经常被查询，使用索引可以提高查询的性能，减少查询的时间。
  • 大表: 当表的数据量较大时，使用索引可以快速定位到所需的数据，提高查询效率。
  • 需要排序或者分组的字段:在对字段进行排序或者分组操作时，索引可以减少排序或者分组的时间。
  • 外键关联的字段:在进行表之间的关联查询时，使用索引可以加快关联查询的速度。

在以下情况下不建议使用索引：

  • 频繁执行更新操作的表。如果表经常被更新教据，使用索引可能会降低更新操作的性能，因为每次更新都需要维护索引。
  • 小表：对于数据量较小的表，使用索引可能并不会带来明显的性能提升，反而会占用额外的存储空间。
  • 对于唯一性很差的字段，一般不建议添加索引。当一个字段的唯一性很差时，查询操作基本上需要扫描整个表的大部分数据，如果为这样的字段创建索引，索引的大小可能会比数据本身还大，导致索引的存储空间占用过高，同时也会导致查询操作的性能下降。

总之，索引需要根据具体情况进行使用和权衡，需要考虑到表的大小、查询频率、更新频率以及业务需求等因素。

3. MySQL优化

3.1 MySQL优化手段

  • SQL查询优化：这时最低成本的优化手段，通过优化查询语句、适当添加索引等方式进行，并且效果显著。
  • 库表结构优化：通过规范化设计、优化索引和数据类型等方式进行库表结构优化，需要对数据库结构进行调整和改进。
  • 系统配置优化：根据硬件和操作系统的特点，调整最大连接数、内存管理、IO调度等参数。
  • 硬件优化：升级硬盘、增加内存容量、升级处理器等硬件方面的投入，需要购买和替换硬件设备，成本较高。

3.2 SQL性能分析工具

查看数据库整体情况

通过以下命令可以查看当前数据库在SQL语句执行方面的整体情况：

show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_delete';
show global status like 'Com_update';

show global status like 'Com_______'  # 7个下划线

这些结果反映了从MySQL服务器启动到当前时刻，所有的select查询总数。对于mysql性能优化来说，通过查看Com_select的值可以了解select查询在整个mysql服务期间所占比例情况：

  • 如果Com_select次数过高，表示该数据库是读密集型数据库。
  • 如果Com_select次数很少，同时insert或delete或update次数很高，说明该数据库属于写密集型的数据库。

总之，通过查看Com_select的值，可以了解MySQL服务器的长期执行情况，并在优化查询性能时，帮助我们了解MySQL的性能瓶颈。

慢查询日志

慢查询日志文件可以将查询较慢的SQL语句记录下来，便于我们定位需要调优的select语句。

通过一下命令查看慢查询日志功能是否开启：

show variables like 'slow_query_log';

image-20240708115339892

慢查询日志功能默认是关闭的。需要修改my.cnf文件来开启慢查询日志功能，在my.cnf的[mysqld]后面添加如下配置：

[mysqld]
slow_query_log=1
long_uqery_time=3

修改完成后，需要重启mysql服务才可以生效。

我们再次使用命令查看慢查询日志功能是否开启：

image-20240708141037511

可以看到，慢查询日志功能已经开启了。

那么，这个日志文件存放在哪里了呢？

默认是存放在mysql的根目录下的data目录下。文件以xx-slow.log结尾。

此时，这个文件中还没有任何的日志信息。

当尝试执行一条时长超过3秒的select语句：

select id,name,sleep(4) from t_user3 where name='tom';

此时，我们就可以在慢查询日志中看到：

image-20240708142804404

记录了慢查询的SQL。

show profiles

通过show profiles可以查看执行过的所有的select语句的耗时情况。帮助我们更好的定位问题所在。

查看当前数据库是否支持profile操作：

select @@have_profiling;

image-20240708143231128

查看profiling开关是否打开。

select @@profiling;

image-20240708143329989

datagrip默认关闭profiling，执行如下命令打开profiling。

set profiling=1;

可以执行多条查询语句，然后使用show profiles;来查看当前数据库中执行过的每个SELECT语句的耗时情况。

select id,name from t_user3 where name='tom';
select name from t_user3;
select count(*) from t_user3;
show profiles;

查看某个SQL语句在执行过程中，每个阶段的好事情况：

show profile for query 查询ID;

也可查看sql整个执行过程中，cpu的占用情况：

show profile cpu for query 查询ID;

explain

explain命令可以查看一个查询语句的执行计划，根据执行计划可以做出相应的优化措施。提高执行效率。

explain select * from t_user3 where name='tom';

image-20240708152443260

为了下面功能的测试，我们先准备一些数据，创建三张表

create table student(
    id int primary key,
    name varchar(255),
    age int
);
create table course(
  id int primary key ,
  name varchar(255)
);
create table sc(
    id int primary key ,
    sid int,
    cid int,
    score int,
    foreign key(sid) references student(id),
    foreign key(cid) references course(id)
);

然后插入一些数据。

insert into student values(1,'张三',20),(2,'李四',21),(3,'王五',20);
insert into course values(1,'数据结构'),(2,'语文'),(3,'英语'),(4,'操作系统');
insert into sc values(1,1,1,89),(2,1,2,70),(3,1,4,88),(4,2,1,76),(5,2,3,68),(6,3,2,77),(7,3,4,89);

explain - id

id反映出一条select语句的执行顺序，id越大优先级越高。id相同则按照自上而下的顺序执行。

select s.name,c.name,sc.score
from student s
join sc on s.id=sc.sid
join course c on c.id=sc.cid
where c.name='数据结构';

image-20240708155023891

由于id相同，反映出三张表在执行顺序上属于平等关系，执行时采用，先c，再sc，最后s。

explain - select_type

反映了mysql查询语句的类型。常用值包括：

  • SIMPLE：表示查询中不包含子查询或UNION操作，这种查询通常包括一个表或是最多一个联接（JOIN）。
  • PRIMARY：表示当前查询是一个主查询。（主要的查询）
  • UNION：表示查询中包含UNION操作。
  • SUBQUERY：子查询。
  • DERIVED：派生表（表示查询语句出现在from后面）。

explain - table

反应了这个查询操作的是哪个表。

explain - type

反映了查询表中数据时的访问类型，常见值：

  • NULL：效率最高，一般不可能优化到这个级别，只有查询时没有查询表的时候，访问类型是NULL，例如select 1;。
  • system：通常访问系统表的时候，访问类型是system。一般也很难优化到这个程序。
  • const：根据主键或者唯一性索引查询，索引值是常量值时。
  • eq_ref：根据主键或者唯一性索引查询，索引值不是常量值。
  • ref：使用了非唯一的索引进行查询。
  • range：使用了索引，扫描了索引树的一部分。
  • index：表示使用了索引，但是也需要遍历整个索引树。
  • all：全表扫描。

效率最高的是NULL，效率最低的是all。

explain - possible_keys

这个查询可能会用到的索引。

explain - key

实际用到的索引。

explain - key_len

反应索引在查询中所用的列所占的总字节数。

explain - rows

查询扫描的预估计行数。

explain - Extra

给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好的理解查询执行的过程。

3.3 索引优化

加索引 vs 不加索引

首先将sql脚本初始化到数据库中（初始化100w条记录）。

根据id查询（id时主键，有索引）：

select * from t_vip where id = 900000;

根据name查询（name上没有索引）：

select * from t_vip where name = '4c6494cb';

image-20240708163532441

给name字段添加索引：

alter table t_vip add index idx_name(name);

再次根据name查询（此时name上有索引）：

select * from t_vip where name = '4c6494cb';

image-20240708163511757

明显可见，添加索引后，查询效率得到了提升。

最左前缀原则

假设有这样一张表

create table t_customer(
	id int primary key auto_increment,
	name varchar(255),
	age int,
	gender char(1),
	email varchar(255)
);

添加了这些数据

insert into t_customer values(null,'zhangsan',20,'M','zhangsan@123.com');
insert into t_customer values(null,'lisi',22,'M','lisi@123.com');
insert into t_customer values(null,'wangwu',18,'F','wangwu@123.com');
insert into t_customer values(null,'zhaoliu',22,'F','zhaoliu@123.com');
insert into t_customer values(null,'jack',30,'M','jack@123.com');

添加了这样的复合索引：

create index idx_name_age_gender on t_customer(name,age,gender);

最左前缀原则：当查询语句条件中包含了这个复合索引最左边的列name时，此时索引才会起作用。

验证1：

explain select * from t_customer where name='zhangsan' and age=20 and gender='M';

验证结果：完全使用了索引。

image-20240708164528191

验证2：

explain select * from t_customer where name='zhangsan' and age=20;

验证结果：部分使用索引。

image-20240708164923406

验证3：

explain select * from t_customer where age=20 and gender='M';

验证结果：没有使用索引。

image-20240708164610051

验证4：

explain select * from t_customer where name='zhangsan' and gender='M';

验证结果：部分使用索引。由于中间断开了，所以gender没有使用索引。

image-20240708165117461

注意：

在进行范围查询时，在范围条件右侧的列索引会失效。建议范围查找带上”=“，这样就会解决该问题。

索引失效情况

有这样一张表：

create table t_emp(
	id int primary key auto_increment,
  name varchar(255),
  sal int,
  age char(2)
);

有这样一些数据：

insert into t_emp values(null,'张三',5000,'20');
insert into t_emp values(null,'李飞',4000,'30');
insert into t_emp values(null,'张飞',6000,'40');

有这样一些索引：

create index idx_t_emp_name on t_emp(name);
create index idx_t_emp_sal on t_emp(sal);
create index idx_t_emp_age on t_emp(age);

各种情况：

  • 索引列参加了运算，索引失效。如select * from t_emp where sal*10>50000;

  • 索引列进行模糊查询时，以%开始的，索引失效。select * from t_emp where name like %张;

  • 索引列是字符串类型，但查询时省略了单引号，索引失效。select * from t_emp where age=20;

  • 查询条件中有or，只要有未添加索引的字段，索引失效。select * from t_emp where name='张三' or sal<=5000;（sal无索引）。

  • 当查询的符合条件的记录在表中占比较大，索引失效。

指定索引

当一个字段上既有单列索引，又有复合索引时，我们可以通过以下SQL提示来要求该SQL语句执行时采用哪个索引：

  • use index 索引名称：建议使用该索引，只是建议，底层mysql会根据实际效率来考虑是否使用推荐的索引。
  • ignore index 索引名称：忽略该索引。
  • force index 索引名称：强制使用该索引。

覆盖索引

覆盖索引，是指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。

前缀索引

如果一个字段类型是varchar或者text字段，字段中存储的是文本或者是大文本，直接对这种长文本创建索引，会让索引体积很大，怎么优化呢？

可以将字符串的前几个字符截取下来当做索引来创建，这种索引被称为前缀索引。

单列索引和复合索怎么选择

当查询语句的条件中有多个条件，建议将这几个列创建为复合索引，因为创建单列索引很容易回表查询。

索引创建原则

  • 表数据量庞大，通常超过百万条数据。
  • 经常出现在where，order by，group by后面的字段建议添加索引。
  • 创建索引的字段尽量具有很强的唯一性。
  • 如果字段存储文本，内容较大，一定要创建前缀索引。
  • 尽量使用复合索引，使用单列索引容易回表查询。
  • 如果一个字段中的数据不会为NULL，建议建表时添加not nul约束，这样优化器就知道使用哪个索引列更加有效.7.不要创建太多索引，当对数据进行增删政的时候，索引需要重新重新排序。
  • 如果很少的查询，经常的增制改不建议加索引。

3.4 SQL优化

order by优化

准备数据：

drop table if exists workers;

create table workers(
  id int primary key auto_increment,
  name varchar(255),
  age int,
  sal int
);

insert into workers values(null, '孙悟空', 500, 50000);
insert into workers values(null, '猪八戒', 300, 40000);
insert into workers values(null, '沙和尚', 600, 40000);
insert into workers values(null, '白骨精', 600, 10000);

explain查看一个带有order by的语句时，Extra列会显示：using index 或者 using filesort，区别是什么？

  • using index: 表示使用索引，因为索引是提前排好序的。效率很高。
  • using filesort：表示使用文件排序，这就表示没有走索引，对表中数据进行排序，排序时将硬盘的数据读取到内存当中，在内存当中排好序。这个效率是低的，应避免。

此时name没有添加索引，如果根据name进行排序的话：

explain select id,name from workers order by name;

image-20240708172736330
显然这种方式效率较低。
给name添加索引：

create index idx_workers_name on workers(name);

再根据name排序：

explain select id,name from workers order by name;

image-20240708172742560
这样效率则提升了。

如果要通过age和sal两个字段进行排序，最好给age和sal两个字段添加复合索引，不添加复合索引时：
按照age升序排，如果age相同则按照sal升序

explain select id,age,sal from workers order by age,sal;

image-20240708172750922
这样效率是低的。
给age和sal添加复合索引：

create index idx_workers_age_sal on workers(age, sal);

再按照age升序排，如果age相同则按照sal升序：

explain select id,age,sal from workers order by age,sal;

image-20240708172758826
这样效率提升了。

在B+树上叶子结点上的所有数据默认是按照升序排列的，如果按照age降序，如果age相同则按照sal降序，会走索引吗？

explain select id,age,sal from workers order by age desc,sal desc;

image-20240708172808387
可以看到备注信息是：反向索引扫描，使用了索引。
这样效率也是很高的，因为B+树叶子结点之间采用的是双向指针。可以从左向右（升序），也可以从右向左（降序)。

如果一个升序，一个降序会怎样呢？

explain select id,age,sal from workers order by age asc, sal desc;

image-20240708172817323
可见age使用了索引，但是sal没有使用索引。怎么办呢？可以针对这种排序情况创建对应的索引来解决：

create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);

创建的索引如下：A表示升序，D表示降序。
image-20240708172826921
再次执行：

explain select id,age,sal from workers order by age asc, sal desc;

image-20240708172835369

我们再来看看，对于排序来说是否支持最左前缀法则：

explain select id,age,sal from workers order by sal;

image-20240708172841831
通过测试得知，order by也遵循最左前缀法则。

我们再来看一下未使用覆盖索引会怎样？

explain select * from workers order by age,sal;

image-20240708172848938
通过测试得知，排序也要尽量使用覆盖索引。

order by 优化原则总结：

  1. 排序也要遵循最左前缀法则。
  2. 使用覆盖索引。
  3. 针对不同的排序规则，创建不同索引。（如果所有字段都是升序，或者所有字段都是降序，则不需要创建新的索引）
  4. 如果无法避免filesort，要注意排序缓存的大小，默认缓存大小256KB，可以修改系统变量 sort_buffer_size ：
show variables like 'sort_buffer_size';

image-20240708172720468

group by优化

创建empx表：

create table empx as select * from emp;

job字段上没有索引，根据job进行分组，查看每个工作岗位有多少人：

select job,count(*) from empx group by job;

image-20240708172713959
看看是否走索引了：

explain select job,count(*) from empx group by job;

image-20240708172657831
使用了临时表，效率较低。

给job添加索引：

create index idx_empx_job on empx(job);

再次执行：

explain select job,count(*) from empx group by job;

image-20240708172648772
效率提升了。

我们再来看看group by是否需要遵守最左前缀法则：给deptno和sal添加复合索引

create index idx_empx_deptno_sal on empx(deptno, sal);

根据部门编号分组，查看每个部门人数：

explain select deptno,count(*) from empx group by deptno;

image-20240708172641603
效率很高，因为deptno是复合索引中最左边的字段。
根据sal分组，查看每个工资有多少人：

explain select sal, count(*) from empx group by sal;

image-20240708172635769
使用了临时表，效率较低。
通过测试得知，group by也同样遵循最左前缀法则。

我们再来测试一下，如果将部门编号deptno（复合索引的最左列）添加到where条件中，效率会不会提升：

explain select sal, count(*) from empx where deptno=10 group by sal;

image-20240708172629756
效率有提升的，这说明了，group by确实也遵循最左前缀法则。（where中使用了最左列)

limit优化

数据量特别庞大时，取数据时，越往后效率越低，怎么提升？mysql官方给出的解决方案是：使用覆盖索引+子查询的形式来提升效率。

image-20240708172615649

怎么解决？使用覆盖索引，加子查询
使用覆盖索引：速度有所提升
image-20240708172603513
使用子查询形式取其他列的数据：
image-20240708172553616

主键优化

主键设计原则：

  1. 主键值不要太长，二级索引叶子结点上存储的是主键值，主键值太长，容易导致索引占用空间较大。
  2. 尽量使用auto_increment生成主键。尽量不要使用uuid做主键，因为uuid不是顺序插入。
  3. 最好不要使用业务主键，因为业务的变化会导致主键值的频繁修改，主键值不建议修改，因为主键值修改，聚集索引一定会重新排序。
  4. 在插入数据时，主键值最好是顺序插入，不要乱序插入，因为乱序插入可能会导致B+树叶子结点频繁的进行页分裂与页合并操作，效率较低。
    1. 主键值对应聚集索引，插入主键值如果是乱序的，B+树叶子结点需要不断的重新排序，重排过程中还会频繁涉及到页分裂和页合并的操作，效率较低。
    2. B+树上的每个节点都存储在页（page）中。一个页面中存储一个节点。
    3. MySQL的InnoDB存储引擎一个页可以存储16KB的数据。
    4. 如果主键值不是顺序插入的话，会导致频繁的页分裂和页合并。在一个B+树中，页分裂和页合并是树的自动调整机制的一部分。当一个页已经满了，再插入一个新的关键字时就会触发页分裂操作，将页中的关键字分配到两个新的页中，同时调整树的结构。相反，当一个页中的关键字数量下降到一个阈值以下时，就会触发页合并操作，将两个相邻的页合并成一个新的页。如果主键值是随机的、不是顺序插入的，那么页的利用率会降低，页分裂和页合并的次数就会增加。由于页的分裂和合并是比较耗时的操作，频繁的分裂和合并会降低数据库系统的性能。因此，为了优化B+树的性能，可以将主键值设计成顺序插入的，这样可以减少页的分裂和合并的次数，提高B+树的性能。在实际应用中，如果对主键值的顺序性能要求不是特别高，也可以采用一些技术手段来减少页分裂和合并，例如B+树分裂时采用“延迟分裂”技术，或者通过调整页的大小和节点的大小等方式来优化B+树的性能。

image-20240708172538934

insert优化

insert优化原则：

  • 批量插入：数据量较大时，不要一条一条插入，可以批量插入，当然，建议一次插入数据不超过1000条
insert into t_user(id,name,age) values (1,'jack',20),(2,'lucy',30),(3,'timi',22);
  • mysql默认是自动提交事务，只要执行一条DML语句就自动提交一次，因此，当插入大量数据时，建议手动开启事务和手动提交事务。不建议使用数据库事务自动提交机制。
  • 主键值建议采用顺序插入，顺序插入比乱序插入效率高。
  • 超大数据量插入可以考虑使用mysql提供的load指令，load指令可以将csv文件中的数据批量导入到数据库表当中，并且效率很高，过程如下：
    • 第一步：登录mysql时指定参数
mysql --local-infile -uroot -p1234
  • 第二步：开启local_infile功能
set global local_infile = 1;
  • 第三步：执行load指令
use powernode;

create table t_temp(
  id int primary key,
  name varchar(255),
  password varchar(255),
  birth char(10),
  email varchar(255)
);

load data local infile 'E:\\powernode\\05-MySQL高级\\resources\\t_temp-100W.csv' into table t_temp fields terminated by ',' lines terminated by '\n';

文件中的数据如下：
image.png

导入表中之后，数据如下：
image-20240708172523717

count优化

分组函数count的使用方式：

  • count(主键)
    • 原理：将每个主键值取出，累加
  • count(常量值)
    • 原理：获取到每个常量值，累加
  • count(字段)
    • 原理：取出字段的每个值，判断是否为NULL，不为NULL则累加。
  • count(*)
    • 原理：不用取值，底层mysql做了优化，直接统计总行数，效率最高。

结论：如果你要统计一张表中数据的总行数，建议使用 count(*)

注意：

  • 对于InnoDB存储引擎来说，count计数的实现原理就是将表中每一条记录取出，然后累加。如果你想真正提高效率，可以自己使用额外的其他程序来实现，例如每向表中插入一条记录时，在redis数据库中维护一个总行数，这样获取总行数的时候，直接从redis中获取即可，这样效率是最高的。
  • 对于MyISAM存储引擎来说，当一个select语句没有where条件时，获取总行数效率是极高的，不需要统计，因为MyISAM存储引擎维护了一个单独的总行数。

update优化

当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，或者不是索引列时，会提升为表级锁。
什么是行级锁？A事务和B事务，开启A事务后，通过A事务修改表中某条记录，修改后，在A事务未提交的前提下，B事务去修改同一条记录时，无法继续，直到A事务提交，B事务才可以继续。

有一张表：t_fruit

create table t_fruit(
  id int primary key auto_increment,
  name varchar(255)
);

insert into t_fruit values(null, '苹果');
insert into t_fruit values(null, '香蕉');
insert into t_fruit values(null, '橘子');

开启A事务和B事务，演示行级锁：
事务A没有结束之前，事务B卡住：
image-20240708172454774
事务A结束之后，事务B继续执行：
image-20240708172446743
当然，如果更新的不是同一行数据，事务A和事务B可以并发：
image-20240708172503876

行级锁是对索引列加锁，以上更新语句的where条件是id，id是主键，当然有索引，所以使用了行级锁，如果索引失效，或者字段上没有索引，则会升级为表级锁：
image-20240708172511882

因此，为了更新的效率，建议update语句中where条件中的字段是添加索引的。

posted @ 2025-03-27 20:50  mango0219  阅读(35)  评论(0)    收藏  举报