mysql调优

华婷深深

已于 2022-09-11 09:22:00 修改

阅读量587

点赞数 1

分类专栏： MySql 文章标签： mysql 数据库 sql

于 2022-09-09 16:57:58 首次发布

本文链接：https://blog.csdn.net/weixin_49385823/article/details/126782910

版权

MySql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

索引相关调优知识、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

右连接

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

内连接

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

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

二、索引优化分析

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

1、优缺点

优势：

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

劣势：

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

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

适合创建索引的情况：

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

不适合创建索引的情况：

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

3、索引分类

3.1 单值索引

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

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

3.2 唯一索引

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

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

3.3 主键索引

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

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

3.4 复合索引

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

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

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

删除索引

DROP INDEX [indexName] ON table_name;

查看索引

SHOW INDEX FROM table_name;
SHOW INDEX FROM table_name\G

三、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， 表示慢查询日志是禁用的

开启慢查询日志

set global slow_query_log=1;

查看慢查询设定阈值

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

设定慢查询阈值

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

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

五、Show Profile

1、开启命令

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

#开启
set profiling=on;

2、使用

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

show profiles;

使用 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;

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