Mysql 数据库优化 -- Mysql面试

1MySQL 数据库优化

数据库优化一方面是找出系统的瓶颈,提高mysql的数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的响应速度,同时还要尽可能的去节约系统资源, 以便系统提供更大的去符合项目性能

1.优化的一览图:

1.1 软优化: 数据库的一些操作

• 查询语句优化;
• 优化子查询
• 使用索引
• 分解表
• 增加一些中间表
• 增加冗余字段

1.1.1 查询语句优化

首先 我们需要知道, MySQL的一个查询过程 :
它是 一个 客户端/服务端通信协议 : 相当于是一个半双工的一个过程, 在任一个时刻,要么是服务器向客户端发送数据, 要么是,客户端向服务器发送数据, 两个动作是不可能同时发生的; 一旦一端开始发送消息,另一端要接收完整的消息才能响应, 所以我们也无法将一个消息切成小块然后独立发送,这样还涉及到了 流量控制.
客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候, 需要设置max_allowed_packet 参数,但是需要注意的是, 如果查询实在太大的话, 服务端会拒绝接收更多的数据并抛出异常

max_allowed_packet : 参数定义了客户端和服务器之间传输的最大数据包大小限制。
如果试图传输的数据包大小超过这个限制，MySQL 将会拒绝该操作并可能会导致错误,
通常情况下， max_allowed_packet 参数的数值，默认值为 4 MB。如果您需要处理大量的数据传输或需要导入大型数据文件，可能需要增加这个参数的值以确保顺利完成数据传输操作。

[mysqld]
max_allowed_packet=xxM

其中，“xx” 代表您想要设置的数据包大小，单位为 MB。设置完毕后，记得重启 MySQL 服务使修改生效。
总的来说，max_allowed_packet 参数的作用是控制 MySQL 数据库允许传输的最大数据包大小，通过合理设置这个参数可以更好地处理大量数据传输操作。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT * 以及加上LIMIT限制的原因之一。

1.1.2 查询缓存

  在查询一个语句之前, 如果查询缓存是打开的, 那么mysql 会检查这个查询语句是否命中查询缓存中的数据,查询不会被优化, 也不会生成执行机会,更不会执行
   Mysql 将缓存放在一个引用表里面 , 这个引用表不是table , 而是一个类似于Hash map的一个结构, 通过一个哈希值索引, 这个哈希值 就可以通过查询本身,当前要查询的数据库,客户端协议版本号等一些可能影响结果的信息计算得来,所以在查询时候,任何字符的不同都会导致缓存不命中
  如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。
     当然哈, 缓存也会失效, ,如果 涉及到的表的数据或者结构发生了变化, 那么和这个表有关的所有缓存都会失效, 因此我们在进行数据库的写操作的时候, 我们会将这些表的缓存设置为失效, 在读操作时候,:

1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

     如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

• 用多个小表代替一个大表，注意不要过度设计
• 批量插入代替循环单条插入
• 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
• 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

  1.1.3 语法解析,预处理

     MySQL 通过关键字将SQL语句进行解析,并生成一颗对应的解析树,这个过程解析器主要通过 语法规则,来进行验证和解析, 例如,sql中是否使用了 错误的关键字或者关键字的顺序是否正确等,
     预处理则会根绝MySQL规则,进一步检查解析树是否合法,比如检查要查询的数据和数据列是否存在等等

1.1.4 查询优化

     MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
     MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

• 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
• 优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值）
• 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
• 优化排序（单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。）

     在 完成解析和 优化阶段 以后,MySQL会生成对应的一个 执行计划, 查询引擎根据执行计划给出的指令,逐步完成,得出结果,

1.2 Scheme优化 与数据类型优化

    选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

1.3 创建高性能索引

    尽量使使用b树和b+树

2 硬优化: 操作服务器硬件和参数配置

• 优化硬件的三件套:CPU 内存 磁盘
• 参数设置
• 分库分表 进行 读写分离 [ 问的较多 ]

  1 读写分离

      因为用户增多了,数据的增多,单机的数据库往往会支撑不住快速发展的业务,所以就出现了数据库集群
       读写分离: 顾名思义: 就是读和写分开了,对应到数据库集群中就是 一主一从,一个主库一个从库,或者是, 一个主库,多个从库,
       业务服务器,把需要写的操作都写到主数据库中,读的操作都去从库查询,主库会同步数据到从库保证数据的一致性
       这种集群方式的本质就是把访问的压力从主库转移到从库，适合读的请求较多的情况下。
      在单机的情况下，一般我们做数据库优化都会加索引，但是加了索引对查询有优化，但是会影响写入，因为写入数据会更新索引。所以做了主从之后，我们可以单独的针对从库(读库)做索引上的优化，而主库(写库)可以减少索引而提高写的效率。
  主从同步延迟
      主库有数据写入之后，同时也写入在binlog(二进制日志文件)中，从库是通过binlog文件来同步数据的，这期间会有一定时间的延迟，可能是1秒，如果同时有大量数据写入的话，时间可能更长。
      所以为了解决主从同步延迟的问题有以下几个方法：
  1. 二次读取：意思就是读从库没读到之后再去主库读一下
  2. 写之后的马上的读操作访问主库
  3. 关键业务读写都由主库承担，非关键业务读写分离

  2 分库分表

  分库
      假设数据库中有两张表分别是用户表和订单表。如果要分库的话现在你需要买两台机子，搞两个数据库分别放在两台机子上，并且一个数据库放用户表，一个数据库放订单表。
  
  分表
  我们已经做了分库了，但是现在情况是我们一个表里面的数据太多了，就一不小心你的公司的产品火了，像抖音这种，所有用户如果就存在一张表里吃不消，所以这时候得分表。分别又分垂直分表和水平分表。

1. 垂直分表

比如我们表有10列，现在一刀切下去，分成了两张表，其中一张表3列，另一张表7列。

这个一刀切下去让两个表分别有几列不是固定的，垂直分表适合表中存在不常用并且占用了大量空间的字段拆分出去。

就拿头条的用户信息，比如用户表只有用户id、昵称、手机号、个人简介这4个字段。但是手机号和个人简介这种信息就属于不太常用的，占用的空间也不小，个人简介有些人写了一坨。所以就把手机号和个人简介这两列拆分出去。

那垂直分表影响就是之前只要一个查询的，现在需要两次查询才能拿到分表之前的完整用户表信息。

2. 水平分表

比如现在用户表有5000万行数据，我们切5刀，分成5个表，每个表1000万行数据。

水平分表就适合用户表行数很多的情况下，一般单表行数超过5000万就得分表，如果单表的数据比较复杂那可能2000万甚至1000万就得分了，这个得看实际情况有些表很简单可能一亿行都不用分。所以当一个表行数超过千万级别的时候关注一下，如果没有性能问题就可以再等等看，不要急着分表，因为分表会是带来很多问题。
水平分表的问题比垂直分表就更烦了。
要考虑怎么切，讲的高级点就叫路由：

1. 按id（也就是范围路由）

比如id 值1-999万的放一张表，1000万-1999万放一张表，一次类推（也就是一段一段）。这个得试的，因为范围分的大了，可能性能还有问题，范围分的小了。。那表不得多死。

这种分法的好处就是容易切啊，简单粗暴，以后新增的数据分表都不会影响到之前的数据，之前的数据都不需要移动。

2. 哈希路由

就是取几列哈希一下看看数据哪个库，比如拿id来做哈希，1500取余8等于4，所以这条记录就放在user_4这个表中，2011取余8等于3，所以这条记录就放在user_3中。这种分法好处就是分的很均匀，基本上每个表的数据都差不多，但是以后新增数据又得分表了咋办，以前的数据都得动，比较烦！

3. 搞一张表来存储路由关系

还是拿用户表来说，就是弄一个路由表，里面存userId和表编号，表示这个userId是这张user表的的。这种方式也简单，之后又要分表了之后改改路由表，迁移一部分数据。但是这种方法导致每次查询都得查两次，并且如果路由表太大了，那路由表又成为瓶颈了！