十年JAVA搬砖路——MYSQL优化高级知识一

电玩女神 2023-10-15 06:51 116阅读 0赞

### MYSQL调优应该具备的知识 ###

1.  MySQL数据库基础知识：了解MySQL的架构、数据类型、索引、查询语句等基本概念和操作方法。
2.  SQL语言：熟悉SQL语言，包括查询语句的编写、优化和调试。
3.  数据库设计：了解数据库设计原则和范式，能够设计出高效的数据库结构。
4.  索引优化：掌握索引的原理和使用方法，能够根据查询需求合理创建索引，并进行索引的优化和调整。
5.  查询优化：了解查询执行计划的生成过程，能够分析查询的性能瓶颈，优化查询语句和表结构，提高查询效率。
6.  数据库性能调优：掌握数据库性能调优的方法和工具，能够定位和解决数据库性能问题，如慢查询、死锁等。
7.  数据库监控和管理：了解数据库监控和管理工具，能够监控数据库的运行状态、性能指标，并进行适时的管理和调整。

**MySQL 结构**

1.  客户端：客户端是与MySQL服务器进行通信的应用程序或工具。它可以是命令行界面、图形用户界面或Web应用程序。
2.  连接管理器：连接管理器负责处理客户端与服务器之间的连接。它接受客户端的连接请求，并将请求分配给服务器的线程池。
3.  查询解析器：查询解析器负责解析和验证客户端发送的SQL查询语句。它将查询语句转换为内部数据结构，以便后续处理。  
    （1）语法分析：MySQL会对SQL语句进行语法分析，检查语句是否符合语法规范。  
    （2）语义分析：MySQL会对SQL语句进行语义分析，检查语句是否符合数据库模式的规范。例如，检查表是否存在、列是否匹配等。
4.  优化器：优化器负责分析查询语句，并生成最优的执行计划。它会考虑索引、表关联、过滤条件等因素，以提高查询性能。  
    （3）查询优化：MySQL会对SQL语句进行查询优化，生成最优的查询计划。查询优化的过程包括索引选择、关联表顺序、过滤条件等。
5.  执行引擎：执行引擎执行优化器生成的执行计划。它负责从存储引擎中读取数据、处理数据，并返回结果给客户端。  
    （4）查询执行：MySQL会根据查询计划执行查询语句，读取数据并返回结果。
6.  存储引擎：存储引擎负责管理数据的存储和检索。MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory等，每个存储引擎都有不同的特点和适用场景。  
    （5）锁定和事务处理：MySQL会在执行查询语句的过程中，根据需要对相关的数据行进行锁定，以保证数据的一致性和完整性。如果涉及到事务处理，MySQL还会对事务进行管理和控制。
7.  日志系统：日志系统记录数据库的变更操作，包括事务日志和错误日志。它用于恢复数据库和故障排除。

### MySQL数据库支持多种隔离级别，用于控制并发事务对数据的访问和修改。以下是MySQL数据库的隔离级别： ###

**1. 读未提交**（Read Uncommitted）：最低级别的隔离级别，允许事务读取其他事务尚未提交的数据。可能导致脏读、不可重复读和幻读的问题。

**2. 读已提交**（Read Committed）：确保事务只能读取到已经提交的数据。其他事务对数据的修改在当前事务读取期间是不可见的。但仍可能导致不可重复读和幻读的问题。 （实现方式：在读操作时，只能读取已经提交事务的数据，事务在读取数据时会获取读锁，其他事务可以并发地读取相同的数据，但在写操作时需要获取写锁。在"读已提交"隔离级别下，读锁是瞬时的，也称为短暂读锁（short-lived read lock）。当一个事务读取数据时，会获取读锁，但读操作完成后立即释放读锁。这意味着其他事务可以并发地读取相同的数据，而不会被阻塞。这种读锁的释放机制允许其他事务修改已读取的数据，可能导致不可重复读和幻读的问题。 ）

**3. 可重复读**（Repeatable Read）：确保事务在整个事务期间读取的数据保持一致。其他事务对数据的修改在当前事务读取期间是不可见的。解决了不可重复读的问题，但仍可能导致幻读的问题。 （实现对读取的数据添加了写锁，和共享读锁。而在"可重复读"隔离级别下，读锁是持久的，也称为长期读锁（long-lived read lock）。当一个事务读取数据时，会获取读锁，并在整个事务过程中持有读锁，直到事务结束。这意味着其他事务无法同时获取写锁或写入已被读取的数据，保持了数据的一致性和可重复性。持久的读锁阻止了其他事务对已读取数据的修改，解决了不可重复读的问题）

**4. 串行化**（Serializable）：最高级别的隔离级别，确保事务串行执行，避免任何并发问题。事务之间的读写操作会相互阻塞，解决了脏读、不可重复读和幻读的问题。 （实现对读取的数据添加了写锁，和非共享读锁，及排它锁。）

什么是脏读： 读取了事务未提交的数据，事务回滚，读到的数据就事脏数据。  
什么是不可重复读问题：同一个事务，在两次读取同一个数据时，得到的数据不一致。原因没有加读锁，导致读取的数据被修改。

\*\*问题：\*\*在读可重复读的级别下，如果被某事务读取的全部数据，在该事务没有结束的情况下。其他并的事务是否能读取之前事务读取的数据？ 答可以。  
\*\*问题：\*\*在读可重复读的级别下，如果被某事务读取的全部数据，在该事务没有结束的情况下。其他并的事务是否能读取之前事务读取的数据并修改数据？ 答不可以。  
\*\*问题：\*\*在读可重复读的级别下，如果被某事务读取的全部数据，在该事务没有结束的情况下。其他并的事务是否能在该表插入数据，答能。