本文参考:https://mp.weixin.qq.com/s/BQLfq3fUiTEHxzA5Y41l_A
一. Mysql优化思路:
1.周期性的故障
1).访问高峰或缓存崩溃:增加缓存,并修改缓存失效策略,失效时间分散
2.通过show processlist 或者 开启慢查询日志,获取有问题的sql
profiling 和 explain 分析sql语句
1).sql语句等待时间长:对服务器参数优化,增加缓冲区和线程数
2).sql语句执行时间长: 优化sql,建立并优化索引,优化表结构
3.增加硬件设备,添加集群数量
二. 获取需要优化的 SQL 语句
1.通过show processlist命令,查看State 的值,出现如下值时需要优化。
Converting HEAP to MyISAM # 查询结果太大时,把结果放到磁盘,严重 Create tmp table #创建临时表,严重 Copying to tmp table on disk #把内存临时表复制到磁盘,严重 locked #被其他查询锁住,严重 loggin slow query #记录慢查询 Sorting result #排序
2.开启慢查询日志
在配置文件 my.cnf 中的 [mysqld] 一行下边添加
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1
其中,slowquerylog = 1 表示开启慢查询;slowquerylogfile 表示慢查询日志存放的位置; longquerytime = 2 表示查询 >=2 秒才记录日志;logqueriesnotusing_indexes = 1 记录没有使用索引的 SQL 语句。
注意:slowquerylog_file 的路径不能随便写,否则 MySQL 服务器可能没有权限将日志文件写到指定的目录中。建议直接复制上文的路径。
虽然在慢查询日志中记录查询慢的 SQL 信息,但是日志记录的内容密集且不易查阅。因此,我们需要通过工具将 SQL 筛选出来。
MySQL 提供 mysqldumpslow 工具对日志进行分析。我们可以使用 mysqldumpslow --help 查看命令相关用法。
-s:排序方式,后边接着如下参数
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录书
at:平均查询时间
-t:返回前面多少条的数据
-g:翻遍搭配一个正则表达式,大小写不敏感
案例:获取返回记录集最多的10个sqlmysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log
获取访问次数最多的10个sqlmysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log
获取按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log
三. 分析SQL语句
1.explain方式分析:
1) id:select 查询序列号。id相同,执行顺序由上至下;id不同,id值越大优先级越高,越先被执行
2) select_type:查询数据的操作类型,其值如下:
simple:简单查询,不包含子查询 unionprimary:包含复杂的子查询,最外层查询标记为该值 subquery:在 select 或 where 包含子查询,被标记为该值 derived:在 from 列表中包含的子查询被标记为该值,MySQL 会递归执行这些子查询,把结果放在临时表 union:若第二个 select 出现在 union 之后,则被标记为该值。若 union 包含在 from 的子查询中,外层 select 被标记为 derived union result:从 union 表获取结果的 select
3) table:显示该行数据是关于哪张表
4) partitions:匹配的分区
5) type:表的连接类型,其值,性能由高到底排列如下:
system:表只有一行记录,相当于系统表 const:通过索引一次就找到,只匹配一行数据 eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常用于主键或唯一索引扫描 ref:非唯一性索引扫描,返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列 range:只检索给定范围的行,使用一个索引来选择行。一般使用between、>、<情况 index:只遍历索引树 ALL:全表扫描,性能最差
注:前5种情况都是理想情况的索引使用情况。通常优化至少到range级别,最好能优化到 ref
6) possible_keys:指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL,说明没有使用索引,可以建立索引提高性能
7) key:显示 MySQL 实际使用的索引。如果为 NULL,则没有使用索引查询
8) key_len:表示索引中使用的字节数,通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好 显示的是索引字段的最大长度,并非实际使用长度
9) ref:显示该表的索引字段关联了哪张表的哪个字段
10) rows:根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好
11) filtered:返回结果的行数占读取行数的百分比,值越大越好
12) extra: 包含不合适在其他列中显示但十分重要的额外信息,常见的值如下:
using filesort:说明 MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。出现该值,应该优化 SQL using temporary:使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值,应该优化 SQL using index:表示相应的 select 操作使用了覆盖索引,避免了访问表的数据行,效率不错 using where:where 子句用于限制哪一行 using join buffer:使用连接缓存distinct:发现第一个匹配后,停止为当前的行组合搜索更多的行
四. 优化手段
1 查询优化
1) 避免 SELECT *,需要什么数据,就查询对应的字段。
2) 小表驱动大表,即小的数据集驱动大的数据集。如:以 A,B 两表为例,两表通过 id 字段进行关联。
3) 一些情况下,可以使用连接代替子查询,因为使用 join,MySQL 不会在内存中创建临时表。
4) 适当添加冗余字段,减少表关联。
5) 合理使用索引(下文介绍)。如:为排序、分组字段建立索引,避免 filesort 的出现。
2 索引使用
2.1 适合使用索引的场景
1) 主键自动创建唯一索引
2) 频繁作为查询条件的字段
3) 查询中与其他表关联的字段
4) 查询中排序的字段
5) 查询中统计或分组字段
2.2 不适合使用索引的场景
1) 频繁更新的字段
2) where 条件中用不到的字段
3) 表记录太少
4) 经常增删改的表
5) 字段的值的差异性不大或重复性高
2.3 索引创建和使用原则
1) 单表查询:哪个列作查询条件,就在该列创建索引
2) 多表查询:left join 时,索引添加到右表关联字段;right join 时,索引添加到左表关联字段
3) 不要对索引列进行任何操作(计算、函数、类型转换)
4) 索引列中不要使用 !=,<> 非等于
5) 索引列不要为空,且不要使用 is null 或 is not null 判断
6) 索引字段是字符串类型,查询条件的值要加''单引号,避免底层类型自动转换
违背上述原则可能会导致索引失效,具体情况需要使用 explain 命令进行查看
2.4 索引失效情况
除了违背索引创建和使用原则外,如下情况也会导致索引失效:
1) 模糊查询时,以 % 开头
2) 使用 or 时,如:字段1(非索引)or 字段2(索引)会导致索引失效。
3) 使用复合索引时,不使用第一个索引列。
index(a,b,c) ,以字段 a,b,c 作为复合索引为例: