乐趣区

关于mysql:mysq从0到01SQL是怎么执行的上

jiezi

select 语句执行流程

流程

  1. 创立连贯 ，先连贯到数据库，通过连接器连贯到客户端（TCP 握手链接）
    此时会获取用户的权限，并且权限获取后，如果批改权限，不会影响以后连贯。并且链接的默认有效期是 8 小时，到期之后会主动断开，默认应用长连贯。然而因为长连贯内存占用大，会导致 mysql 内存涨得比拟快，导致 OOM。目前常见的解决方案是应用连接池。
    MySQL 在执行过程中长期应用的内存是治理在连贯对象外面的导致连贯内存大
  2. 查问缓存
    建设连贯后，进行一个查问申请，会先查问 mysql 的查问缓存。执行的 sql 作为 key，上次查问的后果作为 value。如果缓存命中，则间接返回。如果语句不在查问缓存中，就会持续前面的执行阶段。然而因为缓存的生效条件是 只有表上有一次更新，就会淘汰这个表上所有的缓存，所以缓存的命中率会非常低。MySQL 8.0 版本间接将查问缓存的整块性能删掉了，也就是说 8.0 开始彻底没有这个性能了
  3. 解析器
    如果在查问缓存的步骤中没有查问到缓存（8.0 之前），则进行这一步。这里解析器会进行 SQL 语句的解析，外部将文本格式转换为二进制构造，把关键字解析进去，而后会判断的你的 SQL 是否合乎语法。
    解析器次要性能有：

通过这两个性能会产生一棵解析树相似于 java 的词法剖析 留神这里的解析树不是个别的字节代码，而是 C /C++ 构造 这里不对解析树进行深刻解说，如果想理解能够看这篇博文。

MySQL 内核源码解读 -SQL 解析之解析器浅析
这里会产生一个很多人常常碰到的谬误。

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xxxxx' at line 1

这个谬误就示意，你的 SQL 存在语法问题。

* 词法剖析(Lexical scanner)：作用是将整个查问合成为多个元素。* 语法规定(Grammar rule module)：寻找 sql 语法规定组合，产生一个序列，执行这些规定相干的代码。
  1. 优化器
    优化器算是整个流程中最重要的一个局部，整个 SQL 的索引抉择，JOIN 查问的表连贯程序，依附它来实现。特地是在多索引的环境下，会影响到整个 SQL 的效率。在上面我会具体的说。
  2. 执行器
    执行器，顾名思义就是用来真正执行 SQL 的货色。第一步会先判断你有没有被操作表的权限，如果没有会报错。
 ERROR 1142 (42000): SELECT command denied to user 'xx'@'localhost' for table 'xx'

如果有权限就会进入表，执行查问语句。而依据优化对于索引的抉择不同又有不同的查问逻辑。

优化器是怎么抉择索引的

优化器是 MySQL 比较复杂的一个组件，一条语句在后果雷同的状况下个别会有多种执行形式，而优化器则是找到多种执行形式中最优的一个。

查问优化程序有几个指标，然而其次要指标是∶尽可能应用索引，并且应用最严格的索引来打消对行数量随时可能疾速减少的顾虑。——《MySQL 技术底细》

在这里，咱们能够通过这个 SQL 去查看咱们上次查问的老本，它是 io\_cost 和 cpu\_cost 的开销总和

show status like 'Last_query_cost';

后果示意 MySQL 的优化器认为大略须要做 3.399 个数据页的随机查找能力实现下面的查问。

对于这个老本在《高性能 MySQL》是这样形容的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引散布状况。优化器在评估老本的时候并不思考任何层面的缓存，它假如读取任何数据都须要一次磁盘 I/O。

这句话能够总结为 统计后果仅供参考，会和理论的老本有差异，最间接的就是，InnoDB 因为 MVCC 导致每个视图统计的行数不一样，所以老本也会不一样。

咱们在理论应用中常常产生这样一种状况，当咱们在一个表上有多个索引时，咱们冀望 SQL 执行的时候应用 idx\_table\_a，然而 mysql 却抉择了 idx\_table\_b。当然这是因为优化器认为应用 idx\_table\_b 比 idx\_table\_a 成果更好，这里就波及到优化器如何判断一个 SQL 应用哪个索引最优。

十分倡议大家去看看这篇博文，十分讲究的做了对于优化器的试验。

MySQL 索引抉择准则

文中是基于 MySQL 5.5 进行的试验，前面的版本是否有变动待考据。

所有依据下面博文的说法，大略能够分为这几个规定

  1. 对无过滤条件、索引能够笼罩的查问。查问优化器抉择笼罩索引键值最短的索引进行查问;
  2. 对无过滤条件、无索引笼罩的查问。查问优化器抉择全表扫描；
  3. 对有过滤条件、索引能够笼罩的查问。查问优化器优先基于代价的形式对过滤条件进行解决。如果能够索引查找，将抉择代价最低的索引进行查找。如果是全表扫描，则通过查找键值最短的笼罩索引进行查问，并通过过滤条件进行过滤。
  4. 对有过滤条件、无索引笼罩的查问。查问优化器基于代价的形式对过滤条件进行解决，生成查问打算。

这里次要代价的就是 扫描行数，扫描的行数越少，意味着拜访 IO 次数越少（随机 IO），耗费的 CPU 资源越少。当然影响老本的也不止扫描行数，优化器还会联合是否应用长期表、是否排序等因素进行不同的优化流程。能够参考《高性能 MYSQL》第六章，这里不开展。

接下来咱们来看看 扫描行数 的问题，其实 MySQL 并不能精确的晓得扫描行数，只能依据 基数 来判断。

列的基数（cardinality）是指它所包容的所有非反复值的个数。——《MySql 技术底细》

例如，某个列蕴含值 1，3，7，4，7，3，那么它的基数为 4。绝对干表里行的总数来说，列的基数越高（也就是说，它蕴含的惟一值多，反复值少），索引的应用成果越好。

咱们能够应用 show index from table 这个 SQL 去看这个表的基数。

咱们这里无妨来做个测试，先创立一张表。

CREATE TABLE `demo`.`test` (`id` INT(11) UNSIGNED NOT NULL,
    `a` INT(11) UNSIGNED NULL,
    `b` INT(11) UNSIGNED NULL,
    `c` INT(11) UNSIGNED NULL,
    PRIMARY KEY (`id`),
    KEY `idx_a`(`a`) USING BTREE,
    KEY `idx_b`(`b`) USING BTREE,
    KEY `idx_c`(`c`) USING BTREE
)    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_general_ci;

而后再进行填充数据，这里我间接用华为云的网页端去生成测试数据。

在这里我生成了 100000 行数据，这里的规定就是从 0 开始减少步长为 1，所以 a，b，c，字段的数据完全一致，而后我咱们用 show index from test 这个语句来看一看整个表的基数。

cardinality 这个字段就是不同索引的基数，咱们能够显著的看进去 B 的基数和其余的不一样，并且正确的值都应该是 100000。

ps：如果应用的是阿里云数据库进行这个测试，可能会遇到无奈复现的状况，起因是阿里云的数据库 innodb\_flush\_log\_at\_trx\_commit 参数设置的是 2，导致插入数据并没有刷盘，这里波及到 WAL 的概念，我将在下一篇具体解读。

为什么这里的基数统计会产生误差呢，这里和 mysql 的统计办法无关，mysql 采纳的是采样统计来获取基数的。

采样统计的时候，InnoDB 默认会抉择 N 个数据页，统计这些页面上的不同值，失去一个平均值，而后乘以这个索引的页面数，就失去了这个索引的基数。而数据表是会继续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会主动触发从新做一次索引统计。

当然除了基数，还有个重要的因素就是，这个 SQL 语句自身扫描的行数，并且加上非主键索引的回表老本。所以有时候两个索引，优化器却抉择了扫描行数更多的索引。

如何解决索引抉择谬误

  1. 采纳 force index 强行抉择一个索引。
  2. 批改 SQL 语义，疏导优化器去抉择索引。
  3. 删掉性能差的索引。

这里只列举几种解决方案，理论还是要依据具体情况来。

集体博客

西西弗的石头

作者程度无限，若有谬误脱漏，请指出。

参考文章

1.MySQL 内核源码解读 -SQL 解析之解析器浅析

2.MySQL 索引抉择准则

3.MySQL 实战 45 讲

参考书籍

  1. 《高性能 MySQL》
  2. 《MySQL 技术底细》
退出移动版