MySQL优化

Blue Protocol

已于 2023-03-03 09:09:50 修改

阅读量499

点赞数 1

分类专栏： MySQL 文章标签：数据库 MySQL SQL优化方案

于 2022-10-12 14:17:57 首次发布

本文链接：https://blog.csdn.net/weixin_51921447/article/details/127280811

版权

MySQL 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

如何优化一条SQL语句？SQL优化

那么我们就得看这条SQL语句是什么样子的
是select语句？还是insert语句？

insert语句

如果是insert语句插入比较慢的情况下，我们该如何优化呢？
1、可以使用消息中间件的异步来提高响应客户端的响应速度，或者我们也可以改写insert into 语句为 insert delayed into ,这个insert delayed 不同之处在于：立即返回结果，后台进行处理，与用异步处理的方式差不多。
2、如果是多条insert into语句的执行，我们可以这样优化：
1）把多条insert into语句修改成一条insert into 语句。如

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);

修改成：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

这样执行效率较高的原因主要有两个：
一、减少SQL语句解析的操作，只需要要解析异常就能进行数据的插入处理。
二、可以减少网络传输的IO

2）使用事务可以提高数据的插入效率，因为进行一个insert操作时，MySQL内部会建立一个事务，在事务内进行真正的插入处理。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。

select语句

优化一条查询语句，我们还得看看索引的命中情况，所以我们需要熟悉MySQL执行优化器explain

expain执行优化器的使用

执行优化器我们其实只需要在SQL语句前面加expain关键字即可。

一般会输出如下字段
在这里插入图片描述

expain执行优化器输出字段的解释

首先我们需要理解expain各个字段的含义，才能更好得使用expain执行优化器。

id
id的话，一般会存在三种情况
1、id相同，执行顺序由上到下。
2、id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
3、id有相同和不相同的，遵守上述1/2规则。

举个例子
在这里插入图片描述
我们可以这样理解，执行顺序由大到小，所以会先执行id为2的，然后再执行id为1的两个，id相同，执行顺序由上到下，所以先A再B；执行顺序为第三行，第一行再到第二行。

select_type
1、SIMPLE：简单的select,不使用union和子查询。
2、PRIMARY：查询中包含子部分，最外层的查询的select_type被标记为PRIMARY。
3、SUBQUERY：子查询中的第一个查询语句。

在这里插入图片描述

4、UNION：union关键字后面的查询语句。
5、UNION RESULT：union的结果。

在这里插入图片描述
其他的感觉都不怎么重要。

table
显示数据来自于哪个表，有时候不能真实的表名，可能是虚拟表，虚拟表后面的数字，代表id为多少的查询。

非常重要：type

我们重点关注这个字段，type直接翻译我们SQL的性能是否高效。
这个字段的值较多，这里我只关注我们开发中常用到的几个段：system,const,eq_ref,ref,range,index,all
性能由好到差依次为：system > const > eq_ref > ref > range > index >all（一定要牢记）

system：表只有一行记录，这个是const的特例，一般不会出现。
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。

在这里插入图片描述

eq_ref：唯一索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或者是唯一索引。

在这里插入图片描述

ref：非唯一索引扫描，返回匹配某个值的所有行。

在这里插入图片描述

range：检索给定范围的行，一般条件查询中出现了>、<、in、between等查询

在这里插入图片描述

index：遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。ALL和INDEX都是读全表，但index是从索引中检索的，而all是从硬盘中检索到。

在这里插入图片描述

all：遍历全表以找到匹配的行。

在这里插入图片描述
possible_keys

显示可能应用在这张表中的索引，但不一定被使用。

key

实践使用到的索引。

key_len

表示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。
一般来说
索引长度越长，精度越高，效率越低。
索引长度越短，精度越低，效率越高。
但是这个是越高预估值，并不是真正使用索引的长度。

ref

表示那一列被使用了，常树表示这一列等于某个常数

rows

大致找到所需要记录读取的行数。

filter

表示所选取的行和读取的行的百分比，100表示读取了100%，80表示读取了80%。

非常重要：extra

一些重要的额外信息。

1、using filesort ：这个值说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取的。MYSQL中无法利用索引完成的排序称为"文件排序"。（糟糕的，一般需优化）
2、using temporary：使用了临时表来保存中间结果。常见于排序order by和分组查询group by。（更糟糕的，最后优化）
3、using index：表示select语句中使用了覆盖索引，通过索引直接返回数据区的内容，而不需要回表操作。
4、using where：使用了where过滤。
5、using index condition：表示查询的列有非索引的列，先判断索引条件，来减少磁盘的IO。
6、using join buffer：使用了连接缓存。
7、impossible where ：where 子句的值总是false。

而我们优化select条件语句的时候，我们需要使用执行优化器来查看select语句有没有命中索引。

一、常用的SQL优化手段

查询语句中不要使用select *；
尽量减少子查询，使用关联查询（left join,right join,inner join）替代
减少使用in或者not in,使用exists,not exists或者关联查询语句替代
or的查询尽量使用union或者union all替代（在确认没有重复数据或者不用剔除重复数据时，union all会更好）

以下的情况会使得索引无法命中：

应尽量避免在 where 子句中使用!=或者<>操作符，否则引擎将会放弃使用索引而进行全表扫描。
应尽量避免在where子句中使用or来连接条件，否则将导致执行引擎放弃使用索引而进行全表扫描，如：select id from t where num = 10 or num = 20；
应尽量避免在where子句中对字段进行null判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num = 0；
应尽量避免在where子句中对字段进行函数操作，否则将导致执行引擎放弃使用索引而进行全表扫描。
不要在where 子句中的 = 左边进行函数、算术运算或者其他表达式运算，否则将肯能无法正确使用索引。
阻断是字符串类型的时候，必须加引号，否则索引失效。
like 查询，如果 % 在前面，则无法使用索引，如果需要模糊匹配，可以使用全文索引。

二、大表优化手段

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围

务必禁止不带限制范围条件的查询语句。比如：我们当用户查询历史订单的时候，我们可以控制在一个月的范围内。

读写分离

读写分离是经典的数据库拆分方案，主库负责写，从库负责读

分库分表
（1）垂直拆分

简单来说垂直拆分是指将数据库表列的拆分，把一张列比较多的表拆分为多张表。如下图所示，这样来说就应该更容易理解了

请添加图片描述

垂直拆分的优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直拆分会让事务变得更加复杂

（2）水平分区

保持数据表的结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张表的数据拆分成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支撑非常大的数据量。需要注意一点是：分表仅仅是解决了单一表数据过大的问题，单由于表的数据还是在同一台机器上，其实对于提升MySql并发能力没有什么意义，所以水平拆分最好是分库。

水平拆分能耐支持非常大的数据量存储，应用端改造也少，但是分配事务难以解决，跨节点Join性能超差，逻辑复杂。《Java工程师修炼之道》的作者推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。

请添加图片描述

优化注意点

（1）分库分表之后，主键id如何处理？

因为如果是分成多个表之后，每个表都是从1开始累加的，这样是不对的，我们需要一个全局唯一的id来支持。

生成全局id的几种方式

UUID：不适合做主键，因为太长了，并且是无序的，查询效率低。比较适合用于生成唯一的名字的标识例如文件的名字。
数据库自增id：两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这个生成的id有序，但是需要独立不是数据库实例，成本高，还会有性能瓶颈。
利用Redis生成的id：性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性减低，编码更加复杂，增加了系统成本。
Twitter的snowflake算法：SnowFlake
美团的Leaf分布式ID生成系统：Leaf是美团开源的分布式ID生成器，能保证ID的全局唯一性、趋势递增、单调递增、信息安全，里面也里面也提到了几种分布式方案的对比，但是也需要依赖管理数据库、Zookeeper等组件。
有关美团技术团队的一片文章,里面包含了几个常见的分布式全局唯一算法，并进行了分析：常见的几个分布式全局唯一算法

三、MySQL高可用

MySQL高可用，意味着不能一台MySQL出了问题，就不能访问了。
那么如何做到MySQL如何做到高可用呢？
1、MySQL高可用：分库分表，通过Sharding-JDBC或者MyCat连接多个MySQL。
2、如果使用MyCat,MyCat也得高可用，使用Haproxy连接多个Mycat;
3、Haproxy也得高可用：通过keepalived辅助Haproxy

四、有关联结的相关知识

什么是内联接、左外联接、右外联接？

内联接（Inner Join）：匹配2张表中相关联的记录。
左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。
右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join 的左右位置关系。