mysq从0到0.1-SQL是怎么执行的（上）

知识点掌握了，还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战，手把手教大家学习《mysq从0到0.1-SQL是怎么执行的（上）》，在实现功能的过程中也带大家重新温习相关知识点，温故而知新，回头看看说不定又有不一样的感悟！

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存在语法问题。

 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技术内幕》

今天关于《mysq从0到0.1-SQL是怎么执行的（上）》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！