从零到MySQL性能优化大师

关注

原创 2023-09-14 09:25:55 542 阅读 AI 写同款· GEO 优化 ›

Python 100天，从新手到大师——Python数据库性能优化秘籍，深入理解MySQL索引（day43） MySQL索引优化指南：Python开发者必备摘要本文深入探讨MySQL索引在Python应用中的优化实践。主要内容包括：索引原理：通过B+树数据结构解析，展示索引如何将查询复杂度从O(n)降至O(log n) 索引类型对比：聚集索引（主键索引）直接存储数据非聚集索引需要"回表"查询 Python伪代码演示了两者的性能差异实战操作：使用pymysql库创建和管理索引提供索引性能测试方法展示索引对查询速度的显著提升优化建议：为高频查询字段创建索引避免过度索引注意索阅读详情

一.MySQL架构

二.优化与执行

执行计划是MySQL优化器为了优化查询而生成的一种数据结构，它记录了数据库系统执行查询时所采取的操作流程，即对查询语句的各部分如何进行处理以最终得到查询结果的过程。执行计划通常被表示为一棵树状结构，节点代表不同的操作符(operator)，叶子节点代表访问底层数据的方式，例如表扫描或索引查找等。

MySQL解析查询以创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊关键字向优化器传递提示，从而影响优化器的决策过程。也可以请求服务器解释优化过程的各个方面，使用户可以知道服务器是如何进行优化决策的，并提供一个参考点，便于用户重构查询和schema、修改相关配置，使应用尽可能高效地运行。

在旧版本中，MySQL可以使用内部查询缓存(query cache)来查看是否可以直接提供结果。但是，随着并发性的增加，查询缓存成为一个让人诟病的瓶颈。从MySQL 5.7.20版本开始，查询缓存已经被官方标注为被弃用的特性，并在8.0版本中被完全移除。尽管查询缓存不再是MySQL服务器的核心部分，但缓存被频繁请求的结果集依然是一个很好的实践，一个流行的设计模式是在memcached或Redis中缓存数据

三.并发控制+锁的类型与力度

无论何时，只要有多个查询需要同时修改数据，就会产生并发控制问题，比如一张表我们读取或者修改某一行或者多行的时候，其它的用户也在进行同样的操作，那么是可以同时进行还是必须排队呢

锁的类型

并发控制这一经典问题的解决方案相当简单。处理并发读/写访问的系统通常实现一个由两种锁类型组成的锁系统。这两种锁通常被称为共享锁(shared lock)和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)

锁的粒度

表锁(table lock)是MySQL中最基本也是开销最小的锁策略。它会锁定整张表，当客户端想对表进行写操作（插入、删除、更新等）时，需要先获得一个写锁，这会阻塞其他客户端对该表的所有读写操作。只有没有人执行写操作时，其他读取的客户端才能获得读锁，读锁之间不会相互阻塞

行级锁(row lock)可以最大程度地支持并发处理（也带来了最大的锁开销）。回到电子表格的类比，行级锁等同于锁定电子表格中的某一行。这种策略允许多人同时编辑不同的行，而不会阻塞彼此。这使得服务器可以执行更多的并发写操作，带来的代价则是需要承担更多开销，以跟踪谁拥有这些行级锁、已经锁定了多长时间、行级锁的类型，以及何时该清理不再需要的行级锁

四.事务

上面的一致性可能说的有点难懂看看下面解释

示例1 ：转账

在这一系列操作中，有更多的失败可能性。连接可能会断开、会超时，甚至数据库服务器在操作执行过程中会崩溃。这就是为什么存在高度复杂且缓慢的两阶段提交系统的典型原因：为了应对各种失败场景

示例2 使用savepoint

五.脏读与幻读

脏读:读取未提交的数据，也称为脏读(dirty read)

幻读:指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(phantom row)

六.隔离级别

READ UNCOMMITTED（未提交读）:在事务中可以查看其他事务中还没有提交的修改,会导致很多问题,除非有非常必要的理由，在实际应用中一般很少使用弊大于利,直接导致脏读

READ COMMITTED（提交读）:事务提交之前，其所做的任何修改对其他事务都是不可见的,

这个级别仍然允许不可重复读(nonrepeatable read)，这意味着同一事务中两次执行相同语句，可能会看到不同的数据结果

REPEATABLE READ（可重复读）:保证了在同一个事务中多次读取相同行数据的结果是一样的,

但是还是无法解决幻读的问题.

SERIALIZABLE（可串行化）:SERIALIZABLE是最高的隔离级别。该级别通过强制事务按序执行，使不同事务之间不可能产生冲突，从而解决了前面说的幻读问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，除非需要严格确保数据安全且可以接受并发性能下降的结果。