MySQL性能优化

最新推荐文章于 2023-08-22 11:38:02 发布

啦哆咪

最新推荐文章于 2023-08-22 11:38:02 发布

阅读量600

点赞数

分类专栏： sql 文章标签： mysql 性能优化数据库

本文链接：https://blog.csdn.net/qq_43713303/article/details/129768882

版权

sql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

MySQL性能优化

性能分析

在进行性能优化前，我们首先需要分析哪里有性能问题，这样才能针对性的优化。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，可以用如下语句查看是否开启

show variables like 'slow_query_log';

若需要开启，可以在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySOL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，执行systemctl restart mysqld重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/xxx-slow.log(xxx为主机名)。

在这里插入图片描述

profile

profile会记录MySQL中每条sql执行的时间，可以使用如下命令来查看数据库是否支持profile

select @@have_profiling;

查看当前是否开启profile，默认是没有开启的，如果使用第三方的工具连接，工具可能会帮你开启

select @@profiling;

开启profile

set [global|session] profiling=1;

接下来就可以使用profile查看每一条sql的情况

-- 看每一条SQL的耗时噬本情况
show profiles;

-- 查看指定query id的SQL语句各个阶段的耗时情况
show profile for query query_id;

-- 查看指定query id的SQL语句CPU的使用情况
show profile cpu for query query_id;

在这里插入图片描述

explain

在select语句前加上explain，可以获取执行SELECT语句的信息，包括使用索引的情况，在SELECT语句执行过程中表如何连接和连接的顺序。是我们进行优化时最常用的
在这里插入图片描述
其列的含义如下

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。
select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等。
type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。通过主键和唯一索引进行访问，一般为const；通过一般索引进行访问时，一般为ref；index表示用了索引，但遍历了整个索引树，all则表示全表扫描，效率最低
possible_keys：表示可能使用到的索引，一个或多个
key：实际用到的索引，若没用到则为null
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。
extra：额外信息

索引失效的情况

为了防止索引失效，我们在使用索引时需要满足一定使用原则，才可使索引真正生效，提高查找效率。下面我们介绍一些会导致索引失效的情况，在使用索引查询时，要尽量避免如下下面几种情况

不满足最左前缀法则

对于联合索引，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，一旦跳过，后面字段的索引将会失效。

tips：如果where后条件的顺序跟联合索引的顺序不一致，但没有跳过列，此时索引也可以生效

查询条件中使用or

当使用了or时，除非所有的查询条件都建有索引，否则索引失效。因为必须去扫描未建索引的字段

like查询以%开头

当like以%开头时，我们将无法使用索引，因为索引整体虽然是有序的，但是如果只指定后半部分的索引值，则没有办法保证有序。因此只使用%结尾时，是可以走索引的，但开头不能有%。

存在隐性类型转换

如果在查询字符串时，条件值未用引号，则会因为需要类型转换，导致索引失效

索引列上参与计算会导致索引失效

例如

select * from app_user where id+1 = 1

当对索引列进行了计算，则无法保证索引

当mysql认为全表扫描更快时

当需要查询的范围比较大时（通常为超过一半，不确定），且数据比较多时，则会使用全表扫描

当使用了not in 或not exist时

当使用了not in 或not exist时，MySQL无法确定查询数据的范围，会直接使用全表扫描

sql提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。主要包括如下几个：

use index：建议MySQL使用指定的索引，MySQL有可能不接受建议
ignore index：强制MySQL忽略指定的索引
force index：强制MySQL使用指定索引（前提是能用上）

select * from table use|ignore|force index(索引名);

覆盖索引

当查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到，我们称之为覆盖索引。当使用了覆盖索引时，我们就不需要回表查询，会有更高的效率。

在这里插入图片描述
比如对于上面的表，如果执行下面的sql

select id,name,gender from student where name = 'Jack';

那么由于gender字段在name的索引中不存在，需要回表查询整行的数据，才能取到gender

这个时候，我们可以为name和gender建立一个联合索引，这样在查询时，将会走联合索引，且在联合索引的叶子节点中就保存有gender，所以不需要回表查询

索引的设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪索引最有效地用于查询。

insert优化

当我们需要一次性插入大量数据时，可以采用如下手段进行优化

批量插入

Insert into test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

开启事务

start transaction;
Insert into test values(1,'Tom');
Insert into test values(2,'Cat');
Insert into test values(3,'Jerry');
commit;

主键顺序插入：当乱序插入时，为了维护主键索引，会有页分裂操作，影响效率，因此，最好按照主键顺序插入

order by 优化

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

limit 优化

通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引+子查询形式进行优化，但提升有限。
```
select * from app_user where id >=(select id from app_user limit 9000000,1) limit 10;
```
当id连续时，可以直接加where id>9000000来处理
限制不允许一下子跳到最后一页，比如只允许一页页翻，则可以将上一页的最后id传递进来，最后where id>8999999 limit 0,10
通过倒序排序，反向查找

count优化

count主要有如下几种用法：

count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为u)。
count(字段)：没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为nul，计数累加。有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(1)：InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

因此，效率上count(1)=count(*)>count(主键)>count(字段)。尽量使用count(1)