mysql 优化思路_Mysql 优化思路

一. 整体思路首先需要使用 '慢查询日志功' 能，去获取所有查询时间比较长的SQL语句 , 他会记录所有慢SQL的执行信息. 再利用自带的mysqldumpslow对 慢SQL文件进行分析排名, 找出查询时间慢的SQL.

查看执行计划，查看有问题的SQL的执行计划

针对查询慢的SQL语句进行优化

使用 show profile/show profiles 查看有问题的SQL的性能使用情况

调整操作系统参数优化

升级服务器硬件

二. 查询慢查询SQL 功能

mysql数据库拥有一个"慢查询日志" 的功能, 用于记录超过某个设定的时间阈值的查询sql, 我们可以利用他的记录来找出性能较差的SQL语句

1. 设置

此功能需要手动开启show variables like '%slow_qurey%'

set global slow_query_log = ON; 开启慢查询SQL功能,最好在配置环境直接配置保证重启时也生效

set global long_query_time = 1; 慢查询阈值【slow_query_log】  ：是否开启慢查询日志，1为开启，0为关闭。

【log-slow-queries】 ：旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参

数，系统则会默认给一个缺省的文件host_name-slow.log

【slow-query-log-file】：新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置

该参数，系统则会默认给一个缺省的文件host_name-slow.log

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

2. 查看

格式说明：

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

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

第三行,记录了一些我们比较有用的信息，如下解析Query_time,这条SQL执行的时间,越长则越慢

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

Rows_sent,查询返回的行数

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

第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。第五行,及后面所有行(第二个# Time:之前),执行的sql语句记录信息，因为sql可能会很长

3. 分析慢查询日志的工具

使用mysqldumpslow工具，mysqldumpslow是MySQL自带的慢查询日志工具。[root@mysql132 mysql]# mysqldumpslow -s t /var/lib/mysql/mysql132-slow.log

Reading mysql slow query log from /var/lib/mysql/mysql132-slow.log

Count: 1  Time=143.16s (143s)  Lock=0.00s (0s)  Rows=27907961.0 (27907961),

root[root]@localhost

select * from t_slow a left join t_slow b on a.name=b.name

Count: 5  Time=5.80s (28s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

insert into t_slow(name,address) select name,address from t_slow

Count: 1  Time=3.01s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost

select sleep(N)

mysqldumpslow 常用参数说明：-s：是表示按照何种方式排序

al 平均锁定时间

ar 平均返回记录时间

at 平均查询时间(默认)

c 计数

l 锁定时间

r 返回记录

t 查询时间

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

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

三. 查看执行计划

MySQL 提供了一个 EXPLAIN 命令, SELECT 语句前加上 explain , 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.mysql>explain select * from tuser where id = 2 \G

*************************** 1. row ***************************

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

select_type: SIMPLE                   #  SELECT 查询的类型

table: tuser                    #  查询的是哪个表

partitions: NULL                     #  匹配的分区

type: const                    #  join 类型

possible_keys: PRIMARY                  #  此次查询中可能选用的索引

key: PRIMARY                  #  此次查询中确切使用到的索引.

key_len: 4

ref: const     #  此次查询中确切使用到的索引.

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

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

Extra: NULL                    #   额外的信息

select_type(重要)

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。simple: 不需要union 或不包含子查询的 select查询.

primary: 需要UNION 或者包含子查询时, 位于在外层的查询基准表

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

select_type都是union

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

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

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

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

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

type(重要)

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：

system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL除了all之外，其他的type都可以使用到索引

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

最少要使用到range级别system:  表中只有一行数据或者是空表

const(重要): 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。

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

ref(重要): 非唯一性索引扫描，使用到了索引, 且匹配到多个结果

fulltext: 全文索引检索

ref_or_null: 和ref类似, 只是匹配到了NULL值

unique_subquery: 用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery:  用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询

去重。

range(重要): 索引范围扫描，常见于使用>,

index_merge: 使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引

index(重要): select结果列中使用到了索引，type会显示为index

all(重要)这个就是全表扫描数据文件

possible_keys

此次查询中可能选用的索引，一个或多个

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的

select_type这里只会出现一个。

key_len  查询的索引长度

key_len越小 索引效果越好。如果是用到一个索引, 则将单个是单个索引的长度, 如果是多列索引, 则计算具体用到了多少列索引.

留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了

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

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

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

rows

这里是执行计划中估算的扫描行数，不是精确值(InnoDB不是精确的值，MyISAM是精确的值，主要原

因是InnoDB里面使用了MVCC并发机制)

extra(重要)

这个列包含不适合在其他列中显示十分重要的额外的信息，这个列可以显示的信息非常多，有几十种Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利

用索引完成的排序操作称为“文件排序”。需要优化sql。

Using temporary

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。

using index(重要)

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

using where(重要)

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

Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join

buffer调大一些。

impossible where

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

四.SQL语句优化

1、索引优化为搜索字段(where中的条件)、排序字段、select查询列，创建合适的索引，不过要考虑数据的

业务场景：查询多还是增删多？

尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询

条件放到最左边。

尽量使用覆盖索引，SELECT语句中尽量不要使用*。

order by、group by语句要尽量使用到索引

索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多

的索引键值。太长的列，可以选择建立前缀索引

索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。

order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排

序，那么排序的规则必须相同(同是升序或者降序)，否则索引同样会失效。

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

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

会非常差。LIMIT OFFSET , SIZE；

LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。解决方案：单表分页时，使用自增主键排序之后，先使用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 时, 小的表应该为基准表, 外联大表

避免全表扫描, mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。

尽量使用count(主键)

JOIN条件的两表的字段最好加上索引

WHERE条件中尽量不要使用not in语句(建议使用not exists)

五. profile分析语句

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能

瓶颈在什么地方。

我们可以从这些信息查出每一句SQL的详细对硬件方面做一些判断

通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位

出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该

工具只有在MySQL 5.0.37以及以上版本中才有实现。

默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。set profiling=1; --1是开启、0是关闭

1. 语句使用show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.show profile : 列出本次会话发送的SQL语句 执行的资源占用情况show profile for query 2; # 查看Query_ID 为2的SQL资源占用情况

可指定资源类型查询show profile cpu,swaps for query 66;