5-Mysql性能分析

Mysql性能优化

1. 性能优化思路

1. 慢查询日志

2. 查看问题sql的执行计划

3. 优化慢sql

4. 查看慢sql执行时的性能使用情况

5. 调整系统参数

6. 提升服务器硬件

2. 慢查询日志

1. 参数，开启sql

• 【slow_query_log】 ：是否开启慢查询日志，1为开启，0为关闭。

• 【slow-query-log-file】：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

• 【long_query_time】 ：慢查询阈值，当查询时间多于设定的阈值时，记录日志，【单位为秒】。

SQL:

set global slow_query_log = ON;

set global long_query_time = 1;

配置文件：

[mysqld]

slow_query_log=ON

long_query_time=1

2. 日志格式

# Time: 210317 10:12:45
# User@Host: mysql[mysql] @  [192.168.233.1]  Id:     7
# Query_time: 2.072475  Lock_time: 0.000040 Rows_sent: 1  Rows_examined: 1197821
SET timestamp = 1615947165;
SELECT * FROM `t` WHERE amount = 48342.84;

• 第一行,SQL查询执行的具体时间

• 第二行,执行SQL查询的连接信息，用户和连接IP

• 第三行,记录了一些我们比较有用的信息，如下解析

  Query_time,这条SQL执行的时间,越长则越慢

  Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间

  Rows_sent,查询返回的行数

  Rows_examined,查询检查的行数，越长就当然越费时间

• 第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。

• 第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

3. 慢查询分析工具

1) 命令

​ mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/slow.log

2）参数

1. -s：是表示按照何种方式排序

   al 平均锁定时间

   ar 平均返回记录时间

   at 平均查询时间（默认）

   c 计数

   l 锁定时间

   r 返回记录

   t 查询时间

2. -t：是top n的意思，即为返回前面多少条的数据

3. -g：后边可以写一个正则匹配模式，大小写不敏感的

3. 执行计划

explain 命令

1. id

SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

2. select_type

SELECT 查询的类型.

1）simple

没有union，没有子查询。

2）primary

一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。

3）union

union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。

4）dependent union

与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。

5）union result

包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null。

6）subquery

除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery。

7）dependent subquery

与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。

8）derived

from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。

3. table

查询的是哪个表

• 如果查询使用了别名，那么这里显示的是别名
• 如果不涉及对数据表的操作，那么这显示为null
• 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
• 如果是尖括号括起来的**<union M,N>**，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

4. partitions

匹配的分区

5. type

显示的是单位查询的连接类型或者理解为访问类型

• 除了all之外，其他的type都可以使用到索引

• 除了index_merge之外，其他的type只可以用到一个索引

• 最少要使用到range级别

根据性能：

1. system

   表中只有一行数据或者是空表

2. const

   使用唯一索引或者主键

3. eq_ref

   唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

4. ref

   非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

5. fulltext

6. ref_or_null

7. unique_subquery

8. index_subquery

9. range

   索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

10. index_merge

11. index

    select结果列中使用到了索引，type会显示为index。

12. ALL

    全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

6. possible_keys

此次查询中可能选用的索引

7. key

此次查询中确切使用到的索引.

8. ref

哪个字段或常数与 key 一起被使用

如果是使用的常数等值查询，这里会显示const

如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段

如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

9. rows

显示此查询一共扫描了多少行. 这个是一个估计值.

10. filtered

表示此查询条件所过滤的数据的百分比

11. extra

额外的信息

1) Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。需要优化sql。

2) Using temporary

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。需要优化SQL。

3) using index

查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。

4) using where

表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引。

5) impossible where

where子句的值 总是false ，不能用来获取任何元组。

4. SQL语句优化

1. 索引优化

• 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的业务场景：查询多还是增删多？
• 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。
• 尽量使用覆盖索引，SELECT语句中尽量不要使用*。
• order by、group by语句要尽量使用到索引。
• 索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引。
• 索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。
• order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

2.LIMIT 优化

• 如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。

• 处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。

  单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows

  select * from (select * from tuser2 where id > 1000000 and id < 1000500 ORDER BY id) t limit 0, 20
  

3. 其他查询优化

• 小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。
• 避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
• 避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）
• JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。
• WHERE条件中尽量不要使用not in语句（建议使用not exists）
• 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

5. profile分析sql

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。

6. 服务器优化

1. 缓冲区优化

• 设置足够大的 innodb_buffer_pool_size ，将数据读取到内存中。

  建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Z5Y5wyk4-1615986581120)(C:\Users\12101\AppData\Roaming\Typora\typora-user-images\image-20210317165400164.png)]

2. 降低磁盘写入次数

• 关闭 通用查询日志、慢查询日志、错误日志。

• 使用足够大的写入缓存 innodb_log_file_size

  推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

3. Mysql配置优化

• 表示缓冲池字节大小。推荐值为物理内存的50%~80%。

  推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

• 用来控制redo log刷新到磁盘的策略。

  innodb_flush_log_at_trx_commit=1

• 每提交1次事务同步写到磁盘中，可以设置为n。

  sync_binlog=1

• 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。

  innodb_max_dirty_pages_pct=30

• 后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000

  innodb_io_capacity=200

• 指定innodb共享表空间文件的大小

  innodb_data_file_path

• 慢查询日志的阈值设置，单位秒

  long_qurey_time=0.3

• mysql复制的形式，row为MySQL8.0的默认形式

  binlog_format=row

• 调高该参数则应降低interactive_timeout、wait_timeout的值

  max_connections=200

• 过大，实例恢复时间长；过小，造成日志切换频繁

  innodb_log_file_size

• 全量日志建议关闭

  general_log=0