Mysql优化

2023-08-25 15:26 作者:尚学堂日报 0人读过 | 我要投稿

Sql性能下降的原因

在程序的运行过程中，我们会发现这样的一个现象，随着程序运行时间的不断推移以及数据量越来越大，程序响应的时间逐渐变慢，程序变得卡顿，但最开始的时候并不是这样的，那是什么原因导致的呢？

性能下降的原因

外部原因

计算机磁盘空间占用较大，服务器cpu性能及内存不足
内部原因

程序员编写的sql语句有问题索引失效

表关联太多

服务器参数设置不合理

Sql读取顺序

我们在编写sql代码时的顺序和Mysql内部读取sql时的顺序是不一样的，因此有必要搞清楚Mysql读取sql语句的顺序。

代码编写的顺序

Mysql读取顺序

整体过程

先对多表进行关联，根据条件找出符合的记录

在符合的记录基础上进行where条件过滤

对筛选出的记录进行分组操作

分组完成后再进行having操作，过滤出满足条件的数据

对取出的记录进行排序

再按照分页条件取出要显示的数据

explain执行计划

Mysql 使用 explain 关键字可以模拟优化器执行 sql 语句，我们就能够知道 Mysql 会如何处理sql，可以根据 explain 的分析结果和Mysql 底层数据结构优化 sql。不同Mysql 版本可能有差别，但差别不会很大。

执行示例：

id：select查询的序号，表示执行select操作时表的顺序。

id相同按顺序走

id不同，序号大的先执行

id相同，不同同时存在

select_type

查询类型，主要用于区别普通查询、联合查询、子查询

simple：简单select查询，查询中不包含子查询或union；

PRIMARY：主键查询

SUBQUERY：where条件包含了子查询

DERIVED：from的表中包含子查询，被标记为derived（衍生），把子查询的结果放在临时表中

table

显示这一行的数据是来自于哪张表的
partitions

如果查询是基于分区的话，会显示查询访问的分区

type

访问类型，按性能从低到高依次排列为

ALL：全表扫描，一定要优化

index：它和All都是扫描全表，但index是从索引中读取表，All是从硬盘中读取

range：只检索给定范围的行，key列显示使用了哪个索引， between and 或 in 等查询ref：非唯一性索引扫描，本质上也是一种索引访问

eq-ref：唯一性索引扫描，对于每个索引键只有一条记录与之匹配

const：通过索引一次就找到了，常见于primary或unique索引查找

system：表中只有一行记录（系统表），很少出现

NULL：不需要访问表

possible_keys：显示可能应用在这张表中的索引，一个或多个，查询涉及的字段上若建立了索引则会列出来，但不一定被使用
key：它和possible_keys的关系，理论上应该用到哪些索引，实际上用到了哪些索引
key_len：索引使用的字节数，key_len显示的值为索引字段的最大可能长度
ref：索引是否被引用到，用到了哪几个索引
rows：根据表统计信息及索引使用情况，估算所需读取的记录行数
filtered：满足查询条件记录数量的比例，是百分比，不是具体的记录数，这个值越大越好，它依赖于统计信息，并不是很准确
Extra：Using filesort（文件排序，表示mysql无法利用索引完成排序操作）

Using temporary（使用了临时表保存中间结果，常见于order by和group by）

Using index（如果同时出现Using where，表明索引用来执行索引键值的查找，如果没有出现Using where，表明索引用来读取数据而非执行查找）

Using where（使用了where过滤）

Using join buffer（使用了连接缓存）

impossible where（where子句的值总是false，不能用来获取记录）

优化技巧

建表规约

各大公司都有自己的MySQL开发规约，我们以阿里为例，阿里的

MySQL开发规约如下：

表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。

说明：任何字段如果为非负数，必须是 unsigned。

注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀。数据库表示是与否的值，使用 tinyint 类型，以is_xxx 的命名方式是为了明确其取值含义与取值范围。例：表达逻辑删除的字段名is_deleted，1 表示删除，0 表示未删除
表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

正例：aliyun_admin，rdc_config，level3_name

反例：AliyunAdmin，rdcConfig，level_3_name
表名不使用复数名词。

说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。
禁用保留字，如 desc、range、match、delayed 等，请参考MySQL 官方保留字。
主键索引名为 pk字段名；唯一索引名为 uk字段名；普通索引名则为 idx字段名。

说明：pk 即 primary key；uk_ 即 unique key；idx_ 即 index的简称。
小数类型为 decimal，禁止使用 float 和 double。

说明：float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：

不是频繁修改的字段；

不是 varchar 超长字段，更不能是 text 字段

例如，商品单位使用的频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储单位名称，避免关联查询。
合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

正例：如下表，其中无符号值可以避免误存负数，且扩大了表示范围。