当前位置：首页 > news >正文

MySQL 性能优化

news 文章来源：https://blog.csdn.net/weixin_47156401/article/details/134753887 2024/11/16 11:28:28

未完待续...

1. 分库、分表结构优化

1.1 数据库设计

不规范的数据库设计存在数据冗余以及插入、更新、删除异常问题。

规范化（Normalization）是数据库设计的一系列原理和技术，主要用于减少表中数据的冗余，增加完整性和一致性，同时使得数据库易于维护和扩展。

对于大多数的数据库系统而言，到达第三范式就已经足够了。也就是说，表需要定义主键，表中的字段都是不可再分的单一属性。非主键字段必须完全依赖于主键，不能只依赖于主键的一部分。属性不依赖于其它的非主属性。

对于前三个范式而言，只需要将不同的实体/对象单独存储到一张表中，并且通过外键建立它们之间的联系即可满足。

规范化可能导致连接查询（JOIN）过多，从而降低数据库的性能。因此，有时候为了提高某些查询或者应用的性能而故意降低规范反的程度，也就是反规范化。

常用的反规范化方法包括：

增加冗余字段；
增加计算列；
将小表合成大表等。

例如，想要知道每个部门的员工数量，需要同时连接部门表和员工表；可以在部门表中增加一个字段（emp_numbers），查询时就不需要再连接员工表，但是每次增加或者删除员工时需要更新该字段。

反规范化可能带来数据完整性的问题；因此，通常我们应该先进行规范化设计，再根据实际情况考虑是否需要反规范化。一般来说，数据仓库（Data Warehouse）和在线分析系统（OLAP）会使用到反规范化的技术，因为它们以复杂查询和报表分析为主。

> 推荐图书：《数据库系统概念（第七版）》

1.2 选择数据类型

我们在选择字段的数据类型时，首先应该满足存储业务数据的要求，其次还需要考虑性能和使用的便捷性。

一般来说，我们可以先确定基本的类型：

文本数据使用字符串类型进行存储。
数值数据，尤其是需要进行算术运算的数据，使用数字类型。
日期和时间信息最好使用原生的日期时间类型。
文档、图片、音频和视频等使用二进制类型。但推荐存储在文件服务器上，数据库中存储文件的路径，以减少数据库的压力。

然后进一步确定具体的数据类型。

1）在满足数据存储和扩展的前提下，尽量使用更小的数据类型。这样可以节省一些存储，通常性能也会更好。例如，对于一个小型公司而言，员工人数通常不会超过几百，可以使用SMALLINT类型存储员工编号。

2）尽量避免使用NULL属性。NULL需要更多的存储和额外的处理，尽量使用NOT NULL加上默认值。

3）如果一个字段同时出现在多个表中，我们应该使用相同的数据类型。例如，员工表中的部门编号（dept\_id）字段与部门表的编号（dept\_id）字段应该保持名称和类型一致。

1.3 数字类型

1.3.1 整数类型

MySQL支持TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）以及BIGINT整数类型。如果为整数类型指定了UNSIGNED属性，可以存储的正整数范围将会扩大一倍。

| 数字类型   | 存储（字节）  | 有符号类型最小值  | 有符号类型最大值   | 无符号类型最小值  | 无符号类型最大值 |
| :-------: | :----------: | :--------------: | :--------------: | :--------------: | :--------------: |
|  TINYINT  |      1       |       -128       |       127        |        0         |       255        |
| SMALLINT  |      2       |      -32768      |      32767       |        0         |      65535       |
| MEDIUMINT |      3       |     -8388608     |     8388607      |        0         |     16777215     |
|    INT    |      4       |   -2147483648    |    2147483647    |        0         |    4294967295    |
|  BIGINT   |      8       |      2^63^       |     2^63^-1      |        0         |     2^64^-1      |

MySQL 8.0.17开始，整数类型的显示宽度（例如INT(10)）和ZEROFILL选项已经被弃用（非标准型），将来的版本中会删除。直接定义 INT 即可。

显示宽度被弃用的原因：

MySQL 8.0.17 弃用整数类型的显示宽度和 ZEROFILL 选项是因为它们在实际使用中没有实际影响。显示宽度仅仅是用于控制在显示查询结果时整数的宽度，并不会影响存储或计算。而 ZEROFILL 选项则是指定在显示整数时是否填充零。

然而，这些选项在实际的数据存储和计算过程中并没有实际意义。整数类型在数据库中的存储大小是固定的，不受显示宽度的影响。而 ZEROFILL 选项只是在显示整数时添加了填充零的功能，并不会改变实际存储的值。

因此，MySQL 社区决定弃用这些选项，以简化整数类型的使用和理解。通过直接定义 INT 类型，可以更清晰地表示整数类型的含义，而无需关注显示宽度或 ZEROFILL 选项。

1.3.2 实数类型

MySQL提供了精确数字类型DECIMAL，也支持浮点数类型FLOAT和DOUBLE。

DECIMAL(p, s)用于存储对精度要求严格的数据（计算速度较慢），例如财务。其中精度p表示总的有效位数，刻度s表示小数点后允许的位数。例如，123.04的精度为5，刻度为2。

DECIMAL使用二进制格式存储，每9个数字使用4字节表示。NUMERIC是DECIMAL的同义词。

FLOAT是单精度浮点数，需要4字节存储空间；DOUBLE是双精度浮点数，需要8字节存储空间。浮点数使用近似运算，速度比DECIMAL更快但可能丢失精度。

CREATE TABLE t(d1 DOUBLE, d2 DOUBLE); 
INSERT INTO t(d1, d2) VALUES (101.40, 80.0);// 此时查不到数据，因为 21.4 只是近似数据，和真实情况不完全匹配
SELECT * FROM t WHERE d1-d2=21.4; -- 101.40-80.0d1|d2|
--+--+

一种折衷的方案是使用BIGINT替代DECIMAL存储财务数据（整数相对于DECIMAL计算速度更快，所以可以考虑先将DECIMAL转换为整数，然后使用BIGINT存储）。例如要存储精确到万分之一分的金额，可以将数据乘以100万倍之后存储到BIGINT，可以减少存储并优化计算性能，不过应用程序可能需要增加额外的处理。

1.4 字符串类型

1.4.1 CHAR与VARCHAR

1.4.2 BINARY与VARBINARY

1.4.3 TEXT与BLOB

1.4.4 ENUM类型

1.4.5 SET类型

1.5日期时间类型

1.5.1 日期类型

1.5.2 时间类型

2. 索引优化

2.1 索引简介

以下是一个简单的查询语句，它的作用是查找编号为5的员工：

SELECT * FROM employee WHERE emp_id = 5;

如果没有索引，数据库就只能扫描整个员工表，然后依次判断每个数据记录中的员工编号是否等于5并且返回满足条件的数据。这种查找数据的方法被称为**全表扫描**（Full Table Scan）。

全表扫描最大的一个问题，就是当表中的数据量逐渐增加时性能随之明显下降，因为磁盘 I/O 是数据库最大的性能瓶颈。

当表中的数据量很小（例如配置表），或者查询需要访问表中大量数据（数据仓库），索引对查询的优化效果不会很明显。

为了解决大量磁盘访问带来的性能问题，MySQL引入了一个新的数据结构：索引（Index）。索引在MySQL中也被称为键（Key）。MySQL默认使用B-树（B+树）索引，它就像图书后面的关键字索引一样，按照关键字进行排序并且提供了指向具体内容的页码。

B-树索引就像是一棵倒立的树，树的节点按照顺序进行组织，节点左侧的数据都小于该节点的值，节点右侧的数据都大于该节点的值。B+树索引基于B-树索引进行了优化，它们只在叶子节点存储索引数据（降低树的高度，从而减少了磁盘访问次数），并且增加了叶子节点或者兄弟节点之间的指针（优化范围查询）。

举例来说，假设索引的每个分支节点可以存储100个键值，100万条记录只需要3层B-树即可完成索引。数据库通过索引查找指定数据时需要读取3次磁盘I/O（每次磁盘I/O读取整个索引节点）就可以得到查询结果。

如果采用全表扫描的方式，数据库需要执行的磁盘I/O可能高出几个数量级。当数据量增加到1亿条记录时，通过索引访问只需要增加一次磁盘I/O即可，全表扫描则需要再增加几个数量级的磁盘I/O。

主流数据库默认使用的都是B-树（B+树、 B*树）索引，它们实现了稳定且快速的数据查找（O(log n) 对数时间复杂度），可以用于优化=、、 BETWEEN、 IN运算符以及字符串的前向匹配（“ABC%”）等查询条件。

2.2 聚簇索引

聚集索引（Clustered Index）将表中的数据按照索引（通常是主键）的结构进行存储。也就是说，聚集索引的叶子节点中直接存储了表的数据，而不是指向数据的指针。

聚集索引其实是一种特殊的表， MySQL（InnoDB）和 Microsoft SQL Server 将这种结构的表称为聚集索引， Oracle数据库中将其称为索引组织表（IOT）。这种存储数据的方式类似于Key-Value存储，适合基于主键进行查询的应用。

聚簇索引生成方式：

如果定义了主键，InnoDB使用主键聚集数据；
如果没有定义主键，InnoDB使用第一个非空的UNIQUE索引聚集数据；
如果没有主键和可用的UNIQUE索引，InnoDB使用一个隐藏的内部ID字段聚集数据。（存在问题：只有一个字段ID，如果多个表都是用该方式构建聚簇索引，此时内部ID的自增都是在同一个ID上自增）。

2.3 辅助索引

MySQL（InnoDB）中的辅助索引也被称为二级索引（Secondary Index），叶子节点存储了聚集索引的键值（通常是主键）。

我们通过二级索（上图中使用 name 作为二级索引）引查找数据时，系统需要先找到相应的主键值，再通过主键索引查找相应的数据（回表）。因此，创建聚集索引的主键字段越小，二级索引就越小。这也是我们通常使用自增数字而不是UUID作为MySQL主键的原因之一。

二级索引叶子节点存储聚集索引键值的好处：当当数据发生改变或移动时，可以保证二级索引的稳定性（只要ID不变则无需修改二级索引）。

二级索引叶子节点存储聚集索引键值的缺点：增加了一次回表操作。

2.4 复合索引

复合索引是基于多个字段创建的索引，也叫多列索引。

复合索引可以避免为每个字段创建单独的索引，使用复合索引时最重要的是索引字段的顺序。

复合索引首先按照第一个（最左侧）字段排序，然后按照第二个字段排序，以此类推。因此，一个选择索引字段顺序的经验法则是：将选择性最高的字段放在最前面。

通过如下命令可以查看不同前缀长度的选择性：

SELECT count(DISTINCT emp_name)/count(*) emp_name_sel,count(DISTINCT sex)/count(*) sex_sel
FROM employee;emp_name_sel|sex_sel|
------------+-------+1.0000| 0.0800|

注意：如果数据分布不均匀，这种经验法则可能对于特定值的查询性能很差。

最左前缀匹配原则：复合索引(col1, col2, col3)，相当于以下三个索引：

(col1)
(col1, col2)
(col1, col2, col3)

举例来说，它可以用于优化以下查询条件（左侧的确定了才能使用索引查找右侧的）：

WHERE col1 = val1 AND col2 = val2 AND col3 = val3
WHERE col1 = val1 AND col2 = val2
WHERE col1 = val1
WHERE col1 = val1 AND col2 BETWEEN val2 AND val3
WHERE col1 BETWEEN val1 AND val2
WHERE col1 LIKE 'ABC%'

2.5 前缀索引

前缀索引（Prefix Index）是指基于字段的前一部分内容创建的索引。BLOB 、TEXT或者很长的VARCHAR类型字段必须使用前缀索引，因为MySQL对索引的长度有限制。MySQL 5.7默认不能超过3072字节。

前缀索引的优点是可以节省空间，提高索引性能，但缺点是会降低索引的选择性。

索引的选择性是指不重复的索引值（基数）和表中的数据总量的比值，范围处于（1/总数据量）到1之间。选择性越高的索引查询效率越高，因为可以过滤掉更多的数据。主键和唯一索引的选择性是1。

通过如下命令可以查看不同前缀长度的选择性：

SELECT count(DISTINCT LEFT(email,3))/count(DISTINCT email) left3,count(DISTINCT LEFT(email,4))/count(DISTINCT email) left4,count(DISTINCT LEFT(email,5))/count(DISTINCT email) left5,count(DISTINCT LEFT(email,6))/count(DISTINCT email) left6
FROM employee;left3 |left4 |left5 |left6 |
------+------+------+------+
0.6000|0.7200|0.9200|1.0000|

示例中，当前缀长度到达6的时候，选择性和索引整个email字段没有区别。因此，可以基于该字段创建一个前缀索引：

CREATE INDEX idx_employee_email ON employee(email(6));

前缀索引也存在缺点，MySQL不能使用前缀索引进行排序（ORDER BY）和分组（GROUP BY），也不能实现索引覆盖扫描。

前缀索引的设计关键在于保证足够的选择性，同时又不能太长，以便节约存储。

2.6 函数索引

MySQL 8.0支持函数索引（Function-Based Index），也被称为表达式索引（Expression-Based Index），是基于函数或者表达式创建的索引。

例如，员工的电子邮箱不区分大小写并且唯一，我们可以基于LOWER(email)函数创建一个唯一的函数索引。

explain select * from employee where lower(email) = lower('ZhangFei@shuguo.com');Name         |Value      |
-------------+-----------+
id           |1          |
select_type  |SIMPLE     |
table        |employee   |
partitions   |           |
type         |ALL        |        // 代表全表扫描，因为email建立的索引，包含大小写，上述查询时都转化为了小写
possible_keys|           |
key          |           |
key_len      |           |
ref          |           |
rows         |25         |
filtered     |100.0      |
Extra        |Using where|create unique index uk_emp_email_lower on employee((lower(email)));    // 使用小写创建索引
analyze table test;explain select * from employee where lower(email) = lower('ZhangFei@shuguo.com');Name         |Value             |
-------------+------------------+
id           |1                 |
select_type  |SIMPLE            |
table        |employee          |
partitions   |                  |
type         |const             |
possible_keys|uk_emp_email_lower|
key          |uk_emp_email_lower|        // 使用新建的索引查询
key_len      |403               |
ref          |const             |
rows         |1                 |
filtered     |100.0             |
Extra        |                  |

函数索引能够支持其他方式无法使用的数据类型，例如JSON数据。

CREATE TABLE employees (data JSON,INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
);
INSERT INTO employees VALUES('{ "name": "james", "salary": 9000 }'),('{ "name": "James", "salary": 10000 }'),('{ "name": "Mary", "salary": 12000 }'),('{ "name": "Peter", "salary": 8000 }');SELECT * FROM employees WHERE data->>'$.name' = 'James';

函数索引要求完全按照索引定义的相同方式指定查询中的条件。

扩展：什么是 JSON 数据？

在数据库中，JSON（JavaScript Object Notation）是一种用于存储和表示结构化数据的格式。它是一种轻量级的数据交换格式，常用于Web应用程序和分布式系统中的数据传输和存储。

JSON数据由键值对组成，使用大括号 {} 包围。每个键值对由冒号 : 分隔，键是一个字符串，值可以是字符串、数字、布尔值、数组、对象或null。JSON数据具有以下特点：

1）简洁性：JSON使用简洁的语法表示数据，易于阅读和编写。

2）可读性：JSON数据采用文本格式，可被人类读取和理解。

3）可扩展性：JSON支持嵌套结构，可以构建复杂的数据层次。

下面是一个示例JSON数据：

{"name": "John Doe","age": 30,"email": "johndoe@example.com","address": {"street": "123 Main St","city": "New York","state": "NY"},"hobbies": ["reading", "traveling", "photography"]
}

在这个例子中，JSON数据表示一个人的信息。它包含了姓名、年龄、电子邮件和地址等属性。其中，地址是一个嵌套的对象，包含街道、城市和州。而兴趣爱好是一个数组，包含多个字符串元素。

在数据库中，JSON数据可以存储在特定的JSON字段中，例如MySQL中的JSON数据类型或PostgreSQL中的JSONB数据类型。这样可以方便地存储和查询具有不同结构的数据，而无需提前定义固定的表结构。

使用JSON数据类型，数据库可以存储和检索非结构化或半结构化的数据，适用于存储用户配置、日志、文档、社交媒体数据等。同时，数据库系统提供了一系列的JSON函数和操作符，用于在查询中处理和操作JSON数据。

2.7 降序索引

MySQL 8.0支持降序索引（Descending index）：索引定义中的DESC不再被忽略，而是以降序方式存储索引键值。

在之前的版本中，索引支持反向扫描，但是性能稍差一些。降序索引可以进行正向扫描，效率更高。当查询需要针对某些列升序排序，同时针对另一些列降序排序时，降序索引使得优化器可以使用多列混合索引扫描。

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT,INDEX idx1 (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEX idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC)
);

优化器可以为不同的ORDER BY子句使用正向索引扫描，而不需要执行 *filesort* 排序。

explain select * from t ORDER BY c1 ASC, c2 DESC;    // 会自动使用最适合的索引方式进行查找Name         |Value      |
-------------+-----------+
id           |1          |
select_type  |SIMPLE     |
table        |t          |
partitions   |           |
type         |index      |
possible_keys|           |
key          |idx2       |
key_len      |10         |
ref          |           |
rows         |1          |
filtered     |100.0      |
Extra        |Using index|

MySQL 8.0不再对GROUP BY操作进行隐式排序，排序需要明确指定ORDER BY。

2.8 隐藏索引

MySQL 8.0支持隐藏索引（invisible index），也称为不可见索引。隐藏索引不会被优化器使用。

主键不能设置为隐藏（包括显式设置或隐式设置）。

CREATE TABLE t1 (i INT,j INT,k INT,INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;CREATE INDEX j_idx ON t1 (j) INVISIBLE;        // 创建一个名为 j_idx 的隐藏索引
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;  // 添加一个名为 k_idx 的隐藏索引ALTER TABLE t1 ALTER INDEX m_idx INVISIBLE;    // 将一个已经创建的索引修改为隐藏索引(假设 m_idx 索引已经被创建)

索引的可见性不会影响索引的维护。例如，无论索引是否可见，每次修改表中的数据时都需要对相应索引进行更新，而且唯一索引都会阻止插入重复的列值。

MySQL系统变量optimizer_switch中的use_invisible_indexes设置控制了优化器构建执行计划时是否使用隐藏索引。如果设置为off（默认值），优化器将会忽略隐藏索引（与引入该属性之前的行为相同）。如果设置为on，隐藏索引仍然不可见，但是优化器在构建执行计划时将会考虑这些索引。

不可见索引特性可以用于测试删除某个索引对于查询性能的影响，同时又不需要真正删除索引，也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时，而将其设置为不可见或可见将会非常简单快捷。

隐藏索引应用场景：软删除、灰度发布。

2.9 覆盖索引

在某些情况下，查询语句通过索引访问就可以返回所需的结果，不需要访问表中的数据（回表），此时我们把这个索引称为覆盖索引（Covering Index）。某些数据库中称之为Index Only Scan。

// emp_id 是主键 id，在 dept_id 上面建立了辅助索引
explain select emp_id, dept_id  from employee where dept_id = 5;    Name         |Value       |
-------------+------------+
id           |1           |
select_type  |SIMPLE      |
table        |employee    |
partitions   |            |
type         |ref         |        // 等值查询
possible_keys|idx_emp_dept|        // dept_id 索引
key          |idx_emp_dept|
key_len      |4           |
ref          |const       |
rows         |8           |
filtered     |100.0       |
Extra        |Using index |        // 使用索引查询且没有回表

上述查询语句要查询的值是 emp_id（主键id）和 dept_id，而辅助索引 dept_id 本身包含了 dept_id，叶节点中包含了emp_id（主键id），所以在查询这两个值时，无需回表即可获得想要结果。此时的执行计划中Extra列显示Using index。

覆盖索引是优化器选择的一种执行计划；或者也可以说，任何索引在某种情况下都可能称为覆盖索引。

任何索引都包含了主键列，可用覆盖通过索引查找主键的查询语句。

2.10 索引和排序

MySQL数据排序可通过 *filesort* 或者索引顺序扫描的方式实现。

文件排序（filesort）：当查询中没有适用于排序的索引或无法使用现有索引时，MySQL会使用文件排序。它的工作原理是将查询结果加载到临时文件中，然后在文件中进行排序操作。这种方式需要将数据加载到磁盘上的临时文件中，然后进行排序，可能会导致较高的磁盘I/O和内存消耗。
索引顺序扫描：当查询中存在适用于排序的索引时，MySQL可以直接利用索引的顺序来避免文件排序。它会按照索引的顺序扫描数据，并返回按照排序要求的结果（索引一般默认升序）。

示例一：

EXPLAIN SELECT * FROM employee e ORDER BY emp_name;Name         |Value         |
-------------+--------------+
id           |1             |
select_type  |SIMPLE        |
table        |e             |
partitions   |              |
type         |ALL           |        // 全表扫描
possible_keys|              |
key          |              |
key_len      |              |
ref          |              |
rows         |25            |
filtered     |100.0         |
Extra        |Using filesort|        // 此处代表排序，但不一定是 filesort 排序，也可能是内存排序

上述查询中，由执行计划可看出，在查询时走的是全表扫描。

走全表扫描而没有走索引扫描的原因：查询语句中要查询的是记录的所有字段，如果直接通过辅助索引（emp_name）进行查找，会涉及到回表操作；虽然辅助索引是有序的，但辅助索引叶节点包含的主键 id 是无序的，也就是说，在进行回表时需要进行大量的随机 IO（可参考优化器：MRR），最终导致查询性能低下，不如直接进行全表扫描，然后再排序。

优化器 MRR：MySQL 优化器 MRR_mysql优化器-CSDN博客

示例二：

EXPLAIN SELECT emp_id, emp_name FROM employee e ORDER BY emp_name;Name         |Value       |
-------------+------------+
id           |1           |
select_type  |SIMPLE      |
table        |e           |
partitions   |            |
type         |index       |
possible_keys|            |
key          |idx_emp_name|
key_len      |202         |
ref          |            |
rows         |25          |
filtered     |100.0       |
Extra        |Using index |

上述查询语句要查询的值是 emp_id（主键id）和 emp_name，而辅助索引 emp_name 本身包含了 dept_id，叶节点中包含了emp_id（主键id），所以在查询这两个值时，无需回表即可获得想要结果。此时的执行计划中Extra列显示Using index，即没有使用回表直接进行查询。

MySQL索引即可以用于查询数据，也可以用于实现排序。前提是索引字段的顺序和ORDER BY子句字段的顺序完全一致（最左前缀原则）。

对于复合索引(col1, col2, col3)，可以用于优化以下查询：

WHERE col1 = val1 ORDER BY col2, col3
WHERE col1 = val1 ORDER BY col2 DESC
WHERE col1 BETWEEN val1 AND val2 ORDER BY col1, col2（col1是范围查询，必须出现在ORDERR BY里面）

但是无法使用该索引实现以下查询中的排序：

WHERE col1 = val1 ORDER BY col2 DESC, col3（复合索引中 col1, col2, col3 都是默认升序）
WHERE col1 = val1 ORDER BY col3
WHERE col1 BETWEEN val1 AND val2 ORDER BY col2, col3

如果查询连接了多个表，只有ORDER BY子句字段全部属于第一个表时，才能利用索引进行排序。

2.11 重复索引和冗余索引

MySQL允许在相同的字段上按照相同的顺序创建多个相同类型的索引，也就是**重复索引**。这样会占用更多存储空间，也导致优化器需要进行更多的评估。

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY,c1 INT UNIQUE, c2 INT,INDEX idx_pk (id),INDEX idx1 (c1)
);

以上示例中的索引idx_pk和idx1都属于重复索引（MySQL（InnoDB）自动为主键、唯一约束以及外键约束创建相应的索引）。

复合索引字段顺序不同，则不算重复索引。例如(col1, col2)和(col2, col1)不是重复索引。

索引类型不同，则不算重复索引。例如INDEX(col)和FULLTEXT INDEX(col)不是重复索引。

**冗余索引**是指字段已经被其他索引包含的索引。

如果已经存在复合索引(col1, col2)，那么索引(col1)就是冗余索引，因为前者可用替代索引(col1)。不过需要注意，索引(col2)不是冗余索引，因为col2不是索引(col1, col2)的最左前缀列。

索引(col1, id)是一个冗余索引，因为辅助索引中一定会包含主键字段。

一般建议基于已有的索引进行扩展，而不是不断增加新的冗余索引，但是也存在例外。

重复索引和冗余索引的处理方法就是删除索引，但是删除之前需要确认不会产生副作用。MySQL 8.0可用利用不可见索引特性减少影响。

另外，可能会存在从未使用过的索引，通过系统视图sys.schema_unused_indexes查看，建议确认后删除。

2.12 索引和DML

索引不仅会对查询产生影响，对数据进行插入、更新和删除操作时也需要同步维护索引结构。

INSERT语句

对于INSERT语句而言，索引越多执行越慢。插入数据必然导致增加索引项，这种操作的成本往往比插入数据本身更高，因为索引必须保持顺序和B+树的平衡（索引节点拆分）。因此，优化插入语句的最好方法就是减少不必要的索引。

没有任何索引时的插入性能是最好的，因此在加载大量数据时，可以临时删除所有的索引并在加载完成后重建索引。

UPDATE语句

UPDATE语句如果指定了查询条件，可以通过索引提高更新操作的性能，因为通过索引可以快速找到需要修改的数据。

另一方面，UPDATE语句如果修改了索引字段的值，需要删除旧的索引项并增加新的索引项。因此，更新操作的性能通常也取决于索引的数量。为了优化UPDATE语句，频繁更新的字段不适合创建索引；同时应该尽量避免修改过多的字段。

**DELETE语句**

对于DELETE语句而言，如果指定了查询条件，可以通过索引提高删除操作的性能。因为它和UPDATE语句一样，需要先执行一个SELECT语句找到需要删除的数据。

删除操作涉及的索引更新和插入操作类似，只不过它是删除一些索引项并确保索引树的平衡。因此，索引越多删除性能越差。不过有一个例外就是没有任何索引，这个时候性能会更差，因为数据库需要执行全表扫描才能找到需要删除的数据。

2.13 索引设计原则

> 推荐图书：《数据库索引设计与优化》

三星索引：

索引将相关的数据存储在一起，减少需要扫描的数据量，获得一星（即针对需要查询字段建立索引）；
索引中的数据顺序和查询排序顺序一致，避免排序操作，获得二星；
索引包含了查询所需的全部字段，避免随机IO，获得三星。

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c1 INT,c2 INT,INDEX idx1 (c1, c2)
);EXPLAIN SELECT * FROM t WHERE c1>100 ORDER BY c1, c2;Name         |Value                   |
-------------+------------------------+
id           |1                       |
select_type  |SIMPLE                  |
table        |t                       |
partitions   |                        |
type         |index                   |        // 使用了索引查找
possible_keys|idx1                    |
key          |idx1                    |
key_len      |10                      |
ref          |                        |
rows         |1                       |
filtered     |100.0                   |
Extra        |Using where; Using index|        // Using where 指 where 判断，Using index 指使用 index 查找且无需进行回表

既然索引可以优化查询的性能，那么我们是不是遇到性能问题就创建一个新的索引，或者直接将所有字段都进行索引？显然并非如此，因为索引在提高查询速度的同时也需要付出一定的代价：

首先，索引需要占用磁盘空间。索引独立于数据而存在，过多的索引会导致占用大量的空间。
其次，进行DML操作时，也需要对索引进行维护；维护索引有时候比修改数据更加耗时。

一般来说，可以考虑为以下情况创建索引：

经常出现在WHERE条件或者ORDER BY中的字段创建索引，可以避免全表扫描和额外的排序操作；
多表连接查询的关联字段或者外键涉及的字段，可以避免全表扫描和外键级联操作导致的锁表；
查询中的GROUP BY分组操作字段。

对于交易类型的系统，首先找出查询时间最长或者占用资源最多的语句，检查它们涉及的表结构、索引结构，判断表结构和索引是否合理。如果这些优化还不能满足要求，另一个方法就是SQL查询优化。

如果需要本文 WORD、PDF 相关文档请在评论区留言！！！

如果需要本文 WORD、PDF 相关文档请在评论区留言！！！

如果需要本文 WORD、PDF 相关文档请在评论区留言！！！