1. 查询性能有哪些优化方法？

  1. 减少请求的数据量。
    • 只返回必要的列：最好不要使用 SELECT *进行查询。
    • 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
    • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能的提升也是很明显的。
  2. 减少服务器扫描的行数。

最有效的方式是使用索引来覆盖查询。

2. MySQL 性能优化

1. 部分 SQL 运行慢

部分 SQL 运行比较慢，排查方法有以下几个步骤：

  1. 根据慢日志定位慢查询 SQL
  2. 使用 explain 等工具分析这条 SQL
  3. 修改 SQL 或者尽量让 SQL 走索引

    解决方案：慢查询分析

    MySQL 中自带了慢查询日志的功能，开启它就可以用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10S 以上的语句。默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。使用mysql> show variables like '%slow_query_log%';来查询慢查询日志是否开启。

    扩展知识：SQL 语句分析

    在 MySQL 中我们可以使用 explain 命令来分析 SQL 的执行情况，比如：
    1. explain select * from person where id=5;
    如下图所示：
    数据库性能优化 - 图1
    其中：
  • id — 选择标识符，id 越大优先级越高，越先被执行；
  • select_type — 表示查询的类型；
  • table — 输出结果集的表；
  • partitions — 匹配的分区；
  • type — 表示表的连接类型；
  • possible_keys — 表示查询时，可能使用的索引；
  • key — 表示实际使用的索引；
  • key_len — 索引字段的长度；
  • ref— 列与索引的比较；
  • rows — 大概估算的行数；
  • filtered — 按表条件过滤的行百分比；
  • Extra — 执行情况的描述和说明。

其中最重要的就是 type 字段，type 值类型如下：

  • all — 扫描全表数据；
  • index — 遍历索引；
  • range — 索引范围查找；
  • index_subquery — 在子查询中使用 ref；
  • unique_subquery — 在子查询中使用 eq_ref；
  • ref_or_null — 对 null 进行索引的优化的 ref；
  • fulltext — 使用全文索引；
  • ref — 使用非唯一索引查找数据；
  • eq_ref — 在 join 查询中使用主键或唯一索引关联；
  • const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

    2. 单条 SQL 运行慢

    使用 explain 命令可以分析 SQL 的执行情况。一般来说，造成单条 SQL 运行比较慢的常见原因有以下两个：1. 未正常创建或使用索引；2. 表中数据量太大。

    解决方案1：创建并正确使用索引

    索引是一种能帮助 MySQL 提高查询效率的主要手段，因此一般情况下我们遇到的单条 SQL 性能问题，通常都是由于未创建或没有正确使用索引而导致的，所以在遇到单条 SQL 运行比较慢的情况下，你首先要做的就是检查此表的索引是否正常创建。

如果表的索引已经创建了，接下来就要检查一下此 SQL 语句是否正常触发了索引查询，如果发生以下情况那么 MySQL 将不能正常的使用索引：

  1. 在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描；
  2. 不能使用前导模糊查询，也就是 ‘%XX’ 或 ‘%XX%’，由于前导模糊不能利用索引的顺序，必须一个个去找，看是否满足条件，这样会导致全索引扫描或者全表扫描；
  3. 如果条件中有 or 即使其中有条件带索引也不会正常使用索引，要想使用 or 又想让索引生效，只能将 or 条件中的每个列都加上索引才能正常使用；
  4. 在 where 子句中对字段进行表达式操作。

因此你要尽量避免以上情况，除了正常使用索引之外，我们也可以使用以下技巧来优化索引的查询速度：

  1. 尽量使用主键查询，而非其他索引，因为主键查询不会触发回表查询；
  2. 查询语句尽可能简单，大语句拆小语句，减少锁时间；
  3. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型；
  4. 用 exists 替代 in 查询；
  5. 避免在索引列上使用 is null 和 is not null。

回表查询：普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。

解决方案2：分库分表（解决写压力问题）

当表中数据量太大时 SQL 的查询会比较慢，你可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。但分库分表会给系统带来巨大的复杂性，不是万不得已建议不要提前使用。

一般是先垂直再水平。
数据库性能优化 - 图2
垂直拆分：
垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。比如，用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中，插入数据时，使用事务确保两张表的数据一致性。垂直拆分的原则：

  • 把不常用的字段单独放在一张表；
  • 把 text，blob 等大字段拆分出来放在附表中；
  • 经常组合查询的列放在一张表中。

水平拆分：
水平切分是将同一个表中的记录拆分到多个结构相同的表中。当表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。当一个表的数据不断增多时，水平切分是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

通常情况下，我们使用取模的方式来进行表的拆分，比如，一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4，然后通过用户 ID 取模的方法，同时查询、更新、删除也是通过取模的方法来操作。

分表后的 ID 怎么保证唯一性？

  1. 设定步长，比如 1- 1024 张表我们分别设定的基础步长，这样主键落到不同的表就不会冲突了。
  2. 分布式 ID，使用分布式 ID 生成算法。
  3. 分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。

    3. 整个 SQL 运行慢

    当出现整个 SQL 都运行比较慢就说明目前数据库的承载能力已经到了峰值，因此我们需要使用一些数据库的扩展手段来缓解 MySQL 服务器了。

    解决方案：读写分离（解决读压力问题）

    读写分离常用代理方式来实现，代理服务器接受应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的主要原因在于：

  1. 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  2. 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；

  3. 增加冗余，提高可用性。

    扩展知识：MySQL 主从同步是怎么做的？

  4. master 提交完事务后，写入 binlog

  5. slave 连接到 master，获取 binlog
  6. master 创建 dump 线程，推送 binlog 到 slave
  7. slave 启动一个 IO 线程读取同步过来的 master 的 binlog，记录到 relay log 中继日志中
  8. slave 再开启一个 sql 线程读取 relay log 事件并在 slave 执行，完成同步
  9. slave 记录自己的 binlog

数据库性能优化 - 图3
由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制：
主库写入 binlog 后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制：
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回 ACK 确认给主库，主库收到至少一个从库的确认就认为写操作完成。

扩展知识：如何解决 MySQL 主从同步延时的导致数据不一致的问题？

业务高峰期MySQL可能会出现主从复制延迟，极端情况，主从延迟高达数秒。

如何监控主从同步状态呢？可以在从库机器上，执行show slave status，查看Seconds_Behind_Master值，代表主从同步从库落后主库的时间，单位为秒，若主从同步无延迟，这个值为0。

1. 数据库同步写方案

主从数据同步方案，一般都是采用的异步方式同步给备库。我们可以将其修改为同步方案，主从同步完成，主库上的写才能返回。

  1. 业务系统发起写操作，数据写主库
  2. 写请求需要等待主从同步完成才能返回
  3. 数据读从库，主从同步完成就能读到最新数据

这种方案，我们只需要修改数据库之间同步配置即可，业务层无需修改，相对简单。「不过，由于主库写需要等待主从完成，写请求的时延将会增加，吞吐量将会降低。」这一点对于现在的在线业务，可能无法接受。

2. 中间件或缓存选择路由方案

这种方案需要使用一个中间件，所有数据库操作都先发到中间件，由中间件再分发到相应的数据库。
数据库性能优化 - 图4
这时流程如下：

  1. 写请求，中间件将会发到主库，同时记录一下此时写请求的 key（操作表加主键等）
  2. 读请求，如果此时 key 存在，将会路由到主库
  3. 一定时间后（经验值），中间件认为主从同步完成，删除这个 key，后续读将会读从库

这种方案，可以保持数据读写的一致。但是系统架构增加了一个中间件，整体复杂度变高，业务开发也变得复杂，学习成本也比较高。

也可以使用缓存来代替中间件，使用缓存的好处是改造成本相对较低。

4. 其他的一些思路

  • 优化MySQL参数，比如增大innodb_buffer_pool_size，让更多操作在MySQL内存中完成，减少磁盘操作。
  • 使用高性能CPU主机。
  • 数据库使用物理主机，避免使用虚拟云主机，提升IO性能。
  • 使用SSD磁盘，提升IO性能。SSD的随机IO性能约是SATA硬盘的10倍甚至更高。
  • 业务代码优化，将实时性要求高的某些操作，强制使用主库做读操作。
  • 升级高版本MySQL，支持并行主从复制。

    3. MySQL 中的 21 个好习惯

    https://mp.weixin.qq.com/s/YFgBJ49fU662VgV2w1mY3Q

    4. 读写分离 TODO

    https://mp.weixin.qq.com/s/4mDmrzfkyECfJSWYexir6A

    5. 分库分表 TODO

    5.1 什么情况下需要分库分表？

    5.2 分库分表会带来什么问题？

    https://mp.weixin.qq.com/s/4mDmrzfkyECfJSWYexir6A

    参考

  1. 从零到千万用户，我是如何一步步优化MySQL数据库的？
  2. 数据库读写分离这个坑，让刚入职的我一脸懵逼！