mysql中高阶玩法系列(七) 阳光穿透心脏的1/2处 2022-09-05 00:14 117阅读 0赞 ### 目录 ### * 三种语句结束符 * 查看当前SQL执行状态、是否锁表 * 巨人的肩膀 * VChat -------------------- 先定义一张表,在之后的讲解中的所有例子,都是基于这张表做的演示,那么就定义一张最常见的用户表吧。 CREATE TABLE `user` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) COLLATE UTF8MB4_UNICODE_CI NOT NULL, `pwd` VARCHAR(255) COLLATE UTF8MB4_UNICODE_CI NOT NULL, `created_at` INT(8) UNSIGNED NOT NULL, PRIMARY KEY (`id`), KEY `idx_created_at` (`created_at`) ) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI; 插入几条数据,方便之后快乐的玩耍 INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('1', '牛A', '555', '1559318400'); INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('2', '牛B', '555', '1559318400'); INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('3', '牛C', '555', '1573441871'); INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('4', '牛X', '555', '1558329240'); ![在这里插入图片描述][20190725224136243.] ![在这里插入图片描述][20190725230908971.] # 三种语句结束符 # MySQL中有`\g`、`\G`与`;`三种语句结束符 1. `;` 其中最常用的就是`;` mysql> select * from user; +----+------+-----+------------+ | id | name | pwd | created_at | +----+------+-----+------------+ | 1 | 牛A | 555 | 1559318400 | | 2 | 牛B | 555 | 1559318400 | | 3 | 牛C | 555 | 1573441871 | | 4 | 牛X | 555 | 1558329240 | +----+------+-----+------------+ 4 rows in set (0.00 sec) 1. `\g` 实际上`\g`是和`;`是等价的,效果一样 mysql> select * from user \g +----+------+-----+------------+ | id | name | pwd | created_at | +----+------+-----+------------+ | 1 | 牛A | 555 | 1559318400 | | 2 | 牛B | 555 | 1559318400 | | 3 | 牛C | 555 | 1573441871 | | 4 | 牛X | 555 | 1558329240 | +----+------+-----+------------+ 4 rows in set (0.00 sec) 1. `\G` `\G`的作用是将查到的结构旋转90度变成纵向,显示的时候,每行数据,所有列均竖向打印,以便更好地显示内容较长的记录。 mysql> select * from user \G *************************** 1. row *************************** id: 1 name: 牛A pwd: 555 created_at: 1559318400 *************************** 2. row *************************** id: 2 name: 牛B pwd: 555 created_at: 1559318400 *************************** 3. row *************************** id: 3 name: 牛C pwd: 555 created_at: 1573441871 *************************** 4. row *************************** id: 4 name: 牛X pwd: 555 created_at: 1558329240 4 rows in set (0.00 sec) # 查看当前SQL执行状态、是否锁表 # # 其实查询的是 information_schema.processlist 表 show full processlist 显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查看他们自己的进程,但是如果他们拥有process权限,就可以查看所有人的进程,包括密码。 +----+------+-----------+------+---------+------+----------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+-----------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show full processlist | +----+------+-----------+------+---------+------+----------+-----------------------+ 1 row in set (0.01 sec) <table> <thead> <tr> <th>字段名</th> <th>解释</th> </tr> </thead> <tbody> <tr> <td>Id</td> <td>唯一连接标识</td> </tr> <tr> <td>User</td> <td>建立此连接所使用的用户名</td> </tr> <tr> <td>Host</td> <td>建立此连接的机器的IP与端口</td> </tr> <tr> <td>db</td> <td>线程的默认数据库,如果没有则是 NULL</td> </tr> <tr> <td>Command</td> <td>线程根据客户端行为正在执行的命令的类型<br>1. <code>Binlog Dump</code>:这是复制源上的线程,用于将二进制日志内容发送到副本。<br>2. <code>Change user</code>:线程正在执行更改用户操作。<br>3. <code>Close stmt</code>:线程正在关闭准备好的语句。<br>4. <code>Connect</code>:副本连接到其源。<br>5. <code>Connect Out</code>:副本正在连接其源。<br>6. <code>Create DB</code>:线程正在执行创建数据库操作。<br>7. <code>Daemon</code>:该线程在服务器内部,而不是为 Client 端连接提供服务的线程。<br>8. <code>Debug</code>:该线程正在生成调试信息。<br>9. <code>Delayed insert</code>:该线程是延迟插入处理程序。<br>10. <code>Drop DB</code>:线程正在执行放置数据库操作。<br>11. <code>Error</code>:<br>12. <code>Execute</code>:线程正在执行准备好的语句。<br>13. <code>Fetch</code>:线程正在从执行准备好的语句中获取结果。<br>14. <code>Field List</code>:线程正在检索 table 列的信息。<br>15. <code>Init DB</code>:线程正在选择默认数据库。<br>16. <code>Kill</code>:该线程正在杀死另一个线程。<br>17. <code>Long Data</code>:执行准备好的语句的结果是线程正在检索长数据。<br>18. <code>Ping</code>:线程正在处理服务器 ping 请求。<br>19. <code>Prepare</code>:该线程正在准备一个准备好的语句。<br>20. <code>Processlist</code>:该线程正在生成有关服务器线程的信息。<br>21. <code>Query</code>:线程正在执行一条语句。<br>22. <code>Quit</code>:线程正在终止。<br>23. <code>Refresh</code>:该线程是刷新 table,日志或缓存,或者重置状态变量或复制服务器信息。<br>24. <code>Register Slave</code>:线程正在注册副本服务器。<br>25. <code>Reset stmt</code>:线程正在重置准备好的语句。<br>26. <code>Set option</code>:该线程正在设置或重置 Client端语句执行选项。<br>27. <code>Shutdown</code>:线程正在关闭服务器。<br>28. <code>Sleep</code>:线程正在 awaitClient 端向其发送新语句。<br>29. <code>Statistics</code>:该线程正在生成服务器状态信息。<br>30. <code>Table Dump</code>:线程正在将 table 内容发送到副本。<br>31. <code>Time</code>:Unused.</td> </tr> <tr> <td>Time</td> <td>连接持续时间,单位是秒</td> </tr> <tr> <td>State</td> <td>显示当前连接的sql语句的状态<br>1. <code>After create</code>:当线程创建表(包括内部临时表)时,会在创建表的函数的末尾创建。即使由于某些错误而无法创建表,也会使用此状态。<br>2. <code>altering table</code>:服务器正在执行就地ALTER TABLE。<br>3. <code>Analyzing</code>:线程正在计算MyISAM表密钥分布(例如:for ANALYZE TABLE)。<br>4. <code>checking permissions</code>:线程正在检查服务器是否具有执行语句所需的权限。<br>5. <code>Checking table</code>:线程正在执行表检查操作。<br>6. <code>cleaning up</code>:线程已经处理了一个命令,正在准备释放内存并重置某些状态变量。<br>7. <code>closing tables</code>:线程将更改的表数据刷新到磁盘并关闭已用表。这应该是一个快速的操作。如果没有,请验证您是否没有完整的磁盘,并且磁盘没有被非常大的使用。<br>8. <code>committing alter table to storage engine</code>:服务器已经完成就位ALTER TABLE并提交结果。<br>9. <code>converting HEAP to MyISAM</code>:<br>10. <code>copy to tmp table</code>:线程正在处理ALTER TABLE语句。此状态发生在已创建新结构的表之后,但是将行复制到该表之前。对于此状态的线程,可以使用性能模式来获取有关复制操作的进度。<br>11. <code>Copying to grouptable</code>:如果语句具有不同ORDER BY和GROUP BY标准,各行按组排列和复制到一个临时表。<br>12. <code>Copying to tmp table</code>:服务器正在复制磁盘到内存的临时表,是直接在磁盘创建的临时表而并非从内存转到磁盘的临时表。<br>13. <code>Copying to tmp table on disk</code>:对于线程将临时表从内存中更改为基于磁盘的格式存储以节省内存后,又把临时表从磁盘复制到内存时的状态。<br>14. <code>Creating index</code>:线程正在处理ALTER TABLE … ENABLE KEYS一个MyISAM表。<br>15. <code>Creating sort index</code>:线程正在处理一个SELECT使用内部临时表解析的线程 。<br>16. <code>creating table</code>:线程正在创建一个表,这包括创建临时表。<br>17. <code>Creating tmp table</code>:线程正在内存或磁盘上创建临时表。如果表在内存中创建,但后来转换为磁盘表,则该操作中的状态将为Copying to tmp table on disk。<br>18. <code>deleting from main table</code>:服务器正在执行多表删除的第一部分,它仅从第一个表中删除,并从其他(引用)表中保存要用于删除的列和偏移量。<br>19. <code>deleting from reference tables</code>:服务器正在执行多表删除的第二部分,并从其他表中删除匹配的行。<br>20. <code>discard_or_import_tablespace</code>:线程正在处理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE声明。<br>21.<code>end</code>:这发生在结束,但的清理之前ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT,或UPDATE语句。<br>22. <code>executing</code>:该线程已经开始执行一个语句。<br>23. <code>Execution of init_command</code>:线程正在init_command系统变量的值中执行语句 。<br>24. <code>freeing items</code>:线程已经执行了一个命令,在这种状态下完成的项目的一些释放涉及查询缓存,这个状态通常在后面cleaning up。<br>25. <code>FULLTEXT initialization</code>:服务器正在准备执行自然语言全文搜索。<br>26. <code>init</code>:此操作在初始化ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE之前发生,服务器在该状态中采取的操作包括刷新二进制日志、Innodb日志和一些查询缓存清理操作。对于最终状态, 可能会发生以下操作<br>27. <code>Killed</code>:执行KILL语句,向线程发送了一个声明,下次检查kill标志时应该中断。在MySQL的每个主循环中检查该标志,但在某些情况下,线程可能需要很短时间才能死掉。如果线程被某个其他线程锁定,则一旦其他线程释放锁定,该kill就会生效。<br>28. <code>logging slow query</code>:线程正在向慢查询日志写入语句。<br>29. <code>login</code>:连接线程的初始状态,直到客户端成功认证为止。<br>30. <code>manage keys</code>:服务器启用或禁用表索引。<br>31. <code>Opening tables</code>:线程正在尝试打开一个表,这应该是非常快的程序,除非有事情阻止打开。例如,一个ALTER TABLE或一个LOCK TABLE语句可以阻止打开一个表,直到语句完成。还可能需要关注table_open_cache参数的值是否足够大。对于系统表,使用Opening system tables状态。<br>32. <code>optimizing</code>:服务器正在执行查询的初始优化。<br>33. <code>preparing</code>:此状态发生在查询优化期间。<br>34. <code>preparing for alter table</code>:服务器正在准备就地执行ALTER TABLE。<br>35. <code>Purging old relay logs</code>:线程正在删除不需要的中继日志文件。<br>36. <code>query end</code>:处理查询之后,freeing items状态之前会发生这种状态。<br>37. <code>Reading from net</code>:<br>38. <code>Removing duplicates</code>:该查询的使用SELECT DISTINCT方式使得MySQL不能在早期阶段优化不同的操作。因此,MySQL需要一个额外的阶段来删除所有重复的行,然后将结果发送给客户端。<br>39. <code>removing tmp table</code>:处理语句后,该线程正在删除一个内部临时表SELECT 。如果没有创建临时表,则不使用该状态。<br>40. <code>rename</code>:线程正在重命名一个表。<br>41. <code>rename result table</code>:线程正在处理一个ALTER TABLE语句,已经创建了新表,并重新命名它来替换原始表。<br>42. <code>Reopen tables</code>:线程获得了表的锁,但在获得基础表结构更改的锁之后注意到。它释放了锁,关闭了table,并试图重新打开它。<br>43. <code>Repair by sorting</code>:修复代码正在使用排序来创建索引。<br>44. <code>Repair done</code>:线程已经完成了一个MyISAM表的多线程修复 。<br>45. <code>Repair with keycache</code>:修复代码通过密钥缓存逐个使用创建密钥,这比慢得多Repair by sorting。<br>46. <code>Rolling back</code>:线程正在回滚事务。<br>47. <code>Saving state</code>:对于MyISAM表操作(如修复或分析),线程将新的表状态保存到.MYI文件头。状态包括行数, AUTO_INCREMENT计数器和键分布等信息。<br>48. <code>Searching rows for update</code>:线程正在进行第一阶段,以便在更新之前查找所有匹配的行。如果UPDATE要更改用于查找涉及的行的索引,则必须执行此操作 。<br>49. <code>Sending data</code>:线程正在读取和处理SELECT语句的行,并将数据发送到客户端。由于在此状态期间发生的操作往往执行大量的磁盘访问(读取),所以在给定查询的整个生命周期内通常是最长的运行状态。<br>50. <code>setup</code>:线程正在开始一个ALTER TABLE操作。<br>51. <code>Sorting for group</code>:线程正在做一个满足一个GROUP BY。<br>52. <code>Sorting for order</code>:线程正在做一个满足一个ORDER BY。<br>53. <code>Sorting index</code>:线程是排序索引页,以便在MyISAM表优化操作期间更有效地访问。<br>54. <code>Sorting result</code>:对于一个SELECT语句,这类似于Creating sort index,但是对于非临时表。<br>55. <code>statistics</code>:服务器正在计算统计信息以开发查询执行计划。如果一个线程长时间处于这种状态,服务器可能是磁盘绑定的,执行其他工作。<br>56. <code>System lock</code>:线程已经调用mysql_lock_tables() ,且线程状态从未更新。这是一个非常普遍的状态,可能由于许多原因而发生。例如, 线程将请求或正在等待表的内部或外部系统锁。当InnoDB在执行锁表时等待表级锁时, 可能会发生这种情况。如果此状态是由于请求外部锁而导致的,并且不使用正在访问相同表的多个mysqld服务器MyISAM,则可以使用该–skip-external-locking选项禁用外部系统锁 。但是,默认情况下禁用外部锁定,因此这个选项很有可能不起作用。因为SHOW PROFILE,这个状态意味着线程正在请求锁定(不等待它)。对于系统表,使用Locking system tables状态。<br>57. <code>update</code>:线程正在准备开始更新表。<br>58. <code>Updating</code>:线程正在搜索要更新的行并正在更新它们。<br>59. <code>updating maintable</code>:<br>60. <code>updating reference tables</code>:服务器正在执行多表更新的第二部分,并从其他表更新匹配的行。<br>61. <code>User lock</code>:线程将要求或正在等待通过GET_LOCK()呼叫请求的咨询锁定 。因为 SHOW PROFILE,这个状态意味着线程正在请求锁定(不等待它)。<br>62. <code>User sleep</code>:线程调用了一个 SLEEP()调用。<br>63. <code>Waiting for commit lock</code>:FLUSH TABLES WITH READ LOCK正在等待提交锁。<br>64. <code>Waiting for global read lock</code>:FLUSH TABLES WITH READ LOCK正在等待全局读锁定或read_only正在设置全局系统变量。<br>65. <code>Waiting for tables</code>:线程得到一个通知,表格的底层结构已经改变,需要重新打开表以获得新的结构。但是,要重新打开表格,必须等到所有其他线程都关闭该表。如果另一个线程已使用FLUSH TABLES或下面的语句之一<br>66. <code>Waiting for table flush</code>:线程正在执行FLUSH TABLES并正在等待所有线程关闭它们的表,或者线程得到一个通知,表中的底层结构已经改变,并且需要重新打开表以获得新的结构。但是,要重新打开表,必须等到所有其他线程都关闭该表。如果另一个线程已使用FLUSH TABLES或下面的语句之一<br>67. <code>Waiting for table level lock</code>:<br>68. <code>Waiting for event metadata lock</code>:<br>69. <code>Waiting for global read lock</code>:FLUSH TABLES WITH READ LOCK正在等待全局读锁定或read_only正在设置全局系统变量。<br>70. <code>Waiting for schema metadata lock</code>:<br>71. <code>Waiting for stored function metadata lock</code>:<br>72. <code>Waiting for stored procedure metadata lock</code>:<br>73. <code>Waiting for table metadata lock</code>:当前线程正在等待获取该表的 metadata 独占锁,但是该锁现在被其它线程占用了,如果一直不释放的话,就只能一直处于等待状态,后面对该表查询的线程也只能处于等待的状态<br>74. <code>Waiting for trigger metadata lock</code>:<br>75. <code>Waiting on cond</code>:线程等待条件成为true的一般状态,没有特定的状态信息可用。<br>76. <code>Writing to net</code>:服务器正在将数据包写入网络,如果一个线程长时间在执行并且一直处于Writing to net状态,那么一直在发送数据包到网络,可以试着调整max_allowed_packet大小。另外,这可能会导致其他线程大量阻塞。</td> </tr> <tr> <td>Info</td> <td>线程正在执行的语句,如果没有执行则为 NULL</td> </tr> </tbody> </table> # 巨人的肩膀 # > 从他人的工作中汲取经验来避免自己的错误重复,正如我们是站在巨人的肩膀上才能做出更好的成绩。 [https://docs.pingcap.com/zh/tidb/stable/mysql-schema][https_docs.pingcap.com_zh_tidb_stable_mysql-schema] [http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/preface.com.coder114.cn.html][http_www.searchdoc.cn_rdbms_mysql_dev.mysql.com_doc_refman_5.7_en_preface.com.coder114.cn.html] [https://www.docs4dev.com/docs/zh/mysql/5.7/reference/general-thread-states.html][https_www.docs4dev.com_docs_zh_mysql_5.7_reference_general-thread-states.html] [https://www.docs4dev.com/docs/zh/mysql/5.7/reference/thread-commands.html][https_www.docs4dev.com_docs_zh_mysql_5.7_reference_thread-commands.html] [https://www.jb51.net/article/156313.htm][https_www.jb51.net_article_156313.htm] # VChat # 一个没有哆啦A梦和静香的IT码农,不专业Gopher ![在这里插入图片描述][20201206221123294.png] [20190725224136243.]: /images/20220829/ca1e880cf626434388236b6f0754dbdd.png [20190725230908971.]: /images/20220829/9f5f5bc586a04ab5b5da0942f8aa55cc.png [https_docs.pingcap.com_zh_tidb_stable_mysql-schema]: https://docs.pingcap.com/zh/tidb/stable/mysql-schema [http_www.searchdoc.cn_rdbms_mysql_dev.mysql.com_doc_refman_5.7_en_preface.com.coder114.cn.html]: http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/preface.com.coder114.cn.html [https_www.docs4dev.com_docs_zh_mysql_5.7_reference_general-thread-states.html]: https://www.docs4dev.com/docs/zh/mysql/5.7/reference/general-thread-states.html [https_www.docs4dev.com_docs_zh_mysql_5.7_reference_thread-commands.html]: https://www.docs4dev.com/docs/zh/mysql/5.7/reference/thread-commands.html [https_www.jb51.net_article_156313.htm]: https://www.jb51.net/article/156313.htm [20201206221123294.png]: /images/20220829/4ca5be8291094b10baf3446378d1ebf3.png
还没有评论,来说两句吧...