1.MySQL体系结构

1.1连接层

最上层是连接服务，包含本地sock通信、客户端与服务端之间建立的网络连接即TCP/IP通信。主要工作是连接处理、授权认证，同样在该层可以实现基于SSL的安全连接。为了高效的处理并发请求，MySQL在连接层引入了线程池技术，每个连接从线程池中获取，避免了连接的频繁创建和销毁的开销。

1.2服务层

MySQL服务层完成了MySQL的大部分工作，处理来自连接层的SQL命令，完成语法解析、权限检查和查询优化，并提供查询缓存机制以提高性能。当服务层的SQL接口接收到SQL命令后，解析器会对SQL语句进行语法和语义解析，生成语法树，当然在这个过程中，会对用户的权限进行验证，确保用户有执行该语句的权限。解析器完成后，查询优化器开始工作。查询优化器主要是确定SQL的最佳执行路径，生成一个高效的执行计划。优化后的执行计划传递给执行引擎，确保SQL最快的运行。查询缓存也是在服务层，存储SELECT语句的执行结果。如果在查询缓存中有一样的查询语句，则直接返回执行结果给客户端，省去了解析、优化和执行的步骤。需要注意的是，查询缓存在MySQL8版本中已被移除。为什么查询缓存会被移除，是因为在高并发、频繁修改的场景下效果不好，而且也占用了大量内存。

1.3引擎层

与磁盘进行交互，负责实际数据的存储和提取操作，注意数据库的索引也是在引擎层实现的。存储引擎有InnoDB，支持事务处理、行级锁定，适用于高并发和数据完整性的场合。MyISAM存储引擎适合于只读和大量读取的应用，比如新闻系统。在数据之外，MySQL还维持这满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。由于索引也很大，所以索引往往也是以索引文件的形式存储在磁盘上。常见索引结构有B+树、Hash索引，一般默认的索引是B+树。使用索引的好处有提高数据检索的效率，也就降低了数据库的IO成本。通过索引列排序，提高了数据排序效率，也就降低了数据库的CPU成本。索引的缺点也有，第一个就是占用磁盘空间，第二是进行DML操作时，也需要去更新索引文件，增加了操作成本。

2.SQL执行过程

1. 通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限
2. 服务器首先检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。
3. 服务器进行SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。
4. MySQL的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。
5. 服务器将查询的结果返回客户端。

3.性能分析

3.1 explain关键字

使用explain关键字可以模拟查询优化器执行SQL语句，从而分析出性能瓶颈。

id

表示查询中表的读取顺序。

id相同，执行顺序由上而下。id不同，执行顺序从大到小。

select_type

table

查询涉及的表名

type

查询的类型，从最好到最坏依次是：system > const > eq_ref > ref > range > index > ALL，一般来说得保证查询达到range级别，最好能达到ref

possible_keys

显示可能被应用到的索引，一个或多个

key

实际使用的索引

key_len

索引使用的字节数，长度越短越好

ref

表之间的引用，也可能是const

rows

估算出来的查询操作需要读取的行数，当然越小越好

Extra

额外的信息

3.2 排序

MySQL排序算法有两种双路排序和单路排序。双路排序就是首先读取到行指针和ORDER BY列，在buffer区进行排序，然后在从磁盘读取对应数据进行输出。单路排序就是从磁盘读取查询需要的所有列，在buffer区进行排序，然后直接输出。单路排序如果数据量过大会有问题，导致每次只能取出sort_buffer容量大小的数据，进行排序、创建tmp文件、多路合并，从而导致大量I/O操作。什么时候使用单路排序，什么时候使用多路排序？当查询字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序，否这使用多路排序。单路排序是后面出来的，相对于多路排序更好，所以要适当调大max_length_for_sort_data的值。两种算法的数据都可能超出sort_buffer_size的值，超出后会创建tmp文件，然后进行合并排序，导致多次I/O操作，这种情况对单路排序影响更大，所以需要适当提高sort_buffer_size的值。查询max_length_for_sort_data和sort_buffer_size的值可以使用以下SQL查询

SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'max_length_for_sort_data';

要修改这两个值可以在my.cnf配置文件中配置

[mysqld]
sort_buffer_size = 新的值
max_length_for_sort_data = 新的最大长度值

还需要注意一点，能写在where限定的条件就不要去having限定了。是因为WHERE子句在聚合之前执行，它可以有效地减少需要处理的数据量。这通常会导致更好的性能，尤其是在处理大量数据时。

3.3慢查询日志

MySQL 慢查询日志是记录执行时间超过指定阈值的 SQL 语句的日志文件。通过分析慢查询日志，可以找出执行效率低下的 SQL 语句，从而进行优化。在my.cnf配置文件中添加慢查询配置，long_query_time的默认值为10秒。

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

日志数据量大，可以使用MySQL自带的日志分析工具mysqldumpslow去分析日志

mysqldumpslow常用参数

• s：是表示按照何种方式排序
• c：访问次数
• l：锁定时间
• r：返回记录
• t：查询时间
• al：平均锁定时间
• ar：平均返回记录数
• at：平均查询时间
• t：即为返回前面多少条的数据
• g：后边搭配一个正则匹配模式，大小写不敏感的

示例如下

获取返回记录最多的十条SQL

mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

获取访问次数最多的十条SQL

mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

3.4Profile

通过Profile，可以详细了解一个查询在执行期间的各个阶段所花费的时间和资源，从而帮助数据库管理员和开发者找出性能瓶颈并进行优化。MySQL的Profile提供了详细的诊断信息，包括CPU使用情况、上下文切换次数、阻塞操作等。使用如下SQL可以查看Profile状态

SHOW VARIABLES LIKE '%profil%';

在my.cnf配置文件中添加一下配置可以开启Profile

[mysqld]
profiling = ON

执行一系列业务SQL后，可以通过如下命令查看SQL的执行耗时

-- 查看历史执行SQL的耗时情况
show profiles;

-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

3.5 SQL优化

limit优化

在数据量大的时候，越往后查询分页查询的效率就越低。因为当使用分页查询的时候，需要查询出当前页数据和之前的数据并对这些数据做一个排序，但是仅仅只返回当前页数据，其他记录则丢弃，因此代价很高。优化思路是通过覆盖索引加子查询形式进行优化。比如以下SQL

select * from vs_meeting_pm vmp, (select id from vs_meeting_pm order by id limit 2000000,10) a where vmp.id = a.id;

count优化

按照效率排序依次为：count(字段) < count(主键 id) < count(1) < count(*)，所以尽量使用count(*)

• COUNT(字段)：扫描全表，取值传到server层，判断值不为空后累加
• COUNT(主键id)：扫描全表，取值传到server层，直接累加
• COUNT(1)：扫描全表，但不取值，server层收到的每一行都是1，按值累加。
• COUNT(*)：扫描全表，但不取值，按行累加

update优化

在MySQL的InnoDB存储引擎中，执行UPDATE语句修改非索引列时可能会从行锁升级为表锁，这主要是由于MySQL的锁机制和查询优化器的工作方式。当执行UPDATE语句时，如果WHERE条件没有使用索引，MySQL需要进行全表扫描以查找需要更新的行。在这种情况下，为了保护数据一致性并防止其他事务同时修改数据，MySQL会对所有行施加next-key锁（记录锁和间隙锁的组合），这实际上锁定了整个表，以防止幻读现象的发生。可以采用以下优化策略

• 确保WHERE子句使用合适的索引：这样MySQL可以通过索引快速定位到需要更新的行，而不需要全表扫描。
• 调整sql_safe_updates参数：设置为1时，如果UPDATE或DELETE语句没有WHERE子句，或者WHERE子句没有包含索引的列，则会报错，从而提高数据库的安全性。