其他零散知识点Javamysql

Mysql的几个灵魂拷问（五）

2020-10-19  本文已影响0人  千淘萬漉

这一篇继续讲SQL的优化问题，在常规应用开发中，Mysql的单表性能都是够用的，从量级来看，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下Mysql都是可以的，但是如果随着数据量继续上升，超过千万级以后，大表的优化就必须要考虑了，那么千万级的大表优化方案如何来做呢，可以考虑参考下这个顺序：

一、单表优化

事实上很多时候MySQL单表的性能依然有不少优化空间，优化做得好也是能正常支撑千万级以上的数据量的，就从表字段、索引和查询优化来看吧。

1.字段

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
使用枚举或整数代替字符串类型（这个待讨论）
尽量使用TIMESTAMP而非DATETIME，
单表不要有太多字段，建议在20以内
避免使用NULL字段，很难查询优化且占用额外索引空间
用整型来存IP

2.索引

索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键，由程序保证约束
尽量不用UNIQUE，由程序保证约束
使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

3.查询SQL

可通过开启慢查询日志来找出较慢的SQL
不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
不用SELECT *
OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
不用函数和触发器，在应用程序实现
避免%xxx式查询
少用JOIN
使用同类型进行比较，比如用'123'和'123'比，123和123比
尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

4.引擎

一般来说，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表，但是这个地方无脑选择InnoDB就好了，很少见过不用事务、不需奔溃恢复的系统。

二、读写分离

读写分离就是只在主服务器上写，只在从服务器上读。对应到数据库集群一般都是一主一从(一个主库，一个从库)或者一主多从(一个主库，多个从库)，业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。一般读写分离的实现方式有两种：

一文带你了解 Mysql 读写分离

1、主从同步复制是怎么做的

首先先了解mysql主从同步的原理

Mysql主从同步复制原理

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

三、引入缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。目前主要用Redis来实现，对于维护数据库与缓存的一致性也有两种方式：

四、表分区

区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化：

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user_partition | p1,p4      | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where; Using index |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1row in set (0.00 sec)

分区的好处：

分区表的不足：

分区的方式：

分区最适合场景：数据的时间序列性比较强，则可以按时间来分区，不然说下面这个，查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

垂直拆分

首先分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平。再讲拆分之前先引入的讲讲数据库的性能瓶颈

1、IO瓶颈

第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。

第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

2、CPU瓶颈

第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。

第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

1、垂直分库

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。拆到了单独的库以后，可以形成服务化，提供api方式的接口调用。

场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。

其实基于现在微服务拆分来说，都是已经做到了垂直分库了，比如随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

2、垂直分表

概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。类似于列表页和详情页的结构拆分。
结果：

垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会将两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

水平拆分

水平分库和水平分表方法是差不多的，只是会按照拆分规则放库还是放表的区别，但是需要注意两者的场景，有不同的适用场景。

1、水平分库

概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

结果：

场景：如果系统的绝对并发量上来了，水平分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库，这时候就该分库。
分析：库多了，io和cpu的压力自然可以成倍缓解。

2、水平分表

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
结果：

场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。推荐：一次SQL查询优化原理分析

分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

分库分表加深总结

1.分库分表工具

目前主要流行的就是Mycat和sharding-sphere，TDDL是早期的一个方案。

分库分表的具体拆分做法，和前面Mysql分区的range/hash一致，可以参考。

2.现在有一个未分库分表的系统，未来要分库分表，如何设计才可以让系统从未分库分表动态切换到分库分表上？

主要两种方案，一种是停机迁移，一种是双写迁移方案。

首先来看停机迁移方案，先等到深夜将系统挺掉，没有流量写入了，此时老的单库单表数据库静止了。然后把写好一个导数的一次性工具，此时直接跑起来，然后将单库单表的数据批量读取并写到分库分表里面去。导数完了之后，就可以了，此时再修改系统的数据库连接配置，包括可能代码和SQL也许有修改，那你就用最新的代码，然后直接启动连到新的分库分表上去。

再来看双写迁移方案，这个不需要停应用，流程如下：
第一步：修改应用配置和代码，加上双写，部署；
第二步：将老库中的老数据复制到新库中，根据gmt_modified等字段判断这条数据最后修改的时间、或者只有老库里才有的数据才写入到新库中去；
第三步：以老库为准校对新库中的老数据，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止；
第四步：修改应用配置和代码，去掉双写，部署；

同步、异步双写方案

3.扩容

一种方案还是停机扩容，现有库表的数据抽出来慢慢倒入到新的库和表里去，但是耗时和方案不可控。

另外一种就是提前预估好，直接上32个库，每个库32个表，1024张表，这个方案，第一，基本上国内的互联网肯定都是够用了，第二，无论是并发支撑还是数据量支撑都没问题：

每个库正常承载的写入并发量是1000，那么32个库就可以承载32 * 1000 = 32000的写并发，如果每个库承载1500的写并发，32 * 1500 = 48000的写并发，接近5万/s的写入并发，前面再加一个MQ，削峰，每秒写入MQ 8万条数据，每秒消费5万条数据。1024张表，假设每个表放500万数据，在MySQL里可以放50亿条数据。每秒的5万写并发，总共50亿条数据，对于国内大部分的互联网公司来说，其实一般来说都够了！

刚开始的时候，这个库可能就是逻辑库，建在一个数据库上的，就是一个mysql服务器可能建了n个库，比如16个库。后面如果要拆分，就是不断在库和mysql服务器之间做迁移就可以了。然后系统配合改一下配置即可。比如说最多可以扩展到32个数据库服务器，每个数据库服务器是一个库。如果还是不够？最多可以扩展到1024个数据库服务器，每个数据库服务器上面一个库一个表。因为最多是1024个表。

这么搞，是不用自己写代码做数据迁移的，都交给dba来搞好了，但是dba确实是需要做一些库表迁移的工作，但是总比自己写代码，抽数据导数据来的效率高得多了。

3.那分表后的ID怎么保证唯一性的呢？

分布式环境下的ID生成有两个基本的要求：
1， 全局唯一，在分布式集群下，不同的节点并发生成的分布式id要唯一；
2， 顺序性，分布式id是有序生成

这里可以了解一下雪花算法：

snowflake

雪花算法的工作流程是，这个 SnowFlake 算法系统首先肯定是知道自己所在的机房和机器的，比如机房 id = 17，机器 id = 12。接着 SnowFlake 算法系统接收到这个请求之后，首先就会用二进制位运算的方式生成一个 64 bit 的 long 型 id，64 个 bit 中的第一个 bit 是无意义的。接着 41 个 bit，就可以用当前时间戳（单位到毫秒），然后接着 5 个 bit 设置上这个机房 id，还有 5 个 bit 设置上机器 id。最后再判断一下，当前这台机房的这台机器上这一毫秒内，这是第几个请求，给这次生成 id 的请求累加一个序号，作为最后的 12 个 bit。最终一个 64 个 bit 的 id 就出来了

4.分表后非sharding_key的查询怎么处理呢？

兼容MySQL且可水平扩展的数据库

目前也有一些开源数据库兼容MySQL协议，如：TiDB，在一个 TiDB 的数据库上，所有业务场景不需要做分库分表，所有的分布式工作都由数据库层完成。TiDB 兼容 MySQL 协议，所以可以直接替换 MySQL，而且基本做到了开箱即用。

NoSQL

在MySQL上做Sharding是一种戴着镣铐的跳舞，事实上很多大表本身对MySQL这种RDBMS的需求并不大，并不要求ACID，可以考虑将这些表迁移到NoSQL，彻底解决水平扩展问题，例如：

参考引用

1、MySQL 大表优化方案
2、MySQL 优化提高笔记整理
3、《我想进大厂》之mysql夺命连环13问
4、MySQL:互联网公司常用分库分表方案汇总

上一篇 下一篇

猜你喜欢

热点阅读