Sql

前几天，我在论坛溜达。看到一个人发帖说做了6年的企业级开发，总是被互联网行业的人认为没技术含量，不就是CRUD么先解释下 CRUD 是什么。CRUD 就是我们常说的增删改查(Create,Retrieve,Update,Delete)其实，对这个问题，我也思考过。我们所有的业务流程，最终都会抽象出数据模型，保存到数据库中。把业务之间的联系抽象成数据库中表与表，字段与字段之间的联系。实际上，企业的各种系统，在技术层面上确实是在 CRUD。不过话说回来了，互联网的系统不是 CRUD 吗？只不过 CRUD 的姿势不同罢了，互联网可能是面对高并发的 CRUD, 我们是面对的是复杂业务流程的 CRUD。这些业务逻辑还需要一定的行业积淀才能捋清楚。所以在企业级开发上业务和技术基本是五五开，业务比重甚至要大于技术。所以，今天我们就花很短的时间，来学学简单的 SQL.了解下 CRUD 的姿势。假如我们有下面一个订单表 ORDER_HEADER，不要在意表里的数据，为了方便说明，瞎填的。查询查询是我们平日使用最多的，下面着重说一下：查询使用 SELECT 关键字，基本结构如下SELECT <列名> FROM <表名> WHERE <条件>假如我们想查找所有已经完工的订单信息，那么 SQL 怎么写呢？SELECT * FROM order_header WHERE order_status = ‘完工’执行后会看到我们需要的结果SELECT * 代表查询所有列，一般我们会关注我们需要的字段，比如我们要找到订单类型为 1 的并且完工的订单号，我们可以这么写SELECT order_no FROM order_header WHERE order_status = ‘完工’ AND order_type = 1我们可以看到只有下面两个符合条件的订单号被选中。在写 SQL 中我们要尽量避免 SELECT * ,我们需要哪个字段就取哪个字段，可以节省 SQL 查询的时间。如果要查询订单类型为 1 的或者处于下达状态的订单，SQL 怎么写呢？SELECT order_no FROM order_header WHERE order_type = 1 OR order_status = ‘下达’如果要查找开工，下达和完工状态的订单，我们可以用 IN 关键字SELECT order_no, order_type, order_statusFROM order_headerWHERE order_status IN ( ‘开工’, ‘完工’, ‘下达’ )除了 IN 我们可以使用 LIKE 进行模糊查询，比如我们要查询订单状态中包含 “工” 的所有订单SELECT order_no FROM order_header WHERE order_status LIKE ‘%工%‘这里的 “%” 表示通配符，"%工"表示以工结尾的所有匹配，“工%“表示以工开头的所有匹配。此外，我们可以用 NOT IN , NOT LIKE 来取相反的逻辑。通过 GROUP BY 可以进行分组，比如我们按照订单状态来分组，就可以很方便的查看当前有几种状态的订单SELECT order_status FROM order_header GROUP BY order_status结果如下图如果我们想看到每个分组中有多少订单，那个我们可以使用 COUNT() 函数SELECT order_status, COUNT(1) FROM order_header GROUP BY order_status结果如下图,可以看到每个分组中订单的数量。除了COUNT()，还有 MAX(),MIN(),SUM()等函数如果我们只想看到数量大于 2 的分组，该怎么写？SELECT order_status, COUNT(1) FROM order_header GROUP BY order_status HAVING COUNT(1) > 2可以看到，只有数量大于2的分组被查到了。如果仅仅想去重，比如想知道总共有几种订单类型，那么我们只需要对此列用 DISTINCT 即可。SELECT DISTINCT order_type FROM order_header结果中的订单类型列已经被去重了。CASE WHEN ,有时我们会需要简单的判断逻辑，就可以用 CASE WHEN 了。比如我们想让 订单类型为1 的表示生产订单，订单类型为2 的表示更改订单，订单类型为3 的表示废弃订单。那么我们可以这么写SELECT order_no, order_type, order_status,CASE WHEN order_type = 1 THEN ‘生产订单’ WHEN order_type = 2 THEN ‘更改订单’ WHEN order_type = 3 THEN ‘废弃订单’ ELSE ‘未知类型’ END AS type_descFROMorder_header结果如下图子查询，有时候我们需要从一个结果集中再次查找，就会用到子查询。比如下面这样写SELECT order_no, type_descFROM (SELECT order_no, order_type, order_status,CASE WHEN order_type = 1 THEN ‘生产订单’ WHEN order_type = 2 THEN ‘更改订单’ WHEN order_type = 3 THEN ‘废弃订单’ ELSE ‘未知类型’ END AS type_descFROM order_header) t下节课我们说一下 连接查询和常用到的分析函数，在企业中，单表查询情况是很少的，要关联查询。插入数据插入数据我们使用 INSERT 语句INSERT INTO order_header ( order_no, order_type, order_status, order_date, createdon )VALUES ( ‘2018102109’, 2, ‘下达’, sysdate( ), sysdate( ) )看到，最下面就是我们新插入的一行更新数据更新数据使用 UPDATE 语句，我们更新一下刚才插入的数据的订单号UPDATE order_header SET order_no = ‘112109’ WHERE order_no = ‘2018102109’我们把 订单号为'2018102109’的一行数据更新为了'112109’，在update时一定要写好 WHERE 条件，如果没有 WHERE 条件，会更新表中所有数据。删除数据我们删除刚才加入的一条数据DELETE FROM order_header WHERE order_no = ‘112109’运行后，订单号为'112109’的订单就被删除了，DELETE 时同样要写好 WHERE 条件，如果没有 WHERE 条件，会删除表中所有数据。下节重点说说 多表连接，常用分析函数。不要走开哦。 ...

有一个业务是查询最新审核的5条数据SELECT id, titleFROM th_contentWHERE audit_time < 1541984478 AND status = ‘ONLINE’ORDER BY audit_time DESC, id DESCLIMIT 5;查看当时的监控情况 cpu 使用率是超过了100%，show processlist看到很多类似的查询都是处于create sort index的状态。查看该表的结构CREATE TABLE th_content ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, title varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT ’’ COMMENT ‘内容标题’, content mediumtext CHARACTER SET utf8 NOT NULL COMMENT ‘正文内容’, audit_time int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘审核时间’, last_edit_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最近编辑时间’, status enum(‘CREATED’,‘CHECKING’,‘IGNORED’,‘ONLINE’,‘OFFLINE’) CHARACTER SET utf8 NOT NULL DEFAULT ‘CREATED’ COMMENT ‘资讯状态’, PRIMARY KEY (id), KEY idx_at_let (audit_time,last_edit_time)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;索引有一个audit_time在左边的联合索引，没有关于status的索引。分析上面的sql执行的逻辑：从联合索引里找到所有小于该审核时间的主键id（假如在该时间戳之前已经审核了100万条数据，则会在联合索引里取出对应的100万条数据的主键 id）对这100万个 id 进行排序（为的是在下面一步回表操作中优化 I/O 操作，因为很多挨得近的主键可能一次磁盘 I/O 就都取到了）回表，查出100万行记录，然后逐个扫描，筛选出status=‘ONLINE’的行记录最后对查询的结果进行排序（假如有50万行都是ONLINE，则继续对这50万行进行排序）最后因为数据量很大，虽然只取5行，但是按照我们刚刚举的极端例子，实际查询了100万行数据，而且最后还在内存中进行了50万行数据库的内存排序。所以是非常低效的。画了一个示意图，说明第一步的查询过程，粉红色部分表示最后需要回表查询的数据行。图中我按照索引存储规律来YY伪造填充了一些数据，如有不对请留言指出。希望通过这张图大家能够看到联合索引存储的方式和索引查询的方式改进思路 1范围查找向来不太好使用好索引的，如果我们增加一个audit_time, status的联合索引，会有哪些改进呢？ALTER TABLE th_content ADD INDEX idx_audit_status (audit_time, status);mysql> explain select id, title from th_content where audit_time < 1541984478 and status = ‘ONLINE’ order by audit_time desc, id desc limit 5;+—-+————-+————+——-+——————————————+——————+———+——+——–+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+————+——-+——————————————+——————+———+——+——–+————-+| 1 | SIMPLE | th_content | range | idx_at_ft_pt_let,idx_audit_status | idx_audit_status | 4 | NULL | 209754 | Using where |+—-+————-+————+——-+——————————————+——————+———+——+——–+————-+细节：因为audit_time是一个范围查找，所以第二列的索引用不上了，只能用到audit_time，所以key_len是4。而下面思路2中，还是这两个字段key_len则是5。还是分析下在添加了该索引之后的执行过程：从联合索引里找到小于该审核时间的audit_time最大的一行的联合索引然后依次往下找，因为< audit_time是一个范围查找，而第二列索引的值是分散的。所以需要依次往前查找，匹配出满足条件（status=‘ONLINE’）的索引行，直到取到第5行为止。回表查询需要的具体数据在上面的示意图中，粉红色标识满足第一列索引要求的行，依次向前查询，本个叶子节点上筛选到了3条记录，然后需要继续向左，到前一个叶子节点继续查询。直到找到5条满足记录的行，最后回表。改进之处因为在索引里面有status的值，所以在筛选满足status=‘ONLINE’行的时候，就不用回表查询了。在回表的时候只有5行数据的查询了，在iops上会大大减少。该索引的弊端如果idx_audit_status里扫描5行都是status是ONLINE，那么只需扫描5行；如果idx_audit_status里扫描前100万行中，只有4行status是ONLINE，则需要扫描100万零1行，才能得到需要的5行记录。索引需要扫描的行数不确定。改进思路 2ALTER TABLE th_content DROP INDEX idx_audit_status;ALTER TABLE th_content ADD INDEX idx_status_audit (status, audit_time);这样不管是排序还是回表都毫无压力啦。本文作者：周梦康阅读原文本文为云栖社区原创内容，未经允许不得转载。 ...

1 引言重回 “手写 SQL 编辑器” 系列。这次介绍如何利用缓存优化编译器执行性能。可以利用 Frist 集 与 Match 节点缓存 这两种方式优化。本文会用到一些图做解释，下面介绍图形规则：First 集优化，是指在初始化时，将整体文法的 First 集找到，因此在节点匹配时，如果 Token 不存在于 First 集中，可以快速跳过这个文法，在文法调用链很长，或者 “或” 的情况比较多时，可以少走一些弯路：如图所示，只要构建好了 First 集，不论这个节点的路径有多长，都可以以最快速度判断节点是否不匹配。如果节点匹配，则继续深度遍历方式访问节点。现在节点不匹配时性能已经最优，那下一步就是如何优化匹配时的性能，这时就用到 Match 节点缓存。Match 节点缓存，指在运行时，缓存节点到其第一个终结符的过程。与 First 集相反，First 集可以快速跳过，而 Match 节点缓存可以快速找到终结符进行匹配，在非终结符很多时，效果比较好：如图所示，当匹配到节点时，如果已经构建好了缓存，可以直接调到真正匹配 Token 的 Match 节点，从而节省了大量节点遍历时间。这里需要注意的是，由于 Tree 节点存在分支可能性，因此缓存也包含将 “沿途” Chances 推入 Chances 池的职责。2 精读那么如何构建 First 集与 Match 节点缓存呢？通过两张图解释。构建 First 集如图所示，构建 First 集是个自下而上的过程，当访问到 MatchNode 节点时，就可以收集作为父节点的 First 集了！父集判断 First 集收集完毕的话，就会触发它的父节点 First 集收集判断，如此递归，最后完成 First 集收集的是最顶级节点。构建 Match 节点缓存如图所示，访问节点时，如果没有缓存，则会将这个节点添加到 Match 缓存查找队列，同时路途遇到 TreeNode，也会将下一个 Chance 添加到缓存查找队列。直到遇到了第一个 MatchNode 节点，则这个节点是 “Match 缓存查找队列” 所有节点的 Match 节点缓存，此时这些节点的缓存就可以生效了，指向这个 MatchNode，同时清空缓存查找队列，等待下一次查找。3 总结拿 select a, b, c, d from e 这个语句做测试：node 节点访问次数Frist 集优化First 集 + Match 节点缓存优化784669652从这个简单 Demo 来看，提效了 16% 左右。不过考虑到文法结构会影响到提效，对于层级更深的文法、能激活深层级文法的输入可以达到更好的效率提升。4 更多讨论讨论地址是：精读《手写 SQL 编译器 - 性能优化之缓存》 · Issue #110 · dt-fe/weekly如果你想参与讨论，请点击这里，每周都有新的主题，周末或周一发布。前端精读 - 帮你筛选靠谱的内容。 ...

欢迎大家前往腾讯云+社区，获取更多腾讯海量技术实践干货哦~本文由腾讯云数据库 TencentDB发表于云+社区专栏作者介绍：王晓宇，腾讯数据库TDSQL团队成员，目前参与TDSQL数据库内核研发工作。腾讯与高校合作的论文入选数据库顶会腾讯TDSQL团队携手中国人民大学信息学院、武汉大学计算机学院合作的DEMO论文“MSQL+: a Plugin Toolkit for Similarity Search under Metric Spaces in Distributed Relational Database Systems”被国际数据库顶级会议VLDB 2018录取。该论文设计了一款基于RDBMS的插件式近似查询工具MSQL+。MSQL+遵循SQL标准，支持面向度量空间（一种比文本空间、向量空间等更为简洁和通用的表达方式）的近似查询，依托分布式数据库系统TDSQL，实现了通用、易用、高效的相似查询处理技术。会议期间，团队展示了基于腾讯分布式数据库TDSQL实现的相似查询工具MSQL+，用于在分布式系统TDSQL中实现相似查询。在TDSQL数据库内部集成更多的计算功能，赋予数据库更为丰富的计算能力。原论文请见http://www.vldb.org/pvldb/vol…。论文信息如下：Wei Lu, Xinyi Zhang, Zhiyu Shui, Zhe Peng, Xiao Zhang, Xiaoyong Du, Hao Huang, Xiaoyu Wang, Anqun Pan, Haixiang Li: MSQL+: a Plugin Toolkit for Similarity Search under Metric Spaces in Distributed Relational Database Systems. VLDB 2018 Demonstration如果您想了解更多技术细节，请参考如下内容（如下重点介绍MSQL+的产生背景、功能、架构、设计）：论文解读以下重点介绍MSQL+的产生背景、功能、架构、设计，原论文请见http://www.vldb.org/pvldb/vol…。MSQL+产生背景相似查询是诸多数据库应用的基本操作。举例来说，相似查询在文本检索、拼写检查、指纹认证、人脸识别等场景作用显著。那么这些应用是如何进行相似查询的？给出对象q和集合R，返回R中与q相似度相差不超过的元素。最直接地，遍历r∈R，计算r与q的相似度，可想而知，此方法效率很低。各领域已发展出多种方式对上述相似查询方式针对优化，但仍存在以下问题：1与现有数据库系统剥离：现有的相似查询方法，大量建立新系统或新式索引提高效率，如M-Tree、D-Index、kd-tree等，虽说性能得以提升，但很难将其融合到现有RDBMS。另有一些方法基于B+-tree实现相似查询，但要在现有RDBMS上开发新API，而且效率表现不佳。这些方法缺少统一标准、兼容性差，每接触新方法，都要付出额外的学习成本。2数据空间有限，普适性差：众多应用场景对“相似”的定义不同，衡量维度、数据类型不同，难以建立通用的相似查询模型。借助于定制化的剪枝规则，特定场景相似查询性能得到提升，但几乎不可能移植到其他应用场景。作为基本操作，相似查询应该具有普适性，在不同RDBMS应用中都能保证良好表现。3仅适用中心化系统，难应对“大数据”场景：大数据时代下，借助于分布式系统维护日益增长的数据是大势所趋。遗憾的是现有的相似查询方式，并未良好地支持分布式系统。为避免上述问题，MSQL+被设计为：基于RDBMS，遵循SQL标准，借力分布式数据库，以实现通用、易用、高效。在实际生产系统中，MSQL+以腾讯公司的分布式数据库TDSQL为依托，高效地实现了论文提出的思想和功能。MSQL+主要功能MSQL+由两大模块组成：1构建索引：MSQL+为每个数据对象生成可比较的签名（Signature），并在签名上建立B+-tree索引，签名值位于相似度范围内的对象，作为相似查询的候选项；2查询处理：用户提交SELECT-FROM-WHERE语句，该语句须提供两个约束条件，分别为：a) 用户定义的相似度函数，b) 相似度范围，条件b)初步筛选候选项，条件a)精炼候选项、返回相似结果集。相比于现有的相似查询方式，MSQL+具备以下优势：1基于RDBMS现有功能实现，使用B+-tree索引数据，使用SELECT-FROM-WHERE语句相似查询；2支持广泛的数据空间：任意类型数据可被合理地索引（见下文设计），经由统一接口相似查询；3可运行于单机和分布式RDBMS，依托分布式关系数据库系统TDSQL，能够加快预处理及相似查询进程。MSQL+设计方案本节将对MSQL+近似查询方案做简单介绍，细节请见原论文。1. Similarity Search in Metric SpacesMSQL+采用分治策略，将完整的数据集划分成多个独立的分片，每个分片筛选出若干较为相似的候选者，这些候选者后续会被二次精选。MSQL+如何划分数据集？论文说明，数据集内一些对象被选为pivot（下节介绍选举pivot的策略），剩余的数据对象按某种策略分配到唯一的pivot（比如，与之最近的pivot），这些pivot和分配至此的数据对象构成了一个分片。如此，完整的数据集被划分成多个互不相交的小数据集，然后在各分片内筛选较为相似的候选者。筛选候选者的规则是什么呢？我们从一个例子入手：给出对象q和数据集R，相似查询返回R中与q相差不超过的数据对象。对分区Pi而言，筛选r∈Pi ，且|q,r|≦的对象r作为候选者。定理 1：对于分区Pi（其pivot为Pi），∀r∈Pi ，|q,r|≦的必要条件是：LBi=|pi, q|-≦|pi, r|≦|pi, q|+ = UBiPivot的挑选，是上述过程的基础，那么，MSQL+如何挑选pivot？2. Pivot Selection选择合适的pivot，可以加快筛选候选者及精选结果集的过程，论文提出了四种pivot选择方式：1Random：从集合R中随机挑选对象作为pivot；2MaxVariance：从集合R中挑选方差最大的一组对象作为pivots；3MaxProb：pivot需满足，预期筛选出的候选项的个数最少；4Heuristic：采取类似于k-means的启发式算法，整体上看，各分区中的元素尽量靠近pivot。到此，已经可以筛选较为相近的候选者，那么，如何从中精选出更相近的结果集呢？3. Processing similarity queries in RDBMS为了快速精选出结果集，MSQL+在数据集上构建B+-tree索引，以下分两步，介绍该索引如何构建、如何使用。论文做出定义：某表存储了数据集R，表上有M个属性（即M列），部分属性作为相似度的度量，记作A:{ A1, A2, …, An } n≦M ,对于r∈R ，r[A]表示数据r属性{A1, A2, …, An}的值。3.1 Index Building在A上建立B+-tree索引，有两个条件：a) 域{ A1, A2, …, An }都是可比较的，b) 只需比较A各域的值即可精选候选项。借助于此索引，可方便地实现相似查询。那么，如何构建这样的索引？论文做出这样的设计：对于r∈R，一张“签名表”（Signature generation schema）记录了r的签名S(r[A])，S(r[A])=<i, |r, Pi|>，其中i是分区ID，|r, Pi|是分区内数据对象r和pivot Pi的差距，签名的比较规则为：原表（存储数据集R）上新增一列I记录签名<i, |r, Pi|>，并在I上建立B+-tree索引，此索引满足“可比较”和“比较索引可确定候选项”两要素，故可借助此索引方便地近似查询。3.2 Query Processing至此，已经构建了合适的B+-tree索引，如何利用该索引精选候选者？MSQL+支持用户自定义相似度函数DIST(r[A], q[A], )，此函数判断r[A]和q[A]的距离未超过，用户定义相似度函数这一设计，扩展了MSQL+支持的数据空间和类型。有了DIST函数，用户输入SELECT-FROM-WHERE语句形如：SELECT R.A1,…,R.AnFROM RWHERE DIST(r[A], q[A], )上面这条SQL，直接从数据集R中精确筛选结果集，效率堪忧。候选者此时派上用场，定理1（见Similarity Search in Metric Spaces）描述了如何筛选候选者，减少精确筛选的数据量，加速精拣过程。结合定理1和DIST函数，用户输入SELECT-FROM-WHERE语句形如：SELECT R.A1,…,R.AnFROM R, PivotsRangeSet PRSWHERE I BETWEEN PRS.LB and PRS.UB ANDDIST(r[A], q[A], )其中，临时表PivotsRangeSet维护了各pivot的LU和UB。因为PivotsRangeSet规模很小，查询优化器总会先索引扫描得到候选项，然后DIST函数精炼结果集。MSQL+分布式架构MSQL+既可在本地RDBMS上工作，又可部署在分布式RDBMS。论文给出MSQL+基于TDSQL的架构。1. System Architecture1.1 TDSQL介绍TDSQL是腾讯针对金融联机交易场景推出的高一致性，分布式数据库集群解决方案，能够保证强一致下的高可用，拥有灵活的全球部署架构，实现了倍数性能提升，增强了MySQL原生的安全机制，能够在水平方向上分布式扩展，具有自动化的运营体系和完善的配套设施。TDSQL由以下关键组件构成：1Routing Node：负载均衡；2ZooKeeprt：维护系统元信息，如表、索引、分区等；3Global Executor：接收SQL、下发local executor、汇集本地结果、生成执行计划等；4Local executor：本地数据存、取、计算等。1.2 TDSQL增益MSQL+是一款由用户自定义函数、存储过程实现的插件式工具，可以无缝融入TDSQL。MSQL+如何在TDSQL上工作呢？ZooKeeper维护MSQL+特需的元信息，并同步至各local executors；Global executor接收相似查询请求，分发至各local executors执行，汇集最终结果并给出执行计划；Local executor完成本地分片相似查询，返回执行结果。TDSQL又能给MSQL+带来什么样的增益？首先是可靠、可用性，TDSQL实现了多副本强一致性，最大程度地保障MSQL+所需的大量样本数据的安全、可用、可靠。其次，TDSQL支持水平方向分布式扩展，免除单机存储容量不足的后顾之忧，无论MSQL+样本数据多大，TDSQL都可轻松应对。TDSQL在安全机制做出的优化，很大程度保证MSQL+样本数据的安全和机密。我们最关心的性能问题，从分布式角度看，TDSQL多个本地节点并行查询，全局相似查询效率大幅度提升；具体到本地节点，TDSQL在数据库内核方面做出大量优化，使得单节点效率也有很大提升。2. Index BuildingZooKeeper维护了全部pivot信息，并由Global executor将pivot信息下发至local executors。 Global executor协调local executors构建索引，每个local executor维护一定数量的分片，也就维护对应的pivots，基于这些pivots，local executor生成签名S(r[A])，进而构建起索引。3. Query Processing用户发起相似查询请求时，routing node选择一个global executor，global executor协调local executors并行执行相似查询，汇集本地执行结果并生成执行计划。MSQL+界面展示论文展示的操作界面如下，MSQL+支持相似查询、索引构建、客户端连接、集群管理、数据导入、查询状态显示、执行计划可视化等功能。结论：MSQL+是一款基于RDBMS的插件式近似查询工具，基于腾讯TDSQL实现，具有通用、易用、高效的特点：统一接口支持多种数据空间；遵循SQL标准，发起SELECT-FROM-WHERE命令即可完成相似查询任务；MSQL+依托腾讯分布式数据库TDSQL，实现了负载均衡、多点并行，可高效地完成相似查询。问答PHP + MSQL +函数在函数中使用请求相关阅读TDSQL参加全球数据库顶级盛会 VLDB 2018回顾腾讯云数据库MySQL游戏行业数据安全实践分享MySQL 8.0 版本功能变更介绍 【每日课程推荐】机器学习实战！快速入门在线广告业务及CTR相应知识 ...

本文旨在用最通俗的语言讲述最枯燥的基本知识这个话题比较有意思。昨天中午吃完饭间突然有个同事蹦出了一句：“like有索引吗？”，我顺口就说没有，另一个同事反驳说有啊，还有些同事说看情况的有，这下有点懵逼了，都不知道那种说法是正确的，于是决定花了个半小时来研究验证这个问题，终于得到答案。怎么验证的呢？坊间有传言：MySQL性能优化有个神器，叫做explain，它可以对select语句进行分析并且输出详细的select执行过程的详细信息，让开发者从这些信息中获得优化的思路。下面来讲讲这个MySQL提供的explain命令：语法：explain SQL语句例如：explain select * from user where id=1执行完毕之后，它的输出有以下字段：idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsExtra要想知道explain命名怎么使用，就必须把这些字段搞清楚1. idSELECT查询的标识符， 每个SELECT语句都会自动分配一个唯一的标识符2. select_type每个select查询字句的类型，具体类型以及对应作用如下表：类型名解释SIMPLE简单SELECT,不使用UNION或子查询等PRIMARY查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARYUNIONUNION中的第二个或后面的SELECT语句DEPENDENT UNIONUNION中的第二个或后面的SELECT语句，取决于外面的查询UNION RESULTUNION的结果SUBQUERY子查询中的第一个SELECTDEPENDENT SUBQUERY子查询中的第一个SELECT，取决于外面的查询DERIVED派生表的SELECT, FROM子句的子查询UNCACHEABLE SUBQUERY一个子查询的结果不能被缓存，必须重新评估外链接的第一行3. table显示这一行的数据是查哪张表的，不过有时短路显示的不是真实的表名。4. partitions匹配的分区(这个目前用处不大)5. type访问类型，表示MySQL在表中找到所需行的方式，对应的值和解释如下：类型名优级别解释system1表仅有一行const2表最多有一个匹配行,在查询开始时即被读取eq_ref3使用primary key或者unique key作为多表连接的条件,仅从该表中读取一行ref4作为查询条件的索引在每个表匹配索引值的行从表中读取出来fulltext5全文索引检索ref_or_null6和ref一致，但增加了NULL值查询支持index_merge7表示使用了索引合并优化方法unique_subquery8使用了替换了in子查询index_subquery9使用了替换了in子查询，但只适用于子查询中的非唯一索引range10只检索给定范围的行,使用一个索引来选择行index11全表扫描，但扫描表的方式是按索引的次序进行ALL12全表扫描的方式找到匹配的行type作为访问类型，其值代表着当前查询所用的类型，是体现性能的一个重要指标，从表中可以看到，从上到下，扫描表的方式越来越宽，性能也就越来越差，因此，对于一个查询，最好能保持在range级别以上。6. possible_keys主动指出查询能用哪个索引在表中找到记录也就是会列出在查询中的字段中有索引的字段，但不一定被查询所用。7. key显示再查询中实际使用的索引/键，如果没有索引，则显示NULL。但如果想强制查询中使用或忽视possible_keys列中的索引，则可以在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。8. key_len表示索引中使用的字节数。9. ref表示哪些列或常量被用于查找索引列上的值。10. rows显示当前查询估算到的查找到匹配记录所需的记录行数。11. Extra显示当前查询所用的解决方式，它有以下几种情况：类型名解释Using where列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，Using temporary表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询Using filesortMySQL中无法利用索引完成的排序操作称为“文件排序”Using join buffer改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。Impossible where这个值强调了where语句会导致没有符合条件的行。Select tables optimized away这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行讲完了语法，我们来实际操作一波，首先创建个表：– 创建表CREATE TABLE test(id INT(11) NOT NULL AUTO_INCREMENT,uname VARCHAR(255),PRIMARY KEY(id) );然后给uname字段加上索引：– 添加索引ALTER TABLE test ADD INDEX uname_index (uname);查看一下索引是否添加成功：– 查看是否有索引SHOW INDEX FROM test;输出结果为：可以看出索引已经创建成功，接下来添加一些数据：– 添加一些数据INSERT INTO test VALUES(1,‘jay’);INSERT INTO test VALUES(2,‘ja’);INSERT INTO test VALUES(3,‘bril’);INSERT INTO test VALUES(4,‘aybar’);一切准备就绪，下面用explain这个命令来探究一些like语句是否有索引,like有四种情况，分别为没有%、 %% 、左%、右%、1. like 字段名EXPLAIN SELECT * FROM test WHERE uname LIKE ‘j’; 输出为：可以看出：type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。2. like %字段名%EXPLAIN SELECT * FROM test WHERE uname LIKE ‘%j%’; 输出为：可以看出：type的值为ALL也就是全表扫描，而且key的值为NULL，也就是说没用到任何索引。3. like %字段名EXPLAIN SELECT * FROM test WHERE uname LIKE ‘%j’; 输出为：可以看出：type的值为ALL，key的值为NULL，同样没用到索引。4. like 字段名%EXPLAIN SELECT * FROM test WHERE uname LIKE ‘j%’; 输出为：可以看出：type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。总结由上面的试验可以总结出like是否使用索引的规律：like语句要使索引生效，like后不能以%开始，也就是说 （like %字段名%） 、（like 字段名%）这类语句会使索引失效，而（like 字段名）、（like 字段名%）这类语句索引是可以正常使用。其它为了查证like索引的问题，研究了MySQL神奇explain，但explain不仅仅只能检查索引使用情况，还可以提供很多其它的性能优化方面的帮助，至于具体的使用，其实跟上面讲的一样，把explain结果列出来，然后顺藤摸瓜查阅相关的字段就可以得到相应的内容。觉得本文对你有帮助？请分享给更多人关注「编程无界」，提升装逼技能 ...

1 引言编译器除了生成语法树之外，还要在输入出现错误时给出恰当的提示。比如当用户输入 select (name，这是个未完成的 SQL 语句，我们的目标是提示出这个语句未完成，并给出后续的建议： ) - + % / * . ( 。2 精读分析一个 SQL 语句，现将 query 字符串转成 Token 数组，再构造文法树解析，那么可能出现错误的情况有两种：语句错误。文法未完成。给出错误提示的第一步是判断错误发生。通过这张 Token 匹配过程图可以发现，当深度优先遍历文法节点时，匹配成功后才会返回父元素继续往下走。而当走到父元素没有根节点了才算匹配成功；当尝试 Chance 时没有机会了，就是错误发生的时机。所以我们只要找到最后一个匹配成功的节点，再根据最后成功与否，以及搜索出下一个可能节点，就能知道错误类型以及给出建议了。function onMatchNode(matchNode, store) { const matchResult = matchNode.run(store.scanner); if (!matchResult.match) { tryChances(matchNode, store); } else { const restTokenCount = store.scanner.getRestTokenCount(); if (matchNode.matching.type !== “loose”) { if (!lastMatch) { lastMatch = { matchNode, token: matchResult.token, restTokenCount }; } } callParentNode(matchNode, store, matchResult.token); }}所以在运行语法分析器时，在遇到匹配节点（MatchNode）时，如果匹配成功，就记录下这个节点，这样我们最终会找到最后一个匹配成功的节点：lastMatch。之后通过 findNextMatchNodes 函数找到下一个可能的推荐节点列表，作为错误恢复的建议。findNextMatchNodes 函数会根据某个节点，找出下一节点所有可能 Tokens 列表，这个函数后面文章再专门介绍，或者你也可以先阅读 源码.语句错误也就是任何一个 Token 匹配失败。比如：select * from table_name as table1 error_string;这里 error_string 就是冗余的语句。通过语法解析器分析，可以得到执行失败的结果，然后通过 findNextMatchNodes 函数，我们可以得到下面分析结果：可以看到，程序判断出了 error_string 这个 Token 属于错误类型，同时给出建议，可以将 error_string 替换成这 14 个建议字符串中任意一个，都能使语句正确。之所以失败类型判断为错误类型，是因为查找了这个正确 Token table1 后面还有一个没有被使用的 error_string，所以错误归类是 wrong。注意，这里给出的是下一个 Token 建议，而不是全部 Token 建议，因此推荐了 where 表示 “或者后面跟一个完整的 where 语句”。文法未完成和语句错误不同，这种错误所有输入的单词都是正确的，但却没有写完。比如：select *通过语法解析器分析，可以得到执行失败的结果，然后通过 findNextMatchNodes 函数，我们可以得到下面分析结果：可以看到，程序判断出了 * 这个 Token 属于未完成的错误类型，建议在后面补全这 14 个建议字符串中任意一个。比较容易联想到的是 where，但也可以是任意子文法的未完成状态，比如后面补充 , 继续填写字段，或者直接跟一个单词表示别名，或者先输入 as 再跟别名。之所以失败类型判断为未完成，是因为最后一个正确 Token * 之后没有 Token 了，但语句解析失败，那只有一个原因，就是语句为写完，因此错误归类是 inComplete。找到最易读的错误类型在一开始有提到，我们只要找到最后一个匹配成功的节点，就可以顺藤摸瓜找到错误原因以及提示，但最后一个成功的节点可能和我们人类直觉相违背。举下面这个例子：select a from b where a = ‘1’ ~ – 这里手滑了正常情况，我们都认为错误点在 ~，而最后一个正确输入是 ‘1’。但词法解析器可不这么想，在我初版代码里，判断出错误是这样的：提示是 where 错了，而且提示是 .，有点摸不着头脑。读者可能已经想到了，这个问题与文法结构有关，我们看 fromClause 的文法描述：const fromClause = () => chain( “from”, tableSources, optional(whereStatement), optional(groupByStatement), optional(havingStatement) )();虽然实际传入的 where 语句多了一个 ~ 符号，但由于文法认为整个 whereStatement 是可选的，因此出错后会跳出，跳到 b 的位置继续匹配，而 显然 groupByStatement 与 havingStatement 都不能匹配到 where，因此编译器认为 “不会从 b where a = ‘1’ ~” 开始就有问题吧？因此继续往回追溯，从 tableName 开始匹配：const tableName = () => chain([matchWord, chain(matchWord, “.”, matchWord)()])();此时第一次走的 b where a = ‘1’ ~ 路线对应 matchWord，因此尝试第二条路线，所以认为 where 应该换成 .。要解决这个问题，首先要 承认这个判断是对的，因为这是一种 错误提前的情况，只是人类理解时往往只能看到最后几步，所以我们默认用户想要的错误信息，是 正确匹配链路最长的那条，并对 onMatchNode 作出下面优化：将 lastMatch 对象改为 lastMatchUnderShortestRestToken:if ( !lastMatchUnderShortestRestToken || (lastMatchUnderShortestRestToken && lastMatchUnderShortestRestToken.restTokenCount > restTokenCount)) { lastMatchUnderShortestRestToken = { matchNode, token: matchResult.token, restTokenCount };}也就是每次匹配到正确字符，都获取剩余 Token 数量，只保留最后一匹配正确 且剩余 Token 最少的那个。3 总结做语法解析器错误提示功能时，再次刷新了笔者三观，原来我们以为的必然，在编译器里对应着那么多 “可能”。当我们遇到一个错误 SQL 时，错误原因往往不止一个，你可以随便截取一段，说是从这一步开始就错了。语法解析器为了让报错符合人们的第一直觉，对错误信息做了 过滤，只保留剩余 Token 数最短的那条错误信息。4 更多讨论讨论地址是：精读《手写 SQL 编译器 - 错误提示》 · Issue #101 · dt-fe/weekly如果你想参与讨论，请点击这里，每周都有新的主题，周末或周一发布。 ...

应用场景：发货后15天自动更改订单状态为已收货实现方法：mysql的event事件1.查看event事件是否开启，默认都是关闭状态 SHOW VARIABLES LIKE ’event_scheduler’;2.开启event事件 set global event_scheduler=on;3.创建函数注意下入参4.接下来在自己的代码中启动就可以了，一般是在后台发货时启用(我这里还包括了15分钟自动取消订单功能)写的不好，多多见谅，如果有问题和指教欢迎评论私信，看到都会回复的