SQL - MySQL 性能优化详解

2022年7月15日
大约 7 分钟

SQL - MySQL 性能优化详解

MySQL性能优化我们可以从以下四个维度考虑:硬件升级、系统配置、表结构设计、SQL语句和索引。

从成本上来说:硬件升级>系统配置>表结构设计>SQL语句及索引,然而效果却是由低到高。所以我们在优化的时候还是尽量从SQL语句和索引开始入手。

图

硬件升级

硬件升级这里不在过多赘述,升级更好配置的机器、机械硬盘更换为SSD等等。

系统配置优化

1. 调整buffer_pool

通过调整buffer_pool使数据尽量从内存中读取,最大限度的降低磁盘操作,这样可以提升性能。查看buffer_pool数据的方法:

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_page_%'

图

可以看出总页数8192,空闲页数1024。

//查看buffer_pool大小 SELECT @@innodb_buffer_pool_size/1024/1024

图

innodb_buffer_pool_size默认为128M,理论上可以扩大到内存的3/4或4/5。我们修改mysql配置文件my.cnf,增加如下配置:

innodb_buffer_pool_size = 750M

然后重启MySQL。

2. 数据预热

默认情况下,某条数据被读取过一次才会被缓存在innodb_buffer_pool里。所以数据库刚刚启动,可以进行一次数据预热,将磁盘上的数据缓存到内存中去。 预热脚本:

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb, ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache FROM ( SELECT engine,table_schema db,table_name tb, index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist FROM ( SELECT B.engine,A.table_schema,A.table_name, A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN ( SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engine='InnoDB' ) B USING (table_schema,table_name) WHERE B.table_schema NOT IN ('information_schema','mysql') ORDER BY table_schema,table_name,index_name,seq_in_index ) A GROUP BY table_schema,table_name,index_name ) AA ORDER BY db,tb;

将脚本保存为:loadtomem.sql,执行命令:

mysql -uroot -p -AN < /root/loadtomem.sql > /root/loadtomem.sql

在需要进行数据预热时就执行下面的命令:

mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

3. 降低日志的磁盘落盘

  • 增大redolog,减少落盘次数,innodb_log_file_size设置为0.25 * innodb_buffer_pool_size
  • 通用查询日志、慢查询日志可以不开,bin-log要开,慢日志查询可以遇到性能问题再开
  • 写redolog策略 调整innodb_flush_log_at_trx_commit参数为0或2。当然涉及安全性非常高的系统(金融等)还是保持默认的就行。

在配置文件里加上 innodb_flush_log_at_trx_commit =2 即可。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'

表结构设计优化

  1. 设计中间表
  • 设计中间表,一般针对于统计分析功能
  1. 设计冗余字段
  • 为减少关联查询,创建合理的冗余字段
  1. 拆表
  • 对于字段太多的大表,考虑拆表;对于表中经常不被使用的字段或存储数据比较多的字段,考虑拆表
  1. 主键优化
  • 主键类型最好是int类型,建议自增主键(分布式系统下用雪花算法)
  1. 字段的设计
  • 字段的宽度设得尽可能的小。
  • 尽量把字段设置为NOT NULL
  • 对于某些文本字段,如省份、性别等,我们可以把他们定义为enum类型。在mysql里enum类型被当作数值类型数据来处理,而数值型数据处理起来比文本类型快得多。

SQL语句及索引优化

  1. 学会用explain分析
  2. SQL语句中IN包含的值不应太多
  • MySQL对IN做了一些优化,将IN中的常量去不存在一个数组里,而且会进行排序。如果数值较多,这些步骤消耗也是比较大的。
  1. SELECT 语句务必指明字段名称
  • SELECT * 增加了很多不必要的消耗(CPU、IO、内存、网络带宽)
  1. 当只需要一条数据时,使用limit
  2. 排序字段加索引
  3. 如果查询条件中其他字段没有索引,少用or
  • or两边的字段中,如果有一个不是索引字段,则会造成该查询都不会走索引的情况。
  • select * from tbiguser where nickname='zy1' or loginname='zhaoyun3';
  • 如nickname是索引字段,loginname不是索引字段,则整体不会走索引。可以用union all代替
  1. 尽量用union all代替union
  • union和union all的区别是,union需要将结果集合并再进行唯一性过滤操作,这就会涉及到排序,增加了大量的CPU运算。当然,使用union all的前提条件是两个结果集没有重复数据。
  1. 区分in和exists、not in和not exists
  • exists:以外表为驱动表,先被访问。适合外表小而内表大的情况
  • in:先执行子查询。适合外表大而内表小的情况

关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句?

原语句:

select colname … from A表 where a.id not in (select b.id from B表)

优化后的语句:

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
  1. 不建议使用%前缀模糊查询,不会走索引
  2. 避免在where子句中对字段进行表达式或函数操作
  3. 避免隐式类型转换 如where age='18',如果确定是int类型,应写为where age = 18;
  4. 对于联合索引,要遵守最左前缀法则

举例来说索引含有字段id、name、school,可以直接用id字段,也可以id、name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面。

  1. 必要时可以使用force index来强制查询使用某个索引
  2. 注意范围查询语句 对于联合索引来说,如果存在范围查询,比如between,>,<等条件时,会造成后面的索引字段失效
  3. 使用JOIN优化 LEFT JOIN里左边的表为驱动表,RIGHT JOIN里右边的表为驱动表,而INNER JOIN MySQL会自动找出数据少的表为驱动表

注意:

  • MySQL没有full join,可以用以下方式解决
select * from A left join B on B.name = A.name where B.name is null union all select * from B;
  • 尽量用inner join,避免left join
  • 合理利用索引字段作为on的限制字段
  • 利用小表去驱动大表

下图是join查询的原理图,从图中可以看出如果能够减少驱动表的话,就能减少嵌套循环中的次数,以减少IO总量及CPU运算的次数。

图

SQL优化实战案例

介绍:tbiguser表有10000000条记录,表结构如下:

create table tbiguser( id int primary key auto_increment, nickname varchar(255), loginname varchar(255), age int , sex char(1), status int, address varchar(255) );

创建存储过程,并执行,插入一千万条数据

CREATE PROCEDURE test_insert() BEGIN DECLARE i INT DEFAULT 1; WHILE i<=10000000 DO insert into tbiguser VALUES(null,concat('zy',i),concat('zhaoyun',i),23,'1',1,'beijing'); SET i=i+1; END WHILE ; commit; END;
call test_insert

还有tuser1表和tuser2表,两个表结构一致。

create table tuser1( id int primary key auto_increment, name varchar(255), address varchar(255) ); create table tuser2( id int primary key auto_increment, name varchar(255), address varchar(255) );

图

需求:tbiguser表按照地区分组统计求和,并且要求是在tuser1表和tuser2表中出现过的地区。

按照需求写出SQL:

SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser1) GROUP BY address UNION SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser2) GROUP BY address

执行时间:4.65s

第一次优化:

加索引。我们可以给address字段加索引。

ALTER TABLE tuser1 ADD INDEX idx_address(address); ALTER TABLE tuser2 ADD INDEX idx_address(address); ALTER TABLE tbiguser ADD INDEX idx_address(address);

执行时间0.9s

我们用explain分析sql

图

发现有两次都扫描了964147行,就是tbiguser这个大表扫描了两次。且有临时表使用。于是我们进行优化

第二次优化

SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser1) OR address IN (SELECT address FROM tuser2) GROUP BY address

执行时间0.65s

图

没有临时表了,大表也只扫描了一次。另外我尝试这样查询:

SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser1 UNION ALL SELECT address FROM tuser2) GROUP BY address

执行时间12s。

SELECT COUNT(x.id),x.address FROM (SELECT DISTINCT b.* FROM tuser1 a,tbiguser b WHERE a.address=b.address UNION ALL SELECT DISTINCT b.* FROM tuser2 a,tbiguser b WHERE a.address=b.address) X GROUP BY x.address;

执行时间5.8s

根据实践发现,sql查询优化没有定式,不同的数据量下相同的sql表现是不一样的,需要灵活运用。

引用资料

  • https://zhuanlan.zhihu.com/p/523232110