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 命令

MySQL中5中类型求百分比_MySQL中5中类型求百分比

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
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