我觉得优化mysql，或者说优化数据库应该从几个层面 来说，分别是 1.软件层面 2硬件层面 3架构层面

1.软件层面

  • 那最简单明了的方式就是直接升级MYSQL，从5.7升级到8.0。因为按照MYSQL的官方描述，8.0的性能是5.7的两倍.，因为MYSQL8.0 ，优化了InnoDB存储引擎(比如我看了下官方文档介绍（这句话一定要说），mysql8 已经支持对索引进行拆分，简单来说把一个大索引拆分成很多个小索引，其实和currenthashmap1.7里面的分段锁思想一致，这么做可以减少锁的冲突，因为在插入修改数据时会对索引进行更新，拆分索引之后可以减少锁粒度。这个虽然官方这么说，但是我自己也没试过)，我觉得还是要以实际数据压测为主。

  • 第二个最关键的地方就是去建立合适的索引。索引的好坏决定了查询的快慢。mysql的索引底层是B+树，他是一个平衡二叉查找树的升级版，并且按照mysql 默认页大小16K的，单条数据1K的情况下，三层B+树可以存储2000多W的数据，在这个情况下也只需要3次IO就能查询到数据。所以命中索引能够很快的获取到数据。我一般建立索引的原则就是合适。索引越多的话，更新插入的性能也就越慢，因为如果更新索引字段也同时会去更新索引。所以要合适，加索引几个原则：

1）在那些经常需要查询或者经常需要排序分组的字段上

2）在那些区分度高的数据上面去建索引

3）某些字段，比如UUID这种的，可以建前缀索引。因为字段长度过长，索引所占用的空间也就越大，IO效率也就越低，但是UUID其实类似于git的版本号吗，前面几个字符就可以去识别一条记录，可以根据索引选择性来确定到底取多少位的前缀。

建了索引之后，就要尽量避免在SQL语句中编写一些全表扫描的语句，比如：

1）最常见的 模糊匹配，like的 %在前，如果非要做前后模糊匹配 可以存2个字段 一个字段正向存储 一个字段反向存储 ，或者使用全文检索的方式。

2）或者 in 一些比较大的数据量， 我自己做过测试 大概in的数据 达到20% -30%之间 in的索引就会失效

3）或者 索引字段在where条件里有函数

4）或者是 一些关联条件进行了隐式的类型转换，比如在A表是 int类型 B表的关联字段是 varchar类型，我以前做过一个真实的场景就是因为关联字段数据类型不一致导致的，一张合同表 一张审批表，合同不一定有审批，所以合同表左关联 审批表去分页查询，这时候发现就两边差不多都只有10W条数据左右查询10条数据，要6-7秒。我当时第一时间就排查了两边的数据类型，发现我们的合同id是 int 他们是 varchar的数字，因为这个审批表别的模块都在，所以我们没法去改审批表的字段类型，我就把原有的SQL优化了一下， 因为我这个场景本身就是左关联，我先把主表写了一个查询 只查询 10条数据 然后再把这个子查询 做成临时表 去关联 审批表，这样哪怕是全表扫描页就扫着10条数据，优化之后只需要300ms 就可以查询出来了。当然最好的优化方式还是去改字段类型。

5）还有就是一些 is null 的 语句 都会造成索引失效。

光去注意可能还不够，必须要通过慢查SQL来监控哪些是慢查语句，可以通过在数据库里配置 show_query_log=1 来打开慢查日志 或者配置 log_queries_not_using_index =1 来监控哪些语句没用使用索引。一般我们配置慢查的阈值是2秒。

我们拿到这些慢查SQL之后就可以通过 explain 执行计划来排查慢查SQL。看执行计划主要是看type，性能从好到差分别是system > const > eq_reg > ref > range > index > ALL 也就是最起码要达到 range级别性能才可以。我们进行优化也就是通过各种方式去优化type的级别。我记得原来我们有一个出库场景，我们数据库里保存的一条条的存储明细，然后出库出库之后就要把某种商品的出库的库存的状态更新为出库，当我们在做批量出库比一下子出库很多数据，大概1W条的时候，写的是一个in 操作，就是查询出这一批出库的ID，然后 update 状态 in select 这些ID，走的这个索引类型是range，大概耗费8秒.后来我们结合我们数据自身的情况，结合这个自增主键，查询出1W条里面最大的那个值，然后直接更新比这个值小的数据集OK了，这个我们了下，type走的是index merge，把几个索引合并起来操作，优化完成之后耗时大概3秒多，提升了不少。

除了这些，mysql的索引还分为聚集索引和非聚集索引，聚集索引是主键索引，非聚集索引就是普通索引。如果命中非聚集索引，可能还要走一个二次回表操作，查询的数据越多，回表次数也就也多，所以针对这种情况可以对一些比较固定的查询做联合索引，这样查询的时候会走索引覆盖，避免二次回表。这也是一种优化方式。

  • 做合理的数据库设计，这个设计包含索引，字段，表结构几方面。字段设计的话要主要，主键最好是单调递增的数字，主要原因两方面，一方面是单调递增可以保证新插入的数据在最后面避免索引频繁重建，另一方面数字主键的话，查询比较的是数字大小，字符串是逐字比较，性能数字会更好。 一些字段如果是空的可以设置空字符串 或者 0 这样查询的适合也可以走索引 另外就是表结构得设计了。一般设计表结构要满足数据库设计的三大范式，如果按照数据库的第二范式和第三范式，我们应该尽量减少数据库中的冗余，但是目前在数据量大的情况下减少冗余就意味着要增加管理。而mysql的join 语句底层也是一个for循环，多次join就以为着多次嵌套。有时候性能会很差。所以反范式设计，增加适当的冗余就很有必要了。比如一张学生表，一张班级表，一张学生班级关系表。如果要查询哪些班有哪些学生，必须要关联三张表。但是如果把学生姓名，班级名称这些字段冗余到这个学生班级关系表里，就可以不用join多次，增加性能。

  • 做合理的分表，一般我们做分表有垂直拆分和水平拆分两种方式。垂直拆分其实是为了减少单条数据的大小，让聚簇索引能够容纳更多的数据，减少单表的树高度。另一种方式是水平分表，水平分表还分为两种方式，一种是按照某一个字段进行hash分表，一般分表的数量是2的倍数，这种分表方的分表原则就是对表进行压测预估，看看到多少容量级会有明显延迟，一般再按照未来3年的数据量预估来确定要分多少张表。还有一种分表方式是按照时间去进行拆分，形成归档表或者冷热表。比如按照月份一个月一张表去分，或者按照年去分表。实际工作里我们是采用这两种方式结合去做的。比如我们的订单表首先是按照 用户去分的表，我们同时还观察到用户可能只关心最近的数据，所以我们把3个月内的数据定义为热数据，3-6个月的数据定义为温数据，把6个月以上的数据定义为冷数据，然后我们用etl工具会定时把超期数据同步到另外的一些表里，就像饿了么也只能查询1年以内的订单。

  • 配置合理的参数，比如几个比较重要的值比如buffer_pool，独立部署的话，缓冲池大小一般推荐是服务器物理内存的50-80%，还有其他的一些参数比如join_buffer_size 是用于mysql中join时分配的缓冲区大小，sort_buffer_size是用于排序的时候的缓存，如果太小会在硬盘排序性能可能会降低。read_buffer_size 是控制单个session读取数据的缓冲池大小，增大也能提高查询性能。 当然这些参数到底设置成多少，都需要根据实际的内存空间，根据实际压测来测试才能给出。

2.硬件层面

一般来说不到万不得已，不太推荐直接去优化硬件，因为mysql没有我们想的那么脆弱，软件优化的成本最低但是提升效果最大，硬件优化的成本最大但是效果想对是较小的。比如可以优先更换硬盘的类型，更换硬盘的成本其实是最低的，可以把机械硬盘更换成SSD，因为SSD的随机读写能力都比机械硬盘要好。我自己也做过压测，如果是insert语句，插入一个大数据量的字符串，我本机ssd 能够达到1K tps 而 机械硬盘只有600多tps。或者就是扩大硬件规格，因为mysql他其实是一个IO密集型的中间件，更加推荐就是加内存，内存越大，我们上面说的那些缓冲池就设置的越大，这样mysql就能把更多的数据放到内存里来提高查询效率

3.架构层面

我觉得可以去部署mysql的主从集群，比如做一主一从的集群，做了这个集群后，理论上就天然能够把读的性能提升一倍。或者可以采用分区表，但是其实分区表还是会受单机性能影响。比如引入MYCAT中间件，或者sharding-jdbc这类框架。再或者可以引入缓存，虽然缓存并不是直接操作数据库本身，但是引入缓存能够减轻数据库的压力，给那些必须要查库的请求让路，其实是从侧面对数据做了优化，在代码里我们一般用@cacheable注解，去做。或者也可以引入二级缓存来解决， 一级缓存caffeine，guava,,ehcache，二级缓存用redis，memoeryCachae这种。这是我大概自己结合工作考虑的整理整个数据库的优化方式