← 返回学习路线

SYSTEM

执行流程

Java基础执行流程system
src/content/notes/system/执行流程.md

执行流程

查询缓存

工作流程

当执行完全相同的 SQL 语句的时候,服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存

查询过程:

  1. 客户端发送一条查询给服务器
  2. 服务器先会检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果(一般是 K-V 键值对),否则进入下一阶段
  3. 分析器进行 SQL 分析,再由优化器生成对应的执行计划
  4. 执行器根据优化器生成的执行计划,调用存储引擎的 API 来执行查询
  5. 将结果返回给客户端

大多数情况下不建议使用查询缓存,因为查询缓存往往弊大于利

  • 查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。因此很可能费力地把结果存起来,还没使用就被一个更新全清空了,对于更新压力大的数据库来说,查询缓存的命中率会非常低
  • 除非业务就是有一张静态表,很长时间才会更新一次,比如一个系统配置表,那这张表上的查询才适合使用查询缓存

缓存配置
  1. 查看当前 MySQL 数据库是否支持查询缓存:

    SHOW VARIABLES LIKE 'have_query_cache';	-- YES
  2. 查看当前 MySQL 是否开启了查询缓存:

    SHOW VARIABLES LIKE 'query_cache_type';	-- OFF

    参数说明:

    • OFF 或 0:查询缓存功能关闭

    • ON 或 1:查询缓存功能打开,查询结果符合缓存条件即会缓存,否则不予缓存;可以显式指定 SQL_NO_CACHE 不予缓存

    • DEMAND 或 2:查询缓存功能按需进行,显式指定 SQL_CACHE 的 SELECT 语句才缓存,其它不予缓存

      SELECT SQL_CACHE id, name FROM customer; -- SQL_CACHE:查询结果可缓存
      SELECT SQL_NO_CACHE id, name FROM customer;-- SQL_NO_CACHE:不使用查询缓存
  3. 查看查询缓存的占用大小:

    SHOW VARIABLES LIKE 'query_cache_size';-- 单位是字节 1048576 / 1024 = 1024 = 1KB
  4. 查看查询缓存的状态变量:

    SHOW STATUS LIKE 'Qcache%';
    参数 含义
    Qcache_free_blocks 查询缓存中的可用内存块数
    Qcache_free_memory 查询缓存的可用内存量
    Qcache_hits 查询缓存命中数
    Qcache_inserts 添加到查询缓存的查询数
    Qcache_lowmen_prunes 由于内存不足而从查询缓存中删除的查询数
    Qcache_not_cached 非缓存查询的数量(由于 query_cache_type 设置而无法缓存或未缓存)
    Qcache_queries_in_cache 查询缓存中注册的查询数
    Qcache_total_blocks 查询缓存中的块总数
  5. 配置 my.cnf:

    sudo chmod 666 /etc/mysql/my.cnf
    vim my.cnf
    # mysqld中配置缓存
    query_cache_type=1

    重启服务既可生效,执行 SQL 语句进行验证 ,执行一条比较耗时的 SQL 语句,然后再多执行几次,查看后面几次的执行时间;获取通过查看查询缓存的缓存命中数,来判定是否走查询缓存


缓存失效

查询缓存失效的情况:

  • SQL 语句不一致,要想命中查询缓存,查询的 SQL 语句必须一致,因为缓存中 key 是查询的语句,value 是查询结构

    select count(*) from tb_item;
    Select count(*) from tb_item;	-- 不走缓存,首字母不一致
  • 当查询语句中有一些不确定查询时,则不会缓存,比如:now()、current_date()、curdate()、curtime()、rand()、uuid()、user()、database()

    SELECT * FROM tb_item WHERE updatetime < NOW() LIMIT 1;
    SELECT USER();
    SELECT DATABASE();
  • 不使用任何表查询语句:

    SELECT 'A';
  • 查询 mysql、information_schema、performance_schema 等系统表时,不走查询缓存:

    SELECT * FROM information_schema.engines;
  • 在跨存储引擎的存储过程、触发器或存储函数的主体内执行的查询,缓存失效

  • 如果表更改,则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除,包括使用 MERGE 映射到已更改表的表的查询,比如:INSERT、UPDATE、DELETE、ALTER TABLE、DROP TABLE、DROP DATABASE


分析器

没有命中查询缓存,就开始了 SQL 的真正执行,分析器会对 SQL 语句做解析

SELECT * FROM t WHERE id = 1;

解析器:处理语法和解析查询,生成一课对应的解析树

  • 先做词法分析,输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是什么代表什么。从输入的 select 这个关键字识别出来这是一个查询语句;把字符串 t 识别成 表名 t,把字符串 id 识别成列 id
  • 然后做语法分析,根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果语句不对,就会收到 You have an error in your SQL syntax 的错误提醒

预处理器:进一步检查解析树的合法性,比如数据表和数据列是否存在、别名是否有歧义等


优化器

成本分析

优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序

  • 根据搜索条件找出所有可能的使用的索引
  • 成本分析,执行成本由 I/O 成本和 CPU 成本组成,计算全表扫描和使用不同索引执行 SQL 的代价
  • 找到一个最优的执行方案,用最小的代价去执行语句

在数据库里面,扫描行数是影响执行代价的因素之一,扫描的行数越少意味着访问磁盘的次数越少,消耗的 CPU 资源越少,优化器还会结合是否使用临时表、是否排序等因素进行综合判断


统计数据

MySQL 中保存着两种统计数据:

  • innodb_table_stats 存储了表的统计数据,每一条记录对应着一个表的统计数据
  • innodb_index_stats 存储了索引的统计数据,每一条记录对应着一个索引的一个统计项的数据

MySQL 在真正执行语句之前,并不能精确地知道满足条件的记录有多少条,只能根据统计信息来估算记录,统计信息就是索引的区分度,一个索引上不同的值的个数(比如性别只能是男女,就是 2 ),称之为基数(cardinality),基数越大说明区分度越好

通过采样统计来获取基数,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数

在 MySQL 中,有两种存储统计数据的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:

  • ON:表示统计信息会持久化存储(默认),采样页数 N 默认为 20,可以通过 innodb_stats_persistent_sample_pages 指定,页数越多统计的数据越准确,但消耗的资源更大
  • OFF:表示统计信息只存储在内存,采样页数 N 默认为 8,也可以通过系统变量设置(不推荐,每次重新计算浪费资源)

数据表是会持续更新的,两种统计信息的更新方式:

  • 设置 innodb_stats_auto_recalc 为 1,当发生变动的记录数量超过表大小的 10% 时,自动触发重新计算,不过是异步进行
  • 调用 ANALYZE TABLE t 手动更新统计信息,只对信息做重新统计(不是重建表),没有修改数据,这个过程中加了 MDL 读锁并且是同步进行,所以会暂时阻塞系统

EXPLAIN 执行计划在优化器阶段生成,如果 explain 的结果预估的 rows 值跟实际情况差距比较大,可以执行 analyze 命令重新修正信息


错选索引

采样统计本身是估算数据,或者 SQL 语句中的字段选择有问题时,可能导致 MySQL 没有选择正确的执行索引

解决方法:

  • 采用 force index 强行选择一个索引

    SELECT * FROM user FORCE INDEX(name) WHERE NAME='seazean';
  • 可以考虑修改 SQL 语句,引导 MySQL 使用期望的索引

  • 新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引


执行器

开始执行的时候,要先判断一下当前连接对表有没有执行查询的权限,如果没有就会返回没有权限的错误,在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。如果有权限,就打开表继续执行,执行器就会根据表的引擎定义,去使用这个引擎提供的接口


引擎层

Server 层和存储引擎层的交互是以记录为单位的,存储引擎会将单条记录返回给 Server 层做进一步处理,并不是直接返回所有的记录

工作流程:

  • 首先根据二级索引选择扫描范围,获取第一条符合二级索引条件的记录,进行回表查询,将聚簇索引的记录返回 Server 层,由 Server 判断记录是否符合要求
  • 然后在二级索引上继续扫描下一个符合条件的记录

推荐阅读:https://mp.weixin.qq.com/s/YZ-LckObephrP1f15mzHpA