2021年简单总结一下MySQL优化

最新推荐文章于 2024-10-02 06:14:43 发布

卤味微微

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量144

点赞数

文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/qzy_java_home/article/details/112606450

版权

2021年简单总结一下MySQL优化

一:为什么需要mysql优化

数据库在运行期间变慢, 或者sql语句执行的时候慢, 这个时候需要提升效率, 所以需要优化

二:mysql优化的结果

好的结果: sql语句运行变快, 数据库服务器变得速度快,
不好的结果: 数据库服务器变得不稳定, 容易宕机.

三:mysql优化的风险

有可能造成数据丢失, 有可能造成服务器不稳定, 可能宕机, 一定要提前备份表结构和数据. 防止丢失.
可以拉上业务部分代表, 部门技术领导, 等人替你分担风险.

四:数据库优化维度

硬件优化:
服务器, 决定性能好坏的因素很多, 网卡, cpu, 内存, 硬盘都是性能的决定因素, 主要看哪里是瓶颈, 也许哪里就是问题原因.
对硬件优化成本高, 需要花钱升级服务器硬件.
数据库结构优化:
可以从业务角度出发, 考虑对业务数据量大的数据库, 进行分库, 分表.
注意: 如果进行表结构更改, 项目中dao成基本都需要从新, 所以成本比较高.

五:sql语句优化:

在mysql中有慢查询日志, 这个日志中记录了所有运行中的执行慢的sql语句, 默认执行时间超过5秒中的sql语句就认为是执行慢的sql. 找到执行慢的sql语句再使用mysql的Explain执行计划进行分析, sql语句哪里慢. 然后再根据慢的技术点, 进行针对性优化.
在编写sql语句的时候, 可以参照sql优化文档, 对执行比较慢的sql语句进行调整优化

六:索引优化

是为了加快查询速度, 主要是提高查询的命中率, 避免全表扫描

数据库优化方案详解

sql语句优化(重点)
特点:
成本最低, 效果最好
优化思路

开启mysql的慢查询日志, 慢查询日志默认认为查询时间超过5秒的sql语句为执行慢的sql, 会记录到慢查询日志中, 这个时	间可以自定义更改.

使用mysql中的执行计划explain, 分析执行慢的sql语句, 看看哪里慢.

根据sql语句优化规则文档,  针对sql语句执行慢的部分, 按照sql语句优化规则文档进行优化.

索引优化(重点)
特点:

	字段加上索引后, 查询会变快, 增删改会变慢.

	查询多, 增删改少的地方加索引.

使用场景:

	数据量大的表, 查询非常多, 经常作为查询条件的字段上, 加索引. 提高查询速度.

索引类型

1. 主键索引 PRIMARY KEY**

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。 
  PRIMARY KEY (`id`)

2. 唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构。
   UNIQUE KEY `num` (`number`) USING BTREE

3. 普通索引 INDEX

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构
   KEY `num` (`number`) USING BTREE

4. 组合索引 INDEX

索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
   KEY `num` (`number`,`name`) USING BTREE
   
**注意，组合索引前面索引必须要先使用，后面的索引才能使用。**

5. 全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

索引结构

3.3.1 BTree索引

在前面的例子中我们看见有USING BTREE，这个是什么呢？这个就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引，也就是BTREE。

BTree占用存储空间比较大, 查询速度比较快, 标准的用空间换时间

BTREE索引以B+树的结构存储数据
BTREE索引能够加快数据的查询速度
BTREE索引更适合进行行范围查找

使用的场景：

全值匹配的查询，例如根据订单号查询 order_sn=‘98764322119900’
联合索引时会遵循最左前缀匹配的原则,即最左优先
匹配列前缀查询，例如：order_sn like ‘9876%’
匹配范围值的查找，例如：order_sn > ‘98764322119900’
只访问索引的查询

3.3.2 哈希索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

占用存储空间小, 查询速度快, 但是不是所有的地方都能用, 支持的功能比较少, 也就是只有在精确查询, 或者大于等于, 小于等于这样的查询中可以使用, 其他地方不可以使用

特点：

Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作；
Hash索引不能利用部分索引键查询；
Hash索引在任何时候都不能避免表扫描；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

3.3.3 Full-text全文索引(鸡肋)

Full-text索引也就是我们常说的全文索引，MySQL中仅有MyISAM和InnoDB存储引擎支持。 找mysql早期版本不支持, 现在新版本支持.

相当于全文检索的原理, 但是msyql不适合存储大文本数据, 所以一般这个索引类型不用.

如果有大文本数据, 可以放入ES中, mongodb中等地方, 不直接存储在mysql中, 因为	mysql存储大文本数据, 查询和传输效率会受很大影响

对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

存储引擎优化

MyISAM存储引擎

特点:

MyISAM不支持事务，不支持外键，SELECT/INSERT为主的应用可以使用该引擎。
每个MyISAM在存储成3个文件，扩展名分别是：

1) frm：存储表定义（表结构等信息）

2) MYD(MYData)，存储数据

3) MYI(MYIndex)，存储索引

不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。
MyISAM类型的表提供修复的工具，可以用CHECK TABLE语句来检查MyISAM表健康，并用REPAIR TABLE语句修复一个损坏的MyISAM表。
在MySQL5.6以前，只有MyISAM支持Full-text全文索引

Innodb存储引擎

特点:

经常UPDETE/INSERT的表，使用处理多并发的写请求
支持事务，必选InnoDB。
可以从灾难中恢复（日志+事务回滚）
外键约束、列属性AUTO_INCREMENT支持

数据库结构优化(重点)

表结构优化

垂直切分

使用场景

		字段较多的情况下使用

解决问题

		如果单表字段较多, 比如几十个字段, 那么一次查询返回的数据将会很多, 而几十个字段内容不可能在同一个页面展示出来, 一般都会分几个tab页面展示, 这样一次性查询过多数据, 查询数度慢, 而查询出来的结果又不加以使用, 比较浪费. 所以可以按照页面需要展示的内容, 将一张表切分成多个表

多张表关系是1:1:1…, 每张表里面的字段不同, 里面的数据不同.

水平切分

	使用场景

			mysql单表数据量大于500万条, 那么查询速度将会成指数级降低, 所以这个时候就需要进行水平分表, 提高查询命中率.

	解决的问题

			如果mysql单表大于500万条数据, 数据量越多, 查询速度越慢, 原因是在大量数据查询的时候, 命中率太低造成的. 所以需要水平分表解决

			将一张表水平的切割成多张表, 每张表他们的字段个数一样, 字段名一样, 字段类型一样, 但是里面存储的数据不一样, 例如一张表有一千万条数据, 那么可以分成两张表, 每张表存储500万条数据. 

			水平分表可以使用按照切勿规则切分, 比如:订单数据, 一般都是查询近一周, 一个月, 三个月的订单, 对于几年前的订单数据, 大多数人不关系, 所以就可以按照订单的创建时间进行水平分表. 查询的时候可以按照时间直接去对应表中查询订单数据.

		水平分表还可以按照hash算法进行分表, 使用一致性hash算法分表, 优点是表中数据分部均匀.

库结构优化

什么情况下需要分库

如果mysql服务器硬盘吞吐率, 也可以说是速度不够用, 那么可以按照业务, 将业务上有关联的几个表, 分成一个库. 这样分出来的多个库, 可以分别部署到不同的数据库服务器上. 这样就相当于根据业务使用不同的数据库服务器, 不同的服务器使用的硬盘不一样, 也就相当于提升了硬盘的吞吐率.

例子:

一个库有三张表, user用户, goods商品, order订单表, 这三张表数据量都很大, 访问频次很高.

解决方案:

将user表用表分到用户库中

将goods商品表分到商品库中

将order订单表分到订单库中

将这分出来的三个库, 分别放到不同的mysql数据库服务器中部署.

原来三张表在同一个数据库, 使用同一个服务器硬盘, 硬盘读和写的速度是有上限的, 不够用.

那么经过这样的分库之后, 也就相当于使用了三个不同的数据库服务器, 也就是三个不同的硬盘. 所以提高了硬盘的吞吐效率.

集群优化

读写分离方案

使用场景

		读远远大于写, 高并发读取

集群做法

		一台主机master, 多台备机slave,

		主机只允许写入, 不允许人为读取, 备机只允许读取, 不允许认为写入操作

		我们可以向主机中写入数据, 主机会将数据自动同步到多台备机中.

		如果我们需要查询数据, 可以从多台备机中查询结果, 由于查询的服务器是多台, 所以提升了读取可以抗住的并发量.

缺点:

		数据主和备之间存在一定时间的数据不一致情况.

对等集群方案

使用场景

		写入操作大于读取操作, 高并发写入

集群做法

		多台主机, 没有备机,

		多台主机里面的库是一样的, 表是一样的, 表中的字段个数, 字段类型都一样, 但是存储的数据不一样

		高并发写入的时候, 可以一些请求往A数据库中写入, 一些请求可以往B数据库中写入, 一些请求可以往C数据库中写入. 由于多个库都是一样的, 但是部署到了不同的服务器中, 所以相当于提升了硬盘的高并发写入能力.

总结

优化思路:

	数据库优化有很多, 我是按照, sql语句优化, 索引优化, 数据库表结构优化等思路完成

	sql语句优化, 首先要通过慢查询日志判断出来查询慢的sql是哪个, 然后根据执行计划分析哪里慢, 最后根据优化规则文档进行优化sql语句

	索引优化, 是看哪个表, 哪个字段, 查询多, 数据量大, 可以考虑加索引. 加索引的类型大概率加BTree结构索引. 因为BTree索引索引占用存储空间大, 但是功能使用比较广泛. 目前硬盘存储空间不太值钱, 所以可以首先考虑BTree. 索引类型主键索引不需要考虑, 因为主键索引是创建表的时候, 数据库默认给主键添加的, 至于唯一索引, 普通索引, 组合索引会根据业务进行添加.

	再就是看单表数据量, 表中的字段个数, 考虑分库分表, 字段多垂直分表,数据量大于单表500万条, 考虑水平分表, 水平分表有特殊业务可以按照业务分表, 比如时间规则, 如果没有业务规则按照hash分表. 水平分表可以解决数据量多, 查询命中率的问题, 如果是硬盘磁盘读取效率不够, 可以考虑按照业务规则分库.分库后就是将不同业务的库放到不同的服务器上, 可以提高硬盘的吞吐率.

	如果读取和写入的吞吐率还达不到要求, 可以做数据库集群.

	如果是读多写少, 考虑读写分离, 如果都是高并发写入, 考虑对等集群.

	数据库集群可以使用mycat, corbar等后端集群中间件

	也可以使用shardingJDBC来当传统的JDBC使用, 作为客户端集群工具.