一、服务器处理客户端请求

image.png
注意：mysql8.0版本中查询缓存去掉
image.png

  • 连接层：验证用户名和密码等身份认证。image.png
  • 服务层：sql interface:sql接口，接收命令并且返回结果；parse解析器：语法、语义解析；optimizer优化器：生成执行计划；caches & buffers:查询缓存组件，5.7版本中会先从缓存组件中进行语句查询，如果有则直接返回，没有则继续语法解析，存储引擎执行之后得到的数据会先缓存在缓存组件中，达到一定数量时再返回给客户端。

  • 引擎层：对sql语句对数据库进行相关操作。

    二、sql执行流程

    3、MySQL高级部分 - 图4

  • 查询缓存：是否打开query_cache_type，如果打开查看是否命中缓存，是的话直接返回结果。mysql8.0抛弃该功能，命中率太低，语句增加一个空格都无法命中，函数的调用和修改表结构和数据时会触发缓存失效。 ```sql — 查看是否开启查询缓存 show variables like ‘%query_cache_type’; — 在配置文件中开启查询缓存 0-关闭查询缓存 1-开启 2-demand(按需使用) query_cache_type=2

— 监控查询缓存命中率 show status like’%Qcache’;

  2. - 解析器：在解析器中对sql语句进行语法分析、语义分析。分析出关键字、表名等，根据语法分析器来判断sql是否满足mysql语法。
  3. - 优化器：确定sql执行成本最低，选择索引操作，选择出最好的执行计划。分为逻辑查询优化(sql写法)和物理查询优化(索引选择、表连接) 。
  4. - 执行器：判断用户是否有权限，调用存储引擎api将结果返回到查询缓存，全部查询完毕返回给客户端。
  5. ```sql
  6. -- 查看是否开启记录sql执行过程
  7. select @@profiling;
  8. show variables like 'profiling';
  9. -- 临时修改 永久修改设置在配置文件中
  10. set profiling=1;
  12. -- 查看最近执行语句
  13. show profiles;
  15. -- 查看指定语句执行情况
  16. show profile for query 7;

三、缓冲池

  • innodb存储形式为数据页形式，默认16kb大小；
  • innodb缓冲池包括数据页、索引页、插入缓存、锁信息、自适应Hash和数据字典等信息；
  • 缓存原则：“位置*频次”，位置决定效率，在内存中直接访问数据；频次决定优先级，缓冲池与内存存储大小小于磁盘数据时，优先使用频次高的热数据进行加载；
  • 预读特性：缓冲池内存允许的情况下，预先读取数据页前后数据事先缓存在缓冲池中；
  • 缓冲池读取数据：

image.png

  1. -- 查看缓存池大小
  2. show variables like '%innodb_buffer_pool_size%';

  • 多线程情况下访问bufferPool需要加锁，可以一个缓冲池拆解成多个小的缓存池

    1. -- 启动服务器时设置个数
    2. [server]
    3. innodb_buffer_pool_instances = 2
    4. -- 查看缓冲池个数
    5. show variables like 'innodb_buffer_pool_instances';

  • 脏数据/脏页：在更新操作时，缓冲池不存在该数据时需要从磁盘中读取数据，在缓冲池进行修改，修改完后缓冲池内数据与磁盘数据不一致；

    四、存储引擎

    1. -- 查看存储引擎
    2. show engiens;
    3. -- 设置存储引擎(临时)
    4. set default_storage_engine=MyISAM;
    5. -- 设置存储引擎(永久)
    6. default-storage-engine=MyISAM;
    7. -- 重启服务
    8. systemctl restart mysqld.service;

  • innodb支持外键、事务，在5.5版本后默认为存储引擎，具有行锁，高并发时效率高。对内存要求高，处理增加和查询效率低，占用磁盘空间存储索引和数据。

  • myisam不支持外键、事务，只支持表级锁，崩溃后无法恢复，5.5之前默认存储引擎，select和insert速度比innodb大，数据和索引分开存储，数据量不大的时候读和写建议使用myisam。
  • archive用于数据存档，5.5之后支持索引，不支持修改，压缩性能好，使用行级锁，适合日志和数据采集类应用。
  • csv可以转换为csv文件，数据交换机制