mysql性能优化

最新推荐文章于 2024-07-03 03:22:24 发布

程序猿hww

最新推荐文章于 2024-07-03 03:22:24 发布

阅读量123

点赞数

文章标签： mysql 性能优化数据库

本文链接：https://blog.csdn.net/hwwjason/article/details/134281519

版权

前言

写这篇文章，主要是为了能写出更好的SQL、以及优化慢查询提供一定思路

MYSQL查询过程

SQL优化

查询SQL尽量不要使用select *，而是具体字段

1、反例

SELECT * FROM t_team A WHERE 1 = 1 AND A.delete_flag = FALSE ORDER BY A.modify_time DESC

2、正例

select t1.attachment_id, t1.speech_id from t_speech_attachment t1;

3、理由

节省资源、减少网络开销。
可能用到覆盖索引，减少回表，提高查询效率。

尽量使用数值替代字符串类型

1、正例

主键（id）：primary key优先使用数值类型int，tinyint

性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint

2、理由

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

而对于数字型而言只需要比较一次就够了；

字符会降低查询和连接的性能，并会增加存储开销；

inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

inner join 内连接，只保留两张表中完全匹配的结果集；
left join会返回左表所有的行，即使在右表中没有匹配的记录；
right join会返回右表所有的行，即使在左表中没有匹配的记录；

原因：

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

使用UNION ALL而不是UNION

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

UNION ALL操作只是简单地将两个结果合并后就返回。

表连接不宜太多，索引不宜太多，一般5个以内

1、表连接不宜太多，一般5个以内

关联的表个数越多，编译的时间和开销也就越大

每次关联内存中都生成一个临时表

应该把连接表拆开成较小的几个执行，可读性更高

如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

阿里规范中，建议多表联查三张表以下

2、索引不宜太多，一般5个以内

索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；

索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；

索引表的数据是排序的，排序也是要花时间的；

insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；

一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

避免在索引列上使用内置函数

操作delete或者update语句，加个limit或者循环分批次删除

怎么出找出慢查询SQL

mysql配置

在MySQL中查看慢 SQL

查看是否开启慢查询日志

show variables like "%slow%";

默认都是关闭的，可以手动开启，或者镜像打包的时候修改配置文件默认开启

开启慢查询日志

在配置文件中开启慢查询日志功能

配置文件路径： /ect/mysql/my.cnf，内容如下（#的被我删除掉了）

root@499b72caafb2:/# cat /etc/mysql/my.cnf [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Custom config should go here !includedir /etc/mysql/conf.d/

添加如下配置

slow_query_log=ON slow_launch_time=1 slow_query_log_file=可以自定义位置

执行命令临时开启

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL slow_launch_time = 1;

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

使用explain分析SQL执行计划

使用示例

EXPLAIN select * from organ_platform.t_environment_monitor t where t.space_id like '157971372135625932%'

字段说明

列名	描述
id	在一个大的查询语句中，每个 SELECT 关键字都对应一个唯一的 id。在连接查询中，记录的 id 值都是相同的；在多个 SELECT 关键字的查询中，查询优化器可能会对子查询进行优化，使得多条 SELECT 记录的 id 值相同
select_type	查询类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际使用的索引
key_len	实际使用的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	针对预估的需要读取的记录，经过搜索条件过滤后剩余记录条数的百分比。在单表查询中没什么意义，在连表查询中可以计算出在驱动表执行完查询后，还需要对被驱动表执行多少次查询
Extra	额外的备注信息

分析数据库常用SQL

查询所有表条数并排序

-- 查询所有表条数并排序 SELECT table_schema AS '数据库名称', table_name AS '表名称', table_rows AS '数据量' FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema') ORDER BY table_rows DESC;

示例：查询结果如下

如果某些表条数，特别大，就要考虑相关的性能优化

查询索引创建情况

-- 查看表的索引信息 show index from organ_platform.t_environment_monitor; -- 查看表的结构信息，包括索引信息 DESCRIBE organ_platform.t_environment_monitor; -- INFORMATION_SCHEMA 也可以查看表的索引信息 SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'organ_platform' AND TABLE_NAME = 't_environment_monitor';