35 张图带你 MySQL 调优

这是 MySQL 基础系列的第四篇文章,以前的三篇文章见以下连接html

138 张图带你 MySQL 入门mysql

47 张图带你 MySQL 进阶!!!程序员

炸裂!MySQL 82 张图带你飞面试

通常传统互联网公司不多接触到 SQL 优化问题,其缘由是数据量小,大部分厂商的数据库性能可以知足平常的业务需求,因此不须要进行 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 后,出现这个错误。session

应该是我没有输入密码的缘由,使用 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 连接

相关文章
相关标签/搜索