189 8069 5689
我们先来看第一个阶段，MySQL慢的诊断思路，一般我们会从三个方向来做：
站在用户的角度思考问题，与客户深入沟通，找到峨边彝族网站设计与峨边彝族网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站制作、网站建设、企业官网、英文网站、手机端网站、网站推广、域名与空间、虚拟主机、企业邮箱。业务覆盖峨边彝族地区。
第一个方向是MySQL内部的观测
第二个方向是外部资源的观测
第三个方向是外部需求的改造
1.1 MySQL 内部观测
我们来看MySQL内部的观测，常用的观测手段是这样的，从上往下看，第一部分是Processlist，看一下哪个SQL压力不太正常，第二步是explain，解释一下它的执行计划，第三步我们要做Profilling，如果这个SQL能再执行一次的话, 就做一个Profilling，然后高级的DBA会直接动用performance_schema ，MySQL 5.7 以后直接动用sys_schema，sys_schema是一个视图，里面有便捷的各类信息，帮助大家来诊断性能。再高级一点，我们会动用innodb_metrics进行一个对引擎的诊断。
除了这些手段以外，大家还提出了一些乱七八糟的手段，我就不列在这了，这些是常规的一个MySQL的内部的状态观测的思路。除了这些以外，MySQL还陆陆续续提供了一些暴露自己状态的方案，但是这些方案并没有在实践中形成套路，原因是学习成本比较高。
1.2 外部资源观测
外部资源观测这部分，我引用了一篇文章，这篇文章的二维码我贴在上面了。这篇文章是国外的一个神写的，标题是：60秒的快速巡检，我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检。一共十条命令，这是前五条，我们一条一条来看。
1.uptime，uptime告诉我们这个机器活了多久，以及它的平均的负载是多少。
2.dmesg -T | tail，告诉我们系统日志里边有没有什么报错。
3.vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap有没有使用。
4. mpstat -P ALL，告诉我们CPU压力在各个核上是不是均匀的。
5.pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。
我们来看一下后五条：
首先是iostat-xz 1，查看IO的问题，然后是free-m内存使用率，之后两个sar，按设备网卡设备的维度，看一下网络的消耗状态，以及总体看TCP的使用率和错误率是多少。最后一条命令top，看一下大概的进程和线程的问题。
这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。
1.3 外部需求改造
第三个诊断思路是外部的需求改造，我在这里引用了一篇文档，这篇文档是MySQL的官方文档中的一章，这一章叫Examples of Common Queries，文档中介绍了常规的SQL怎么写, 给出了一些例子。文章的链接二维码在slide上。
我们来看一下它其中提到的一个例子。
它做的事情是从一个表里边去选取，这张表有三列，article、dealer、price，选取每个作者的最贵的商品列在结果集中，这是它的最原始的SQL，非常符合业务的写法，但是它是个关联子查询。
关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的。
第三步，会教大家直接把子查询拿掉，然后转成这样一个SQL，这个就叫业务改造，前后三个SQL的成本都不一样，把关联子查询拆掉的成本，拆掉以后SQL会跑得非常好，但这个SQL已经不能良好表义了，只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式。
为什么它能够把一个关联子查询拆掉呢？
这背后的原理是关系代数，所有的SQL都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。
上面的这篇文档是一个大学的教材，它从头教了关于代数和SQL之间的关系。然后一步步推导怎么去简化这句SQL。
第一，MySQL本身提供了很多命令来观察MySQL自身的各类状态，大家从上往下检一般能检到SQL的问题或者服务器的问题。
第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，我们把服务器的资源全部都观察一圈就可以了。
第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写SQL，这个成本会下降的非常快，这个是常规的MySQL慢的诊断思路。
1、简化应用程序。
视图做为数据库中的一种实体，实际上存在的只是它的脚本，而它的内容并不真正的单独存在一份。一般，可以对复杂的应用程序从功能角度进行分析，将可以与其它的应用程序共用的那一部分，分离出来。对这部分功能，视具体情况可做成不同的数据库实体（如过程），有些是可以做成视图的。这样，上层的应用程序就可以从视图中取数据了。还有，可以把对远地数据库的访问封装在视图中，使之对上层应用程序透明。2、可以对 UNION 后的记录集排序。
直接对以下语句的结果排序，是不可能的。 select a.id id from a
union
select b.id id from b;
所以把以上语句作成视图后，就可以了。设视图名为A_B:
select id from A_B order by id;3、可以实现一定的权限控制。
可以根据需要，对表中的一部分内容做一个视图，以供一定的角色使用。可以对表中的一部分记录做一个视图（纵向），也可以对一个表中的一部分字段做一个视图（横向），或二者兼而有之。--------------------------------------------------------------------
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。分布式查询也可用于定义使用多个异类源数据的视图。如果有几台不同的服务器分别存储组织中不同地区的数据，而您需要将这些服务器上相似结构的数据组合起来，这种方式就很有用。一、视图的作用 简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。 安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上：使用权限可被限制在基表的行的子集上。
使用权限可被限制在基表的列的子集上。
使用权限可被限制在基表的行和列的子集上。
使用权限可被限制在多个基表的连接所限定的行上。
使用权限可被限制在基表中的数据的统计汇总上。
使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。 逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。二、视图的优点 (1)视图能简化用户的操作
(2)视图机制可以使用户以不同的方式查询同一数据
(3)视图对数据库重构提供了一定程度的逻辑独立性
(4)视图可以对机密的数据提供安全保护三、视图的安全性视图的安全性可以防止未授权用户查看特定的行或列，是用户只能看到表中特定行的方法如下： 1 在表中增加一个标志用户名的列；
2 建立视图，是用户只能看到标有自己用户名的行；
3 把视图授权给其他用户。四、逻辑数据独立性 视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立： 1 如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而应用程序可以不动。
2 如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。
3 如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而应用程序可以不动。
4 如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而数据库可以不动。五、视图的书写格式 CREATE VIEW 视图名[(列名组)]
AS 子查询
DROP VIEW 索引名 注意：视图可以和基本表一样被查询，但是利用视图进行数据增，删，改操作，会受到一定的限制。（1）由两个以上的基本表导出的视图
（2）视图的字段来自字段表达式函数
（3）视图定义中有嵌套查询
（4）在一个不允许更新的视图上定义的视图
用Explain语法：
explain select … from … [where ...]
1、id：这是SELECT的查询序列号
2、select_type：select_type就是select的类型
3、table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
在已有的 MySQL 服务器之上使用 Apache Spark （无需将数据导出到 Spark 或者 Hadoop 平台上），这样至少可以提升 10 倍的查询性能。使用多个 MySQL 服务器（复制或者 Percona XtraDB Cluster）可以让我们在某些查询上得到额外的性能提升。你也可以使用 Spark 的缓存功能来缓存整个 MySQL 查询结果表。
思路很简单：Spark 可以通过 JDBC 读取 MySQL 上的数据，也可以执行 SQL 查询，因此我们可以直接连接到 MySQL 并执行查询。那么为什么速度会快呢？对一些需要运行很长时间的查询（如报表或者BI），由于 Spark 是一个大规模并行系统，因此查询会非常的快。MySQL 只能为每一个查询分配一个 CPU 核来处理，而 Spark 可以使用所有集群节点的所有核。在下面的例子中，我们会在 Spark 中执行 MySQL 查询，这个查询速度比直接在 MySQL 上执行速度要快 5 到 10 倍。
另外，Spark 可以增加“集群”级别的并行机制，在使用 MySQL 复制或者 Percona XtraDB Cluster 的情况下，Spark 可以把查询变成一组更小的查询（有点像使用了分区表时可以在每个分区都执行一个查询），然后在多个 Percona XtraDB Cluster 节点的多个从服务器上并行的执行这些小查询。最后它会使用map/reduce 方式将每个节点返回的结果聚合在一起形成完整的结果。
时间的快慢主要由索引决定，在索引都最优化的情况下才与数据量有关。
视图不可能明显加快的查询速度，只是编程方便而已。
一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句\x0d\x0a\x0d\x0a1，slow_query_log\x0d\x0a这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。\x0d\x0a\x0d\x0a2，long_query_time\x0d\x0a当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。\x0d\x0a\x0d\x0a3，slow_query_log_file\x0d\x0a记录日志的文件名。\x0d\x0a\x0d\x0a4，log_queries_not_using_indexes\x0d\x0a这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。\x0d\x0a\x0d\x0a二、检测mysql中sql语句的效率的方法\x0d\x0a\x0d\x0a1、通过查询日志\x0d\x0a（1）、Windows下开启MySQL慢查询\x0d\x0aMySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上\x0d\x0a代码如下\x0d\x0alog-slow-queries = F:/MySQL/log/mysqlslowquery。log\x0d\x0along_query_time = 2\x0d\x0a\x0d\x0a（2）、Linux下启用MySQL慢查询\x0d\x0aMySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上\x0d\x0a代码如下\x0d\x0alog-slow-queries=/data/mysqldata/slowquery。log\x0d\x0along_query_time=2\x0d\x0a说明\x0d\x0alog-slow-queries = F:/MySQL/log/mysqlslowquery。\x0d\x0a为慢查询日志存放的位置，一般这个目录要有MySQL的运行帐号的可写权限，一般都将这个目录设置为MySQL的数据存放目录；\x0d\x0along_query_time=2中的2表示查询超过两秒才记录；\x0d\x0a\x0d\x0a2.show processlist 命令\x0d\x0a\x0d\x0aSHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。\x0d\x0a各列的含义和用途：\x0d\x0aID列\x0d\x0a一个标识，你要kill一个语句的时候很有用，用命令杀掉此查询 /*/mysqladmin kill 进程号。\x0d\x0auser列\x0d\x0a显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。\x0d\x0ahost列\x0d\x0a显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。\x0d\x0adb列\x0d\x0a显示这个进程目前连接的是哪个数据库。\x0d\x0acommand列\x0d\x0a显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。\x0d\x0atime列\x0d\x0a此这个状态持续的时间，单位是秒。\x0d\x0astate列\x0d\x0a显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，以查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成\x0d\x0ainfo列\x0d\x0a显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。\x0d\x0a\x0d\x0a这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：\x0d\x0aChecking table\x0d\x0a 正在检查数据表（这是自动的）。\x0d\x0aClosing tables\x0d\x0a 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。\x0d\x0aConnect Out\x0d\x0a 复制从服务器正在连接主服务器。\x0d\x0a\x0d\x0aCopying to tmp table on disk\x0d\x0a 由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。\x0d\x0aCreating tmp table\x0d\x0a 正在创建临时表以存放部分查询结果。\x0d\x0adeleting from main table\x0d\x0a 服务器正在执行多表删除中的第一部分，刚删除第一个表。\x0d\x0adeleting from reference tables\x0d\x0a 服务器正在执行多表删除中的第二部分，正在删除其他表的记录。\x0d\x0a\x0d\x0aFlushing tables\x0d\x0a 正在执行FLUSH TABLES，等待其他线程关闭数据表。\x0d\x0aKilled\x0d\x0a 发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。\x0d\x0aLocked\x0d\x0a 被其他查询锁住了。\x0d\x0aSending data\x0d\x0a 正在处理SELECT查询的记录，同时正在把结果发送给客户端。\x0d\x0a\x0d\x0aSorting for group\x0d\x0a 正在为GROUP BY做排序。\x0d\x0a Sorting for order\x0d\x0a 正在为ORDER BY做排序。\x0d\x0aOpening tables\x0d\x0a 这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。\x0d\x0aRemoving duplicates\x0d\x0a 正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。\x0d\x0a\x0d\x0aReopen table\x0d\x0a 获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。\x0d\x0aRepair by sorting\x0d\x0a 修复指令正在排序以创建索引。\x0d\x0aRepair with keycache\x0d\x0a 修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。\x0d\x0aSearching rows for update\x0d\x0a 正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。\x0d\x0aSleeping\x0d\x0a 正在等待客户端发送新请求.\x0d\x0a\x0d\x0aSystem lock\x0d\x0a 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。\x0d\x0aUpgrading lock\x0d\x0a INSERT DELAYED正在尝试取得一个锁表以插入新记录。\x0d\x0aUpdating\x0d\x0a 正在搜索匹配的记录，并且修改它们。\x0d\x0a\x0d\x0aUser Lock\x0d\x0a 正在等待GET_LOCK()。\x0d\x0aWaiting for tables\x0d\x0a 该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。\x0d\x0awaiting for handler insert\x0d\x0a INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。\x0d\x0a 大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。\x0d\x0a 还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。\x0d\x0a\x0d\x0a例如如图：\x0d\x0a\x0d\x0a3、explain来了解SQL执行的状态\x0d\x0aexplain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。\x0d\x0a使用方法，在select语句前加上explain就可以了：\x0d\x0a例如：\x0d\x0aexplain select surname,first_name form a,b where a.id=b.id\x0d\x0a结果如图\x0d\x0a\x0d\x0aEXPLAIN列的解释\x0d\x0atable\x0d\x0a显示这一行的数据是关于哪张表的\x0d\x0atype\x0d\x0a这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL\x0d\x0apossible_keys\x0d\x0a显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句\x0d\x0akey\x0d\x0a实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句 中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引\x0d\x0akey_len\x0d\x0a使用的索引的长度。在不损失精确性的情况下，长度越短越好\x0d\x0aref\x0d\x0a显示索引的哪一列被使用了，如果可能的话，是一个常数\x0d\x0arows\x0d\x0aMYSQL认为必须检查的用来返回请求数据的行数\x0d\x0aExtra\x0d\x0a关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢\x0d\x0a\x0d\x0aextra列返回的描述的意义\x0d\x0aDistinct\x0d\x0a一旦MYSQL找到了与行相联合匹配的行，就不再搜索了\x0d\x0aNot exists\x0d\x0aMYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了\x0d\x0aRange checked for each Record（index map:#）\x0d\x0a没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一\x0d\x0aUsing filesort\x0d\x0a看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行\x0d\x0aUsing index\x0d\x0a列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候\x0d\x0aUsing temporary\x0d\x0a看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上\x0d\x0aWhere used\x0d\x0a使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）\x0d\x0aconst\x0d\x0a表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待\x0d\x0aeq_ref\x0d\x0a在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用\x0d\x0aref\x0d\x0a这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好\x0d\x0arange\x0d\x0a这个连接类型使用索引返回一个范围中的行，比如使用或
回答于 2022-11-16