Mysql单机性能

https://support.huaweicloud.com/pwp-rds/rds_swp_mysql_09.html

对于4c8g 服务器，mysql 可承载：

500 TPS(数据库每秒执行的事务数，以COMMIT 成功次数为准)
10000 QPS(数据库每秒执行的 SQL 数)

explain执行计划

注：本节最重要的目的是根据type、key、extra三个字段判断查询是否走了索引

在执行计划中，参数有：

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）：在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。
index（全索引扫描）：index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。
range（索引范围扫描）：range 表示采用了索引范围扫描，一般在 where 子句中使用 <、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
ref（非唯一索引扫描）：ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条，因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即使有重复值，也是在一个非常小的范围内扫描。
eq_ref（唯一索引扫描）：eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
const（结果只有一条的主键或唯一索引扫描）：const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率全最快，而 eq_ref 通常用于多表联查中。

extra 显示的结果，这里说几个重要的参考指标：

Using filesort：当查询语句中包含 order by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。

Using filesort 通常出现在以下几种情况:

ORDER BY 子句中引用的列没有索引，或者没有使用到索引。

WHERE 子句和 ORDER BY 子句引用了不同的列。

ORDER BY 中的列不包含在任何可用的索引中。

ORDER BY 中的列使用了不同的排序方向(例如，一个是升序另一个是降序)

Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
Using index：所需数据只需在索引即可全部取得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

可以通过 explain 查看 SOL的执行计划，关注 type 字段，这个字段表明 SQL 扫描的方式.如果 type 字段不是all 或者 index 就代表是索引扫描的方式，这种情况就代表 SQL 走了索引，并且我们还可以通过 key 字段，看这条查询用了哪个索引字段来走索引，如果 key 为 null，也代表没有走索引。

全索引和全表遍历的都是叶子节点的那层双链表，时间复杂度都是O(n)。不同之处在于，all扫描的是主键索引树，index扫描的是二级索引树。因为全索引扫描这种方式实际上没有起到加快查询的效果，所以认为它不属于“走索引”之流。

走索引的话能二分查找，快速定位到具体的数据，时间复杂度是logn。

索引调优

几种常见优化索引的方法：

覆盖索引优化：
- 假设我们只需要查询商品名称和价格这两个数据，这时候我们可以对这两个字段建立联合索引，即「商品名称、价格」作为一个联合索引，针对 select product_id, product_name, price from table where product_name = "iphone"; 的语句，这时候就能利用覆盖索引优化了，因为索引中已经包含这两个字段数据了，所以查询将不会再次检索主键索引，从而避免回表，减少了大量的 I/O 操作。
主键索引最好是自增的：
- 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
- 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
防止索引失效：
- 当我们使用左或者右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效；
- 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
- 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
- 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
前缀索引优化：
- 使用前缀索引可以减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

总结：主键递增、前缀索引、联合覆盖、避免失效

对于只需要查询几个字段数据的 SQL 来说，我们可以对这些字段建立联合索引，这样查询方式就变成了覆盖索引，避免了回表，减少了大量的 I/O 操作。
我们的主键索引最好是递增的值，因为我们索引是按顺序存储数据的，如果主键的值是随机的值，可能会引发页分裂的现象，页分裂会导致大量的内存碎片，这样索引结构不紧凑了，就会影响查询效率。
我们要避免写出发生索引失效的 SQL 的语句，比如不要对索引进行计算、函数、类型转换操作，联合索引要能正确使用需要遵循最左匹配原则等等。
对于一些大字符串的索引，我们可以考虑用前缀索引只对索引列的前缀部分建立索引，节省索引的存储空间，提高查询性能。

SQL优化

索引覆盖（略）

还是使用联合索引使查询不要回表，此处不再赘述。

ORDER BY优化

我们查询一些数据之后，都要求对数据进行一定的排序，比如说按照更新时间来排序、根据优先级来排序等等。

// 按照更新时间来排序
SELECT *FROM t table WHERE id =1 ORDER BY update time

//根据优先级来排序，asynflow 项目拉去任务的时候的 sq1 语句
SELECT *FROM 't lark task 2' WHERE status = '1'ORDER BY order time LIMIT 1000

如果 ORDER BY 排序字段没有索引的话，mysq!会进行额外排序的操作，会损耗査询效率，如果通过 explain 会出现 using filesort，就代表有额外排序的操作，有优化的空间。

为什么 using filesort 会影响査询性能?

对“score”排序，排序是在内存排序，还是外部文件排序，要看排序的数据量是否超过 sort buffer size 大小。

内存排序:将选出来的记录，存放到【内存临时表】，然后根据对应的字段进行【快速排序】

外部文件排序:内存放不下时，会把一部分排序的结果放在磁盘上，空出内存空间，继续排序，所以外部排序般使用【归并排序】。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中，然后把这 12 个有序文件再合并成一个有序的大文件。

由于无论怎么样，排序始终属于额外的开销，因此肯定会影响查询性能~

优化分页offset x limit y

在系统需要实现分页操作的时候，通常都是用 limit 加上偏移量实现的，如果偏移量太大，就存在性能问题，比如 limit 10000,20 这样的查询，这时候 MySQL 最左叶子节点开始向右扫描 10020 条记录，时间复杂度为O(n)，然后只返回 20 条给客户端，前面 10000 条记录都将被抛弃。

如果是使用了二级索引，这种场景性能损失会加剧，因为对于前10000个不需要的数据，MySQL每次也要回表去查找，这就导致了10000次随机IO，会慢成狗。

1	select * from t_player order by score limit 10000,20

优化方式一，减少扫描次数

从业务上改进，将”第几页”改成”下一页”，先记录上一页的最后一条记录的id，然后下次就直接从该记录的位置开始扫描，这样就避免 MySQL 扫描大量不需要的行然后再抛弃掉的问题。方案如下：

---记录score为prev_score
select score from t_player order by score limit 20

-- 下一页
select score from t_player where score > prev_score order by score limit 20

优化方式二，减少回表（推荐使用）

如果要遵循第几页的方案，可以通过 SQL 的拆分，来达到目的，思路如下。这句话是说，先从条件查询中，查找数据对应的数据库唯一id值，因为主键在辅助索引上就有，所以不用回归到聚簇索引的磁盘上拉取。如此一来，offset部分均不回表查聚簇索引，只有limit出来的20 个主键id会去查询聚簇索引，这样只会20 次IO。

1 2	select * from t_player where id in (select id from t_player order by score offset 10000 limit 20)

这是一个嵌套查询：

内层查询：
1
select id from t_player order by score offset 10000 limit 20
- 从t_player表中选择ID字段
- 按score字段排序
- 跳过前10000条记录（offset 10000）
- 只取接下来的20条记录（limit 20）
- 关键点：这一步只获取ID值，如果有score的索引，则可以直接从索引中获取ID，无需回表
外层查询：
1
select * from t_player where id in (...)
- 使用内层查询返回的20个ID值
- 直接从t_player表中获取这20条记录的完整信息
- 这一步只需要对这20个ID执行回表操作

如果不使用这种优化方式，当执行SELECT * FROM t_player ORDER BY score OFFSET 10000 LIMIT 20这样的查询时，数据库需要检索并回表10020条记录（前10000条用于offset，后20条用于返回结果），这会产生10020次IO操作。

减少锁持有时间

案例一：调整事务的 sql 执行顺序

开启一个事务，事务中有更新操作和查询操作，那是先执行更新操作好？还是先执行查询操作好？

执行 update 语句的时候，会对记录加 next-key 锁（行级锁），而这个锁是在事务提交之后才释放的。

如果 update 和 select 语句之间没有什么依赖关系，那么应该把加锁操作的语句，放在事务靠后的位置，减少加锁的时间，这样能提升整体的并发性能。

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作:

1.从顾客 A 账户余额中扣除电影票价;

2.给影院 B 的账户余额增加这张电影票价;

3.记录一条交易日志。

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性,我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢?

试想如果同时有另外一个顾客 ℃ 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

这篇内容讲解的是如何优化大量数据删除操作的案例和解决方案。我来详细解释：

案例二：分批删除

当需要删除数据表中的大量数据时，直接使用 DELETE 语句进行批量删除会导致几个问题：

生成大量行锁
需要等所有数据删除完成后才释放锁
锁持有时间长
会影响其他事务的正常操作

优化方式

采用分批删除的方法，比如每次删除 1000 条记录，可以减少锁持有的时间。

具体场景

描述了一个游戏玩家登录表的场景：

表中记录玩家每次登录的信息
数据量达到了两亿多条
没有定期删除，数据累积了约一年
一个表的数据量达到了几十GB
需要删除过期数据，只保留近三个月的统计数据

问题报错

当尝试直接删除大量数据时，会遇到错误：

The total number of locks exceeds the lock table size in MySQL

这是因为要删除的数据太多，扫描的记录都会加行级锁，占用内存空间。MySQL 的 InnoDB 缓冲池默认只有 8MB（不同版本可能有所不同）。

解决方案

根据 ID 来分批删除，每次删除 10k 条数据：

第一步：找出符合删除条件（create_time < ‘2017-04-06’）的最大 ID

1	select max(id) from table_name where create_time < '2017-04-06'

第二步：按 ID 分批删除，每次删除 10k 条

1	delete from table_name where id < maxId limit 10000

这样做的优点是每次只删除 10k 条数据，而不是直接删除全部符合条件的数据，可以显著减少事务持有锁的时间，降低对系统的影响。

为什么按id删除比按索引列删除更高效？

一个是主键通常不需要回表就能直接定位，按范围删除时能减少随机IO；

还有就是避免使用非主键索引列条件删除时可能导致的范围扫描。

将HAVING过滤条件前置到WHERE

在进行在 GROUP BY 分组査询过程中，我们都会使用 WHERE 和 HAVING 来过滤条件。

WHERE和 HAVING 区别如下:

WHERE 子句在 GROUP BY 分组和聚合函数之前对数据行进行过滤，where 子句无法使用聚合函数。
HAVING 子句对 GROUP BY 分组和聚合函数之后的数据行进行过滤，having 子句可以使用聚合函数如果我们分组査询的过滤条件没有使用聚合函数的话，最好把=、><之类过滤条件放到 WHERE 条件里，因为这样可以在分组之前，提前将不符合条件的数据过滤掉了。

假设 HAVING 条件中有一个非聚合条件：

SELECT 
    customer_id, 
    SUM(amount) as total_amount
FROM 
    orders
WHERE 
    order_date >= '2023-01-01'
GROUP BY 
    customer_id
HAVING 
    COUNT(*) > 5 AND customer_id != 1001;

这里 customer_id != 1001 不是聚合条件，它完全可以在分组前就过滤掉。

优化后的查询：

SELECT 
    customer_id, 
    SUM(amount) as total_amount
FROM 
    orders
WHERE 
    order_date >= '2023-01-01' AND customer_id != 1001
GROUP BY 
    customer_id
HAVING 
    COUNT(*) > 5;

优化效果：

在第一阶段（WHERE）就过滤掉了客户ID为1001的订单，减少了参与分组和聚合计算的数据量
减轻了分组和聚合的计算负担
提高了查询性能，尤其是在数据量大的情况下

优化前后的主要区别对应于图中所示：

优化前：所有符合date条件的数据都会进入groupby阶段，然后才在having阶段筛选
优化后：customer_id条件被前置到where中，这样客户ID为1001的数据在分组前就被过滤掉了，减少了参与分组的数据量

这种优化的核心思想是：在能够提前过滤的情况下，尽量减少参与后续复杂操作（如分组和聚合）的数据量。

总结

Mysql性能优化

这里只是要知道，Mysql优化并不局限于sql优化，还有表结构优化、字段优化、索引结构优化即可。

原文链接：https://juejin.cn/post/7163894728201601060#heading-8

所谓的MySQL优化，主要是指三方面，即表结构、字段结构以及索引结构，这些结构如果不合理，在某些场景下也会影响数据库的性能，因此优化时也可以从结构层面出发，但对于结构的优化，一般在项目的库表设计之初就要考虑，当性能瓶颈出现时再调整结构，早就为时过晚。

这是为啥呢？因为如果在项目之初没考虑好，到了性能瓶颈出现时再去更改，由于表中已经存在数据，所以发生结构变更时，势必会由于已经存在数据，而产生一系列的连锁问题出现，也有可能会出现一些棘手问题难以推进优化方案的落地。

表结构的优化

①表结构设计时的第一条也是最重要的一条，字段数量一定不要太多，一些老项目里面30~40个字段的表比比皆是，更有甚者达到了60~70个字段一张表，这种方式显然并不合理，因为InnoDB引擎基本上都会将数据操作放到内存中完成，而当一张表的字段数量越多，那么能载入内存的数据页会越少，当操作时数据不在内存，又不得不去磁盘中读取数据，这显然会很大程度上影响MySQL性能。

咱们思考一个问题：一张40~50个字段的表结构，在实际业务中会使用到它每个字段吗？答案是No，一张40~50个字段的表中，常用的字段最多只有10~20个，这类字段可以理解成热点字段，而其他的字段都属于冷字段，但由于你将所有字段都设计到了一张表中，因此就会导致载入内存时，会将一整条数据全部载入，对应的冷字段会造成额外的额外的内存浪费。

一张表最多最多只能允许设计30个字段左右，否则会导致查询时的性能明显下降。

因此对于表结构的设计，正常情况下应当遵循“数据库三范式”的原则设计，尽可能的根据业务将表结构拆分的更为精细化，一方面能够确保内存中缓存的数据更多，同时也更便于维护，而且执行SQL时，效率也会越高。

②当然，也并不是说一定要遵守三范式的设计原则，有时候经常做连表查询的字段，可以适当的在一张表中冗余几个字段，这种做法的最大好处是能够减少连表查询次数，用空间换时间的思想。但也并非是无脑做冗余，否则又会回到前面的情况，一张表中存在大量的无用字段，这里的冗余是指经常连表查询的字段。

只有当迫不得已的情况下，再考虑使用其他类型的字段作为主键，但也至少需要保持递增性，比如分布式系统中的分布式ID，这种情况下就无法依靠数据库int自增去确保唯一性，就必须得通过雪花算法这类的ID生成策略，以此来确保ID在全局的唯一性。

③对于实时性要求不高的数据建立中间表。很多时候咱们为了统计一些数据时，通常情况下都会基于多表做联查，以此来确保得到统计所需的数据，但如若对于实时性的要求没那么高，就可以在库中建立相应的中间表，然后每日定期更新中间表的数据，从而达到减小连表查询的开销，同时也能进一步提升查询速度。

啥叫中间表呢？举个最简单的例子，比如排名类的统计业务，就可以这么实现，好比MOBA游戏中的战力排名，以英雄联盟、王者荣耀为例，由于每个玩家的战力在一天内都会不断变化，同时一个用户在任何时间段都有可能去查询战力排名，所以每次查询都基于数据库的多张表去联查，基于这些游戏的用户量而言，其带来的开销必然的巨大的，因此可以对英雄战力设计一张中间表，每日凌晨五点统计一次…..

上述这种做法也是大多数MOBA游戏的实现方式，但实际场景中也会结合Redis来实现，毕竟这种方式速度会更快，但这里就不多拓展了，总之记住一点即可：适当的场景下建立中间表，是一种能够带来不小性能收益的手段。

④根据业务特性为每张不同的表选择合适的存储引擎。其实存储引擎这块主要是在InnoDB、MyISAM两者之间做抉择，对于一些经常查询，很少发生变更的表，就可以选择MyISAM引擎，比如字典表、标签表、权限表….，因为读远大于写的表中，MyISAM性能表现会更佳，其他的表则可以使用默认的InnoDB引擎。

字段结构的优化

字段结构的优化其实主要指选择合适的数据类型，大多数开发在设计表字段结构时，如果要使用数值类型一般会选择int，使用字符串类型一般会选择varchar，但这些字段类型可以适当的做些调整，例如：

对于姓名字段，一般都会限制用户名长度，这时不要无脑用varchar，使用char类型更好。
对于一些显然不会拥有太多数据的表，主键ID的类型可以从int换成tinyint、smallint、mediumit。
对于日期字段，不要使用字符串类型，而更应该选择datetime、timestamp，一般情况下最好为后者。
对于一些固定值的字段，如性别、状态、省份、国籍等字段，可以选择使用数值型代替字符串，如果必须使用字符串类型，最好使用enum枚举类型代替varchar类型。
.......

总之在选择字段的数据类型时有三个原则：

①在保证足够使用的范围内，选择最小数据类型，因为它们会占用更少的磁盘、内存和CPU缓存，同时在处理速度也会更快。
②尽量避免索引字段值为NULL，定义字段时应尽可能使用NOT NULL关键字，因为字段空值过多会影响索引性能。
③在条件允许的情况下，尽量使用最简单的类型代替复杂的类型，如IP的存储可以使用int而并非varchar，因为简单的数据类型，操作时通常需要的CPU资源更少。