MySQL优化-优快云博客

1.连接字段加索引

在进行连接查询时，为连接字段添加索引是一个良好的实践。索引能够显著加快查询速度，尤其在数据量较大的情况下，这种加速效果更为明显。尽管索引会占用一定的磁盘空间，并且可能使增删改操作的速度稍有变慢，但如果公司有足够的预算购置大容量磁盘，且增删改操作的频率较低，那么这些负面影响基本可以忽略不计。提前添加索引，可避免后期因数据量增大导致查询变慢时再进行添加的麻烦。

为什么一定要加索引

1.1. 提高查询效率减少数据扫描范围索引是表中数据的有序映射。在连接查询中，如果没有索引，MySQL 可能需要扫描整个表来查找满足连接条件的行。例如，在前面提到的嵌套循环连接中，如果没有索引，内层表的每一行都需要和外层表的每一行进行比较，时间复杂度是 O(nm)，其中 n 和 m 分别是两个表的行数。而如果在连接列上添加了索引，MySQL 可以利用索引快速定位到可能满足条件的行。例如，对于 a.id = b.id 的连接条件，如果 b.id 上有索引，当从外层表 tablea 中取出一行时，可以通过索引快速在 tableb 中查找匹配的行，而不需要扫描整个 tableb。这样可以大大减少数据扫描的范围，提高查询效率。加速连接算法的执行对于哈希连接，虽然它本身不需要索引，但如果连接列上有索引，优化器可能会优先选择其他更高效的连接算法（如嵌套循环连接），因为索引可以提高这些算法的效率。对于合并连接，索引可以帮助快速对表进行排序。如果连接列上有索引，表可能已经按照索引排序，这样就可以直接进行合并操作，而不需要额外的排序步骤，从而节省时间和资源。 1.2. 改善性能减少磁盘 I/O 索引可以将数据存储在更紧凑的结构中，减少磁盘 I/O 操作。在没有索引的情况下，MySQL 可能需要频繁地从磁盘读取数据，尤其是当数据量很大时。而索引可以将数据按照一定的顺序存储，使得 MySQL 可以通过索引快速定位到数据所在的磁盘位置，减少不必要的磁盘读取。例如，如果一个表有 1000 万行数据，没有索引时，MySQL 可能需要逐行扫描整个表来查找满足连接条件的行，这会导致大量的磁盘 I/O 操作。而如果有索引，MySQL 可以通过索引快速跳过不满足条件的行，减少磁盘 I/O 的次数。提高并发性能索引还可以提高数据库的并发性能。当多个连接查询同时执行时，如果没有索引，每个查询都需要扫描整个表，这会导致大量的磁盘竞争和锁竞争。而索引可以减少每个查询需要扫描的数据量，从而减少磁盘竞争和锁竞争，提高并发性能。总之，索引在 MySQL 的连接查询中起着至关重要的作用。它不仅可以提高查询效率，还可以改善性能，减少磁盘 I/O 和锁竞争。在设计数据库时，合理地为连接列添加索引是非常重要的

2.能用连接查询不用子查询

如果你是个经验丰富的mysql使用人员你知道建索引你知道子查询里查小表那么或许你用子查询问题也不大但是在如下场景中，你在子查询中先查的大表。连接查询通常会比子查询更高效，因为连接查询可以避免生成大临时结果集，并且可以更好地利用索引和优化器的智能决策。 EXPLAIN SELECT FROM small_table WHERE id IN (SELECT id FROM big_table); EXPLAIN SELECT small_table. FROM small_table JOIN big_table ON small_table.id = big_table.id;

2.1. 连接查询的优点

连接查询（JOIN）通常在以下情况下表现更好：高效利用索引：连接查询可以更好地利用索引，尤其是当连接条件（如 ON 子句中的条件）涉及的列上有索引时。优化器可以利用这些索引来快速定位匹配的行，从而提高查询效率。优化器优化空间大：MySQL优化器对连接查询的优化策略通常更丰富。优化器可以根据表的大小、索引情况和统计信息，选择最优的连接顺序和连接算法（如嵌套循环连接、哈希连接等）。减少临时结果集：连接查询不会生成一个大的临时结果集（如子查询可能产生的中间结果），而是直接通过连接条件进行匹配，减少了中间结果的处理开销。

2.2. 子查询的优点

子查询（IN、EXISTS 等）在某些场景下也有其独特的优势：语义清晰：子查询的逻辑通常更直观，尤其是当需要从一个表中筛选出满足某个条件的记录时。例如，SELECT FROM small_table WHERE id IN (SELECT id FROM big_table) 的逻辑非常清晰，容易理解。适用于某些特定场景：非等值连接：当需要基于非等值条件进行筛选时，子查询可能更合适。例如，SELECT FROM small_table WHERE some_column > (SELECT MAX(some_column) FROM big_table)。相关子查询：当子查询依赖于外部查询的列时（相关子查询），子查询是必要的。例如，SELECT * FROM small_table WHERE id IN (SELECT id FROM big_table WHERE big_table.status = small_table.status)。减少连接的复杂性：在某些情况下，使用子查询可以避免复杂的连接逻辑，尤其是当连接条件较为复杂时。

2.3. 性能对比

连接查询：优点：通常在表大小差异较大时表现更好，尤其是当小表作为驱动表时。缺点：如果连接条件没有合适的索引，可能会退化为全表扫描，导致性能下降。子查询：优点：在某些特定场景下（如子查询结果集较小）可能更高效。缺点：如果子查询结果集很大，可能会生成一个大的临时结果集，导致性能下降。

2.4. 建议

优先考虑连接查询适用场景：当查询涉及多个表，并且连接条件明确且有合适的索引时。当表的大小差异较大时（小表作为驱动表）。当需要返回多个表的列时。

总结：其实上面说这么多就记住我这一句话就行了除了要用in,exist 这种关键字查询可以用用子查询其它全都给我用连接查询当然连接查询你要记得建索引

3.查大表不要用select *

我们业务逻辑中当查询某些字段比较多的大表时你本来只用5个字段但是这张表有50个字段你直接select 那不是查出来45个你用不到的字段？那多占用的带宽，以及业务逻辑运行时你对象没被销毁之前占用的内存都是没必要的消耗。这样短期还好，越往后你越多表用select 就会造成更多的浪费内存带宽这都是钱啊并且不便宜，后续要省钱还得重构麻烦的很

4.能用union all不用union

这是啥意思呢 union all不会去重而 union会但是你知道你查询后的数据不会用重复比如你知道查出来的数据不会重复那么就可以直接用union all 这样能加快查询速度判断去重的逻辑就给省了

5.group by禁用排序

    你是否有观察过 当你group by 某个字段 查询的时候会默认用这个字段排序 当你不需要这个排序时 无疑会查询时间会变长 这个时候你就可以加上order by null

6.巧用limit 1

当你只需要一条数据或者你知道你查询出来的数据只有一条时可以加上limit 1 这样查出一条后就会给你返回不会继续查询

7.字段类型合理选择

这个是在建表时就要主义的，在满足你需求的情况下你的字段类型占用空间要尽可能的小。在MySQL中存储手机号时，选择合适的数据类型非常重要，主要需要考虑存储效率、查询性能和数据完整性等因素。以下是几种常见的数据类型及其适用场景分析：

7.1. VARCHAR

定义：可变长度的字符串类型。适用场景：手机号通常是固定长度的数字字符串（例如中国大陆的手机号是11位数字），使用VARCHAR可以灵活地存储不同国家或地区的手机号格式（如带区号的国际号码）。优点：可以存储包含特殊字符的手机号（如+、-等）。存储效率较高，因为只占用实际字符长度的存储空间。缺点：需要指定最大长度（如VARCHAR(20)），如果长度设置不合理，可能会浪费存储空间或限制数据存储。查询时可能需要额外的处理（如去除空格或特殊字符）。示例：

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20) NOT NULL
);

7.2. CHAR

定义：固定长度的字符串类型。适用场景：如果手机号的长度是固定的（如中国大陆的11位手机号），使用CHAR可以节省存储空间并提高查询性能。优点：存储效率高，因为它是固定长度的。查询性能较好，因为MySQL可以直接按固定长度读取数据。缺点：不支持可变长度数据，如果手机号长度不固定（如国际号码），可能会浪费存储空间。不能存储特殊字符（如+、-等）。示例：

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone CHAR(11) NOT NULL
);

7.3. BIGINT

定义：用于存储大整数。适用场景：如果手机号只包含数字且长度固定（如11位），可以使用BIGINT。优点：存储效率高，占用8字节。查询性能较好，因为整数类型的比较和索引效率较高。缺点：不能存储特殊字符（如+、-等）。如果手机号长度超过11位（如国际号码），可能会超出BIGINT的范围。示例：

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone BIGINT NOT NULL
);

7.4. 其他建议

数据完整性：无论选择哪种数据类型，建议在数据库层面添加约束（如NOT NULL、UNIQUE等）以确保数据的完整性和唯一性。索引优化：如果手机号需要频繁查询，建议为其添加索引，以提高查询性能。国际化支持：如果应用需要支持国际手机号，建议使用VARCHAR，并存储完整的国际格式（如+8613800138000）。推荐选择如果手机号长度固定且只包含数字（如中国大陆手机号），推荐使用CHAR(11)或BIGINT。如果需要支持国际手机号或手机号中可能包含特殊字符，推荐使用VARCHAR(20)。根据你的具体需求选择合适的数据类型，确保既能满足存储需求，又能优化查询性能。

7.5 IP地址存储

某些字段我们是只需要存储并且不需要通过这个字段作为条件查询比如IP地址，那么我们可以转换为数值类型存储，这样比用字符串存储能节省大量的磁盘空间 ip_address VARCHAR(15) 存储255.255.255.255：占用16字节（15个字符 + 1个长度前缀）。

ip_address INT UNSIGNED 占用字节数：固定占用 4个字节。

如下是转换逻辑我们知道转换逻辑之后在数据库中查出来后在程序运行的内存中转换下就好了，相当于就是经典的时间换空间但是这个时间基本可以忽略所以看你需要来选择类型

IP地址转换为数值的逻辑 IPv4地址转换 IPv4地址是一个32位的二进制数，通常以点分十进制格式表示（如192.168.1.1）。要将其转换为数值，可以按照以下步骤进行：将IP地址拆分为四个部分：将点分十进制的IP地址拆分为四个十进制数，例如192.168.1.1拆分为192、168、1和1。将每个部分转换为二进制：将每个十进制数转换为8位二进制数。 192 → 11000000 168 → 10101000 1 → 00000001 1 → 00000001 将四个二进制数拼接成一个32位的二进制数： 11000000 10101000 00000001 00000001 将32位二进制数转换为十进制数： 11000000 10101000 00000001 00000001 = 3232235777

8. 连接数的设置

MySQL的连接数设置并不是越大越好，需要根据实际的硬件资源、业务需求和系统负载情况进行合理配置。以下是关于如何合理设置MySQL连接数的建议：

8.1. 理解连接数的影响

连接数过少：可能导致部分用户无法正常访问数据库，出现连接超时或连接拒绝的问题，影响用户体验。连接数过多：会占用大量系统资源（如内存、CPU），导致数据库性能下降，甚至可能使服务不可用。

8.2. 合理设置连接数的方法

（1）监控当前连接使用情况通过监控工具查看当前的连接使用情况，可以使用以下命令：

SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';

这些命令可以帮助你了解当前的最大连接使用情况和当前的连接数。（2）根据业务需求调整评估业务负载：根据业务的并发需求和历史数据，估算合理的连接数。例如，如果业务高峰期需要支持100个并发连接，可以将最大连接数设置为120左右，以应对突发流量。硬件资源限制：根据服务器的硬件资源（如CPU、内存）进行调整。如果硬件资源充足，可以适当增加连接数，但要避免超过硬件的承载能力。（3）优化应用层连接管理使用连接池：通过连接池技术复用数据库连接，减少连接的创建和销毁开销。合理设置连接池的最大连接数和最小空闲连接数。关闭不必要的连接：确保应用程序在使用完数据库连接后及时关闭，避免连接长时间占用。（4）优化数据库性能查询优化：通过优化SQL查询和索引设计，减少查询时间和资源消耗，从而降低对连接数的需求。限制慢查询：避免执行时间过长的SQL查询，减少单个连接的占用时间。

8.3. 动态调整连接数

MySQL的连接数可以通过配置文件my.cnf或动态调整命令进行设置： SET GLOBAL max_connections = 300; 或者在my.cnf文件中设置： max_connections=300 调整后需要重启MySQL服务以生效。

8.4. 理论计算与压测验证

理论计算：根据业务的QPS（每秒查询次数）和平均查询耗时，估算合理的连接数。例如，QPS为1000，平均查询耗时为10ms，则理论连接数为1000 * 0.01 = 10。压力测试：通过压力测试工具（如JMeter）模拟高并发场景，验证设置的连接数是否合理。

MySQL连接数的设置需要综合考虑业务需求、硬件资源和系统负载情况。合理设置连接数可以提高系统的稳定性和性能，但并不是越大越好。建议通过监控、理论计算和压力测试相结合的方式，动态调整连接数，以达到最佳的性能表现。

9.分流

使用mq redis等组件分流减压

举个例子有十个人去打饭是不是排队肯定比一窝蜂挤上去快啊，使用mq就类似于排队能使得mysql的压力不会一下子激增也就是我们所说的限流削峰

redis呢则是类似于第一道关口会缓存热点数据这样redis中能查到的就不会查mysql是不是也能够给mysql分流减压

10.索引

整理成如下两张表格分别为MySQL索引设计注意事项及索引失效的常见场景

MySQL索引设计注意事项

注意事项	描述	示例
选择合适的字段作为索引	索引字段应是查询中经常使用的字段，且具有较高的唯一性（选择性）。	对于用户表，`user_id` 或 `email` 是较好的索引字段，因为它们的值通常具有唯一性。
避免对频繁更新的字段创建索引	索引会增加插入、更新和删除操作的开销，因为需要同步更新索引。	如果某个字段（如 `status`）经常更新，尽量避免为其创建索引。
合理使用复合索引	复合索引可以提高多列查询的性能，但需要注意列的顺序。	对于查询 `SELECT * FROM table WHERE col1 = 'value1' AND col2 = 'value2'`，创建复合索引 `(col1, col2)` 更有效。
避免过度索引	每个索引都会占用额外的存储空间，并增加维护成本。只对真正需要的字段创建索引。	如果某个表有10个字段，但只有3个字段经常用于查询，只需为这3个字段创建索引。
使用前缀索引	对于长文本字段（如 `VARCHAR`），可以创建前缀索引以节省空间并提高性能。	对于字段 `description VARCHAR(1000)`，创建前缀索引：`CREATE INDEX idx_desc ON table(description(255));`
考虑覆盖索引	如果查询的所有字段都在索引中，MySQL可以直接使用索引返回结果，而无需访问表数据。	查询 `SELECT col1, col2 FROM table WHERE col1 = 'value1'`，如果存在复合索引 `(col1, col2)`，则为覆盖索引。
定期维护索引	定期检查和优化索引，删除无用的索引，以保持数据库性能。	使用 `SHOW INDEX FROM table;` 查看索引使用情况，删除长时间未使用的索引。

索引失效的常见场景

失效场景	描述	示例
使用函数或表达式	在索引字段上使用函数或表达式时，索引可能失效。	查询 `SELECT * FROM table WHERE YEAR(date_field) = 2024`，即使 `date_field` 有索引，也可能失效。
不匹配的数据类型	如果查询条件中的数据类型与索引字段的类型不匹配，索引可能失效。	查询 `SELECT * FROM table WHERE col1 = '123'`，如果 `col1` 是 `INT` 类型，索引可能失效。
使用 `OR` 条件	如果 `OR` 条件中的字段没有索引，或者索引无法同时覆盖所有条件，索引可能失效。	查询 `SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2'`，如果 `col2` 没有索引，可能会导致全表扫描。
模糊查询的前缀通配符	如果模糊查询使用了前缀通配符（如 `%` 在开头），索引可能失效。	查询 `SELECT * FROM table WHERE col1 LIKE '%value1'`，即使 `col1` 有索引，也可能失效。
范围查询和等值查询混合	在复合索引中，如果范围查询在前，等值查询在后，索引可能失效。	查询 `SELECT * FROM table WHERE col1 > 'value1' AND col2 = 'value2'`，如果复合索引是 `(col1, col2)`，索引可能失效。
索引列的顺序问题	在复合索引中，查询条件的列顺序与索引的列顺序不一致时，索引可能失效。	查询 `SELECT * FROM table WHERE col2 = 'value2' AND col1 = 'value1'`，如果复合索引是 `(col1, col2)`，索引可能失效。
索引列的空值问题	如果查询条件中包含 `IS NULL` 或 `IS NOT NULL`，索引可能失效。	查询 `SELECT * FROM table WHERE col1 IS NULL`，即使 `col1` 有索引，也可能失效。
表数据量过小	如果表的数据量非常小，MySQL可能会认为全表扫描比使用索引更高效，从而导致索引失效。	查询 `SELECT * FROM table WHERE col1 = 'value1'`，如果表只有几条记录，索引可能失效。

11.配置

当MySQL查询变慢时，可以通过调整以下配置来优化性能。以下是一些关键的MySQL配置参数及其优化建议，整理成表格形式供参考： MySQL调优配置参数

1. innodb_buffer_pool_size

描述：InnoDB存储引擎的缓存区域，用于缓存数据和索引。优化建议：增加该值以提高缓存能力，通常设置为物理内存的50%-70%。适用场景：读密集型应用，数据量较大。

2. query_cache_size

描述：查询缓存的大小（MySQL 8.0已移除）。优化建议：合理设置查询缓存大小，但需注意其在高并发场景下的局限性。适用场景：读多写少的场景，且查询重复度高。

3. max_connections

描述：允许的最大并发连接数。优化建议：根据服务器硬件资源和业务需求调整，避免过多连接导致资源耗尽。适用场景：高并发访问场景。

4. thread_cache_size

描述：用于缓存已断开连接的线程池大小。优化建议：适当增加该值以减少线程创建的开销。适用场景：高并发连接场景。

5. innodb_log_file_size

描述：InnoDB日志文件的大小。优化建议：增加日志文件大小以提高事务性能，但需注意恢复时间可能会延长。适用场景：需要高事务性能的场景。

6. innodb_flush_log_at_trx_commit

描述：控制日志写入磁盘的时机。优化建议：设置为1时保证事务的ACID特性，但性能稍差；设置为2时性能更好，但可能丢失最近的事务。适用场景：根据对数据一致性和性能的需求选择。

7. key_buffer_size

描述：MyISAM表的索引缓存大小。优化建议：对于使用MyISAM存储引擎的表，适当增加该值。适用场景：使用MyISAM表的场景。

8. tmp_table_size 和 max_heap_table_size

描述：内存中临时表的最大大小。优化建议：增加这些值以避免临时表溢出到磁盘。适用场景：需要大量临时表操作的查询。其他优化建议使用读写分离：通过主从复制实现读写分离，减轻主库压力。优化查询语句：避免使用SELECT ，减少不必要的JOIN操作，尽量使用索引。定期分析和优化表：使用ANALYZE TABLE和OPTIMIZE TABLE命令。使用缓存技术：对于读多写少的场景，可以使用Redis或Memcached等缓存系统。