【数据库优化】MySQL性能优化基础

mysql> show variables like '%slow_query_log%';+---------------------+------------------------------------------------------+| Variable_name       | Value                                                |+---------------------+------------------------------------------------------+| slow_query_log      | OFF                                                  || slow_query_log_file | D:\mysql-5.7.27-winx64\data\DESKTOP-E9F062A-slow.log |+---------------------+------------------------------------------------------+

slow_query_log 慢查询开启状态 OFF 未开启 ON 为开启slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）

mysql> set global slow_query_log = 1; //设置开启或者关闭，0为关闭，1为开启mysql> set global long_query_time = 3;  //设置慢的阙值时间，默认10秒

[mysqld]slow_query_log = 1 #开启slow_query_log_file = /mysql-5.7.27-winx64/data/mysql-slow.log #默认host_name_show.loglong_query_time = 3 #默认10秒（查询超过多少秒才记录）log-queries-not-using-indexes = on #如果值设置为ON，则会记录所有没有利用索引的查询，一般在性能调优的时候会暂时开启。log_output = 'FILE,TABLE' #输出的格式(FILE:文本, TABLE:表中, FILE,TABLE:同时输出到文本和表中)

mysql> select sleep(5);

mysql> show global status like '%Slow_queries%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slow_queries  | 1     |+---------------+-------+

mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log  //得到返回记录集最多的10个SQLmysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log //得到访问次数最多的10个SQL mysqldumpslow -s t -t 10 -g "left join" /data/mysql/mysql-slow.log  //得到按照时间排序的前10条里面含有做了连接的查询SQLmysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log | more  //另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况

mysqlsla -lt slow -sort t_sum -top 1000  /tmp/slow_query.log

结果选项说明：总查询次数 (queries total),去重后的sql数量 (unique),输出报表的内容排序(sorted by),最重大的慢sql统计信息(包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数)Count, sql的执行次数及占总的slow log数量的百分比.Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.Lock Time, 等待锁的时间.95% of Lock , 95%的慢sql等待锁时间.Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.Rows examined, 扫描的行数量.Database, 属于哪个数据库Users, 哪个用户,IP, 占到所有用户执行的sql百分比Query abstract, 抽象后的sql语句Query sample, sql语句mysqlsla常用参数说明：-log-type (-lt) type logs:通过这个参数来制定log的类型，主要有slow, general, binary, msl, udl,分析slow log时通过制定为slow-sort:t_sum:按总时间排序(默认)，c_sum:按总次数排序c_sum_p: sql语句执行次数占总执行次数的百分比。-top:显示sql的数量，默认是10,表示按规则取排序的前多少条–statement-filter (-sf) [+-][TYPE]:过滤sql语句的类型，比如select、update、drop，[TYPE] 有SELECT, CREATE, DROP, UPDATE, INSERT，例如”+SELECT,INSERT”，不出现的默认是-，即不包括。-db：要处理哪个库的日志：

# 举个例子，只取funsion数据库的select语句，并按照总时间排序，取前1000条数据# 保存到当前目录下的 slow_query.pretty.log文件中mysqlsla -lt slow  -sort t_sum  -sf "+select"  -db funsion  -top 1000  /tmp/slow_query.log > ./slow_query.pretty.log

# 分析最近12小时内的查询：pt-query-digest  --since=12h  slow.log > slow_report2.log

pt-query-digest [OPTIONS] [FILES] [DSN]    --create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。    --create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。    --filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析    --limit    限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。    --host  mysql服务器地址    --user  mysql用户名    --password  mysql用户密码    --history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。    --review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。    --output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。    --since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。    --until 截止时间，配合—since可以分析一段时间内的慢查询。

mysql> explain select * from user_info where id = 2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: user_info   partitions: NULL         type: constpossible_keys: PRIMARY          key: PRIMARY      key_len: 8          ref: const         rows: 1     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.06 sec)

各列的含义如下:
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.select_type: SELECT 查询的类型.SIMPLE, 表示此查询不包含 UNION 查询或子查询PRIMARY, 表示此查询是最外层的查询UNION, 表示此查询是 UNION 的第二或随后的查询DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询UNION RESULT, UNION 的结果SUBQUERY, 子查询中的第一个 SELECTDEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.table: 查询的是哪个表partitions: 匹配的分区type: join 类型type字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过type字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.system: 表中只有一条数据. 这个类型是特殊的 const 类型.const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难.possible_keys: 此次查询中可能选用的索引表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.key: 此字段是 MySQL 在当前查询时所真正使用到的索引.key_len: 表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.ref: 哪个字段或常数与 key 一起被使用rows: 显示此查询一共扫描了多少行. 这个是一个估计值.rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数, 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.filtered: 表示此查询条件所过滤的数据的百分比extra: EXplain 中的很多额外的信息会在 Extra 字段显示Using filesort: 当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.Using index: “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错Using temporary: 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.type 类型的性能比较通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < systemALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

看看当前的MySQL版本是否支持: show variables like 'profiling';或show variables like 'profiling%';

mysql> show variables like 'profiling%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| profiling              | OFF   || profiling_history_size | 15    |+------------------------+-------+

mysql> set profiling = 1;