mysql中高阶玩法系列（五）

Bertha 。 2023-01-04 13:26 106阅读 0赞

### 目录 ###

*  MySQL优化---EXPLAIN
 *  查看表的索引详情
 *  查看mysql数据库的运行状态
 *  巨人的肩膀
 *  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优化—EXPLAIN #

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。这可以帮助分析查询语句或是表结构的性能瓶颈。

*  表的读取顺序
 *  数据读取操作的操作类型
 *  哪些索引可以使用
 *  哪些索引被实际使用
 *  表之间的引用
 *  每张表有多少行被优化器查询

在执行Explain时MySQL不会执行查询，这是一个误区。事实上查询中包括子查询，那么MySQL实际上会执行子查询，将其结果放在一个临时表中，然后完成外层的查询优化。

> EXPLAIN的不足
> 
>  *  无法知道触发器，存储过程是如何影响查询
>  *  无法得知mysql在查询执行中所做的特定优化
>  *  无法显示关于查询的执行计划的所有信息
>  *  并不区分具有相同名字的事物。例如，对内存排序和临时文件都使用filesort，并且对于磁盘上和内存中的临时表都显示using temporary
>  *  信息可能会误导。例如，会对一个有着很小limit的查询显示全索引扫描

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>select查询的序列号，即sql语句执行的顺序，表示查询中执行select子句或操作表的顺序</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>1. <code>SIMPLE</code>：表示简单的select，没有union和子查询<br>2. <code>PRIMARY</code>：查询中若包含任何复杂的子部分，如果有子查询或者联合查询等则是primary<br>3. <code>DERIVED</code>：用来表示包含在FROM子句的子查询中的SELECT， MySQL会递归执行并将结果放到一个临时表中。服务器内部称 其“派生表”，因为该临时表是从子查询中派生来的。<br>4. <code>SUBQUERY</code>：在SELECT或WHERE列表中包含了子查询<br>5. <code>DEPENDENT SUBQUERY</code>：在SELECT或WHERE列表中包含了子查询,子查询基于外层<br>6. <code>UNCACHEABLE SUBQUREY</code>：结果集无法缓存的子查询，必须重新评估外部查询的每一行<br>7. <code>UNION</code>：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED<br>8. <code>DEPENDENT UNION</code>：UNION中第二个或之后的SELECT语句取决于外面的查询<br>9. <code>UNION RESULT</code>：UNION的结果<br>10. <code>MATERIALIZED</code>：物化子查询<br>11. <code>UNCACHEABLE UNION</code>：UNION中第二个或之后的SELECT，属于无法缓存的子查询</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>表示这一行的数据是关于哪张表的</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>该列显示的为分区表命中的分区情况</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>表示查询使用了何种类型。从最好到最差的连接类型为system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_null &gt; index_merge &gt; unique_subquery &gt; index_subquery &gt; range(尽量保证) &gt; index &gt; ALL。一般来说，得保证查询至少达到range级别，最好能达到ref。<br>1. <code>system</code>：系统表，表中只有一行数据，这是const类型的特列<br>2. <code>const</code>：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以速度很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量，如id=1; id为 主键或唯一键<br>3. <code>eq_ref</code>：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描，返回某单一行的数据(通常在联接时出现，查询使用的索引为主键或惟一键)<br>4. <code>ref</code>：非唯一性索引扫描，返回匹配某个单独值的所有行。<br>5. <code>fulltext</code>：进行全文索引检索。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引<br>6. <code>ref_or_null</code>：对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。<br>7. <code>index_merge</code>：在查询过程中需要多个索引组合使用，然后对索引结果进行合并（merge），再读取表数据，通常出现在有 or 的关键字的sql中。<br>8. <code>unique_subquery</code>：子查询中的返回结果字段组合是主键或唯一约束。用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。<br>9. <code>index_subquery</code>：利用索引来关联子查询，不再全表扫描。子查询中的返回结果字段组合是一个索引（或索引组合）<br>10. <code>range</code>：只检索给定范围的行,使用一个索引来选择行，常见于使用 =, &lt;&gt;, &gt;, &gt;=, &lt;, &lt;=, IS NULL, &lt;=&gt;, BETWEEN, IN()或者like等运算符的查询中，并且该字段上建有索引时发生的情况(注:不一定好于index)<br>11. <code>index</code>：全索引扫描，以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描。index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）<br>12. <code>ALL</code>：全表扫描，应该尽量避免</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际使用的索引。如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>使用的索引的长度。在不损失精确性的情况下，长度越短越好（key_len显示的值为索引字段的最大可能长度，并非实际使用长度）</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>mysql估算的需要扫描的行数。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>关于MYSQL如何解析查询的额外信息，主要有以下几种<br>1. <code>using index</code>：所需数据只需在 Index 即可全部获得，不用扫描表数据文件, 表示查询在索引树中就可查找所需数据, 往往说明性能不错<br>2. <code>using where</code>：使用到where来过滤数据. 不是所有的where clause都要显示using where. 如以=方式访问索引<br>3. <code>using tmporary</code>：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，　used_tmp_disk_table才能看出来。<br>4. <code>using filesort</code>：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大。MySQL中无法利用索引完成的排序操作称为“文件排序”(当使用order by v1,而没用到索引时,就会使用额外的排序)<br>5. <code>range checked for eache record(index map:N)</code>：通过 MySQL 官方手册的描述，当 MySQL Query Optimizer 没有找到理想的索引，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySQL检查是否可以使用range或 index_merge访问方法来索取行。这是使用索引的最慢的连接之一<br>6. <code>using join buffer</code>：使用了join缓存，主要是减少内表的循环数量以及比较顺序地扫描查询。<br>7. <code>impossible where noticed after reading const tables</code>：表明无法通过where获取到任何元组，where子句的值总是false。MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果。<br>8. <code>select tables optimized away</code>：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候。<br>9. <code>distinct</code>：在select部分使用了distinc关键字<br>10. <code>full scan on null key</code>：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。<br>11. <code>using index for group-by</code>：数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。<br>12. <code>using where with pushed condition</code>：这是一个仅仅在 NDBCluster存储引擎中才会出现的信息，而且还须要通过打开 Condition Pushdown 优化功能才可能被使用。<br>13. <code>no tables</code>：Query 语句中使用 FROM DUAL或不包含任何 FROM子句。<br>14. <code>not exists</code>：在某些左连接中，MySQL Query Optimizer通过改变原有 Query 的组成而使用的优化方法，可以部分减少数据访问次数。</td> 
  </tr> 
 </tbody> 
</table>

# 查看表的索引详情 #

show index from '表名'

mysql> show index from user \G;
    *************************** 1. row ***************************
            Table: user
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: id
        Collation: A
      Cardinality: 4
         Sub_part: NULL
           Packed: NULL
             Null: 
       Index_type: BTREE
          Comment: 
    Index_comment: 
    *************************** 2. row ***************************
            Table: user
       Non_unique: 1
         Key_name: idx_created_at
     Seq_in_index: 1
      Column_name: created_at
        Collation: A
      Cardinality: 3
         Sub_part: NULL
           Packed: NULL
             Null: 
       Index_type: BTREE
          Comment: 
    Index_comment: 
    
    2 rows in set (0.00 sec)

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Table</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>Non_unique</td> 
   <td>如果索引不能包括重复词，则为0。如果可以，则为1。</td> 
  </tr> 
  <tr> 
   <td>Key_name</td> 
   <td>索引名</td> 
  </tr> 
  <tr> 
   <td>Seq_in_index</td> 
   <td>索引中的列顺序号, 从 1 开始</td> 
  </tr> 
  <tr> 
   <td>Column_name</td> 
   <td>字段名</td> 
  </tr> 
  <tr> 
   <td>Collation</td> 
   <td>字段在索引中是如何被排序的，大概意思就是字符序。在MySQL中，这可以有值A（升序) 或NULL（不排序)。</td> 
  </tr> 
  <tr> 
   <td>Cardinality</td> 
   <td>基数的意思，索引中唯一值的数量的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。</td> 
  </tr> 
  <tr> 
   <td>Sub_part</td> 
   <td>前置索引的意思，如果该列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。</td> 
  </tr> 
  <tr> 
   <td>Packed</td> 
   <td>指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。</td> 
  </tr> 
  <tr> 
   <td>Null</td> 
   <td>索引的列中含有NULL。含有NULL则为YES。如果没有，则这里显示为空。</td> 
  </tr> 
  <tr> 
   <td>Index_type</td> 
   <td>索引类型（BTREE, FULLTEXT, HASH, RTREE）</td> 
  </tr> 
  <tr> 
   <td>Comment</td> 
   <td>字段的评注</td> 
  </tr> 
  <tr> 
   <td>Index_comment</td> 
   <td>索引的评注</td> 
  </tr> 
 </tbody> 
</table>

# 查看mysql数据库的运行状态 #

可以列出MySQL数据库的基本参数。比如编码信息，MySQL版本，查询次数等等。

status

mysql> status;
    --------------
    mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper
    
    Connection id:		2
    Current database:	test
    Current user:		root@localhost
    SSL:			Not in use
    Current pager:		stdout
    Using outfile:		''
    Using delimiter:	;
    Server version:		5.7.21-log MySQL Community Server (GPL)
    Protocol version:	10
    Connection:		Localhost via UNIX socket
    Server characterset:	latin1
    Db     characterset:	utf8mb4
    Client characterset:	utf8
    Conn.  characterset:	utf8
    UNIX socket:		/tmp/mysql.sock
    Uptime:			3 hours 26 min 26 sec
    
    Threads: 1  Questions: 32  Slow queries: 0  Opens: 112  Flush tables: 1  Open tables: 105  Queries per second avg: 0.002
    --------------

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Connection id</td> 
   <td>连接ID。</td> 
  </tr> 
  <tr> 
   <td>Current database</td> 
   <td>当前数据库名</td> 
  </tr> 
  <tr> 
   <td>Current user</td> 
   <td>当前连接的登录用户</td> 
  </tr> 
  <tr> 
   <td>SSL</td> 
   <td>是否使用ssl</td> 
  </tr> 
  <tr> 
   <td>Current pager</td> 
   <td>标准输出到屏幕</td> 
  </tr> 
  <tr> 
   <td>Using outfile</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Using delimiter</td> 
   <td>使用的语句结束符</td> 
  </tr> 
  <tr> 
   <td>Server version</td> 
   <td>当前mysql服务器的版本号</td> 
  </tr> 
  <tr> 
   <td>Protocol version</td> 
   <td>协议版本</td> 
  </tr> 
  <tr> 
   <td>Connection</td> 
   <td>使用的连接类型，如本地连接数据库用的是unix sock 方式</td> 
  </tr> 
  <tr> 
   <td>Server characterset</td> 
   <td>mysql server使用字符集类型</td> 
  </tr> 
  <tr> 
   <td>Db characterset</td> 
   <td>数据库使用的字符集类型</td> 
  </tr> 
  <tr> 
   <td>Client characterset</td> 
   <td>客户端使用的字符集类型</td> 
  </tr> 
  <tr> 
   <td>Conn.characterset</td> 
   <td>连接使用的字符集类型</td> 
  </tr> 
  <tr> 
   <td>UNIX socket</td> 
   <td>socket文件路径</td> 
  </tr> 
  <tr> 
   <td>Uptime</td> 
   <td>数据库开机运行时间</td> 
  </tr> 
  <tr> 
   <td>Threads</td> 
   <td>数据库连接线程数</td> 
  </tr> 
  <tr> 
   <td>Questions</td> 
   <td>已经发送给服务器的查询的个数</td> 
  </tr> 
  <tr> 
   <td>Slow queries</td> 
   <td>慢查询数</td> 
  </tr> 
  <tr> 
   <td>Opens</td> 
   <td>在table cache记录中当前被打开的非TEMPORARY表的数量</td> 
  </tr> 
  <tr> 
   <td>Flush tables</td> 
   <td>关闭所有已打开的表对象，同时将查询缓存中的结果清空</td> 
  </tr> 
  <tr> 
   <td>Open tables</td> 
   <td>mysqld已经打开了多少表的数量</td> 
  </tr> 
  <tr> 
   <td>Queries per second avg</td> 
   <td>QPS，计算方式是 Queries 除以 Uptime 时间</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://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA][https_mp.weixin.qq.com_s_1MsyxhtG6Zk3Q9gIV2QVbA]  
[http://blog.itpub.net/30126024/viewspace-2374623/][http_blog.itpub.net_30126024_viewspace-2374623]  
[https://www.cnblogs.com/116970u/p/10984012.html][https_www.cnblogs.com_116970u_p_10984012.html]  
[https://blog.51cto.com/lee90/1957629][https_blog.51cto.com_lee90_1957629]

# VChat #

一个没有哆啦A梦和静香的IT码农，不专业Gopher  
![在这里插入图片描述][20201206221123294.png]

[20190725224136243.]: /images/20221119/e3cf31ec6eaf415b8d65341214f1fd99.png
[20190725230908971.]: /images/20221119/abf3e8bc7a6a412eb5c47eb7bdd9c6fe.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_mp.weixin.qq.com_s_1MsyxhtG6Zk3Q9gIV2QVbA]: https://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA
[http_blog.itpub.net_30126024_viewspace-2374623]: http://blog.itpub.net/30126024/viewspace-2374623/
[https_www.cnblogs.com_116970u_p_10984012.html]: https://www.cnblogs.com/116970u/p/10984012.html
[https_blog.51cto.com_lee90_1957629]: https://blog.51cto.com/lee90/1957629
[20201206221123294.png]: /images/20221119/f67e39f735744503a08527884e42f008.png