关于mysql:mysq从0到01系列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存在语法问题。

* 词法剖析(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技术底细》

【腾讯云】云产品限时秒杀,爆款1核2G云服务器,首年50元

阿里云限时活动-2核2G-5M带宽-60G SSD-1000G月流量 ,特惠价99元/年(原价1234.2元/年,可以直接买3年),速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

You may also like...

发表评论

邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据