mysql调优

索引相关调优知识、explain性能分析、慢查询优化、日志分析工具mysqldumpslow、show profile

一、常见sql语句

1、基本的增删改查

• 查询语句：select * from table
• 删除语句：delect from table where (加条件)
• 修改语句：update table set 字段名=‘值’ where (加条件)
• 插入语句：insert into table(字段) values(值)

2、Join 查询

A表、B表为列（AB表可做连表查询）：

• 左连接

select * from A left join B on a.key=b.key
1

• 右连接

select * from A right join B on a.key=b.key
1

• 内连接

select * from A inner join B on a.key=b.key
1

• union(查询A和B表所有的数据)

select * from A left join B on a.key=b.key
union
select * from A right join B on a.key=b.key where A.key is null 
1
2
3

二、索引优化分析

• MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
• 索引的本质： 索引是数据结构。可以简单理解为排好序的快速查找数据结构。
• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

1、优缺点

优势：

• 提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为 更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的。

2、是否适合创建索引的情况

适合创建索引的情况：

• 主键自动建立唯一索引；
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题， 组合索引性价比更高
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

不适合创建索引的情况：

• 表记录太少
• 经常增删改的表或者字段
• Where 条件里用不到的字段不创建索引
• 过滤性不好的不适合建索引

3、索引分类

3.1 单值索引

• 概念：即一个索引只包含单个列，一个表可以有多个单列索引
• 语法：

CREATE INDEX 索引名字 ON 表名(字段名);
1

3.2 唯一索引

• 概念：索引列的值必须唯一，但允许有空值
• 语法：

CREATE UNIQUE INDEX 索引名字 ON 表名(字段名);
1

3.3 主键索引

• 概念：设定为主键后数据库会自动建立索引，innodb为聚簇索引
• 语法：

ALTER TABLE 表名 add PRIMARY KEY (主键字段名);
1

3.4 复合索引

• 概念：即一个索引包含多个列

CREATE INDEX 索引名字 ON 表名(字段名1,字段名2);
1

3.5 基本语法

• 创建索引

CREATE [UNIQUE ] INDEX [indexName] ON table_name(column);
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
ALTER TABLE tbl_name ADD INDEX index_name (column_list):
1
2
3

• 删除索引

DROP INDEX [indexName] ON table_name;
1

• 查看索引

SHOW INDEX FROM table_name;
SHOW INDEX FROM table_name\G
1
2

三、Explain 性能分析

1、概念

• 使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道MySQL 是如何处理你的 SQL 语句的。分 析你的查询语句或是表结构的性能瓶颈。
• 用法： Explain+SQL 语句。
• Explain 执行后返回的信息：
  

1.1 id

• select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序。
• id 相同，执行顺序由上至下
• id 不同，id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
• 关注点：id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

1.2 select_type

• select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

1.3 table

• 这个数据是基于哪张表的。

1.4 type

• type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL
• 一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

1.5 possible_keys

• 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用。

1.6 key

• 实际使用的索引。如果为NULL，则没有使用索引。

1.7 key_len

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
• key_len 字段能够帮你检查是否充分的 利用上了索引。
• ken_len 越长，说明索引使用的越充分。

1.8 ref

• 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

1.9 rows

• rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

1.10 Extra

• 其他的额外重要的信息。
• Using filesort：文件排序，尽量不要出现
• Using temporary：使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。尽量不要出现
• Using index：利用索引进行了排序或分组。效率高
• Using where：表明使用了 where 过滤。
• Using join buffer：使用了连接缓存。

四、慢查询日志分析

（1）MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具 体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

（2）具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句。

（3）由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能 收集超过5秒的sql，结合之前explain进行全面分析

1、开启慢查询日志

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。
如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

• 查看慢查询日志是否开启

SHOW VARIABLES LIKE '%slow_query_log%';
# 默认情况下 slow_query_log 的值为 OFF， 表示慢查询日志是禁用的
1
2

• 开启慢查询日志

set global slow_query_log=1;
1

• 查看慢查询设定阈值

SHOW VARIABLES LIKE 'long_query_time%';
# 单位秒
1
2

• 设定慢查询阈值

set long_query_time=3
# 单位秒，这里是3秒（即超过3秒的sql记录到日志）
1
2

2、日志分析工具mysqldumpslow

• -s ：是表示按照何种方式排序
• c ：访问次数
• l ：锁定时间
• r ：返回记录 t 查询时间
• al ：平均锁定时间
• ar ：平均返回记录数
• at ：平均查询时间
• -t ：即为返回前面多少条的数据
• -g ：后边搭配一个正则匹配模式，大小写不敏感的

# 得到返回记录集最多的 10 个 SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log 

#得到访问次数最多的 10 个 SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log 

#得到按照时间排序的前 10 条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log 

#另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log | more
1
2
3
4
5
6
7
8
9
10
11

五、Show Profile

1、开启命令

# 查看开启状态 （默认是关闭 OFF）
show variables like '%profiling%'

#开启
set profiling=on;
1
2
3
4
5

2、使用

• 查看持续时间(耗时)、sql语句、和对应的Query_ID

show profiles;
1

• 使用 Show Profile对sql语句进行诊断

# Query_ID为show profiles列表中的Query_ID
show profile cpu,block io for query Query_ID;

# 查询cpu开销信息和block io开销信息
show profile cpu,block io for query 15;
1
2
3
4
5

3、常用查询参数

①ALL：显示所有的开销信息。

②BLOCK IO：显示块IO开销。

③CONTEXT SWITCHES：上下文切换开销。

④CPU：显示CPU开销信息。

⑤IPC：显示发送和接收开销信息。

⑥MEMORY：显示内存开销信息。

⑦PAGE FAULTS：显示页面错误开销信息。

⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

⑨SWAPS：显示交换次数开销信息。

4、注意

①Converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。

②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。

③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！

④locked。

如果在Show Profile诊断结果中出现了以上4条结果中的任何一条，则SQL语句需要优化。

六、索引失效

• 最佳左前缀法则：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无 法被使用。
• 不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。
• 字符串不加单引号，则会在 name 列上做一次转换，导致索引失效
• 将可能做范围查询的字段的索引顺序放在最后
• 尽量使用覆盖索引（即查询列和索引列一直，不要写 select *）
• mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。
• is not null 用不到索引，is null 可以用到索引。
• like 的前后模糊匹配（前缀不能出现模糊匹配！即%a）
• 减少使用 or，使用 union all 或者 union 来替代

案例

假设 index(a,b,c)；

where a = 3  #使用到 a

where a = 3 and b = 5 and c = 4 #使用到 a,b,c

where a = 3 and c = 5 #使用到 a，但是c不可以，b中间断了

where a = 3 and b > 4 and c = 5 #使用到a 和b，c不能用在范围之后，b断了

where abs(a) =3 # 不能使用索引

where a = 3 and b like 'kk%' and c = 4 #使用到 a,b,c

where a = 3 and b like '%kk%' and c = 4 #只用到 a

1
2
3
4
5
6
7
8
9
10
11
12
13
14

口诀

全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写*；
不等空值还有 OR，索引影响要注意；
VAR 引号不可丢，SQL 优化有诀窍。

七、总结

• 慢查询的开启并捕获
• explain+慢SQL分析
• show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
• SQL数据库服务器的参数调优。