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