数据库优化：从索引到分库分表策略-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_42411696/article/details/127483751

前言

经常都会被问到或者与遇到数据库调优的问题，我的一般思路如下：

1）首先是数据量（百万级以上），需不需要分库分表；
2）第二是需不需要使用缓存技术，缓存一些热数据；
3）第三是sql优化，如果sql太复杂了，那我一般会用到explain分析sql的执行计划，优化sql；
4）第四选择索引；
5）还有些读写分离、网络带宽的东西。

一、索引

对where和order by后面的条件列进行分析，看是否有建索引的必要，要建是建那种索引：普通索引、唯一索引、全文索引或者单行索引、复合索引。

二、sql的优化

查询语句的优化，首先考虑在where和order by后的条件建立索引；
不要在where后的设置过索引的字段使用=和<>等符号，引擎会放弃索引进而全表扫描；
尽量避免在where子句中使用or，一个字段有索引，一个字段没有索引，引擎也会放弃索引进行全盘扫描。or可以用union all替代；
in和not in的使用也会造成全表扫描，in 和 not in也可以用exists 和not exists代替。如：select num from a where num in(select num from b)替换为select num from a where exists(select 1 from b where num=a.num)会提高效率；
注意like的使用，’%?%’ 和’?%'效率肯定是有区别的；
避免在where子句中对字段进行函数操作和表达式计算；
任何地方都不要使用 select * from * ，用具体的字段列表代替“*”；
sql不要返回任何用不到的字段。

三、表结构

少用null，最好使用not null来填充数据库，可以设置默认值的都设置默认值。比如：设置订单数据的初始订单状态：待支付；消息的状态为：已发送。
充分考虑字段类型和字段长度。

四、分库分表

针对百万级别以上的数据表，再考虑分库分表。几万条、十几万条数据，就先不要考虑这些了。

(1) 垂直拆分
垂直分表：基于列拆分数据。一般是表中的字段较多，将不常用的、数据较大的、长度较长的列拆分到“扩展表“。
垂直分库：垂直分库针对的是一个系统中的不同业务，比如说user一个库，product一个库，order一个库，activity一个库，shopCar一个库。切分后，要放在多个服务器上，而不是一个服务器上。为什么？我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前，全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上，随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。
(2) 水平拆分
水平分表：针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。每张表都是相同的数据结构，而且这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。
水平分表分库：水平分库分表较之水平分表类似，也是将数据量巨大的单张表，按照某种规则切分到多张表里去，每张表都有相同的数据结构，但是这些数据不在同一个数据库里。这样易于突破瓶颈压力–比如IO的压力，连接数的压力

五、读写分离

为了减少单服务器和单库的IO瓶颈、连接数压力。我们一般采用读写分离的模式：主写从读。
这个地方我曾经遇到过一个坑，主库数据同步到从库是需要时间的，主库里订单数据变更成已支付，但是从库订单数据仍然是待支付，导致我读数据出来仍是待支付返回给了用户。如何解决？可以借助redis缓存中间件，将更新的（此种操作都会发生在主库操作上）数据按照用户ID+业务ID+其他业务维度做成KEY，并将其存储在redis中，设置失效时间就是1秒；从库做查询时按照上述key去redis中查找如果存在则读取主库，如果不存在说明数据已经同步到了从库直接查从库即可。