关于mysql:35-张图带你-MySQL-调优

28次阅读

共计 8265 个字符，预计需要花费 21 分钟才能阅读完成。

这是 MySQL 根底系列的第四篇文章，之前的三篇文章见如下链接

138 张图带你 MySQL 入门

47 张图带你 MySQL 进阶！！！

炸裂！MySQL 82 张图带你飞

个别传统互联网公司很少接触到 SQL 优化问题，其起因是数据量小，大部分厂商的数据库性能可能满足日常的业务需要，所以不须要进行 SQL 优化，然而随着应用程序的一直变大，数据量的激增，数据库本身的性能跟不上了，此时就须要从 SQL 本身角度来进行优化，这也是咱们这篇文章所探讨的。

SQL 优化步骤

当面对一个须要优化的 SQL 时，咱们有哪几种排查思路呢？

通过 show status 命令 理解 SQL 执行次数

首先，咱们能够应用 show status 命令查看服务器状态信息。show status 命令会显示每个服务器变量 variable_name 和 value，状态变量是只读的。如果应用 SQL 命令，能够应用 like 或者 where 条件来限度后果。like 能够对变量名做规范模式匹配。

图我没有截全，上面还有很多变量，读者能够本人尝试一下。也能够在操作系统上应用 mysqladmin extended-status 命令来获取这些音讯。

然而我执行 mysqladmin extended-status 后，呈现这个谬误。

应该是我没有输出明码的起因，应用 mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status 后，问题解决。

这里须要留神一下 show status 命令中能够增加统计后果的级别，这个级别有两个

  • session 级：默认以后链接的统计后果
  • global 级：自数据库上次启动到当初的统计后果

如果不指定统计后果级别的话，默认应用 session 级别。

对于 show status 查问进去的统计后果，有两类参数须要留神下，一类是以 Com_ 为结尾的参数，一类是以 Innodb_ 为结尾的参数。

上面是 Com_ 为结尾的参数，参数很多，我同样没有截全。

Com_xxx 示意的是每个 xxx 语句执行的次数，咱们通常关怀的是 select、insert、update、delete 语句的执行次数，即

  • Com_select：执行 select 操作的次数，一次查问会使后果 + 1。
  • Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
  • Com_update：执行 UPDATE 操作的次数。
  • Com_delete：执行 DELETE 操作的次数。

以 Innodb_ 为结尾的参数次要有

  • Innodb_rows_read：执行 select 查问返回的行数。
  • Innodb_rows_inserted：执行 INSERT 操作插入的行数。
  • Innodb_rows_updated：执行 UPDATE 操作更新的行数。
  • Innodb_rows_deleted：执行 DELETE 操作删除的行数。

通过下面这些参数执行后果的统计，咱们可能大抵理解到以后数据库是以更新（包含插入、删除）为主还是查问为主。

除此之外，还有一些其余参数用于理解数据库的根本状况。

  • Connections：查问 MySQL 数据库的连贯次数，这个次数是不论连贯是否胜利都算上。
  • Uptime：服务器的工作工夫。
  • Slow_queries：满查问次数。
  • Threads_connected：查看以后关上的连贯的数量。

上面这个博客汇总了简直所有 show status 的参数，能够当作参考手册。

https://blog.csdn.net/ayay_87…

定位执行效率较低的 SQL

定位执行效率比较慢的 SQL 语句，个别有两种形式

  • 能够通过 慢查问日志 来定位哪些执行效率较低的 SQL 语句。

MySQL 中提供了一个慢查问的日志记录性能，能够把查问 SQL 语句工夫大于多少秒的语句写入慢查问日志，日常保护中能够通过慢查问日志的记录信息疾速精确地判断问题所在。用 –log-slow-queries 选项启动时，mysqld 会写一个蕴含所有执行工夫超过 long_query_time 秒的 SQL 语句的日志文件，通过查看这个日志文件定位效率较低的 SQL。

比方咱们能够在 my.cnf 中增加如下代码，而后退出重启 MySQL。

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

通常咱们设置最长的查问工夫是 2 秒，示意查问工夫超过 2 秒就记录了，通常状况下 2 秒就够了，然而对于很多 WEB 利用来说，2 秒工夫还是比拟长的。

也能够通过命令来开启：

咱们先查问 MySQL 慢查问日志是否开启

show variables like "%slow%";

启用慢查问日志

set global slow_query_log='ON';

而后再次查问慢查问是否开启

如图所示，咱们曾经开启了慢查问日志。

慢查问日志会在查问完结当前才记录，所以在利用反馈执行效率呈现问题的时候慢查问日志并不能定位问题，此时应该应用 show processlist 命令查看以后 MySQL 正在进行的线程。包含线程的状态、是否锁表等，能够实时的查看 SQL 执行状况。同样，应用 mysqladmin processlist 语句也能失去此信息。

上面就来解释一下各个字段对应的概念

  • Id：Id 就是一个标示，在咱们应用 kill 命令杀死过程的时候很有用，比方 kill 过程号。
  • User：显示以后的用户，如果不是 root，这个命令就只显示你权限范畴内的 SQL 语句。
  • Host：显示 IP，用于追踪问题
  • Db：显示这个过程目前连贯的是哪个数据库，为 null 是还没有 select 数据库。
  • Command：显示以后连贯锁执行的命令，个别有三种：查问 query，休眠 sleep，连贯 connect。
  • Time：这个状态继续的工夫，单位是秒
  • State：显示以后 SQL 语句的状态，十分重要，上面会具体解释。
  • Info：显示这个 SQL 语句。

State 列十分重要，对于这个列的内容比拟多，读者能够参考一下这篇文章

https://blog.csdn.net/weixin_…

这外面波及线程的状态、是否锁表等选项，能够实时的查看 SQL 的执行状况，同时对一些锁表进行优化。

通过 EXPLAIN 命令剖析 SQL 的执行打算

通过以上步骤查问到效率低的 SQL 语句后，能够通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包含在 SELECT 语句执行过程中表如何连贯和连贯的程序。

比方咱们应用上面这条 SQL 语句来剖析一下执行打算

explain select * from test1;

上表中波及内容如下

  • select_type：示意常见的 SELECT 类型，常见的有 SIMPLE，SIMPLE 示意的是简略的 SQL 语句，不包含 UNION 或者子查问操作，比方上面这段就是 SIMPLE 类型。

PRIMARY，查问中最外层的 SELECT（如两表做 UNION 或者存在子查问的外层的表操作为 PRIMARY，内层的操作为 UNION），比方上面这段子查问。

UNION，在 UNION 操作中，查问中处于内层的 SELECT（内层的 SELECT 语句与外层的 SELECT 语句没有依赖关系时）。

SUBQUERY：子查问中首个 SELECT（如果有多个子查问存在），如咱们下面的查问语句，子查问第一个是 sr（sys_role）表，所以它的 select_type 是 SUBQUERY。

  • table，这个选项示意输入后果集的表。

  • type，这个选项示意表的连贯类型，这个选项很有深入研究的价值，因为很多 SQL 的调优都是围绕 type 来讲的，然而这篇文章咱们次要围绕优化形式来开展的，type 这个字段咱们临时作为理解，这篇文章不过多深刻。

    type 这个字段会牵扯到连贯的性能，它的不同类型的性能由好到差别离是

    system：表中仅有一条数据时，该表的查问就像查问常量表一样。

    const：当表中只有一条记录匹配时，比方应用了表主键（primary key）或者表惟一索引（unique index）进行查问。

    eq-ref：示意多表连贯时应用表主键或者表惟一索引，比方

    select A.text, B.text where A.ID = B.ID

这个查问语句，对于 A 表中的每一个 ID 行，B 表中都只能有惟一的 B.Id 来进行匹配时。

ref：这个类型不如下面的 eq-ref 快，因为它示意的是因为对于表 A 中扫描的每一行，表 C 中有几个可能的行，C.ID 不是惟一的。

ref_or_null：与 ref 相似，只不过这个选项蕴含对 NULL 的查问。

index_merge：查问语句应用了两个以上的索引，比方常常在有 and 和 or 关键字呈现的场景，然而在因为 读取索引过多 导致其性能有可能还不如 range（前面说）。

unique_subquery：这个选项常常用在 in 关键字前面，子查问带有 where 关键字的子查问中，用 sql 来示意就是这样

value IN (SELECT primary_key FROM single_table WHERE some_expr)

range：索引范畴查问，常见于应用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等运算符的查问中。

index：索引全表扫描，把索引从头到尾扫一遍。

all：这个咱们接触的最多了，就是全表查问，select * from xxx，性能最差。

下面就是 type 内容的大抵解释，对于 type 咱们常常会在 SQL 调优的环节应用 explain 剖析其类型，而后改良查问形式，越凑近 system 其查问效率越高，越凑近 all 其查问效率越低。

  • possible_keys：示意查问时，可能应用的索引。
  • key：示意理论应用的索引。
  • key_len：索引字段的长度。
  • rows：扫描行的数量。
  • filtered：通过查问条件查问进去的 SQL 数量占用总行数的比例。
  • extra：执行状况的形容。

通过下面的剖析，咱们能够大抵确定 SQL 效率低的起因，一种十分无效的晋升 SQL 查问效率的形式就是应用索引，接下来我会解说一下如何应用索引进步查问效率。

索引

索引是数据库优化中最罕用也是最重要的伎俩，通过应用不同的索引能够解决大多数 SQL 性能问题，也是面试常常会问到的优化形式，围绕着索引，面试官能让你造出火箭来，所以总结一点就是索引十分十分重！要！不只是应用，你还要懂其原！理！

索引介绍

索引的目标就是用于疾速查找某一列的数据，对相干数据列应用索引可能大大提高查问操作的性能。不应用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相干的行，表越大查问数据所破费的工夫就越多。如果表中查问的列有索引，MySQL 可能疾速达到一个地位去搜寻数据文件，而不用查看所有数据，那么将会节俭很大一部分工夫。

索引分类

先来理解一下索引都有哪些分类。

  • 全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎反对全局索引，它的呈现是为了解决针对文本的含糊查问效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列。
  • 哈希索引(HASH)：哈希索引是 MySQL 中用到的惟一 key-value 键值对的数据结构，很适宜作为索引。HASH 索引具备一次定位的益处，不须要像树那样一一节点查找，然而这种查找适宜利用于查找单个键的状况，对于范畴查找，HASH 索引的性能就会很低。默认状况下，MEMORY 存储引擎应用 HASH 索引，但也反对 BTREE 索引。
  • B-Tree 索引：B 就是 Balance 的意思，BTree 是一种均衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 宽泛应用。
  • R-Tree 索引：R-Tree 在 MySQL 很少应用，仅反对 geometry 数据类型，反对该类型的存储引擎只有 MyISAM、BDb、InnoDb、NDb、Archive 几种，绝对于 B-Tree 来说，R-Tree 的劣势在于范畴查找。

从逻辑上来对 MySQL 进行分类，次要分为上面这几种

  • 一般索引：一般索引是最根底的索引类型，它没有任何限度。创立形式如下

    create index normal_index on cxuan003(id);

删除形式

drop index normal_index on cxuan003;

  • 惟一索引：惟一索引列的值必须惟一，容许有空值，如果是组合索引，则列值的组合必须惟一，创立形式如下

    create unique index normal_index on cxuan003(id);

  • 主键索引：是一种非凡的索引，一个表只能有一个主键，不容许有空值。个别是在建表的时候同时创立主键索引。

    CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,
         `title` char(255) NOT NULL ,
         PRIMARY KEY (`id`)
)

  • 组合索引：指多个字段上创立的索引，只有在查问条件中应用了创立索引时的第一个字段，索引才会被应用。应用组合索引时遵循最左前缀准则，上面咱们就会创立组合索引。

  • 全文索引：次要用来查找文本中的关键字，而不是间接与索引中的值相比拟，目前只有 char、varchar，text 列上能够创立全文索引，创立表的适宜增加全文索引

    CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

当然也能够间接创立全局索引

CREATE FULLTEXT INDEX index_content ON article(content)

索引应用

索引能够在创立表的时候进行创立，也能够独自创立，上面咱们采纳独自创立的形式，咱们在 cxuan004 上创立前缀索引

咱们应用 explain 进行剖析，能够看到 cxuan004 应用索引的状况

如果不想应用索引，能够删除索引，索引的删除语法是

索引应用细则

咱们在 cxuan005 上依据 id 和 hash 创立一个复合索引，如下所示

create index id_hash_index on cxuan005(id,hash);

而后依据 id 进行执行打算的剖析

explain select * from cxuan005 where id = '333';

能够发现，即便 where 条件中应用的不是复合索引（Id、hash），索引依然可能应用，这就是索引的前缀个性。然而如果只依照 hash 进行查问的话，索引就不会用到。

explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';

如果 where 条件应用了 like 查问，并且 % 不在第一个字符，索引才可能被应用。

对于复合索引来说，只能应用 id 进行 like 查问，因为 hash 列不管怎么查问都不会走索引。

explain select * from cxuan005 where id like '%1';

能够看到，如果第一个字符是 %，则没有应用索引。

explain select * from cxuan005 where id like '1%';

如果应用了 % 号，就会触发索引。

如果列名是索引的话，那么对列名进行 NULL 查问，将会触发索引。

explain select * from cxuan005 where id is null;

还有一些状况是存在索引然而 MySQL 并不会应用的状况。

  • 最简略的，如果应用索引后比不应用索引的效率还差，那么 MySQL 就不会应用索引。

  • 如果 SQL 中应用了 OR 条件，OR 前的条件列有索引，而前面的列没有索引的话，那么波及到的索引都不会应用，比方 cxuan005 表中，只有 id 和 hash 字段有索引，而 info 字段没有索引，那么咱们应用 or 进行查问。

    explain select * from cxuan005 where id = 111 and info = 'cxuan';

咱们从 explain 的执行后果能够看到，尽管 possible_keys 选项上依然有 id_hash_index 索引，然而从 key、key_len 能够得悉，这条 SQL 语句并未应用索引。

  • 在带有复合索引的列上查问不是第一列的数据，也不会应用索引。

    explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';

  • 如果 where 条件的列参加了计算，那么也不会应用索引

    explain select * from cxuan005 where id + '111' = '666';

  • 索引列应用函数，一样也不会应用索引

    explain select * from cxuan005 where concat(id,'111') = '666';

  • 索引列应用了 like，并且 % 位于第一个字符，则不会应用索引。
  • 在 order by 操作中，排序的列同时也在 where 语句中，将不会应用索引。

  • 当数据类型呈现隐式转换时，比方 varchar 不加单引号可能转换为 int 类型时，会使索引有效，触发全表扫描。比方上面这两个例子可能不言而喻的阐明这一点

  • 在索引列上应用 IS NOT NULL 操作

  • 在索引字段上应用 <>，!=。不等于操作符是永远不会用到索引的，因而对它的解决只会产生全表扫描。

对于设置索引然而索引没有失效的场景还有很多，这个须要小伙伴们工作中一直总结和欠缺，不过我下面总结的这些索引生效的情景，可能笼罩大多数索引生效的场景了。

查看索引的应用状况

在 MySQL 索引的应用过程中，有一个 Handler_read_key 值，这个值示意了 某一行被索引值读的次数。Handler_read_key 的值比拟低的话，则表明减少索引失去的性能改善不是很现实，可能索引应用的频率不高。

还有一个值是 Handler_read_rnd_next，这个值高则意味着查问运行效率不高，应该建设索引来进行抢救。这个值的含意是在数据文件中读下一行的申请数。如果正在进行大量的表扫描，Handler_read_rnd_next 的值比拟高，就阐明表索引不正确或写入的查问没有利用索引。

MySQL 剖析表、检查表和优化表

对于大多数开发者来说，他们更偏向于解决简略 SQL 的优化，而简单 SQL 的优化交给了公司的 DBA 来做。

上面就从一般程序员的角度和你聊几个简略的优化形式。

MySQL 剖析表

剖析表用于剖析和存储表的关键字散布，剖析的后果能够使得零碎失去精确的统计信息，使得 SQL 生成正确的执行打算。如果用于感觉理论执行打算与预期不符，能够执行剖析表来解决问题，剖析表语法如下

analyze table cxuan005;

剖析后果波及到的字段属性如下

Table：示意表的名称；

Op：示意执行的操作，analyze 示意进行剖析操作，check 示意进行查看查找，optimize 示意进行优化操作；

Msg_type：示意信息类型，其显示的值通常是状态、正告、谬误和信息这四者之一；

Msg_text：显示信息。

对表的定期剖析能够改善性能，应该成为日常工作的一部分。因为通过更新表的索引信息对表进行剖析，可改善数据库性能。

MySQL 检查表

数据库常常可能遇到谬误，比方数据写入磁盘时产生谬误，或是索引没有同步更新，或是数据库未敞开 MySQL 就进行了。遇到这些状况，数据就可能产生谬误：Incorrect key file for table: ‘ ‘. Try to repair it. 此时，咱们能够应用 Check Table 语句来检查表及其对应的索引。

check table cxuan005;

检查表的次要目标就是查看一个或者多个表是否有谬误。Check Table 对 MyISAM 和 InnoDB 表有作用。Check Table 也能够查看视图的谬误。

MySQL 优化表

MySQL 优化表实用于删除了大量的表数据，或者对蕴含 VARCHAR、BLOB 或则 TEXT 命令进行大量批改的状况。MySQL 优化表能够将大量的空间碎片进行合并，打消因为删除或者更新造成的空间节约状况。它的命令如下

optimize table cxuan005;

我的存储引擎是 InnoDB 引擎，然而从图能够晓得，InnoDB 不反对应用 optimize 优化，倡议应用 recreate + analyze 进行优化。optimize 命令只对 MyISAM、BDB 表起作用。

我本人肝了六本 PDF，全网流传超过 10w+，微信搜寻「程序员 cxuan」关注公众号后，在后盾回复 cxuan，支付全副 PDF，这些 PDF 如下

六本 PDF 链接

正文完
mysql
发表至： mysql
2021-06-09
 0
mysql-explain-keylen计算公式
关于mysql:产品-GreatSQL打造更好的MGR生态
关于mysql:datagrip使用mysqldump导出sql出现1109错误
关于mysql:MySql-insert-on-duplicate-key-update-死锁