Mysql优化

置顶 ww_kobe

已于 2023-08-10 11:16:11 修改

阅读量245

点赞数 1

分类专栏：数据库文章标签： sql 数据库 java

于 2021-05-19 15:52:21 首次发布

本文链接：https://blog.csdn.net/ww_kobe/article/details/117030516

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

3.2、设置刷盘策略，平衡好数据安全性和性能关系

4、硬件&系统设置

5、select 语句完整执行顺序

6、使用explain sql 分析sql语句

Mysql优化

1、表结构&索引

分库分表、读写分离、为字段选择合适的数据类型、适当的反范式设计、为查询操作创建必要的索引等等。

1.1、选择正确的数据库引擎

Mysql 中有两个引擎 MyISAM 和 InnoDB

MyISAM：不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描。适用于以读为主的应用程序，比如博客系统、新闻门户等网站。另外，MyISAM 对于 select count(*)这类操作是超级快的。
InnoDB：支持外键、事务，行锁，查表总行数时，全表扫描，所以在写操作比较多的时候会比较优秀。比如OA自动化办公系统。

1.2、选择正确的索引类型

mysql中有Hash索引、B+索引

Hash索引：等值查询效率高，不能排序，不能进行范围查询。
B+索引：数据有序，范围查询。

1.3、表设计原则

三大范式

第一范式：属性的原子性，要求属性具有原子性，不可在分解。

第二范式：记录唯一性，要求记录有唯一标识，实体的唯一性，即不存在部分依赖。

第三范式：属性冗余性，要求属性没有冗余，即不存在传递依赖。

范式的利弊：

优点：范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。

缺点：按照范式的规范设计出来的表，等级越高的范式设计出来的表越多。如第一范式可能设计出来的表可能只有一张表而已，再按照第二范式去设计这张表时就可能出来两张或更多张表，如果再按第三范式或更高的范式去设计这张表会出现更多比第二范式多的表。表的数量越多，当我们去查询一些数据，必然要去多表中去查询数据，这样查询的时间要比在一张表中查询中所用的时间要高很多。也就是说我们所用的范式越高，对数据操作的性能越低。所以我们在利用范式设计表的时候，要根据具体的需求再去权衡是否使用更高范式去设计表。

反范式

故名思义，跟范式所要求的正好相反，在反范式的设计模式，我们可以允许适当的数据的冗余，用这个冗余去取操作数据时间的缩短。也就是用空间来换取时间,把数据冗余在多个表中，当查询时可以减少或者是避免表之间的关联。

反范式的利弊：

优点：查询时可以减少表的关联，可以更好的进行索引优化。

缺点：存在数据冗余以及数据维护异常，对数据的修改需要更多的成本。

1.4、索引设计原则

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
较频繁作为查询条件的字段才去创建索引，更新频繁字段不适合创建索引
基数较小的类，索引效果较差，没有必要在此列建立索引
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，对于定义为text、image和bit的数据类型的列不要建立索引
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可

2、sql语句优化

2.1、sql优化

避免select * 查询，尽可能使用索引扫描等等。
只要一行数据时使用 limit 1。查询时如果已知会得到一条数据，这种情况下加上limit 1会增加性能。因为mysql数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。
not exists代替not in。not exists 用到了连接能够发挥已经建立好的索引的作用，not in不能使用索引。not in是最慢的方式要同每条记录比较，在数据量比较大的操作中不建议使用这种方式。
操作符的优化，尽量不采用不利于索引的操作符。如：in、not in、is null、is not null、<> 等，某个字段总要拿来搜索，为其建立索引。
减少使用or，使用 union all 或者 union 来替代。

2.2、索引失效情况

索引列上不能有范围查询，将可能做范围查询的索引字段尽量放最后：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描。
like以%开始。
字段允许为 null 的条件下：is not null 用不到索引，is null 可以用到索引。
用 (!= 、<、>) 的时候，会导致索引失效。

3、Mysql参数优化

3.1、设置Buffer_pool大小

官方推荐总内存50%-70%左右。

3.2、设置刷盘策略，平衡好数据安全性和性能关系

sync-binlog：控制binlog刷入磁盘的频率。default vaule：1

0：禁止MySQL服务器将二进制日志同步到磁盘。相反，MySQL服务器依赖于操作系统不时地将二进制日志刷新到磁盘，就像处理其他文件一样。此设置提供了最佳性能，但是在出现电源故障或操作系统崩溃时，服务器可能提交了未同步到二进制日志的事务。

1：允许在事务提交之前将二进制日志同步到磁盘。这是最安全的设置，但是由于磁盘写操作的增加，可能会对性能产生负面影响。在出现电源故障或操作系统崩溃时，二进制日志中缺少的事务仅处于准备状态。这允许自动恢复例程回滚事务，从而保证二进制日志中没有丢失任何事务。

N：其中N是0或1之外的值:在收集了N个二进制日志提交组之后，将二进制日志同步到磁盘。在出现电源故障或操作系统崩溃时，服务器可能提交了未刷新到二进制日志的事务。由于磁盘写操作数量的增加，该设置可能会对性能产生负面影响。值越大，性能越好，但是数据丢失的风险越大。

innodb-flush-log-at-trx-commit：控制redo log刷新到磁盘。default vaule：1

控制提交操作的严格的ACID遵从性和更高的性能之间的平衡，当提交相关的I/O操作被重新安排并成批执行时，可以获得更高的性能。通过更改默认值可以获得更好的性能，但是在崩溃时可能会丢失事务。

1：默认设置为1是完全符合ACID要求的。日志在每次事务提交时被写入并刷新到磁盘。

0：设置为0时，每秒将日志写入并刷新到磁盘一次。没有刷新日志的事务可能在崩溃中丢失。

2：如果设置为2，则在每个事务提交之后写入日志，并每秒刷新一次磁盘。没有刷新日志的事务可能在崩溃中丢失。

4、硬件&系统设置

CPU核心数、磁盘的读写性能、网卡、内存大小等等。

5、select 语句完整执行顺序

FROM：对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。
ON：对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2。
OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置。
WHERE：对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4。
GROUP BY：按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5。
CUTE|ROLLUP：把超组插入 VT5，生成 VT6。
HAVING：对 VT6 应用 HAVING 筛选器，只有使为 true 的组插入到 VT7。
SELECT：处理 SELECT 列表，产生 VT8。
DISTINCT：将重复的行从 VT8 中删除，产品 VT9。
ORDER BY：将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。
TOP：从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者。

where 子句中的条件书写顺序

6、使用explain sql 分析sql语句

table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为 const、eq_reg、ref、range、index和 ALL
all: full table scan ;MySQL将遍历全表以找到匹配的行；
index ： index scan; index 和 all的区别在于index类型只遍历索引；
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与 between，<,>等查询；
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；
const，system：当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MySQL认为必须检查的用来返回请求数据的行数