MYSQL优化汇总

最新推荐文章于 2022-10-18 19:00:00 发布

呕泡果奶

最新推荐文章于 2022-10-18 19:00:00 发布

阅读量119

点赞数

分类专栏： MySQL 文章标签： MYSQL优化

本文链接：https://blog.csdn.net/weixin_43725997/article/details/94455999

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、表结构设计优化

a、字段选取

varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

说明：varchar(N)，N表示最大字符数，实际存储需要看实际值的字符长度，其中长度信息占用两个字节，如果可以为NULL，则是否为NULL标记会占用一字节，字符所占字节数跟编码有关，latin1编码一个字符一个字节，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节。

预计不会存储非负数的字段，例如各项id、状态值等，必须设置为UNSIGNED类型。

说明：UNSIGNED类型比非UNSIGNED类型所能存储的正整数范围大一倍，因此能获得更大的数值存储空间。

基于效率的考虑，经常需要作为查询条件的字段均不能为空，即全部NOT NULL；

说明：允许NULL值的字段，数据库在进行比较操作时，会先判断其是否为NULL，非NULL时才进行值的比对，在数据存储上是否为NULL的标记需要占用1个字节。

可以使用整型的避免使用字符类型。

说明：数值运算一般比字符串运算更快。例如比较运算，可在单一运算中对数进行比较。而串运算涉及几个逐字节的比较，如果串更长的话，这种比较还要多。如果某一个列的值数目有限，应该利用普通整型或emum类型来获得数值运算的优越性，避免使用字符类型。

应尽量使得常被读写的大表为定长类型。

说明：对于变长表，由于记录大小不同，在其上进行许多删除和更改将会使表中的碎片更多。需要定期运行OPTIMIZE TABLE以保持性能。而定长表就没有这个问题；大表定长化，可以通过改变数据存储结构和数据读取方式，将一个大表拆成一个读写多的定长表，和一个读多写少的变长表来实现。

b、索引规范

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验和控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、 SQL 性能。

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

如果有 order by 的场景，请注意利用索引的有序性。 order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

正例： where a = ? and b = ? order by c; 索引： a_b_c
反例：索引中有范围查找，那么索引有序性无法利用，如： WHERE a > 10 ORDER BY b; 索引 a_b无法排序。

利用延迟关联或者子查询优化超多分页场景。

说明： MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N行，那当offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
.
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id = b.id

建组合索引的时候，区分度最高的在最左边。

正例：如果 where a = ? and b = ? ， a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
.
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： where a>? and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。